MySQL的索引單表優(yōu)化案例分析

2019-11-09 16:02:52

字體：大中小

供稿：網(wǎng)友

建表

建立本次優(yōu)化案例中所需的數(shù)據(jù)庫及數(shù)據(jù)表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');

單表索引分析

下面我們來執(zhí)行這條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查詢優(yōu)化信息EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;sql查詢優(yōu)化信息

sql查詢分析

結(jié)論：很顯然type是ALL，即最壞情況。Extra里還出現(xiàn)Using filesort（文件內(nèi)排序），也是最壞情況，所以優(yōu)化是必須的。

開始優(yōu)化

1.1新建索引+刪除索引

建立索引的SQL語句CREATE INDEX idx_article_ccv ON article (category_id,comments,views);再次執(zhí)行查詢分析sqlEXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;查詢分析結(jié)果

第一次優(yōu)化結(jié)果

結(jié)論： type變成了range，這是可以忍受的。但是extra里使用了Using filesort 仍然是無法接受的。但是我們已經(jīng)建立的索引，為啥沒有用呢？這是因為按照BTree索引的工作原理先排序category_id，如果遇到相同的category_id則再排序comments，如果遇到相同的commetns則再排序views 當(dāng)comments字段在聯(lián)合索引中處于中間位置時，因為comments > 1 條件是一個范圍值（所謂的range）， MySQL無法利用索引再對后面的views部分進行檢索，即range類型查詢字段后面索引無效。