MySql（十四）--索引单表优化

create table if not exists `article`(
 `id` INT(10) unsigned NOT NULL primary key auto_increment,
 `article_id` INT(10) unsigned NOT NULL,
 `category_id` INT(10) unsigned NOT NULL,
 `views` INT(10) unsigned NOT NULL,
 `comments` INT(10) unsigned NOT NULL,
 `title` VARCHAR(255) NOT NULL,
 `content` TEXT NOT NULL
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into `article` (`article_id`, `category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

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

把comments>1改成=1

性能好了很多。是因为comments>1是范围查询，会截断索引views。

删除索引：drop index idx_article_ccv on article

再建索引

create index idx_article_cv on article(category_id, views)