mysql不会使用索引的情况_mysql 中不会使用索引的几种情况

表如下，其中有一个 article 与  dealer 的组合索引

mysql> select * from shop;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

| 0001 | A | 3.45 |

| 0001 | B | 3.99 |

| 0002 | A | 10.99 |

| 0003 | B | 1.45 |

| 0003 | C | 1.69 |

| 0003 | D | 1.25 |

| 0004 | D | 19.95 |

+---------+--------+-------+

7 rows in set (0.00 sec)

mysql> show index from shop;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| shop | 0 | PRIMARY | 1 | article | A | 7 | NULL | NULL | | BTREE | | |

| shop | 0 | PRIMARY | 2 | dealer | A | 7 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

1) 如果where 中使用了 or ，则不会使用索引。

mysql> explain select * from shop where article=0001;

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

| 1 | SIMPLE | shop | ref | PRIMARY | PRIMARY | 4 | const | 2 | NULL |

+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+

1 row in set (0.01 sec)

mysql> explain select * from shop where article=0001 or dealer='B';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | shop | ALL | PRIMARY | NULL | NULL | NULL | 7 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.08 sec)

2)对于组合索引，如果前导列没有出现在where 中，也不会使用索引

mysql> explain select * from shop where dealer='B';

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| 1 | SIMPLE | shop | ALL | NULL | NULL | NULL | NULL | 7 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> create index id_dealer on shop(dealer);

Query OK, 0 rows affected (0.48 sec)

Records: 0 Duplicates: 0 Warnings: 0

3) 对于使用了 like ，如果以% 开头，则不会使用索引。

mysql> explain select * from shop where dealer like 'A%';

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+

| 1 | SIMPLE | shop | range | id_dealer | id_dealer | 20 | NULL | 2 | Using index condition |

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+

1 row in set (0.00 sec)