SQL性能优化系列——索引的使用原则[七]

2019年8月19日01:05:08 发表评论 53
摘要

1.什么情况使用索引?
2.什么情况不需要创建索引?
3.如何避免索引失效?

前面说到了索引的使用和底层原理,那么如何通过索引实现查询效率的最大化?

哪些情况可以创建索引

1.字段的数值有唯一性限制,比如用户名;

2.频繁作为 WHERE 查询条件的字段,尤其在数据表大的情况下;

3.需要经常 ORDER BY 和 GROUP BY 的列;

4.UPDATE、DELETE的 WHERE 条件列,一般也需要创建索引;

5.DISTINCT 字段需要创建索引;

6.做多表 JOIN 连接操作时,创建索引需要注意以下原则:

首先,连接表的数量尽量不要超过3张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长非常快,严重影响查询效率。

其次,对 WHERE 条件创建索引,因为 WHERE 才是对数据条件的过滤。

最后,对用于连接的字段创建索引,并且该字段在多张表中的数据类型必须一致。

哪些情况不需要创建索引

1.WHERE 条件(包括 GROUP BY、ORDER BY)里用不到的字段不需要创建索引,索引的价值在于快速定位,如果起不到定位的字段不需要创建索引。

2.如果表记录太少,比如少于1000条,不需要创建索引,原因前面说到过。

3.如果字段中有大量重复数据也不需要创建索引,比如性别字段,原因前面也说到过。

4.频繁更新的字段不一定要创建索引。因为数据更新时,也需要更新索引,如果索引太多,在更新索引时也会造成负担,影响效率。

哪些情况下索引会失效

1.如果索引进行了表达式计算,则会失效

可以使用 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 | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+

可以看出如果对索引进行了表达式计算,索引就失效了。这是因为需要把索引字段的值都取出来,然后依次进行表达式计算进行条件判断,因此采用的是全表扫描的方式,运行会慢很多,最终运行时间为2.538秒。

为了避免索引失效,可以改造SQL:

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000

运行时间为0.039秒。

2.如果对索引使用函数,也会造成失效

比如要对 commont_text 的前三位为 abc 的内容进行条件筛选,执行计划:

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'

运行结果:

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+

可以看到索引失效,重写SQL:

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 | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product_comment | NULL       | range | comment_text  | comment_text | 767     | NULL |  213 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

可以看到,经过查询重写后,可以使用索引进行范围检索,从而提高查询效率。

3.在 WHERE 子句中,如果在 OR 前的条件列进行了索引,而在 OR 后的条件列没有进行索引,那么索引会失效

4.当使用 LIKE 进行模糊查询的时候,后面不能是%

这个很好理解,如果一本字典按照字母顺序进行排序,我们会从首位进行匹配,而不会对中间位置进行匹配,否则索引就失效了。

5.索引列与 NULL 或 NOT NULL 进行判断的时候也会失效

这是因为索引并不存储空值,所以最好是在设计数据表的时候就将字段设置为 NOT NULL 约束,比如可以将 INT 类型的字段默认值设置为0,将字符类型的字段默认值设置为空字符串('')。

6.在使用联合索引的时候要注意最左原则

最左原则就是需要从左到右的使用索引中的字段,一条SQL语句可以只使用联合索引的一部分,但是要从最左侧开始,否则就会失效。(联合索引有这段)

思考题

针对 product_comment 数据表,其中 comment_time 已经创建了普通索引。假设想查询在某段时间期间的记录:

SELECT comment_id, comment_text, comment_time FROM product_comment WHERE DATE(comment_time) >= '2018-10-01 10:00:00' AND comment_time <= '2018-10-02 10:00:00'

此时索引会失效吗?为什么?如果失效如何重写SQL?

(本文完)

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: