mysql之能使用到索引系列

匹配全值

对索引中所有列都指出具体值,即是对索引中的所有列都有等值匹配的条件

例如: 租赁表 retal中通过指定出租日期 rental_date + 库存编号inventory_id + 客户编号customer_id的组合进行查询

表结构

+--------------+-----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                  | Null | Key | Default           | Extra                       |
+--------------+-----------------------+------+-----+-------------------+-----------------------------+
| rental_id    | int(11)               | NO   | PRI | NULL              | auto_increment              |
| rental_date  | datetime              | NO   | MUL | NULL              |                             |
| inventory_id | mediumint(8) unsigned | NO   | MUL | NULL              |                             |
| customer_id  | smallint(5) unsigned  | NO   | MUL | NULL              |                             |
| return_date  | datetime              | YES  |     | NULL              |                             |
| staff_id     | tinyint(3) unsigned   | NO   | MUL | NULL              |                             |
| last_update  | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------------------+------+-----+-------------------+-----------------------------+

索引

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         |       16008 |     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         |       16008 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | idx_rental_date     |            3 | customer_id  | A         |       16008 |     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      |         |               |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

从执行计划的key和extra的值可以看到优化器选择了复合索引idx_rental_date,type是const

示例SQL

select  * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343;

执行计划

mysql> explain select sql_no_cache * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343;
+----+-------------+--------+------------+-------+--------------------------------------------------------+-----------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys                                          | key             | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+-------+--------------------------------------------------------+-----------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | const | idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id | idx_rental_date | 10      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+--------------------------------------------------------+-----------------+---------+-------------------+------+----------+-------+

匹配值的范围查询

表结构和索引参照匹配全值

sql

select * from rental where customer_id>=373 and customer_id < 400;

执行计划

mysql> explain select sql_no_cache * from rental where customer_id>=373 and customer_id < 400;
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | range | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL |  718 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+--------------------+--------------------+---------+------+------+----------+-----------------------+

这里优化器选择了idx_fk_customer_id索引,同时Using index condition表明需要根据索引回表查询数据

匹配最左前缀

仅仅对索引列进行查询(覆盖索引)

匹配列前缀

仅仅使用索引中的第一列,并且只包含索引第一列开头一部分进行查找.

表前缀

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| film_id     | smallint(6)  | NO   | PRI | NULL    |       |
| title       | varchar(255) | NO   | MUL | NULL    |       |
| description | text         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

索引

mysql> show index from film_text;
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_text |          0 | PRIMARY               |            1 | film_id     | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |
| film_text |          1 | idx_title_description |            1 | title       | NULL      |        1000 |     NULL | NULL   |      | FULLTEXT   |         |               |
| film_text |          1 | idx_title_description |            2 | description | NULL      |        1000 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

sql

我们查询title是以AFRICAN开头的电影信息

select title from film_text where title like 'AFRICAN%';

执行计划

mysql> explain select sql_no_cache  title from film_text where title like 'AFRICAN%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film_text | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

这里明显看到并没有使用到索引

增加索引

create index idx_title_desc_part on film_text (title(10),description(20));

增加索引之后的索引情况

mysql> show index from film_text;
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| film_text |          0 | PRIMARY               |            1 | film_id     | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |
| film_text |          1 | idx_title_desc_part   |            1 | title       | A         |        1000 |       10 | NULL   |      | BTREE      |         |               |
| film_text |          1 | idx_title_desc_part   |            2 | description | A         |        1000 |       20 | NULL   | YES  | BTREE      |         |               |
| film_text |          1 | idx_title_description |            1 | title       | NULL      |        1000 |     NULL | NULL   |      | FULLTEXT   |         |               |
| film_text |          1 | idx_title_description |            2 | description | NULL      |        1000 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

执行计划

mysql> explain select sql_no_cache  title from film_text where title like 'AFRICAN%';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film_text | NULL       | range | idx_title_desc_part | idx_title_desc_part | 32      | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+------+----------+-------------+

这里我们看到 idx_title_desc_part索引就被用上了, Using where表明需要回表查询数据

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

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

表结构

+--------------+-----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                  | Null | Key | Default           | Extra                       |
+--------------+-----------------------+------+-----+-------------------+-----------------------------+
| rental_id    | int(11)               | NO   | PRI | NULL              | auto_increment              |
| rental_date  | datetime              | NO   | MUL | NULL              |                             |
| inventory_id | mediumint(8) unsigned | NO   | MUL | NULL              |                             |
| customer_id  | smallint(5) unsigned  | NO   | MUL | NULL              |                             |
| return_date  | datetime              | YES  |     | NULL              |                             |
| staff_id     | tinyint(3) unsigned   | NO   | MUL | NULL              |                             |
| last_update  | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------------------+------+-----+-------------------+-----------------------------+

索引情况

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         |       16008 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            1 | rental_date  | A         |       15815 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            2 | inventory_id | A         |       16008 |     NULL | NULL   |      | BTREE      |         |               |
| rental |          0 | rental_date         |            3 | customer_id  | A         |       16008 |     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      |         |               |
+--------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

示例SQL --- 1

select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <= 400;

执行计划

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

这里用到了rental_date索引,并且 我们select 的时候也只取出了 所以列的字段,,所以覆盖索引了,不需要回表

示例SQL --- 2

select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and last_update>='2006-02-15 21:30:53';

执行计划

mysql> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and last_update >= '2006-02-15 21:30:53';
+----+-------------+--------+------------+------+--------------------------------+-------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys                  | key         | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+--------------------------------+-------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date,idx_fk_customer_id | rental_date | 5       | const |  182 |    16.47 | Using index condition; Using where |
+----+-------------+--------+------------+------+--------------------------------+-------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

这里我们 换了一个last_update >= '2006-02-15 21:30:53'条件,last_update是没有索引的,所以.即使我们用到了所以,,也是需要回表扫描的

如果列名是索引

如果列名是索引,那么 cllumn is null 就会使用到索引(区别于Oracle)

表结构

+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                 | Null | Key | Default           | Extra                       |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id   | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| customer_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| staff_id     | tinyint(3) unsigned  | NO   | MUL | NULL              |                             |
| rental_id    | int(11)              | YES  | MUL | NULL              |                             |
| amount       | decimal(5,2)         | NO   |     | NULL              |                             |
| payment_date | datetime             | NO   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+

索引

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         |       16086 |     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      |         |               |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)

示例SQL

select * from payment where rental_id is null;

执行计划

mysql> explain select sql_no_cache * from payment where rental_id is null;
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | payment | NULL       | ref  | fk_payment_rental | fk_payment_rental | 5       | const |    5 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Index Condition Pushdown