索引三表优化实战

ゞ浴缸里的玫瑰 2022-09-12 00:54 178阅读 0赞

# 一 脚本 #

CREATE TABLE IF NOT EXISTS `phone`(
    `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `card` INT (10) UNSIGNED NOT NULL
    )ENGINE = INNODB;
    
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
    mysql> select * from phone;
    +---------+------+
    | phoneid | card |
    +---------+------+
    |       1 |    5 |
    |       2 |   15 |
    |       3 |    2 |
    |       4 |    3 |
    |       5 |    8 |
    |       6 |   12 |
    |       7 |   15 |
    |       8 |   17 |
    |       9 |   19 |
    |      10 |    3 |
    |      11 |   17 |
    |      12 |   17 |
    |      13 |   13 |
    |      14 |   15 |
    |      15 |   13 |
    |      16 |   20 |
    |      17 |    2 |
    |      18 |    9 |
    |      19 |   17 |
    |      20 |   17 |
    +---------+------+
    20 rows in set (0.00 sec)
    
    # 其它两张表脚本参考《索引两表优化实战》

# 二 实战 #

### 1 确保要操作的三张表都没有建索引 ###

### 2 需要执行的业务操作 ###

mysql> select * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on book.card = phone.card;
    +----+------+--------+------+---------+------+
    | id | card | bookid | card | phoneid | card |
    +----+------+--------+------+---------+------+
    |  7 |    3 |      9 |    3 |       4 |    3 |
    | 12 |    3 |      9 |    3 |       4 |    3 |
    |  7 |    3 |     17 |    3 |       4 |    3 |
    | 12 |    3 |     17 |    3 |       4 |    3 |
    | 14 |    8 |      6 |    8 |       5 |    8 |
    |  2 |   12 |     14 |   12 |       6 |   12 |
    |  3 |   12 |     14 |   12 |       6 |   12 |
    | 16 |   12 |     14 |   12 |       6 |   12 |
    |  2 |   12 |     18 |   12 |       6 |   12 |
    |  3 |   12 |     18 |   12 |       6 |   12 |
    | 16 |   12 |     18 |   12 |       6 |   12 |
    | 10 |   17 |      2 |   17 |       8 |   17 |
    | 20 |   17 |      2 |   17 |       8 |   17 |
    | 10 |   17 |     15 |   17 |       8 |   17 |
    | 20 |   17 |     15 |   17 |       8 |   17 |
    |  7 |    3 |      9 |    3 |      10 |    3 |
    | 12 |    3 |      9 |    3 |      10 |    3 |
    |  7 |    3 |     17 |    3 |      10 |    3 |
    | 12 |    3 |     17 |    3 |      10 |    3 |
    | 10 |   17 |      2 |   17 |      11 |   17 |
    | 20 |   17 |      2 |   17 |      11 |   17 |
    | 10 |   17 |     15 |   17 |      11 |   17 |
    | 20 |   17 |     15 |   17 |      11 |   17 |
    | 10 |   17 |      2 |   17 |      12 |   17 |
    | 20 |   17 |      2 |   17 |      12 |   17 |
    | 10 |   17 |     15 |   17 |      12 |   17 |
    | 20 |   17 |     15 |   17 |      12 |   17 |
    |  8 |   13 |     19 |   13 |      13 |   13 |
    |  8 |   13 |     19 |   13 |      15 |   13 |
    | 13 |   20 |      5 |   20 |      16 |   20 |
    | 17 |   20 |      5 |   20 |      16 |   20 |
    | 10 |   17 |      2 |   17 |      19 |   17 |
    | 20 |   17 |      2 |   17 |      19 |   17 |
    | 10 |   17 |     15 |   17 |      19 |   17 |
    | 20 |   17 |     15 |   17 |      19 |   17 |
    | 10 |   17 |      2 |   17 |      20 |   17 |
    | 20 |   17 |      2 |   17 |      20 |   17 |
    | 10 |   17 |     15 |   17 |      20 |   17 |
    | 20 |   17 |     15 |   17 |      20 |   17 |
    |  5 |   10 |      1 |   10 |    NULL | NULL |
    |  1 |   16 |      3 |   16 |    NULL | NULL |
    |  5 |   10 |      4 |   10 |    NULL | NULL |
    |  4 |    1 |      7 |    1 |    NULL | NULL |
    | 19 |   11 |     10 |   11 |    NULL | NULL |
    | 19 |   11 |     12 |   11 |    NULL | NULL |
    |  4 |    1 |     13 |    1 |    NULL | NULL |
    |  6 |    7 |     16 |    7 |    NULL | NULL |
    | 18 |    7 |     16 |    7 |    NULL | NULL |
    |  9 |   15 |   NULL | NULL |    NULL | NULL |
    | 11 |   19 |   NULL | NULL |    NULL | NULL |
    | 15 |   19 |   NULL | NULL |    NULL | NULL |
    +----+------+--------+------+---------+------+
    51 rows in set (0.00 sec)

### 3 没优化前 ###

查询效率很差，需要优化

### 4 建立索引进行优化 ###

mysql> alter table phone add index z(card);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table book add index z(card);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

### 5 优化后的效果 ###

mysql> explain select * from class LEFT JOIN book on class.card = book.card LEFT JOIN phone on book.card = phone.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            |   20 |   100.00 | NULL        |
    |  1 | SIMPLE      | book  | NULL       | ref  | z             | z    | 4       | db01.class.card |    1 |   100.00 | Using index |
    |  1 | SIMPLE      | phone | NULL       | ref  | z             | z    | 4       | db01.book.card  |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+

后两行的 type 都是 ref 且总 rows 优化很好，效果不错。因此索引最好建立在需要经常查询的字段中。

# 三 小结 #

尽可能减少 join 语句中嵌套的循环总次数，永远用小结果集驱动大的结果集。即小表驱动大表。（书多于类别，书是大表，类别是小表，类别驱动书）

优先优化嵌套循环中的内层循环。

保证 join 语句中被驱动表上 join 条件字段已经被索引。（被驱动的表为 book 和 phone，给它们对应的条件字段 card 建立索引）

当无法保证被驱动表的 Join 条件字段被索引且内层资源充足的前提下，不要太吝啬 JoinBuffer 的设置。