? ? ? ? ? ? ? mysql使用索引能提高查詢效率，但在有些情況下，你使用索引查詢，索引并沒有起作用。

mysql> select * from stu; +------+------+----------+ | id | age | name | +------+------+----------+ | 1 | 1 | zhangsan | | 2 | 2 | lisi | | 3 | 3 | wangwu | | 4 | 4 | zhaoliu | | 5 | 5 | sunqi | +------+------+----------+ 5 rows in set (0.00 sec)mysql> show index from stu; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | stu | 1 | index_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)

可以看到stu表中的name字段為該表的索引。

mysql> explain select * from stu where name like 'z%'; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | stu | NULL | range | index_name | index_name | 23 | NULL | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql> explain select * from stu where name like '%i'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | stu | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

由結(jié)果可以看到，like 'z%'使用了索引查詢，而like '%i'沒有使用索引查詢。key為index_name表示使用了索引查詢。

關(guān)于like的查詢：如果匹配字串的第一個字符為'%'，索引不會起作用。只有'%'不在第一個位置，索引才會起作用。

mysql> create index index_id_age on stu(id,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> show index from stu; +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | stu | 1 | index_id_age | 1 | id | A | 5 | NULL | NULL | YES | BTREE | | | | stu | 1 | index_id_age | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)mysql> explain select * from stu where id=4; +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | stu | NULL | ref | index_id_age | index_id_age | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)mysql> explain select * from stu where age=4; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | stu | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

我們?yōu)楸韘tu創(chuàng)建了一個多列索id和age。當(dāng)我們單獨(dú)使用id時，查詢使用了索引；而當(dāng)單獨(dú)使用age時，查詢沒有使用索引。則對應(yīng)多列索引，只有當(dāng)查詢使用了這些字段的第一個字段時，索引才會被使用。

?

當(dāng)查詢條件使用or關(guān)鍵字時，且or前后的兩個條件中的列都是索引時，查詢中才使用索引。否則將不使用索引。

我目前機(jī)器上測試的結(jié)果是：or前后都使用索引，查詢還是不用索引，不知何故。感興趣的讀者可以在自己mysql上嘗試一下。

?

?

參考資料：mysql從入門到精通

?

?

?

?

總結(jié)

以上是生活随笔為你收集整理的查询无法解析索引的几种情况的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。