目录

  • 一、建表语句
  • 二、索引两表左关联优化分析（以class表为左表，book表为右表演示）
  • 三、索引两表右关联优化分析（以class表为左表，book表为右表演示）
  • 四、索引两表优化总论


一、建表语句

1、书籍的类别表建表语句

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类id' ,
`card` INT(10) UNSIGNED NOT NULL COMMENT '类别',
PRIMARY KEY (`id`) 
)COMMENT='书籍的类别表';

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)));

给表2个字段加索引 两个表建立索引_主键


2、书籍表的建表语句

CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '书籍id',
`card` INT(10) UNSIGNED NOT NULL COMMENT '类别',
PRIMARY KEY (`bookid`) 
)COMMENT='书籍表';

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个字段加索引 两个表建立索引_访问类型_02

二、索引两表左关联优化分析（以class表为左表，book表为右表演示）

1、执行2张表的索引查询sql如下：

SHOW INDEX FROM book;

给表2个字段加索引 两个表建立索引_建表_03

SHOW INDEX FROM class;


给表2个字段加索引 两个表建立索引_访问类型_04

  • 结论：只有主键索引

2、直接执行explain查看情况

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

给表2个字段加索引 两个表建立索引_建表_05

  • 结论：type 有All,需要进行优化。因为type的常用访问类型结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL，得保证查询至少达到range级别，最好能达到ref。

3、第一次优化（左表即class表中建立索引）
（1）、在左表（即class表）中建立索引,并查看所建立的索引。

ALTER TABLE class ADD INDEX index_car (card);
SHOW INDEX FROM class ;

给表2个字段加索引 两个表建立索引_主键_06

（2）、执行EXPLAIN

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

给表2个字段加索引 两个表建立索引_主键_07

  • 结论：左表（即class表）的type变成了index，比type为all的级别高一级。因为type的常用访问类型结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL，得保证查询至少达到range级别，最好能达到ref。

4、第二次优化（右表即book表中建立索引）
（1）、先删除第一次优化创建的旧索引，并查看索引情况。

DROP INDEX index_car ON class ;
SHOW INDEX FROM class ;

给表2个字段加索引 两个表建立索引_建表_08

  • 结论：只有主键索引

（2）、在右表（即book表）中建立索引,并查看所建立的索引。

ALTER TABLE book ADD INDEX index_car (card);
SHOW INDEX FROM book;

给表2个字段加索引 两个表建立索引_给表2个字段加索引_09

（3）、执行EXPLAIN

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

给表2个字段加索引 两个表建立索引_访问类型_10

  • 结论：可以看到第二行的 type 变为了ref，rows也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以左连接时右表是我们的关键点,一定需要建立索引。

三、索引两表右关联优化分析（以class表为左表，book表为右表演示）

1、执行2张表的索引查询sql如下：

SHOW INDEX FROM book;

给表2个字段加索引 两个表建立索引_建表_03

SHOW INDEX FROM class;


给表2个字段加索引 两个表建立索引_访问类型_04

  • 结论：只有主键索引

2、直接执行explain查看情况

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

给表2个字段加索引 两个表建立索引_访问类型_13

  • 结论：type 有All,需要进行优化。因为type的常用访问类型结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL，得保证查询至少达到range级别，最好能达到ref。

3、第一次优化（右表即book表中建立索引）
（1）、在右表（即book表）中建立索引,并查看所建立的索引。

ALTER TABLE book ADD INDEX index_car (card);
SHOW INDEX FROM book;

给表2个字段加索引 两个表建立索引_给表2个字段加索引_14

（2）、执行EXPLAIN

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

给表2个字段加索引 两个表建立索引_主键_15

  • 结论：右表（即book表）的type变成了index，比type为all的级别高一级。因为type的常用访问类型结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL，得保证查询至少达到range级别，最好能达到ref。

4、第二次优化（左表即class表中建立索引）
（1）、先删除第一次优化创建的旧索引，并查看索引情况。

DROP INDEX index_car ON book;
SHOW INDEX FROM book;

给表2个字段加索引 两个表建立索引_主键_16

  • 结论：只有主键索引

（2）、在左表（即class表）中建立索引,并查看所建立的索引。

ALTER TABLE class ADD INDEX index_car (card);
SHOW INDEX FROM class;

给表2个字段加索引 两个表建立索引_建表_17

（3）、执行EXPLAIN

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

给表2个字段加索引 两个表建立索引_建表_18

  • 结论：可以看到第二行的 type 变为了ref，rows也变成了优化比较明显。这是由右连接特性决定的。RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以右连接时左表是我们的关键点,一定需要建立索引。

四、索引两表优化总论

  • 左连接时右表建立索引
  • 右连接时左表建立索引