简要列出知识点,仅供个人学习记录。
一、基础篇
7. 行锁功过:怎么减少行锁对性能的影响?
- 两阶段锁:需要时获取,事务提交时释放。
- 如果没有其他要求,可以将事务中热点行的操作放在最后,最大程度减少事务之间的锁等待,提高并发度。
- 死锁:
- 进入等待,直到超时。(不建议,超时时间默认50s)
- 发起死锁检测,检测到死锁后,主动回滚其中一个事务,让其他事务得以执行。
- 死锁检测消耗CPU资源。
- 死锁检测是针对行锁的,如果有n个并发线程被堵住,则检测复杂度会是O(n2)。
- 可以分表来降低死锁检测的CPU消耗,比如一个总账户分拆10个表。
二、实践篇
9. 普通索引和唯一索引,应该怎么选择?
普通索引和唯一索引在查询能力上没差别,更新性能上可能会是普通索引更好,尽量选择普通索引。
- 性能对比普通索引和唯一索引
- 查询性能
- 普通索引查询到目标 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消耗。
10. MySQL为什么有时候会选错索引?
- 出现场景
- 删除历史数据、新增数据后可能会选错索引。
- 优化器的逻辑
- 代价考虑因素:扫描行数、是否使用临时表、是否排序等。
- 扫描行数是采样统计估算出的,可能不准。
- 索引上的不同的值称为基数(可以理解为区分度)。
- 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
则优化器会重新考虑代价大小。
- 新建更合适的索引,或者删除被误用的索引。
11. 怎么给字符串字段加索引?
- 完整索引
- 占用空间。
- 前缀索引
- (相比完整字段)会增加扫描行数。
- (相比完整字段)无法覆盖索引,需要回表。
- 倒序存储+前缀索引(不支持范围查询)
- 比如身份证这类尾部区分度大的情况,可以避免前缀区分度不大的情况。
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'
12. 为什么我的MySQL会“抖”一下?
- 平时执行很快的更新操作,就是在写内存和日志。
- 抖一下是在刷脏页(flush):
- redo log写满了。
- redo log的checkpoint向前推进,推进部分对应的所有脏页都要flush到磁盘上。
- 内存满了。
- 需要新的内存页,淘汰一些数据页,如果淘汰的是脏页,就需要先将脏页flush到磁盘上。
- redo log写满了。
- 还有两种情况下也会刷脏页,但不会被感知到:
- MySQL空闲时。
- MySQL正常关闭时。
- InnoDB刷脏页的控制策略
- 需要合理设置innodb_io_capacity参数,告诉InnoDB全力刷磁盘的IO上限。
13. count(*)这么慢,我该怎么办?
14.
15.
16. order by是怎么工作的?
- 全字段排序
- rowid排序(相比全字段排序多一次回表)
- 优化:在order by的字段上增加索引。
- 无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
- 无条件查询但是是order by create_time limit m。如果m值较小,是可以走索引的。因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。