1.三表优化数据

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`id`)

);

运行一定次数下列sql

INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));

CREATE TABLE IF NOT EXISTS `book`(
`book_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`book_id`)
)

运行一定次数下列sql

INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); 

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

运行一定次数下列sql

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)))

2.分析

查询
EXPLAIN SELECT * FROM  class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card

在phone表中建立索引
CREATE INDEX index_card ON phone(card)
在book表中建立索引
CREATE INDEX index_card ON book(card)

查看索引

　SHOW INDEX FROM phone

　SHOW INDEX FROM book

查询结果

EXPLAIN SELECT * FROM  class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card=phone.card

结论：后两行查询type都是ref且总的rows优化很好，效果不错。因此索引最好设置在经常需要查询的字段中

JOIN语句优化

1.尽可能的减少join语句中的NestedLoop（嵌套循环）的循环总次数：“永远用小结果集驱动大结果集” 小表驱动大表

2.优先优化NestedLoop（嵌套循环）里面的内层循环；

3.保证Join语句中被驱动表上Join条件字段已经被索引

4.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置