10 两表优化案例
2017-12-15 本文已影响2人
笑Skr人啊
创建表
#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)
