MySQL索引详解及演进过程及面试题延伸(2)-木庄网络博客

当前第2页返回上一页

如果是有序增加，新增一条数据怎么办？
页写满了，那么是不是得开启一个新页！
并且页的数据必须满足一个条件：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
因为是有序增加，我们直接在页的双向链表末端增加一个页即可。
那如果是无序增加，新增一条数据怎么办？

开启一个新页，并且找到数据的位置。
把旧数据移动到新页，把新的数据放到有序的位置上。
叶子结点数据一直平移。
触发叶子结点数据Page页的分裂与合并触发上层叶结点和根结点的再次分裂与合并。
这叫什么，“牵一发而动全身”,也叫做页分裂！！

总结：Page页出现增加、修改、删除遇到的问题：

我们可以说，当无序增加、更新主键ID、删除索引页的更新操作时候，会有大量的树结点调整，触发子叶结点Page页和上层叶结点和根节点页的分页与合并，造成大量磁盘碎片，损耗数据库的性能，也就是解释了我们为什么不要在频繁更新修改的列上建索引，或者是不要去更新主键。

让我们总结一下：

聚集索引（聚簇索引）:

主键索引树也叫聚集索引或者是聚簇索引，在InnoDB中一张表只有一个聚集索引树，如果一张表创建了主键索引，那么这个主键索引就是聚集索引，我们是根据聚集索引树的键值，决定数据行的物理存储顺序，我们的聚集索引会对表中的所有列进行排序存储，索引即数据，数据即索引，指的就是我们的主键索引树啦。

2.5根据我们刚才推演的，延申出几个面试题

为什么主键ID最好是趋势递增的？

你刚刚看完啊，不会没记住吧，有序递增，下一个数据页中用户记录的主键值必须大于上一个页中用户的主键值，假如我是趋势递增，存入的数据肯定是在最末尾链表或者新增一个链表，就不会触发页的分裂与合并，导致添加的速度变慢。

三层B+数能存多少数据？

考察点：Page页的大小，B+树的定义
1GB = 1024 M， 1mb = 1024k，1k= 1024 bytes

答：
已知：索引逻辑单元 16bytes 字节，16KB=16* 1024*1024，肯定比一千万多，在InnoDB中B+树的深度为3层就能满足千万级别的数据存储。

mysql 大字段为什么要拆分？

一个Page页可存放16K的数据，大字段占用大量的存储空间，意味着一个Page页可存储的数据条数变少，那么就需要更多的页来存储，需要更多的Page，意味着树的深度会变高。那么磁盘IO的次数会增加，性能下降，查询更慢。大字段不管是否被使用都会存放在索引上，占据大量内存空间压缩Page数据条数。

为什么用B+树？

B+树的底层是多路平衡查找树，对于每一次的查询的都是从根节点触发，到子叶结点才存放数据，根节点和非叶子结点都是存放的索引指针，查找叶子结点互，可以根据键值数据查询。扫库、扫表能力更强排序能力更强查询效率和查询性能稳定存储能力更强、三层B+树就能存储千万级别的数据。

3什么是二级索引树

刚才看的是根据主键得来的索引，我们如果不查主键，或者说表里压根就没有主键，怎么办？我们还可以根据几个字段来创建联合索引(组合索引聚合索引。。哎呀名字而已怎么叫都行)。

根据主键得到的索引树叫主键索引树，根据别的字段得到的索引树叫二级索引树。

通过下面的SQL 可以建立一个组合索引

ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');

其实，看似建立了1个索引，但是你使用 age 查询 age，user_name 查询 age，user_name，phone 都能生效
您也可以认为建立了三个这样的索引：

ALTER TABLE INNODB__USER ADD INDEX
SECOND_INDEX_AGE__USERNAME_PHONE('age');
ALTER TABLE INNODB_USER ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name');
ALTER TABLE `INNODB_USER`ADD INDEX
SECOND_INDEX_AGE_USERNAME_PHONE('age','user_name','phone');

3.1那么二级索引树怎么排序？

首先需要知道参与排序的字段类型是否有有序？

如果是有序字段，就按照有序字段排序比如（int） 1 2 3 4。
如果是无序字段，按照这个列的字符集的排序规则来排序，这点不去深入，知道就好。

我现在有一个组合索引（A-B-C）他会按照你建立字段的顺序来进行排序：
如果A相同按照B排序，如果B相同按照C排序，如果ABC全部相同，会按照聚集索引进行排序。

我们的Page会根据组合索引的字段建立顺序来存储数据，年龄用户名手机号。
它的数据结构其实是一样的

3.2索引桥的概念是什么呢（最左匹配原则）？

还是上面那个索引，年龄用户名手机号，age,username,phone
那么可以看到我们第一个字段是AGE，如果需要这个索引生效，是不是在查询的时候需要先使用Age查询，然后如果还需要user_name，就使用user_name。

只使用了user_name 能使用到索引吗？
其实是不行的，因为我是先使用age进行排序的，你必须先命中age，再命中user_name，再命中phone，这个其实
就是我们所说的最左匹配原则。

最左其实就是因为我们是按照组合索引的顺序来存储的。大家常说的"索引桥"也是这个原因。命中组合索引必须是像过桥一样，必须现在从第一块木板走到第二块木板再走到第三块木板。

3.3回表、覆盖索引、索引下推

二级索引树有三个重要的概念，分别是回表、覆盖索引、索引下推。.

回表就是：我们查询的数据不在二级索引树中需要拿到ID去主键索引树找的过程。

覆盖索引就是：我们需要查询的数据都在二级索引树中，直接返回这种情况就叫做覆盖索引。
索引下推（index condition pushdown ）简称ICP：在Mysql5.6以后的版本上推出，用于优化回表查询；
可以参考我写的另一篇博客：有详细介绍

链接: MySQL 回表,覆盖索引,索引下推

看完二级索引，

3.4延申几个面试题：

为什么离散度低的列不走索引？

离散度是什么概念？相同的数据越多离散度越低，相同的数据越少离散度就越高。
请问都是相同的数据，怎么排序？没办法排序啊？
在B+Tree 里面重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建立了索引也不会走。走不走索引，是MySQL的优化器去决定的。

索引是不是越多越好？

空间上：用空间换时间，索引是需要占用磁盘空间的。
时间上：命中索引，加快我们的查询效率，如果是更新删除，会导致页的分裂与合并，影响插入和更新语句的响应时间，反而延缓性能。
如果是频繁需要更新的列，不建议建立索引，因为频繁触发页的分裂与合并。

3.5二级索引树的总结

也叫作组合索引（复合索引），二级索引树存储的是我们创建索引时候的保存了列名顺序来存储的，它只保存了创建二级索引列名的部分数据，二级索引树是为了辅助我们查询，提高查询效率诞生的，二级索引树里有三个动作：回表、覆盖索引、索引下推。其中，性能最高的是覆盖索引。

4主键索引与二级索引的区别

网上找了一张区别图

到此这篇关于MySQL索引详解及演进过程以及延申出面试题的文章就介绍到这了,更多相关MySQL索引内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL索引详解及演进过程及面试题延伸

MySQL索引详解及演进过程及面试题延伸