mysql教程进行SQL调优，你得晓得要调优的SQL语句是怎么施行的，查看SQL语句的具体施行历程，以加速SQL语句的施行效率。可以运用explain+SQL语句来模拟优化器施行SQL查询语句，从而晓得MySQL是怎样处置SQL语句的。

mysql教程栏目介绍施行规划explain与索引数据构造

预备工作

先建好数据库表，演示用的MySQL表，建表语句：

CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `empno` int(11) DEFAULT NULL COMMENT '雇员工号',  `ename` varchar(255) DEFAULT NULL COMMENT '雇员姓名',  `job` varchar(255) DEFAULT NULL COMMENT '工作',  `mgr` varchar(255) DEFAULT NULL COMMENT '经理的工号',  `hiredate` date DEFAULT NULL COMMENT '聘用日期',  `sal` double DEFAULT NULL COMMENT '薪水',  `comm` double DEFAULT NULL COMMENT '津贴',  `deptno` int(11) DEFAULT NULL COMMENT '所属部门号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表';CREATE TABLE `dept` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `deptno` int(11) DEFAULT NULL COMMENT '部门号',  `dname` varchar(255) DEFAULT NULL COMMENT '部门名称',  `loc` varchar(255) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';CREATE TABLE `salgrade` (  `id` int(11) NOT NULL COMMENT '主键',  `grade` varchar(255) DEFAULT NULL COMMENT '品级',  `lowsal` varchar(255) DEFAULT NULL COMMENT '最低薪水',  `hisal` varchar(255) DEFAULT NULL COMMENT '最高薪水',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='薪水品级表';CREATE TABLE `bonus` (  `id` int(11) NOT NULL COMMENT '主键',  `ename` varchar(255) DEFAULT NULL COMMENT '雇员姓名',  `job` varchar(255) DEFAULT NULL COMMENT '工作',  `sal` double DEFAULT NULL COMMENT '薪水',  `comm` double DEFAULT NULL COMMENT '津贴',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='奖金表'; 

后续施行规划，查询优化，索引优化等等见识的演练，基于以上几个表来操纵。

MySQL施行规划

要进行SQL调优，你得晓得要调优的SQL语句是怎么施行的，查看SQL语句的具体施行历程，以加速SQL语句的施行效率。

可以运用explain + SQL语句来模拟优化器施行SQL查询语句，从而晓得MySQL是怎样处置SQL语句的。

对于explain可以看看官网介绍。

explain的导出格局

mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 

字段id，select_type等字段的解释：

Column	Meaning
id	The SELECT identifier（该SELECT标识符）
select_type	The SELECT type（ 该SELECT类型）
table	The table for the output row（导出该行的表名）
partitions	The matching partitions（匹配的分区）
type	The join type（连贯类型）
possible_keys	The possible indexes to choose（可能的索引选中）
key	The index actually chosen（现实选中的索引）
key_len	The length of the chosen key（所选键的长度）
ref	The columns compared to the index（与索引比拼的列）
rows	Estimate of rows to be examined（检查的预估行数）
filtered	Percentage of rows filtered by table condition（按表前提过滤的行百分比）
extra	Additional information（附加信息）

id

select查询的序列号，包括一组数字，表示查询中施行select子句或者操纵表的次序。

id号分为三类：

• 要是id雷同，那么施行次序从上到下

mysql> explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.lowsal and sg.hisal;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | sg    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 

这个查询，用explain施行一下，id序号都是1，那么MySQL的施行次序就是从上到下施行的。

• 要是id不一样，要是是子查询，id的序号会递增，id值越大优先级越高，越先被施行

mysql> explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALEDept');
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|  1 | SIMPLE       |  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                               |
|  1 | SIMPLE       | e           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | d           | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                                        |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 

这个例子的施行次序是先施行id为2的，然后施行id为1的。

• id雷同和不一样的，同时存在：雷同的可以以为是一组，从上往下次序施行，在所有组中，id值越大，优先级越高，越先施行

还是上面阿谁例子，先施行id为2的，然后按次序从上往下施行id为1的。

select_type

主要用来辨论查询的类型，是普通查询还是结合查询还是子查询。

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)
PRIMARY	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

• SIMPLE 简略的查询，不包括子查询和union

mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 

• primary 查询中若包括任何复杂的子查询，最外层查询则被标志为Primary
• union 若第二个select涌现在union之后，则被标志为union

mysql> explain select * from emp where deptno = 1001 union select * from emp where sal < 5000;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
|  2 | UNION        | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where     |
| NULL | UNION RESULT |  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 

这条语句的select_type包括了primary和union

• dependent union 跟union相似，此处的depentent表示union或union all结合而成的效果会挨外部表影响
• union result 从union表猎取效果的select
• dependent subquery subquery的子查询要挨到外部表查询的影响

mysql> explain select * from emp e where e.empno  in ( select empno from emp where deptno = 1001 union select empno from emp where sal < 5000);
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | PRIMARY            | e          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
|  3 | DEPENDENT UNION    | emp        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where     |
| NULL | UNION RESULT       |  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 

这条SQL施行包括了PRIMARY、DEPENDENT SUBQUERY、DEPENDENT UNION和UNION RESULT

• subquery 在select或者where列表中包括子查询

举例：

mysql> explain select * from emp where sal > (select avg(sal) from emp) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | PRIMARY     | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
|  2 | SUBQUERY    | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 

• DERIVED from子句中涌现的子查询，也叫做派生表
• MATERIALIZED Materialized subquery？
• UNCACHEABLE SUBQUERY 表示运用子查询的效果不克不及被缓存

例如：

mysql> explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | PRIMARY              | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
|  2 | UNCACHEABLE SUBQUERY | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 

• uncacheable union 表示union的查询效果不克不及被缓存

table

对应行正在拜访哪一个表，表名或者又名，可能是暂时表或者union合并效果集。

1. 要是是具体的表名，则表白从现实的物理表中猎取数据，固然也可以是表的又名
2. 表名是derivedN的情势，表示运用了id为N的查询发生的衍生表
3. 当有union result的时候，表名是union n1,n2等的情势，n1,n2表示参与union的id

type

type显示的是拜访类型，拜访类型表示我是以何种方式去拜访我们的数据，最容易想到的是全表扫描，直接暴力的遍历一张表去寻觅需要的数据，效率非常低下。

拜访的类型有许多，效率从最佳到最坏顺次是：

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

个别状况下，得保障查询至少达到range级别，最佳能达到ref

• all 全表扫描，个别状况下涌现这样的sql语句并且数据量比拼大的话那么就需要进行优化

平常，可以通过增加索引来以免ALL

• index 全索引扫描这个比all的效率要好，主要有两种状况：
  • 一种是目前的查询时遮盖索引，即我们需要的数据在索引中就可以索取
  • 一是运用了索引进行排序，这样就以免数据的重排序
• range 表示应用索引查询的时候限定了范畴，在指定范畴内进行查询，这样以免了index的全索引扫描，适用的操纵符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

官网上举例如下：

SELECT * FROM tbl_name WHERE key_column = 10;

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index_subquery 应用索引来关联子查询，不再扫描全表

value IN (SELECT key_column FROM single_table WHERE some_expr)

• unique_subquery 该连贯类型相似与index_subquery，运用的是独一索引

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_merge 在查询历程中需要多个索引组合运用
• ref_or_null 关于某个字段既需要关联前提，也需要null值的状况下，查询优化器会选中这种拜访方式

SELECT * FROM ref_table

WHERE key_column=expr OR key_column IS NULL;

• fulltext 运用FULLTEXT索引施行join
• ref 运用了非独一性索引进行数据的查寻

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• eq_ref 运用独一性索引进行数据查寻

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• const 这个表至多有一个匹配行

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

例如：

mysql> explain select * from emp where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 

• system 表只要一行记载（等于系统表），这是const类型的特例，平时不会涌现

possible_keys

显示可能利用在这张表中的索引，一个或多个，查询波及到的字段上若存在索引，则该索引将被列出，但纷歧定被查询现实运用

注意：

在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，并且所有叶子节点（即数据节点）之间是一种链式环构造。

因而可以对 B+Tree 进行两种查寻运算：一种是关于主键的范畴查寻和分页查寻，另一种是从根节点开端，进行随机查寻。

因为B+树叶子结点只寄存data，根节点只寄存key，那么我们盘算一下，即便只要3层B+树，也能制成千万级另外数据。

你得晓得的技（zhuang）术（b）名词

假如有这样一个表如下，其中id是主键：

mysql> select * from stu;
+------+---------+------+| id   | name    | age  |
+------+---------+------+|    1 | Jack Ma |   18 |
|    2 | Pony    |   19 |
+------+---------+------+ 

回表

我们对普通列建普通索引，这时候我们来查：

select * from stu where name='Pony'; 

因为name建了索引，查询时先寻name的B+树，寻到主键id后，再寻主键id的B+树，从而寻到整行记载。

这个终究会回到主键上来查寻B+树，这个就是回表。

遮盖索引

要是是这个查询：

mysql> select id from stu where name='Pony'; 

就没有回表了，由于直接寻到主键id，返回就完了，不需要再寻其他的了。

没有回表就叫遮盖索引。

最左匹配

再来以name和age两个字段建组合索引(name, age)，然后有这样一个查询：

select * from stu where name=? and age=? 

这时按照组合索引(name, age)查询，先匹配name，再匹配age，要是查询酿成这样：

select * from stu where age=? 

直接不按name查了，此时索引不会生效，也就是不会按照索引查询---这就是最左匹配准则。

参加我就要按age查，还要有索引来优化呢？可以这样做：

• （举荐）把组合索引(name, age)换个次序，建(age, name)索引
• 或者直接把age字段独自建个索引

索引下推

可能也叫谓词下推。。。

select t1.name,t2.name from t1 join t2 on t1.id=t2.id 

t1有10笔记录，t2有20笔记录。

我们猜想一下，这个要末按这个方式施行：

先t1,t2按id合并（合并后20条），然后再查t1.name,t2.name

或者：

先把t1.name,t2.name寻出来，再按照id关联

要是不运用索引前提下推优化的话，MySQL只能依据索引查询出t1,t2合并后的所有行，然后再顺次比拼可否相符全部前提。

当运用了索引前提下推优化技术后，可以通过索引中存储的数据推断目前索引对应的数据可否相符前提，只要相符前提的数据才将整行数据查询出来。

小结

1. Explain 为了晓得优化SQL语句的施行，需要查看SQL语句的具体施行历程，以加速SQL语句的施行效率。
2. 索引长处及用场。
3. 索引采纳的数据构造是B+树。
4. 回表，遮盖索引，最左匹配和索引下推。

更多相干免费学习举荐：mysql教程(视频)

以上就是MySQL 施行规划explain与索引数据构造推演的细致内容，更多请关注 百分百源码网 其它相干文章！