26丨索引的使用原则：如何通过索引让SQL查询效率最大化？

陈旸



该思维导图由 AI 生成，仅供参考

我之前讲了索引的使用和它的底层原理，今天我来讲一讲索引的使用原则。既然我们的目标是提升 SQL 的查询效率，那么该如何通过索引让效率最大化呢？
今天的课程主要包括下面几个部分：
什么情况下使用索引？当我们进行数据表查询的时候，都有哪些特征需要我们创建索引？
索引不是万能的，索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引？
创建了索引不一定代表一定用得上，甚至在有些情况下索引会失效。哪些情况下，索引会失效呢？又该如何避免这一情况？
创建索引有哪些规律？创建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：
1. 字段的数值有唯一性的限制，比如用户名
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
我之前列举了 product_comment 数据表，这张数据表中一共有 100 万条数据，假设我们想要查询 user_id=785110 的用户对商品的评论。

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本文总结了如何通过索引让SQL查询效率最大化的原则。首先，文章介绍了何时需要创建索引，包括字段具有唯一性限制、频繁作为WHERE查询条件的字段、需要经常GROUP BY和ORDER BY的列、UPDATE、DELETE的WHERE条件列以及DISTINCT字段。其次，文章强调了创建索引的规律，包括对字段创建索引以提升查询效率，并且在多表JOIN连接操作时需要注意连接表的数量、对WHERE条件和用于连接的字段创建索引的原则。最后，文章通过具体的案例分析了创建索引对SQL查询效率的影响，强调了合理创建索引对提升数据库查询性能的重要性。文章还提到了避免索引失效的方法，包括避免对索引进行表达式计算、避免对索引使用函数、注意在WHERE子句中的条件列是否都进行了索引等。总的来说，文章强调了合理创建和使用索引对提升数据库查询性能的重要性，同时也指出了避免索引失效的方法，为读者提供了实用的技术指导。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《SQL 必知必会》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(60)

最新
精选

wusiration
索引失效，因为使用了date函数。改成SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_time BETWEEN DATE('2018-10-01 10:00:00') AND DATE('2018-10-02 10:00:00')
作者回复: Good Job
2019-08-09
9
50
Yuhui
老师您好！请教一下如何查找“不经常使用的“索引呢？谢谢！
作者回复: 你可以查看下MySQL中的performance_schema.table_io_waits_summary_by_index_usage数据表，它表明了每个索引进行统计的I/O等待事件，其中COUNT_STAR代表了事件的次数。过滤掉一些系统表，查看下数据表中有哪些索引不经常使用，具体的SQL语句：SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,COUNT_STAR FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA != 'mysql' AND OBJECT_SCHEMA != 'performance_schema'
2019-09-19

35
佚花
关于like. %在左边，即使有索引，也会失效. 只有当%在右边时，才会生效
作者回复: 对的如果%在左侧就是前模糊匹配，有索引也会失效。所以字符串进行匹配的时候，需要最左侧是个明确的字符才能使用上索引。
2019-08-21

8
haer
索引失效，因为使用了date函数
作者回复: 对的
2019-08-09

7
Vackine
关于关系型数据库模型介绍的论文，老师有推荐的么✨
作者回复: 一本经典的书《Towards a Logical Reconstruction of Relational Database Theory》另外关系型数据库里面也有不同的使用场景，比如关于图像检索的《Chabot: Retrieval from a Relational Database of Images》查询XML的：《Storing and querying ordered XML using a relational database system》
2019-08-09

5
Goal
老师，今天文章中的“product_comment”表结构和数据，是从哪里导入的呢？个人感觉，本课程用到的所有表都可以放到一个统一的地方，比如之前的 GitHub上面，方便我们统一下载。
作者回复: 因为文件大于100M，就先放到百度网盘：https://pan.baidu.com/s/1LBEAm50DDP9AjErLtGplLg 提取码：32ep 多谢建议，我把它也放到GitHub上，大于100M的给出百度网盘的链接
2019-08-13

4
ABC
索引会失效，因为使用了date函数。如果修改的话，可以用between和and，对查询条件进行转换。例如:currtime between date('2018-01-10 10:00:00) and date('2018-02-10 12:00:00') 手机回复，没有实际运行，如有错误请老师指正，谢谢
作者回复: Good Job
2019-08-09

4
rike
“按照 user_id 进行评论分组，同时按照评论时间降序的方式进行排序”，执行对应的sql后，报错。望大神验证一下，不要误导付费学习的读者。
作者回复: 应该是可以运行的，rike同学再试试
2019-12-27
3
3
抢小孩子糖吃
老师如果我们给女儿国的性别加上了索引我们查看男性的话会快很多但如果我们有时需要查看男性有时需要查看女性还适合在性别上建索引吗查看女性的时候优化器会选择用这个索引找数据吗
作者回复: 这个看需求，对于某个疾病（发病率很低）的正样本，可以选择创建索引。如果性别男女都需要查看，可以不建索引，对于女性，优化器也不会使用索引进行查找。
2019-11-23

3
niemo
老师您好，sql条件执行顺序不是从右到左么？所有在使用联合索引的时候，把最左的索引写在where条件的最右边，这样理解对么？
作者回复: SQL执行顺序： SELECT DISTINCT player_id, player_name, count(*) as num #顺序5 FROM player JOIN team ON player.team_id = team.team_id #顺序1 WHERE height > 1.80 #顺序2 GROUP BY player.team_id #顺序3 HAVING num > 2 #顺序4 ORDER BY num DESC #顺序6 LIMIT 2 #顺序7
2019-08-09

3

收起评论