关于mysql索引的一些疑问和解答
部分内容参考了 MySQL索引-B+树(看完你就明白了)
疑问和解答
- 每创建一个索引都会生成一棵B+树吗? 是的,所以索引不是越多越好,因为生成和维护b+树也是有开销的
- 聚集索引,(也叫聚簇索引)数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。如果存在主键,主键就是聚集索引。若无主键,mysql会自行维护一个隐藏列作为主键索引
- 非聚集索引,(也叫非聚簇索引)(之前理解我错了,以为myisam 的索引都是非聚集索引,innoDB的都是聚集索引,这是错的!)
事实上,不是主键的索引,就是非聚集索引。二级索引、联合索引啥的,都是非聚集索引。非聚集索引的特征是,叶子节点放的是索引列值+主键id值,除此之外,不会存其他字段值 - 为什么建议尽量在建表时,就创建好索引?
这要从索引的本质说起,索引是一种有序的数据结构,是B+树。
如果在表中已有数据的情况下再创建索引,那么原有的数据行就要被重新排列组织成B+树,会非常地耗费时间以及性能 - 回表问题的产生和如何解决回表问题
查询时如果select到主键或者索引列值,则不会触发回表,select到以上以外的字段,就会回表。
解决回表的方式是,生成复合索引(也叫覆盖索引)。
假设现在有个表:有字段id,name,age,其中id是主键,name有索引,age无索引。
如果要执行select name, age from t where name = “xx”时,给name和age字段设置成覆盖索引就可以避免回表了
索引失效场景
- 有or必全有索引;
- 复合索引未用左列字段;
- like以%开头;
- 需要类型转换,比如字符串类型一定要加“ ”;
- where中索引列有运算;
- where中索引列使用了函数;
- 如果mysql觉得全表扫描更快时(数据少);
不适合用索引的场景
- 唯一性差,比如性别字段没必要加;
- 频繁更新的字段不用(更新索引消耗);
- where中不用的字段;
- 索引使用<>时,效果一般;
利用聚集索引查找数据的过程
这部分的内容援引自博客 MySQL索引-B+树(看完你就明白了)
还是这张 B+ 树索引图,现在我们应该知道这就是聚集索引,表中的数据存储在其中。
现在假设我们要查找 id>=18 并且 id<40 的用户数据。对应的 sql 语句为:
MySQL
select * from user where id>=18 and id <40
其中 id 为主键,具体的查找过程如下:
①一般根节点都是常驻内存的,也就是说页 1 已经在内存中了,此时不需要到磁盘中读取数据,直接从内存中读取即可。
从内存中读取到页 1,要查找这个 id>=18 and id <40 或者范围值,我们首先需要找到 id=18 的键值。
从页 1 中我们可以找到键值 18,此时我们需要根据指针 p2,定位到页 3。
②要从页 3 中查找数据,我们就需要拿着 p2 指针去磁盘中进行读取页 3。
从磁盘中读取页 3 后将页 3 放入内存中,然后进行查找,我们可以找到键值 18,然后再拿到页 3 中的指针 p1,定位到页 8。
③同样的页 8 页不在内存中,我们需要再去磁盘中将页 8 读取到内存中。
将页 8 读取到内存中后。因为页中的数据是链表进行连接的,而且键值是按照顺序存放的,此时可以根据二分查找法定位到键值 18。
此时因为已经到数据页了,此时我们已经找到一条满足条件的数据了,就是键值 18 对应的数据。
因为是范围查找,而且此时所有的数据又都存在叶子节点,并且是有序排列的,那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。
我们可以一直找到键值为 22 的数据,然后页 8 中就没有数据了,此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。
④因为页 9 不在内存中,就又会加载页 9 到内存中,并通过和页 8 中一样的方式进行数据的查找,直到将页 12 加载到内存中,发现 41 大于 40,此时不满足条件。那么查找到此终止。
最终我们找到满足条件的所有数据,总共 12 条记录:
(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。
下面看下具体的查找流程图