欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql 高级(基础四 索引优化 join 案例)

程序员文章站 2022-05-03 14:41:28
...

索引分析:单表
建表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 分析
mysql 高级(基础四 索引优化 join 案例)
显然,type是all ,即最坏的情况,Extra 里还出现了Using filesort,也是最坏的情况。优化是必须的。

show index from article ;

mysql 高级(基础四 索引优化 join 案例)

开始优化:
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);

mysql 高级(基础四 索引优化 join 案例)
查看添加索引后的查询效果:
mysql 高级(基础四 索引优化 join 案例)
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)));

如果是左连接 就将索引建到右表;
如果是右连接 就将索引建到左表;
事例:
mysql 高级(基础四 索引优化 join 案例)
可以看出来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)));

mysql 高级(基础四 索引优化 join 案例)
book phone 为全表扫描
为book phone 建立索引后的结果
mysql 高级(基础四 索引优化 join 案例)
结论:
join语句的优化
尽可能减少join语句中的NestedLoop 的循环总次数;”永远用小结果集驱动大结果集”
优先优化NestedLoop 的内层循环:
保证join语句中被驱动表上join条件字段已经被索引;
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬joinbuffer的设置。