mysql索引总结

Hongjay
1,109 阅读7分钟

最近一直在看mysql相关的知识，恰好索引有事数据库基本知识中比较重要的一个，看了一些博客书籍，现在大概对索引有一个比较清晰的了解，所以综合整理了一下来分享给大家。

开头

  • mysql的Innodb引擎目前支持以下几种常见的索引
    • B+树索引
    • 全文索引
    • 哈希索引
  • 哈希索引看名字就知道是基于哈希算法，Innodb引擎支持的哈希索引是自适应的，就是说Innodb引擎会根据数据表的使用情况自动的为表创建哈希索引，所以是不需要我们去人为操作的，所以不是讲解的重点。本文主要着重讲解B+树索引。
  • B+树索引并不能直接找到一个给定键值的具体行，而是找到数据行所在的页，然后找到的页读进内存，在内存中进行比较最终找到确定的那一行。

B+树的简单介绍

  • B+数是一种平衡查找树（平衡可对比二叉平衡树）具体构造如下图

image.png

  • B+树的非叶子节点不存储数据，叶子节点才会存储数据，每一个叶子节点都表示一个页，一页里面有多行数据，所以很好理解上面所说的B+树索引并不能直接找到一个给定键值的具体行，而是找到数据行所在的页，因为B+树查找只能定位到叶子节点，叶子节点内的比较就需要读进内存，在内存中进行比较最终找到确定的那一行。
  • 同时B+树因为需要维持平衡，所以增删叶子节点的数据时需要根据情况来旋转或者分裂节点等操作来进行B+树的平衡维持操作（具体怎么操作这里不做具体讲解有兴趣可以看这篇博客https://zhuanlan.zhihu.com/p/27700617）。

索引提高查询速度的原理

  • 其实就是将无序的数据变成有序(相对)：
    image.png
  • 要找到id为8的记录简要步骤：
    image.png
  • 很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过**“目录”**就可以很快地定位到对应的页上了！

聚集索引

  • 当一张表存在主键时就以主键构造一颗B+树创建聚集索引
  • 一张表只能有一个聚集索引，因为实际的数据页只能按照一颗B+树来进行排序
  • 聚焦索引页上存放的是完整的每行的记录，在非聚集索引的B+树上面存放的是指向主键页的地址和列值。
  • 聚集索引的存储并不是物理连续的，而是逻辑连续的因为聚集索引的页与页之间是双向链表连接，页按照主键的顺序排序，每个页里面的记录也是通过双链表链接，所以物理存储上可以不按照主键顺序排序。
  • 聚集索引对主键的排序查找和范围查找速度很快，并且页上就是完整的行数据，不需要进行二次查找。对聚集主键的范围查找只需要通过叶子节点的上层节点就可以找到页的范围，直接取页数据即可。

辅助索引（非聚集索引、二级索引）

  • 上面说了非聚集索引的页上面只存储了主键地址和单独的列值，索引当根据非聚集索引查询的时候如果返回字段涉及到该列以外的其他列就会在根据主键的指针定位到主键索引所在的页查询查找其他列数据（所以到主键查找这个过程会有一定的查找性能损失，这个过程也叫做回表）。
  • 一个数据表只有一个聚集索引，但是可以有很多个非聚集索引。（但是过多的不必要的索引会影响数据库性能）

什时候应该添加索引

  • 不是所有的查询条件出现的列都需要添加索引。对于什么时候添加B+树索引。一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段，他们可取值范围很小，称为低选择性。如
SELECT * FROM student WHERE sex='M'

按性别进行查询时，可取值一般只有M、F。因此SQL语句得到的结果可能是该表50%的数据(假如男女比例1:1)这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，属于高选择性。则此时使用B+树的索引是最合适的。例如对于姓名字段，基本上在一个应用中不允许重名的出现

  • 怎样查看索引是否有高选择性？通过SHOW INDEX结果中的列Cardinality来观察。非常关键，表示所以中不重复记录的预估值，需要注意的是Cardinality是一个预估值，而不是一个准确值基本上用户也不可能得到一个准确的值
  • 可以参考cardinality值来决定是否应该删除该索引（如果cardinality值很小可以考虑删除该索引）。

覆盖索引

  • 覆盖索引就是在从辅助索引中就可以得到查询记录不需要查询聚集索引（查询的列和建立索引的列是对应的）

索引的最左匹配原则

  • 在说索引最左匹配原则之前，需要了解一个东西就是联合索引，就是有一个列a，一个列b，可以对a,b两列建一个索引（a，b）(当然两个以上也是可以的，在索引的内部就会根据（a,b）来排序，一般来说是先根据a排序，然后再根据b排序）
  • 当根据a的值来做条件查询的时候可以使用使用(a,b)索引(因为(a,b)索引是先根据a索引排序再根据b索引排序)，但是单独使用b做条件查询的时候就不能使用(a,b)索引（原因同上，只有在a条件确定的情况下才开始使用b）
  • 同时索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。（比如有（a,b,c,d）联合索引当查询条件为a=1 ,b=2,c>8，d=9）这个时候根据索引建立顺序就会依次匹配到a,b到c的时候就不会继续往下匹配，即使d不是范围查询，匹配玩a，b索引后就退化为线性查找。

索引总结

  • 最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。

  • 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

  • 索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2016-06-06')。

  • 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

  • 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

  • 参考资料：

avatar
文章
阅读
粉丝