MySQL--索引单表优化案例 短命女 2021-08-13 21:31 426阅读 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
还没有评论,来说两句吧...