MySQL--索引三表优化案例分析

ゝ一纸荒年。 2021-08-13 19:52 547阅读 0赞

### 索引三表优化案例分析 ###

**在两表的基础上再创建一张表：**

CREATE TABLE IF NOT EXISTS `phone`(
    `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(`phoneid`)
    )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)));

**案例：**

EXPLAIN SELECT * 
    FROM class LEFT JOIN book
    ON class.card = book.card LEFT JOIN phone
    ON book.card = phone.card;

![在这里插入图片描述][20191124181057738.png]

结论：存在全表扫描和连接缓存，必须优化

ALTER TABLE `book` ADD INDEX idx_book(`card`);
    ALTER TABLE `phone` ADD INDEX idx_phone(`card`);

![在这里插入图片描述][20191124181618325.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

[20191124181057738.png]: /images/20210813/cc148b77fa3642a7aa97c3bbfd0c2068.png
[20191124181618325.png]: /images/20210813/b5c478d2a333419d86fd638e99efdb87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/44c7e9dd538f4047b498e88894386989.png