MySQL在执行语句时会使用多种算法方案，最终会从中选择效率最高的方案执行我们的sql语句。
获取执行方案
#将sql语句放在desc后面，可以得到该sql语句的执行计划，并不是执行sql语句，对系统性能无影响
mysql> desc select * from vote_record where user_id='tGR7RL4T5AyPl358fElA';
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | vote_record | NULL | ALL | NULL | NULL | NULL | NULL | 996424 | 10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#等同于
mysql> explain select * from vote_record where user_id='tGR7RL4T5AyPl358fElA';
说明:
type
表示查询的类型，分为如下两种扫描
全表扫描 : ALL(效率最差)
索引扫描 : index,range,ref,eq_ref,const(system),NULL(性能依次从低到高)
ALL
全表扫描
所查询的列未创建索引，需要遍历全表的数据。
mysql> desc select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#sage列未建索引时作为条件查询是全表扫描
mysql> desc select * from student where sage=18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#对于辅助索引、唯一键索引来说，!=和not in 等语句，type为ALL，全表扫描
mysql> SELECT COUNT(sname) FROM student WHERE sname NOT IN ('zhang3', 'li4');
mysql> desc select * from student where sname like '%z%';
mysql> SELECT sname FROM student WHERE sname != 'zhang3';
index
全索引扫描
满足条件：
- 需要查询的列已创建索引；
- 需要遍历整棵索引树来实现；
例如
#查询student表中的学号，需要遍历整个索引树，所以type是index全索引扫描
mysql> desc select sno from student;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
range
索引范围扫描
包括>,<,>=,<=,between and,or,in,like都是索引范围扫描
注意：对于主键索引来说，!=和not in 等语句是走索引，type为range。
mysql> desc select sno from student where sno > 5;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
注意：在同等查询数据量下，or跟in的效率要低于其他范围条件查询，因为mysql使用的b*树，叶子节点有双向指针Q，能优化范围查找。
#in跟or虽然执行方式是range但是要比其他范围条件查询效率低
mysql> desc select * from student where sname in ('zhang3','li4');
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_name | idx_name | 62 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#使用union all代替or跟in，执行方式会变为ref
mysql> desc select * from student where sname = 'zhang3' union all select * from student where sname = 'zh4';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | PRIMARY | student | NULL | ref | idx_name | idx_name | 62 | const | 1 | 100.00 | NULL |
| 2 | UNION | student | NULL | ref | idx_name | idx_name | 62 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
使用Like进行查询时有如下两种情况
#%z%进行的是全表扫描
mysql> desc select * from student where sname like '%z%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#而w%是range
mysql> desc select * from student where sname like 'w%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_name | idx_name | 62 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
ref
辅助索引等值查询
因为是等值查询，所以比范围查询效率要高
#除了union on等值查询，还有如下形式
mysql> desc select * from student where sname='ls4';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 62 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
eq_ref
多表连接查询时，子表使用主键列或唯一索引列作为连接条件进行查询。
#teacher表的tno为主键列作为子表进行查询，所以type为eq_ref
#驱动表为ALL，全表查询，所以驱动表数据量越少越好，或者为驱动表列创建索引进行连接查询也可以提高效率
mysql> desc SELECT teacher.tname ,GROUP_CONCAT(student.sname)
-> FROM student
-> JOIN score
-> ON student.sno=score.sno
-> JOIN course
-> ON score.cno=course.cno
-> JOIN teacher
-> ON course.tno=teacher.tno
-> WHERE teacher.tname='oldguo';
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | teacher | NULL | ALL | PRIMARY,uidx_tn | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | course | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | score | NULL | ALL | NULL | NULL | NULL | NULL | 17 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | student | NULL | eq_ref | PRIMARY | PRIMARY | 4 | school.score.sno | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
const或system
主键或者唯一键的等值查询
mysql> desc select * from teacher where tno = 101;
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | const | PRIMARY,uidx_tn | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
NULL
想要查询的数据在表里找不到，当数据不存在时查询速度最快。
#注意查询列是主键列或者唯一键列时才为NULL
mysql> desc select * from student where sno=123;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
#查询的列是辅助索引时，为ref
mysql> desc select * from student where sname='tz';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 62 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#当查询的列未设置索引时，type为ALL
mysql> desc select * from teacher where tname='tz';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
学习来自：郭老师博客，老男孩深标DBA课程 第四章