MySQL优化|一分钟带你了解单表优化

2021 年 5 月 26 日
筆記

在开始前，分享给大家我看过觉得讲数据库讲的算是很不错的，也在B站拥有百万播放量的教程。

这个MySQL视频是动力节点的老杜讲解，个人也很喜欢老杜的教学风格，老杜真的是从MySQL基础一点点带我入门，基础也学得很扎实。

这个教程总体来说就就像列文虎克教学，细到极致，妙到毫巅。

内容涵盖了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性等等。

学完这套视频基本可掌握MySQL全套知识了，值得收藏学习，需要的小伙伴点击以下链接👇

在线观看：

MySQL基础入门-mysql教程-数据库实战（MySQL基础+MySQL高级+MySQL优化+MySQL34道作业题）_哔哩哔哩 (゜-゜)つロ干杯~-bilibili

资料下载：

MySQL视频教程全套免费下载【适合零基础入门学习】 – 动力节点 (bjpowernode.com)

sql创建代码

1 CREATE TABLE IF NOT EXISTS `article` (
2 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
3 `author_id` INT(10) UNSIGNED NOT NULL,
4 `category_id` INT(10) UNSIGNED NOT NULL,
5 `views` INT(10) UNSIGNED NOT NULL,
6 `comments` INT(10) UNSIGNED NOT NULL,
7 `title` VARBINARY(255) NOT NULL,
8 `content` TEXT NOT NULL
9 );

添加测试数据

1 INSERT INTO `article` ( `author_id`, `category_id`,`views`,`comments`, `title` ,`content`) VALUES
2 (1, 1,1,1,'1','1'),
3 (2,2,2,2, '2', '2'),
4 (1, 1,3,3,'3', '3');

查询表数据

sql需求：

查询category_ id 为1且comments大于1的情况下,views最多的文章ID

写出SQL语句并执行

1 select id,author_id from article where category_id=1 and comments>1 order by views;

可以对比上边表数据对照看

初步分析SQL语句

可以看到进行了全表扫描，并且使用了文件排序这种SQL是必须进行优化的

建立索引

1 # 创建索引
2 create index ind_article_ccv on article(category_id,comments,views);
3 
4 # 查看表的所有索引
5 show index from article

在次使用explain分析

1 explain select id,author_id from article where category_id=1 and comments>1 order by views;

发现全表扫描我们解决了，但是文件排序还没有解决。那么这个方案也是不可以的

删除索引

1 # 删除索引
2 drop index ind_article_ccv on article
3 
4 # 查看表的所有索引
5 show index from article

重新建立索引，并查看分析

那么我们在来给caterory_id和view建立一个索引

1 # 创建索引
2 create index ind_article_cv on article(category_id,views);
3 
4 # 分析语句
5 explain select id,author_id from article where category_id=1 and comments>1 order by views;

此次优化总结

在第一次给三个字段都加上索引后，type变成了range这个是可以忍受的，但是extra里使用了filesort仍是无法忍受的
但是我们已经给他们三个字段建立了索引了，为什么没有用呢？
这是因为根据Btree索引的工作原理
先进行排序category_id
如果遇到相同的category_id则在排序comments，如果在遇到comments则再排序views
当comments字段在联合索引里处于中间位置时
因为comments>1是一个范围值（所谓range）
MySQL无法利用索引在对后边的views部分进行检索，即range类型查询字段后面的索引会失效

————————————————
原文链接：//blog.csdn.net/fangkang7/article/details/105282077