一篇文章帮你记住MySQL优化常见切入点

FishBones
152 阅读13分钟

本篇文章会从Mysql的读、写操作两个层面一步一步的进行分析。

一、Mysql的读操作

1.1 一条查询语句的执行流程

通过查询语句的执行流程，抓住优化的核心点。

Mysql的读操作流程如下：

image.png

Mysql 分为“服务层”和“存储引擎层”。服务层主要负责处理SQL，选择索引，存储引擎层负责数据获取。

对于“服务层”来说我们重点需要关注的就是索引的选择（分析优化器），对于存储引擎层来说，重点需要关注Buffer Poll的缓存机制。

1.2 Buffer Pool

理解了Buffer Pool缓存机制，才能更好的做优化

Mysql 大部分情况也是基于缓存的查询。

如果所有的数据都从磁盘读取，那么速度是非常缓慢的。因此Inno DB 提供了Buffer Pool作为缓存，存储数据。

Buffer Pool 缓存机制

1.  如果Buffer Pool中没有数据，则从磁盘中读取一页数据，加载到缓存中。这里还有一个【预读】：innodb_read_ahead_threshold 如果一个区域读取的数据页超过阈值，就会触发预读策略，将相邻的数据页也加载到缓存中）

2.  Buffer Pool 在内存空间不足时，采用 LRU 淘汰策略。【刷脏】：Buffer Pool 不仅是作为数据查询缓存，在数据修改时也会需改 Buffer Pool（Change Buffer） 中数据，同时记录Redo Log日志，此时不会直接更新磁盘。因此在内存不足时，需要进行刷脏，写脏数据到磁盘。

3.  Buffer Pool 默认大小 128M。可以通过设置 innodb_buffer_pool_size 参数调整大小。

既然Mysql Buffer Pool  也是使用缓存，那么直接调大配置，是不是就不需要Redis了？Buffer Pool 与 Redis 缓存区别在哪呢？

首先本质上都是基于内存存储数据，在性能方面是没有差异的。

但是Redis使用的是Hash结构。我们通常都是通过  Key 直接返回结果数据，不需要条件、分页、聚合。

而InnoDB 的 Buffer Pool 也可以做到通过 Key （索引） 访问到 这一条数据，但是通常我们并不会直接使用 Key 去获取一条数据。而是通过一条 SQL 去获取一堆数据（SQL势必要经过解析，然后确定索引，通过索引去找一堆数据。返回匹配的那一条）。

所以，Buffer Pool 缓存 与 Redis 缓存其实没有区别，只是 Mysql 和 Redis 基于各自的使用场景对内存的使用方式。有区别的只是 Mysql 和 Redis。

本质都是一种取舍，有所得必有所失。

Mysql 由于要支持 条件、范围、关联、排序、分组 .... 等场景，增加了额外的开销，牺牲了大量的查询性能。

Redis 追求极致的查询速度，只能支持简单的查询。

1.3 索引（B+Tree）

99%的SQL优化都离不开SQL本身，而决定SQL本身的还是索引，从不同的角度来理解索引。

据说Mysql InnoDB 三级索引树，支持千万级别数据存储。

1.3.1 索引结构

聚簇索引： 主键索引，叶子结点存储整条数据。在没有设置主键时，使用表默认ID字段（db_row_id） image.png

非聚簇索引： 其他索引包括唯一索引。叶子结点存储主键ID。 image.png

看完聚簇索引以及非聚簇索引后，不得不提到另外一个名词：回表。

回表： 在使用普通索引(非聚簇索引)查询时，查询除索引字段、主键ID外的其他字段时，就需要利用叶子节点存储的主键ID，回到聚簇索引结构中查询其他字段。

另外，对于回表还有一个不得不提的深度分页问题：

用户表：

字段类型索引
idbigint主键索引
namevarchar无
ageint普通索引
<!-- 额外产生10W次回表 --> 
select * from user where age < 18 limit 100000,20; 
<!-- 优化后 --> 
select * from user where id in ( select id from (select id from user where age < 18 limit 100000,20） as t )

试想：age作为普通索引，SQL 又是select * 所以肯定会产生回表。而对于分页limit 100000,20 会查询 100020 条，如果再加上回表，对性能影响还是非常大的，关键是没有必要。

1.3.2 主键索引顺序

索引的有序无序，也会对性能产生很大的影响。一方面是避免 order by，另一方面也能避免数据页分裂。

有序索引： 自增主键（int、bigint）、时间戳

无序索引： UUID

一个数据页的大小是有限的，而索引都是按照顺序排列数据的。

如果使用的主键是无序的（UUID，随机字符串）等，很容易在新增时，按照索引顺序，应该在中间部分插入，此时如果数据页已满，就会出现数据页的分裂，极端情况甚至出现级联，就可能出现慢SQL。

并不是说使用了有序类型作为主键就不会有页的分裂，也需要保证新增时有序才行。

image.png

假如此时插入一条 id 为 24的数据。变动情况如下：产生了多次页的分裂重组。

image.png

1.3.3 普通索引顺序

单个索引： 只包含一个字段

联合索引： 包含多个字段

在这里插入图片描述

对数据进行排序也是一个耗时操作，而且数据量越大越慢。数据量小还可以使用内存排序，数据量大只能进行文件排序。

explain  file_sort 并不代表使用了文件排序。只是说明需要对结果数据进行排序（索引结构与返回结果顺序不一致）。

Mysql 本身也会采用一些优化排序的算法。全字段排序、RowId排序、优先队列排序...

索引是有序的，联合索引也是有序的。还是以User表为例，假如建立联合索引：name、age，那么该索引结构中，会先按照name索引进行排列，name一样的数据，会按照age顺序进行排列。

也就是说，假如我们需要查询所有name = 张三 的用户 并且按照 年龄排序时，我们就可以直接建立 name + age 的联合索引，在数据新增时就保证与查询数据顺序一致，此时查询 就不会出现 file sort。

联合索引时避免重复建立索引：

字段类型索引
idbigint主键索引
avarchar
bint
cvarchar
dvarchar

当我们对上表，建立 联合索引 a_b_c 时，实际上我们建立的索引还有：a、a_b、a_b_c_id；

遵循最左前缀原则。

8.0 之后存在索引跳跃扫描(Index skip Scan)的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引

联合索引字段顺序，尽可能按照字段区分度高低排列。

1.3.4 索引字段重复

唯一索引： 不允许出现重复。

普通索引： 允许重复。

既然知道了两者的不同，那么如何更好的选择呢？

唯一索引在查询数据时性能要略高（忽略不计）于普通索引。

普通索引查询：查询到满足条件的值时，仍然需要继续往后查，直到不满足条件为止。

唯一索引查询：查询到满足条件值时，直接返回即可。

唯一索引在新增数据时性能要低于普通索引。唯一索引怎么校验唯一性？

非唯一索引更新过程：计算要新增的数据页-》 判断Buffer Pool中是否存在该页-》存在，则直接写入-》不存在，则写入Change Buffer

唯一索引更新数据过程：计算要新增的数据页-》判断 Buffer Pool 中是否有该页 -》存在，判断是否重复，不重复写入-》不存在，从磁盘读取该页，再判断是否重复，不重复写入。

也就是说，唯一索引是没有办法用到Change Buffer的，而且大部分情况是需要额外从磁盘加载数据到缓存的。因此在更新性能方面要低于普通索引。

唯一索引的优点

唯一索引需要将数据写入到Buffer Pool，而 Buffer Pool 中的数据在查询时可以直接返回。所以对于更新完需要立刻查询的场景，使用唯一索引还是比较合适的。

普通索引虽然直接写Change Buffer 即可，但是当查询时，把数据页加载到Buffer Pool 之后，是需要 将 Change Buffer 数据同步到 Buffer Pool中的。此时也有额外的耗时操作。如果更新完不立刻使用，没有必要使用唯一索引。

1.3.5 索引失效

15个必知的Mysql索引失效场景，别再踩坑了！

二、Mysql的写操作

2.1 一条更新语句的执行流程

前半部分（客户端-》Mysql服务的过程）与查询相同，不再细说。主要看存储引擎部分。

image.png

  1. 用户提交SQL语句。

    1. 解析器校验语法
    2. 执行器执行SQL

  2. 读取数据到Buffer Pool，修改Buffer Pool中的数据。

  3. 写入undo Log

  4. 写入redo Log

  5. 返回成功/失败

  6. 后续由后台线程执行刷脏，持久化数据到磁盘。

在事务commit、redo log buffer 内存不足是会写redo Log 文件。

2.1.1 Mysql 持久化机制

Mysql 持久性是通过 Redo Log 来保证的。并非是写入磁盘数据页才算持久化完成。

原因就是因为顺序IO速度，要远大于随机IO。

image.png

由于Mysql 在更新数据时，可以直接修改 Buffer Pool (Change Buffer) 中数据，并记录 Redo Log 日志来保证完成持久化工作。并不需要直接写磁盘，所以会存在一部分脏数据。

  1. 定时刷脏。

  2. redo log 写满时刷脏

  3. buffer pool 空间不足时刷脏

  4. 数据库正常关闭时刷脏

2.2 锁

如果数据被锁住了，我还能怎么快？

Mysql ACID 特性中 除了D持久性以外，其他三个特性都与锁有关。

原子性： 事务要么全部成功，要么全部失败。要保证原子性，就得保证过程没有冲突，需要抢占锁。

一致性： 事务执行前后，完整性没有被破坏，转账操作，A账户必须扣减成，B账户必须增加成功。同理，一致性的保证，也必须依赖与锁。

隔离性： 事务的隔离级别，RC -》 RR 主要也是通过锁力度的大小来实现。

2.2.1 行锁、表锁

锁仅存在与索引字段上，走索引时才会加行锁。否则直接使用表锁。

2.2.2 共享锁、排它锁

锁是一种很重量级的操作。我们一直都在致力于减轻锁的粒度，甚至以不加锁的方式来完成并发操作。

因此我们会有 偏向锁-》轻量级锁-》重量级锁-》表锁-》行锁-》读写锁。简单来说就是尽量不加锁，如果加锁，也要尽可能的减少锁的粒度。

Mysql的共享锁和排他锁就类似与读写锁，加了共享锁之后，还能够正常访问数据，如果有排它锁则需要等待。

Mysql既然有读写锁，而且读写是互斥的，那么我们为什么可以边读边修改呢？（一个事务查询该数据，另外一个事务修改该数据，此时是没有阻塞等待的）

原因是因为，Mysql select 语句是不加任何锁的，update/insert/delete会加排他锁。

<!-- 共享锁 -->
select * from user where age = 18 lock in share mode; 
<!-- 排它锁 --> 
select * from user where age = 18 lock in share mode;

共享锁适用场景：

在父级节点下方新增子级节点。如果此时父节点恰好被删除，此时要如何确保不会插入一条脏数据呢？

排它锁适用场景：

下单扣减库存。

事务1 (扣减5)事务2(扣减10)
time1查询库存数量。count = 10
time2判断数量满足要求查询库存数量。count = 10
time3判断数量满足要求
time4扣减库存扣减库存

也可以直接使用如下SQL来实现。

update table set count = count - num where  goodsId = 1 and  count >= num；

2.2.3 记录锁（Record Lock）

对数据表中现有记录加锁。

2.2.4 间隙锁（Gap Lock）

事务的隔离级别：

RC：已提交读。一个事务能够读取到其他事务已提交的数据。

RR：可重复读。一个事务A开始后执行一次查询操作，在事务A执行过程中，事务B对数据执行任何的写操作，并且提交后，对于事务A后续读取没有任何影响。

user 表结构：

字段类型索引
idbigint主键索引
namevarchar无
ageint普通索引

表数据：

id nameage
1张三5
5李四10
10王五15

事务B写操作完成但是对于事务A不可见

MVCC (Multiversion Concurrency Control) ，多版本并发控制协议来实现，快照读生效，当前读不生效。RR隔离级别下，事务启动时生成快照版本，后续全部使用快照读。

下图为抽象示意图，Read View 具体实现并非如此。

image.png

事务1 事务2(扣减10)
time1BEGIN;  
time2select * from user where age = 10;BEGIN:
time3update user set age = 10 where id = 1;
time4COMMIT;
time5select * from user where age = 10;
time6select * from user where age = 10 for update;  （此时读取到2条）update user set age = 20 where age = 10; (此时修改2条)
time7COMMIT;

事务B不能执行写操作（间隙锁）

事务1 事务2事务3
time1BEGIN;  
time2select * from user where age = 10;  （此时读取到1条）update user set age = 20 where age > 10; （修改1条）BEGIN:
time3update user set age = 12 where id = 1; （阻塞）BEGIN:
time4COMMIT;inset user values (20, '赵六', 13)（阻塞）
time5select * from user where age = 10;COMMIT;
time6select * from user where age = 10;
time7COMMIT;

Redo Log/Bin Log

update user set age = 12 where id = 1; （事务2）

inset user values (20, '赵六', 13)；（事务3）

update user set age = 20 where age > 10; （事务1）【最终结果修改3条】数据不一致问题。

正是因为RR隔离级别下间隙锁的存在，所以在执行 update 语句时一定要注意使用主键ID，将间隙锁转换为记录锁，减少锁的粒度。

记录锁仅存在与包括主键索引在内的唯一索引上。间隙锁仅存在与非唯一索引上。

<!-- 记录锁 id = 10 --> 
select * from user where id = 10 for update; 
<!-- 间隙锁 age: （5,15） --> 
select * from user where age = 10 for update;

原因如下图：如果要锁 age = 10 这部分数据，对于非唯一索引来说，在索引树上找到 age = 10 这个记录后，依然需要往前、往后进行查询（因为不是唯一，允许重复）直到找到不为10的数据为止，而中间这部分空间都是需要加锁的部分。

因此上方示例中，锁的范围就是（5,15）,如果 age = 10 前后数据为 9, 11,那么锁的范围就是（9,11）。

image.png

2.2.5 临键锁（Next-Key Lock）

记录锁 + 间隙锁。

锁的基本单位就是Next Key Lock，age = 10； (5,10] ，[10,15)

2.2.6 锁的范围

user 表结构：

字段类型索引
idbigint主键索引
namevarchar无
ageint普通索引

表数据：

id nameage
1张三5
5李四10
10王五15
15赵六20

基于间隙锁部分的理解，不难估算以下场景锁的粒度范围。关于实际范围前后的开闭，不同版本可能有一定的区别。有的 [5,10), 有的是 (5,10]

2.2.6.1 唯一索引等值

select * from user where id = 5 for update;   => id = 5

select * from user where id = 7 for update;   => (5,10) 

2.2.6.2 非唯一索引等值

select * from user where age = 10 for update;   => (5,15)  + id = 5

select * from user where age = 12 for update;   => (10,15) 

2.2.6.3 唯一索引范围

select * from user where id >= 5 for update;   => [5,10)

select * from user where id >=5  and id < 6 for update;   => [5,10)

2.2.6.4 非唯一索引范围

select * from user where age >= 10 for update;   => (5, +∞)   + id = 5, 10, 15

select * from user where age >= 10 and <= 15  for update;   => (5, 20)  + id = 5,10

select * from user where age > 10 and age < 15 for update;   => (10, 15)

avatar
文章
阅读
粉丝