26丨索引的使用原则：如何通过索引让SQL查询效率最大化？
下载APP
关闭
渠道合作
推荐作者
26丨索引的使用原则：如何通过索引让SQL查询效率最大化？
2019-08-09 陈旸 来自北京
《SQL必知必会》
课程介绍
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="1142" height="640"><rect fill-opacity="0"/></svg>)
讲述：陈旸
时长12:20大小11.30M
我之前讲了索引的使用和它的底层原理，今天我来讲一讲索引的使用原则。既然我们的目标是提升 SQL 的查询效率，那么该如何通过索引让效率最大化呢？
今天的课程主要包括下面几个部分：
什么情况下使用索引？当我们进行数据表查询的时候，都有哪些特征需要我们创建索引？
索引不是万能的，索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引？
创建了索引不一定代表一定用得上，甚至在有些情况下索引会失效。哪些情况下，索引会失效呢？又该如何避免这一情况？
创建索引有哪些规律？
创建索引有一定的规律。当这些规律出现的时候，我们就可以通过创建索引提升查询效率，下面我们来看看什么情况下可以创建索引：
1. 字段的数值有唯一性的限制，比如用户名
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
我之前列举了 product_comment 数据表，这张数据表中一共有 100 万条数据，假设我们想要查询 user_id=785110 的用户对商品的评论。
如果我们没有对 user_id 字段创建索引，进行如下查询：
运行结果：
运行时间为 0.699s，你能看到查询效率还是比较低的。当我们对 user_id 字段创建索引之后，运行时间为 0.047s，不到原来查询时间的 1/10，效率提升还是明显的。
3. 需要经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。
比如我们按照 user_id 对商品评论数据进行分组，显示不同的 user_id 和商品评论的数量，显示 100 个即可。
如果我们不对 user_id 创建索引，执行下面的 SQL 语句：
运行结果（100 条记录，运行时间 1.666s）：
如果我们对 user_id 创建索引，再执行 SQL 语句：
运行结果（100 条记录，运行时间 0.042s）：
你能看到当对 user_id 创建索引后，得到的结果中 user_id 字段的数值也是按照顺序展示的，运行时间却不到原来时间的 1/40，效率提升很明显。
同样，如果是 ORDER BY，也需要对字段创建索引。我们再来看下同时有 GROUP BY 和 ORDER BY 的情况。比如我们按照 user_id 进行评论分组，同时按照评论时间降序的方式进行排序，这时我们就需要同时进行 GROUP BY 和 ORDER BY，那么是不是需要单独创建 user_id 的索引和 comment_time 的索引呢？
当我们对 user_id 和 comment_time 分别创建索引，执行下面的 SQL 查询：
运行结果（运行时间 >100s）：
实际上多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。在这个例子中，我们创建联合索引 (user_id, comment_time)，再来看下查询的时间，查询时间为 0.775s，效率提升了很多。如果我们创建联合索引的顺序为 (comment_time, user_id) 呢？运行时间为 1.990s，同样比两个单列索引要快，但是会比顺序为 (user_id, comment_time) 的索引要慢一些。这是因为在进行 SELECT 查询的时候，先进行 GROUP BY，再对数据进行 ORDER BY 的操作，所以按照这个联合索引的顺序效率是最高的。
4.UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
我们刚才说的是数据检索的情况。那么当我们对某条数据进行 UPDATE 或者 DELETE 操作的时候，是否也需要对 WHERE 的条件列创建索引呢？
我们先看一下对数据进行 UPDATE 的情况。
如果我们想要把 comment_text 为 462eed7ac6e791292a79 对应的 product_id 修改为 10002，当我们没有对 comment_text 进行索引的时候，执行 SQL 语句：
运行结果为 Affected rows: 1，运行时间为 1.173s。
你能看到效率不高，但如果我们对 comment_text 字段创建了索引，然后再把刚才那条记录更新回 product_id=10001，执行 SQL 语句：
运行结果为 Affected rows: 1，运行时间仅为 0.1110s。你能看到这个运行时间是之前的 1/10，效率有了大幅的提升。
如果我们对某条数据进行 DELETE，效率如何呢？
比如我们想删除 comment_text 为 462eed7ac6e791292a79 的数据。当我们没有对 comment_text 字段进行索引的时候，执行 SQL 语句：
运行结果为 Affected rows: 1，运行时间为 1.027s，效率不高。
如果我们对 comment_text 创建了索引，再来执行这条 SQL 语句，运行时间为 0.032s，时间是原来的 1/32，效率有了大幅的提升。
你能看到，对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
不过在实际工作中，我们也需要注意平衡，如果索引太多了，在更新数据的时候，如果涉及到索引更新，就会造成负担。
5.DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
比如我们想要查询商品评论表中不同的 user_id 都有哪些，如果我们没有对 user_id 创建索引，执行 SQL 语句，看看情况是怎样的。
运行结果（600637 条记录，运行时间 2.283s）：
如果我们对 user_id 创建索引，再执行 SQL 语句，看看情况又是怎样的。
运行结果（600637 条记录，运行时间 0.627s）：
你能看到 SQL 查询效率有了提升，同时显示出来的 user_id 还是按照递增的顺序进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 product_comment 表和 user 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
举个例子，如果我们只对 user_id 创建索引，执行 SQL 语句：
运行结果（1 条数据，运行时间 0.810s）：
这里我们对 comment_text 创建索引，再执行上面的 SQL 语句，运行时间为 0.046s。
如果我们不使用 WHERE 条件查询，而是直接采用 JOIN…ON…进行连接的话，即使使用了各种优化手段，总的运行时间也会很长（>100s）。
什么时候不需要创建索引
我之前讲到过索引不是万能的，有一些情况是不需要创建索引的，这里再进行一下说明。
WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子：
因为我们是按照 user_id 来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在 SELECT 字段中。
第二种情况是，如果表记录太少，比如少于 1000 个，那么是不需要创建索引的。我之前讲过一个 SQL 查询的例子（第 23 篇中的 heros 数据表查询的例子，一共 69 个英雄不用索引也很快），表记录太少，是否创建索引对查询效率的影响并不大。
第三种情况是，字段中如果有大量重复数据，也不用创建索引，比如性别字段。不过我们也需要根据实际情况来做判断，这一点我在之前的文章里已经进行了说明，这里不再赘述。
最后一种情况是，频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
什么情况下索引失效
我们创建了索引，还要避免索引失效，你可以先思考下都有哪些情况会造成索引失效呢？下面是一些常见的索引失效的例子：
1. 如果索引进行了表达式计算，则会失效
我们可以使用 EXPLAIN 关键字来查看 MySQL 中一条 SQL 语句的执行计划，比如：
运行结果：
你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多，最终运行时间为 2.538 秒。
为了避免索引失效，我们对 SQL 进行重写：
运行时间为 0.039 秒。
2. 如果对索引使用函数，也会造成失效
比如我们想要对 comment_text 的前三位为 abc 的内容进行条件筛选，这里我们来查看下执行计划：
运行结果：
你能看到对索引字段进行函数操作，造成了索引失效，这时可以进行查询重写：
使用 EXPLAIN 对查询语句进行分析：
你能看到经过查询重写后，可以使用索引进行范围检索，从而提升查询效率。
3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。
比如下面的 SQL 语句，comment_id 是主键，而 comment_text 没有进行索引，因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效：
运行结果：
如果我们把 comment_text 创建了索引会是怎样的呢？
你能看到这里使用到了 index merge，简单来说 index merge 就是对 comment_id 和 comment_text 分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。
4. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %
运行结果：
这个很好理解，如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了。
5. 索引列尽量设置为 NOT NULL 约束。
MySQL 官方文档建议我们尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。
判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 ('')。
6. 我们在使用联合索引的时候要注意最左原则
最左原则也就是需要从左到右的使用索引中的字段，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。我在讲联合索引的时候举过索引失效的例子。
总结
今天我们对索引的使用原则进行了梳理，使用好索引可以提升 SQL 查询的效率，但同时 也要注意索引不是万能的。为了避免全表扫描，我们还需要注意有哪些情况可能会导致索引失效，这时就需要进行查询重写，让索引发挥作用。
实际工作中，查询的需求多种多样，创建的索引也会越来越多。这时还需要注意，我们要尽可能扩展索引，而不是新建索引，因为索引数量过多需要维护的成本也会变大，导致写效率变低。同时，我们还需要定期查询使用率低的索引，对于从未使用过的索引可以进行删除，这样才能让索引在 SQL 查询中发挥最大价值。
针对 product_comment 数据表，其中 comment_time 已经创建了普通索引。假设我想查询评论时间在 2018 年 10 月 1 日上午 10 点到 2018 年 10 月 2 日上午 10 点之间的评论，SQL 语句为：
你可以想一下这时候索引是否会失效，为什么？如果失效的话，要进行查询重写，应该怎样写？
欢迎你在评论区写下你的答案，也欢迎把这篇文章分享给你的朋友或者同事，一起来交流。
分享给需要的人，Ta购买本课程，你将得20元
生成海报并分享赞 15
提建议
© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
25丨Hash索引的底层原理是什么？
下一篇
27丨从数据页的角度理解B+树查询
精选留言(60)
wusiration2019-08-09索引失效，因为使用了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
共 9 条评论49
Yuhui2019-09-19老师您好！请教一下如何查找“不经常使用的“索引呢？谢谢！作者回复: 你可以查看下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'
30
黑山老妖2019-08-10老师 SELECT user_id, count(*) as num FROM product_comment group by user_id order by comment_time desc limit 100 这个例子中 对（comment_time,user_id）进行索引 ，老师不是说按照最左原则，索引会失效嘛 为什么还是会起作用，望老师解答 ：）共 9 条评论11
佚花2019-08-21关于like. %在左边，即使有索引，也会失效. 只有当%在右边时，才会生效作者回复: 对的 如果%在左侧就是前模糊匹配，有索引也会失效。所以字符串进行匹配的时候，需要最左侧是个明确的字符才能使用上索引。
8
haer2019-08-09索引失效，因为使用了date函数作者回复: 对的
7
梦想天空2019-08-10老师 您好。使用selet * from T where a<4 or a=9; a有索引，但还是全盘扫描，不知道什么原因共 5 条评论6
Vackine2019-08-09关于关系型数据库模型介绍的论文，老师有推荐的么✨作者回复: 一本经典的书《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》
5
Andywu2019-08-26去掉date函数后，查看执行计划，索引生效了。 mysql> explain SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_time >= '2018-10-01 10:00:00' AND comment_time <= '2018-10-02 10:00:00'; +----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | product_comment | NULL | range | comment_time | comment_time | 6 | NULL | 2929 | 100.00 | Using index condition | +----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) 查询时间缩短为0.01s。 mysql> SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_time >= '2018-10-01 10:00:00' AND comment_time <= '2018-10-02 10:00:00'; 2929 rows in set (0.01 sec)展开4
Goal2019-08-13老师，今天文章中的“product_comment”表结构和数据，是从哪里导入的呢？ 个人感觉，本课程用到的所有表都可以放到一个统一的地方，比如之前的 GitHub上面，方便我们统一下载。作者回复: 因为文件大于100M，就先放到百度网盘：https://pan.baidu.com/s/1LBEAm50DDP9AjErLtGplLg 提取码：32ep 多谢建议，我把它也放到GitHub上，大于100M的给出百度网盘的链接
4
ABC2019-08-09索引会失效，因为使用了date函数。 如果修改的话，可以用between和and，对查询条件进行转换。 例如:currtime between date('2018-01-10 10:00:00) and date('2018-02-10 12:00:00') 手机回复，没有实际运行，如有错误请老师指正，谢谢展开作者回复: Good Job
4- rike2019-12-27“按照 user_id 进行评论分组，同时按照评论时间降序的方式进行排序”，执行对应的sql后，报错。望大神验证一下，不要误导付费学习的读者。
作者回复: 应该是可以运行的，rike同学再试试
共 3 条评论3 
抢小孩子糖吃2019-11-23老师 如果我们给女儿国的性别加上了索引 我们查看男性的话会快很多 但如果我们有时需要查看男性 有时需要查看女性 还适合在性别上建索引吗 查看女性的时候优化器会选择用这个索引找数据吗作者回复: 这个看需求，对于某个疾病（发病率很低）的正样本，可以选择创建索引。如果性别男女都需要查看，可以不建索引，对于女性，优化器也不会使用索引进行查找。
3
niemo2019-08-09老师 您好，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
3
博弈2020-03-25需要创建索引的情况： 1、 字段值唯一 2、 Where条件 3、 Group by 和order by 4、 Update、delete的where条件 5、 Distinct字段 6、 多表join操作时，表尽量不超过3个，对where条件创建索引，对连接的字段创建索引且类型一致 7、 多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。 不需要创建索引的情况： 1、 WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引 2、 如果表记录太少，比如少于 1000 个，那么是不需要创建索引的 3、 字段中如果有大量重复数据，也不用创建索引，比如性别字段 4、 频繁更新的字段不一定要创建索引 索引失效的情况： 1、 如果索引进行了表达式计算，则会失效 2、 如果对索引使用函数，也会造成失效 3、 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效 4、 当我们使用 LIKE 进行模糊查询的时候，后面不能是 % 5、 索引列尽量设置为 NOT NULL 约束 6、 我们在使用联合索引的时候要注意最左原则展开2
加载中……2019-08-30老师好，请教个问题，有没有经验数据，在索引区分度低于“某个值”(80%)的时候，就不适合在这个列上建立索引了？作者回复: 重复值高的字段一般不创建索引，重复率高于10%可以考虑不创建索引，具体情况也要具体分析，比如通常我们都不应该给性别字段创建索引，因为重复度高，但是如果数据集是女人国，男性比例只有0.01%，而且我们想要查询哪些为男性的情况，此时我们使用索引进行查询效率会更高。这种情况也适用于某种疾病的查询，是否患有某个罕见病的数值为0或者1，且疾病的发病率（数值=1的情况）往往很低，但是如果我们想要进行查询哪些人患病了这种疾病，将这个字段创建索引进行查询效率高，因为我们想要查找的这个人群区分度还是很高的。
2
Geek_Wison2019-08-09老师您好，本节的内容我有个疑惑的地方：创建联合索引(comment_time, user_id)，但是查询语句是先GROUP BY，然后再ORDER BY，那这样子的话，这个联合索引不是应该不符合最左侧原则而失效了吗？作者回复: 如果联合索引的中的字段语句都用了，就不会失效，下面有同学回答了，解释的正确。
共 2 条评论2
Ronnyz2019-08-09作业： 对comment_time使用了函数，索引失效 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'); 2888 rows in set (1.60 sec)展开作者回复: 正确
2
卡布2019-12-04KLOOK校招面试的时候就问了索引，那时候对索引一概不通，这回得在专栏补回来。作者回复: 加油卡布
1
melon2019-08-13老师 为什么group by 后的列要加索引呢？作者回复: 提升检索效率
1
LiuChen2019-08-09老师，对于已有的表结构去创建索引，创建后就直接可以用吗？需不需要其他操作？作者回复: 直接用就可以，就是看能否使用上索引，你可以用 EXPLAIN来检查下，看看SQL语句是否用到了索引
1
