MySQL知识梳理(二)

MySQL知识梳理(二)

Scroll Down
cropped-1366-768-1101847
cropped-1366-768-1101847

知识总结来源于极客时间专栏《MySQL实战45讲》,此文仅为本人日后复习所用,不用再去一篇篇文章翻阅。

https://time.geekbang.org/column/article/70848

索引选择

上篇总结聊到了索引,这次就从索引开始,继续讨论。建数据库时候,有时候纠结是建唯一索引好,还是普通索引好?要知道哪个好,要得从两方面进行分析,分别为查询过程、更新过程。

查询过程

唯一索引等值查询时候,找到结果就会直接返回了,不会再去进行额外判断。普通索引找到结果后,还要继续往下判断,直到找到第一个不满足条件的记录为止。

所以从上面来看,查询时候,仅仅是多了一些判断而已,这个影响大不大呢?答案是微乎其微。

首先要知道,引擎读写数据是按数据页为单位进行读写,一个数据页默认16k,可以存放很多叶子节点了,一般读取某个数据时候,就是先把整个数据页读到内存里面了,在内存里面进行数据判断,速度是非常快的,所以查询时候,两者的影响是微乎其微。

更新过程

要了解更新过程的差异,就得先知道 change buffer。

当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果数据页还没有在内存中,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。

change buffer 不仅存在内存中,也会写到磁盘上。change buffer 缓存的操作写到对应数据页的过程称为 merge。访问这个数据页会触发 merge,系统也有后台线程会定期 merge。

什么条件下使用 change buffer ?

唯一索引的更新用不上 change buffer,只有普通索引可以使用。理由很简单,唯一索引插入时候,要判断数据是否冲突,那就需要将对应数据页加载进内存,都已经在内存中了,我们直接在内存中进行更新岂不是更好。

change buffer 的好处:减少随机磁盘访问,对更新性能的提升很明显

这里给生产环境DB阻塞提供了一个排查思路:如果把普通索引换成唯一索引,在业务有大量数据插入的情况下,因为用不上 change buffer,所以每次读写都要频繁读取磁盘上的数据页,插入语句就会比较缓慢了,有可能就阻塞了。

change buffer 的使用场景

change buffer 累计的操作越多,收益就越大。如果业务是写多读少,写完之后不用马上读取,那么这样使用 change buffer 的效果是最好的。在上家公司搞的运维系统,就是写入之后就马上要刷新页面,这种场景就很不合适了。写多读少类似账单类、日志类系统。

所以在业务能保证唯一性的情况下,尽量使用普通索引

d69ddc773534f3bcd94e5fe6eb4d1036
d69ddc773534f3bcd94e5fe6eb4d1036

这个提个疑问,change buffer 是存在内存中,也会写到磁盘,那假如在 change buffer 还没来得及写到磁盘中时,数据库崩溃了,那是不是这部分数据就丢失了呀?

答案是不会。因为事务提交时候,change buffer 的操作也会写到 redo log 里面。

MySQL 选错索引

选择索引是优化器的工作,优化器是根据扫描的行数、是否使用临时表、是否排序等因素来决定使用那个索引。

扫描行数是怎么判断的?

首先要知道扫描的行数是一个估算值,要想知道扫描的行数,得先知道总行数,这也是个估算值,这个值叫索引的基数,基数的统计是通过采样的方式,InnoDB默认选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面树,就得到了索引的基数。

基数里面扫描的行数可以这样看,用explain命令,这个行数不仅包括了索引本身扫到的行数,也包括了回表的行数。

image-20200917093751160
image-20200917093751160

如果你觉得这个行数与实际行数相差太大,可以用 analyze table t 命令,来重新统计索引信息。如果这样还是选错索引,可以用 force index 来指定正确的索引。

字符串加索引有门路

如果字符串不是很长,我会选择直接把完整字符串作为索引的一部分,一旦字符串很长,比如身份证、邮箱这种,如果选择完整字符串做索引,会导致索引占用空间比较大,这时候应该怎么办才好呢?

前缀索引

这种时候,我们可以使用前缀索引,类似 index (email(6)),既然是前缀索引,那我们就得找出具体多少位能给我们一个比较好的区分度,

## 看具体字段有多少个不同值
mysql> select count(distinct email) as L from SUser;
## 看取不同的位,能提供的不同值,两者一比,就可以拿到一个区分度比例
mysql> select 
  count(distinct left(email,4))as L4,
  count(distinct left(email,5))as L5,
  count(distinct left(email,6))as L6,
  count(distinct left(email,7))as L7,
from SUser;

使用前缀索引有利有弊:

  • 好处:使用前缀索引,定义好长度,可以做到既省空间,同时又不用增加太多的查询成本
  • 坏处:使用前缀索引,就不能使用覆盖索引了,必须得回表才行,因为前缀索引的值要到主键索引上,看看是不是正确的值

其他方式

倒序存储

还有可能遇到一种情况是,前缀索引要加很多位才有足够的区分度,这种时候,就得换个方法了,可以考虑使用倒序存储,反过来区分度也许就好了,缺点是每次都要使用 MySQL 提供的 reverse 函数进行计算。

加多个hash字段

我们可以加多一个字段,并且给这个字段加个索引,缺点是每次都要进行 crc32 函数进行 hash 计算。比如

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

需要特别注意了,这两种方式查询性能稳定,但都不支持范围查询

1600324133763
1600324133763

MySQL突然很慢如何排查

注意了,我这里说的是突然,不属于没有加索引的情况,如果 sql 平时都正常,突然变得很慢,这种时候就要考虑是不是在刷“脏页”了。

当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,成为“干净页”。

平时操作突然变得很慢,可能就是在刷脏页了

触发数据库刷脏页的场景:

  • InnoDB 的 redo log 写满了,要先把部分 redo log 里面对应的脏页 flush 到磁盘。
  • 系统内存不足。要淘汰一些数据页,给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘
  • MySQL 会在系统空闲时候,安排线程来进行脏页处理。
  • MySQL 要准备关闭时候,也会把脏页刷到磁盘上。

第三、第四点都是数据库的正常情况,第二点也不是大问题,内存总是有限的,用完就得淘汰一些,只不过一下子淘汰的脏页太多就会影响查询时间。日志写满这种情况就要极力避免了。

MySQL 刷脏页速度可以重点看 innodb_io_capacity 这个参数的值,如果是 SSD 硬盘,那就可以设大点了,太小会被 MySQL 认为你的系统性能一般,导致慢慢刷。

count(*) 必知必会

不同的 MySQL 引擎,count() 有不同的实现方式。MyISAM 是把表的总数存到了磁盘上,所以 count()时候,直接拿这个数返回就行。InnoDB 统计表的总数时候,是自己一行行加。

看到这里应该会有个疑惑,为啥 InnoDB 不也存一下总数吗?那是因为,InnoDB 是支持事务的,因为 MVCC 的存在,事务在不同时刻看到的数据是不一样的,所以没法存起来。

不要再纠结 count() 和 count(1) 和 count(字段)哪个更快了,无脑 Count() 就完事了,因为 MySQL 专门优化过count(*)。绝对不要 count(字段),count字段会去判断没个字段是不是 null,不是 null 再加1,浪费时间。

count(*) 统计正确做法

如果表比较大,对统计数量有需求的,可以把数量保存起来,那具体保存到哪里?有两个选择,一个是Redis,一个是数据库

保存到 Redis

保存数据到Redis,有可能出现数据不一致的情况。

img
img

A先插,B读Redis,如果A的Redis计数还没加1,那就会对不上了。归根结底是因为这属于分布式事务,没有比较好的解决方案。

保存到数据库

可以把数量保存到单独的表中,利用 InnoDB 的事务特性,不同时刻开启的事务,根据 MVCC 只能看到对应版本的数据。

order by 的工作原理

首先要知道 order by 排序时候,会用到一块内存,这块内存被成为 sort_buffer。排序有两种算法,分别为全字段排序rowid排序。MySQL 会根据查询的字段和索引情况来选择合适的算法。

假如现在就有个这样的sql,索引是city, 查询杭州的人信息,并且按名字排序

select city,name,age from t where city='杭州' order by name limit 1000  ;

如果我们要查询的字段很多,就像现在有 city、name、age,这三个加起来的长度一旦超过了 MySQL

内置的 max_length_for_sort_data 参数指定的值,会使用 rowid 排序,如果还在范围内,那就使用全字段排序。

全字段排序

这种情况下,先通过 city 索引树逐条找出符合条件的记录,然后根据 主键id 去遍历主键索引,拿出语句需要的字段。就上面的语句而言,就拿出city、name、age 这个三个字段,放到 sort_buffer 中,进行排序,但内存总是有限的,如果需要排序的内容过多,则会借用磁盘来进行辅助排序。磁盘排序就是把要排序的内容分成好几个文件,每个文件先排序,最后再汇总。

img
img

rowid排序

如果要排序的字段很多,sort_buffer 里面要放的字段数太多,这样内存中能够存放的行数就少,要分成很多个临时文件,排序性能会变差。

现在流程变为,city 索引树取出主键id,去主键索引上获取 name 字段,然后把 name,id 放入到 sort_buffer 中,排完序后,再到主键索引中找出完整的数据记录,返回给用户。

img
img

这种排序算法缺点就是要回表造成多磁盘读,不会被有限选择,除非内存不够了

优化方案

不是所有的语句,都要在 sort_buffer 中进行排序,如果索引树上有要排序的字段,那只要我们利用起来这个索引,拿到的记录自然就是有序的。这就可以好好利用联合索引了,更有甚者,利用起覆盖索引,回表也不用了。但也不是无脑用覆盖索引,索引的维护是有代价的。

img
img

像这个语句就用到了覆盖索引,Extra列有个 Using indx,这就是覆盖索引独有的信息。

select namg,age, city from T order by create_time

大家可以猜下上面的语句会不会用到 create_time 索引,答案是不会的,因为优化器认为走了 create_time 索引,还得回表到主键索引上,代价较高,索引就直接使用主键索引了。

select namg,age, city from T order by create_time limit 1000

这个语句就会用到 create_time 索引了,为啥呢?因为用到 limit,减少了要回表的数量,那就现在 create_time 索引上遍历会更快点。