如何通过索引让 SQL 查询效率最大化
如何通过索引让 SQL 查询效率最大化
什么时候创建索引？
如果出现如下情况，可以创建索引。
- 字段的数值唯一性的限制 索引可以起到约束的作用，比如唯一索引，主键索引，都可以起到唯一约束的作用。当字段的数值唯一时，可以考虑建立唯一索引或者主键索引。
- 频繁作为 Where 查询条件的字段 在表数据量比较大的时候，某个字段在 SQL 查询的 where条件时，就学英语给这个字段创建索引。
SELECT comment_id, product_id, comment_text, comment_time, user_id FROM product_comment WHERE user_id = 7851
运行结果
运⾏时间为0.699s，你能看到查询效率还是⽐较低的。当我们对user_id字段创建索引之后，运⾏时间为 0.047s，不到原来查询时间的1/10。
- 经常需要 Group By 和 Order By 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 Group By 对数据查询或者使用 Order By 对数据进行排序的时候，就需要对分组或者排序字段建立索引。
SELECT user_id, count(*) as num FROM product_comment group by user_id limit 100
运⾏结果（100条记录，运⾏时间1.666s）
对 user_id 创建索引，再执行 SQL 语句
SELECT user_id, count(*) as num FROM product_comment group by user_id limit 100
运⾏结果（100条记录，运⾏时间0.042s）：
image
- DISTINCT 字段需要创建索引 有时候需要对某个字段进行去重，使用 DISTINCT ,那么对这个字段创建索引，也会提升效率。
SELECT DISTINCT(user_id) FROM product_comment
运⾏结果（600637条记录，运⾏时间2.283s）. 如果我们对user_id创建索引，再执⾏SQL语句。
SELECT DISTINCT(user_id) FROM `product_comment`
运⾏结果（600637条记录，运⾏时间0.627s）：建立索引，SQL 查询效率上有所提升。
- UPDATE ，DELETE 的Where 条件列，一般也需要建立索引。
当对某条数据进行 UPDATE 或者DELETE 操作的时候，可以考虑建立索引。
当没有对 comment_text 建立索引时，执行下面语句：
UPDATE product_comment SET product_id = 10002 WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1，运⾏时间为1.173s。
对 comment_text 字段建立索引
UPDATE product_comment SET product_id = 10001 WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1，运⾏时间仅为0.1110s。
comment_text 没有建立索引，执行如下语句
DELETE FROM product_comment WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1，运⾏时间为1.027s，
对comment_text创建了索引，再来执⾏这条SQL语句，运⾏时间为0.032s，时间是原来的1/32。 6. 做多表连接操作时，创建索引需要注意：
- 连接表的梳理尽量不要超过三张， 每增加一张表，就相当于增加了一次循环，会成指数级增长，验证影响查询效率。
其次，对 用于连接字段创建索引，并且该字段在夺标中的类型必须一致。
什么时候不需要创建索引
- Where Group By Order By 中用不掉的字段不需要创建索引，索引的价值是快速定位，提高效率
- 如果表记录太少，比如少于 1000 个，那么是不需要创建索引的。
- 字段中如果有大量的重复数据，也不要创建索引，比如性别字段。因为更新数据的时候，也需要更新索引，如果索引太多，更新索引的时候会造成负担，影响效率。
什么情况下，索引会失效 ？
- 如果索引进行了表达式计算，索引会失效
可以使用 EXPLAIN 关键字来看 MySQL 中一条 SQL 语句的执行计划：
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001
执行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 100.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
可以看到，如果对索引进行表达式计算，那么索引就失效了。这是因为我们需要把索引字段都取出来，然后依次进行表达式计算进行条件判断，因此采用了全表扫描的方式，运行时间会慢很多，执行时间为 2.538 秒。
SQL 如果写成这样：
SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000
运⾏时间为0.039秒。
- 如果索引使用函数，也会造成失效
比如 我们想要对 comment_text 的前三位为abc 的 内容进行条件筛选。
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='a
运行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 100.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
- WHERE 子句中，如果在 OR 前的条件进行了索引，但是在 OR 之后没有进行索引，那么索引会失效。比如下面的SQL 语句，comment_id 是主键，而 comment_text 没有进行索引，因为 OR 的含义就是两个只要满足一个即可，因此只要一个条件进行索引是没有意义 的，只要有条件没进行索引，就会进行全表扫描。索引索引的条件也就失效了。
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text
运行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | PRIMARY | NULL | NULL | NULL | 996663 | 10.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
- 当我们使用 Like 进行模糊查询时，% 在前面索引失效 比如如下：
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'
索引是不生效的
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 11.11 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+-
- 索引列与 NULL 或者 NOT NULL 进行判断时也会失效
这是因为索引并不存储空值，所以最好在设计数据表的时候就将字段设置为NOTNULL约束，⽐如你可以将 INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串(’’)
- 使用联合索引时要注意最左原则
最左原则也就是需要从左到右使用的索引字段中的字段，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则会失效。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-06-30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。