澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

MySQL进阶

索引

1、概述

MySQL索引的创建对于MySQL的敏捷运维是很入眼的,索引能够大大进步MySQL的追寻速度。

就算索引大大进步了查询速度,同期却会下滑更新表的快慢,如对表实行INSERT、UPDATE和DELETE。因为更新表时,MySQL不独有要保存数据,还要保存一下索引文件。

确立索引会占用磁盘空间的目录文件。

2、索引体系

  • 常备索引:仅加快查询
  • 独一索引:加速查询 列值独一(能够有null)
  • 主键索引:加快查询 列值独一  表中独有二个(不得以有null)
  • 组合索引:多列值组成三个目录,
                  特意用来组合寻找,其成效超过索引合併
  • 全文索引:对文件的剧情开展分词,实行寻找 

目录合併:使用三个单列索引组合查询检索
覆盖索引:select的数据列只用从索引中就可见获得,不必读取数据行,换句话说查询列要被所建的目录覆盖

a、普通索引

平日索引独有三个效应:加速查询

图片 1图片 2

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

创建表 索引

图片 3图片 4

create index index_name on table_name(column_name)

创设索引

图片 5图片 6

drop index_name on table_name;

除去索引

图片 7图片 8

show index from table_name;

翻开索引

留心:对于开创索引时倘诺是BLOB 和 TEXT 类型,必需钦赐length。

create index ix_extra on in1(extra(32));

b、独一索引

独一索引有四个职能:加速查询 和 独一约束(可含null)

图片 9图片 10

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

开创独一索引 表

图片 11图片 12

create unique index 索引名 on 表名(列名)

创办独一索引

图片 13图片 14

drop unique index 索引名 on 表名

删去独一索引

c、主键索引

主键有多个成效:加快查询 和 独一约束(不可含null)

图片 15图片 16

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

成立表 制造主键

图片 17图片 18

alter table 表名 add primary key(列名);

始建主键

图片 19图片 20

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

删去主键

d、组合索引

结缘索引是将n个列组合成一个目录

其行使场景为:频仍的还要利用n列来进展询问,如:where n1 = 'djb' and n2 = 666

图片 21图片 22

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

创建表

图片 23图片 24

create index ix_name_email on in3(name,email);

创制索引

3、相关命令

- 查看表结构
    desc 表名

- 查看生成表的SQL
    show create table 表名

- 查看索引
    show index from  表名

- 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

4、使用索引和不选用索引

出于索引是特意用来加速寻找而生,所以加上索引之后,查询成效会快到飞起来。

5、精确选取索引

数据库表中加多索引后真正会让查询速度起飞,但前提必需是情有可原的使用索引来查询,假设以错误的艺术使用,则就是创设目录也会不奏效。
就算建构目录,索引也不会卓有功能:

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的:当or条件中有未建立索引的列才失效,以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的:如果是主键,则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的:如果是主键或索引是整数类型,则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候,选择的映射如果不是索引,则不走索引
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

6、其余注意事项

1 - 避免使用select *
2 - count(1)或count(列) 代替 count(*)
3 - 创建表时尽量时 char 代替 varchar
4 - 表的字段顺序固定长度的字段优先
5 - 组合索引代替多个单列索引(经常使用多个条件查询时)
6 - 尽量使用短索引
7 - 使用连接(JOIN)来代替子查询(Sub-Queries)
8 - 连表时注意条件类型需一致
9 - 索引散列值(重复少)不适合建索引,例:性别不适合

7、limit分页

方案:
记录当前页最大或最小ID
1. 页面只有上一页,下一页
# max_id
# min_id
下一页:
select * from userinfo3 where id > max_id limit 10;
上一页:
select * from userinfo3 where id < min_id order by id desc limit 10;
2. 上一页 192 193  [196]  197  198  199 下一页

select * from userinfo3 where id in (select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)

8、实施陈设

explain 查询SQL - 用于显示SQL推行音讯参数,依据参谋新闻能够举办SQL优化

mysql> explain select * from tb2;
 ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------- 
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
 ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------- 
|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
 ---- ------------- ------- ------ --------------- ------ --------- ------ ------ ------- 
1 row in set (0.00 sec)

9、慢日志查询

a、配置MySQL自动记录慢日志

slow_query_log = OFF                            是还是不是开启慢日志记录
long_query_time = 2                              时间范围,超过此时间,则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF     为利用索引的探求是或不是记录

注:查看当前布置音信:
       show variables like '%query%'
     修改当前配备:
    set global 变量名 = 值

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

图片 25图片 26

"""
--verbose    版本
--debug      调试
--help       帮助

-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配;grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间;don't subtract lock time from total time
"""

View

视图

视图是一个设想表(非真实存在),其本质是【依照SQL语句获取动态的数据集,并为其命名】,用户使用时只需选取【名称】就能够获取结果集,并可以将其当作表来使用。

图片 27图片 28

SELECT
    *
FROM
    (
        SELECT
            nid,
            NAME
        FROM
            tb1
        WHERE
            nid > 2
    ) AS A
WHERE
    A. NAME > 'alex';

不时表找寻

1、创设视图

图片 29图片 30

--格式:CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

View Code

2、删除视图

图片 31图片 32

--格式:DROP VIEW 视图名称

DROP VIEW v1

View Code

3、修改视图

图片 33图片 34

-- 格式:ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

View Code

4、使用视图

使用视图时,将其当作表进行操作就可以,由于视图是虚构表,所以不可能运用其对量体裁衣表进行创办、更新和删除操作,仅能做询问用。

图片 35图片 36

select * from v1

View Code

视图

视图是三个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其取名】,顾客选拔时只需选拔【名称】就可以得到结果集,并得以将其当作表来选用。

图片 37图片 38

SELECT
    *
FROM
    (
        SELECT
            nid,
            NAME
        FROM
            tb1
        WHERE
            nid > 2
    ) AS A
WHERE
    A. NAME > 'alex';

偶然表寻觅

1、成立视图

图片 39图片 40

--格式:CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

View Code

2、删除视图

图片 41图片 42

--格式:DROP VIEW 视图名称

DROP VIEW v1

View Code

3、修改视图

图片 43图片 44

-- 格式:ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

View Code

4、使用视图

接纳视图时,将其当作表实行操作就能够,由于视图是设想表,所以不能使用其对实在表张开创办、更新和删除操作,仅能做询问用。

图片 45图片 46

select * from v1

View Code

事务

1、概述

MySQL 事务首要用于拍卖操作量大,复杂度高的数量。比方说,在人口管理连串中,你剔除壹人口,你即需求删除人士的基本资料,也要去除和该人士相关的音信,如信箱,文章等等,那样,那么些数据库操作语句就重组叁个政工,可是假若有某叁个面世错误,就能够回滚到原本的图景,进而确认保证数据库数据完整性。!

  • 在MySQL中独有应用了Innodb数据库引擎的数据库或表才帮助职业
  • 事务管理能够用来敬爱数据库的完整性,保障成批的SQL语句要么全部实行,要么全体不实践
  • 事务用来保管insert,update,delete语句

相似的话,事务是必得满足4个尺码(ACID): Atomicity(原子性)、Consistency(牢固性)、Isolation(隔开性)、Durability(可信赖性)

  • 1、政工的原子性:一组职业,要么成功;要么撤回。
  • 2、稳定性 : 有不合法数据(外键约束之类),事务撤回。
  • 3、隔离性:事务独立运维。三个事务管理后的结果,影响了其他工作,那么另外事务会撤回。事务的百分之百隔断,需求就义速度。
  • 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可信赖性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定哪些时候呢事务保存到日志里。

2、事务操作

  • 拉开事务 start transaction
  • 回滚事务 rollback
  • 交给业务 commit
  • 保留点    savepoint

    delimiter -- 此条可改造SQL语句截止符 create PROCEDURE p1(

    OUT p_return_code tinyint
    

    ) BEGIN DECLARE exit handler for sqlexception BEGIN

    -- ERROR 
    set p_return_code = 1; 
    rollback; 
    

    END;

    DECLARE exit handler for sqlwarning BEGIN

    -- WARNING 
    set p_return_code = 2; 
    rollback; 
    

    END;

    START TRANSACTION;

    DELETE from tb1;
    insert into tb2(name)values('seven');
    

    COMMIT;

    -- SUCCESS set p_return_code = 0;

    END delimiter ;

触发器

对有些表张开【增/删/改】操作的左右假设指望触发有个别特定的一言一行时,能够使用触发器,触发器用于定制客商对表的行开展【增/删/改】前后的行为。

1、创制基本语法

图片 47图片 48

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

View Code

图片 49图片 50

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'alex' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;

插入前触发器

图片 51图片 52

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('666'),
            ('666') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('555'),
            ('555') ;
    END IF;
END//
delimiter ;

插入后触发器

特意的:NEW表示就要插入的多少行,OLD代表将在删除的数据行。

2、删除触发器

图片 53图片 54

DROP TRIGGER tri_after_insert_tb1;

View Code

3、使用触发器

触发器不恐怕由客商一贯调用,而知由于对表的【增/删/改】操作被动引发的。

图片 55图片 56

insert into tb1(num) values(666)

View Code

触发器

对某些表举行【增/删/改】操作的上下如若期望触发有些特定的一坐一起时,能够使用触发器,触发器用于定制顾客对表的行开展【增/删/改】前后的行为。

1、创立基本语法

图片 57图片 58

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

View Code

图片 59图片 60

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'alex' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;

插入前触发器

图片 61图片 62

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('666'),
            ('666') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('555'),
            ('555') ;
    END IF;
END//
delimiter ;

布置后触发器

特意的:NEW表示将在插入的数据行,OLD代表就要删除的数据行。

2、删除触发器

图片 63图片 64

DROP TRIGGER tri_after_insert_tb1;

View Code

3、使用触发器

触发器不可能由客商一直调用,而知由于对表的【增/删/改】操作被动引发的。

图片 65图片 66

insert into tb1(num) values(666)

View Code

视图

视图是三个设想表(非真实存在),其本质是【依据SQL语句获取动态的数据集,并为其命名】,客商使用时只需利用【名称】就能够获得结果集,并得以将其当作表来行使。

图片 67图片 68

SELECT
    *
FROM
    (
        SELECT
            nid,
            NAME
        FROM
            tb1
        WHERE
            nid > 2
    ) AS A
WHERE
    A. NAME > 'djb';

有时表搜素

1、成立视图

图片 69图片 70

--格式:CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

始建视图

2、删除视图

图片 71图片 72

--格式:DROP VIEW 视图名称

DROP VIEW v1

剔除视图

3、修改视图

图片 73图片 74

-- 格式:ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

修改视图

4、使用视图

应用视图时,将其当作表举行操作就能够,由于视图是虚构表,所以不可能接纳其对真实表展开创办、更新和删除操作,仅能做询问用。

图片 75图片 76

select * from v1

View Code

存储进度

储存进程是三个SQL语句集合,当主动去调用存款和储蓄进度时,个中内部的SQL语句会按部就班逻辑实践。

1、创造存储进程

图片 77图片 78

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;



-- 执行存储过程

call p1()

无参数存款和储蓄进度

对此仓库储存进程,可以选拔参数,其参数有三类:

  • in          仅用于传入参数用
  • out        仅用于再次回到值用
  • inout     不只能够流传又足以当作重回值

图片 79图片 80

-- 创建存储过程
delimiter \
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;

    set temp1 = 1;

    set r1 = i1   i2   temp1   temp2;

    set i3 = i3   100;

end\
delimiter ;

-- 执行存储过程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

有参数存款和储蓄进度

2、删除存款和储蓄进程

图片 81图片 82

drop procedure proc_name;

View Code

3、施行存款和储蓄进度

图片 83图片 84

-- 无参数
call proc_name()

-- 有参数,全in
call proc_name(1,2)

-- 有参数,有in,out,inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)

View Code

图片 85图片 86

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

pymysql施行存款和储蓄进度

仓库储存进度

积存进程是叁个SQL语句集合,当主动去调用存款和储蓄进度时,当中内部的SQL语句会安份守己逻辑试行。

1、创造存款和储蓄进度

图片 87图片 88

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;



-- 执行存储过程

call p1()

无参数存储进程

对此仓储进度,可以吸收接纳参数,其参数有三类:

  • in          仅用于传入参数用
  • out        仅用于再次回到值用
  • inout     不仅能够流传又足以当作重返值

图片 89图片 90

-- 创建存储过程
delimiter \
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;

    set temp1 = 1;

    set r1 = i1   i2   temp1   temp2;

    set i3 = i3   100;

end\
delimiter ;

-- 执行存储过程
DECLARE @t1 INT default 3;
DECLARE @t2 INT;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

有参数存款和储蓄进度

2、删除存款和储蓄进程

图片 91图片 92

drop procedure proc_name;

View Code

3、实施存储进度

图片 93图片 94

-- 无参数
call proc_name()

-- 有参数,全in
call proc_name(1,2)

-- 有参数,有in,out,inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)

View Code

图片 95图片 96

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

pymysql施行存款和储蓄进度

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:MySQL进阶