MySQL--索引单表优化案例

短命女 2021-08-13 21:31 659阅读 0赞

#### 索引单表优化 ####

**建表及插入数据：**

CREATE TABLE IF NOT EXISTS `article`(#文章表
    `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,#id
    `author_id` INT(10) UNSIGNED NOT NULL,#作业id
    `category_id` INT(10) UNSIGNED NOT NULL,#分类id
    `views` INT(10) UNSIGNED NOT NULL,#被查看次数
    `comments` INT(10) UNSIGNED NOT NULL,#回帖
    `title` VARBINARY(255) NOT NULL,#标题
    `content` TEXT NOT NULL#正文
    );
    
    INSERT INTO `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`)
    VALUE (1,1,1,1,'1','1'),
    			(2,2,2,2,'2','2'),
    			(1,1,3,3,'3','3');

### 案例一 ###

**查询category\_id为1，且comments大于1的情况下，views最多的article\_id**

EXPLAIN SELECT id,author_id 
    FROM article 
    WHERE category_id = 1 AND comments > 1 
    ORDER BY views DESC
    LIMIT 1;

![在这里插入图片描述][20191124165825382.png]  
结论：很显然，type是ALL全表扫描，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况，必须优化。

**开始优化：**  
使用了3列，所以在3个列上创建符合索引

CREATE INDEX idx_article_ccv on article(category_id,comments,views);

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

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

删除索引再次优化：(绕过范围值)

DROP INDEX idx_article_ccv ON article;
    CREATE INDEX idx_article_cv ON article(category_id, views);

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

[20191124165825382.png]: /images/20210813/5610f41564f842ce841b98628429bd90.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/ce9e04b45b82442aa99777ef912f4184.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/0534d604fc3b4c9a9e13eb0357fc3fb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/e613b5c2836a40c7abfb92a5bade28c9.png