0.查询优化
1.慢查询的开启并捕获
2.explain+慢sql分析
3.show profile查询SQL在mysql服务器中的执行细节和生命周期情况
4.sql数据库服务器的参数调优
查询优化
永远小表驱动大表
即小数据集驱动大数据集
通过小表驱动大表 减少连接次数
select * from A where id in(select id from B)
in是先执行括号里的语句 先执行子表（小表）再根据小表查询主表（大表）
此时B表如果数据集大于A表 那么用in比较合适
等价于：
for select id from B
for select * from A where A.id=B.id
当B表的数据集必须小于A表的数据集时，用in优于exists
select * from A where exists (select 1 from B where B.id=A.id)
等价于：
for select * from A
for select * from B where B.id =A.id
此时先查询外层（A表） 根据外层的结果在括号的子查询做验证（只返回true和false 不返回实际数据）
如果A表数据比较少 那用exist合适
当A表的数据集小于B表的数据集时，用exists优于in
order by关键字优化
order by尽量使用index方式排序 避免使用FileSort方法排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
create table tblA(
age int,
birth TIMESTAMP not null
);
insert into tblA(age,birth) VALUES(22,now());
insert into tblA(age,birth) VALUES(23,now());
insert into tblA(age,birth) VALUES(24,now());
create index idx_A_ageBirth on tblA(age,birth);
建表添加索引
第一句无法命中索引 走的filesort
第二句无filesort
mysql索引的排序方式是desc 当age使用asc排序时 birth就无法按照索引中age的顺序来查找 只能filesort排序然后在查询
总结：
MYSQL支持两种排序方式 FileSort和Index Index效率高 它指MYSQL扫描索引本身完成排序 FIleSort效率低
ORDER BY满足两种情况 会使用INDEX排序：
ORDER BY语句使用索引最左前列
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列
filesort排序有两种算法：单路排序和双路排序
双路排序：MYSQL4.1前使用双路排序 意思就是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表读数据
单路排序：从磁盘读取查询需要的所有列，按照orderby列在buffer对他们排序，然后扫描排序后的列表进行输出，与双路排序相比他减少了一次IO且将随机IO变为了顺序IO
但是单路有一定的问题：如果取出的数据大小比sort_buffer大 就需要进行多次读取 增多IO次数，可以通过sort_buffer_size和max_length_for_sort_data 两个参数调大来解决
GROUP BY关键字优化
group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data 参数的设置+增大sort_buffer_size参数的设置
where高于having 能写在where限定的条件就不要去having限定
慢查询日志
MYSQL的慢查询日志是MYSQL提供的一种日志记录，它用来记录在MYSQL中响应时间超过阈值的语句，具体值运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，long_query_time默认值为10，意思是运行时间10s以上的语句
慢查询日志默认是不开启的
查看是否开启：SHOW VARIABLES LIKE’%slow_query_log%';
开启：set global slow_query_log = 1; 只对当前数据库生效 重启MYSQL后失效
想要永久生效需要修改my.cnf文件
mysqldumpslow
批量数据脚本
建表
CREATE TABLE dept(
id int unsigned primary key auto_increment,
deptno MEDIUMINT UNSIGNED not null DEFAULT 0,
dname VARCHAR(20) not null DEFAULT "",
loc varchar(13) not null DEFAULT ""
)engine=INNODB DEFAULT charset=GBK;
CREATE table emp(
id int UNSIGNED PRIMARY key auto_increment,
empno MEDIUMINT UNSIGNED not null DEFAULT 0,
ename VARCHAR(20) not null DEFAULT "",
job VARCHAR(20) not null DEFAULT "",
mgr MEDIUMINT UNSIGNED not null DEFAULT 0,
hiredate DATE not null,
sal DECIMAL(7,2) not null,
comm DECIMAL(7,2) not null,
deptno MEDIUMINT UNSIGNED not null DEFAULT 0
)ENGINE=INNODB DEFAULT CHARSET=GBK;
直接执行可能会报错
需要先设置参数
show VARIABLES like 'log_bin_trust_function_creators'
set global log_bin_trust_function_creators=1;
方法和存储
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i< n DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_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(100+RAND()*10);
return i;
END $$
DELIMITER $$
CREATE PROCEDURE insert_emp(in start int(10),in max_num int(10))
BEGIN
DECLARE i int DEFAULT 0;
set autocommit = 0;
repeat
set i=i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i =max_num
end REPEAT;
COMMIT;
END $$
DELIMITER $$
CREATE PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
DECLARE i int DEFAULT 0;
set autocommit =0;
repeat
set i=i+1;
insert into dept(deptno,dname,loc) VALUES ((START+i),rand_string(10),rand_string(8));
UNTIL i = max_num
end REPEAT;
COMMIT;
END $$
执行
DELIMITER ;
call insert_dept(100,10);
select * from dept;
delimiter ;
call insert_emp(100001,500000);
select * from emp
Show Profile
是mysql提供可以用来分析当前绘画中语句执行的资源消耗情况，可以用于SQL的调优的测量
默认情况下，参数处于关闭状态 并保存最近15次运行结果
查询是否开启：show variables like ‘profiling’
开启： set profiling=on
查看日志：show profiles;
查询4号的cpu，block io
converting HEAP to MyISAM 查询结果 太大 内存不够用 往磁盘上搬
Creating tmp table 创建临时表
Copying to tmp table on disk把内存中临时表复制到磁盘 危险
locked
全局查询日志
不要在生产环境开启
启动：set global general_log=1;
输出：set global log_output=‘TABLE’;
此后 编写的sql语句会记录到mysql库里的general_log表 用一下命令查看
select * from mysql.general_log