MySQL的索引單表優化案例分析

2019-11-09 17:13:31

字體：大中小

來源：轉載

供稿：網友

建表

建立本次優化案例中所需的數據庫及數據表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類型查詢字段后面索引無效。