图灵第4期MySQL调优专题学习笔记

目录

一、首先复习打印的课件

二、Explain中的列

三、解读extra

四、索引最佳实践

五、MySQL的内部组结构

2. bin-log归档：

六、常见SQL深入优化

1. order by 与 group by 优化

2. 索引设计原则

3. 分页查询优化（根据非主键字段排序的分页查询）

4. join关联查询优化

5. in 和 exist优化

七、阿里巴巴MySQL规范手册

八、并发事务

1. 并发事务带来的4个问题（见讲义）；

2. 事务隔离级别；

3. 锁详解；

4. InnoDB与MyISAM引擎最大的不同是什么？（面试题）

5. 什么是MySQL的MVCC机制？

九、group by的实质是先排序后分组

---

一、首先复习打印的课件

二、Explain中的列

id列：id列的编号是select的序列号，有几个select就有几个id，且id的顺序是按select出现的顺序增长的；id编号越大执行优先级越高，id相同则从上到下执行，id为null最后执行。

select_type列：表示对应行时简单还是复杂的查询。回忆有哪几个取值？

table列：当前表名。

partitions列：显示查询将访问的分区，如果你的查询是基于分区表。

type列：这一列表示关联类型或访问类型，即MySQL决定如何查询表中的行，查找数据记录大大概范围。依次从最优到最差分别为：____________________________________；一般来说，得保证查询达到range级别，最好达到ref。

• eq_ref：主键索引（primary key）或唯一索引（unique key）的所有部分被连接使用，最多只返回一条符合条件的记录；
• ref：不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
• range：范围扫描通常出现在in()，between，>，<，≥等操作中。使用一个索引来检索给定范围的行；
• index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
• ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

possible_keys列：这一列显示查询可能使用哪些索引来查找。

• 可能出现possible_keys列有值，而key列为null的情况，这种情况是因为表中数据不多，MySQL认为索引对此帮助不大，选择了全表查询；
• 如果possible_keys列为null，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创建一个适当的索引来提高查询性能。

key列：这一列显示MySQL实际采用哪个索引来优化对该表的访问。如果没有使用索引，则显示为null。

key_len列：这一列显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。该值越小越好。

例如：Explain下面的SQL后，看到key_len列的值为8；因为id定义时是bigint类型，是8个字节：（更多内容请参考笔记）

explain select * from student_info where id='2';

ref列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例如：film.id)。

rows列：这一列是MySQL估计要读取并检测的行数。

filtered列：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

关于MySql中explain结果filtered的理解_explain filtered_xszhaobo的博客-CSDN博客

        filtered不是万能的，关注执行计划结果中其他列的值并优化查询更重要。比如为了避免出现filesort（使用可以满足order by的索引），即使filtered的值比较低也没问题。再比如上面filtered=0.1%的场景，我们更应该关注的是添加一个索引提高查询性能，而不是看filtered的值。

extra列：这一列展示的是额外信息。

extra常见的重要值

Using index	使用了覆盖索引扫描，也就是需要访问的数据都在索引中，不需要回表。在一般情况下，减少不必要的数据访问能够提升效率。
Using where	使用where语句来处理结果，并且查询的列未被索引覆盖
Using index condition	查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary	MySQL需要创建一张临时表来处理查询。出现这种情况一般是要 进行优化的，首先是想到用索引来优化。
Using filesort	需要优化
Select tables optimized away	使用某些聚合函数如min、max来访问存在索引的某个字段时，extra的值。

实现 

三、解读extra

•  extra字段详细解释说明：MySQL中explain执行计划中额外信息字段(Extra)详解_start temporary_haughty_xiao的博客-CSDN博客
• 另请参考：MySql学习笔记（八）：explain之extra_51CTO博客_mysql学习笔记

• 转载自：数栈技术分享：解读MySQL执行计划的type列和extra列_51CTO博客_mysql执行计划中type 

1、using index
表示实现了覆盖索引扫描；也就是需要访问的数据都在索引中，不需要回表。在一般情况下，减少不必要的数据访问能够提升效率。

2、using where：
一般有两层意思：表示通过索引访问时，需要再回表访问所需的数据；
                             过滤条件发生在server层而不是存储引擎层；
        如果执行计划中显示走了索引，但是rows值很高，extra显示为using where，那么执行效果就不会很好。因为索引访问的成本主要在回表上，这时可以采用覆盖索引来优化。
        通过覆盖索引也能将过滤条件下压，在存储引擎层执行过滤操作，这样效果是最好的。所以，覆盖索引是解决using where的最有效的手段。

3、using index condition
表示将过滤下压到存储层执行，防止server层过滤过多数据
如果extra中出现了using index condition，说明对访问表数据进行了优化。

4、using temporary
表示语句执行过程中使用到了临时表。以下子句的出现可能会使用到临时表：
order by
group by
distinct
union等
数据不能直接返回给用户，就需要缓存，数据就以临时表缓存在用户工作空间。注意，可能会出现磁盘临时表，需要关注需要缓存的数据的rows。
可以使用索引消除上面的四个操作对应的临时表。

5、using filesort
说明有排序行为，但是不一定是磁盘排序。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘排序。这种情况下一般也是要考虑使用索引来优化的。

Using filesort通常出现在order by，当试图对一个不是索引的字段进行排序时，mysql就会自动对该字段进行排序，这个过程就称为“文件排序”。

6、using sort_union(indexs)
比如当执行下面语句：

Sname和sphone列上都有索引，这时执行计划的extra项就会显示using sort_union(i_sname,i_spone)，表示索引合并。常伴随着index_merge。

7、using MRR
一般通过二级索引访问表数据的过程是：先访问二级索引列，找到对应的二级索引数据后就得到对应的主键值，然后拿着这个主键值再去访问表，取出行数据。这样取出的数据是按照二级索引排序的。
MRR表示：通过二级索引得到对应的主键值后，不直接访问表而是先存储起来，在得到所有的主键值后，对主键值进行排序，然后再访问表。这样可以大幅减低对表的访问次数，至少实现了顺序访问表。
MRR的一个优点就是提升索引访问表的效率，也就是降低了回表的成本。但是有一个比较大的问题：取出来的数据就不按照二级索引排序了。

8、using join buffer(Block Nested Loop)
BNL主要发生在两个表关联时，被关联的表上没有索引。
BNL表示这样的意思：A关联B，A的关联列上有索引而B的没有。这时就会从A表中取10行数据拿出来放到用户的join buffer空间中，然后再取B上的数据和join buffer中A的关联列进行关联，这时只需要对B表访问一次，也就是B表发生一次全表扫描。
如果join buffer中的10行数据关联完后，就再取10行数据继续和B表关联，一直到A表的所有数据都关联完为止。
从上面可以看出来，这种方式大概效率会提高约90%。

9、using join buffer(Batched Key Access)
一般出现BKA的情况是：表关联时，被驱动表上有索引，但是驱动表返回的行数太多。
当出现上述情况时，就会将驱动表的返回结果集放到用户工作空间的join buffer中，然后取结果集的一条记录去关联被驱动表的索引关联列。得到相应的主键列后并不马上通过这个主键列去被被驱动表中取数据，而是先存放到工作空间中。等到结果集中的所有数据都关联完了，对工作空间中的所有通过关联得到主键列进行排序，然后统一访问被驱动表，从中取数据。这样的好处就是大大降低了访问的次数。
从上面可以看出：BKA用到了MRR技术；BKA适合驱动表返回行数较多、被驱动表访问时走的是索引的情况。
这个功能可以打开或者关闭：
Set optimizer_switch=’mrr=on,batched_key_access=on’;

10、using index for group by
表示通过复合索引完成group by,不用回表。
例如复合索引(a,b)，执行语句：select a from tb group by b;时就会出现using index for group by。

11、materialize scan
对物化表的全扫描，因为物化表就是一个临时表，表上没有索引。

四、索引最佳实践

1. 不在索引列上进行计算、函数、类型转换，否则会导致索引失效而全表扫描；

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

3. 尽量使用覆盖索引，减少回表；

4. MySQL在使用不等于(!=或者<>)，not in，not exist，not like的时候无法使用索引会导致全表扫描；

5. 小于<，大于>，小于等于≤，大于等于≥这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引；

6. is null，is not null一般无法使用索引；

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

8. 用or或者in查询时，MySQL不一定使用索引；MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引；（or或者in在表数据量比较大的情况会走索引，在表记录不多时会全表扫描）；

9. like KK%一般情况都会走索引；

这里需要引入一个概念——索引下推（Index Condition PushDown，ICP），like KK%其实就是用了索引下推优化。那什么是索引下推呢？       

SELECT * FROM employees WHERE name LIKE 'LiLei%' AND age=22 AND position='manager';

这种情况，若其中联合索引是name,age,position，则：

• 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是'LiLei%'开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合；
• 在MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，从而有效减少了回表次数。即：使用索引下推优化后，首先匹配到'LiLei%'开头的索引后，还会在索引里过滤age和position这两个字段，最后拿着过滤完剩下的索引对应的主键id再回表查询整行数据。

注意：

• 索引下推会减少回表次数，对于InnoDB引擎的表索引下推只能用于二级索引，InnoDB的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少全行数据的效果。 
• 查询的结果集主键索引和二级索引都有时，优先二级索引，因为二级索引的B+树小。

10. 为什么范围查找时MySQL并没有索引下推优化？

        估计应该是MySQL认为范围查找过滤的结果集过大的缘故。like KK% 在绝大时候，过滤掉的结果集比较小，所以这里MySQL选择给like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

五、MySQL的内部组结构

Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL大部分核心服务功能，以及所有的内置函数(如日期、时间、数字、加密函数)，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、试图等。

Store层(引擎层)：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的是InnoDB引擎。

1. 查询缓存，在MySQL5.8版本正式移除了；

2. bin-log归档：

• binlog是Server层实现的二进制日志，它会记录我们的CUD操作，会将增删改SQL语句的执行逻辑记录在bin-log当中。
• binlog有哪些特点？（面试题）

        （1）binlog在MySQL的Server层实现；

        （2）binlog为逻辑日志，记录的是一条语句的原始逻辑；

        （3）binlog不限大小，追加写入，不会覆盖以前的日志。

六、常见SQL深入优化

1. order by 与 group by 优化

 说明：group by的实质是先排序后分组

 Explain分析sql语句后，关注Extra列的值。优化总结：

2. 索引设计原则

• 代码先行，索引后上；
• 联合索引尽量覆盖条件，尽量少建单值索引，可以设计一个或两三个联合索引，让每一个联合索引都尽量去包含sql语句中的where、order by、group by的字段，还要确保这些字段的顺序尽量满足sql查询的最左特性原则；
• 长字符串可以采用前缀索引；
• where与order by冲突时优先where。

3. 分页查询优化（根据非主键字段排序的分页查询）

-- 优化前
EXPLAIN SELECT * FROM employees ORDER BY name LIMIT 90000,5;

-- 优化后
EXPLAIN 
    SELECT * FROM employees e1 
inner join (SELECT id FROM employees ORDER BY name LIMIT 90000,5) e2
ON e1.ID = e2.ID;

优化的关键是：让排序时返回的字段尽可能的少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

4. join关联查询优化

MySQL的表关联时常见有两种算法：

• Nested-Loop Join （NLJ）算法
• Block Nested-Loop Join （BNL）算法

5. in 和 exist优化

七、阿里巴巴MySQL规范手册

• MySQL数据类型补充（见讲义）；
• 如果整型没有负数时，如ID，建议指定为UNSIGNED无符号类型，容量可以扩大一倍；
• 避免使用整数的显示宽度，即不要用 int(10)类似的方法指定字段显示宽度，直接用int；
• 数据类型为timestamp、datetime类型时，可以用CURRENT_TIMESTAMP作为默认值（MySQL5.6之后），MySQL会自动返回记录插入的确切时间；
• 建议用date类型来保存日期，MySQL中默认的日期格式为yyyy-mm-dd；
• MySQL能存储的最小时间粒度为秒；
• timestamp是UTC时间戳，与时区相关；
• datetime（8字节）与时区无关；它比timestamp（4字节）浪费空间；

八、并发事务

为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

1. 并发事务带来的4个问题（见讲义）；

2. 事务隔离级别；

3. 锁详解；

4. InnoDB与MyISAM引擎最大的不同是什么？（面试题）

• InnoDB支持事务；
• InnoDB支持行级锁，而MyISAM引擎只有表级锁；
• MyISAM引擎在执行查询语句select之前，会自动给涉及的所有表加读锁；但在执行select语句时(非串行化隔离级别)，则不会加锁。（详见讲义中的笔记）

5. 什么是MySQL的MVCC机制？

MySQL的默认隔离级别是可重复读，可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读(历史版本)，insert、update、delete会更新版本号，是当前读(当前版本)。

九、group by的实质是先排序后分组