Mysql优化2

  

7 表的设计准则

有时候也会反三范式，比如将部门名称也维护到员工表中，这样是为了避免表的关联操作

8 DQL的执行过程

9 Explain

9.1 Explain的作用

9.2 Explain

9.2.1 执行顺序ID分析

1、ID相同：执行顺序由上自下

EXPLAIN SELECT * from employee e,department d,customer c
where e.dep_id = d.id and e.cus_id = c.id;

2、ID不同：ID越大越先执行

如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行

EXPLAIN SELECT * from department
WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

3、ID相同不同同时存在

可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
deriverd 衍生出来的虚表

EXPLAIN select * from department d, 
(select max(salary),dep_id from employee GROUP BY dep_id ) t 
where d.id = t.dep_id;

包含一组数字,表示查询中执行select子句或操作表的顺序
总结：
	相同顺序走
	不同看谁大
	大的先执行

9.2.2 查询类型SelectType

作用：查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询

	SIMPLE	:简单select查询,查询中不包含子查询或者UNION
	PRIMARY :查询中若包含任何复杂的子查询,最外层查询则被标记为primary
	SUBQUERY:在select或where中包含了子查询
	DRIVIED :在from列表中包含的子查询被标记为derived(衍生),把结果放在临时表当中
	UNION	:若第二个select出现的union之后,则被标记为union
		  	 若union包含在from子句的子查询中,外层select将被标记为deriver
	UNION RESULT:从union表获取结果select,两个UNION合并的结果集在最后

SIMPLE:表示简单select查询,查询中不包含子查询或UNION

EXPLAIN SELECT * FROM department;

PRIMARY:查询中若包含任何复杂的子查询，最外层查询则被标记为PRIMARY

EXPLAIN SELECT * from department
WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

DRIVIED:在from列表中包含的子查询被标记为drivied

EXPLAIN select * from department d, 
(select max(salary),dep_id from employee GROUP BY dep_id ) t 
where d.id = t.dep_id;

UNION和UNION_RESULT

table：显示这一行的数据是关于哪张表的
partitions:如果查询是基于分区表的话，会显示查询访问的分区

9.2.3 访问类型排列Type*

表示是以哪种方式访问这张表的,结果值最好到最差
一般来说,保证查询至少达到range级别
最好能达到ref

Type	说明
SYSTEM	表中有一行记录，这是 CONST类型的特例，平时不会出现
CONST	通过索引一次就找到了 CONST用户比较primary或unique索引 直接查询主键或者唯一索引 因为只匹配一行数据所以很快
EQL_REF	唯一性索引扫描 对于每个索引键，表中只有一条记录与之匹配 常见于主键或唯一索引扫描
REF	非唯一性索引扫描 ,返回匹配某个单独值的所有行 本质上也是一种索引访问，它返回所有匹配某个单独值的行 可能会找到多个符合条件的行 所以它应该属于查找和扫描的混合体
RANGE	只检索给定范围的行，使用一个索引来选择行 key列显示使用了哪个索引 一般是在where语句中出现between < > in 等查询 这种扫描索引比扫描全表要好 因为它只需要开始于索引的某一点，而结束于另一点 不用扫描全部索引
INDEX	Full Index Scan INDE与ALL区别为INDEX类型只遍历索引树，通常比ALL要快 因为索引文件通常比索引文件要小 ALL和INDEX都是读全表，但INDEX是从索引中读取，ALL从硬盘读取
ALL	将全表进行扫描,从硬盘当中读取数据 如果出现了ALL数据量非常的大，一定要去做优化

演示CONST:

演示EQ_REF:两张表的主索引一一对应

演示REF：非唯一性索引扫描，前提是要使用索引。
不添加索引：
explain select * from employee e,department d where e.dep_id = d.id
explain select e.dep_id ,d.id  from employee e,department d where e.dep_id = d.id

添加索引：
alter table employee add index(dep_id);
explain select * from employee e,department d where e.dep_id = d.id
explain select e.dep_id ,d.id  from employee e,department d where e.dep_id = d.id

演示RANGE:

演示INDEX:

演示ALL:

9.2.4 可能使用索引POSSIBLE_KEYS以及实际使用索引KEY*

可能不会使用索引，但实际使用索引

alter table employee add index(dep_id);
EXPLAIN SELECT dep_id from employee 

可能会使用索引，但实际没有使用索引

alter table employee add index(dep_id);
explain select * from employee e,department d where e.dep_id = d.id
explain select e.dep_id ,d.id  from employee e,department d where e.dep_id = d.id

原因是使用*导致的

9.2.5 KEY_LEN索引长度

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .

alter table employee drop INDEX dep_id
alter table employee add index(dep_id,name,age);
EXPLAIN select * from employee where dep_id = 1 and name = '鲁班' and  age = 10
EXPLAIN select * from employee where dep_id = 1 and name = '鲁班' 
EXPLAIN select * from employee where dep_id = 1

EXPLAIN select * from employee where name = '鲁班' 
索引失效

9.2.6 REF索引值引用情况

REF用来判断索引是否被引入以及到底使用了哪几个索引

d使用了主键索引，并且引用了e表的dep_id字段
EXPLAIN SELECT * FROM employee e,department d 
WHERE e.dep_id = d.id and e.cust_id = 1;

e表索引引用的值是一个常量，c表主索引引用了e表的cust_id字段
d表的主键索引引用了e表的dept_id字段
EXPLAIN SELECT * FROM employee e,department d,customer c 
where e.dep_id = d.id and e.cust_id = c.id and e.name = '鲁班'

e表的联合索引引用了表的name字段和一个常量字段
EXPLAIN SELECT * FROM employee e,customer c 
where c.name = e.name and e.dep_id = 1

9.2.7 ROWS和FILTERED

Mysql版本不一样：
没有建立索引：

建立了索引后：

删除e表的所有索引
EXPLAIN SELECT e.dep_id,d.id FROM employee e ,department d where e.dep_id = d.id

ALTER TABLE employee add INDEX (dep_id)
EXPLAIN SELECT e.dep_id,d.id FROM employee e ,department d where e.dep_id = d.id

9.2.8 额外信息EXTRA

EXTRA	说明
Using filesort	说明mysq会对数据使用一个外部的索引排序 而不是按照表内的索引排序进行 Mysql中无法利用索引完成排序操作成为‘文件排序’
Using temporary	使用临时表保存中间结果，Mysql在对查询结果排序时，使用了临时表 常见于排序order by和分组查询group by
Using index	表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好 如果同时出现using where 表明索引被用来执行索引键值的查找 如果没有同时出现using where表明索引 用来读取数据而非执行查找动作
Using where	表明使用了wher过滤
Using join buffer	使用了连接缓存
impossible where	where 子句的值总是false,不能用来获取任何元素

演示Using fileSort

Using temporary

Using Index

Using Where