mysql8.0索引优化与查询优化(二)

4、子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。
子查询可以一次性完成很多逻辑上需要多个步骤才能完成的操作 。

子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。 
通常我们可以将其优化成一个连接查询~

原因:
① 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。
  查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。 连接查询 不需要建立临时表，其 速度比子查询要快，如果查询中使用索引的话，性能就会更好。

举例1：查询学生表中是班长的学生信息
使用子查询
#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询班长的信息
EXPLAIN SELECT * FROM student stu1
    WHERE stu1.`stuno` IN (
    SELECT monitor
    FROM class c
    WHERE monitor IS NOT NULL
);

推荐：使用多表查询
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.`stuno` = c.`monitor` WHERE c.`monitor` IS NOT NULL;

举例2：取所有不为班长的同学
不推荐
#查询不为班长的学生信息
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
            SELECT monitor FROM class b 
            WHERE monitor IS NOT NULL);
推荐
# 转换成左连接查询
EXPLAIN SELECT SQL_NO_CACHE a.* FROM  student a LEFT OUTER JOIN class b ON a.stuno =b.monitor WHERE b.monitor IS NULL;

结论：尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

5、排序优化

5.1、排序优化

问题：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢？
在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

Index排序中，索引可以保证数据的有序性，就不需要再进行排序，效率更更高。
FileSort排序则一般在 内存中进行排序，占用CPU较多。如果待排序的结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率低。
优化建议:
1、SQL 中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中 避免全表扫描，在ORDER BY子句 避免使用 FileSort 排序。
   当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2、尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
3、无法使用Index时，需要对FileSort方式进行调优。

5.2、测试

执先案例前，调用存储过程删除student和class表上的索引。只留主键：
call proc_drop_index('atguigudb2','student');
call proc_drop_index('atguigudb2','class');

以下是否能使用索引，能否去掉 using filesort
过程一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程二：
1. 创建索引，但是不加limit限制，索引失效
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);
#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

我们不是建立了索引嘛，为啥显示没有使用呢？这是优化器通过计算发现，这里需要回表的数据量特别大，使用索引的性能代价反而比不上不用索引的。

2. 假如我们最后只查询二级索引中有的字段，观察结果：
# 会使用索引 （覆盖索引）
EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;  

3. 假如我们限制排序返回的结果数量，观察结果：
#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程三：order by 时顺序错误，索引失效
#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 
#以下哪些索引失效?
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;  
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; 
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

过程四：order by 时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）
# 失效
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;
# 失效
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 
# 可以
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

过程五：无过滤，不索引
# 可以
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;
# 可以
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid,NAME; 
# 失效
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age;
# 可以
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10;
CREATE INDEX idx_cid ON student(classid);
# 可以
EXPLAIN  SELECT * FROM student WHERE classid=45 ORDER BY age;

小结：
INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀 - ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果 WHERE 使用索引的最左前缀定义为常量，则 order by 能使用索引 
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */ 
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

5.3、案例实战

下面我们通过一个案例来实战filesort和index两种排序。对ORDER BY子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。
场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序
执行案例前先清除student上的索引，只留主键：
DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index('atguigudb2','student');

测试以下的查询，此时显然使用的是filesort进行排序
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort，也是最坏的情况。优化是必须的。

方案一：为了去掉 filesort 我们可以创建特定索引
# 创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);
# 进行测试，可以看到已经使用了索引，虽然仅仅使用到了age这个字段
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY NAME ;

方案二：尽量让 where 的过滤条件和排序使用上索引
DROP INDEX idx_age_name ON student;
# 建立三个字段的索引
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
# 进行测试
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

此时又使用了filesort，这是为什么呢？
这是因为此时filesort的性能更高。不信你可以对比执行下（0.03sec和0.00sec），看看时间的区别。
结果竟然有 filesort 的 sql 运行速度，超过了已经优化掉 filesort的 sql，而且快了很多，几乎一瞬间就出现了结果。
看来优化器做的工作真的特别灵活

原因：所有的排序都是在条件过滤之后才执行的。
所以，如果条件过滤大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。
相对的 stuno < 10100 这个条件，如果没有用到索引的话，要对几万条数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论:
两个索引同时存在，mysql 自动选择最优的方案。（对于这个例子，mysql 选择 idx_age_stuno_name）。但是，随着数据量的变化，选择的索引也会随之变化的 。
当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，
优先把索引放在范围字段上。反之，亦然。

思考：这里我们使用如下索引，是否可行? (可行)
DROP INDEX idx_age_stuno_name ON student;
CREATE INDEX idx_age_stuno ON student(age,stuno);

5.4、filesort 算法：双路排序和单路排序

排序的字段若不在索引列上，则 filesort 会有两种算法：双路排序 和 单路排序
1. 双路排序（慢）
MySQL4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，
然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在 buffer 进行排序，再从 磁盘取其他字段 。
取一批数据，要对磁盘进行两次扫描，众所周知，IO 是很耗时的，所以在 MySQL4.1 之后，出现了第二种改进的算法，就是单路排序。

2. 单路排序（快）
从磁盘读取查询需要的所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题
由于单路是后出的，总体而言好过双路
但是用单路有问题
  在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以可能取出的数据的总大小超出了sort_buffer的容量，
导致每次只能取sort_buffer容量大小的数据，进行排序（创建temp文件，多路合并），排完再取sort_buffer容量大小，再排…从而多次I/O。
  单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略
尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的1M-8M之间调整。
MySQL5.7，InnoDB 存储引擎默认值都是 1048576 字节，1MB。

尝试提高 max_length_for_sort_data
  提高这个参数，会增加改进算法的概率。
  SHOW VARIABLES LIKE'%max_length_for_sort_data%';
  但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。
如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。

Order by时select是一个大忌。最好只Query需要的字段。*
  当 Query 的字段大小综合小于max_length_for_sort_data，而且排序字段不是TEXT|BLOG类型时，会改进后的算法——单路排序，否则用老算法——多路排序。
  两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，
但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size

6、GROUP BY优化

group by使用索引的原则几乎跟order by一致 ，group by即使没有过滤条件用到索引，也可以直接使用索引。
group by先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费 CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

7、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见有非常头疼的问题就是limit 2000000,10，
此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
EXPLAIN SELECT * FROM student LIMIT 2000000,10;

优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

优化思路二
该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询 。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8、优先考虑覆盖索引

8.1、什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。
毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。 一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN 和 WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键包含SELECT到 FROM之间查询的列。
举例一：
#删除之前的索引
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student (age,NAME);
EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

注意：前面我们提到如果使用上<>就不会使用上索引了 并不是绝对的。
比如上面这条SQL就用上了！！！Attention！我们讲解的关于索引失效以及索引优化都是根据效率来决定的。
对于二级索引来说：查询时间=二级索引计算时间+回表查询时间，由于我们使用的是覆盖索引，回表查询时间 = 0，索引优化器考虑到这一点就使用上 二级索引了~

举例二：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

同上，由于也使用了覆盖索引，最终SQL执行也正常使用上了索引~

8.2、覆盖索引的利弊

好处:
1. 避免Innodb表进行索引的二次查询（回表）
Innodb 是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，
在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。
在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2. 可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的，对于I/O密集型的范围查找来说，对比随机从磁盘读取每一行的数据I/O要少的多，
因此利用覆盖索引在访问时也可以把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端:
索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

9、如何给字符串添加索引

有一张教师表，表定义如下：
create table teacher(
    ID bigint unsigned primary key,
    email varchar(64),
    ...
)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句:
mysql> select col1, col2 from teacher where email='xxx'; 
如果 email 这个字段上没有索引，那么这个语句就只能做 全表扫描 。

9.1、前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
mysql> alter table teacher add index index1(email); 
#或
mysql> alter table teacher add index index2(email(6));

这两种不同的定义在数据结构和存储上有什么区别呢?下图就是这两个索引的示意图。

如果使用的是 index1 （即 email 整个字符串的索引结构），执行顺序是这样的:
  1、从 index1 索引树找到满足索引值是’ [e-mail] ’的这条记录，取得 ID2 的值;
  2、到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集;
  3、取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’ [e-mail] ’的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：
  1、从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1;
  2、到主键上查到主键值是 ID1 的行，判断出 email 的值不是’ [e-mail] ’，这行记录丢弃;
  3、取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集;
  4、重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
也就是说 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。 
前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

9.2、前缀索引对覆盖索引的影响

前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止如此，我们再看一下另外一个场景：
如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从 index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。
而如果使用 index2（即 email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。
即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。
select id,email from teacher where email='songhongkangexxx.com';

结论:
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

9.3、拓展内容

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，我们要怎么办呢?
比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为6的前缀索引的话，这个索引的区分度就非常低了。
按照我们前面说的方法，可能你需要创建长度为12以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢?这种方法，既可以占用更小的空间，也能达到相同的查询效率。有!

**第一种方式是使用倒序存储。**如果你存储身份证号的时候把它倒过来存，每次查询的时候：
mysql> select field list from teacher where id_card=reverse(input_id_card_string);
由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。当然，实践中你还要使用 count(distinct) 方法去做验证。

**第二种方式是使用 hash 字段。**你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
mysql> alter table teacher add id_card_crc int unsignedadd index(id_card_crc);
然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段，由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，
所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
mysql> select field list from twhere id_card_rc=crc32(input_id_card_string) and id_card=input id_card_string
这样，索引的长度变成了4个字节，比原来小了很多。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些，因为 crc32 算出来的值虽然有冲突的概率但是概率非常小，可以认为每次查询的平均扫描行数接近 1。
而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。