史上最全存儲引擎、索引使用及SQL優化的實踐

史上最全存儲引擎、索引使用及SQL優化的實踐

• 1 MySQL的體繫結構概述
• 2. 存儲引擎
• 2.1 存儲引擎概述
• 2.2 各種存儲引擎特性
• 2.2.1 InnoDB
• 2.2.2 MyISAM
• 3. 優化SQL步驟
• 3.1 查看SQL執行頻率
• 3.2 定位低效率執行SQL
• 3.3 explain分析執行計劃
• 3.3.2 explain 之id
• 3.3.3 explain 之 select_type
• 3.3.4 explain 之 table
• 3.3.5 explain 之 type
• 3.3.6 explain 之key
• 3.3.7 explain 之 rows
• 3.3.8 explain 之 extra
• 3.4 show profile分析SQL
• 3.5 trace分析優化器執行計劃
• 4. 索引的使用
• 4.1 驗證索引提升查詢效率
• 4.1.2 避免索引失效
• 4.3 查看索引使用情況
• 5. SQL優化
• 5.1 大批量插入數據
• 5.2 優化insert語句
• 5.3 優化order by語句
• 5.3.2 兩種排序方式
• 5.3.3 FileSort 的優化
• 5.4 優化group by 語句
• 5.5 優化嵌套查詢
• 5.6 優化OR條件
• 5.7 優化分頁查詢
• 5.7.1 優化思路一
• 5.7.2 優化思路二
• 5.8 使用SQL提示
• 5.8.1 USE INDEX
• 5.8.2 IGNORE INDEX
• 5.8.3 FORCE INDEX

1 MySQL的體繫結構概述

整個MySQL Server由以下組成 ：
Connection Pool ：連接池組件
Management Services & Utilities ：管理服務和工具組件
SQL Interface ：SQL介面組件
Parser ：查詢分析器組件
Optimizer ：優化器組件
Caches & Buffers ：緩衝池組件
Pluggable Storage Engines ：存儲引擎
File System ：文件系統
1）連接層
最上層是一些客戶端和鏈接服務，包含本地sock通信和大多數基於客戶端/服務端工具實現的類似於TCP/IP的通信。主要完成一些類似於連接處理、授權認證、及相關的安全方案。在該層上引入了線程池的概念，為通過認證安全接入的客戶端提供線程。同樣在該層上可以實現基於SSL的安全鏈接。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
2）服務層
第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成緩存的查詢，SQL的分析和優化，部分內置函數的執行。所有跨存儲引擎的功能也在這一層實現，如過程、函數等。在該層，伺服器會解析查詢並創建相應的內部解析樹，並對其完成相應的優化如確定表的查詢的順序，是否利用索引等，最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環境中能夠很好的提升系統的性能。
3）引擎層
存儲引擎層，存儲引擎真正的負責了MySQL中數據的存儲和提取，伺服器通過API和存儲引擎進行通信。不同的存儲引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的存儲引擎。
4）存儲層
數據存儲層，主要是將數據存儲在文件系統之上，並完成與存儲引擎的交互。

和其他資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要體現在存儲引擎上，插件式的存儲引擎架構，將查詢處理和其他的系統任務以及數據的存儲提取分離。這種架構可以根據業務的需求和實際需要選擇合適的存儲引擎。

2. 存儲引擎

2.1 存儲引擎概述

	和大多數的資料庫不同，MySQL中有一個存儲引擎的概念，針對不同的存儲需求可以選擇最優的存儲引擎。
存儲引擎就是存儲數據，建立索引，更新查詢數據等等技術的實現方式。存儲引擎是基於表，而不是基於庫的。所以存儲引擎也可被稱為表類型。
	Oracle、SqlServer等資料庫只有一種存儲引擎。MySQL提供插件式的存儲引擎架構。所以MySQL存在多種存儲引擎，
可以根據需要使用相應的引擎，或者編寫存儲引擎。
	MySQL5.0支持的存儲引擎包含 ：InooDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、
CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事物安全表，其他存儲引擎是非事物安全表。
可以通過指定show engines，來查詢當前資料庫支持的存儲引擎 ：	

創建新表時如果不指定存儲引擎，那麼系統就會使用預設的存儲引擎，MySQL5.5之前的預設存儲引擎是MyISAM，5.5之後就改為了InnoDB。
查看MySQL資料庫預設的存儲引擎 ，指令 ：
show variables like ‘%storage_engine%’;

2.2 各種存儲引擎特性

下麵重點介紹幾種常用的存儲引擎，並對比各個存儲引擎之間的區別，如下表所示 ：

2.2.1 InnoDB

InnoDB存儲引擎是MySQL的預設存儲引擎。InnoDB存儲引擎提供了具有提交、回滾、崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，
InnoDB寫的處理效率差一些，並且會占用更多的磁碟空間以保留數據和索引。
InnoDB存儲引擎不同於其他存儲引擎的特點 ：
事務控制

測試發現InnoDB中是存在事務的。
外鍵約束
	MySQL支持外鍵的存儲引擎只有InnoDB，在創建外鍵的時候，要求父表必須有對應的索引，子表在創建外鍵的時候，也會自動的創建
	對應的索引。
下麵是兩張表中，country_innodb是父表，country_id為主鍵索引，city_innodb表是子表，country_id欄位為外鍵，對應於
country_innodb表的主鍵country_id

在創建索引時，可以指定在刪除、更新父表時，對子表進行的相應操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。
RESTRICT和NO ACTION相同，是指限制在子表有關聯記錄的情況下，父表不能更新；
CASCADE表示父表在更新或者刪除時，更新或者刪除子表對應的記錄；
SET NULL則表示父表在更新或者刪除的時候，子表的對應欄位被SET NULL.
針對上面創建的兩個表，子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的，那麼在主表刪除記錄的時候，如果子表有對應記錄，則不允許刪除，主表在更新記錄的時候，如果子表有對應的記錄，則子表對應更新。
ON DELETE RESTRICT ----> 刪除主表數據時，如果有關聯記錄，不刪除；
ON UPDATE CASCADE ----> 更新主表時，如果子表有關聯記錄，更新子表記錄。
表中數據如下圖所示 ：

存放方式
InnoDB存儲表和索引有以下兩種方式 ：
（1）使用共用表空間存儲，這種方式創建的表的表結構保存在.frm文件中，數據和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個文件。
（2）使用躲表空間存儲，這種方式創建的表的表結構任然存在.frm文件中，但是每個表的數據和索引單獨保存在.ibd中。

2.2.2 MyISAM

MyISAM不支持事務、也不支持外鍵，其優勢是訪問速度快，對事物的完整性沒有要求或者以SELECT、INSERT為主的應用基本上都可以使用這個
引擎來創建表。有以下兩個比較重要的特點 ：

不支持事務

通過測試，我們發現，在MyISAM存儲引擎中，是沒有事務控制的。

3. 優化SQL步驟

3.1 查看SQL執行頻率

MySQL客戶端連接成功後，通過show [session | global] status命令可以提高伺服器狀態信息。show [session | global] status 可以根據需要加上參數 “session”或者“global”來顯示session級（當前連接）的計結果和global級（自資料庫上次啟動至今）的統計結果。如果不寫，預設使用參數是“session”。
下麵的命令顯示了當前session中所有統計參數的值 ：
show status like “Com_”;

show status like ‘Innodb_rows_%’;

Com_xxx表示每個xxx語句執行的次數，我能通常比較關心的是以下幾個統計參數

Com_*** : 這些參數對於所有存儲引擎的表操作都會進行累計。
Innodb_*** ：這幾個參數只是針對InooDB存儲引擎，累加的演算法也略有不同。

3.2 定位低效率執行SQL

可以通過以下兩種方式定位執行效率較低的SQL語句。
1）慢查詢日誌 ：通過慢查詢日誌定位那些執行效率較低的SQL語句，用–log-slow-queries[=file_name]選型啟動時，mysqld寫一個包含索引執行時間超過long_query_time秒的SQL語句的日誌文件。
2）show processlist : 慢查詢日誌在查詢結束以後才記錄，所以在應用反映執行效率出現問題的時候查詢慢查詢日誌並不能定位問題，可以使用 show processlist 查看當前MySQL在進行的線程，包括線程的狀態、是否鎖表等，可以實時地查看SQL的執行情況，同時對一些鎖表操作進行優化。

1）id列，用戶登錄mysql時，系統分配的"connection_id"，可以使用函數connection_id()查看
2）user列，顯示當前用戶。如果不是root，這個命令就只顯示用戶許可權範圍的sql語句
3）host列，顯示這個語句是從哪個ip的哪個埠上發的，可以用來跟蹤出現問題語句的用戶
4）db列，顯示這個進程目前連接的哪個資料庫
5）command列，顯示當前連接的執行的命令，一般取值為休眠（sleep），查詢（query），連接（connect）等
6）time列，顯示這個狀態持續的時間，單位是秒
7）state列，顯示使用當前連接的sql語句的狀態，很重要的列。state描述的是語句執行中的某一個狀態。一個sql語句，查詢為例，可能需要經過copying to tmp table、sorting result、sending data等狀態才可以完成。
8）info列，顯示這個sql語句，是判斷問題語句的一個重要依據

3.3 explain分析執行計劃

通過以上步驟查詢到效率低的SQL語句後，可以通過EXPLAIN或者DESC命令獲取MySQL如何執行SELECT語句的信息，包括在SELECT語句執行過程中表如何連接和連接的順序。
查詢SQL語句的執行計劃 ：
explain select * from tb_item where id = 1;

explain select * from tb_item where title = ‘阿爾卡特（ot-979）冰川白 聯通3G手機3’;

3.3.2 explain 之id

id欄位是select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序。id情況有三種 ：
1）id相同表示載入表的順序是從上到下。
explain select * from t_role r,t_user u,user_role ur where r.id = ur.role_id and u.id = ur.user_id;

2）id不同id值越大，優先順序越高，越先被執行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = ‘stul’))

3）id有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先順序越高，優先執行。
EXPLAIN SELECT * FROM t_role r,(SELECT * FROM user_role ur WHERE ur.‘user_id’ = ‘2’) a WHERE r.id = a.role_id;

3.3.3 explain 之 select_type

表示SELECT的類型，常見的取值，如下表所示 ：
SIMPLE ：簡單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY ：查詢中若包含任何複雜的子查詢，最外層查詢標記為該標識
SUBQUERY ：在SELECT或WHERE列表中包含了子查詢
DERIVED ：在FROM列表中包含的子查詢，被標記為DERIVED（衍生）MySQL會遞歸執行這些子查詢，把結果放在臨時表中
UNION ：若第二個SELECT出現在UNION之後，則標記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為 ：DERIVED
UNION RESULT ：從UNION表獲取結果的SELECT

3.3.4 explain 之 table

展示這一行的數據是關於哪一張表的

3.3.5 explain 之 type

type顯示的是訪問類型，是較為重要的一個指標，可取值為 ：
NULL ：MySQL不訪問任何表，索引，直接返回結果
system ：表只有一行記錄（等於系統表），這是const類型的特例，一般不會出現
const ：表示通過索引一次就找到了，const用於比較primary key（主鍵）或者unique（唯一）索引。因為只匹配一行數據，所以很快。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常亮。const於將“主鍵”或“唯一”索引的所有部分與常量值進行比較。
eq_ref ：類似ref，區域在於使用的是唯一索引，使用主鍵的關聯查詢，關聯查詢出的記錄只有一條。常見於主鍵或唯一索引掃描
ref ：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，返回所有匹配某個單獨值的所有行（多個）
range ：只檢索給定返回的行，使用一個索引來選擇行。where之後出現between，<，>，in等操作。
index ：index與ALL的區別為 index類型只是遍歷了索引樹，通常比ALL快，ALL是遍曆數據文件。
all ：將遍歷全表以找到匹配的行
結果值從最好到最壞依次是 ：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
一般來說，我們需要保證查詢至少達到range基本，最好達到ref。

3.3.6 explain 之key

possible_keys : 顯示可能應用在這張表的索引，一個或多個。
key ：實際使用的索引，如果為null，則沒有使用索引。
key_len : 表示索引中使用的位元組數，該值為索引欄位最大可能長度，並非實際使用長度，在不損失精確性的前提下，長度越短越好。

3.3.7 explain 之 rows

掃描行的數量

3.3.8 explain 之 extra

其他的額外的執行計劃信息，在該列展示。
using filesort : 說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，稱為“文件排序”。效率低
using temporary ：使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見於order by 和group by。效率低
using index ：表示相應的select操作使用了覆蓋索引，避免訪問表的數據行，效率不錯。
當extra列出現using filesort和using temporary時就需要進行sql優化了。
還有一個問題，很多人認為當出現效率低的情況，加索引，一味的加索引就認為能解決問題？實際上這種想法是錯誤的，索引不是想加就加的，每個索引都需要深思熟慮過的，不是因為業務需要而去加索引，這是一種錯誤的做法。索引是為了提升獲取資料庫數據的獲取效率而加的。而業務的需要可以用其他方式去實現。比如排序，很多人第一時間想到資料庫order by去排序，而需要排序的欄位又是一些特殊的欄位。我不認為這個時候去加索引是一種很好解決方案，可以嘗試使用ES。

3.4 show profile分析SQL

MySQL從5.0.37版本開始增加了對 show profiles和show profile語句的支持。show profiles能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去了。
通過have_profiling參數，能夠看到當前MySQL是否支持profile；

預設profiling是關閉的，可以通過set語句在Session級別開啟profiling；

set profiling=1; // 開啟profiling開關；
通過profile，我們能夠更清楚地瞭解SQL執行的過程。
首先，我們可以執行一系列的操作，如下圖所示 ：
show databasesl
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
執行完上述命令之後，再執行show profiles指令，來查看SQL語句執行的耗時 ：

通過show profile for query query_id 語句可以查看到該SQL執行過程中每個線程的狀態和消耗的時間 ：

TIP :
Sending data 狀態表示MySQL線程開始訪問數據行並把結果返回給客戶端，而不僅僅是返回個客戶端。由於在Sending data狀態下，MySQL線程往往需要做大量的磁碟讀取操作，所以經常是整個查詢中耗時最長的狀態。
在獲取到最消耗時間的線程狀態後，MySQL支持進一步選擇all、cpu、block io、context switch、page faults等明細類型查看MySQL在使用什麼資源上耗費了過高的時間。例如，選擇查看CPU的耗費時間 ：

show PROFILE ALL for QUERY 58; 可以查看到所有的狀態耗時。

3.5 trace分析優化器執行計劃

MySQL5.6提供了對SQL的跟蹤trace，通過trace文件能夠進一步瞭解為什麼優化器選擇A計劃，而不是選擇B計劃。
打開trace，設置格式為JSON，並設置trace最大能夠使用的記憶體大小，避免解析過程中因為預設記憶體過小而不能夠完整展示。
set optimizer_trace=“enabled=on”,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
執行SQL語句 ：
select * from tb_item where id < 4;
最後，檢查information_schema.optimizer_trace就可以知道MySQL是如何執行SQL的 ：
select * from information_schema.optimizer_trace\G;

開源的小米SQL優化工具SOAR可以瞭解一下。

4. 索引的使用

索引是資料庫優化最常用也是最重要的手段之一，通過索引通常可以幫助用戶解決大多數的MySQL的性能優化問題。

4.1 驗證索引提升查詢效率

在表中存儲了300萬條記錄；
A. 根據ID查詢
select * from tb_item where id = 1999;

查詢速度很快，接近0秒，主要的原因是因為id 為主鍵，有索引；
如果查詢條件沒有索引那麼查詢效率會很低。

4.1.2 避免索引失效

1）.全值匹配，對索引中所有列都指定具體值。
改情況，索引生效，執行效率高。
explain select * from tb_seller where name = ‘小米科技’ and status = ‘1’ and address = ‘北京市’;

2）.最左首碼法則
如果索引多列，要遵守最左首碼法則。指的是查詢從索引的最左前列開始，並且不能跳過創建複合索引中的中間列。
匹配最左首碼法則，走索引 ：
如下創建複合索引的列是name、status、address，那麼查詢的時候就必需要有name列，不能跳過中間的status直接去查詢address，因為這樣索引會失效。（where條件後面的and順序可以和複合索引的添加順序不一致）

explain select * from tb_seller where name=‘小米科技’ and address=‘北京市’;
這個時候雖然走了索引，但是只是name欄位走列索引，address沒有走索引（看key_len只有403，而name索引長度就是403）。所以可以無序但是不能跳過status欄位進行查詢，否則索引無效。

3）.範圍查詢右邊的列，不能使用索引。
根據前面的兩個欄位name，status查詢是走索引的，但最後一個條件address沒有用到索引。
4）.不要在索引列上進行運算操作，索引將失效。

5）.字元串不加單引號，造成索引失效。

由於，在查詢中，沒有對字元串加單引號，MySQL的查詢優化器，會自動的進行類型轉換，造成索引失效。
6）.儘量使用覆蓋索引，避免select *
儘量使用覆蓋索引（只訪問索引的查詢（索引列完全包含查詢列）），減少select *。

如果查詢列，超出索引列，也會降低性能。

TIP ：
using index : 使用覆蓋索引的時候就會出現
using where ：在查找使用索引的情況下，需要回表去查詢所需的數據
using index condition ：查找使用列索引，但是需要回表查詢數據
using index ；using where ：查找使用了索引，但是需要的數據都在索引列中能找到，所以不需要回表查詢數據。
7）用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
示例 ：name欄位是索引列，而createtime不是索引列，中間是or進行連接是不走索引的 ：
explain select * from tb_seller where name=‘姓名’ or createtime = ‘2018-01-01’；

8）以%開頭的Like模糊查詢，索引失效。
如果僅僅是尾部模糊匹配，索引不會失效。如果是頭部模糊匹配，索引失效。

解決方案 ：通過覆蓋索引來解決；如下圖所示 ：

9）如果MySQL評估使用索引比全表更慢，則不使用索引。
例如所查詢欄位的address有11行是北京市，只有一行是西安市。那麼MySQL的底層在進行了優化，執行器會進行判斷走索引不如全表查詢速度更快，所以就算給address添加索引，MySQL的評估也會使用全表查詢。

10）is NULL，is NOT NULL 有時索引失效。
主要是因為所查詢列的數據是以null為主，還是以not null為主。如果以null為主那麼where後面查詢列是is not null時，MySQL底層會判斷走索引更快一些，如果以not null為主那麼where後面查詢列是is not null時，MySQL底層會判斷走全表會更快一些。反之亦然。

11）in走索引，not in索引失效。

12）單列索引和複合索引
	儘量使用複合索引，而少使用單列索引。
	創建複合索引 ：
	create index idx_name_sta_address on tb_seller(name,status,address);
	就相當於創建三個索引 ：
		name
		name + status
		name + status + address
	創建單列索引
		create index idx_seller_name on tb_seller(name);
		create index idx_seller_status on tb_seller(status);
		create index idx_seller_address on tb_seller(address);
	資料庫會選擇一個最油的索引來使用，並不會使用全部索引。		

4.3 查看索引使用情況

show status like ‘Handler_read%’;
show global status like ‘Handler_read%’;

Handler_read_first : 索引中第一條被讀的次數。如果較高，表示伺服器正執行大量全索引掃描（這個值越低越好）。
Handler_read_key : 如果索引正在工作，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好）。
Handler_read_next : 按照鍵順序讀下一行的請求數。如果你用範圍約束或如果執行索引掃描來查詢索引列，該值增加。
Handler_read_prev : 按照鍵順序讀取前一行的請求數。該讀方法主要用於優化ORDER BY … DESC。
Handler_read_rnd : 根據固定位置讀一行的請求數。如果你正執行大量查詢並需要對結果進行排序該值較高。你可能使用了大量需要MySQL掃描正整個表的查詢或你的連接沒有正確使用鍵。這個值較高，意味著運行效率低，應該建立索引來補救。
Handler_read_rnd_next : 在數據文件中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明你的表索引不正確或寫入的查詢沒有利用索引。

5. SQL優化

5.1 大批量插入數據

當使用load命令導入數據的時候，適當的設置可以提高導入的效率。

對於InnoDB類型的表，有以下幾種方式可以提高導入的效率 ：
1）主鍵順序插入
因為InnoDB類型的表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順序排列，可以有效的提高導入數據的效率。如果InnoDB表沒有主鍵，那麼系統會自動預設創建一個內部列做為主鍵，所以如果可以給表創建一個主鍵，將可以利用這點，來提高導入數據的效率。
插入ID順序排列的數據 ：

下圖是有序數據導入的時間

下圖是無序數據導入的時間

2） 關閉唯一性校驗
在導入數據前執行SET_UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束後執行SET_UNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。

3）手動提交事務
如果應用使用自動提交的方式，建議在導入前執行SET AUTOCOMMIT=0，關閉自動提交，導入結束後再執行SET AUTOCOMMIT=1，打開自動提交，也可以提高導入的效率。

5.2 優化insert語句

當進行數據的insert操作的時候，可以考慮採用以下幾種優化方案。
如果需要同時對一張表插入很多行數據時，應該儘量使用多個值表的insert語句；這種方式將大大的縮減客戶端與資料庫之間的連接、關閉等消耗。使得效率比分開執行的單個insert語句快。
示例，原始方式為 ：

優化後的方案為 ：

在事務中進行數據插入。

5.3 優化order by語句

5.3.2 兩種排序方式

1）第一種是通過對返回數據進行排序，也就是通常說的filesort排序，所有不是通過索引直接返回排序結果的排序都叫FileSort排序。

2）第二種通過有序索引順序掃描直接返回有序數據，這種情況既為using index，不需要額外排序，操心效率高。

多欄位排序
1）order by多欄位時，要麼全部升序，要麼全部降序。並且排序欄位使用複合索引欄位。
2）當為多欄位排序時，排序欄位順序要和創建複合索引的欄位順序保持一致。


瞭解MySQL的排序方式，優化目標就清晰了 ：
儘量減少額外的排序，通過索引直接返回有序數據。where條件和order by使用相同的索引，並且order by的順序和索引順序相同，並且order by的欄位都是升序，或者都是降序。否則肯定需要額外的操作，這樣就會出現FileSort。

5.3.3 FileSort 的優化

通過創建合適的索引，能夠減少FileSort的出現，但是在某些情況下，條件限制不能讓FileSort消失，那就需要加快FileSort的排序操作。對於FileSort，MySQL有兩種排序演算法 ：
1）兩次掃描演算法 ：MySQL4.1之前，使用該方式排序。首先根據條件取出排序欄位和行指針信息，然後在排序區sort buffer中排序，如果sort buffer不夠，則在臨時表temporary table中存儲排序結果。完成排序之後，再根據行指針回表讀取記錄，該操作可能會導致大量隨機I/O操作。
2）一次掃描演算法 ：一次性取出滿足條件的所有欄位，然後在排序區sort buffer中排序後直接輸出結果集。排序時記憶體開銷較大，但是排序效率比兩次掃描演算法要高。
MySQL通過比較系統變數max_length_for_sort_data的大小和Query語句取出的欄位總大小，來判定是否那種排序演算法，如果max_length_sort_data更大，那麼使用第二種優化之後的演算法 ：否則使用第一種。
可以適當提高sort_buffer_size和max_length_for_sort_data系統變數，來增大排序區的大小，提高排序的效率。

5.4 優化group by 語句

由於GROUP BY實際上也同樣會進行排序操作，而且與ORDER BY相比，GROUP BY主要只是多了排序之後的分組操作。當然，如果在分組的時候還使用來其他的一些聚合函數，那麼還需要一些聚合函數的計算。所以，在GROUP BY的實現過程中，與ORDER BY一樣也可以利用索引。
如果查詢包含group by但是用戶想要避免排序結果的消耗，則可以執行oerder by null禁止排序。如下 ：
drop index inx_emp_age_salary on emp;
explain select age,count() from emp group by age;

優化後
explain select age,count() from emp group by age order by null;

從上面的例子可以看出，第一個SQL語句需要進行“filesort”，而第二SQL由於order by null不需要進行“filesort”，而上文提過FileSort往往非常耗費時間。
也可以通過創建索引提高分組列的效率
create index idx_emp_age_salary on emp(age,salary);

5.5 優化嵌套查詢

MySQL4.1版本之後，開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表死鎖，並且寫起來也很容易。但是，有些情況下，子查詢是可以被更搞笑的連接（JOIN）替代。
示例 ：查找有角色的所有的用戶信息 ：
explain select * from t_user where id in (select user_id user_role);
執行計劃為 ：

優化後 ：
explain select * from t_user u, user_role ur where u.id = ur.user_id;

5.6 優化OR條件

對於包含OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須使用到索引，而且不能使用到複合索引；如果沒有索引，則應該考慮增加索引。
獲取emp表的所有索引 ：

示例 ：
explain select * from emp where id = 1 or age = 30；


建議使用union 替換or ：
這裡的type中const要性能遠高於range


我們來比較下重要指標，發現主要差別是type和ref這兩項
type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是 ：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL
UNION語句的type值為const，OR語句的type值為range，可以看到這是一個很明顯的差距。
UNION語句的ref值為const，OR語句的type值為null，const表示是常量值引用，非常快這兩項的差距就說明來
UNION要優於OR。

5.7 優化分頁查詢

一般分頁查詢時，通過創建覆蓋索引能夠比較好的提高性能。一個常見又非常頭痛的問題就是 limit 20000000，10，此時需要MySQL排序前2000010記錄，僅僅返回2000000 - 2000010的記錄，其他記錄丟棄，查詢排序的代價非常大。

5.7.1 優化思路一

在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容。

兩個SQL的執行計劃如下

5.7.2 優化思路二

該方案適用於主鍵自增的表（不能出現主鍵斷層），可以把limit查詢轉換成某個位置的查詢。

5.8 使用SQL提示

SQL提示，是優化資料庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示來達到優化操作的目的。

5.8.1 USE INDEX

在查詢語句中表名的後面，添加use index來提供希望MySQL去參考的索引列表，就可以讓MySQL不再考慮其他可用的索引。
create index idex_seller_name on tb_seller(name);

5.8.2 IGNORE INDEX

如果用戶只是單純的想讓MySQL忽略一個或者多個索引，則可以使用ignore index作為hint
explain select * from tb_seller ignore index(idx_seller_name) where name = ‘小米科技’;

5.8.3 FORCE INDEX

為強制MySQL使用一個特定的索引，可在查詢中使用force index作為hint。
create index inx_seller_address on tb_seller(address);