MySQL的索引单表优化案例分析
来源:互联网 发布:linux sed 不显示首行 编辑:程序博客网 时间:2024/06/05 04:34
建表
- 建立本次优化案例中所需的数据库及数据表
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查询优化信息
- 结论:很显然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;
- 查询分析结果
- 结论:
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的查询分析报告可知,使用当前建立的索引,达到了type=ref,且extra中没有出现Using filesort,因此,我们现在使用的索引结构达到了最优的情况。
0 0
- MySQL的索引单表优化案例分析
- mysql 单表索引优化案例
- 索引案例一:单表优化案例
- mysql 两个表索引优化案例
- mysql 三个表索引优化案例
- mysql优化案例分析
- Mysql索引的优化分析-索引优化(1)
- mysql 索引优化案例(索引失效)
- Mysql索引的优化分析-索引性能分析
- Mysql数据库的索引分析和优化
- Mysql数据库的索引分析和优化
- Mysql数据库的索引分析和优化
- Mysql数据库的索引分析和优化
- Mysql数据库的索引分析和优化
- mysql数据库的索引分析和优化
- Mysql索引的优化分析-索引的简介
- MySQL索引分析和优化索引分析
- Mysql性能优化案例-覆盖索引分享
- range,xrange,arange比较
- Review: calix Automation test case study notes
- poj1488
- java设计模式趣谈
- SSM框架——以注解形式实现事务管理
- MySQL的索引单表优化案例分析
- 单点登录
- Remote Desktop Organizer远程访问时报错
- JQuery:除指定区域外点击任何地方隐藏DIV
- Controller中不会调用ViewDidLoad方法
- Android 属性动画(Property Animation) 完全解析 (上)
- kylin1.6.0构建build测试cube出错
- Tomcat工作原理
- ActivityOptionsCompat