文章目录

  1. 1. 不要在列上使用函数和进行运算
  2. 2. 尽量避免使用 != 或 not in或 <> 等否定操作符
  3. 3. 尽量避免使用 or 来连接条件
  4. 4. 多个单列索引并不是最佳选择
  5. 5. 复合索引的最左前缀原则
  6. 6. 覆盖索引的好处
  7. 7. 范围查询对多列查询的影响
  8. 8. 索引不会包含有NULL值的列
  9. 9. 隐式转换的影响
  10. 10. like 语句的索引失效问题

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度,在索引的使用过程中,存在一些使用细节和注意事项。

不要在列上使用函数和进行运算

不要在列上使用函数,这将导致索引失效而进行全表扫描。

select * from news where year(publish_time) < 2017

为了使用索引,防止执行全表扫描,可以进行改造。

select * from news where publish_time < '2017-01-01'

还有一个建议,不要在列上进行运算,这也将导致索引失效而进行全表扫描。

select * from news where id / 100 = 1

为了使用索引,防止执行全表扫描,可以进行改造。

select * from news where id = 1 * 100

尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符,因为这几个操作符都会导致索引失效而进行全表扫描。

尽量避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件,因为这会导致索引失效而进行全表扫描。

select * from news where id = 1 or id = 2

多个单列索引并不是最佳选择

MySQL 只能使用一个索引,会从多个索引中选择一个限制最为严格的索引,因此,为多个列创建单列索引,并不能提高 MySQL 的查询性能。

假设,有两个单列索引,分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。现在,有一个场景需要针对资讯的年份和月份进行查询,那么,SQL 语句可以写成:

select * from news where news_year = 2017 and news_month = 1

事实上,MySQL 只能使用一个单列索引。为了提高性能,可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

复合索引的最左前缀原则

复合索引遵守“最左前缀”原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。

假设,有一个场景只需要针对资讯的月份进行查询,那么,SQL 语句可以写成:

select * from news where news_month = 1

此时,无法使用 news_year_month_idx(news_year, news_month) 索引,因为遵守“最左前缀”原则,在查询条件中没有使用复合索引的第一个字段,索引是不会被使用的。

覆盖索引的好处

如果一个索引包含所有需要的查询的字段的值,直接根据索引的查询结果返回数据,而无需读表,能够极大的提高性能。因此,可以定义一个让索引包含的额外的列,即使这个列对于索引而言是无用的。

范围查询对多列查询的影响

查询中的某个列有范围查询,则其右边所有列都无法使用索引优化查找。

举个例子,假设有一个场景需要查询本周发布的资讯文章,其中的条件是必须是启用状态,且发布时间在这周内。那么,SQL 语句可以写成:

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

这种情况下,因为范围查询对多列查询的影响,将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说,news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。

对于这种情况,我的建议:对于范围查询,务必要注意它带来的副作用,并且尽量少用范围查询,可以通过曲线救国的方式满足业务场景。

例如,上面案例的需求是查询本周发布的资讯文章,因此可以创建一个news_weekth 字段用来存储资讯文章的周信息,使得范围查询变成普通的查询,SQL 可以改写成:

select * from news where     news_weekth = 1 and enable = 1

然而,并不是所有的范围查询都可以进行改造,对于必须使用范围查询但无法改造的情况,我的建议:不必试图用 SQL 来解决所有问题,可以使用其他数据存储技术控制时间轴,例如 Redis 的 SortedSet 有序集合保存时间,或者通过缓存方式缓存查询结果从而提高性能。

索引不会包含有NULL值的列

只要列中包含有 NULL 值都将不会被包含在索引中,复合索引中只要有一列含有 NULL值,那么这一列对于此复合索引就是无效的。

因此,在数据库设计时,除非有一个很特别的原因使用 NULL 值,不然尽量不要让字段的默认值为 NULL。

隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换,隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中,date_str 是字符串,然而匹配的是整数类型,从而发生隐式转换。

select * from news where date_str = 201701    

因此,要谨记隐式转换的危害,时刻注意通过同类型进行比较。

like 语句的索引失效问题

like 的方式进行查询,在 like “value%” 可以使用索引,但是对于 like “%value%” 这样的方式,执行全表查询,这在数据量小的表,不存在性能问题,但是对于海量数据,全表扫描是非常可怕的事情。所以,根据业务需求,考虑使用 ElasticSearch 或 Solr 是个不错的方案。

(完)

微信公众号

文章目录

  1. 1. 不要在列上使用函数和进行运算
  2. 2. 尽量避免使用 != 或 not in或 <> 等否定操作符
  3. 3. 尽量避免使用 or 来连接条件
  4. 4. 多个单列索引并不是最佳选择
  5. 5. 复合索引的最左前缀原则
  6. 6. 覆盖索引的好处
  7. 7. 范围查询对多列查询的影响
  8. 8. 索引不会包含有NULL值的列
  9. 9. 隐式转换的影响
  10. 10. like 语句的索引失效问题