MySQL 存在索引但不能使用索引的典型场景

MySQL 存在索引但不能使用索引的典型场景

表结构

mysql> show index from actor;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         121 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

一、以 % 开头的 LIKE 查询不能够利用 B-Tree 索引

mysql> explain select * from actor where last_name like '%NI%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

​ 解决方法：可以扫描二级索引 idx_actor_last_name 获得满足条件 last_name like ‘%NI%’ 的主键 actor_id 列表，之后根据主键回表去检索记录，这样访问避开了全表扫描 actor 表产生的大量 IO 请求，理论上是能够比直接全表扫描更快一些。

mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a,actor b where a.actor_id=b.actor_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_actor_last_name
      key_len: 182
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

二、出现隐式转换

​ 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。

#没加上引号
mysql> explain select * from actor where last_name= 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 201
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

ERROR:
No query specified

#加上引号之后，再次检查执行计划，就发现使用上索引了。
mysql> explain select * from actor where last_name= '1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

三、不满足最左原则

​ 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 Leftmost，是不会使用复合索引的。

mysql> show index from payment;
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment |          0 | PRIMARY            |            1 | payment_id   | A         |       16125 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_fk_staff_id    |            1 | staff_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_fk_customer_id |            1 | customer_id  | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | fk_payment_rental  |            1 | rental_id    | A         |       16045 |     NULL | NULL   | YES  | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            1 | payment_date | A         |       15819 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            2 | amount       | A         |       15869 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            3 | last_update  | A         |       16043 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_amount_by_lk   |            1 | amount_by_lk | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

mysql> explain select * from payment where amount=3.98 and last_update='2006-02-15 22:12:32' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

四、MySQL优化器认为用索引比全表扫描更慢

​ MySQL 估计使用索引比全表扫描更慢，则不会使用索引。也就是在查询时，筛选性越高越容易使用索引，筛选性越低越不容易使用索引。

mysql> update film_text set title=concat('s',title);
Query OK, 1000 rows affected (0.39 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> explain select * from film_text where title like 's%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: ALL
possible_keys: idx_title_description
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

五、有 or 分割开的条件

​ 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。因为 or 后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加 I/O 访问，一次全表扫描过滤条件就可以了。

mysql> show index from payment;
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name           | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| payment |          0 | PRIMARY            |            1 | payment_id   | A         |       16125 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_fk_staff_id    |            1 | staff_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_fk_customer_id |            1 | customer_id  | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | fk_payment_rental  |            1 | rental_id    | A         |       16045 |     NULL | NULL   | YES  | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            1 | payment_date | A         |       15819 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            2 | amount       | A         |       15869 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_payment_date   |            3 | last_update  | A         |       16043 |     NULL | NULL   |      | BTREE      |         |               |
| payment |          1 | idx_amount_by_lk   |            1 | amount_by_lk | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.01 sec)

mysql> explain select * from payment where customer_id=203 or amount=3.96 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16125
     filtered: 10.15
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql>