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

建表

  • 建立本次优化案例中所需的数据库及数据表
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,因此,我们现在使用的索引结构达到了最优的情况。

上一篇:一个让开发人员仅通过声明式代码的方式实现智能数据结构的Java框架


下一篇:MySQL 5.7 GTID OOM bug案例分析 --大量压测后主从不同步