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