mysql 索引使用注意事项

青旅半醒 2021-09-15 04:28 483阅读 0赞

原文地址 [点击][Link 1]

文章目录

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

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

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

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

1.  select\*from news where year(publish\_time)<2017

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

1.  select\*from news where publish\_time <'2017-01-01'

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

1.  select\*from news where id /100=1

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

1.  select\*from news where id =1\*100

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

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

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

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

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

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

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

假设，有两个单列索引，分别为 news\_year\_idx(news\_year) 和 news\_month\_idx(news\_month)。现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：

1.  select\*from news where news\_year =2017and news\_month =1

事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用复合索引 news\_year\_month\_idx(news\_year, news\_month) 保证 news\_year 和 news\_month 两个列都被索引覆盖。

## 复合索引的最左前缀原则 ##

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

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

1.  select\*from news where news\_month =1

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

## 覆盖索引的好处 ##

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

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

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

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

1.  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 可以改写成：

1.  select\*from news where news\_weekth =1and enable =1

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

## 索引不会包含有NULL值的列 ##

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

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

## 隐式转换的影响 ##

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

1.  select\*from news where date\_str =201701

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

## like 语句的索引失效问题 ##

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

[Link 1]: http://blog.720ui.com/2017/mysql_core_04_index_item/
[1.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E4%B8%8D%E8%A6%81%E5%9C%A8%E5%88%97%E4%B8%8A%E4%BD%BF%E7%94%A8%E5%87%BD%E6%95%B0%E5%92%8C%E8%BF%9B%E8%A1%8C%E8%BF%90%E7%AE%97
[2._ _ _ not in_ _]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E5%B0%BD%E9%87%8F%E9%81%BF%E5%85%8D%E4%BD%BF%E7%94%A8-%E6%88%96-not-in%E6%88%96-lt-gt-%E7%AD%89%E5%90%A6%E5%AE%9A%E6%93%8D%E4%BD%9C%E7%AC%A6
[3._ or]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E5%B0%BD%E9%87%8F%E9%81%BF%E5%85%8D%E4%BD%BF%E7%94%A8-or-%E6%9D%A5%E8%BF%9E%E6%8E%A5%E6%9D%A1%E4%BB%B6
[4.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E5%A4%9A%E4%B8%AA%E5%8D%95%E5%88%97%E7%B4%A2%E5%BC%95%E5%B9%B6%E4%B8%8D%E6%98%AF%E6%9C%80%E4%BD%B3%E9%80%89%E6%8B%A9
[5.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E5%A4%8D%E5%90%88%E7%B4%A2%E5%BC%95%E7%9A%84%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80%E5%8E%9F%E5%88%99
[6.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95%E7%9A%84%E5%A5%BD%E5%A4%84
[7.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E8%8C%83%E5%9B%B4%E6%9F%A5%E8%AF%A2%E5%AF%B9%E5%A4%9A%E5%88%97%E6%9F%A5%E8%AF%A2%E7%9A%84%E5%BD%B1%E5%93%8D
[8._NULL]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E7%B4%A2%E5%BC%95%E4%B8%8D%E4%BC%9A%E5%8C%85%E5%90%AB%E6%9C%89NULL%E5%80%BC%E7%9A%84%E5%88%97
[9.]: http://blog.720ui.com/2017/mysql_core_04_index_item/#%E9%9A%90%E5%BC%8F%E8%BD%AC%E6%8D%A2%E7%9A%84%E5%BD%B1%E5%93%8D
[10._like]: http://blog.720ui.com/2017/mysql_core_04_index_item/#like-%E8%AF%AD%E5%8F%A5%E7%9A%84%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E9%97%AE%E9%A2%98