mysql 索引

Mysql查看是否使用到索引

mysql数据库创建索引优化之后，在查询时想看下是否使用到索引，

使用执行计划查看：

mysql> explain  SELECT* FROMtb_user WHERE STATUS=1 limit 0,20;

mysql> explain  SELECT * FROM tb_user
 WHERE STATUS=1 limit 0,20;
+----+-------------+----------------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys        | key                  | key_len | ref   | rows  | filtered | Extra |
+----+-------------+----------------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | tb_news_online | NULL       | ref  | idx_tb_news_online_9 | idx_tb_news_online_9 | 5       | const | 99494 |      100 | NULL  |
+----+-------------+----------------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-------+
1 row in set
 
mysql> 

EXPLAIN列的解释

• table：显示这一行的数据是关于哪张表的

• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

• type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说，得保证查询至少达到range级别，最好能达到ref。

• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

• key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

• rows：MYSQL认为必须检查的用来返回请求数据的行数

• Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

最左前缀法则的概念：

一个联合索引中一定会包含多个字段，最左前缀指的是索引字段列表中最左边的字段。

在使用联合索引时，要从索引的最左边的字段开始使用，并且查询条件中一定要包含索引中最左侧的字段，否则这个联合索引将没有任何作用，还是会从全表范围进行查询。

想要发挥联合索引的作用，那么就在SQL的查询条件里全部指定上联合索引中所有的字段，此时查询效率是最高的。

在最左前缀法则中，在查询条件里指定所有的联合索引字段，效率最高，只指定最左边的索引字段，索引有效，跳过其中一个字段，跳过的字段以及该字段往右的所有字段索引无效，不指定最左边的索引字段，该索引无效。

1.索引规则之最左前缀法则

1.1.最左前缀法则的概念

在讲解索引规则的使用时，首先来说一下索引的最左前缀法则。

最左前缀法则主要是针对联合索引的一项指标，可以根据这个规则然后合理的使用联合索引。

如果表中多列形成了联合索引，那么一定要遵守最左前缀的法则去使用这个联合索引，否则在查询表时，这个索引就可能起不到任何作用。

最左前缀法则的概念：
一个联合索引中一定会包含多个字段，最左前缀指的是索引字段列表中最左边的字段。
在使用联合索引时，要从索引的最左边的字段开始使用，并且查询条件中一定要包含索引中最左侧的字段，否则这个联合索引将没有任何作用，还是会从全表范围进行查询。
想要发挥联合索引的作用，那么就在SQL的查询条件里全部指定上联合索引中所有的字段，此时查询效率是最高的。
在最左前缀法则中，在查询条件里指定所有的联合索引字段，效率最高，只指定最左边的索引字段，索引有效，跳过其中一个字段，跳过的字段以及该字段往右的所有字段索引无效，不指定最左边的索引字段，该索引无效。

在使用联合索引时可能会出现以下几种情况，某些情况中索引是无效的。

• 只查询最左侧的索引字段，只有最左侧的索引字段产生索引的作用，即使表中返回的数据也包含了联合索引中的其他字段，但是我们在查询条件里没有指定，那么也是没有作用的。

• 查询联合索引中的全部索引字段，此时联合索引的效率最高。

• 查询最左侧字段和联合索引中其他的一个索引字段，例如联合索引中有三个字段，分别是name、age、xb，我们在查询条件里只查询name和xb字段，跳过中间的age字段，那么此时只有最左侧的name字段的索引有效，跳过了某一个索引字段，该索引字段往右的其他索引字段将无效。

• 查询左侧字段以及挨着左侧字段，即连续的几个索引字段时，只对指定的几个索引有效。

• 查询条件里不包含最左侧的索引字段，那么此联合索引不会起到任何作用。

结论：使用联合索引时，必须要遵循最左前缀查询法则，并且在查询条件上指定所有的索引字段，此时查询效率最优。
在最左前缀法则中，只要在查询时包含最左侧的索引列，不管顺序是怎么样的，索引都生效。

1.2.最左前缀法则的验证案例

tb_user表中已经在前面创建好了一个联合索引，下面我们通过该联合索引，验证最左前缀法则。

在这个联合索引中，索引字段的顺序是zy、nl、zt，其中最左前缀索引字段是zy，最右侧索引字段是zt。

1）查询联合索引中的全部字段，观察执行计划。

mysql>explainselect*from tb_user where zy ='网络工程'and nl ='31'and zt ='1';+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-----------------------+| id | select_type |table| partitions |type| possible_keys     |key| key_len | ref               |rows| filtered | Extra                 |+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-----------------------+|1|SIMPLE| tb_user |NULL| ref  | idx_user_zy_nl_zt | idx_user_zy_nl_zt |54| const,const,const |1|100.00|Usingindex condition |+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-----------------------+1rowinset,1 warning (0.00 sec)

注意观察这三个字段，可能使用的索引是idx_user_zy_nl_zt，实际使用的索引也是idx_user_zy_nl_zt，索引的长度为54，说明三个索引的累计的长度是54，当我们不知道联合索引中有几个索引生效时，可以观察ref这个字段，里面有几个值，就表示有几个字段生效了，当前也不是那么精准。

2）只查询最左侧的索引字段，观察执行计划。

本次不全部查询联合索引中的全部字段，只查询一个最左侧的索引字段。

mysql>explainselect*from tb_user where zy ='网络工程';

观察输出的执行计划，根据ref字段得知本次只有一个索引起到了作用，也就验证了一句话“只查询最左侧的索引字段，只有最左侧的索引字段产生索引的作用”，即使我们查询的是表中所有的字段，联合索引也有其他字段的索引，但是不会生效哦。

根据索引长度我们得知最左侧的索引字段占用的长度是47。

3）只查询最左侧索引字段和挨着最左侧索引字段的一个字段，观察执行计划。

只查询最左侧的字段和挨着左侧字段的第二个字段，验证“指定最左侧的索引字段，并且还使用了挨着最左侧索引字段的连续的索引字段，此时指定了哪些索引字段，哪些索引字段就有效”。

mysql>explainselect*from tb_user where zy ='网络工程'and nl ='31';

观察输出的执行计划，根据ref字段我们得知本次有两个索引字段起到了作用，我们查询的索引字段是最左侧的索引和第二个索引字段，第一个索引字段的长度从2中得知是47，根据下图中显示的索引长度，我们可以得知第二个字段的索引长度为2，那么第三个字段的索引长度就是5。

在优化索引时，要牢记每个索引字段的长度，可以分析对应长度的索引字段是否生效起到了作用。

4）查询最左侧的索引字段和第三个索引字段，观察执行计划。

只查询最左侧的索引字段和第三个索引字段，验证“查询条件包含最左侧索引字段，但是中间跳过了索引列表中的一些字段，此时只有最左侧的索引字段有效。”

mysql>explainselect*from tb_user where zy ='网络工程'and zt ='1';

观察输出的执行计划，可以看到只有一个索引生效了，索引的长度是47，那么生效的索引字段就是最左侧的索引字段，其余的索引字段没有生效，也验证了“查询条件包含最左侧索引字段，但是中间跳过了索引列表中的一些字段，此时只有最左侧的索引字段有效。”这句话。

5）查询条件中不指定最左侧的索引字段。

查询条件中不指定最左侧的索引字段，验证“查询条件里不包含最左侧的索引字段，那么此联合索引不会起到任何作用。”这句话。

mysql>explainselect*from tb_user where nl ='31'and zt ='1';

观察执行计划的输出结果，可以看到没有使用任何索引。

2.索引规则之范围查询

范围查询也是针对联合索引的一种使用规则，在联合索引中，可能会出现范围查询的操作，例如年龄大于20，小于30等等，如果在使用联合索引时，某个索引字段使用了范围查询，那么该字段往右的索引字段将会失效。

避免范围查询影响右侧索引字段失效的方法就是在业务逻辑允许的情况下，尽可能去使用大于等于或者小于等于。

如下面这个SQL，nl字段也是联合索引中的一个字段，对该字段我们使用了范围查询。

mysql>explainselect*from tb_user where zy ='网络工程'and nl >'30'and zt ='1';

观察执行计划的输出，可以看到索引的长度是49，从前面的案例中我们得知最左侧的索引字段长度是47，第二个索引字段的长度是2，那么我们就可以得知在这条SQL中，只有最左侧索引和第二个索引生效了，第三个字段的索引没生效，此时也就验证了“某个索引字段使用了范围查询，那么该字段往右的索引字段将会失效。”这句话。

如果业务逻辑允许的情况下，我们将第二个索引字段的范围查询条件改成大于等于，就可以避免右侧索引无效的情况。

mysql>explainselect*from tb_user where zy ='网络工程'and nl >='30'and zt ='1';

观察执行计划的输出，可以看到索引的长度为54，那么就表示三个索引字段全部都起到作用了。

小细节：当我们不知道有几个索引字段生效时，可以一次查询一个索引，记录他的索引长度，和总的索引长度去比较，就可以得知联合索引中有几个索引起到作用了。

3.使用索引时会导致索引失效的几种情况

3.1.索引列使用运算导致索引失效

在使用索引时，一定不要在索引的列上进行运算操作，否则会导致索引失效。

在tb_user表中有一个id_user_lxfs的索引，这个索引是针对lxfs这个字段的，我们针对这个索引去做一组运算，观察索引是否还有效。

mysql>explainselect*from tb_user where lxfs ='13900090963';
mysql>explainselect*from tb_user where substring(lxfs,10,2)='63';

从下面的执行计划中可以看出，在使用运算后，索引就没有任何作用了。

3.2.索引列的值不加引号导致索引失效

如果索引列的字段类型是字符串类型，那么在查询时，值不使用引号引起来，那么就会导致该列的索引失效。

常规类型的索引列不使用引号引起来，就会导致索引失效，联合索引也是同样的，只要是索引的字段值没有添加引号，就会导致索引失效。

此时会有人有疑问：在MySQL数据库中，字符串类型的字段内容一般都是文字、字母类型的，肯定会带着引号，那么也有情况字符串中全部都是数字，如果写SQL的人不规范，就看影响索引的效率。

索引字段值加引号和不加引号的对比。

mysql>explainselect*from tb_user where lxfs =13900090963;

观察执行计划，索引字段在查询时不加引号，可以看到在possible_keys列中虽然显示了可能会用到的索引，但是在key列中则显示了NULL，表示没有使用任何索引，此时索引就无效了。

3.3.索引列模糊查询可能会导致索引失效

如果查询条件中，索引列的字段值是用的模糊匹配，如果仅仅是尾部进行模糊匹配，这种条件下索引是有效的，如果是头部模糊匹配，则索引无效。

无论是中间内容匹配，还是头部内容匹配，都会导致索引无效。

首先我们在查询条件中指定索引列，并且使用尾部模糊匹配，观察索引是否有效。

mysql>explainselect*from tb_user where lxfs like"139%";

通过执行计划的输出，我们可以看到即使使用了尾部的模糊匹配，索引依旧生效。

下面我们再索引列的字段中使用头部模糊匹配，观察索引是否还有效。

mysql>explainselect*from tb_user where lxfs like"%139";

通过执行计划的输出，我们可以看到当索引字段在查询时使用了头部模糊匹配，索引就失效了。

经过上面的案例，我们可以得出结论，在索引列使用模糊查询时，关键字前面加%，索引就会失效，关键字后面加%，索引正常使用。

3.4.OR连接条件使用不规范可能会导致索引失效

在查询条件很多情况下都会包含OR连接条件，但是在使用索引列作为条件查询时，如果OR条件前面的字段有索引，OR条件后面的字段没有索引，此时即使有索引的字段也会失去索引的作用。

OR连接条件前后的字段必须全都有索引，此时索引才不会失效。

tb_user表中的lxfs和xm字段都是有索引的，我们编写一条SQL，使用or连接查询，观察索引是否有效。

mysql>explainselect*from tb_user where xm ="江睿基"or lxfs ="15101030779";

从SQL的执行计划中来看，OR连接条件前后的字段的索引全部起到了作用。

tb_user表中nl字段的索引是联合索引，nl字段在联合索引中也不是最左侧的索引，我们直接使用这个字段查询时，这个字段相当于没有索引，我们可以通过这个案例，观察索引是否会失效。

mysql>explainselect*from tb_user where xm ="江睿基"or nl ="31";

从SQL的执行计划中，我们可以看到当我们使用了OR连接条件，即使连接条件左侧字段有索引，但是右侧字段没有索引，此时所有的索引都会失效。