mysql之不能使用索引系列

以%开头的LIKE查询

表结构

+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

索引情况

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      |         |               |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

查询sql

select * from actor where last_name like '%NI%';

执行计划

mysql> explain select * from actor where last_name like '%NI%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

很惊讶,居然没有使用到索引

因为B-Tree索引的结构,所以以%开头的查询很自然的没法利用索引了,一般都推荐使用全文索引(FullText)来解决类似的全文索引的问题. 或者考虑利用InnoDB的表都是聚簇表的特点,采用一种轻量级别的解决方式:一般情况下,索引都会比表小,扫码索引要比扫描表更快(某些特殊情况下,索引比表达,不在本例讨论范围内),而InnoDB表上的二季索引idx_last_name实际上存储字段last_name还有主键actor_id,那么理想的访问方式应该是首先扫描二级索引idx_last_name获得满足条件last_name like '%NI%'的主键actor_id列表,之后根据主键回表去检索记录,这样访问避开了全表扫描演员表actor产生的大量IO请求

更改sql

select * from (select actor_id from actor where last_name like '%NI%')a, actor b where a.actor_id = b.actor_id;

执行计划

mysql> explain select * from (select actor_id from actor where last_name like '%NI%')a, actor b where a.actor_id = b.actor_id;
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key                 | key_len | ref                   | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | actor | NULL       | index  | PRIMARY       | idx_actor_last_name | 137     | NULL                  |  200 |    11.11 | Using where; Using index |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY             | 2       | sakila.actor.actor_id |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+

从执行计划中能够看到, 内层查询的Using index 代表索引覆盖扫描, 之后通过主键join操作去演员表actor中获取到最终的查询结果,理论上是能够比直接全表扫描更快一些.

数据类型出现隐式转换(强制类型转换会全表扫描)

表结构

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | varchar(10)      | NO   | MUL | NULL    |                |
| post_id    | int(10) unsigned | NO   |     | 0       |                |
| car_id     | int(10) unsigned | NO   |     | 0       |                |
| created_at | int(10) unsigned | NO   |     | 0       |                |
| updated_at | int(10) unsigned | NO   |     | 0       |                |
+------------+------------------+------+-----+---------+----------------+

索引

+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| most_left_index |          0 | PRIMARY  |            1 | id          | A         |     2711843 |     NULL | NULL   |      | BTREE      |         |               |
| most_left_index |          1 | user_id  |            1 | user_id     | A         |     1464543 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SQL

select * from most_left_index where user_id=170050;

确实也能查询到数据
+---------+---------+---------+--------+------------+------------+
| id      | user_id | post_id | car_id | created_at | updated_at |
+---------+---------+---------+--------+------------+------------+
|       1 | 170050  |   70331 |  94184 | 1521774262 |          0 |
| 1946001 | 170050  |  437066 | 591661 | 1521824159 | 1521824159 |
+---------+---------+---------+--------+------------+------------+
2 rows in set (1.82 sec)

执行计划

mysql> explain select * from most_left_index where user_id=170050;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | most_left_index | NULL       | ALL  | user_id       | NULL | NULL    | NULL | 2711843 |    10.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

因为user_id的字段类型是varchar,你这里写的是整型,发生了类型转换,所以全表扫描了

优化SQL

select * from most_left_index where user_id='170050';

优化后执行计划

mysql> explain select * from most_left_index where user_id='170050';
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | most_left_index | NULL       | ref  | user_id       | user_id | 32      | const |    2 |   100.00 | NULL  |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如果user_id本来就是整型,那么user_id=170050和user_id='170050' 都是可以用到索引的...

mysql> explain select * from most_left_index where user_id='170050';
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | most_left_index | NULL       | ref  | user_id       | user_id | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from most_left_index where user_id=170050;
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | most_left_index | NULL       | ref  | user_id       | user_id | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+-----------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

复合索引,不满足最左原则

如果Mysql使用索引比全表扫描更慢

存在索引数据太量大优化器判定全表扫描

用or分割开条件

用or分割开的条件,如果or前的条件中的列有索引, 而后面的列中没有索引, 那么涉及的索引都不会被用到

表结构

+--------------+----------------------+------+-----+-------------------+-----------------------------+
| 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      |         |               |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SQL

select * from payment where customer_id=203 or amount = 3.96;

执行计划

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

因为 or 后面的添加列中没有索引,那么后面的查询肯定要走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加 I/O 访问,一次全表扫描过滤条件就足够了

amount加上索引之后

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      |         |               |
| payment |          1 | idx_amount         |            1 | amount      | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

amount加上索引之后执行计划

mysql> explain select sql_no_cache * from payment where customer_id=203 or amount = 3.96;
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys                 | key                           | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | payment | NULL       | index_merge | idx_fk_customer_id,idx_amount | idx_fk_customer_id,idx_amount | 2,3     | NULL |   21 |   100.00 | Using union(idx_fk_customer_id,idx_amount); Using where |
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)