一 点睛
mysql 在使用不等于(!= 或者 <>)的时候,无法使用索引,会导致索引失效,进行全表扫描。
二 实战
# 使用 = 能用到索引
mysql> explain select * from staffs where name = 'July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 使用 != ,索引失效,进行了全表扫描。
mysql> explain select * from staffs where name != 'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 使用 <> ,索引失效,进行了全表扫描。
mysql> explain select * from staffs where name <> 'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)