sql优化-1-查询执行计划:explain

mysql优化的官网文档:https://dev.mysql.com/doc/refman/8.0/en/optimization.html

SQL执行流程

a. sql编写顺序

select dinstinct .. from .. join .. on .. where .. group by .. having .. order by ..

b. mysql服务器解析顺序:

from .. on .. join .. where .. group by .. having .. select dinstinct ..order by ..

SQL性能优化

MySQL查询优化器会自动解析优化我们写的sql，结果可能不是我们想要的，所以这时候需要分析sql的执行计划，看看sql到底是怎么去执行的。

分析SQL的执行计划使用explain关键字,可以查看SQL优化器执行SQL语句，从而让开发人员知道自己编写的sql的执行情况，从而进行优化。

测试数据

-- 课程表
create table course(
    cid int,
    cname varchar(32),
    tid int
);

insert into course (cid,cname,tid) values (1,'java',1),(2,'html',1),(3,'sql',2),(4,'web',3);

-- 教师表
create table teacher(
    tid int,
    tname varchar(32),
    tcid int
);

insert into teacher (tid,tname,tcid) values (1,'tz',1),(2,'tw',2),(3,'tl',3);

-- 教师信息表
create table teacherCard(
    tcid int,
    tcdesc varchar(32)
);

insert into teacherCard (tcid,tcdesc) values (1,'tzdesc'),(2,'twdesc'),(3,'tldesc');

explain:查看sql执行计划

explain select * from course;

mysql> explain select * from course;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | course | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

mysql>

id:编号
select_type:查询类型
table:表
type:类型
possible_keys:预测用到的索引
key:实际使用的索引
key_len:实际使用索引的长度
ref:表之间的引用
rows: ~~通过索引~~ 查询到的数据量
Extra:额外的信息

id编号

查询课程编号为2 或教师证编号为3 的老师信息，看一下这条sql的执行情况。

id:id值相同，从上往下顺序执行

执行顺序： teacher3 --》 teachercard3 --》 course4

mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3);
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.04 sec)

id值不同:id值越大越优先查询(本质:在嵌套子查询时，先查内层再查外层)

id值有相同，又有不同:值越大越优先;id值相同，从上往下顺序执行

-- 查询教授SQL课程的老师的描述(desc)

mysql> explain select course.cname,teacher.tname,teacherCard.tcdesc from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and course.cname='sql';
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.07 sec)

-- 将以上 多表查询 转为子查询形式:
mysql> explain select teacherCard.tcdesc from teacherCard where teacherCard.tcid =
(select tcid from teacher where teacher.tid =
    (select tid from course where course.cname='sql')
);
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  2 | SUBQUERY    | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  3 | SUBQUERY    | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.05 sec)

-- 子查询+多表 形式:
mysql> explain select teacherCard.tcdesc from teacher,teacherCard where teacher.tcid = teacherCard.tcid and teacher.tid= (select tid from course where course.cname='sql');
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | PRIMARY     | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | PRIMARY     | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
|  2 | SUBQUERY    | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                                        |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.05 sec)

mysql>

select_type和table

select_type 查询类型

table 查询涉及的表，或者临时表类型及id号

（1）SIMPLE 简单查询：不包含子查询或Union查询的sql语句。

（2）PRIMARY 主查询：查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。

（3） SUBQUERY 子查询。

（4） DERIVED 衍生查询，

派生表的处理方式是MySQL首先执行子查询（DERIVED部分），得到结果集，然后将这个结果集当作一个临时表来对待，供外部查询（PRIMARY查询）使用。

mysql> explain select * from (select cname,tid from course) a;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
|  2 | DERIVED     | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.07 sec)

（5）UNION 和 UNION RESULT

若第二个select出现在union之后，则被标记为UNION。

UNION RESULT表示合并所有UNION操作的最终结果集。结果集是一个临时虚拟表，标记为<union1,2>，数字1和2代表前面两个SELECT查询的id值。

mysql> explain select * from course union select * from teacher;
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|    1 | PRIMARY      | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL            |
|    2 | UNION        | teacher    | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.12 sec)

mysql>

type:索引类型、或者直接称为类型

常见的type值及其优先级大致如下（从最优到最差）：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_merge > range > index > ALL

实际见的比较多的类型：system > const > eq_ref > ref > range > index > all

越往左性能越高，system，const只是理想情况;实际能达到ref>range

system

在MySQL中，EXPLAIN输出的type列的system值通常表示查询可以非常快速地完成，因为它仅涉及系统表或已知的单行结果。然而，是否能获得system类型取决于多种因素，包括查询的具体情况、表的存储引擎、数据分布以及MySQL服务器的配置。

准备test01

create table test01
(
    tid int(3),
    tname varchar(20)
);
insert into test01 values(1,'a');

衍生表只有一条数据的主查询

explain select * from (select * from test01) t where tid=1;
⼦查询查询得出⼀条数据（该条数据构成衍⽣表），通过衍⽣表的数据进⾏主查询，其衍⽣表的索引类型为system。

linux下可以达到system

windows下同样的sql则达不到

参考MySQL索引类型（type）分析

只有一条数据的表（系统表）

使用innodb存储引擎不行，是const

使用myisam可以得到system，不需要加索引

参考 mysql EXPLAIN type列的system值

mysql> create table test02
(
    tid int(3),
    tname varchar(20)
) engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test02 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> explain select tid from test02 where tid=1;
+----+-------------+--------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test02 | system | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+--------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.05 sec)

mysql> explain select * from (select * from test02) t where tid=1;
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 | NULL  |
|  2 | DERIVED     | test02     | system | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.07 sec)

mysql>

const

使用Primary key或unique索引仅仅能查到一条数据的SQL，如主键置于where列表中，MySQL就能将该查询转换为一个常量例子：

主键索引可以达到const

alter table test01 add constraint tid_pk primary key(tid); -- 增加主键
-- tid是主键
mysql> explain select tid from test01 where tid=1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test01 | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.06 sec)

单值索引不行，达不到const

alter table test01 drop primary key ; -- 删除主键
create index test01_index on test01(tid); -- 给tid创建索引
explain select tid from test01 where tid = 1;

eq_ref:唯一性索引

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描，例子：被驱动表使用主键索引，结果唯一

主键索引

-- 设置teacherCard的tcid为主键索引，teacher表中无索引
mysql> alter table teacherCard add constraint pk_tcid primary key(tcid);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | tc    | index | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using index                                        |
|  1 | SIMPLE      | t     | ALL   | NULL          | NULL    | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.07 sec)

mysql> select * from teacher;
+-----+-------+------+
| tid | tname | tcid |
+-----+-------+------+
|   1 | tz    |    1 |
|   2 | tw    |    2 |
|   3 | tl    |    3 |
|   4 | tll   |    4 |
|   5 | tik   |    5 |
|   6 | tzz   |    6 |
+-----+-------+------+
6 rows in set (0.05 sec)

mysql> select * from teacherCard;
+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | tldesc |
+------+--------+
3 rows in set (0.05 sec)

mysql> delete from teacher where tid > 3;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from teacher;
+-----+-------+------+
| tid | tname | tcid |
+-----+-------+------+
|   1 | tz    |    1 |
|   2 | tw    |    2 |
|   3 | tl    |    3 |
+-----+-------+------+
3 rows in set (0.04 sec)

mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL       |    3 | Using where |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY       | PRIMARY | 4       | db1.t.tcid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.06 sec)

mysql> insert into teacherCard (tcid,tcdesc) values (4,'tzdesc4');
Query OK, 1 row affected (0.00 sec)

mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | ALL    | NULL          | NULL    | NULL    | NULL       |    3 | Using where |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY       | PRIMARY | 4       | db1.t.tcid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.06 sec)

mysql>

从表（主键表）即 teacherCard 为 eq_ref ：

以上SQL,用到的索引是t.tcid,即teacher表中的tcid字段;如果teacher表的数据个数和连接查询的数据个数一致(都是3条数据)，则有可能满足eq_ref,否则无法满足。

1.给从表（主键表）添加数据，数据多余主表（无索引的表），从表也可以得到eq_ref

2.给主表（无索引表）添加数据，数据多余从表（主键表），不能得到eq_ref

总结：应该说是主表的数据都要在从表中匹配上，即便从表的数据多于主表。

唯一索引

-- 设置teacherCard的tcid为唯一索引，teacher表中无索引
mysql> alter table teacherCard add constraint uq_tcid unique(tcid);

mysql> explain select t.tid,tc.tcid from teacher t,teacherCard tc where t.tcid=tc.tcid;
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL    | NULL    | NULL       |    3 | Using where |
|  1 | SIMPLE      | tc    | ref  | uq_tcid       | uq_tcid | 5       | db1.t.tcid |    1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.03 sec)

mysql>

ref:非唯一性索引

非唯一性索引扫描，返回匹配某个单独值的所有行(0，一条，多条)，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体

给teacher的tname添加索引，根据tname查询可以得到ref

alter table teacher add index index_name(tname);

返回1条可以得到ref

mysql> select tname from teacher where tname='tz';
+-------+
| tname |
+-------+
| tz    |
+-------+
1 row in set (0.00 sec)

mysql> explain select tname from teacher where tname='tz';
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 99      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

mysql>

返回0条也可以得到ref

mysql> select tname from teacher where tname='tz1';
Empty set (0.00 sec)

mysql> explain select tname from teacher where tname='tz1';
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 99      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

返回多条可以得到ref

mysql> insert into teacher values(4,'tz',4);  -- 添加一条数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher where tname = 'tz';
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    4 | tz    |    4 |
+------+-------+------+
2 rows in set (0.00 sec)

mysql> explain select tname from teacher where tname='tz';
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 99      | const |    2 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

mysql>

range:检索指定范围的行

只检索给定范围的行，使用一个索引来选择行，一般是where后面的范围(between,> < >= 特殊:in有时候会失效,从而转为无索引all)

给tid添加索引

alter table teacher add index tid_index(tid);

大于、小于

mysql> explain select * from teacher where tid > 2;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | teacher | range | tid_index     | tid_index | 5       | NULL |    2 | Using index condition |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.05 sec)

mysql> explain select * from teacher where tid > 2 and tid < 4;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | teacher | range | tid_index     | tid_index | 5       | NULL |    1 | Using index condition |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.05 sec)

mysql>

in有可能会失效

mysql> explain select * from teacher where tid in (3,4);
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | teacher | ALL  | tid_index     | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql>

between ... and ... 可能会失效，仅仅数据的改变都可能会引起索引的失效SQL优化,是一种概率层面的优化。

mysql> explain select * from teacher where tid between 2 and 4;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | teacher | ALL  | tid_index     | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> explain select * from teacher where tid between 3 and 4;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | teacher | range | tid_index     | tid_index | 5       | NULL |    2 | Using index condition |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.05 sec)

mysql>

index

全索引扫描，Full index Scan，只需要扫描索引表，不需要非索引列数据(即不需要回表查询)

mysql> explain select tid from teacher;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | teacher | index | NULL          | tid_index | 5       | NULL |    4 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> explain select tid,tname from teacher;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | teacher | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.05 sec)

mysql>

all

全表扫，Full Table Scan，将遍历全表以找到匹配行，需要回表查询

mysql> explain select * from teacher;  -- 查询表中所有数据
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | teacher | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.05 sec)

mysql>

possible_keys与key

possible_keys:可能用到的索引，是一种预测，不准。

key:实际使用到的索引

情况1：预测不使用索引，但实际却使用了索引 索引类型为index的例子。

情况2：预测使用索引，但实际却没使用索引

情况3：如果possible_key/key是NULL,则说明没用索引

key_len:索引的长度

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确度的情况下，长度越短越好

key_len显示的值为索引字段最大的可能长度，并非实际使用长度，即key_len是根据定义计算而得，不是通过表内检索出的

作用:用于判断复合索引是否被完全使用 (a,b,c)

长度计算方式 参考Mysql 索引 key_len 计算方式最强解析

先使用show create table teacher; 查看字段长度
1.数据类型本身占用空间（字符类型需要乘以字符集长度）
2.如果可以为空+1
3.变长类型+2

先计算一个非空定长的char类型的索引长度

mysql> create table test_k1
(
    name char(20) not null default ''
);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table test_k1 add index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

mysql> explain select * from test_k1 where name = '';
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_k1 | ref  | index_name    | index_name | 20      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.04 sec)

mysql> explain select name from test_k1 where name = 'tz';
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_k1 | ref  | index_name    | index_name | 20      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.05 sec)

mysql> show create table test_k1;
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                    |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| test_k1 | CREATE TABLE `test_k1` (
  `name` char(20) NOT NULL DEFAULT '',
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql>

新插入一个可以为null的namel列再看看索引长度

mysql> alter table test_k1 add column namel char(20); 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_k1 add index index_namel(namel);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_k1 where namel ='';
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test_k1 | ref  | index_namel   | index_namel | 21      | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
1 row in set (0.05 sec)

mysql> show create table test_k1;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_k1 | CREATE TABLE `test_k1` (
  `name` char(20) NOT NULL DEFAULT '',
  `namel` char(20) DEFAULT NULL,
  KEY `index_name` (`name`),
  KEY `index_namel` (`namel`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql>

如果索引字段可以为Null,则会使用1个字节用于标识。

name和namel的单值索引改成一个复合索引

mysql> drop index index_name on test_k1 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index index_namel on test_k1 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_k1 add index name_name1_index(name,namel);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_k1 where namel = '';
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_k1 | index | NULL          | name_name1_index | 41      | NULL |    1 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.05 sec)

mysql> explain select * from test_k1 where name = '';
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_k1 | ref  | name_name1_index | name_name1_index | 20      | const |    1 | Using where; Using index |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
1 row in set (0.05 sec)

mysql>

再插入一个varchar类型可为null的列

mysql> alter table test_k1 add column name2 varchar(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_k1 add index name2_index (name2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_k1 where name2 = '';
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test_k1 | ref  | name2_index   | name2_index | 23      | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------+
1 row in set (0.05 sec)

mysql>

20*1（拉丁为单字节字符编码） + 2 (可变长度) +1 (可以为null)

ref :注意与type中的ref值区分。

作用：指明当前表所参照的字段。select .... where a.c = b.x ;(可以是常量const)

mysql> explain select * from course c, teacher t where c.tid = t.tid and t.tname ='tw' ;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where                    |
|  1 | SIMPLE      | c     | ALL  | tid_index     | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

为null看看是不是没有索引，没有索引先加索引
重置测试表的数据，并添加索引
alter table course add index tid_index (tid);
alter table teacher add index index_tid (tid); --前面已添加索引
alter table teacher add index tname_index (tname);

mysql> explain select * from course c, teacher t where c.tid = t.tid and t.tname ='tw' ;
+----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+
| id | select_type | table | type | possible_keys         | key         | key_len | ref        | rows | Extra       |
+----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | ref  | tname_index,index_tid | tname_index | 99      | const      |    1 | Using where |
|  1 | SIMPLE      | c     | ref  | tid_index             | tid_index   | 5       | test.t.tid |    1 | Using where |
+----+-------------+-------+------+-----------------------+-------------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

rows:被索引优化査询的数据个数（实际通过索引而査询到的数据个数）

数据小的表优先查询

mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3);
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.04 sec)

给teacher表插入3条数据

mysql> insert into teacher (tid,tname,tcid) values (4,'tll',4),(5,'tik',5),(6,'tzz',6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from course,teacher,teacherCard where course.tid = teacher.tid and teacher.tcid = teacherCard.tcid and (course.cid=2 or teacher.tid=3);
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | teacherCard | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                                               |
|  1 | SIMPLE      | course      | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | teacher     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.05 sec)

执行顺序： teachercard3 --》 course4 --》 teacher6

表的执行顺序因数据的个数改变而改变的原因:笛卡儿积

    a     b    c 
    4     3    2    =       2*3=6*4=24
                            3*4=12*2=24

Extra:

参考MySQL优化常见Extra分析

准备数据

create table user
(
id int,
name char(3),
age int,
index idx_id(id),
index idx_name(name),
index idx_age(age)
);

-- 新插入一列，并添加索引
alter table user add height int;
alter table user add index index_height(height);

(i). using filesort:文件内排序(性能极差)

得到所需结果集，需要对所有记录进行文件排序。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

对于单值索引,如果order和where是同一个字段,则不会出现using filesort;如果不是同一个字段,则会出现using filesort

mysql> explain select * from user where height =180 order by id;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where height =180 order by height;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

对于复合索引,需要符合最佳左前缀，否则会出现using filesort

删除所有索引,创建一个复合索引
alter table user add index index_id_name_age(id,name,age);

Ⅰ:仅使用where的情况

参考索引优化--最佳左前缀法则

explain select id from user where id=20;

explain select id from user where id=20 and name='tz';

explain select id from user where id=20 and name='tz' and age=20;

explain select id from user where name='tz';

explain select id from user where name='tz' and age=20;

explain select id from user where id=20 and age=20;

mysql> explain select id from user where id=20;

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select id from user where id=20 and name='tz';
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 15      | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select id from user where id=20 and name='tz' and age=20;
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref               | rows | Extra                    |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 20      | const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

总结i.如果(a,b,c,d)复合索引和使用的顺序全部一致(且不跨列使用),则复合索引全部使用。

Ⅱ:where和order by同时使用的情况

-- where和order by使用的字段拼起来不要跨列，并且不能倒序
explain select * from user where id = 1 order by name;
explain select * from user where name='tz' order by id;  -- Using filesort

explain select * from user where age=20 order by id; -- Using filesort
explain select * from user where name='tz' order by age; -- Using filesort

-- and前后的字段顺序可以调换，不影响结果
explain select * from user where id=1 and name='tz' order by age;
explain select * from user where name='tz' and id=1 order by age;

explain select * from user where name='tz' order by name; -- where和order后面是同一字段 不发生Using filesort

mysql> explain select * from user where name='tz' order by id;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

(ii). using temporary(性能较低)

需要建立临时表(temporary table)来暂存中间结果。

group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

# 仅使用复合索引
mysql> explain select * from user where id=1 group by name order by name;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from user where id=1 group by name order by age;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>  explain select * from user where id=1 group by age order by age;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
1 row in set (0.01 sec)

# 仅单值索引
mysql> explain select height from user where height=1 group by height order by height;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

# 单值索引和复合索引一起使用
仅使用 一个单值索引 和 复合索引中的一个字段

mysql> explain select height from user where height=1 group by name order by name;   -- 会触发Using temporary
+----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select height from user where id=1 group by height order by height;   -- 会触发Using temporary
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select height from user where height=1 group by name order by height;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                        |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using temporary |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+

mysql> explain select height from user where height=1 group by id order by height;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                        |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using temporary |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+------------------------------+

(iii). using index :索引覆盖（性能提升）

SQL所需要返回的所有列数据均在已使用的一棵索引树上，而无需访问实际的行记录。

不读取原文件，只从索引文件中获取数据(不需要回表查询）

例如：user表中有两个索引一个复合索引index_id_name_age，一个单值索引 index_height

如果用到了索引覆盖(using index时）会对possible_keys和key造成影响：

a. 如果没有where，则索引只出现在key中；

b. 如果有where则索引出现在key和possible_keys中。

(Ⅳ).using where

SQL使用了where条件过滤数据
需要注意的是：
（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；
本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。
常见的优化方法为，在where过滤属性上添加索引。

**Using where只是过滤元组，和是否读取数据文件或索引文件没有关系 **
参考MySQL查询优化概念辨析---Using where 和 Using index
参考Mysql进行Explain分析时Extra列中Using where、Using index、Using temporary、Using filesort

mysql> explain select id,height from user where id=1;  -- 删掉height还是Using where
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id from user where id=1;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_id_name_age | index_id_name_age | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select id,height from user where height=180;  -- 删掉id还是Using where
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select height from user where height=180;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | index_height  | index_height | 5       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select id from user;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | index_id_name_age | 20      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select height from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | index_height | 5       | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

(Ⅴ). impossible where : where子句永远为false

mysql> explain select id from user where id=1 and id=2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

(Ⅵ) Using join buffer:连接缓存(性能较低)

两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。
例子:ref中的例子

posted @ 2022-07-11 10:46 姬雨晨阅读(124) 评论(0) 编辑收藏举报

刷新页面返回顶部

jiyuchen1

sql优化-1-查询执行计划:explain

SQL执行流程

SQL性能优化

测试数据

explain:查看sql执行计划

id编号

select_type和table

type:索引类型、或者直接称为类型

system

const

eq_ref:唯一性索引

ref:非唯一性索引

range:检索指定范围的行

index

all

possible_keys与key

key_len:索引的长度

ref :注意与type中的ref值区分。

rows:被索引优化査询的数据个数（实际通过索引而査询到的数据个数）

Extra:

准备数据

(i). using filesort:文件内排序(性能极差)

对于单值索引,如果order和where是同一个字段,则不会出现using filesort;如果不是同一个字段,则会出现using filesort

对于复合索引,需要符合最佳左前缀，否则会出现using filesort

Ⅰ:仅使用where的情况

Ⅱ:where和order by同时使用的情况

(ii). using temporary(性能较低)

(iii). using index :索引覆盖（性能提升）

(Ⅳ).using where

(Ⅴ). impossible where : where子句永远为false

(Ⅵ) Using join buffer:连接缓存(性能较低)

公告

jiyuchen1

sql优化-1-查询执行计划:explain

SQL执行流程

SQL性能优化

测试数据

explain:查看sql执行计划

id编号

select_type和table

type:索引类型、或者直接称为类型

system

const

eq_ref:唯一性索引

ref:非唯一性索引

range:检索指定范围的行

index

all

possible_keys与key

key_len:索引的长度

ref :注意与type中的ref值区分。

rows:被索引优化査询的数据个数（实际通过索引而査询到的数据个数）

Extra:

准备数据

(i). using filesort:文件内排序(性能极差)

对于单值索引,如果order和where是同一个字段,则不会出现using filesort;如果不是同一个字段,则会出现using filesort

对于复合索引,需要符合 最佳左前缀，否则会出现using filesort

Ⅰ:仅使用where的情况

Ⅱ:where和order by同时使用的情况

(ii). using temporary(性能较低)

(iii). using index :索引覆盖（性能提升）

(Ⅳ).using where

(Ⅴ). impossible where : where子句永远为false

(Ⅵ) Using join buffer:连接缓存(性能较低)

公告

对于复合索引,需要符合最佳左前缀，否则会出现using filesort