MySQL的索引单表优化案例分析

时间：2024-01-06 15:06:38

建表

  • 建立本次优化案例中所需的数据库及数据表
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_id
SELECT 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查询优化信息

MySQL的索引单表优化案例分析

  • 结论：很显然type是ALL，即最坏情况。Extra里还出现Using filesort（文件内排序），也是最坏情况，所以优化是必须的。

开始优化

1.1新建索引+删除索引

  • 建立索引的SQL语句
CREATE INDEX idx_article_ccv ON article (category_id,comments,views);
  • 再次执行查询分析sql
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • 查询分析结果

MySQL的索引单表优化案例分析

  • 结论：

    type变成了range，这是可以忍受的。但是extra里使用了Using filesort 仍然是无法接受的。

    但是我们已经建立的索引，为啥没有用呢？

    这是因为按照BTree索引的工作原理

    先排序category_id，

    如果遇到相同的category_id则再排序comments，如果遇到相同的commetns则再排序views

    当comments字段在联合索引中处于中间位置时，

    因为comments > 1 条件是一个范围值（所谓的range），

    Mysql无法利用索引再对后面的views部分进行检索，即range类型查询字段后面索引无效。

1.2 第二次优化

  • 删除不合适的索引
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;

  • 查询分析结果

    MySQL的索引单表优化案例分析

  • 结论：

    根据MySQL的查询分析报告可知，使用当前建立的索引，达到了type=ref,且extra中没有出现Using filesort，因此，我们现在使用的索引结构达到了最优的情况。

相关文章