MySQL 中能够使用索引的典型场景

MySQL 中能够使用索引的典型场景

​ 表结构

mysql> show index from rental;
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rental |          0 | PRIMARY             |            1 | rental_id    | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | idx_rental_date     |            1 | rental_date  | A         |       15815 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | idx_rental_date     |            2 | inventory_id | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | idx_rental_date     |            3 | customer_id  | A         |       16005 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_inventory_id |            1 | inventory_id | A         |        4580 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_customer_id  |            1 | customer_id  | A         |         599 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          1 | idx_fk_staff_id     |            1 | staff_id     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

一、匹配全值

​ 匹配全值(Match the full value)：对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。

mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: idx_rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

二、匹配值的范围查询

​ 匹配值的范围查询（Match a range of values）：对索引的值能够进行范围查找。Extra 为 Using index condition：表示 MySQL 使用了 ICP来进一步优化查询

mysql> explain select * from rental where customer_id>=373 and customer_id<400 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

三、匹配最左前缀

​ 匹配最左前缀（Match a leftmost prefix）,仅仅使用索引中的最左边列进行查找，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 （col1）、（col2 + col3）、（col1 + col2 + col3）的等值查询利用到。可是不能够被 （col2）、（col2 + col3）的等值查询利用到。

mysql> alter table payment add index idx_payment_date(payment_date,amount,last_update);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from payment where payment_date='2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 10.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

四、仅仅对索引进行查询

​ 仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高。

mysql> explain select last_update from payment where payment_date='2006-02-14 15:16:03' and amount=3.98 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 8
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

五、匹配列全缀

mysql> create index idx_title_desc_part on film_text(title(10),description(20));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

ERROR:
No query specified

mysql>

六、能够实现索引匹配部分精确而其他部分进行范围匹配

​ 能够实现索引匹配部分精确而其他部分进行范围匹配（match one part exactly and match a range on another part）

mysql> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_rental_date,idx_fk_customer_id
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.86
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)