USE mysqldb;
#1.索引单表优化案例
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');
SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;
#查看上条sql语句的性能
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;
#创建一个category_id,comments,views的复合索引
CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
SHOW INDEX FROM article;
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;
#上条sql语句的extra里有Using filesort,也就是重新使用了一个外部索引排序,需要优化
#索引ccv在遇到comments>1这种范围类的失效,导致后面的排序索引用不上
#优化如下:
DROP INDEX idx_article_ccv ON article;
CREATE INDEX idx_article_cv ON article(category_id,views);
EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1;