参考：尚硅谷 MySQL高级 周阳
1.索引分析
1.1 单表优化
1.1.1 案例
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` VARBINARY(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');
执行SQL查询语句
select id, author_id from article
where category_id=1 and comments>1
order by views desc limit 1;
使用explain查看SQL性能
explain select id, author_id from article
where category_id=1 and comments>1
order by views desc limit 1;
因为where子句中使用到的字段没有加索引 所以type是ALL全表扫描
1.1.2 优化
先给字段加索引 使用到了三个字段 分别是category_id、comments、views
先尝试添加联合索引 并查看索引
create index idx_article_cv on article(category_id, comments, views);
show index from article;
在执行explain语句 查看SQL语句的性能
explain
select id, author_id from article
where category_id=1 and comments>1
order by views desc limit 1;
type是range 这是因为where子句中有>范围搜索 这是可以接受的
但重要的是Extra 中有Using filesort 这个问题有些严重
我们建立的索引是联合索引(category_id, comments, views), 在comments>1范围搜索之后的views索引会失效 order by 子句不会使用索引排序 而是使用了文件排序。
解决方法是建立（category_id, views）的联合索引
先将之前建立的索引drop一下 在建立新的联合索引
drop index idx_article_cv on article;
create index idx_article_cv on article(category_id, views);
再次查看SQL性能
explain
select id, author_id from article
where category_id=1 and comments>1
order by views desc limit 1;
Using filesort变成了Using where
1.2 两表优化
1.2.1 案例
建表语句
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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)));
执行两表联查
select * from class left join book on class.card=book.card;
查看SQL性能
explain select * from class
left join book on class.card=book.card;
因为两个表的card字段都没有建立索引， 所以不出预料type都是ALL
1.2.2 优化
先给左表card加索引
alter table class add index Y(card);
show index from class;
查看SQL性能
尝试给右表card加索引
先drop左表的索引
drop index Y on class;
给右表加索引
alter table book add index Y(card);
show index from book;
查看SQL性能
因为是左连接 left join，左表一定是全表扫描的 从右表搜索
所以左连接给右表字段加索引
下面是两表都加索引的性能结果：
虽然class的type是index 但是rows是20 依然是扫描全表
结论：左连接对右表加索引 右连接对左表加索引
1.3 三表优化
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;
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)));
三表联查使用的是class、book 和 phone三张表
首先要将1.2中的class、book除了主键的其它索引全部drop掉
show index from class;
drop index YL on class;
show index from book;
drop index Y on book;
查看三表联查的性能
explain select * from class
left join book on class.card=book.card
left join phone on book.card=phone.card;
基于1.2的结论，索引应该建立在book 和 phone表上
alter table book add index Y(card);
alter table phone add index Z(card);
class表作为最外层表一定是全部扫描的 rows一定是20
其它两张表的rows被优化到了1行数据；表示通过索引查找到记录
不需要读取行数据遍历， 优化了查询效率。
结论：索引需要建立在需要经常查询的字段上。
1.4 结论
- 尽可能减少Join语句中的NestedLoop的循环次数：永远用小的结果集驱动大的结果集；
比如 在实际业务上class(书的类别)表的数据一定是小于book的数据数；使用class作为主表来驱动book表效率会比使用book作为主表驱动class表高得多。 - 优先优化NestedLoop语句的内层循环；
要先保证最里面的执行效率才能优化外层语句。 - 保证join语句中被驱动表上的join条件字段已经被索引
- 在无法保证被驱动表的join字段被索引且内存资源充足的前提下，不要太吝 啬joinBuffer的设置。
2.索引失效(应该避免)
建表语句
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
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());
CREATE TABLE tbl_user(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into tbl_user(NAME,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(NAME,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(NAME,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(NAME,age,email) values('4aa4',21,'d@163.com');
create table test03(
id int primary key not null 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');
2.1 对于联合索引全值匹配查询效率最高
案例：
对staffs表建立联合索引 并执行而小盘explain查看查询效率
alter table staffs add index idx_staffs_nameAgePos(name, age, pos);
explain select name, age, pos from staffs where name='July' and age=23 and pos='dev';
这句查询语句使用到了(name, age, pos) 三个字段的索引 key_len=140
explain select name, age, pos from staffs where name='July' and age=23;
这句查询只用到了name age两个字段的索引 所以key_len=78
2.2 最佳左前缀法则
使用联合索引查询时不能跳过中间索引，否则之后的索引会失效
explain select * from staffs where age=23 and pos='dev';
跳过name索引， 导致age和pos字段索引失效
explain select * from staffs where name='July' and pos='dev';
跳过了age字段， 联合索引的pos字段失效。因为key_len时74 只用到了name字段的索引。
2.3. 在索引列上做操作
在索引列上做操作(计算、函数、自动或手动的类型转换)，会导致索引失效而转向全表扫描
对比以下两条语句的查询效率
explain select * from staffs where name='July';
explain select * from staffs where left(name, 4)='July';
对那么字段使用left函数 索引失效 转变为了全表扫描。
2.4 范围查找之后的索引会全部失效
explain select * from staffs where name='July' and age>20 and pos='dev';
key_len为78，只用到了name 和 age索引 age之后的pos索引失效
2.5 尽量使用覆盖索引 尽量不使用select * 查询
explain select * from staffs where name='July' and age=23 and pos='dev';
explain select name, age, pos from staffs where name='July' and age=23 and pos='dev';
using index 表示使用了覆盖索引，Using where 是因为索引字段不是唯一的需要使用where 过滤。
2.6 使用不等于(!= <>)索引失效
MySQL使用不等于(!= <>)查询时索引失效会导致全表或全索引扫描
explain select * from staffs where name<>'July';
explain select name, age, pos from staffs where name<>'July';
2.7 is null 和 is not null无法使用索引
explain select * from staffs where name is null;
2.8 like通配符开头的mysql索引失效会变成全表扫描
%开头的索引会失效 如 %July
%放在后面 July% 不会引起索引失效 是range查询 因为索引底层是根据字母顺序来排序的
explain select * from staffs where name like 'Jul%';
range 表示使用到了索引
explain select * from staffs where name like '%Jul%';
ALL表示遍历整个表 索引失效
explain select name, age, pos from staffs where name like 'Jul%';
type是index表示遍历整个索引树但是不遍历行数据 一般使用覆盖索引的场景出现
explain select name, age, pos from staffs where name like '%Jul%';
因为使用了覆盖索引，就算%放在前面type也是index 遍历索引 不遍历整个表。
面试题：如何有优化需要将%放在模糊查询字符之前的SQL？
答：使用覆盖索引 可以将type优化到index(注意覆盖索引是一种优化方式不是一个索引类型，覆盖索引通过建立联合索引实现)
2.9 自动类型转换会使索引失效
explain select * from staffs where name=2000;
本应该是ref 非唯一性的索引扫描 因为自动的类型转换导致索引失效。
2.10 使用or连接会导致索引失效
explain select * from staffs where name='z3' or name='July';
扫一扫
6167
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
