读书笔记--MySQL45讲

屈定

发布于 2020-02-10 16:54:12

9520

发布于 2020-02-10 16:54:12

最近学习极客时间的MySQL45讲，补充下对于MySQL方面的知识，也在这里把自己之前的疑惑问题记录下来，从中寻找答案。由于InnoDB为常用引擎，以下分期默认都是InnoDB场景。

表与索引

引用评论中的一段解释：对于使用者来说，可以简单的认为每一张表都是有多个B+树组成，其中主键对应的B+树其连接着每一行的数据，称为主B+树，每一个索引所构成的树为辅B+树，其指向主树上的主键。因此当一个查询语句无法走任何索引时需要在主树上全量扫描，能走主键时会直接在主树上查找，走非主键索引时会定位到主键，然后回表其主B+树上查找，定位数据。

count(*)的原理

为了保证事务可重读的隔离性，因此每一次的数量查询其实都需要全遍历，在遍历过程中累加。对于一张表我们可以看作是多颗B+树，当没有其他额外过滤条件时，那么遍历过程所做的优化为扫描最小的那颗B+树，然后统计数量。

在没有过滤场景的条件下，频繁的count查询也会带来相应的性能问题，解决思路是使用一张单独的表存储数量，当对表进行插入删除操作时，在一个事务中同时更新数量，这样既保证了数量获取的高效，也保证了可重读隔离性的正确性。

order by的原理

在无法利用索引有序性的情况下，MySQL会有全字段排序以及rowId排序两种策略，全字段排序则是把全部字段放入到sort_buffer中，然后根据sort_buffer_size的配置大小决定使用内存排序还是外部排序算法，排序后输出。

全字段排序有个缺点就是要把字段都放入到sort_buffer中，当单行数据大于max_length_for_sort_data值，MySQL则会使用rowId算法，该算法只是把要排序的字段+主键放入到sort_buffer中，排序后再根据主键进行回表查询。

总之无法利用索引的排序消耗也是非常大，尤其是数据量很大的情况下，性能很致命，因此大表排序字段务必考虑索引。

limit的原理

limit m,n 和 limit m，两者是不太一样的，对于limit m,n，其一共会查询出m+n条数据，然后丢弃掉m条数据，返回n条，当m值非常大的时候这个效率是难以忍受的。但是对于limit m，则直接获取m条数据。

这里解决思路一般先从业务上去思考，业务上到底需要不需要大分页？如果不需要加个限制即可，如果需要，有没有可能加一些能缩小数据范围的必选条件，然后让整体分页数量不会变的很大。

业务上无法搞定，则需要从技术上考虑，慢的原因是因为查询了太多不需要的数据，那么整体优化思路就是利用覆盖索引，降低回表次数，只在最后获取数据时回表查询，也就是延迟关联，如清单1所示：

清单1: 分页优化SQL

SELECT * FROM `t` 
INNER JOIN
 ( SELECT id FROM `t` 
    WHERE file_type='TXT' ORDER BY id DESC LIMIT 99900,10) tmp
ON t.id = tmp.id
;

临时表tmp的查询必须全部在索引上，否则还是需要回表获取到对应字段，那么这里查询只会扫描这颗索引树，获取到 m+n个id，在丢弃掉m个id，最后再与t表做交集，从而减少回表次数。这是一种比较通用的做法，针对特定业务场景可以有更加独特的做法，比如在没有条件的情况下，直接根据id进行分页查询, 使用类似where id > 99900 limit 10这样的语句，这样只会查询10条。

幻读到底是什么？

这里直接用到了文章中的解释，数据库中的数据是在变化的，前一秒不满足要求的数据可能下一秒就满足了要求，此时锁对后满足的数据是无用的，因此如下图所示，同一条SQL，先后执行顺序不同，其结果也不同。

MySQL在可重复读隔离级别下，普通的查询是快照读，所以不存在该问题，对于当前读则会存在类似的问题，MySQL的解决方法是使用间隙锁，锁住间隙，防止在读取过程中其范围内新增合格的数据。

Join的原理

现在业务上开发很少遇到Join，因为Join一旦写不好就会造成笛卡儿积M*N的数据量，增加MySQL服务端的压力。对于一条Join的SQL分为驱动表和被驱动表，如清单2所示，t1是驱动表，t2是被驱动表

# t1是驱动表
# t2是被驱动表

select * from t1 straight_join t2 on (t1.a=t2.a);

Index Nested-Loop Join算法

该算法需要t2表对应的join字段存在索引，其步骤如下：

按照对应条件扫描驱动表t1，从驱动表t1中拿出一行数据。
根据驱动表t1的数据，去被驱动表t2中根据索引查询，取出对应的数据后与t1的该记录合并，作为结果集。
接下来重复即可。

Block Nested-Loop Join算法

该算法适用于不存在索引的情况，其步骤如下：

扫描驱动表t1，然后把数据拿到后放入join_buffer中，join_buffer满了后继续下一步
扫描被驱动表t2，获取数据后与join_buffer中的数据进行对比，满足的数据放入结果集。
清空join_buffer
继续扫描驱动表t1，重复之前的步骤。

与Index Nested-Loop Join算法相比，其效率简直无法忍受，因此Join需要有一定必须遵守的原则，

如果可以使用被驱动表的索引，join语句还是有其优势的；
不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用，可以应用层拆分为单表查询解决。
在使用join的时候，应该让小表做驱动表。

group by的原理

针对select id%10 as m, count(*) as c from t1 group by m;该SQL，在无索引的情况下其执行流程如下：

创建内部临时表，该临时表有m，c两个字段，主键是m，也就是group by的key。
扫描t1表，获取对应的id值，计算id%10的结果作为m。
1. 如果临时表中没有主键为m的记录，则插入一条记录
2. 如果临时表中有m对应的记录，则把该行的c加一。
遍历结束后，对该内存临时表使用rowid排序算法输出，如果不需要排序可以加order by null，让MySQL直接输出。

造成使用临时表的原因是输入数据为无序，因此需要利用临时表的唯一索引来去重统计，如果利用索引的有序性，也就是在m字段上加个索引，那么group by的执行只需要扫描一遍数据就可以直接的得来最后的结果。