Mysql版本 5.5
1. 索引单表优化
1.1 建表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` 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');
SELECT * FROM article;
1.2 案例
- #查询 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;
#结论:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
优化1:
建索引:
ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
或者
create index idx_article_ccv on article(category_id,comments,views);
都可以!
再次exolain:
是因为:范围查询、order by和group by会中断联合索引。
本次优化结论:
#type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
#但是我们已经建立了索引,为啥没用呢?
#这是因为按照 BTree 索引的工作原理,
# 先排序 category_id,
# 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
#当 comments 字段在联合索引里处于中间位置时,
#因comments > 1 条件是一个范围值(所谓 range),
#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
#优化1效果不理想!
优化2:
(先删除之前索引)
DROP INDEX idx_article_ccv ON article;
建索引:
ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
或者
create index idx_article_cv on article(category_id,views);
再次explain:
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
结果:
#结论:可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
#优化2 ok!
2.索引双表优化
2.1 建表SQL
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED 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)));
2.2 案例一: LEFT JOIN
分析左连接:
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
可以发现type中有ALL
优化1:
在主表加索引。
ALTER TABLE class ADD INDEX X (card);
再explain:
发现行数没有变化,优化1没啥太大用。
优化2:
在子表建索引
DROP INDEX X ON class; #先删除优化1索引
ALTER TABLE `book` ADD INDEX Y ( `card`); # 新建子表索引
再explain:
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
结论:
1、保证被驱动表的join字段已经被索引
被驱动表 join 后的表为驱动表 (需要被查询)
2、left join 时,选择小表作为驱动表,大表作为被驱动表。
但是 left join 时一定是左边是驱动表,右边是被驱动表
2.3案例二:RIGHT JOIN
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
换成子表索引:
可以看到RIRHT JION中索引还是放在左表中有用!
总结:
RIRHT JION索引建在左表(子表)
2.4 其他
inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
mysql 自动选择。小表作为驱动表。因为 驱动表无论如何都会被全表扫描?。所以扫描次数越少越好
3. 索引三表优化
3.1 建表sql
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`phoneid`)
)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)));
3.2 案例
按照双表中的原则建索引:
可以看到type都是ref且rows优化很好!因此索引最好设置在需要经常查询的字段中。
4. Join语句优化
- 尽可能减少Join语句中的NestedLoop(嵌套循环)的循环总次数;“永远用小结果集驱动大的结果集”
- 优先优化NestedLoop(嵌套循环)的内层循环
- 保证Join语句中被驱动表上Join条件字段已经被索引
- 当无法保证被驱动表的Join字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置
5.其他优化
5.1 建表SQL
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());
SELECT * FROM staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos); # 新建复合索引
show index from staffs
5.2 全值匹配很OK
三次查询 精度 依次增加
没用索引。涉及字段Age、Pos
没用索引 涉及字段Pos
用索引 涉及字段name
总结:
最佳左前缀法则:如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
口诀:火车头不能无,本例中索引idx_staffs_nameAgePos中的name(索引的最左字段)不能没有
本次查询中 全值匹配的部分使用。 违背了:最佳左前缀法则的中间兄弟不能断!
5.3不要在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
不要在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
5.4 范围之后全失效
存储引擎不能使用索引中范围条件右边的列
5.5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
5.6 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
5.7 is not null 也无法使用索引,但是is null是可以使用索引的
5.8 like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
建表sql:
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');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');
like ‘%abc%’ type 类型会变成 all
like ‘abc%’ type 类型为 range ,算是范围,可以使用索引
总结:% like加右边
解决like '%字符串%'时索引不被使用的方法:使用覆盖索引
5.9 字符串不加单引号索引失效
Varchar类型不加单引号,底层进行转换使索引失效,具体是使用了函数造成索引失效。
5.10 少用or,用它来连接时会索引失效
5.11 小总结题目
假设index(a,b,c)
Where****语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 | Y,使用到a,b |
where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
where a = 3 and c = 5 | 使用到a, 但是c不可以,b中间断了 |
where a = 3 and b > 4 and c = 5 | 使用到a和b, c不能用在范围之后,b后断了 |
where a = 3 and b like ‘kk%’ and c = 4 | Y,使用到a,b,c |
where a = 3 and b like ‘%kk’ and c = 4 | Y,只用到a |
where a = 3 and b like ‘%kk%’ and c = 4 | Y,只用到a |
where a = 3 and b like ‘k%kk%’ and c = 4 | Y,使用到a,b,c |
第七个和最后一个讲解:
https://www.bilibili.com/video/BV12b411K7Zu?p=223
6.面试题
6.1 建表SQL
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');
6.2 案例
建索引:
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;
问题:我们创建了复合索引idx_test03_c1234 ,根据以下SQL分析下索引使用情况?
-
explain select * from test03 where c1=‘a1’;
答:使用了一个c1索引。
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’;
答:使用了二个c1、c2索引。
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3=‘a3’;
答:使用了三个c1、c2、c3索引。
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3=‘a3’ and c4=‘a4’;
答:使用了四个c1、c2、c3、c4索引。
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ and c3=‘a3’;
答:使用了四个c1、c2、c3、c4索引。(c4和c3顺序颠倒,mysql内部sql优化器会自动识别排序)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3>‘a3’ and c4=‘a4’;
答:使用了三个c1、c2、c3索引。(c3范围匹配之后中断c4索引的使用)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4>‘a4’ and c3=‘a3’;
答:使用了四个c1、c2、c3、c4索引。(c4和c3顺序颠倒,mysql内部sql优化器会自动识别排序,c4为范围匹配但还是会用到索引)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ order by c3;
答:使用了二个c1、c2索引。(c3作用在排序而不是查找)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c3;
答:使用了二个c1、c2索引。(c3作用在排序而不是查找,也跟c4没啥关系)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c4;
答:使用了二个c1、c2索引。(出现了filesort,因为c3断开了)
-
explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c2,c3;
答: 只用c1一个字段索引,但是c2、c3用于排序,无filesort
-
explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c3,c2;
答:出现了filesort,我们建的索引是c1 c2 c3 c4,它没有按照顺序来,c3 c2 颠倒了
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c2,c3;
答:使用了二个c1、c2索引。(查找和排序都是按索引)
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c2,c3;
答:用c1、c2两个字段索引,但是c2、c3用于排序,无filesort
-
explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c3,c2;(与12题对比)
答:使用了二个c1、c2索引。(因为之前c2已经是一个常量,不影响后续排序,所以查找和排序都是按索引)
-
explain select * from test03 where c1=‘a1’ and c4=‘a4’ group by c2,c3;
答:只用c1一个索引。
-
explain select * from test03 where c1=‘a1’ and c4=‘a4’ group by c3,c2;
答:只用一个索引,会出现Using temporary; Using filesort 。(因为group by分组之前必排序,与order by排序和索引优化原则基本相同。此题中 group by c3,c2不是索引顺序,group by自己排,会有临时表产生)
6.3 一般性建议
-
对于单键索引,尽量选择针对当前query过滤性更好的索引
-
在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)
-
在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
-
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的