Mysql优化查询示例

范围失效

数据准备

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_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` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
 
INSERT INTO 
`article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) 
VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

第一次建立索引

ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );

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

这是因为按照BTree 索引的工作原理,先排序 categoryid,如果遇到相同的 categoryid 则再排序 comments,如果遇到相同的 comments 则再排序 views.

当comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值，无法利用索引再对后面的views 部分进行检索,即 range类型查询字段后面的索引无效。

二次建索引

DROP INDEX idx_article_ccv ON article;
ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;

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