MySQL索引使用的注意事项

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 是个不错的方案。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值