sql何时会触发索引失效

 

前置sql知识

Q：我们怎么知道 sql 有没有使用索引呢
A：要确定 MySQL 是否使用了索引来处理查询，可以使用 EXPLAIN SELECT 语句来检查查询的执行计划。执行 EXPLAIN SELECT 语句后，MySQL 将返回一个描述查询执行计划的结果集，其中包括了 MySQL 在查询过程中所使用的索引信息。

在查询执行计划的结果集中，可以查看 key 列和 Extra 列来确定 MySQL 是否使用了索引：

- 如果 key 列显示了一个索引名，则表示 MySQL 使用了该索引来处理查询；
- 如果 key 列显示 NULL，则表示 MySQL 没有使用索引来处理查询。

需要注意的是，如果 MySQL 在查询过程中使用了多个索引，则 key 列可能会显示多个索引名，这时需要优化查询语句和索引来提高查询性能。

除了 key 列之外，还可以查看 Extra 列来获取关于索引使用情况的更多信息。例如，如果 Extra 列显示了 "Using index"，则表示 MySQL 使用了覆盖索引来处理查询，这可以减少查询的磁盘访问次数，提高查询性能。

EXPLAIN使用方法举例

explain (select * from user where id = 1 or name = 'gcy');


+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |    43.75 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

Q:在 sql 中使用explain语句查询出来的表中，type列的值分别代表什么含义?
A：在使用EXPLAIN语句查询SQL查询语句的执行计划时，type列的值表示MySQL在执行查询时所使用的访问类型，即MySQL使用哪种方式来查找所需的行。

下面是type列可能的值及其含义：

- system：表示只有一行的表（即系统表），这是const类型的特例。
- const：表示通过索引一次就找到了，const用于比较主键或唯一索引。因为只匹配一行数据，所以很快。例如，查询`SELECT * FROM table_name WHERE primary_key=1`，如果primary_key是一条主键或唯一索引，那么type将是const。
- eq_ref：表示使用唯一索引或主键查找时，对于每个索引键，表中只有一行匹配。这是一个联接类型，通常出现在使用主键或唯一索引查找行时。例如，查询`SELECT * FROM table_name JOIN other_table ON table_name.primary_key=other_table.foreign_key`，如果primary_key是一个主键或唯一索引，而foreign_key是一个对应的键，那么type将是eq_ref。
- ref：表示使用普通索引查找时，所需的行数较少。例如，查询`SELECT * FROM table_name WHERE indexed_column=10`，如果indexed_column是一个普通索引，那么type将是ref。
- range：表示使用索引查找时，返回一个范围的行。例如，查询`SELECT * FROM table_name WHERE indexed_column BETWEEN 10 AND 20`，如果indexed_column是一个普通索引，那么type将是range。
- index：表示全表扫描，这是查询效率最低的一种情况。例如，查询`SELECT * FROM table_name WHERE indexed_column LIKE '%abc%'`，如果indexed_column没有索引，那么type将是index。
- all：表示全表扫描，与index区别在于，它遍历了全表而不是使用索引。这是查询效率最低的一种情况。例如，查询`SELECT * FROM table_name`，如果table_name没有索引，那么type将是all。

Q:在 sql 中使用explain语句查询出来的表中，key列的值分别代表什么含义

A：在使用EXPLAIN语句查询SQL查询语句的执行计划时，key列的值表示MySQL在查询过程中所使用的索引，即MySQL使用哪个索引来处理查询。

下面是key列可能的值及其含义：

- NULL：表示MySQL没有使用索引来处理查询，这通常发生在没有可用索引或优化器认为全表扫描的代价更小的情况下。
- PRIMARY：表示MySQL使用了表的主键索引来处理查询。
- 普通索引名称：表示MySQL使用了该名称的普通索引来处理查询。

请注意，如果MySQL在处理查询时使用多个索引，则key列可能会显示多个索引名称，这时需要对多个索引的使用顺序进行优化（例如，使用覆盖索引或联合索引）以提高查询性能。

另外，需要注意的是，即使key列的值为NULL，也不一定意味着查询的性能会很差。实际上，有时候全表扫描可能比使用索引更快，特别是当查询的数据量很小或者索引的选择性很低时。