1.mysql索引的数据结构及使用

红太狼 2022-04-10 03:26 205阅读 0赞

# 1.索引所用的数据结构 #

目前常用的数据库为mysql数据库以innodb为引擎所建的表，对于这种情况，95%以上的情况使用的是B+Tree索引结构。

B+树是对B树的一种变形树，它与B树的差异在于：  
有k个子结点的结点必然有k个关键码；  
非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。  
树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。  
如下图，是一个B+树:  
![在这里插入图片描述][20181224152541580.png]  
B和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。  
B+ 树的优点在于：  
由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。  
B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

# 2.索引的优点 #

众所周知，对磁盘的IO操作消耗远大于对内存IO（差距通常达到成千上万倍），索引可以让服务器快速定位到表的指定位置，以B+Tree索引为例，按照顺序存储数据，所以Mysql可以用索引来做order by和group的操作。因为数据是有序的，所以B+Tree也会把相关的列值存储在一起。又因为索引中存储了实际的列值，所以对于某些查询来说，只使用索引就可以完成全部查询工作。总结下来，索引有以下三个优点：

*  索引极大减少了服务器需要扫描的数据量。
 *  索引可以帮助服务器避免排序和临时表。
 *  索引可以将随机IO变成有序IO。

# 3.索引的成本和性价比计算 #

索引的成本也就是需要维护这么一个B+Tree，本身插入和删除数据的过程也需要增加维护B+Tree的开销。  
如果字段毫无区分度，那么这个索引实际上也并无什么使用价值，所以说索引的NPV计算与下列数据均有相关性

*  使用索引的频率
 *  使用索引节省下的成本
 *  对于查询的延迟敏感度
 *  增删的频率
 *  增删时维护索引的成本
 *  对于增删的延迟敏感度  
    大体来看，可以这么简略的计算  
    NPV = 对于查询的延迟敏感度 X 使用索引的频率 X 使用索引节省下的成本 - 对于增删的延迟敏感度 X 增删的频率 X 增删时维护索引的成本

# 4.有效的使用索引 #

由以上公式可得出，建立索引应当满足以下原则

## 4.1 建立索引的原则: ##

*  比起增删来说，查询的频率很高
 *  比起增删的延迟时间来说，用户更在意查询的延迟时间
 *  使用索引节省下的成本应尽量高
 *  维护索引的成本应尽量低

## 4.2 使用索引需要注意的点: ##

*  最左前缀匹配原则
    
    select \* from s1 where name=‘egon’; \#可以  
    select \* from s1 where name=‘egon’ and email=‘asdf’; \#可以  
    select \* from s1 where email=‘alex@oldboy.com’; \#不可以
    
    mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，  
    比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，  
    d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
 *  =和in可以乱序  
    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器  
    会帮你优化成索引可以识别的形式
 *  尽量选择区分度高的列作为索引，一般以区分度0.1左右为标准  
    使用索引限制前缀时应反复计算,区分度也不宜太高
 *  索引列不能参与计算  
    这样每一个元素都会计算,查询成本会急剧升高
 *  避免使用select \*  
    这样会导致索引无法覆盖需要查询的列
 *  like %xxx  
    这种形式无法使用索引，而like xxx%则可以
 *  排序条件为索引，则select字段必须也是索引字段，否则无法命中  
    如果排序条件为主键也会走索引
 *  !=条件不会使用索引  
    如果条件为主键也会走索引
 *  组合索引代替多个单列索引（经常使用多个条件查询时）  
    视具体业务条件确定
 *  尽量使用短索引

[20181224152541580.png]: /images/20220403/3f93595a08c54628b6999c615517b196.png