索引分析:
单表:
sql:
create table if not exists `article`(
id int(10) unsigned not null primary key auto_increment,
author_id int(10) unsigned not null,
category_id int(10) unsigned not null,
views int(10) unsigned not null,
comments int(10) unsigned not null,
title varchar(255) not null,
content text not null
);
insert into article(author_id, category_id, views, comments, title, content) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
案例:
1. category_id为1且comments大于1的情况下,views最多的article_id
EXPLAIN SELECT id, author_id from article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views);
#CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
#DROP INDEX idx_article_ccv ON article;
#CREATE INDEX idx_article_cv ON article(category_id,views);
两表:
sql:
create table if not exists `class`(
id int not null auto_increment,
card int not null,
primary key(id)
);
create table if not exists book(
bookid int not null auto_increment,
card int not null,
primary key(bookid)
);
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into class(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
insert into book(card)values(floor(1+(rand()*20)));
explain select * from class left join book on class.card = book.card;
alter table book add index Y(card);
drop index Y on book;
alter table class add index Y(card);
三表:
sql:
create table if not exists phone(
phoneid int not null auto_increment,
card int not null,
primary key(phoneid)
);
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
insert into phone(card) values(floor(1+(rand()*20)));
explain select * from class inner join book on class.card = book.card inner join phone on book.card = phone.card;
explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
alter table phone add index z(card);
alter table book add index x(card);
结论:
尽可能减少join语句中的nestedloop的循环总次数:"永远用小结果集驱动大结果集"
优先优化嵌套循环的内层循环
保证join语句中被驱动表上join条件字段已经被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的条件下, 不要吝啬joinbuffer的设置
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '',
age int not null default 0,
pos varchar(20) not null default '',
add_time timestamp default current_timestamp
);
insert into staffs(name, age, pos, add_time)values('z3',22,'manager',now());
insert into staffs(name, age, pos, add_time)values('july',23,'dev',now());
insert into staffs(name, age, pos, add_time)values('2000',23,'dev',now());
alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
索引失效原因:
1. 全值匹配
explain select * from staffs where name = 'july';
explain select * from staffs where name = 'july' and age = 25;
explain select * from staffs where name = 'july' and age = 25 and pos = 'dev';
2. 最佳左前缀法则: 如果索引了多列, 要遵守最左前缀法则, 指的是查询从索引的最左前缀开始并且不跳过索引中的列.
where name = 'july' and pos = 'dev';
3. 不在索引列上左任何操作(计算, 函数, 类型转换), 会导致索引失效而转向全表扫描
explain select * from staffs where left(name,3) = 'july';
4. 存储引擎不能使用索引中范围条件右边的列
explain select * from staffs where name = 'july' and age > 23 and pos = 'dev';
5. 尽量使用覆盖索引, 减少select *
explain select * from staffs where name = 'july' and age = 23 and pos = 'dev';
6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7. is null和is not null也无法使用索引
8. like以通配符开头('%abc')mysql索引会失效变成全表扫描
explain select * staffs where name like '%july%';
explain select * staffs where name like '%july';
explain select * staffs where name like 'july%';
create table tbl_user(
id int auto_increment,
name varchar(20) default null,
age int default null,
email varchar(20) default null,
primary key(id)
);
insert into tbl_user(name, age, email) values('1aa1',21,'a@qq.com');
insert into tbl_user(name, age, email) values('2aa2',221,'b@qq.com');
insert into tbl_user(name, age, email) values('3aa3',231,'c@qq.com');
insert into tbl_user(name, age, email) values('4aa4',251,'d@qq.com');
insert into tbl_user(name, age, email) values('aa',251,'e@qq.com');
create index idx_user_nameAge on tbl_user(name, age);
explain select name, age from tbl_user where name like '%aa%';
explain select id from tbl_user where name like '%aa%';
explain select name from tbl_user where name like '%aa%';
explain select age from tbl_user where name like '%aa%';
explain select id, name from tbl_user where name like '%aa%';
explain select id, name, age from tbl_user where name like '%aa%';
explain select name, age from tbl_user where name like '%aa%';
explain select * from tbl_user where name like '%aa%';
explain select id, name, age, email from tbl_user where name like '%aa%';
利用覆盖索引解决like的%问题
9. 字符串不加单引号索引失效
select * from staffs where name = '2000';
select * from staffs where name = 2000;
10. 少用or, 用它来连接时索引会失效
explain select * from staffs where name = 'july' or name = 'z3';
练习: 假设index(a,b,c)
where a = 3 使用到a
where a = 3 and b = 5 使用到a,b
where a = 3 and b = 5 and c = 4 使用到a,b,c
where b = 3 或者 b = 3 and c = 4 或者 where c = 4 不能
where a = 3 and c = 5 用到a
where a = 3 and b > 4 and c = 5 使用到a,b
where a = 3 and b like 'kk%' and c = 4 使用到a,b,c
where a = 3 and b like '%kk' and c = 4 使用到a
where a = 3 and b like '%kk%' and c = 4 使用到a
where a = 3 and b like 'k%kk%' and c = 4 使用到a,b,c
create table test03(
id int primary key auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
select * from test03;
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
explain select * from test03 where c1 = 'a1';
explain select * from test03 where c1 = 'a1' and c2 = 'a2';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
1)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' and c3 = 'a3';查询优化器(都是常量)
2)explain select * from test03 where c4 = 'a4' and c3 = 'a3' and c2 = 'a2' and c1 = 'a1';
3)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';用了 c1,c2,c3
4)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';用了 c1,c2,c3,c4(查询优化器)
5)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3; c3作用是排序
6)explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c3;
7)explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c4; 出现了filesort
8.1)explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c2,c3; 查找用c1, 排序用c2,c3
8.2)explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c3,c2; 查找用c1, 排序没有用到
9)explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c2,c3; 查找用c1, c2, 排序用c2,c3
10)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2,c3; 查找用c1, c2, 排序用c2,c3
11)explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'c5' order by c3,c2;
12)explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c2,c3;
13)explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c3,c2;分组之前必排序, 会产生临时表
建议: 对于单键索引, 尽量选择针对当前query过滤性更好的条件在组合索引时, 当前query过滤性最好的字段在索引顺序中越靠左越好在组合索引时,
尽量选择可以能够包含当前query中where中更多的字段尽可能通过分析统计信息调整sql达到选择合适索引的目的
分析
1. 观察, 至少跑1天, 检查生产的慢sql情况
2. 开启慢查询日志, 设置阙值, 比如超过5秒的就是慢sql, 并将它抓取
3. explain+慢sql分析
4. show profile
5. 运维经理或dba进行sql数据库服务器的参数调优
查询截取分析:
1. 慢查询的开启并捕获
2. explain + 慢sql分析
3. show profile查询sql在mysql服务器里面的执行细节和生命周期
4. sql数据库服务器的参数调优
for(int i = 0; i < 5; i++){
for(int j = 0; j < 1000; j++){
}
}
for(int i = 0; i < 1000; i++){
for(int j = 0; j < 5; j++){
}
}
* 小表驱动大表
select * from A where id in (select id from B)
等价于:
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表的数据集小于B表的数据集时, 用exists优于in
A表与B表的id字段应建立索引
exists:
select ... from table where exists(subquery)
理解为: 将主查询的数据, 放到子查询中做条件验证, 根据验证结果(true或false)来决定主查询的数据结果是否得以保留
order by关键字优化:
create table tblA(
id int primary key auto_increment,
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);
explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by age,birth;
explain select * from tblA where age > 20 order by birth;
explain select * from tblA where age > 20 order by birth,age;
explain select * from tblA order by birth;
explain select * from tblA where birth > '2017-01-01 00:00:00' order by birth;
explain select * from tblA where birth > '2017-01-01 00:00:00' order by age;
explain select * from tblA order by age asc, birth desc; order by 默认是升序
index效率高, filesort效率低
order by满足两种情况, 会使用index排序:
1. order by语句使用索引最左前列
2. 使用where子句与order by子句条件组合满足索引最前列
如果不在索引列上, filesort有两种算法:
1. 双路排序: mysql4.1之前是使用双路排序, 字面意思是两次扫描磁盘, 最终得到数据
读取行指针和order by列, 对他们进行排序, 然后扫描已经排序好的列表, 按照列表中的值重新
从列表中的值从新从列表中读取对应的数据进行输出
2. 单路排序:
* 为排序使用索引:
mysql两种排序方式: 文件排序和扫描有序索引排序
mysql能为排序与查询使用相同索引
key a_b_c(a,b,c);
order by a;
order by a, b;
order by a, b, c;
order by a desc, b desc, c desc;
where a = const order by b, c;
where a = const and b = const order by c;
where a = const and b > const order by b, c;
不能使用:
order by a asc, b desc, c desc 排序不一致
where a = const order by c 丢失b索引
where a = const order by a, d; d不是索引的一部分
where a in (...) order by b, c; 对于排序来说, 多个相等条件也是范围查询
* group by:实质是先排序后进行分组, 遵照索引建的最佳左前缀
当无法使用索引列, 增大max_length_for_sort_data, sort_buffer_size
其他均和order by一样
* 慢查询日志:
默认情况下没有开启慢查询日志
show variables like '%slow_query_log%';
set global slow_query_log = 1; 只对本次有效
show variables like 'long_query_time%';
show global variables like 'long_query_time%';
set global long_query_time = 3;
select sleep(4);
show global status like '%slow_queries%';
mysqldumpslow --help
# 批量插入数据
create table dept(
id int primary key auto_increment,
deptno int not null default 0,
dname varchar(20) not null default '',
ioc varchar(20) not null default ''
);
create table emp(
id int primary key auto_increment,
empno int not null default 0,
ename varchar(20) not null default '',
job varchar(20) not null default '',
mgr int not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno int not null default 0
);
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 $$
# drop function rand_num;
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, ioc) values((start + i), rand_string(10), rand_string(8));
until i = max_num
end repeat;
commit;
end $$
delimiter ;
call insert_dept(100, 10);
call insert_emp(100001, 500000);