MySQL索引优化案例

这里我们分成三种情况进行分析，分别是单表，两表，三表

1.单表

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10)  NOT NULL,
`category_id` INT(10) NOT NULL,
`views`  INT(10) NOT NULL,
`comments` INT(10) NOT NULL,
`title` VARBINARY(255)  NOT NULL,
`content` TEXT NOT NULL
);

mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

案例

#查询category_id为1且comments大于1的情况下，views最多的atticle_id

mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

#可以看到，type是All，是最坏的情况，extra里还出现了Using filesort，也是最坏的情况，优化是必须的~

#开始优化

#新建索引

CREATE INDEX idx_article_ccv ON `article`(`category_id`,`comments`,`views`)

mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                 |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

虽然全表扫描的问题解决了，但是Using filesort问题没有解决~

因为comments>1这个范围导致后面的views索引失效

重新建索引

mysql> show index from article;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

这样就比较好接受了

 2.两张表

CREATE TABLE IF NOT EXISTS `agency`(
    `agency_id` INT(10)  NOT NULL AUTO_INCREMENT,
    `guide_id` INT(10)  NOT NULL,
    PRIMARY KEY(`agency_id`) 
);
CREATE TABLE IF NOT EXISTS `language`(
    `language_id` INT(10) NOT NULL AUTO_INCREMENT,
    `guide_id` INT(10) NOT NULL,
    PRIMARY KEY(`language_id`) 
);

mysql> select * from language;
+-------------+----------+
| language_id | guide_id |
+-------------+----------+
|           1 |       10 |
|           2 |        7 |
|           3 |        3 |
|           4 |       13 |
|           5 |       17 |
|           6 |        4 |
|           7 |        9 |
|           8 |       19 |
|           9 |       16 |
|          10 |       20 |
+-------------+----------+
10 rows in set (0.00 sec)

mysql> select * from agency;
+-----------+----------+
| agency_id | guide_id |
+-----------+----------+
|         1 |        2 |
|         2 |       18 |
|         3 |        3 |
|         4 |       20 |
|         5 |       15 |
|         6 |       11 |
|         7 |       13 |
|         8 |        4 |
|         9 |       14 |
|        10 |       10 |
+-----------+----------+
10 rows in set (0.00 sec)

下面开始explain分析

mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | language | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL                                               |
|  1 | SIMPLE      | agency   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

type是All，全表扫描

(1)我们首先在TableB也就是agency表加索引(左连接加在右表上)

mysql> alter table `agency` add index `idx_agency`(`guide_id`);

此时再查看一下执行计划

mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref                      | rows | Extra       |
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | language | ALL  | NULL          | NULL       | NULL    | NULL                     |   10 | NULL        |
|  1 | SIMPLE      | agency   | ref  | idx_agency    | idx_agency | 4       | db0629.language.guide_id |    1 | Using index |
+----+-------------+----------+------+---------------+------------+---------+--------------------------+------+-------------+
2 rows in set (0.00 sec)

删除索引idx_agency，在左表创建索引

mysql> drop index idx_agency on agency;

mysql> alter table language add index ldx_language(guide_id);

mysql> explain select * from language left join agency on language.guide_id=agency.guide_id;
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                                              |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | language | index | NULL          | ldx_language | 4       | NULL |   10 | Using index                                        |
|  1 | SIMPLE      | agency   | ALL   | NULL          | NULL         | NULL    | NULL |   10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+----------------------------------------------------+

这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左表一定都有，所以右表一定要创建索引~

同理，right join中右表中的数据全都有，所以索引建在左表上

3.三张表的情况

mysql> explain select * from agency left join language on agency.guide_id=language.guide_id left join contact on language.guide_id=contact.guide_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | agency   | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL                                               |
|  1 | SIMPLE      | language | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | contact  | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

全部全表扫描

 加索引

mysql> alter table language add index idx_language(guide_id);

mysql> alter table contact add index idx_contact(guide_id);

mysql> explain select * from agency left join language on agency.guide_id=language.guide_id left join contact on language.guide_id=contact.guide_id;
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref                      | rows | Extra       |
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
|  1 | SIMPLE      | agency   | ALL  | NULL          | NULL         | NULL    | NULL                     |   10 | NULL        |
|  1 | SIMPLE      | language | ref  | idx_language  | idx_language | 4       | db0629.agency.guide_id   |    1 | Using index |
|  1 | SIMPLE      | contact  | ref  | idx_contact   | idx_contact  | 4       | db0629.language.guide_id |    1 | Using index |
+----+-------------+----------+------+---------------+--------------+---------+--------------------------+------+-------------+
3 rows in set (0.00 sec)