SQL优化（三）—— 索引、explain分析

https://www.cnblogs.com/gdwkong/articles/8505125.html

一、什么是索引

• 索引是一种排好序的快速查找的数据结构,它帮助数据库高效的查询数据
• 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法.这种数据结构,就是索引

• 一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中

• 我们平常所说的索引,如果没有特别说明都是指BTree索引(平衡多路搜索树). 其中聚集索引,次要索引,覆盖索引复合索引,前缀索引,唯一索引默认都是使用的BTree索引,统称索引.除了BTree索引之后,还有哈希索引

二、索引优缺点

• 优点:
  • 提高数据查询的效率,降低数据库的IO成本
  • 通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗
• 缺点:
  • 索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间
  • 虽然索引大大提高了查询的速度,同时反向影响增删改操作的效率,因为表中数据变化之后,会导致索引内容不准,所以也需要更新索引表信息,增加了数据库的工作量
  • 随着业务的不断变化,之前建立的索引可能不能满足查询需求,需要消耗我们的时间去更新索引

三、索引的分类

• 单值索引: 一个索引只包含单列,一个表可以有多个单列索引
• 唯一索引:索引列的值必须唯一,但是允许有空值
• 复合索引: 一个索引可以同时包含多列

四、索引结构

1、BTree索引

• 浅蓝色: 磁盘块
• 深蓝色: 数据项
• 黄色: 数据的指针

• 真实的数据仅在叶子节点中: 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90,99

• 查找过程: (假如要找29)

1. 1. 从树根开始,即先把磁盘块1中内容读到内存中, 发生一次IO
   2. 确定29在(17,35)之间,锁定磁盘块1中的P2指针
   3. 根据P2指针,找到磁盘块3, 读取到内存中, 发生二次IO
   4. 29在(26,30)之间,锁定磁盘块3的P2指针
   5. 通过磁盘3的P2指针,将磁盘块8的内容读取到内存中, 发生第三次IO.
   6. 最终找到数据29,查询结束,总共发生三次IO

• 真实的情况:
  • 3层的BTree可以表示上百万的数据,如果上百万条数据,查找只需要三次IO,性能提高将是巨大的,如果没有索引每次查找都要发生一次IO,那么总共就需要百万次的IO,显然成本是非常高的

2、Hash索引

3、full-Text索引

4、R-Tree索引

五、索引的使用

1、创建索引的情况

1. 主键约束默认建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 多表查询中与其它表进行关联的字段,外键关系建立索引
4. 单列索引/复合索引的选择? 高并发下倾向于创建复合索引
5. 查询中经常用来排序的字段
6. 查询中经常用来统计或者分组字段

2、不创建索引的情况

1. 频繁更新的字段: 每次更新都会影响索引树
2. where条件查询中用不到的字段
3. 表记录太少
4. 经常增删改的表: 更新了表,索引也得更新才行
5. 注意: 如果一张表中,重复的记录非常多,为它建立索引就没有太大意义

3、索引创建语法

1 - 创建索引
2 create index 索引名称 on 表名(列名...);
3 
4 - 删除索引
5 drop index 索引名称 on 表名;
6 
7 - 查看索引
8 show index from 表名;

六、性能分析

1、MySql Query Optimizer

mysql中有专门负责优化select语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供mysql认为最优的执行计划

2、Mysql常见的瓶颈

1. CPU饱和:CPU饱和的时候,一般发生在数据装入内存或从磁盘上读取数据的时候
2. IO瓶颈: 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
3. 服务器硬件的性能瓶颈

七、执行计划

　　使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的.我们可以用执行计划来分析查询语句或者表结构的性能瓶颈

1、作用

1. 查看表的读取顺序
2. 查看数据库读取操作的操作类型
3. 查看哪些索引有可能被用到
4. 查看哪些索引真正被用到
5. 查看表之间的引用
6. 查看表中有多少行记录被优化器查询

2、语法

1 explain + sql语句
2 
3 explain select * from tsmall;
4 +----+-------------+---------+------+---------------+------+---------+------+------+-------+
5 | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
6 +----+-------------+---------+------+---------------+------+---------+------+------+-------+
7 |  1 | SIMPLE      | product | ALL  | NULL          | NULL | NULL    | NULL |   16 |       |
8 +----+-------------+---------+------+---------------+------+---------+------+------+-------+

2.1 环境准备

 1 create table t1(
 2   id int primary key,
 3   name varchar(20),
 4   col1 varchar(20),
 5   col2 varchar(20),
 6   col3 varchar(20)
 7 );
 8 create table t2(
 9   id int primary key,
10   name varchar(20),
11    col1 varchar(20),
12    col2 varchar(20),
13   col3 varchar(20)
14 );
15 create table t3(
16   id int primary key,
17   name varchar(20),
18     col1 varchar(20),
19    col2 varchar(20),
20   col3 varchar(20)
21 );
22 insert into t1 values(1,'zs1','col1','col2','col3');
23 insert into t2 values(1,'zs2','col2','col2','col3');
24 insert into t3 values(1,'zs3','col3','col2','col3');
25 
26 create index ind_t1_c1 on t1(col1);
27 create index ind_t2_c1 on t2(col1);
28 create index ind_t3_c1 on t3(col1);
29 
30 create index ind_t1_c12 on t1(col1,col2);
31 create index ind_t2_c12 on t2(col1,col2);
32 create index ind_t3_c12 on t3(col1,col2);

2.2 id

• select 查询的***,包含一组数字,表示查询中执行Select子句或操作表的顺序
• 三种情况:

1. id值相同,执行顺序由上而下

   1 sql
   2  explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id= t3.id and t1.name = 'zs';

   

2. id值不同,id值越大优先级越高,越先被执行

   1 sql
   2  explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name='zs3'));

   

3. id值有相同的也有不同的,如果id相同,从上往下执行,id值越大,优先级越高,越先执行

   1 sql
   2  explain select t2.* from (select t3.id from t3 where t3.name='zs3') s1,t2 where s1.id = t2.id;

2.3 select_type

 查询类型,主要用于区别

• SIMPLE : 简单的select查询,查询中不包含子查询或者UNION

• PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY

• SUBQUERY : 在SELECT或者WHERE列表中包含子查询

• DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中

• UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived

• UNION RESULT: 从union表获取结果的select

1 sql
2   explain select col1,col2 from t1 union select col1,col2 from t2;

2.4 table

　　显示这一行的数据是和哪张表相关

2.5 type

访问类型: all, index,range,ref,eq_ref, const,system,null

最好到最差依次是: system > const > eq_ref>ref >range > index > all , 最好能优化到range级别或则ref级别

• system: 表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现

• const: 通过索引一次查询就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快,如将主键置于where列表中,mysql就会将该查询转换为一个常量

1 sql
2   explain select * from (select * from t1 where id=1) s1;

 

• eq_ref: 唯一性索引扫描, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描

1 sql
2   explain select * from t1,t2 where t1.id = t2.id;

• ref : 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有符合条件的行,然而它可能返回多个符合条件的行

1 sql
2   explain select * from t1 where col1='zs1';

• range : 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描,不需要扫描全部的索引

1 sql
2   explain select * from t1 where id between 1 and 10;

• index : 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据

1 sql
2   explain select id from t1;

• all : full table scan全表扫描 ,将遍历全表以找到匹配的行

1 sql
2   explain select * from t1;

• 注意: 开发中,我们得保证查询至少达到range级别,最好能达到ref以上

2.6 possible_keys

　　SQL查询中可能用到的索引,但查询的过程中不一定真正使用

2.7 key

查询过程中真正使用的索引,如果为null,则表示没有使用索引

查询中使用了覆盖索引,则该索引仅出现在key列表中

1 explain select t2.* from t1,t2,t3 where t1.col1 = ' ' and t1.id = t2.id and t1.id= t3.id;

1 explain select col1 from t1;

2.8 key_len

　　索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好, key_len显示的值为索引字段的最大可能长度,并非实际使用长度, 即key_len是根据表定义计算而得

1 explain select * from t1 where col1='c1';

1 explain select * from t1 where col1='col1' and col2 = 'col2';
2 
3 -- 注意: 为了演示这个结果,我们删除了c1上面的索引
4 alter table t1 drop index ind_t1_c1;
5 -- 执行完成之后,再次创建索引
6 create index ind_t1_c1 on t1(col1);

2.9 ref

　　显示索引的哪一列被使用了,如果可能的话,是一个常数.哪些列或者常量被用于查找索引列上的值

1 explain select * from t1,t2 where t1.col1 = t2.col1 and t1.col2 = 'col2';

2.10 rows

根据表统计信息及索引选用的情况,估算找出所需记录要读取的行数 (有多少行记录被优化器读取)

2.11 extra

包含其它一些非常重要的额外信息

• Using filesort : 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql中无法利用索引完成的排序操作称为文件排序

1 sql
2   explain select col1 from t1 where col1='col1' order by col3;

1 sql
2   -- 上面这条SQL语句出现了using filesort,但是我们去执行下面这条SQL语句的时候它,又不会出现using filesort
3   explain select col1 from t1 where col1='col1' order by col2;

1 sql
2   -- 如何优化第一条SQL语句 ? 
3   create index ind_t1_c13 on t1(col1,col3);
4   explain select col1 from t1 where col1='col1' order by col3;

• Using temporary : 使用了临时表保存中间结果,Mysql在对查询结果排序时使用了临时表,常见于order by 和分组查询group by

1 sql
2   explain select col1 from t1 where col1>'col1' group by col2;

1 sql
2   explain select col1 from t1 where col1 >'col1' group by col1,col2;

• Using index :

• 查询操作中使用了覆盖索引(查询的列和索引列一致),避免访问了表的数据行,效率好

• 如果同时出现了using where, 表明索引被用来执行索引键值的查找
• 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作
• 覆盖索引: 查询的列和索引列一致, 换句话说查询的列要被所键的索引覆盖,就是select中数据列只需从索引中就能读取,不必读取原来的数据行,MySql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件

1 sql
2   explain select col2 from t1 where col1='col1';

1 sql
2   explain select col2 from t1;

• using where : 表明使用了where条件过滤

• using join buffer : 表明使用了连接缓存, join次数太多了可能会出现

• impossible where : where子句中的值总是false,不能用来获取任何数据

1 sql
2   explain select * from t1 where col1='zs' and col1='ls';

• select tables optimized away :

• 在没有group by 子句的情况下, 基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成阶段即完成优化

• distinct : 优化distinct操作,在找到第一个匹配的数据后即停止查找同样的值的动作

3、小练习

1 explain select a1.name,(select id from t3) a2
2 from
3     (select id,name from t1 where name='zs1') a1
4 union
5     select name,id from t2;

执行顺序如下:

1 id=4, select_type为union, union后的select语句先被执行
2 
3 id=3, 因为(select id,name from t1 where name='zs1')被当作一张表处理,所以为select_type 为derived
4 
5 id=2, select_type为SUBQUERY,为select后面的子查询
6 
7 id=1, 表示union中的第一个select, select_type为primary表示该查询为外层查询,table被标记为<derived3>,表示结果来自衍生表
8 
9 id=null,代表从union的临时表中读取行记录, <union1,4>表示将id=1和id=4的结果进行union操作

4、索引优化

4.1 索引分析

单表

 1 create table if not exists article(
 2   id int primary key auto_increment,
 3   author_id int not null,
 4   category_id int not null,
 5   views int not null,
 6   comments int not null,
 7   title varchar(255) not null,
 8   content text not null
 9 );
10 
11 insert into article values(null,1,1,1,1,'1','1');
12 insert into article values(null,2,2,2,2,'2','2');
13 insert into article values(null,1,1,3,3,'3','3');
14 
15 select * from article;
16 
17 -- 查询category_id为1且comments 大于 1 的所有记录,显示id,author_id,views
18 
19 explain select  id,author_id,views  from  article
20 where category_id = 1
21 and comments > 1;

1 -- 查询category_id为1且comments 大于 1 的所有记录,显示id,author_id,views
2 -- 并且找出views查看次数最多的记录
3 explain select  id,author_id,views from  article
4 where category_id = 1
5 and comments > 1
6 order by views desc limit 1;

 1 -- 总结上面出现的情况:type=all,产生了全表扫描, 并且出现了Using filesort,使用了外部的索引排序,所以优化是必须的
 2 
 3 -- 创建索引:
 4 create index ind_article_ccv on article(category_id,comments,views);
 5 
 6 --执行如下指令:
 7 explain select  id,author_id,views from  article
 8 where category_id = 1
 9 and comments > 1
10 order by views desc limit 1;

 1 创建索引之后type=range, 但是Using filesort 依然存在.
 2 
 3 索引创建了,为什么在排序的时候没有生效?
 4 这是因为先排序category_id, 如果遇到相同的category_id,则再排序comments, 如果遇到相同的comments则再排序views,
 5 当comments字段在联合索引处于中间位置时,
 6 因为comments>1条件是一个范围值,所以type=range
 7 mysql无法再利用索引对后面的views部分进行检索,即range类型查询字段后面的索引全都无效
 8 
 9 综上所述: 索引创建有问题
10 
11 -- 删除上面创建的索引:
12 drop index ind_article_ccv on article;
13 -- 重新创建索引: 
14 create index ind_art_cb on article(category_id,views);
15 -- 重新执行如下代码
16 
17 explain select  id,author_id,views from  article
18 where category_id = 1
19 and comments > 1
20 order by views desc limit 1;

两张表

SQL准备

 1 create table if not exists class(
 2     id int(10) primary key auto_increment,
 3       card int not null
 4 );
 5 
 6 create table  if not exists book(
 7     bookid int primary key auto_increment,
 8       card int not null
 9 );
10 
11 insert into class(card) values(floor(1+rand()*20));
12 insert into class(card) values(floor(1+rand()*20));
13 insert into class(card) values(floor(1+rand()*20));
14 insert into class(card) values(floor(1+rand()*20));
15 insert into class(card) values(floor(1+rand()*20));
16 insert into class(card) values(floor(1+rand()*20));
17 insert into class(card) values(floor(1+rand()*20));
18 insert into class(card) values(floor(1+rand()*20));
19 insert into class(card) values(floor(1+rand()*20));
20 insert into class(card) values(floor(1+rand()*20));
21 
22 insert into book(card) values(floor(1+rand()*20));
23 insert into book(card) values(floor(1+rand()*20));
24 insert into book(card) values(floor(1+rand()*20));
25 insert into book(card) values(floor(1+rand()*20));
26 insert into book(card) values(floor(1+rand()*20));
27 insert into book(card) values(floor(1+rand()*20));
28 insert into book(card) values(floor(1+rand()*20));
29 insert into book(card) values(floor(1+rand()*20));
30 insert into book(card) values(floor(1+rand()*20));
31 insert into book(card) values(floor(1+rand()*20));
32 -- 下面开始分析
33 explain select * from class left join book on class.card = book.card;
34 
35 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
36 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
37 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
38 |  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   10 |       |
39 |  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   10 |       |
40 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
41 
42 -- 结论: type 都有all
43 create index X on book(card);
44 
45 -- 分析
46 explain select * from class left join book on class.card = book.card;
47 -- 我们可以看到第二行的type变为了ref,rows也变小了,优化效果明显

1 -- 这是由左连接特型决定的.left join条件用于确定如何从右表搜索行,左边一定都有,所有右边一定要建立索引
2 
3 -- 我们再来看一个右外连接
4 explain select * from class right join book on class.card = book.card;

三张表

SQL准备

 1 create table if not exists phone(
 2     phoneid int primary key auto_increment,
 3       card int
 4 );
 5 
 6 insert into phone(card) values(floor(1+rand()*20));
 7 insert into phone(card) values(floor(1+rand()*20));
 8 insert into phone(card) values(floor(1+rand()*20));
 9 insert into phone(card) values(floor(1+rand()*20));
10 insert into phone(card) values(floor(1+rand()*20));
11 insert into phone(card) values(floor(1+rand()*20));
12 insert into phone(card) values(floor(1+rand()*20));
13 insert into phone(card) values(floor(1+rand()*20));
14 insert into phone(card) values(floor(1+rand()*20));
15 insert into phone(card) values(floor(1+rand()*20));
16 
17 -- 在没有添加索引的情况下执行如下语句
18 explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
19 
20 -- 创建索引:
21 create index Y on book(card);
22 create index Z on phone(card);

1 -- 后2行的type都是ref且总rows数量大大降低了,效果不错,因此索引最好是设置在需要经常查询的字段中
2 
3 -- 结论:
4 尽可能减少join语句中的循环总次数: 永远用小结果集驱动大的结果集;
5 优先优化内层循环
6 保证join语句中被驱动表上的join条件字段已经创建过索引
7 当无法保证被驱动表的join条件字段是否有索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置

4.2 索引失效

建表语句

 1 create table emp(
 2     empno int primary key auto_increment,
 3     ename varchar(24) not null default '' comment '姓名',
 4       age int not null default 0 comment '年龄',
 5       job varchar(20) not null default '' comment '职位',
 6       hiredate timestamp not null default current_timestamp comment '入职时间'
 7 );
 8 
 9 insert into emp(ename,age,job,hiredate) values('zs',22,'manager',now());
10 insert into emp(ename,age,job,hiredate) values('ls',23,'dev',now());
11 insert into emp(ename,age,job,hiredate) values('1000',24,'manager',now());
12 insert into emp(ename,age,job,hiredate) values('zss',22,'manager',now());
13 insert into emp(ename,age,job,hiredate) values('xzss',22,'manager',now());
14 
15 select * from emp;
16 
17 create index ind_emp_naj on emp(ename,age,job);

1. 全值匹配我最爱: 查询的条件列刚好和索引创建的列数量和顺序相同

 1 ```sql
 2 explain select * from emp where ename=‘zs’;
 3 explain select * from emp where ename=‘zs’ and age=22;
 4 explain select * from emp where ename=‘zs’ and age=22 and job=‘manager’;
 5 
 6   -- 有问题的sql
 7 explain select * from emp where age=22 and job=‘manager’;
 8 explain select * from emp where job=‘manager’;
 9 – 没有问题的sql
10 explain select * from emp where ename=‘zs’;
11 ```

　　2. 最佳左前缀法则: 如果索引了多列,要遵循最左前缀法则. 查询从索引的最左列开始并且不跳过索引中的列

1 ```sql
2 explain select * from emp where ename=‘zs’ and job=‘manager’;
3 
4 explain select * from emp where job=‘manager’;
5 
6 explain select * from emp where ename=‘zs’;
7 ```

　　3.不在索引上做任何操作(计算,函数,(自动/手动)类型转换,这样会导致索引失效而转向全表扫描

1 sql
2    explain select * from emp where left(ename,2)='zs';

　　4.存储引擎不能使用索引中范围条件右边的列

1 ```sql
2 – 使用到了索引
3 explain select * from emp where ename=‘zs’;
4 explain select * from emp where ename=‘zs’ and age=22;
5 explain select * from emp where ename=‘zs’ and age=22 and job=‘manager’;
6 
7 explain select * from emp where ename=‘zs’ and age>22 and job=‘manager’;
8 ```

　　5.尽量使用覆盖索引(要查询的列和索引中的列一致),避免使用select *

1 sql
2    explain select * from emp where ename='zs' and age=22 and job='manager';
3    explain select ename,age,job from emp where ename='zs' and age=22 and job='manager';
4    explain select ename,age,job from emp where ename='zs' and age>22 and job='manager';
5    explain select ename,age,job from emp where ename='zs' and age=22;

　　6.mysql在使用不等于条件判断的时候,索引会失效引发全表扫描

1 sql
2    explain select * from emp where ename='zs';
3    explain select * from emp where ename != 'zs';

　　7.is null, is not null 索引会失效,无法使用索引

1 sql
2    explain select * from emp where ename is null;
3    explain select * from emp where ename is not null;​

　　7.like以通配符开头(‘%xxx…’)索引会失效,导致全表扫描

 1 ```sql
 2 explain select * from emp where ename like ‘%zs%’;
 3 explain select * from emp where ename like ‘%zs’;
 4 explain select * from emp where ename like ‘zs%’;
 5 
 6 问题: 解决like ‘%xxx%’ 时索引失效的问题
 7 explain select ename from emp where ename like ‘%zs%’;
 8 
 9 explain select ename,age from emp where ename like ‘%zs%’;
10 explain select ename,age,job from emp where ename like ‘%zs%’;
11 explain select ename,age,job,hiredate from emp where ename like ‘%zs%’;
12 ```

　　8.字符串不加单引号索引失效

1 sql
2    explain select * from emp where ename=1000;
3    explain select * from emp where ename='1000';

　　9.少用or,它会导致索引失效

1 explain select * from emp where ename='zs' or ename='ls';

　　10.小总结

 

4.3 面试题分析

 1 create table test3(
 2     id int primary key auto_increment,
 3       c1 char(10),
 4       c2 char(10),
 5       c3 char(10),
 6       c4 char(10),
 7       c5 char(10)
 8 );
 9 
10 insert into test3 values(null,'a1','a2','a3','a4','a5');
11 insert into test3 values(null,'b1','b2','b3','b4','b5');
12 insert into test3 values(null,'c1','c2','c3','c4','c5');
13 insert into test3 values(null,'d1','d2','d3','d4','d5');
14 insert into test3 values(null,'e1','e2','e3','e4','e5');
15 
16 select * from test3;
17 
18 -- 创建索引
19 create index ind_test3_c1234 on test3(c1,c2,c3,c4);
20 show index from test3;
21 
22 explain select * from test3 where c1='a1';
23 explain select * from test3 where c1='a1' and c2='a2';
24 explain select * from test3 where c1='a1' and c2='a2' and c3='a3';
25 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
26 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4' and c5='a5';
27 
28 -- 请执行如下问题SQL,分析会出现的问题
29 -- (1)
30 explain select * from test3 where c1='a1' and c2='a2' and c4='a4' and c3='a3' ;
31 -- (2)
32 explain select * from test3 where c4='a1' and c3='a2' and c2='a4' and c1='a3' ;
33 -- (3)
34 explain select * from test3 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
35 -- (4)
36 explain select * from test3 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
37 -- (5)
38 explain select * from test3 where c1='a1' and c2='a2' and c4='a4' order by c3; 
39 -- (6)
40 explain select * from test3 where c1='a1' and c2='a2' order by c3; 
41 -- (7)
42 explain select * from test3 where c1='a1' and c2='a2' order by c4; 
43 -- (8)
44 explain select * from test3 where c1='a1' and c5='a5' order by c2,c3; 
45 只用了c1这个字段索引,但是c2,c3用于排序,无filesort
46 explain select * from test3 where c1='a1' and c5='a5' order by c3,c2; 
47 只用了c1这个字段索引,但是由于c3,c2顺序颠倒了,所以无法使用索引排序,出现filesort
48 
49 -- (9)
50 explain select * from test3 where c1='a1' and c2='a2' order by c2,c3; 
51 -- (10)
52 explain select * from test3 where c1='a1' and c2='a2' and c5='a5' order by c2,c3; 
53 用到了c1,c2两个字段索引,但是c2,c3用于排序,无filesort
54 explain select * from test3 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; 
55 排序字段已经是一个常量,所以不会出现filesort
56 
57 -- (11)
58 explain select * from test3 where c1='a1' and c4='c4' group by c2,c3;
59 用到了c1字段索引
60 explain select * from test3 where c1='a1' and c4='c4' group by c3,c2;
61 索引字段顺序不正确，出现了Using temporary; Using filesort

　　建议

• 1. 对于单列索引,尽量选择针对当前查询过滤性更好的索引
  2. 在选择复合索引的时候,当前查询中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  3. 在创建复合索引的时候,尽量选择能够包含查询中where子句中更多的字段
  4. 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的

4.4 in和exists

优化规则: 小表驱动大表,即小的数据集驱动大的数据集

 1 -- 当A的数据集大于B的数据集时, in 优于 exists
 2 select * from A where id in (select id from B);
 3 等价于:
 4     for select id from B
 5         for select * from A where A.id = B.id;
 6 
 7 -- 当A的数据集小于B的数据集时, exists优于in
 8 select * from A where exists(select 1 from B where B.id = A.id);
 9 等价于:
10     for select * from A
11         for select * from B where B.id = A.id

4.5 Order by 排序优化

1. order by 子句,尽量使用index方式排序,避免使用Filesort方式排序

1 sql
2    select * from emp order by age
3    select * from emp order by age,birth
4    select * from emp order by birth,age
5    select * from emp order by age asc,birth desc;

尽可能再索引列上完成排序操作,遵照索引建的最佳左前缀

1. 如果不在索引列上,filesort算法:双路排序,单路排序

2. 双路排序: mysql4.1之前使用的双路排序,字面意思就是扫描两次磁盘,从而得到最终的数据,读取行指针和order by 列,对它们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出. 从磁盘取排序字段,在buffer进行排序,再从磁盘取其它字段

3. 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存到内存中
4. 结论:
   1. 单路排序,优于双路排序
   2. 在sort_buffer中,单路要比双路占用更多的空间,因为单路是把所有的字段都取出来,有可能导致取出的数据总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp,多路合并),排完再取sort_buffer容量大小,再排…从而导致了多次IO. (本来想节省一次IO操作,反而导致了大量的IO操作,得不偿失)

5. 优化策略

6. 增大sort_buffer_size参数设置

7. 增大max_length_for_sort_data参数设置
8. why
   1. order by时,select * 时一个大忌,只查询需要的字段,这点非常重要.
      1. 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用单路排序,否则会用多路排序
      2. 两种算法的数据都有可能超过sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
   2. 尝试调高sort_buffer_size: 不管使用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去调高,因为这个参数是针对每个进程的
   3. 尝试调高max_length_for_sort_data: 调高这个参数,会增加使用单路算法的概率,但是如果设置太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率

9. 总结

 1 ```sql
 2 mysql有两种排序方式: 文件排序和扫描有序索引排序
 3 mysql能为排序和查询使用相同的索引
 4 
 5 index abc(a,b,c)
 6 order by 能使用最左前缀
 7 order by a
 8 order by b
 9 order by a,b,c
10 order by a desc,b desc,c desc
11 
12 如果where使用索引的最前缀定义为常量,则order by能使用索引
13 where a=const order by b,c
14 where a=const and b = const order by c
15 where a=const order by b,c
16 where a=const and b > const order by b,c
17 
18 不能使用索引进行排序
19 order by a asc,b desc, c desc /*排序顺序不一致*/
20 where g=const order by b,c /*丢失a索引*/
21 where a=const order by c /*丢失b索引*/
22 where a=const order by a,d /*d不是索引*/
23 where a in(…) order by b,c /*对于排序来说,in 相当于是范围查询*/
24 ```

标签: