mysql 高级(基础四 索引优化 join 案例)
索引分析:单表
建表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');
查询category_id 为1 且 comments 大于1 的情况下,views 最多的article_id
select id,author_id from article where category_id =1 and comments>1 order by views desc limit 1;
explain 分析
显然,type是all ,即最坏的情况,Extra 里还出现了Using filesort,也是最坏的情况。优化是必须的。
show index from article ;
开始优化:
1.1新建索引+删除索引
ALTER TABLE article add index index_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_ccv on article(category_id,comments,views);
但是分析三个字段,category_id ,comments ,views
按照BTree 索引建立的工作原理,
先排序category_id,
如果遇到相同的category_id 则再排序comments,如果遇到相同的comments则再排序views。
当comments 字段在联合索引中间位置时,
因为comments>1是范围值(range) ,
mysql 无法利用索引再对后面的view部分进行检索,即range类型查询字段后面的索引无效。
所以,创建索引,选择
create index idx_article_ccv on article(category_id,views);
查看添加索引后的查询效果:
type为ref extra 为Using where
That’s beautiful
两表:
create table if not exists clazz (
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 clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(card) values(FLOOR(1+(RAND()*20)));
insert into clazz(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)));
如果是左连接 就将索引建到右表;
如果是右连接 就将索引建到左表;
事例:
可以看出来book在左索引生效,book在右不走索引;
三表:
再加一张表:
create table if not exists phone (
phoneid INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL
);
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)));
book phone 为全表扫描
为book phone 建立索引后的结果
结论:
join语句的优化
尽可能减少join语句中的NestedLoop 的循环总次数;”永远用小结果集驱动大结果集”
优先优化NestedLoop 的内层循环:
保证join语句中被驱动表上join条件字段已经被索引;
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬joinbuffer的设置。