sql优化之优化案例

优化案例

单表优化、两表优化、三表优化

(1)单表优化

准备表
create table book(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not nu11,
publicid int(4)not null,
typeid int(4) not null);
insert into book values(1,’ tjava’，1, 1,2) ;
insert into book values(2, ’ tc’, 2, 1,2)
insert into book values (3,'wx’,3,2,1) ;
insert into book values (4,'math’,4,2, 3)
commit;

查询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实际解析的顺序(from,where,select)，调整索引的顺序:

索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。
alter table book add index idx_ _bta (typeid, authorid, bid); --虽然可以回表查询bid,但是将bid放到索引中可以提升使用using index

再次优化(之前是index级别) :思路。因为范围查询in有时会导致索引失效，因此交换索引的顺序，将typeid in(2, 3)放到最后。
drop index idx_tab on book;
alter table book add index idx_atb (authorid, typeid, bid) ;
explain select bid from book where authorid=1 and typeid in(2, 3) order by typeid desc;

小结:

• 保持索引的定义和使用的顺序一致性
• 索引需要逐步优化
• 将含In的范围查询放到where条件的最后，防止失效。

(2)两表优化

SQL语句 左连接:
select * from teacher2 t left outer join course2 c on t. cid=c. cid where c. cname=’ java’
优化
索引往哪张表加?

• 小表驱动大表 .where 小表.x=大表.y
• 索引建立经常使用(查询)的字段上,一般情况对于左外连接，给左表加索引;右外连接，给右表加索引

原理:对于双层循环来说:一般建议将数据小的循环放外层;数据大的循环放内存。外层越小程序执行效率越高

避免索引失效的一些原则

(1)复合索引
a.复合索引，不要跨列或无序使用( 最佳左前缀);对于复合索引，如果左边失效，右侧全部失效。
b.复合索引，尽量使用全索引匹配

(2)不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效
select … where A.x =… ; --假设A. x是索引
不要: select … where A.x*3 =…;

(3)复合索引不能使用不等于(!= < >)或is null (is not null)， 否则自身以及右侧索引全部失效
explain select * from book where authorid = 1 and typeid =2 ;
-----SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。我们学习索引优化，是一个大部分情况适用的结论，由于SQL优化器等原因该结 论不是100%正确。一般而言，范围查询(> < in)，之后的索引失效。

(4)补救。尽量使用索引覆盖(using index)
假设(a,b, c)是一个复合索引
select a,b, c from xx. . where a=xx and b=…;

(5) like尽量以 “常量”开头，不要以’%‘开头，否则索引失效
explain select * from teacher where tname like ’ %x%’; ---- tname索引失效
explain select * from teacher where tname like ’x%’;---- tname索引有效
explain select tname from teacher where tname like ’ %x%’ ; ----如果必须使用like ‘%x%’ 进行模糊查询，可以使用索引覆盖挽救一 部分。

(6)尽量不要使用类型转换(显示、隐式)，否则索引失效
tname 是一个索引和varchar
explain select * from teacher where tname =‘abc’;----索引有效
explain select * from teacher where tname = 123 ;----程序底层将123 ->’123’，即进行了类型转换,索引失效

(7)尽量不要使用or，否则索引失效
explain select * from teacher where tname =’ ’ or tcid >1 ; ----将or左侧的tname 失效。

一些其他的优化方法

(1)exist和in
select . .from table where exist (子查询) ;
select … from table where 字段 in (子查询) ;
如果主查询的数据集大，则使用In
如果子查询的数据集大，则使用exist
exist语法:将 主查询的结果，放到子查需结果中进行条件校验(看子查询是否有数据，如果有数据就校验成功,无数据就返回空
select tname from teacher where exists (select * from teacher) ;
–等价于select tname from teacher

(2) order by优化
using filesort 有两种算法:双路排序、单路排序( 根据I0的次数)
MySQL4. 1之前默认使用双路排序;
双路:扫描2次磁盘(1:从磁盘读取排序字段,对排序字段进行排序(在buffer中进行的排序) 2:扫描其他字段)
MySQL4.1之后默认使用单路排序:只读取一 次(全部字段)，在buffer中进行排序。
注意:单路排序比双路排序会占用更多的buffer。单路排序在使用时，如果数据大，可以考虑调大bufferd的容量大小: set max_ length_ for_ sort_ data = 1024 单位 字节

如果max_ length_ for_ sort_ _data值 太低，则mysql 会自动从单路->双路
提高order. by 查询的策略:
a.选择使用单路、双路;调整buffer的容量 大小;
b.避免select * …
c.复合索引不要跨列使用，避免using filesort
d.保证全部的排序字段排序的一-致性(都是升序或降序)

SQL排查-慢查询日志

慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句(long_ query_ time， 默认10秒)

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

检查是否开启了慢查询日志:
show variables like’ %s1ow_ query_ _1og%’ ;

临时开启:
set global slow_ query_ log于1 ; – 在内存种开启
永久开启:
/etc/my. cnf中追加配置:
[mysqld]
slow_ query_ log=1
slow_ query_ 1og_ _file=/var/lib/ mysql/ localhost-slow. log

慢查询阀值:
show variables like ‘%long_ query_t ime%’
临时设置阀值:.
set global long_ query_ time = 5 ; --设置完毕后，重新登陆后起效 (不需要重启服务)
永久设置阀值:
/etc/my. cnf中追加配置:
vi /etc/my. cnf
[ mysqld ]
long_ query_ time=3

----查询超过阀值的SQL数量: show global status like ’%slow_ queries%’;
(1)慢查询的sql被记录在了日志中，因此可以通过日志查看具体的慢SQL。
cat /var/ lib/ mysq1/ localhost-slow. log----这个命令不是在sql数据库状态执行,在linux下执行,下面也是
(2)通过mysq1dumpslow工具查看慢SQL,可以通过一些过滤条件快速查找出需要定位的慢SQL
mysqldumpslow - -help
s:排序方式
r:逆序
1:锁定时间
g:正则匹配模式
–获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/1ib/mysq1/localhost-slow. log
–获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysq1/ localhost-slow. log
–按照时间排序，前10条包含left join 查询语句的SQL
mysqldumpslow -s t -t 10 -g “left join” /var/lib/ mysq1/localhost-slow. log

分析海量数据

建表
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;

delimiter $ :设置以$结尾,防止;被误判
FLOOR():向下取整

通过存储函数插入海量数据:
创建存储函数:
randstring(6) ->aXiayx 用于 模拟员工名称
delimiter $
create function randstring(n int) returns varchar (255)
begin
declare all_ str varchar(100) default ’ abcdefghijklmnopars tuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
declare return_ str varchar (255) default’’;
declare i int default 0
while i<n
do
set return str = concat (return_str,substring(all_str,FLOOR(1+rand()*52),1);
set i=i+1 ;
end whi le ;
return return str ;
end $

如果报错This function has none of DETERMINISTIC，NO SQL，or READS SQL DATA in its declaration
是因为存储过程/存储函数在创建时与之前的慢查询日志冲突了
解决冲突:
临时解决(开启log_ bin_ trust_ function_ creators )
show variables like ‘%log_ bin_ trust_ function_ creators%’ ;

----产生随机整数
create function ran_ num() returns int(5)
begin declare i int default 0;
set i =floor( rand()*100 ) ;
return i;
end $

–通过存储过程插入海量数据: emp表 中
create procedure insert_emp( in eid_ start int(10), in data_ times int (10))
begin
declare i int default 0;
set autocommit = 0 ;
repeat
insert into emp values(eid_ start + i，randstring(5) , 'other’, ran_ num())
set i=i+1
until i=data_ times
end repeat ;
commit ;
end $

–通过存储过程插入海量数据: dept表中
create procedure insert_ dept(in dno_ start int(10) , in data_ times int(10))
begin
declare i int default 0;
set autocommit = 0 ;
repeat
insert into dept values (dno_ start+i , randstring (6), randstring(8)) ;
set i=i+1 ;
until i=data t imes
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查询语句所花费的时间。缺点:不够精确

----精确分析:sql诊断
show profile all for query上一步查询的的Query_ Id
show profile cpu, blockio for query. 上一 步查询的的Query_ _Id

全局查询日志: 记录开启之后的全部SQL语句。(这次全局的记录操作 仅仅在调优、开发过程中使用)
show variables like’ %general_ log%’ ;
----执行的所有SQL记录在表中
set global general_ 1og = 1 ;----开启 全局日志
set global log_output=’ table’’; --设置将全部的SQL记录在表中
----执行的所有SQL记录在文件中
set global log_ output=’ file’
set global general_ log = on
set global general log_ file=’ /tmp/ general. log’
开启后，会记录所有SQL会被记录mysql. general_ log表中。
查询sql记录:select * from mysql. general_ .log ;