MySQL笔记(实践篇)
普通索引和唯一索引,应该怎么选择?
select id from T where k=5
查询过程(差别不大)
通过普通索引的查询过程:查找到k=5记录,接着查找下一条记录直到不满足条件
通过唯一索引的查询过程:查找到k=5记录后直接返回
由于查询过程是把整个数据页(默认16kb)读入内存,普通索引会多一次指针寻找和计算,如果刚好记录在数据页最后,则要多读取一个数据页,而这影响微乎其微
更新过程(写多读少的表建议使用普通索引)
通过普通索引的更新过程:当数据在内存中则直接更新;如果不在内存中,则将更新写入change buffer(在从数据库读取数据后,会执行meige操作,将change buffer的更新作用到数据上,这样才能得到一致的数据)
通过唯一索引的更新过程:当数据在内存中则直接更新;如果不在内存中,由于要判断数据是否唯一,也需要将数据页读入内存中,既然数据页都读入内存中了,直接更新就好,不需要使用change buffer
change buffer减少了随机磁盘访问,性能提升明显
对于写多读少的系统,更新写入change buffer不会立即触发merge,对性能提升
对于写少读多的系统,更新写入change buffer后马上及触发merge,反而影响性能
注意redo log 和 change buffer的区别
redo log减少随机写磁盘的操作(WAL,先写日志有空再写入磁盘)
change buffer减少随机读磁盘的操作(更新的时候不读数据)
MySQL为什么有时候会选错索引?
索引上不同的值越多,区分度就越大
当MySQL选错索引时,可以用force index(索引名)强制使用索引
由于索引统计信息不准确导致的问题,可以用 analyze table 来解决
怎么给字符串字段加索引?
完整索引
如果字段较长的话,比较占空间
前缀索引
节省空间
增加扫描次数且无法使用覆盖索引(每次都要回表判断条件是否符合)
倒序存储
对于身份证这种前缀区分度不够的字段,可以使用倒序取后6位的方式设置索引,查询时使用如下语句
select field_list from t where id_card = reverse('input_id_card_string');
不支持范围查询、额外CPU消耗(reverse函数)
hash字段
额外增加一个hash字段作为索引
不支持范围查询
为什么我的MySQL会“抖”一下?
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。
内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。
平时执行很快的更新操作,其实就是在写内存和日志,而 MySQL 偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)
以下几种情况刷脏页
- redo log写满了
- 内存不足
- 系统空闲时
- MySQL正常关闭
通过innodb_io_capacity控制innodb刷脏页的速度
innode_flush_neighbors参数,控制刷脏页时是否把邻近的脏页也顺便一起刷了(1是,0否),8.0版本中默认0
如果是固态硬盘,建议设置为0