Mysql、(四) 索引优化

---

一、 查看SQL执行频率

命令： show status like 'Com______' 查看本次连接的增删改查语句执行次数；
show global status like 'Com______' 查看子数据库上次启动至今的执行次数；
show global status like 'Innodb_rows_%' ：针对Innodb 数据库的查询；

可以通过查看全局的SQL操作来确定优化哪一部分。

二、 定位低效率执行SQL

区别在于 show processlist 可以查看实时的SQL执行速度；慢查询日志必须等到查询完后才能。

1. show processlist:

各个列的含义：

2. 慢查询日志

三、SQL中JOIN

1. JOIN的执行顺序
   join语句过多过复杂也会影响SQL执行的性能。
   一般人为手写JOIN的顺序如下：
   
   而机读的顺序：

2. 常用的七种join
   

左联： select <select_list> from Table A left join Table B on A.key=B.key

右联： select <select_list> from Table A right join Table B on A.key=B.key

内联： select <select_list> from Table A inner join Table B on A.key=B.key

左独占： select <select_list> from Table A left join Table B on A.key=B.key where B.key is NULL

右独占： select <select_list> from Table B left join Table A on A.key=B.key where A.key is NULL

全连接： select <select_list> from Table A full outer join Table B on A.key=B.key
注意！ Mysql中没有full语法，只能用在Oracle中，但是可以改为A独有+共有+B独有，通过union实现，union可以做到结合+去重。

select <select_list> from Table A left join Table B on A.key=B.key
union 
select <select_list> from Table A right join TableB on A.key=B.key;

中空： select <select_list> from Table A full outer join Table B on A.key=B.key where A.key is NULL and B.key is NULL
同理Mysql中：

select <select_list> from Table A left join Table B on A.key=B.key where B.key is NULL
union
select <select_list> from Table A right join Table B on A.key=B.key where A.key is NULL;

四、explain 性能分析

1. Mysql Query Optimizer: Mysql中对SELECT 语句的性能优化器。
   
2. MySQL常见瓶颈：
   
3. SQL Explain：
   

• 如何使用？ Explain + SQL语句
• 各个字段的含义？

id：数据表的执行顺序；id相同执行顺序由上而下，id不同越大优先级越高越优先被执行；
select_type：数据读取操(SELECT)的操作类型；

更详细可见： mysql 查询优化 ~explain解读之select_type的解读
关于 DERIVED(from中的子查询的解读)：子查询三(在FROM子句中使用子查询)

table：这行数据关于哪张表； derived 表示数据来自衍生表，union表示数据来自联合。

type：访问类型；显示查询使用了何种类型。
从最好到最差顺序为： system>const>eq_ref>ref>range>index>ALL

eq_ref：对应的查找数据库中只有一条数据能够和它对应上；

ref：会找到多个符合条件的行。


possible_keys：显示可能用在这张表上的索引。

key：实际使用的索引。如果为NULL则没有使用索引。
查询中如果使用了复合索引，那么只会出现在key列表中，possible_keys中没有。
覆盖索引：查询的字段和建立的复合索引的顺序和个数完全一致。

key_len：索引字段的最大可能长度，并非实际使用长度。在使用时在不损失精确性的情况下，长度越短越好。

ref：哪些列或常量被用于查找索引列上的值。

rows：根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数。

fitlered：表示选取行和读取行的百分比，80表示读取了80%。

Extra：

Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化）
Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化）。
Using index：表示select语句中使用了覆盖索引，直接冲索引中取值，而不需要回表（从磁盘中取数据）。
Using where：使用了where过滤。
Using index condition：表示使用的是二级索引，涉及到了回表查询。
Using join buffer：使用了连接缓存。
impossible where：where子句的值总是false。

详细参考：mysql中explain详解

• 练习：
  

五、索引优化

1) 单表索引优化

1. 在某一个时间字段加索引，短的时间范围内查询，索引生效，为range。长时间范围，索引失效，查全表。当索引查的数据量超过全表30%的数据，索引失效，会查全表.

2. 范围索引一般放在最后，不要放在索引的中间，这样会降低效率。
   
   问题： 查找 category_id等于1并且 comments大于1并且views 最多的那一篇文章。
   SQL语句如下：select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
   如果此时建立索引①为：create index idx_ccv on article(category_id,comments,views);
   查找的结果为：
   
   虽然type变为了 range，可以接收。但是 extra里面使用 Using filesort 是不可行的。

如果此时建立索引②为：create index idx_ccv on article(category_id,views,comments);
查找结果为：

此时 type为ref，extra为 Using where，证明使用了where进行过滤，可以接受。

原因：如果按照索引①的方式，那么会先排序 category_id，当遇到相同的 category_id时则再会排序 comments，如果遇到相同的comments则再会排序 views。但是 comments>1 条件是一个范围值查询。联想索引的结构B+树，当它在范围中查询时就和索引的使用没有太大关系，可以认为范围查询(range)后面的索引无效。再根据索引的最左匹配原则，如果comments索引字段无效就不能够匹配后面的view索引字段。

2) 两表索引优化

1. 左联(left join) 需要对右表建立索引：

• 原查询
  
• 对 book 建立索引
  
• 对class建立索引
  
  这里从表的 type类型变为了 ref，说明它只是部分扫描。而且两个表的rows总和远远小于第一种。
  这时候有人就会问了，但是这时book表的type为 ALL啊。没错，但是你想既然是左连接那么左边表的全部数据都要访问一遍，此时加上索引获取的优化并不大，相反右表只是一部分需要访问，如果加上了索引那么优化的空间就很大了。

结论：join查询给对立的表加上索引。

3) 三表索引优化

如图三个表join 联查发现 type均为空，急需优化。
类比两表优化，我们对 class和phone 两个表的card字段建立索引：

六、索引失效

6.1 索引失效的原因

对于第8种情况可以通过使用 覆盖索引的方式来解决。
详细用例参考：Mysql索引：图文并茂，深入探究索引的原理和使用

七、覆盖索引优化场景

覆盖索引：SQL语句中需要查询的字段都是索引序列中包含的字段(SQL语句中不能出现索引列中没有的)。此时会优先采用覆盖索引进行查找，如果此时满足覆盖索引的条件，我们需要额外关注 order by 这些字段中的排序顺序，这些字段如果不是按照索引的顺序进行排序则之前建立的索引也会失效。

这里我按照 name,age 的顺序建立了索引，虽然满足覆盖索引的条件，但是由于order by 排序是先按照age后按照name，与B+树的结构相冲突。

7.1 无Where 条件的查询优化

普通查询的 type 类型为ALL。
建立索引： alter table t1 add key(staff_id);

# 结果：
explain select sql_no_cache count(staff_id) from t1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: staff_id
      key_len: 1
          ref: NULL
         rows: 1023849
　　　　　 Extra: Using index

1 row in set (0.00 sec)

当没有 WHERE 条件时查询优化器无法优化，这里使用了覆盖索引来降低查询次数，前提是查询返回的字段足够少。

7.2 二次检索优化

select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)

explain select sql_no_cache rental_date from t1 where inventory_id<80000*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id
          key: inventory_id
      key_len: 3
          ref: NULL
         rows: 153734
        Extra: Using index condition
1 row in set (0.00 sec)

八、索引面试题

MySQL中的优化器(Optimiser) 能够对用户的SQL语句自动进行优化调整。当输入的索引顺序混乱时能够调整，但总体上还是遵循最左匹配原则。

# 建表语句
create table if not exists `test03`(
   `id` int primary key not null auto_increment,
   `c1` char(10),
   `c2` char(10),
   `c3` char(10),
   `c4` char(10),
   `c5` char(10)
);


insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

# 建立索引
create index idx_c1234 on test03(c1,c2,c3,c4);

8.1 where字段中的顺序不符合索引顺序

• 全值匹配查询时（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序 ）；

8.2 范围索引range

可以联想 复合索引的B+树结构，它是先按照c1排序，如果c1相等再按照c2排序…
因此c4的查找是建立在c3的基础上的，而c3此时是范围查询不能使用索引进行定位，索引c4也就没有了索引查找。
如图：

8.3 order by排序

order by 只涉及到了排序并没有进行索引查找。
这里按照c1、c2进行了查找，而到c3进行的是排序。但是已经基于c1、c2、c3、c4建立了索引同时也进行了排序，所以 order by c3 可以看作是多余的。那么c4就不能使用索引了。

这里越过c3直接按照c4排序，不符合原本建立索引时的B+树的结构。MySQL内部会自动调整，去另外构建一个索引，因此Extra字段包含了 Using filesort。

这种排序方式也改变了B+树的数据结构，因此为 Using filesort。

标红的地方不一样，但是结果却一样的。
第二个SELECT 语句中先是按照c1、c2进行了索引，注意此时的c2已经是定值了，那后面的 order by c3,c2就相当于只对c3进行排序。这符合索引的规则，能够使用。

8.4 group by分组

group by 和 order by 类似，都需要进行排序。

改变了排序规则，不符合B+树结构，此时会有临时表产生。

8.5 索引优点、缺点

1. 优点

• 通过创建唯一索引可以保证数据库表中每一行的数据的唯一性；
• 可以大大加快数据查找效率。
• 在实现数据的参考完整性方面可以加速表与表之间的连接；
• 在使用分组和排序子句进行数据查询时能够减少分组和排序的时间；

2. 缺点

• 创建和维护索引需要耗费时间，并且随着数据量的增加也会随之增加；
• 索引也是一个数据表，过多的索引会占用过多的内存；
• 对表中的数据进行增加、删除、修改时，索引也需要进行动态维护，这样就降低了数据的维护速度。

8.6 索引分类

索引可以分为 聚簇索引和非聚簇索引。
细分如下：

• 普通索引：普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
• 唯一索引：唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。例如在student 表中的 id 字段上建立名为 index_id 的索引CREATE UNIQUE INDEX index_id ON tb_student(id);
• 主键索引：主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
• 空间索引：空间索引是对空间数据类型的字段建立的索引，空间索引主要用于地理空间数据类型 ，很少用到。
• 全文索引：全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。

8.7 SQL优化步骤

1. 慢查询的开启并捕获；
2. explain + 慢SQL分析；
3. show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况；
4. SQL数据库服务器的参数调优。

8.8 小表驱动大表[exists与in的选择]：

优化原则：小表驱动大表，小的数据集驱动大的数据集；

select * from A where id in (select id from B)
等价于：
select id from B
select * from A where A.id=B.id

in() 子查询先产生结果集，然后主查询再去结果集中去找符合要求的字段列表。符合要求的就输出，反之不输出。
in 适用于外表大内表小的情况。

select * from A where exists (select 1 from B where B.id=A.id)
等价于：
select * from A
select * from B where B.id=A.id

exists(xxxxx) 后面的子查询被称作相关子查询，它是不返回列表值的。它只是返回一个true 或者 false的布尔值（也就是说可以select 任何东西），如果返回true那么这一行就能够作为外部的结果行，反之false不可以。他只在乎括号里的东西能不能查出来，是否存在这样的记录；

执行顺序如下：

1. 首先执行一次外部查询；
2. 对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询都会引用外部查询中当前行的值；
3. 使用子查询的结果集来确定外部查询的结果集。
   exists 适用于外表小而内表大的情况。
   
   
   
   

select * from class where cid not in(select distinct cid from stu)

注意！！！ 这里存在一个Bug，由于not in 不会对空值进行处理，那么后面子句中查询的空值对应的这行会被忽视(这一行本来是可行的，因为stu表中对应的cid为空，而class中的不为空)。

解决方法：select * from class where cid not in(select distinct cid from stu where cid is not null);

not in是在表中一条记录一条记录的查询(查询每条记录）符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完。也就是说为了证明找不到，所以只能查询全部记录才能证明。
使用了not in就会对内外表进行全表扫描，没有用到索引。
not in 适用于外表大而内表小的情况。

select * from class where not exists(select * from stu where stu.id=class.id)

使用存在量词not exists后，若对应查询结果为空，则外层的WHERE子语句返回值为真值，否则返回假值。
not exists 是根据索引去查询的，如果存在就返回true，不存在就返回false，不会每条记录都去查询。
not exists 适用于外表小，内表大的情况。

exists、in、not exists、not in实例：

create table student(
   `id` int(8) primary key,
   `name` varchar(20),
   `deptment` int(8));

create table if not exists `select_course`(
   `id` int(8) primary key,
   `student_id` int(8),
   `course_id` int(8),
    foreign key(`course_id`) references course(`id`),
    foreign key(`student_id`) references student(`id`)
);

create table if not exists `course`(
   `id` int(8) not null,
   `c_name` varchar(20),
   `c_no` varchar(10)
);

insert into student(id,name,deptment) values(1,'echo',1000);
insert into student(id,name,deptment) values(2,'spring',2000);
insert into student(id,name,deptment) values(3,'smith',1000);
insert into student(id,name,deptment) values(4,'liter',2000);

insert into course(id,c_name,c_no) values(1,'数据库','data1');
insert into course(id,c_name,c_no) values(2,'数学','month1');
insert into course(id,c_name,c_no) values(3,'英语','english1');

insert into select_course(id,student_id,course_id) values(1,1,1);
insert into select_course(id,student_id,course_id) values(2,1,2);
insert into select_course(id,student_id,course_id) values(3,1,3);
insert into select_course(id,student_id,course_id) values(4,2,1);
insert into select_course(id,student_id,course_id) values(5,2,2);
insert into select_course(id,student_id,course_id) values(6,3,2);

1. 问题：查询选修了所有课程的学生信息
   分析：反过来思考，只要从全体学生中去除掉存在课没选的学生就可以了。

SQL:
    select * from student t where not exists
       (select * from course c where not exists
           (select * from select_course sc where t.id=sc.student_id and c.id=sc.course_id));

只要一门课没有选，那么 select * from select_course sc where t.id=sc.student_id and c.id=sc.course_id 对应的结果就为null，根据 not exists 的规则此时返回true，结果是那些有课没有选的学生的ID。接着最外面的 not exists 发现结果不为null，就返回false，那么对应的这行数据都不用再查询了，跳过查询下一条。

2. 问题：查询没有全部选课的学生信息

SQL:
    select * from student t where exists
       (select * from course c where not exists
           (select * from select_course sc where t.id=sc.student_id and c.id=sc.course_id));

此时就很简单了，将最外层的not exists 改为 exists 即可。此时子查询非空，返回的是 true，刚刚好说明这行的数据是有效的。

3. 问题：查询一门课也没有选的学生

select * from student where not exists(select 1 from select_course where student.id=select_course.student_id);

此时只需要根据 select_course 和 student 两张表即可判断。

4. 问题：查询至少选修了一门课程的学生

select * from student where exists(select 1 from select_course where student.id=select_course.student_id);

8.9 Order By 优化

当对 sql 进行order by 排序时，应该尽量使用索引排序。如果无法使用索引排序就会出现 Using filesort 的情况，此时 MySQL会使用文件排序。

1) 出现 Using filesort的情况

1. order by 字段不是索引字段；
2. order by 字段是索引字段，但是顺序被打乱，不是按照最左前缀法则；
3. 跳过了联合索引的中间值。

#索引   idx_name_age_position
 explain select * from employees where name='xxx' order by position;

4. order by 中存在 ASC、DESC不一致的字段。
5. 范围查询，多个相等条件也是范围查询(in)。
   explain select * from employees where a in (1,2,3) order by b,c;

如果SQL查询中不在索引列上，filesort有两种排序算法：双路排序和单路排序。

2) Using filesort实现原理

filesort 使用相关的排序算法，将取得的数据在 sort_buffer_size 系统变量设置的内存排序区中进行排序，如果内存装不下，会将磁盘上的数据进行分块，然后再对每一个数据块进行排序，最后合并所有的数据块组成一个有序集。
参数查看： show variables like '%max_length_for_sort_data%';

2) 双路排序

MySQL 4.1 之前使用双路排序，字面意思就是两次扫描磁盘。

1. 从索引的字段中，查询到满足条件的数据对应行的主键id；
2. 然后根据这个id，获取到排序的字段和id并缓存到 sort buffer中；
3. 重复执行1,2 步骤；
4. 对 sort buffer 中的数据进行排序；
5. 根据排序好的主键id和其位置，从原表中根据id查询数据返回给客户端(回表的过程)。

特点：

• sort buffer只存储主键和排序字段在内存中，在缓冲区中按照排序字段排序，最后做一次回表根据主键id查询到改行数据返回给客户端。

3) 单路排序

1. 从索引的字段中，查询到满足条件的数据对应行的主键id；
2. 然后根据这个id，取出整行数据缓存到 sort buffer中；
3. 重复执行1,2 步骤；
4. 对 sort buffer 中的数据进行排序并返回给客户端。

特点：

• sort buffer 中缓存的是整行的数据，根据排序字段排序后直接返回给客户端。
• 缺点：占用内存；如果查询字段过长，会导致不止一次的排序。
• 优点：读取数据时随机IO变成了顺序IO，同时避免了回表查询，速度更快。

4) 单路排序和多路排序的选择

MySQL可以通过 show variables like '%max_length_for_sort_data%' 来查询缓冲区大小，默认是 1024个字节。

• 如果查询字段的总长度比设定的缓冲区大小大，使用多路排序；
• 如果查询字段的总长度比设定的缓冲区大小小，使用单路排序；

5) 提高 ORDER BY查询速度

1. 尽量少用 select * 这种语句，理由上面解释过了；
2. 尝试提高 sort_buffer_size；
3. 增大参数 max_length_for_sort_data；

8.10 Group By优化

group by 和 order by 几乎类似，只有以下几点不同。

• group by 是先排序后分组，遵循索引建立的最左前缀；
• where优于 having，能写在where限定的条件就不要留到 having中；

参考文章：
Mysql索引：图文并茂，深入探究索引的原理和使用

MySQL中EXISTS的用法

SQL 子查询 EXISTS 和 NOT EXISTS

sql中的in与not in,exists与not exists的区别

order by中的单路和双路排序算法原理

九、慢查询日志

慢查询日志：用于记录查询时间超过阈值的语句。具体指运行时间超过long_query_time 的SQL，会被记录到慢查询日志中。

1. 默认没有开启，查看是否开启：
   show variables like '%slow_query_log%';
   

2. 开启方法：
   临时生效： set global slow_query_log = 1;
   永久生效： 修改 my.cnf 文件(linux下为 my.ini)，添加如下两行数据

slow_query_log = 1
slow_query_log_file = 日志文件存储位置

3. 查看修改等待时间
   show variables like 'long_query_time%';

set global long_query_time = 修改后的值;

4. mysqldumpslow 分析工具