mysql使用explain优化

explain:

解析过程：

from … on … join … where … group by … having … select … dinstinct … order by … limit …

1.id：执行顺序

表的执行顺序，因数量的个数改变而改变的原因：笛卡尔积

数据小的表优先查询，id值不同，id值越大的越优先；id值相同，从上往下顺序执行

2.select_type

PRIMARY 包含子查询SQL中的主查询（最外层）

SUBQUERY 包含子查询SQL中的子查询（非最外层）

SIMPLE 简单查询（不包含子查询，union）

DERIVED 衍生查询（使用到了临时表）

• 在from子查询中只有一张表

  select cr.cname from (select * from course where tid in (1,2)) cr

• 在from子查询中，如果有table1 union table2 ，则table1就是derived

3.type:索引类型，类型

system>const>eq_ref>ref>range>index>all

要对type进行优化的前提，有索引

system:只有一条数据的系统表，或者衍生表只有一条数据的主查询

const:仅仅能查到一条数据的sql，用于Primary key 或 unique 索引（类型与索引类型有关）

eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多，不能0），常见唯一索引和主键索引

ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0或多）

rang:检索指定范围的行，where后面是一个范围查询(between,in,<,>,>=等)

index:查询全部索引中的数据

all：查询全部表数据

4.possible_key:可能使用到的索引

如果possible_key/key是null，则说明没有用索引

5.key:实际使用到的索引

`6.key_len：索引的长度``

作用：用于判断复合索引是否被完全使用（在utf8中一个字符占三个字节）

如果索引字段可以为null，则会使用1个字节用于标识

如果索引字段为varchar，则会用两个字节进行标识

utf8：3字节

gbk：2字节

latin：1字节

7.ref:注意与type中的ref值区分

作用：指明当前表所参照的字段（必须两个都有索引才会指明，否则为null或者常量：const）

8.rows:被索引优化查询的数据个数

9.extra

一。

using filesort: 性能消耗大，需要“额外”的一次排序（查询），常见于order by

小结：对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段则会出现using filesort

避免：where哪些字段就order by哪些字段

复合索引：不能跨列（最佳左前缀）

避免：where和order by按照复合索引的顺序使用，不要跨列或无序使用

二。

using temporary:性能损耗大，用到了临时表，一般出现在group by语句中，已经有表了，但不适用，必须再来一张表

避免：查什么哪些列，就根据那些列group by

三。

using index：性能提升：索引覆盖。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）

只要使用到的列，全部都在索引中，就是索引覆盖

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

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

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

四。

using where：需要回表查询

假设age是索引列

但查询语句select age,name from … where age=… 此语句中必须回原表查询name，因此会出现using where （在5.7之后已经改为在索引表里面查询）

五。

impossible where:where子句永远为false

六。

using join buffer:extra中的一个选项，作用：mysql引擎使用了 连接缓存（说明你sql写太差了，机器给你优化）

优化案例

单表优化

# 查询 authorid=1 且 typeid=2或3的 bid
explain select bid from book where typeid in (2,3) and authorid=1 order by typeid desc;
# 优化：加索引
alter table book add index idx_bta(bid,typeid,authorid) -- 索引一旦进行升级优化，需要将之前的废弃索引删除，防止干扰
# 根据sql实际解析的顺序，调整索引的顺序
alter table book add index idx_tab(typeid,authorid,bid) -- 虽然可以回表查询bid，但是将bid放到索引中，可以提升using index
# 再次优化（之前是index级别）
explain select bid from book where authorid=1 and typeid in (2,3) order by typeid desc;
alter table book add index idx_tab(authorid,typeid,bid) -- 提升到ref
小结：
'1.索引不能跨列使用（最佳左前缀），保持索引的定义和使用的顺序一致性
2.索引需要逐步优化
3.将含in的范围查询，放到where条件的最后，防止失效'

where authorid=1 and typeid in (2,3) order by typeid desc;
本例中同时出现了using where （需要回原表）；using index (不需要回原表)；
原因authorid在索引里面所以不需要回原表；而typeid虽然也在索引中但是含in的范围查询已经使该typeid失效，因此相当于没有typeid这个索引，所以需要回原表 

两表优化

小表驱动大表（小表放左边，大表放右边）
程序设计原则：如果有一个双重循环，一般建议小表放外层，达标放里层，这样可以加快程序速度（这个跟dbms缓冲区有关，缓冲区尽量给外层循环可减少io次数）
当编写... on t.cid = c.cid 时，将数据量小的表放左边（此时假设t表数据量小）
'一般情况对于左外连接，给左表加索引；右外连接给右表加索引'

三表优化

小表驱动大表
索引建立在经常查询的字段上

explain select a1,a2,a3,a4 from test where a1=1 and a2=2 and a3=3 and a4=4;
#　推荐写法，因为索引的使用顺序（where后面的顺序）和复合索引的顺序一致
explain select a1,a2,a3,a4 from test where a4=1 and a3=2 and a2=3 and a1=4;
#  虽然编写的顺序和索引顺序不一致，但是sql在真正执行前，经过了sql优化器的调整，结果与上条sql是一致的
-- 以上两个sql，使用了全部的复合索引--------------------------------------------
explain select a1,a2,a3,a4 from test where a1=1 and a2=2 and a4=3 order by a3;
#  以上sql用到了a1,a2两个索引，该两个字段不需要回表查询using index;而a4因为跨列使用，造成了该索引失效，需要回表查询，因此是using where;以上可以通过key_len进行验证
explain select a1,a2,a3,a4 from test where a1=1 and a4=4 order by a3;
#  以上sql出现了using filesort(文件内排序，"多了一次额外的查找/排序")，不要跨列使用（where 和 order by拼起来，不要跨列使用）
explain select a1,a2,a3,a4 from test where a1=1 and a4=4 order by a2,a3;
#  不会出现using filesort 因为a4失效，后面连接起来了

总结：
'1.如果（a,b,c,d）复合索引和使用的顺序全部一致，则复合索引全部使用（且不跨列使用）。如果部分一致则使用部分索引

避免索引失效的一些原则

• 复合索引，不要跨列或无序使用（最佳左前缀）

• 复合索引，尽量使用全索引匹配（有abc三个复合索引，最好三个都用上）

• 不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效（对于复合索引，如果左边失效，右侧则全部失效，不能断）

• 复合索引不能使用不等于 != <> 或is null (is not null),否则自身或者右侧所有全部失效

• 尽量使用索引覆盖（using index）

• like尽量以“常量开头”，不要以“%”开头，否则索引失效

  select * from xx where name like “%x%” –name索引失效

  select * from xx where name like “x%” --正常

  select * from xx where name like “%x%” –如果必须使用like进行模糊查询，可以使用索引覆盖挽救一部分

• 尽量不要使用类型转换（显示/隐式），否则索引失效

  select * from xx where name = 123 // 程序底层将123转换为“123”，即进行了类型转换

• 尽量不要使用or，否则索引失效（左侧和右侧都失效）

一些其他的优化方法

exist 和 in

select … from table where exist / in (子查询)

如果主查询数据大，则使用in,效率高

如果子查询数据大，则使用exist，效率高

exist语法：将主查询的结果，放到子查询结果中进行校验（是否有数据，如果有数据则校验成功），如果符合校验，则保留数据

select tname from teacher where exists (select * from teacher)

–等价于 select tname from teacher

in:

select … from table where tid in (1,3,5)

select … from A where id in (select id from B)

order by 优化

using filesort 有两种算法：双路排序，单路排序（根据IO的次数）

Mysql4.1之前默认使用双路，扫描两次磁盘：1.从磁盘读取排序字段，进行排序（在buffer中进行排序） 2.扫描其他字段（IO较消耗性能）

Mysql4.1之后默认使用单路，只读取一次（全部字段），在buffer中进行排序。但这种单路排序，会有一定隐患，有可能是多次io，原因：如果数据量特别大，则无法将所有的数据一次读取完毕，因此会进行“分片读取”

注意：单路排序会比双路排序占用更多的buffer

单路排序在使用时，如果数据量比较大，可以考虑调大buffer的容量大小。

set max_length_for_sort_data = 1024 单位 byte

如果max_length_for_sort_data的值太低，则mysql会自动从单路切换到双路

提高order by查询的策略

• 选择使用单路/双路，调整buffer容量的大小
• 避免select *….
• 复合索引不要跨列使用，否则会出现using filesort
• 保证全部的排序字段 排序的一致性(都是升序或者降序)

sql排查，慢查询日志：mysql提供的一种日志记录，用于记录mysql中响应时间超过阈值的sql语句（long_query_time，默认10秒）

慢查询日志默认是关闭的：建议，开发调优时打开，而最终部署时关闭

检查是否开启了慢查询日志：show variables like “%slow_query_log%”

临时开启：set global slow_query_log = 1 //在内存中开启

永久开启：etc/my.cnf 中追加配置：slow_query_log =1 slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值：show variables like “%long_query_time%”

临时开启：set global long_query_time = 5 // 设置完毕后，重新登陆后才生效

永久开启：etc/my.cnf 中追加配置：long_query_time =3

• 慢查询的sql被记录到了日志中，可以通过日志查看具体的慢sql

• 通过mysqldumpslow 工具查看慢sql

  s：排序方式
  r：逆序
  l：锁定时间
  g：正则匹配模式
  -- 获取返回记录最多的3个sql
  mysqldumpslow -s r -t 3 /var/lib/log.log
  -- 获取访问次数最多的3个sql
  mysqldumpslow -s c -t 3 /var/lib/log.log
  -- 按照时间排序，前十条包含left join查询语句的sql
  mysqldumpslow -s t -t 10 -g "left join" /var/lib/log.log
  

  分析海量数据

  通过存储函数插入海量数据

  存储过程（无返回值）/存储函数（有返回值）

use wen10;
CREATE TABLE dept(
dno INT(5) primary key DEFAULT 0,
dname varchar(20) not null default "",
loc VARCHAR(30) DEFAULT "") ENGINE = INNODB DEFAULT CHARSET= utf8;
CREATE TABLE emp(
eid int(5) PRIMARY key,
ename VARCHAR(20) not null DEFAULT "",
job VARCHAR(20) not null DEFAULT "",
deptno int(5) not null DEFAULT 0) ENGINE = INNODB DEFAULT CHARSET=utf8;

randstring(5) # 用于模拟员工名称
delimiter $
CREATE FUNCTION randstring(n int) returns VARCHAR(255)
BEGIN
	DECLARE all_str VARCHAR(100) DEFAULT "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
	DECLARE return_str VARCHAR(255) DEFAULT "";
	DECLARE i int DEFAULT 0;
	WHILE i<n DO
		set return_str = CONCAT(all_str,SUBSTRING(all_str,FLOOR(1+rand()*52),1));
		set i=i+1;
	END WHILE;
	return return_str;
END $


# 产生随机整数
delimiter $
CREATE FUNCTION rand_num() RETURNS int(5)
BEGIN
	DECLARE i int DEFAULT 0;
	set i = FLOOR(rand()*100);
	return i;
END
$

# 通过存储过程插入海量数据：emp表中,从哪里开始插入多少数据
delimiter $
CREATE PROCEDURE insert_emp(in eid_start INT(10),in data_times int(10))
BEGIN
		DECLARE i int DEFAULT 0;
			set autocommit = 0; # 关闭自动提交
			repeat 
				set i=i+1;
				insert into emp VALUES(eid_start+i,randstring(5),"other",ran_num());
				UNTIL i = data_times
			end repeat;
			commit;
END $



# 通过存储过程插入海量数据：dept,从哪里开始插入多少数据
delimiter $
CREATE PROCEDURE insert_dept(in eid_start INT(10),in data_times int(10))
BEGIN
		DECLARE i int DEFAULT 0;
			set autocommit = 0; # 关闭自动提交
			repeat 
				set i=i+1;
				insert into dept VALUES(eid_start+i,randstring(6),randstring(8));
				UNTIL i = data_times
			end repeat;
			commit;
END $

delimiter ;
# 插入数据
call insert_emp(1000,800000);
call insert_dept(10,30);

分析海量数据：profiles

show profiles # 默认关闭

show variables like “%profiling%”

set profiling = on

show profiles : 会记录所有的profiling 打开之后的 全部sql查询语句所花费的时间，确定：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu io）

– 精确分析：sql诊断

show profile all for query 上一步查询的Query_id

show profile cup,blockio for query 2

– 全局查询日志：记录开启之后的全部sql语句（这种全局的记录操作，仅仅在调优，开发过程中打开即可，在最终的部署实施时要关闭）

show variables like “%general_log%”

set global general_log = 1 // 开启全局日志

set global log_output = “table” // 设置将全部的sql记录在表中

set global general_log_file=“/tmp/general.log” // 将所有sql记录在文件中

开启后，会记录所有sql：会被记录 mysql.general_log表中

select * from mysql.general_log

锁机制：解决因资源共享，而造成的并发问题

操作类型：

• 读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰
• 写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读锁和写锁

操作范围

• 表锁：一次性锁一张表整体加锁，如myISAM存储引擎使用表锁，开销小，加锁快，无死锁，但锁范围大，容易发生锁冲突，并发度低

• 行锁：一次性锁一行数据加锁，如innoDB存储引擎使用行锁，开销大，加锁慢，容易出现死锁，锁的范围较小，不易发生锁冲突，并发度高（很小概率发生并发问题：脏读，幻读，不可重复读，丢失更新）

  增加锁
  lock table 表1 read/write, 表2 read/write ...
  查看加锁的表
  show open tables;
  加读锁
  lock table tablelock read
  分析表锁定
  查看哪些表加了锁：show open tables: 1代表被加了锁
  分析表锁定的严重程度：
  show status like "table%"
  Table_locks_immediate:即可能获取到的锁数
  Table_locks_waited:需要等待的表锁数（如果该值越大，说明存在越大的锁竞争）
  一般建议：Table_locks_immediate/Table_locks_waited>5000，建议采用InnoDB引擎，否则MyISAM引擎
  
  mysql默认autocommit自动提交，Oracle需要手动commit
  为了研究行锁，暂时将自动commit关闭：
  set autocommit=0;
  以后需要通过commit进行手动提交
  
  写操作
  insert into linelock values("ab")
  写操作同样的数据
  update linelock set name="a"
  更新时发现此数据被加锁了。直到其他会话将该锁释放后，才能继续操作
  
  1.如果会话x对某条数据a进行DML操作，则其他会话必须等待会话x结束事务后，才能对数据a进行操作
  2.表锁通过unlock tables解锁，也可以通过事务解锁，行锁通过事务解锁
  
  行锁的注意事项：
  1.如果没有索引，则行锁转为表锁（如果索引类因为发生类型转换而造成索引失效（所有索引都失效的情况），则行锁会转换为表锁）
  2.行锁的一种特殊情况：间隙锁，值在范围内，但是不存在
  此时linelock表中，没有id=7的数据
  update linelock set name="x" where id>1 and id<9 即在此where范围中，没有id=7的数据，则id=7的数据成为间隙
  间隙：mysql会自动给间隙加锁(会自动给id=7的数据加间隙锁（行锁）)
  行锁如果有where，则实际加锁的范围就是where后面的范围（不是实际值）
  

• 页锁

行锁：InnoDB默认使用行锁

缺点：比表锁性能损耗大

优点：并发能力强，效率高

因此建议，高并发用innoDB，否则用myISAM

行锁分析：

show status like “%innodb_row_lock%”

查找数据如何加锁

for update