索引的使用规则和设计考虑

偏执的太偏执、 2022-08-28 01:51 245阅读 0赞

当我们建立好一个联合索引之后，我们的SQL语句要怎么写，才能让他的查询使用到我们建立好的索引呢？

索引最基本的规则就是等值匹配了，就是where条件里的查询条件字段和联合索引的字段完全一样，都是基于等于来匹配的。

**最左侧列匹配**，假如我们设计的索引是INDEX(class\_name, student\_name, course\_name)，查询的SQL语句where里不一定要根据三个字段来查询，只要最左侧的部分字段来查，就可以了

比如：select \* from student\_score where class\_name='xx' and student\_name='xx'，只有class\_name和student\_name可以在索引里搜索，就可以查询某个学生所有科目的成绩。

但如果你SQL是select \* from where course\_name='xx’，那就不能走索引了，因为B+树里必须先按class\_name查找，再按student\_name查找，不能跳过前面两个字段。

**最左侧匹配规则**，假如你要用like语法来查，比如select \* from student\_socre where class\_name like '1%'，查找所有1开头的班级的分数，那么也是可以用索引的。

因为你的联合索引的B+树里，是按照class\_name排序的，所以你要是给出class\_name的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的。

但如果你where条件是class\_name like '%班'，左侧是模糊查询，就没法用索引了。

**范围查找**，假如你查询的SQL是：select \* from student\_score where class\_name>'1班’ and class\_name<'5班'，查找几个班的分数。也是可以用到索引的。

因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！

**ORDER BY如何使用上索引？**

假设你有一个查询：select \* from table where xxx=xxx order by xxx，似乎应该是基于where语句通过索引快速筛选出来一波数据，接着放到内存里，或者放在一个临时磁盘文件里，然后通过排序算法按照某个字段来一个排序，最后把排序好的数据返回。

如果是这样的话，肯定就会比较慢，所以最好别这么搞。

尤其是类似于select \* from table order by xx1,xx2,xx3 limit 10，这样的SQL语句，查出一批数据后，按照多个字段进行排序，最后返回前10条数据，类似的语句其实常常见于分页SQL语句里。

这种情况，我们可以建立一个联合索引INDEX(xx1, xx2, xx3)，这时候数据在索引里默认是按照xx1,xx2,xx3排序的，就不需要查出一批数据再在内存里或磁盘里做复杂的排序工作了。

直接拿到10条数据的主键去聚簇索引里回表查询剩下的字段。

所以，你的SQL里最好是按照联合索引的字段顺序去进行order by排序，这样就可以直接利用联合索引的数据有序性，到索引树里直接按照字段值的顺序去获取数据了。

**GROUP BY如何使用上索引？**

那假设你有这样一个查询：select count(\*) from table group by xx语句，看起来必须先查出一批数据，然后数据放到一个临时磁盘文件里还有加上部分内存，按照指定字段的值分成一组一组的，接着对每一组都执行一个聚合函数，这个性能也是极差的，因为毕竟涉及大量的磁盘交互。

在索引树里数据都是按照指定的一些字段都排序好的，其实字段值相同的数据都是在一起的，假设要是走索引去执行分组后再聚合，那性能一定比临时磁盘文件去执行好多了。

所以，group by后的字段，最好也是按照联合索引里最左侧的字段开始，按照顺序排列的，这样就可以用上索引来提取一组一组的数据了。

order by和group by用上索引的原理差不多，都是依赖索引数据的顺序性。

**索引设计的考虑因素**

**一般建立索引，尽量使用那些区分度比较大的字段**，那么才能发挥出B+树快速二分查找的优势来。

什么是区分度比较大？

就是你表里一个列的值不同的越多，区分度越大，值不同的越少，区分度越小。比如订单号，订单表每条数据的订单号都不一样，所以它的区分度大，而性别字段一般就男，女，未知，三个值，区分度就比较小。

要是针对区分度小的字段建立索引，会查出一大批数据，没有太大意义。

**尽量对字段类型比较小的列设计索引**，比如说tinyint之类的，因为他的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时你在搜索的时候性能也会比较好一点。

如果你的字段是name VARCHAR(255)，你可以针对字段的前20个字符建立索引，就是说，对这个字段的每个值的前20个字符放在索引树里。

但此时，你order by name就没法用上索引了，group by name也是一样的道理。因为你索引树里仅仅包含了前20个字符。

**函数与索引**

假设你设计好了索引，但你SQL里这么写，where sum(xxx)=xx，你给你索引里的字段xxx使用了函数，还能用上索引吗？

不能了，所以尽量不要让你的查询语句里的字段搞什么函数。

现在设计索引的时候需要注意的点都已经讲完了，其实就是好好设计索引，让你的查询语句都能用上索引，同时注意一下字段基数、前缀索引和索引列套函数的问题，尽量让你的查询都能用索引，别因为一些原因用不上索引了。

由于索引本身要占用存储空间，增删改的时候也要维护索引本身，索引一般不要设计太多索引，建议两三个联合索引就应该覆盖掉你这个表的全部查询了。

**覆盖索引**

一般二级索引的叶子节点仅仅包含了索引里的几个字段值和主键值，如果你要查询表里的很多字段，就需要回表查询，就是先在二级索引里拿到主键id，再回表根据主键id查出所有字段，这个性能其实也不高。

如果能只在二级索引里查到所有字段，那就完美了，这就要引入覆盖索引的概念了。

覆盖索引不是一种索引，是一种基于索引查询的方式。

假设你有查询select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的语句，这种情况下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要回表去聚簇索引里找其他字段了。

你要查的xx1,xx2,xx3在二级索引里都能提取出来，不需要到聚簇索引里查，这就是覆盖索引。

所以你SQL里最好指定你仅仅需要的几个字段，不要动不动搞一个select \*把所有字段都拿出来，甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引。

即使是必须要回表到聚簇索引，那你也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数，从二级索引里筛选最少的数据，然后再回表到聚簇索引里去，这样性能也会好一些。

有道无术，术可成；有术无道，止于术

欢迎大家关注**Java之道**公众号

![bac61c8354a28879d3736ab51320e8f4.png][]

好文章，我在看❤️

[bac61c8354a28879d3736ab51320e8f4.png]: /images/20220828/54ac56cf79b741c1b1b388ed1501466c.png

索引的使用规则和设计考虑

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ⑩② 【MySQL索引】详解MySQL`索引`：结构、分类、性能分析、设计及使用规则。

相关数据库索引创建规则和优缺点

相关 MySQL高级索引【索引使用&索引设计原则】

相关索引的使用规则和设计考虑

相关 mysql 索引的设计和使用

相关 PVS写缓存容量设计和部署位置的考虑

相关 python中的索引规则

相关 MySql 索引的命中规则

相关第十章 1 索引的设计和使用，创建和删除索引列；索引创建的原则：

相关软件架构的进化过程和设计考虑因素