【学习记录】MySQL 45讲学习提纲

山人（想妙计版）

已于 2023-07-18 23:45:28 修改

阅读量37

点赞数

文章标签： mysql 数据库

于 2023-07-14 00:07:25 首次发布

本文链接：https://blog.csdn.net/qq_39640089/article/details/131714000

版权

简要列出知识点，仅供个人学习记录。

一、基础篇

普通索引和唯一索引在查询能力上没差别，更新性能上可能会是普通索引更好，尽量选择普通索引。

性能对比普通索引和唯一索引
- 查询性能
  - 普通索引查询到目标 k=2 后会向后查询一条，如果下一条是 k=3 则不继续查询了。下一条记录可能需要额外读取下一页，但对于整型字段，一个数据页可以放近千个key，平均下来性能差异可以忽略不计。
  - 唯一索引查询到目标 k=2 后直接停止查询。
- 更新性能
  - 数据页在内存中：都直接更新
  - 数据页不在内存中：
    - 普通索引更新可以使用change buffer，将更新存在内存中，在下次读取到这个数据页时执行change buffer中存储的更新操作。
    - 唯一索引更新时必须将数据页读入内存，做唯一性的判断。
    - 如果业务场景是写多读少，change buffer的效果最好，例如账单类、日志类系统。如果业务场景是更新完就很快读取，change buffer反而会有额外的维护代价。
change buffer和redo log
- change buffer在内存中有拷贝，也会被写入到磁盘上。
- redo log主要节省的是随机写磁盘的IO消耗。
- change buffer主要节省的是随机读磁盘的IO消耗。

出现场景
- 删除历史数据、新增数据后可能会选错索引。
优化器的逻辑
- 代价考虑因素：扫描行数、是否使用临时表、是否排序等。
- 扫描行数是采样统计估算出的，可能不准。
  - 索引上的不同的值称为基数（可以理解为区分度）。
  - InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数。
选错索引的处理方式
- 使用force index强行选择一个索引。
- 修改语句，引导MySQL使用期望的索引。
  - select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
  - 假设上面语句选用了索引b，如果将order by b改为order by b, a则优化器会重新考虑代价大小。
- 新建更合适的索引，或者删除被误用的索引。

完整索引
- 占用空间。
前缀索引
- （相比完整字段）会增加扫描行数。
- （相比完整字段）无法覆盖索引，需要回表。
倒序存储+前缀索引（不支持范围查询）
- 比如身份证这类尾部区分度大的情况，可以避免前缀区分度不大的情况。
- select field_list from t where id_card = reverse('input_id_card_string');
Hash索引（不支持范围查询）
- 增加新字段存储字符串的hash值，比如用crc32()计算。为避免哈希冲突，需要在查询语句上增加原字段的条件。（crc32 4个字节）
- select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

平时执行很快的更新操作，就是在写内存和日志。
抖一下是在刷脏页（flush）：
- redo log写满了。
  - redo log的checkpoint向前推进，推进部分对应的所有脏页都要flush到磁盘上。
- 内存满了。
  - 需要新的内存页，淘汰一些数据页，如果淘汰的是脏页，就需要先将脏页flush到磁盘上。
还有两种情况下也会刷脏页，但不会被感知到：
- MySQL空闲时。
- MySQL正常关闭时。
InnoDB刷脏页的控制策略
- 需要合理设置innodb_io_capacity参数，告诉InnoDB全力刷磁盘的IO上限。

全字段排序
rowid排序（相比全字段排序多一次回表）
优化：在order by的字段上增加索引。
- 无条件查询如果只有order by create_time，即便create_time上有索引，也不会使用到。因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
- 无条件查询但是是order by create_time limit m。如果m值较小，是可以走索引的。因为优化器认为根据索引有序性去回表查数据,然后得到m条数据，就可以终止循环，那么成本比全表扫描小,则选择走二级索引。