绿色健康小清新

耐得住寂寞,守得住繁华

Mysql性能优化

SQL语言分类

SQL(Structure Query Language)语言是数据库的核心语言。

SQL语言共分为四大类:

  • 数据定义语言DDL
  • 数据操纵语言DML
  • 数据查询语言DQL
  • 数据控制语言DCL
  1. 数据定义语言DDL

    数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如:

    CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

    DDL操作是隐性提交的!不能rollback

  2. 数据操纵语言DML

    数据操纵语言DML主要有三种形式:

    • 插入:INSERT
    • 更新:UPDATE
    • 删除:DELETE
  3. 数据查询语言DQL

    数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块:

    SELECT <字段名表>

    FROM <表或视图名>

    WHERE <查询条件>

  4. 数据控制语言DCL

    数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。如:

    • GRANT:授权。

    • ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点。

      回滚—ROLLBACK

      回滚命令使数据库状态回到上次最后提交的状态。其格式为:

      SQL>ROLLBACK


原理

MYSQL逻辑分层 :连接层 服务层 引擎层 存储层

InnoDB(默认) :事务优先 (适合高并发操作;行锁)具体看11.1

  1. 通过事务进行解锁,commit和rollback,在InnoDB中默认commit,三种方式解除自动提交

  2. 如果没有索引,则锁会转为表锁,此时受事务控制,需要用commit或rollback去解锁

  3. select不会自动上锁,通过for update上锁。并且select可以查找加锁的数据

  4. 因此上锁的条件为有索引并且为增删改,

  5. 注意间隙锁

MyISAM :性能优先 (表锁,不支持事务)

  1. 表锁,lock table … read/write以及unlock tables,

  2. MyISAM不支持事务,默认全部提交.在IoonDB中可能会受事务控制

  3. 写锁(write)和读锁(read)

查询数据库引擎:

  • 支持哪些引擎?

    1
    show engines ;
  • 查看当前使用的引擎

    1
    show variables like '%storage_engine%' ;

指定数据库对象的引擎:

1
2
3
4
5
6
create table tb(
id int(4) auto_increment ,
name varchar(5),
dept varchar(5) ,
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

SQL优化

原因:性能低、执行时间太长、等待时间太长、SQL语句欠佳(连接查询)、索引失效、服务器参数设置不合理(缓冲、线程数)

  1. SQL :

    编写过程:

    1
    select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by 		..limit ..

    解析过程:

    1
    from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
  2. SQL优化, 主要就是 在优化索引

    索引: 相当于书的目录

    索引: index是帮助MYSQL高效获取数据的数据结构。索引是数据结构(树:B+树(默认)、Hash树…)

    索引的弊端:

    1. 索引本身很大, 可以存放在内存/硬盘(通常为 硬盘)
    2. 索引不是所有情况均适用:
      • 少量数据
      • 频繁更新的字段
      • 很少使用的字段
      • 不会出现在where中的字段
    3. 索引会降低增删改的效率(增删改 查)

    优势:

    1. 提高查询效率(降低IO使用率)
    2. 降低CPU使用率 (…order by age desc,因为 B树索引 本身就是一个 好排序的结构,因此在排序时 可以直接使用)

注意:

  1. 在左外连接中,一般是左表加索引
  2. 尽量不要对复合索引进行操作,避免失效。
  3. !=,<>,is null,is not null,%%,类型转换,or会直接失效,<,>,<=,>=会看情况进行索引

https://www.cnblogs.com/annsshadow/p/5037667.html

索引

MyISAM引擎使用b+tree作为索引,叶子节点data存放的是记录地址。MyISAM中主索引与辅助索引形式是一样的,主索引要求key不能重复,辅助索引key可以重复。这种索引方式叫非聚集索引。

InnoDB引擎,与MyISAM索引与数据分开存放不同的是,InnoDB引擎数据文件本身就是一个索引,InnoDB引擎数据存放是按b+tree结构组织存放的,叶子节点包含全部的数据信息,这个树的key就是主键,这样的设计会导致数据按照主键聚集,所以这种方式叫聚集索引,正是因为这样,InnoDB引擎要求表必须有主键,这样的设计可以使按主键的查找非常高效。

InnoDB引擎辅助索引叶子节点存放的是主键,正式这个设计,所以不建议主键使用长字段,因为会导致辅助索引data占用空间太大。

分类:

  • 主键索引: 不能重复。id 不能是null

  • 唯一索引 :不能重复。id 可以是null

  • 单值索引: 单列, age ;一个表可以多个单值索引,name。

  • 复合索引 :多个列构成的索引 (相当于 二级目录 : z: zhao) (name,age) (a,b,c,d,…,n)(注意最佳左前缀)

注意:

  1. 要能使用索引必须保证索引的数据是排好序的,会自动根据where排好序否则.也有特殊情况经过多次排序乱了(比如使用了<,<=,>,>=),后面一次复合索引的乱了,看性能,还可能会在原表中查询,索引无效。可能当前也不会索引,因为优化器的原因,概率事件.具体看6.3中的特列和5.6; 例子在7.3(a,b,c)

  2. 好几个单个索引有可能并不会全部采用,根据性能而定.而且如果出现了!=,<>,is null,is not null,%%,类型转换,or的情况索引直接失效(可用in或union优化),主体看7.3

  3. 有一些概率事件和索引失效的情况,无法避免,因此尽量 using index.

  4. 就算是符合索引也可以用索引查单个。比如(a,b),where b=3;此时会用到索引,因为优先级的关系,先查找a,再找b,具体看5.6

  5. 注意复合索引中的顺序与执行循序相同(不是编写顺序),避免出现回表查寻

  6. where中注意a.小表驱动大表 b.若复合索引,使用字段顺序要与创建的索引一致 c.尽量select中的数据在where中或者后面存在,避免进行回表查询

创建索引:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
方式一:
create 索引类型 索引名 on 表(字段)
单值:
create index dept_index on tb(dept);
唯一:
create unique index name_index on tb(name) ;
复合索引
create index dept_name_index on tb(dept,name);

方式二:
alter table 表名 索引类型 索引名(字段)
单值:
alter table tb add index dept_index(dept) ;
唯一:
alter table tb add unique index name_index(name);
复合索引
alter table tb add index dept_name_index(dept,name);

注意:如果一个字段是primary key,则改字段默认就是 主键索引

1
2
3
4
5
6
7
删除索引:
drop index 索引名 on 表名 ;
drop index name_index on tb ;

查询索引:
show index from 表名 ;
show index from 表名 \G

SQL性能问题

分析SQL的执行计划 : explain ,可以模拟SQL优化器执行SQL语句,从而让开发人员 知道自己编写的SQL状况

MySQL查询优化其会干扰我们的优化

优化方法,官网:https://dev.mysql.com/doc/refman/5.5/en/optimization.html

查询执行计划: explain +SQL语句

1
explain  select  * from tb ;
  • id : 编号

  • select_type :查询类型

  • table :表

  • type :类型

  • possible_keys :预测用到的索引

  • key :实际使用的索引

  • key_len :实际使用索引的长度

  • ref :表之间的引用

  • rows :通过索引查询到的数据量

  • Extra :额外的信息

准备数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);


insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

查询课程编号为2 或 教师证编号为3 的老师信息

explain +sql:

id

id值相同,从上往下 顺序执行。 t3-tc3-c4 tc3–c4-t6

表的执行顺序 因数量的个数改变而改变的原因: 笛卡儿积

a b c

4 3 2 = 2*3=6 * 4 =24

        3*4=12* 2 =24

数据小的表 优先查询;

id值不同:id值越大越优先查询 (本质:在嵌套子查询时,先查内层 再查外层)

查询教授SQL课程的老师的描述(desc)

1
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql' ;

将以上 多表查询 转为子查询形式:

1
2
3
4
explain select tc.tcdesc from teacherCard tc where tc.tcid = 
(select t.tcid from teacher t where t.tid =
(select c.tid from course c where c.cname = 'sql')
);

id值有相同,又有不同: id值越大越优先;id值相同,从上往下 顺序执行

子查询+多表:

1
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;

select_type:查询类型(五种)

PRIMARY:包含子查询SQL中的 主查询 (最外层)

SUBQUERY:包含子查询SQL中的 子查询 (非最外层)

simple:简单查询(不包含子查询、union)

derived:衍生查询(使用到了临时表)

  • 在from子查询中只有一张表

    1
    explain select  cr.cname 	from ( select * from course where tid in (1,2) ) cr ;
  • 在from子查询中, 如果有table1 union table2 ,则table1 就是derived,table2就是union

    1
    explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

union:上例

union result :告知开发人员,那些表之间存在union查询


type:索引类型、类型(符合索引的话以最左前缀为准)

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>all ,要对type进行优化的前提:有索引

其中:system,const只是理想情况;实际能达到 ref>range

system(忽略): 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询;这是const类型的特列

1
2
3
4
5
6
7
8
9
10
create table test02
(
tid int(3),
tname varchar(20)
);

insert into test01 values(1,'a') ;
alter table test01 add constraint tid_pk primary key(tid) ;
explain select * from (select * from test01 )t where tid =1 ;
commit;

const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 (类型 与索引类型有关,即使用到的where是主键或者unique并且一般为=,唯一的只显示一条数据,并且找到之后不用继续向下寻找)

1
2
3
4
explain select tid from test01 where tid =1 ;
//测试不是主键
alter table test01 drop primary key ;
create index test01_index on test01(tid) ;

eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(用于主键和唯一索引,匹配一个不用继续向下查询)

1
explain select * from test01 t1,test02 t2 where t1.tid =t2.tid;

以上SQL,用到的索引是 t.tname,即teacher表中的tname字段;

如果teacher表的数据个数 和 连接查询的数据个数一致(都是3条数据),则有可能满足eq_ref级别;否则无法满足。

ref非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多),每匹配一个还要继续向下向下匹配,联合查询和=,

准备数据:

测试:

1
2
3
alter table teacher add index index_name (tname) ;

explain select * from teacher where tname = 'tz';

range检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,)

1
2
3
4
5
alter table teacher add index tid_index (tid) ;

explain select t.* from teacher t where t.tid in (1,2) ;

explain select t.* from teacher t where t.tid <3 ;

index查询全部索引中数据,跟all的区别在与只需要在索引中查询而不用在硬盘中全部读取.

explain select tid from teacher ; --tid 是索引, 只需要扫描索引表,不需要所有表中的所有数据

all查询全部表中的数据,在硬盘中读取,慢

explain select cid from course ; --cid不是索引,需要全表所有,即需要所有表中的所有数据

system/const: 结果只有一条数据

eq_ref:结果多条;但是每条数据是唯一的 ;

ref:结果多条;但是每条数据是是0或多条 ;


possible_keys :可能用到的索引,是一种预测,不准

1
2
3
alter table  course add index cname_index (cname);

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;

如果 possible_key/key是NULL,则说明没用索引

1
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql' ;

key :实际使用到的索引

key_len :索引的长度

作用:用于判断复合索引是否被完全使用 (a,b,c)。

1
2
3
4
5
6
7
8
9
10
11
create table test_kl

(

name char(20) not null default ''

);

alter table test_kl add index index_name(name) ;

explain select * from test_kl where name ='' ; -- key_len :60

在utf8:1个字符站3个字节

1
2
3
4
5
alter table test_kl add column name1 char(20) ;  --name1可以为null

alter table test_kl add index index_name1(name1) ;

explain select * from test_kl where name1 ='' ;

如果索引字段可以为Null,则会使用1个字节用于标识。

1
2
3
drop index index_name on test_kl ;

drop index index_name1 on test_kl ;

增加一个复合索引 (一级一级查,先查nam,并且name为index)

1
2
3
4
5
alter table test_kl add index name_name1_index (name,name1) ; 

explain select * from test_kl where name1 = '' ; --121

explain select * from test_kl where name = '' ; --60

注意不同

1
2
3
4
5
6
7
varchar(20)

alter table test_kl add column name2 varchar(20) ; --可以为Null

alter table test_kl add index name2_index (name2) ;

explain select * from test_kl where name2 = '' ; --63

20*3=60 + 1(null) +2(用2个字节 标识可变长度) =63

utf8:1个字符3个字节

gbk:1个字符2个字节

latin:1个字符1个字节

ref : 注意与type中的ref值区分

作用: 指明当前表所 参照的 字段。

select …where a.c = b.x 😭其中b.x可以是常量,const)

1
explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;

也可以是某个字段

rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数,估算值)

1
explain select * from course c,teacher t  where c.tid = t.tid and t.tname = 'tz' ;

Extra

  • using filesort : 性能消耗大;需要“额外”的一次排序(查询) 。常见于 order by 语句中。优化可看8.2.

    排序:先查询

    10个人 根据年龄排序。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create table test02
    (
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index idx_a1(a1),
    index idx_a2(a2),
    index idx_a3(a3)
    );

    explain select * from test02 where a1 ='' order by a1 ;

    a1:姓名 a2:年龄

    1
    explain select * from test02 where a1 ='' order by a2 ; --using filesort

    小结:对于单索引, 如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort;

    避免: where哪些字段,就order by那些字段2

    复合索引:不能跨列(最佳左前缀)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    drop index idx_a1 on test02;
    drop index idx_a2 on test02;
    drop index idx_a3 on test02;

    alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
    explain select *from test02 where a1='' order by a3 ; --using filesort
    explain select *from test02 where a2='' order by a3 ; --using filesort
    explain select *from test02 where a1='' order by a2 ;
    explain select *from test02 where a2='' order by a1 ; --using filesort

    小结:避免: where和order by 按照复合索引的顺序使用,不要跨列或无序使用。

  • using temporary:性能损耗大 ,用到了临时表。一般出现在group by 语句中。

    1
    2
    explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
    explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary(一张带a1和a2的表)

    避免:查询那些列,就根据那些列 group by .

  • using index :性能提升; 索引覆盖(覆盖索引)。原因:不读取原文件,只从索引文件中获取数据 (不需要回表查询)

    只要使用到的列 全部都在索引中,就是索引覆盖using index

    例如:test02表中有一个复合索引(a1,a2,a3)

    1
    2
    3
    4
    5
    6
    explain select a1,a2 from test02 where a1='' or a2= '' ; --using index   

    drop index idx_a1_a2_a3 on test02;

    alter table test02 add index idx_a1_a2(a1,a2) ;
    explain select a1,a3 from test02 where a1='' or a3= '' ;

    如果用到了索引覆盖(using index时),会对 possible_keys和key造成影响:

    • 如果没有where,则索引只出现在key中;

    • 如果有where,则索引 出现在key和possible_keys中。

      1
      2
      explain select a1,a2 from test02 where a1='' or a2= '' ;
      explain select a1,a2 from test02 ;
  • using where (需要回表查询)

    假设age是索引列

    但查询语句select age,name from …where age =…,此语句中必须回原表查Name,因此会显示using where.

    1
    explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询
  • impossible where : where子句永远为false

    1
    explain select * from test02 where a1='x' and a1='y'  ;

优化案例

  • 单表优化
  • 两表优化
  • 三表优化

示例:

1
2
3
4
5
6
7
8
create table test04
(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);
alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
1
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; 

推荐写法,因为 索引的使用顺序(where后面的顺序) 和 复合索引的顺序一致

1
explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; 

虽然编写的顺序 和索引顺序不一致,但是 sql在真正执行前 经过了SQL优化器的调整,结果与上条SQL是一致的。

以上 2个SQL,使用了 全部的复合索引

1
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; 

以上SQL用到了a1 a2两个索引,该两个字段 不需要回表查询using index ;而a4因为跨列使用,造成了该索引失效,需要回表查询 因此是using where;以上可以通过 key_len进行验证

1
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3; 

以上SQL出现了 using filesort(文件内排序,“多了一次额外的查找/排序”) :不要跨列使用( where和order by 拼起来,不要跨列使用)

1
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; 

上不会using filesort


有一种特殊情况:

select * from table where a = '1' and b > ‘2’ and c='3'这种类型的也只会有a与b走索引,c不会走。

始终记得,在数据库中是先按照a排序,后按照b排序,最后按照c排序。 首先索引找a,找到后在a相等的条件下,b必然是有序的。因为先按照a排序,若a相同,按照b排序,因此a相等的条件下,b必然有序。 好,我们继续。当找到所有b>2的之后,c还有序么?不,c不有序。因为(b,c)的组合可以是(100000,1),也可以是(1,100)。发现了么,当你根据b找到区间之后,c是无序的,因为b和c之间没有关系。你不能保证所有大于2的b的那些记录中,c还是有序的。

总结:

  • 如果 (a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用),则复合索引全部使用。如果部分一致(且不跨列使用),则使用部分索引。

    1
    select a,c where  a = and b= and d= 
  • where和order by 拼起来,不要跨列使用

using temporary:需要额外再多使用一张表. 一般出现在group by语句中;已经有表了,但不适用,必须再来一张表。

解析过程:

1
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
  • explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;–没有using temporary

  • explain select * from test03 where a2=2 and a4=4 group by a3 ;

单表优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table book
(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

insert into book values(1,'tjava',1,1,2) ;
insert into book values(2,'tc',2,1,2) ;
insert into book values(3,'wx',3,2,1) ;
insert into book values(4,'math',4,2,3) ;
commit;

查询authorid=1且 typeid为2或3的 bid

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;

(a,b,c)

(a,b)

优化:加索引

1
alter table book add index idx_bta (bid,typeid,authorid);//不是最佳

索引一旦进行 升级优化,需要将之前废弃的索引删掉,防止干扰。

1
drop index idx_bta on book;

根据SQL实际解析的顺序,调整索引的顺序:

1
alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid,但是将bid放到索引中 可以提升使用using index ;

再次优化(之前是index级别):思路。因为范围查询in有时会实现,因此交换 索引的顺序,将typeid in(2,3) 放到最后。

1
2
3
drop index idx_tab on book;
alter table book add index idx_atb (authorid,typeid,bid);
explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;

小结:

  • 最佳做前缀,保持索引的定义和使用的顺序一致性
  • 索引需要逐步优化
  • 将含In的范围查询 放到where条件的最后,防止失效。

本例中同时出现了Using where(需要回原表); Using index(不需要回原表):原因,where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中,因此不需要回原表(直接在索引表中能查到);而typeid虽然也在索引(authorid,typeid,bid)中,但是含in的范围查询已经使该typeid索引失效,因此相当于没有typeid这个索引,所以需要回原表(using where);

例如以下没有了In,则不会出现using where

1
explain select bid from book where  authorid=1 and  typeid =3 order by typeid desc ;

还可以通过key_len证明In可以使索引失效。


两表优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create table teacher2
(
tid int(4) primary key,
cid int(4) not null
);

insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2
(
cid int(4) ,
cname varchar(20)
);

insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'kotlin');
commit;

左连接:
explain select *from teacher2 t left outer join course2 c
on t.cid=c.cid where c.cname='java';

索引往哪张表加?

  • 小表驱动大表
  • 索引建立经常使用的字段上 (本题 t.cid=c.cid可知,t.cid字段使用频繁,因此给该字段加索引) [一般情况对于左外连接,给左表加索引;右外连接,给右表加索引]

小表:10

大表:300

where

  • 小表.x 10 = 大表.y 300; --循环了几次?10

  • 大表.y 300=小表.x 10 --循环了300次

小表:10

大表:300

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select ...where 小表.x10=大表.x300 ;
for(int i=0;i<小表.length10;i++)
{
for(int j=0;j<大表.length300;j++)
{
...
}
}


select ...where 大表.x300=小表.x10 ;
for(int i=0;i<大表.length300;i++)
{
for(int j=0;j<小表.length10;j++)
{
...
}
}

以上2个FOR循环,最终都会循环3000次;但是 对于双层循环来说:一般建议 将数据小的循环 放外层;数据大的循环放内存。

当编写 …on t.cid=c.cid 时,将数据量小的表 放左边(假设此时t表数据量小)

1
2
alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);

Using join buffer:extra中的一个选项,作用:Mysql引擎使用了 连接缓存。


三张表优化A B C

  • 小表驱动大表
  • 索引建立在经常查询的字段上

避免索引失效的一些原则

复合索引

  • 复合索引,不要跨列或无序使用(最佳左前缀)

    (a,b,c)

  • 复合索引,尽量使用全索引匹配

    (a,b,c)

不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效

1
select ..where A.x = .. ;  --假设A.x是索引

不要:select …where A.x*3 = … ;

1
2
3
4
5
6
7
explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引

explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引

explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引

explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,原因:对于复合索引,如果左边失效,右侧全部失效。(a,b,c),例如如果 b失效,则b c同时失效。
1
2
3
4
drop index idx_atb on book ; 
alter table book add index idx_authroid (authorid) ;
alter table book add index idx_typeid (typeid) ;
explain select * from book where authorid*2 = 1 and typeid = 2 ;

复合索引不能使用不等于(!= <>)或is null (is not null),否则自身以及右侧所有全部失效。

复合索引中如果有>,则自身和右侧索引全部失效。

1
explain select * from book where authorid = 1 and typeid =2 ;

SQL优化,是一种概率层面的优化。至于是否实际使用了我们的优化,需要通过explain进行推测。

1
2
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;

体验概率情况(< > =):原因是服务层中有SQL优化器,可能会影响我们的优化。

1
2
3
4
5
6
7
8
9
drop index idx_typeid on book;
drop index idx_authroid on book;
alter table book add index idx_book_at (authorid,typeid);
explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用
explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>,则自身和右侧索引全部失效。
explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用
----明显的概率问题---
explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引
explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效

我们学习索引优化 ,是一个大部分情况适用的结论,但由于SQL优化器等原因 该结论不是100%正确。

一般而言, 范围查询(> < in),之后的索引失效。

补救。尽量使用索引覆盖(using index)

(a,b,c)

1
select a,b,c from xx..where a=  .. and b =.. ;

like尽量以“常量”开头,不要以’%'开头,否则索引失效

1
2
3
4
5
6
7
select * from xx where name like '%x%' ; --name索引失效

explain select * from teacher where tname like '%x%'; --tname索引失效

explain select * from teacher where tname like 'x%';

explain select tname from teacher where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询,可以使用索引覆盖 挽救一部分。

尽量不要使用类型转换(显示、隐式),否则索引失效

1
2
explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123',即进行了类型转换,因此索引失效

尽量不要使用or,否则索引失效

1
explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。
1
2
3
4
5
6
7
8
9
10
SELECT count(1)  //不会用到索引
FROM XXX
WHERE 1 = 1 AND
(
(3_id = 12345)
OR
(4_id = 12345)
OR
(2_id = 12345)
);

可以使用in来代替,或者使用union

1
2
3
4
5
6
7
8
select count(1) from 
(
SELECT * FROM XXX WHERE 1 = 1 AND (3_id = 12345)
union
SELECT * FROM XXX WHERE 1 = 1 AND (tk_4th_pub_id = 12345)
union
SELECT * FROM XXX WHERE 1 = 1 AND (tk_pub_id = 12345)
) as a where a.refund_status = XXX;
1
f_id=1 or f_id=100 -> f_id in (1,100)

一些其他的优化方法

exists和in

1
2
select ..from table where exists (子查询) ;
select ..from table where 字段 in (子查询) ;
  • 如果主查询的数据集大,则使用In ,效率高。

  • 如果子查询的数据集大,则使用exist,效率高。

1
2
3
4
#对B查询涉及id,使用索引,故B表效率高,可用大表 -->外小内大
select * from A where exists (select * from B where A.id=B.id);
#对A查询涉及id,使用索引,故A表效率高,可用大表 -->外大内小
select * from A where A.id in (select id from B);
  1. exists是对外表做loop循环,每次loop循环再对内表(子查询)进行查询,那么因为对内表的查询使用的索引(内表效率高,故可用大表),而外表有多大都需要遍历,不可避免(尽量用小表),故内表大的使用exists,可加快效率;
  2. in是把外表和内表做hash连接,先查询内表,再把内表结果与外表匹配,对外表使用索引(外表效率高,可用大表),而内表多大都需要查询,不可避免,故外表大的使用in,可加快效率。
  3. 如果用not in ,则是内外表都全表扫描,无索引,效率低,可考虑使用not exists,也可使用A left join B on A.id=B.id where B.id is null 进行优化。

exists语法: 将主查询的结果,放到子查需结果中进行条件校验(看子查询是否有数据,如果有数据 则校验成功)

如果 复合校验,则保留数据;

1
select tname from teacher where exists (select * from teacher) ; --等价于select tname from teacher
1
select tname from teacher where exists (select * from teacher where tid =9999) ;

in:

1
2
select ..from table where tid in  (1,3,5) ;
select * from where id in (select id from B);

order by 优化

using filesort 有两种算法:双路排序、单路排序 (根据IO的次数)

MySQL4.1之前 默认使用 双路排序;双路:扫描2次磁盘(1**:从磁盘读取排序字段 ,对排序字段进行排序(在buffer中进行的排序) 2:扫描其他字段** )

IO较消耗性能

MySQL4.1之后 默认使用 单路排序只读取一次(全部字段),在buffer中进行排序。但种单路排序 会有一定的隐患 (不一定真的是“单路|1次IO”,有可能多次IO)。原因:如果数据量特别大,则无法 将所有字段的数据 一次性读取完毕,因此 会进行“分片读取、多次读取”。

MySQL主要通过比较所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法,如果max_length_for_sort_data更大,则使用第二种优化后的算法,反之使用第一种算法。

using filesort不一定引起mysql的性能问题。但是如果查询次数非常多,每次在mysql中进行排序,还是会有影响的。

注意:单路排序 比双路排序 会占用更多的buffer。

优化filesort方法:

当无法避免排序操作时,很显然应该尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。

  1. 加大 max_length_for_sort_data 参数的设置

    当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的单路排序,反之,则选择老式的双路排序。所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。

  2. 去掉不必要的返回字段

    当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。

  3. 增大 sort_buffer_size 参数设置

    增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对需要排序的数据进行分段,因为分段会造成 MySQL 使用临时表来进行交换排序。

  4. 复合索引 不要跨列使用 ,避免using filesort

SQL排查 - 慢查询日志

MySQL提供的一种日志记录,用于记录MySQL中响应时间超过阀值的SQL语句 (long_query_time,默认10秒)

慢查询日志默认是关闭的;建议:开发调优是 打开,而 最终部署时关闭。

检查是否开启了 慢查询日志 :

1
show variables like '%slow_query_log%' ;

临时开启:

1
2
3
4
set global slow_query_log = 1 ;  --在内存中开启
exit
service mysql restart
set slow_query_log_file=/var/lib/mysql/localhost-slow.log

永久开启:

/etc/my.cnf 中追加配置:

1
vi /etc/my.cnf 
1
2
3
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值:

1
show variables like '%long_query_time%' ;

临时设置阀值:

1
set global long_query_time = 5 ; --设置完毕后,重新登陆后起效 (不需要重启服务)

永久设置阀值:

/etc/my.cnf 中追加配置:

1
vi /etc/my.cnf 
1
2
[mysqld]
long_query_time=3
1
2
3
4
select sleep(4);
select sleep(5);
select sleep(3);
select sleep(3); --查询超过阀值的SQL: show global status like '%slow_queries%' ;
  1. 慢查询的sql被记录在了日志中,因此可以通过日志 查看具体的慢SQL。

    1
    cat /var/lib/mysql/localhost-slow.log
  2. 通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件 快速查找出需要定位的慢SQL

    1
    mysqldumpslow --help

    s:排序方式

    r:逆序

    l:锁定时间

    g:正则匹配模式

1
2
3
4
5
6
7
8
--获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

--获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

--按照时间排序,前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

语法:

1
mysqldumpslow 各种参数  慢查询日志的文件

分析海量数据

模拟海量数据 存储过程(无return)/存储函数(有return)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create database testdata ;
use testdata
create table dept
(
dno int(5) primary key default 0,
dname varchar(20) not null default '',
loc varchar(30) default ''
)engine=innodb default charset=utf8;

create table emp
(
eid int(5) primary key,
ename varchar(20) not null default '',
job varchar(20) not null default '',
deptno int(5) not null default 0
)engine=innodb default charset=utf8;

通过存储函数 插入海量数据:

创建存储函数:

randstring(6) ->aXiayx 用于模拟员工名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter $ -- 设置定界符,防止以";"造成语义中断
create function randstring(n int) returns varchar(255)
begin
declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
declare return_str varchar(255) default '' ;
declare i int default 0 ;
while i<n
do
set return_str = concat( return_str, substring(all_str, FLOOR(1+rand()*52) ,1) );
set i=i+1 ;
end while ;
return return_str;

end $

如果报错:You have an error in your SQL syntax,说明SQL语句语法有错,需要修改SQL语句;

如果报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

是因为 存储过程/存储函数在创建时 与之前的 开启慢查询日志冲突了

解决冲突:

  • 临时解决( 开启log_bin_trust_function_creators ):

    1
    2
    show variables like '%log_bin_trust_function_creators%';
    set global log_bin_trust_function_creators = 1;
  • 永久解决:

    /etc/my.cnf

    [mysqld]

    1
    log_bin_trust_function_creators = 1

产生随机整数

1
2
3
4
5
6
create function ran_num() returns int(5)
begin
declare i int default 0;
set i =floor( rand()*100 ) ;
return i ;
end $

通过存储过程插入海量数据:emp表中 , 10000, 100000 in代表一个insert参数

1
2
3
4
5
6
7
8
9
10
11
12
create procedure insert_emp( in eid_start int(10),in data_times int(10))
begin
declare i int default 0;
set autocommit = 0 ;

repeat
insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
set i=i+1 ;
until i=data_times
end repeat ;
commit ;
end $

通过存储过程插入海量数据:dept表中

1
2
3
4
5
6
7
8
9
10
11
create procedure insert_dept(in dno_start int(10) ,in data_times int(10))
begin
declare i int default 0;
set autocommit = 0 ;
repeat
insert into dept values(dno_start+i ,randstring(6),randstring(8)) ;
set i=i+1 ;
until i=data_times
end repeat ;
commit ;
end$

插入数据

1
2
3
delimiter ; 
call insert_emp(1000,800000) ;
call insert_dept(10,30) ;

分析海量数据

  1. profiles

    show profiles ; --默认关闭

    show variables like ‘%profiling%’;

    set profiling = on ;

    show profiles :会记录所有profiling打开之后的 全部SQL查询语句所花费的时间。缺点:不够精确,只能看到 总共消费的时间,不能看到各个硬件消费的时间(cpu io )

  2. 精确分析:sql诊断

    show profile all for query 上一步查询的的Query_Id

    show profile cpu,block io for query 上一步查询的的Query_Id

  1. 全局查询日志 :记录开启之后的 全部SQL语句。 (这次全局的记录操作 仅仅在调优、开发过程中打开即可,在最终的部署实施时 一定关闭)

    show variables like ‘%general_log%’;

1
2
3
4
5
6
7
--执行的所有SQL记录在表中
set global general_log = 1 ;--开启全局日志 或者on
set global log_output='table' ; --设置 将全部的SQL 记录在表中

--执行的所有SQL记录在文件中
set global log_output='file' ;
set global general_log_file='/tmp/general.log' ;

开启后,会记录所有SQL : 会被记录 mysql.general_log表中。

1
select * from  mysql.general_log ;

锁机制 :解决因资源共享 而造成的并发问题

示例:买最后一件衣服X

A: X 买 : X加锁 ->试衣服…下单…付款…打包 ->X解锁

B: X 买:发现X已被加锁,等待X解锁, X已售空

分类:

操作类型(表锁):

  • 读锁(共享锁): 对同一个数据(衣服),多个读操作可以同时进行,互不干扰。加锁的会话只能对此表进行读操作,其他会话也只能进行读操作。

  • 写锁(互斥锁): 如果当前写操作没有完毕(买衣服的一系列操作),则无法进行其他的读操作、写操作。当前会话只能对此表进行读,写操作,其他会话无法进行任何操作

操作范围:

  • 表锁 :一次性对一张表整体加锁。如MyISAM存储引擎使用表锁,开销小、加锁快;无死锁;但锁的范围大,容易发生锁冲突,并发度低。在MyISAM中,因为不支持事务,因此只能用lock和unlock控制.InnoDB中,表锁还可以通过事务控制

  • 行锁 :一次性对一条数据加锁。如InnoDB存储引擎使用行锁,开销大,加锁慢;容易出现死锁;锁的范围较小,不易发生锁冲突,并发度高(很小概率 发生高并发问题:脏读、幻读、不可重复度、丢失更新等问题)针对事务(commit和rollbcak)。注意行锁失效和间隙锁,并且行锁还可以查找.

  • 页锁

示例:

表锁

表锁 : --自增操作 MYSQL/SQLSERVER 支持;oracle需要借助于序列来实现自增

1
2
3
4
5
6
7
8
9
10
11
12
13
create table tablelock
(
id int primary key auto_increment ,
name varchar(20)
)engine myisam;


insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');
commit;

增加锁:

1
locak table1  read/write  ,表2  read/write   ,...

查看加锁的表:

1
show open tables ;

会话:session :每一个访问数据的dos命令行、数据库客户端工具 都是一个会话

加读锁:

  • 会话0:

    lock table tablelock read ;

    1
    2
    3
    4
    5
    select * from tablelock; --读(查),可以
    delete from tablelock where id =1 ; --写(增删改),不可以

    select * from emp ; --读,不可以(emp为非锁表)
    delete from emp where eid = 1; --写,不可以

    结论1:

    • 如果某一个会话 对A表加了read锁,则 该会话 可以对A表进行读操作、不能进行写操作; 且 该会话不能对其他表进行读、写操作。
    • 即如果给A表加了读锁,则当前会话只能对A表进行读操作。
  • 会话1(其他会话):

    1
    2
    select * from tablelock;   --读(查),可以
    delete from tablelock where id =1 ; --写,会“等待”会话0将锁释放

    会话1(其他会话):

    1
    2
    3
    select * from emp ;  --读(查),可以 (emp为非锁表)
    delete from emp where eno = 1; --写,可以
    select * from emp ; --读(查)

    结论2:会话0给A表加了锁;其他会话的操作:a.可以对其他表(A表以外的表)进行读、写操作b.对A表:读-可以; 写-需要等待释放锁。

    释放锁: unlock tables ;


加写锁

会话0:

1
lock table tablelock write ;

当前会话(会话0):

  • 可以对加了写锁的表 进行任何操作(增删改查);

  • 但是不能 操作(增删改查)其他表

其他会话:对会话0中加写锁的表 可以进行增删改查的前提是:等待会话0释放写锁


MySQL表级锁的锁模式

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(DML)前,会自动给涉及的表加写锁。

所以对MyISAM表进行操作,会有以下情况:

  • 对MyISAM表的读操作(加读锁),不会阻塞其他进程(会话)对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

  • 对MyISAM表的写操作(加写锁),会阻塞其他进程(会话)对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

分析表锁定:

  • 查看哪些表加了锁: show open tables ; 1代表被加了锁

  • 分析表锁定的严重程度: show status like 'Table_locks%' ;

    Table_locks_immediate :即可能获取到的锁数,即立刻能加锁的表数Table_locks_waited:需要等待的表锁数(如果该值越大,说明存在越大的锁竞争)

一般建议:

Table_locks_immediate/Table_locks_waited > 5000, 建议采用InnoDB引擎,否则MyISAM引擎


行锁(InnoDB)

1
2
3
4
5
6
7
8
9
create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values('1') ;
insert into linelock(name) values('2') ;
insert into linelock(name) values('3') ;
insert into linelock(name) values('4') ;
insert into linelock(name) values('5') ;

mysql默认自动commit; oracle默认不会自动commit ;

为了研究行锁,暂时将自动commit关闭(三种方式); set autocommit =0; 以后需要通过commit

start transaction ;

begin ;

  • 会话0: 写操作

    1
    insert into linelock values(	'a6') ;
  • 会话1: 写操作 同样的数据

    1
    update linelock set name='ax' where id = 6;

对行锁情况:

  1. 如果会话x对某条数据a进行 DML操作(研究时:关闭了自动commit的情况下),则其他会话必须等待会话x结束事务(commit/rollback)后 才能对数据a进行操作。

  2. 表锁 是通过unlock tables ; 在innoDB中也可通过事务控制.行锁 是通过事务解锁。

行锁,操作不同数据:

  • 会话0: 写操作

    1
    insert into linelock values(8,'a8') ;
  • 会话1: 写操作, 不同的数据

    1
    update linelock set name='ax' where id = 5;

    行锁,一次锁一行数据;因此 如果操作的是不同数据,则不干扰。

行锁的注意事项:如果没有索引,则行锁会转为表锁

1
2
show index from linelock ;
alter table linelock add index idx_linelock_name(name);

  • 会话0: 写操作

    1
    update linelock set name = 'ai' where name = '3' ;
  • 会话1: 写操作, 不同的数据

    1
    update linelock set name = 'aiX' where name = '4' ;
  • 会话0: 写操作

    1
    update linelock set name = 'ai' where name = 3 ;
    • 会话1: 写操作, 不同的数据,加了强转,索引失效

      1
      update linelock set name = 'aiX' where name = 4 ;

可以发现,数据被阻塞了(加锁)

原因:如果索引类 发生了类型转换,则索引失效。 因此 此次操作,会从行锁 转为表锁。

行锁的一种特殊情况:间隙锁:值在范围内,但却不存在

此时linelock表中 没有id=7的数据

1
update linelock set name ='x' where id >1 and id<9 ;   --即在此where范围中,没有id=7的数据,则id=7的数据成为间隙。

间隙:Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁(行锁)。

行锁:如果有where,则实际加索的范围 就是where后面的范围(不是实际的值)

如何仅仅是查询数据,能否加锁? 可以 for update

研究学习时,将自动提交关闭,三种方式:

1
2
3
set autocommit =0 ;
start transaction ;
begin ;
1
select * from linelock where id =2 for update ;

通过for update对query语句进行加锁。

行锁:

  • InnoDB默认采用行锁;
  • 缺点: 比表锁性能损耗大。
  • 优点:并发能力强,效率高。
  • 因此建议,高并发用InnoDB,否则用MyISAM。

行锁分析:

show status like ‘Innodb_row_lock%’ ;

  • Innodb_row_lock_current_waits :当前正在等待锁的数量

  • Innodb_row_lock_time:等待总时长。从系统启到现在 一共等待的时间

  • Innodb_row_lock_time_avg :平均等待时长。从系统启到现在平均等待的时间

  • Innodb_row_lock_time_max :最大等待时长。从系统启到现在最大一次等待的时间

  • Innodb_row_lock_waits : 等待次数。从系统启到现在一共等待的次数


-------------本文结束感谢您的阅读-------------
六经蕴籍胸中久,一剑十年磨在手

欢迎关注我的其它发布渠道