5.2 索引两表优化案例
1、案例
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、下面使用explain 分析sql
explain SELECT * from class LEFT JOIN book ON class.card = book.card
结论:type 有All ,需要优化
3、优化
#添加索引优化 ALTER TABLE `book` ADD INDEX Y(`card`); #第2次explain explain SELECT * from class LEFT JOIN book ON class.card = book.card
结论:
#可以看到第二行的 type 变为了 ref , rows 也变成了 1 优化比较明显。 #这是由左连接特性决定的。LEFT JOIN 条件用于确定从右表搜索行,左边一定都有, #所以右边是我们的关键点,一定需要建立索引。
3、再次分析
#左连接 改成 右连接 explain SELECT * from class RIGHT JOIN book ON class.card = book.card
#删除索引 drop index Y on book; #重建索引 create index X on class(card); #再次分析 explain SELECT * from class RIGHT JOIN book ON class.card = book.card
结论:优化比较明显。这是因为RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。
综上所述 :我们得到以下结论
左连接:索引建在右表上。 右连接:索引建在左表上。
关注我的公众号,精彩内容不能错过
作者:程序员果果
出处:blog.itwolfed.com
欢迎关注公众号——《程序员果果》 ,分享SpringBoot、SpringCloud、Dubbo、Golang、Docker相关知识与技巧。
原创 Java 博客,点我看看?