一 尽量使用覆盖索引
即查询列和索引列一致,不要写 select *
二 实战
# 全值匹配我最爱
mysql> explain select * from staffs where name = 'July' and age=15 and pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 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 | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 用到了 Using index,数据从索引取,比上面一句更优
mysql> explain select name,age,pos from staffs where name = 'July' and age=15 and pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| 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 | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 使用到 Using index condition
mysql> explain select * from staffs where name = 'July' and age>15 and pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
# Using index 性能优于 Using index condition:参考 https://blog.csdn.net/weixin_42311530/article/details/115854384
mysql> explain select name,age,pos from staffs where name = 'July' and age>15 and pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)