10 两表优化案例

创建表

#class表
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

#book
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `book_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL语句

mysql> explain SELECT * from class c LEFT JOIN book b ON  c.card = b.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    9 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

在左表建立索引

mysql> show index from class;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| class |          0 | PRIMARY        |            1 | id          | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| class |          1 | idx_class_card |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


mysql> explain SELECT * from class c LEFT JOIN book b ON  c.card = b.card;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | c     | index | NULL          | idx_class_card | 4       | NULL |   20 | Using index |
|  1 | SIMPLE      | b     | ALL   | NULL          | NULL           | NULL    | NULL |    9 |             |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

在右表建立索引,删掉左表索引

mysql> show index from book;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY       |            1 | book_id     | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | idx_book_card |            1 | card        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

mysql> explain SELECT * from class c LEFT JOIN book b ON  c.card = b.card;
+----+-------------+-------+-------+---------------+----------------+---------+-------------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref         | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | c     | index | NULL          | idx_class_card | 4       | NULL        |   20 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_book_card | idx_book_card  | 4       | db01.c.card |    1 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)

在右表建立索引,type变为ref,rows优化也比较明显,这是由左连接特性决定的,left join条件用于确定如何从右表搜索行,左边一定都有,所以一定要在右表建立索引