MySQL索引优化(索引单表优化案例)
1、单表查询优化
建表SQL
CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
); INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3'); SELECT * FROM article;
案例
#查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。
执行sql:
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#结论:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
查询索引:show index from article;
#开始优化:
# 1.1 新建索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article
# 1.2 第2次EXPLAIN
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,如果遇到相同的 comments 则再排序 views。
#当 comments 字段在联合索引里处于中间位置时,
#因comments > 1 条件是一个范围值(所谓 range),
#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
# 1.3 删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
# 1.4 第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);
# 1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#结论:可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
最新文章
- mysql 批量插入数据存储过程
- JavaScript Module Pattern: In-Depth
- MySQL默认数据库
- 【转】Unity 之 移动设备的触控操作
- PAT (Basic Level) Practise:1017. A除以B
- 【转载】PHP程序员的技术成长规划
- python用paramiko将执行的结果存入excel表格
- HDU-3665(单源最短路)
- javascript中的promise和deferred:实践(二)
- MySQL查询执行的基础——查询优化处理
- 3、Linux连接oracle
- Java核心技术卷一基础知识-第11章-异常、断言、日志和调试-读书笔记
- 使用Visual Studio Team Services敏捷规划和项目组合管理(四)——冲刺计划和任务板
- AngularJS:directive自定义的指令
- hibernate04--三种状态之间的转换
- Window环境下Python和Django的安装,以及项目的创建
- 用Eclipse开发Androd应用程序时,自带虚机模拟器太慢了,怎么办
- 自然语言交流系统 phxnet团队 创新实训 项目博客 (十)
- 全新升级的WiFi无线上网短信认证系统,适用于咖啡厅、足浴等公共场所,提高门门店营业收入
- @PropertySource加载文件的两种用法以及配置文件加载顺序
热门文章
- 使用指定MTU到特定IP
- NDK下编译JNI
- Java synchronized到底锁住的是什么?
- windows的 附件到底是什么东东?
- C#程序自动安装数字证书
- Collector的使用
- 未解决:found 1 high severity vulnerability run `npm audit fix` to fix them, or `npm audit` for details
- spss中如何处理极端值、错误值
- Python3 多线程编程 threading模块
- 使用Sklearn构建朴素贝叶斯分类器-新闻分类