共計 3828 個字符，預計需要花費 10 分鐘才能閱讀完成。
本篇內容主要講解“MySQL 的查詢優化方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“MySQL 的查詢優化方法”吧!
1、簡介
一個好的 web 應用，最重要的一點是有著優秀的訪問性能。數據庫 MySQL 是 web 應用的組成部分，也是決定其性能的重要部分。所以提升 MySQL 的性能至關重要。
MySQL 性能的提升可分為三部分，包括硬件、網絡、軟件。其中硬件、網絡取決于公司的財力，需要白嘩嘩的銀兩，這里就不說啦。軟件又細分為很多種，在這里我們通過 MySQL 的查詢優化從而達到性能的提升。
最近看了一些關于查詢優化的書籍，同時也在網上看一些前輩們寫的文章。
以下是自己整理借鑒關于查詢優化的一些總結：
回到頂部
2、截取 SQL 語句
1、全面查詢日志
2、慢查詢日志
3、二進制日志
4、進程列表
SHOW FULL PROCESSLIST;
。。。
回到頂部
3、查詢優化基本分析命令
1、EXPLAIN {PARTITIONS|EXTENDED}
2、SHOW CREATE TABLE tab;
3、SHOW INDEXS FROM tab;
4、SHOW TABLE STATUS LIKE‘tab’;
5、SHOW [GLOBAL|SESSION] STATUS LIKE‘’;
6、SHOW VARIABLES
。。。。
ps：我自己都感覺上面都是沒任何營養的東西。下面才是真正的干貨哈。
回到頂部
4、查詢優化幾個方向
1、盡量避免全文掃描，給相應字段增加索引，應用索引來查詢
2、刪除不用或者重復的索引
3、查詢重寫，等價轉換（謂詞、子查詢、連接查詢）
4、刪除內容重復不必要的語句，精簡語句
5、整合重復執行的語句
6、緩存查詢結果
回到頂部
5、索引優化
回到頂部
5.1、索引優點：
1、保持數據的完整性
2、提高數據的查詢性能
3、改進表的連接操作（jion）
4、對查詢結果進行排序。沒索引將會采用內部文件排序算法進行排序，效率較慢
5、簡化聚合數據操作
回到頂部
5.2、索引缺點
1、索引需要占用一定的存儲空間
2、數據插入、更新、刪除時會受索引的影響，性能會降低。因為數據變更索引也需要進行更新
3、多個索引，優化器需要耗時則優選擇
回到頂部
5.3、索引選擇
1、數據量大時采用
2、數據高度重復時，不采用
3、查詢取出數據大于 20%，將采用全文掃描，不用索引
回到頂部
5.4、索引細究
資料查詢：
MySQL 中的 InnoDB、MyISAM 都是 B -Tree 類型索引
B-Tree 包含：PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT
B-Tree 類型索引不支持（即字段使用以下符號時，將不采用索引）：
, , =, =, BETWEEN, !=, ,like‘%**’
【在此先介紹一下覆蓋索引】
以我自己理解的方式介紹吧。覆蓋索引并不是像主鍵索引、唯一索引一樣真實存在，它只是對索引應用某些特定場景的一種定義【另一種理解：查詢的列是索引列，因此列被索引覆蓋】。它可以突破傳統的限制，使用以上操作符，且依然采用索引進行查詢。
因為查詢的列是索引列，所以不需要讀取行，只需要讀取列字段數據就可以了。【例如你看一本書，需要找某一內容，剛好那內容出現在目錄中，那就不用一頁頁翻了，直接在目錄中定位到第幾頁查找】
如何激活覆蓋索引呢？什么樣才是特定場景呢？
索引字段，在 select 中出現就是了。
復合索引還可能有其他的特殊場景。例如，三列復合索引，僅需要在 select、where、group by、order by 中，任意一個地方出現一次復合索引最左邊列就可以激活使用覆蓋索引了。
查看：
EXPLAIN 中 Extra 顯示有 Using index 表示這條語句采用了覆蓋索引。
結論：
不建議在查詢的時候使用 select*from 進行查詢了，應該寫需要用的字段，并且增加相應的索引，以提高查詢性能。
針對以上操作符實測結果：
1、以 select*from 形式，where 中是 primary key 可以通殺【除 like】（使用主鍵進行查詢）；index 則全不可以。
2、以 select 字段 a from tab where 字段 a《以上操作符》形式測試，結果依然可以使用索引查詢。【采用了覆蓋索引】
其他索引優化方法:
1、使用索引關鍵字作為連接的條件
2、復合索引使用
3、索引合并 or and，將涉及到的字段合并成復合索引
4、where、和 group by 涉及字段加索引
回到頂部
6、子查詢優化
在 from 中為非相關子查詢，可以上拉子查詢到父層。在多表連接查詢考慮連接代價再選擇。
查詢優化器對子查詢一般采用嵌套執行的方式，即對父查詢中的每一行，都執行一次子查詢，這樣子查詢會執行很多次。這種執行方式效率很低。
子查詢轉化為連接查詢優點：
1、子查詢不用執行很多次
2、優化器可以根據信息來選擇不同的方法和連接順序
3、子查詢的連接條件，過濾條件變成父查詢的篩選條件，以提高效率。
優化：
子查詢合并，若多個子查詢，能合并的盡量合并。
子查詢展開，即上拉變成多表查詢（時刻保證等價變化）
注意：
子查詢展開只能展開簡單的查詢，若子查詢含有聚集函數、GROUP BY、DISTINCT，則不能上拉。
select * from t1 (select*from tab where id 10) as t2 where t1.age 10 and t2.age
select*from t1,tab as t2 where t1.age 10 and t2.age 25 and t2.id
具體步驟：
1、from 與 from 合并，修改相應參數
2、where 與 where 合并，用 and 連接
3、修改相應的謂詞（in 改 =）
回到頂部
7、等價謂詞重寫：
1、BETWEEEN AND 改寫為 =、= 之類的。實測：十萬條數據，重寫前后時間，1.45s、0.06s
2、in 轉換多個 or。字段為索引時，兩個都能用到索引，or 效率相對 in 好一點
3、name like‘abc%’改寫成 name =’abc’and name’abd’;
注意：百萬級數據測試，name 沒有索引之前 like 比后一種查詢快；給字段增加索引后，后面的快一點點，相差不大，因為兩種方法在查詢的時候都用到了索引。
。。。。
回到頂部
8、條件化簡與優化
1、將 where、having（不存在 groupby 和聚集函數時）、join-on 條件能合并的盡量合并
2、刪除不必要的括號，減少語法分許的 or 和 and 樹層，減少 cpu 消耗
3、常量傳遞。a=b and b= 2 轉換為 a=2 and b=2。盡量不使用變量 a = b 或 a =@var
4、消除沒用的 SQL 條件
5、where 等號右邊盡量不出現表達式計算；where 中不要對字段進行表達式計算、函數的使用
6、恒等變換、不等式變換。例：測試百萬級數據 a b and b 10 變為 a b and a 10 and b 10 優化顯著
回到頂部
9、外連接優化
即將外連接轉為內連接
優點：
1、優化處理器處理外連接比內連接步驟多且耗時
2、外連接消除后，優化器選擇多表連接順序有更多選擇，可以擇優而選
3、可以將篩選條件最為嚴格的表作為外表（連接順序最前面，是多層循環體的外循環層），
可以減少不必要的 I / O 開銷，能加快算法執行的速度。
on a.id=b.id 與 where a.id=b.id 的差別，on 則表進行連接，where 則進行數據對比
注意：前提必須是結果為 NULL 決絕（即條件限制不要 NULL 數據行，語意上是內連接）
優化原則：
精簡查詢，連接消除，等效轉換，去除多余表對象連接
例如：主鍵 / 唯一鍵作為連接條件，且中間表列只作為等值條件，可以去掉中間表連接
回到頂部
10、其他查詢優化
1、以下將會造成放棄索引查詢，采用全文掃描
1.1、where 子句中使用!= 或 操作符 注意：主鍵支持。非主鍵不支持
1.2、避免使用 or
經測試，并非是使用了 or 就一定不能使用索引，大多情況下是沒用到索引，但還有少數情況是用到的，因此具體情況具體分析。
類似優化：
select * from tab name=’aa’or name=’bb’;
=
select * from tab name=’aa’
union all
select * from tab name=’bb’;
實測：
1、十萬數據測試，沒任何索引的情況下，上面比下面的查詢速率快一倍。
2、三十萬數據測試，aa 與 bb 都是單獨索引情況下，下面的查詢速率比 or 快一點。
1.3、避免使用 not in
not in 一般不能使用索引；主鍵字段可以
1.4、where 中盡量避免使用對 null 的判斷
1.5、like 不能前置百分號 like‘%.com’
解決：
1、若必須使用 % 前置，且數據長度不大，例如 URL，可將數據翻轉存入數據庫，再來查。LIKE REVERSE‘%.com’;
2、使用覆蓋索引
1.6、使用索引字段作為條件的時候，假若是復合索引，則應該使用索引最左邊前綴的字段名
2、將 exists 代替 in
select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)
一百萬條數據，篩選 59417 條數據用時 6.65s、4.18s。沒做其他優化，僅僅只是將 exists 替換 in。
3、字段定義是字符串，查詢時沒帶引號，不會用索引，將會進行全文掃描。
到此，相信大家對“MySQL 的查詢優化方法”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
