Mysql索引模型采用B+树
主键索引与非主键索引
image.png从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。
基于主键索引和普通索引的查询有什么区别? - 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
- 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
覆盖索引
由于覆盖索引避免回表操作,可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
最左前缀原则
最大程度复用索引
image.png
SELECT * FROM test_like WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%',哪几种情况有借助于索引加速查询
索引下推
减少回表操作
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
image.png
普通索引与唯一索引
- 查询
- 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到碰到第一个不满足 k=5 条件的记录。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
那么,这个不同带来的性能差距会有多少呢?答案是,微乎其微。
- 写入
假如数据不在内存页,普通索引可以利用change buffer,避免随机读;唯一索引只能读入磁盘数据,做冲突检测。
changebuffer无法用在主键索引,但是他可以用在非主键非唯一索引(每颗B+树的插入都是独立的);但是对于读多写少的场景,并不适用。 - 举例
-
写入
image.png -
读入
image.png
changebuffer操作应用于数据页的过程叫merge,适用于写多读少的场景
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
网友评论