mysql 多表索引_mysql 两表索引优化

建表语句

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

使用explain对sql进行检查

mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | NULL |

| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

可以看到type都为all(执行全表查询)

使用了join缓冲区(其使用的算法为Block Nested-Loop(BNL))

添加索引

对book表的card属性添加索引

alter table `book` add index Y(`card`);

再次用explain查看之前的sql

mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card;

+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+

| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | NULL |

| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | test.class.card | 1 | 100.00 | Using index |

+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

可以看到book表的type优化为了type, 且extra变为了using index, 不会再使用缓冲区了。

对class表的card属性添加索引

alter table `class` add index Y(`card`);

再次用explain查看之前的sql

mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card;

+----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+

| 1 | SIMPLE | class | NULL | index | NULL | Y | 4 | NULL | 24 | 100.00 | Using index |

| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | test.class.card | 1 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

可以看到type变为了index, ref 而 extra 都变为了Using index

可以得出的结论

左连接时, 对右表建索引进行优化更重要(左表都保留, 必然全表扫描)

右连接时, 对左表建索引进行优化更重要(右表都保留, 必然全表扫描)