读书笔记 -- MySQL45讲

最近学习极客时间的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,nlimit m,两者是不太一样的,对于limit m,n,其一共会查询出m+n条数据,然后丢弃掉m条数据,返回n条,当m值非常大的时候这个效率是难以忍受的。但是对于limit m,则直接获取m条数据。

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

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

清单1: 分页优化SQL

1
2
3
4
5
6
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是被驱动表

1
2
3
4
# t1是驱动表
# t2是被驱动表

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

Index Nested-Loop Join算法
该算法需要t2表对应的join字段存在索引,其步骤如下:

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

Block Nested-Loop Join算法
该算法适用于不存在索引的情况,其步骤如下:

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

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

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

group by的原理

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

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

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

explain

key_len的计算
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节
  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节
  • NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

等待更新

造轮子-- Hosts-Switch-Alfred插件
程序员的核心能力(转)