MySQL的索引单表优化案例分析

2019-11-09 16:02:52

字体：大中小

来源：转载

供稿：网友

建表

建立本次优化案例中所需的数据库及数据表CREATE DATABASE db0206;USE db0206;CREATE TABLE `db0206`.`article`( `id` INT(11) NOT NULL AUTO_INCREMENT, `author_id` INT(11) UNSIGNED NOT NULL, `category_id` INT(11) UNSIGNED NOT NULL, `views` INT(11) UNSIGNED NOT NULL, `comments` INT(11) UNSIGNED NOT NULL, `title` VARCHAR(255) NOT NULL, `content` TEXT NOT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB CHARSET=utf8;INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '1', '1', '1', '1', '1', '1');INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '2', '2', '2', '2', '2', '2');INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '3', '3', '3', '3', '3', '3');

单表索引分析

下面我们来执行这条sql：查询category_id为1，且comments大于1的情况下，views最多的article_idSELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;通过explain命令来查看sql查询优化信息EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;sql查询优化信息

sql查询分析

结论：很显然type是ALL，即最坏情况。Extra里还出现Using filesort（文件内排序），也是最坏情况，所以优化是必须的。

开始优化

1.1新建索引+删除索引

建立索引的SQL语句CREATE INDEX idx_article_ccv ON article (category_id,comments,views);再次执行查询分析sqlEXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;查询分析结果

第一次优化结果

结论： type变成了range，这是可以忍受的。但是extra里使用了Using filesort 仍然是无法接受的。但是我们已经建立的索引，为啥没有用呢？这是因为按照BTree索引的工作原理先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同的commetns则再排序views 当comments字段在联合索引中处于中间位置时，因为comments > 1 条件是一个范围值（所谓的range）， MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面索引无效。