SQL学习笔记整理18（索引优化案例）

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;