mysql 索引的理解

深藏阁楼爱情的钟 2022-04-22 16:44 218阅读 0赞

1，索引 在数据库里也可以叫作‘键’ 主键，唯一，普通索引。存储引擎可以通过索引来快速查到记录的一种数据结构，当表中的数据量越来越大的时候，索引的作用越发的重要

2，考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，**当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内**，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

3，索引分类  
mysql 支持多种存储引擎 每个存储引擎使用的索引各不相同 大概有b+tree  ，全文索引，hash索引

4，b-tree 和 b+tree区别

![20190114153357925.png][]

btree：平衡搜索多叉树 非叶子和叶子节点存储key 已经data

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pheWxhb3pob3U_size_16_color_FFFFFF_t_70][]

b+tree 和b-tree区别在于：b+tree 非叶子节点只存储key 叶子节点才存储data

如上图所示 浅蓝色区域我们称之为磁盘块 ，每个磁盘块包含几个数据项（深蓝色）和指针（黄色） 如：磁盘块1包含 17 35数据项 还有p1 p2 p3三个指针 p1表示小于17的磁盘块 p2 表示大于17小于35的磁盘块 p3表示大于35的 真实的数据存储在叶子节点 3 ，5,9,10,13,15，非叶子节点不存数真实数据

查找过程：

如上图所示，如果要想查找数据项29 那么首先会先将磁盘块1加载到内存中，此时发生一次I/O,在内存中通过二分法迅速找到29在17和35之间，锁定磁盘块1的指针p2（内存查询时间非常短的相对于磁盘查询可忽略不计）通过磁盘块1中的p2指针的磁盘地址将磁盘块3由磁盘加载到内存中 发生第二次IO29在26和30之间 锁定p2指针，通过该指针将磁盘块8加载到内存中 发生第三次IO，内存中二分法查到到29 查询over 总共三次io 3层的b+tree可以存储几百万的数据量，上百万的数据只需三次io 性能提升是比较大的如果没有索引 那么要上百万此io

从中我们能发现 树的高度决定了磁盘io的次数，所以同样的数据量的话 怎么才能让树高度低呢，很显然就是每块磁盘块存储的数据量越大树的高度越低

假设树的高度h

表数据总量N

每个磁盘块数据项是m

则有h=㏒(m+1)N N不变的话m越大 h越小  m = 磁盘块的大小/数据项的大小 磁盘块大小就是一个数据页的大小，是固定不变的 如果数据项占的空间越小，那么存的数据项数量就越多，树的高度越低。这也是为什么索引字段要尽量的小 同样的int（4)要比int（8）少一半 ，这也不难解释为啥b+tree非叶子节点不存真实数据

索引的最左匹配特性（从左往右匹配）：当使用了复合索引的时候 比如（name,age,phone) b+tree 时从左往右的顺序来建立索引树 当（jay ,12,18999999999)这样的数据来检索的时候 会优先比较name确定下一步的搜索方向，如果name相同的话再比较age 和 phone。 但是如果是（12,189999999）这样来检索的时候 那么btree就不知道下一步该查询哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索判断下一步的方向所有这就用不到索引了

如果（jay ，189999999）这样的数据来搜索时 会先用name来指定搜索方向，但是age没有 那么只能把名字=jay 的所有数据都找到然后再匹配phone的数据

5，创建索引的原则

索引并不是越多越好，创建合适的索引才能起到好的效果

1,必须得符合最左匹配原则

2，尽量给那些离散度高的字段添加索引 count(distinct col)/count(\*)  离散度高的比如姓名 电话  离散度低的不如性别 就两种

如果离散度低的字段添加索引 查询的时候 查询优化器反而会认为走全表更加快

3，索引列不能参与计算

[20190114153357925.png]: /images/20220327/7db8c046295a46a7b1e51b4cb5e4b772.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pheWxhb3pob3U_size_16_color_FFFFFF_t_70]: /images/20220327/8ce9e1acf0de460cb245feaf81ff742a.png