一、SQL優化
1、優化SQL一般步驟
1.1 查看SQL執行頻率
SHOW STATUS LIKE 'Com_%';
Com_select：執行SELECT操作的次數，一次查詢累加1。其他類似
以下參數只針對InnoDB存儲引擎，累加算法略有不同
Innodb_rows_read：SELECT查詢操作插入的行數
Innodb_rows_inserted/updated/deleted：執行INSERT/UPDATE/DELETE操作的行數
通過以上參數，可以了解當前資料庫應用是查詢為主還是寫入數據為主。
對於事務型的應用。通過Com_commit和Com_rollback可以了解事務提交和回滾的情況，對於回滾操作非常的頻繁的資料庫，可能意味著應用編寫存在問題。
基本情況了解：
Connections：試圖連接MySQL伺服器的次數。Uptime：伺服器工作時間 Slow_queries：慢查詢的次數
1.2 定位執行效率比較低的SQL語句
- 通過慢查詢日誌定位慢SQL，用--log-slow-queries[=file_name]選項啟動時，mysqld會寫一個所有執行時間超過long_query_time秒的SQL語句的日誌文件。
- 使用SHOW FULL PROCESSLIST; 查看當前MySQL在進行的線程，同時對一些鎖表操作進行優化。
1.3 通過EXPLAIN分析慢SQL
語法：EXPLAIN SQL語句
結果：
- select_type：表示SELECT的類型，常見的取值有SIMPLE(簡單表，即不使用表連接或者子查詢)、PRIMARY(主查詢，即外層的查詢)、UNION(UNION中的第二個或者後面的查詢語句)、SUBQUERY(子查詢中的第一個SELECT)等。
- table：輸出結果的表名
- type：表示MySQL在表中找到所需行的方式，或者叫訪問類型
常見的有：ALL index range ref eq_ref const,system NULL，從左到右，性能由最差到最好。
type=ALL：全表掃描。type=index：索引全掃描，MySQL遍歷整個索引來查詢。type=range：索引範圍掃描，常見於<、<=、>、 >=、 between。type=ref：使用非唯一索引掃描或唯一索引的前綴掃描，返回匹配某個單獨值的記錄。type=eq_ref：類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者unique index作為關聯條件。type=const/system：單表中最多有一個匹配行，查詢起來非常迅速，一般主鍵primary key或者唯一索引unique index進行的查詢，通過唯一索引uk_email訪問的時候，類型type為const；而從我們構造的僅有一條記錄的a表中檢索時，類型type為system。type=NULL：MySQL不用訪問表或者索引，就能直接得到結果。
類型type還有其他值，如ref_or_null(與ref類似，區別在於條件中包含對NULL的查詢)、index_merge(索引合併優化)、unique_subquery(in的後面是一個查詢主鍵欄位的子查詢)、index_subquery(與unique_subquery類似，區別在於in的後面是查詢非唯一索引欄位的子查詢)
- possible_keys：表示查詢時可能使用的索引。- key：表示實際使用的索引。- key_len：使用到索引欄位的長度。- rows：掃描行的數量- Extra：執行情況的說明和描述，包含不適合在其他列中顯示但是對執行計劃非常重要的額外信息。Using where：表示優化器除了利用索引來加速訪問之外，還需要根據索引回表查詢數據。
1.4 通過show profile分析SQL
查看當前MySQL是否支持profile
默認profiling是關閉的，可以通過set語句在Session級別開啟profiling：set profiling=1;
使用方法：
- 執行統計查詢：
- 查找上述SQL的query ID：
- 查找上述SQL執行過程中每個線程的狀態和消耗時間：
Sending data狀態表示MySQL線程開始訪問數據並行把結果返回給客戶端，而不僅僅是返回結果給客戶端。由於在Sending data狀態下，MySQL線程往往需要做大量的磁碟讀取操作，所以經常是整個查詢中耗時最長的狀態。
- 查看詳細信息並排序：
SELECT STATE, SUM(DURATION) AS TR, ROUND( 100 * SUM(DURATION) / ( SELECT SUM(DURATION) FROM information_schema.PROFILING WHERE QUERY_ID = 3), 2 ) AS PR, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS "R/Call"FROM information_schema.PROFILINGWHERE QUERY_ID = 3GROUP BY STATEORDER BY TR DESC;
進一步獲取all、cpu、block io、context switch、page faults等明細類型來查看MySQL在使用什麼資源上耗費了過高的時間，例如，選擇查看CPU的耗費時間。
此時可獲取到sending data時間主要消耗在CPU上
提示：InnoDB引擎count(*)沒有MyISAM執行速度快，就是因為InnoDB引擎經歷了Sending data狀態，存在訪問數據的過程，而MyISAM引擎的表在executing之後直接就結束查詢，完全不需要訪問數據。
2、索引問題
索引是資料庫優化中最常用也是最重要的手段之一，通過索引通常可以幫助用戶解決大多數的SQL性能問題。
2.1 存儲引擎的分類
- B-Tree索引：最常見的索引類型，大部分引擎都支持B樹索引。- HASH索引：只有Memory引擎支持。- R-Tree索引：空間索引是MyISAM的一個特殊索引類型，主要用於地理空間數據類型。- Full-text：全文索引是MyISAM的一個特殊索引類型，主要用於全文索引，InnoDB從MySQL5.6版本開始對其支持。
MySQL目前不支持函數索引，但是能對列的前面某一部分進行索引，例如標題title欄位，可以只取title的前10個字符進行索引，但是在排序Order By和分組Group By操作的時候無法使用。前綴索引創建例子：create index idx_title on film(title(10))。
常用的索引是B-Tree和Hash。Hash只有Memory/Heap引擎支持。適用於Key-Value查詢，通過Hash比B-Tree更迅速。Hash索引不使用範圍查詢。Memory/Heap引擎只有在=條件下才會使用索引。
2.2 MySQL如何使用索引
創建一個複合索引：
ALTER TABLE rental ADD INDEX idx_rental_date (rental_date, inventory_id, customer_id);
2.2.1 MySQL中能夠使用索引的典型場景
- 匹配全值，對索引中所有列都指定具體值，即是對索引中的所有列都有等值匹配條件。
比如上述創建的idx_rental_date，包含rental_date, inventory_id, customer_id，此時如果where子句中包含三者，即為全值匹配。
欄位key為idx_rental_date，表示優化器使用的是索引idx_rental_date進行掃描。
- 匹配值的範圍查詢，對索引的值能夠進行範圍查找。
類型type為range說明優化器選擇範圍查詢，索引key為idx_fk_customer_id說明優化器選擇索引idx_fk_customer_id來加速訪問。
- 匹配最左前綴，意思是在複合索引中，索引是從左邊第一個開始查找，不會跨過第一個從第二個查找，比如一個聯合索引包含(c1, c2, c3)三個欄位，可是不能被c2或者c2+c3等值查詢利用到。
添加索引：ALTER TABLE payment ADD INDEX idx_payment_date(payment_date, amount, last_update);此時第一個欄位是payment_date
如果查詢條件包含索引的第一列支付日期，能夠使用複合索引idx_payment_date進行過濾。
比如：
如果使用的是第二個支付金額不包含第一個，則不會使用索引。
比如：(此時key為空)
- 僅僅對索引進行查詢，意思是查詢的數據都在索引欄位中時，查詢的效率更高。
比如此時查詢last_update且last_update欄位被包含在索引欄位中如圖：
那麼直接訪問索引就可以獲取所需的數據，不需要通過索引回表，此時的Extra也變成了Using index，Using index指的是覆蓋索引掃描。
查詢結果：
- 匹配列前綴，僅僅使用索引中的第一列，並且只包含索引第一列的開頭一部分進行查找。
例如查找標題title是以AFRICAN開頭的電影信息。
首先創建索引：
CREATE INDEX idx_title_desc_part ON film_text (title(10), description(20));
查詢可以看到idx_title_desc_part 被使用，Using where表示優化器需要通過索引回表查詢數據：
- 匹配部分精確，其他部分範圍匹配。
指定日期，不同客戶編號
類型type為range說明優化器選擇範圍查詢，索引key為idx_rental_date說明優化器選擇索引idx_rental_date幫助加速查詢，同時所查詢的欄位在索引中，索引Extra能看見Using index。
- 列名 is null，此種情況下會使用索引。
例如：
2.2.2 存在索引但不能使用的典型場景
- 以%開頭的LIKE查詢不能利用B-Tree索引。
如下：
B-Tree索引結構，以%開頭的查詢無法利用索引，一般可使用全文索引(Fulltext)來解決類似問題。或者使用InnoDB表上的二級索引，首先獲取滿足條件的列表的id，之後再根據主鍵回表去檢索記錄。
- 數據類型出現隱式轉換不會使用索引，有些列類型是字符串，在寫where條件時，需要將常量值用引號括起來。
- 複合索引，查詢條件需要包括最左邊部分，否則不會使用複合索引。即leftmost
- MySQL執行語句時會有優化器選擇的過程，當全表掃描的代價小於索引的代價時，會使用全表掃描，所以此時需要更換一個篩選性更高的條件。
- 用or分開的條件，如果or前的列有索引，後面的沒有索引，則不會使用索引。
2.3 查看索引的使用情況
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，如果很低，說明增加索引得到的性能改善不高，因為索引並沒有被經常使用。
Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果值比較大，說明正在進行大量的表掃描，則通常說明表索引不正確或寫入的查詢沒有利用索引。
3、常用SQL優化
3.1 大批量插入數據(load)
- MyISAM
- 打開或者關閉MyISAM表非唯一索引的更新，可以提高導入效率(導入數據到非空MyISAM表)。
步驟：ALTER TABLE tab_name DISABLE KEYS; 導入數據; ALTER TABLE tab_name ENABLE KEYS;
導入數據到一個空的MyISAM表，默認是先導入數據然後才創建索引的，所以不用設置。
- InnoDB
- 因為InnoDB類型的表是按照主鍵順序保存的，所以降導入的數據按主鍵的順序排列，可以有效的提高導入數據的效率。
- 關閉唯一性校驗，SET UNIQUE_CHECKS = 0,導入結束後開啟。
- 如果使用的是自動提交的方式，在導入前使用SET AUTOCOMMIT = 0，導入結束後在恢復。
3.2 優化INSERT語句
- 同一客戶端插入很多行，應儘量使用多個值的INSERT語句。比如：INSERT INTO tab_name values(),(),()...
- 不同客戶端插入很多行，可以使用INSERT DELAYED，DELAYED含義是讓INSERT語句放置到內存的隊列中，並沒有寫入磁碟。LOW_PRIORITY是在所有其他用戶對表的讀寫完成後才進行插入。
- 將索引文件和數據文件分別放置在不同的磁碟上。
- MyISAM如果進行批量插入，增加bulk_insert_buffer_size的值。
- 從文件裝載一個表時，使用LOAD DATA INFILE，比INSERT語句快20倍。
3.3 優化ORDER BY 語句
3.3.1 MySQL排序方式
- 通過有序索引順序掃描直接返回有序數據。
在表customer上有索引idx_fk_store_id，指向欄位store_id
此時order by使用store_id排序時，Extra為Using index，不需要額外的排序，操作效率較高。
- 通過Filesort排序，所有不是通過索引直接返回排序結果的排序都叫Filesort排序。MySQL伺服器對排序參數的設置和需要排序數據的大小決定排序操作是否使用磁碟文件或臨時表。
Filesort是通過算法，將取得的數據在sort_buffer_size系統變量設置的內存排序區中進行排序，如果內存裝不下，就會將磁碟上的數據進行分塊，再對各個數據塊進行排序，然後合併。sort_buffer_size的排序區為線程獨佔，可能同時存在多個。
比如通過store_id排序所有客戶記錄時，此時為全表掃描，並且使用filesort。
一般優化方式：減少額外的排序，通過索引直接返回有序數據。儘量使WHERE條件和ORDER BY使用相同的索引，並且ORDER BY的順序和索引數據相同，並且ORDER BY的欄位都是升序或者都是降序，否則肯定會出現Filesort。
- 不會使用索引情況：
- order by的欄位混合ASC和DESC：SELECT * FROM TAB_NAME ORDER BY KEY_PART1 DESC, KEY_PART2 ASC;
- 用於查詢的關鍵字與ORDER BY 中所使用的不相同：SELECT * FROM TAB_NAME WHERE KEY2=CONSTANT ORDER BY KEY1;
- 對不同的關鍵字使用ORDER BY：SELECT * FROM TAB_NAME ORDER BY KEY1, KEY2;
3.3.2 優化Filesort
Filesort有兩種排序算法：
- 兩次掃描算法：首先根據條件取出排序欄位和行指針信息，之後在排序區sort buffer中排序。如果排序區sort buffer不夠，則在臨時表Temporary Table中存儲排序結果，完成排序後根據行指針回表讀取記錄。需要兩次訪問數據，第一次獲取排序欄位和行指針信息，第二次根據行指針獲取記錄，第二次讀取操作可能導致大量隨機I/O操作，優點是排序的時候內存開銷較少。
- 一次掃描算法：一次性取出滿足條件的行的所有欄位，然後在排序區sort buffer中排序後直接輸出結果集，排序的時候內存開銷比較大，但是排序效率比兩次掃描要高。
MySQL通過比較系統變量max_length_for_sort_data的大小和Query語句取出的欄位總大小來判斷使用哪種算法。max_length_for_sort_data大使用第二種算法，否則第一種。
適當加大系統變量max_length_for_sort_data的值，能夠讓MySQL選擇更優化的Filesort排序算法。但是過大會引起CPU利用率過低和磁碟I/O過高。
適當加大sort_buffer_size排序區，儘量讓排序在內存中完成，而不是通過創建臨時表放在文件中進行；該大小需要考慮資料庫活動連接數和伺服器內存的大小來適當設置排序區。因為這個參數是每個線程獨佔的，如果設置過大，會導致伺服器SWAP嚴重。儘量只使用必要的欄位，而不是SELECT *。
3.3.3 優化GROUP BY
默認情況下，MySQL對所有的GROUP BY欄位進行排序，如果查詢包括GROUP BY但是用戶想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。
SELECT XXX FROM XXX GROUP BY XXX ORDER BY NULL
3.3.4 優化嵌套查詢
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死。子查詢可以被更有效的連接JOIN替代。
3.3.5 優化OR條件
對於含有OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須要用到索引。
3.3.6 優化分頁查詢
一般分頁查詢時，通過創建覆蓋索引能夠比較好地提高性能，但是當分頁為1000 20時，此時會排序前1020條記錄後返回1001到1020條記錄，前1000條記錄都會被拋棄，查詢和排序的代價非常高。
- 第一種優化思路：從索引完成排序分頁的操作，最後根據主鍵關聯回原表查詢所需的其他列內容。
例如：對電影表film根據標題title排序後取某一頁數據
- 直接查詢
按照索引分頁後回表方式改寫SQL
- 第二種優化思路：把limit查詢轉換成某個位置的查詢。
假設需要查詢第100頁，則可以記錄99頁最後一行的id(倒序或者正序)，然後再次查詢時使用where取99也最後一行的id進行大於或者小於，然後在直接使用limit n即可。n為每頁顯示的行數。
比如以每頁十行查詢第100頁的數據，可以使用一下步驟：
首先查詢到第99行最後一行的id：
在通過獲取到的id取小於它的值，取10行，即為第100頁：
與直接查詢結果相比較：
explain比較：
3.3.7 使用SQL提示
- USE INDEX
提示MySQL參考使用的索引，可以讓MySQL不再考慮其他可用的索引。
比如：select count(1) from tab_name use index(index_name) where xxx; 此時查詢會用index_name所以，而忽略其他。
- IGNORE INDEX
提示MySQL忽略一個或者索引。
比如：select count(1) from tab_name ignore index (index_name); 此時查詢會忽略index_name索引。
- FORCE INDEX
強制MySQL使用某個索引，使用情況：當where子句取id>1的值，因為資料庫中大部分庫表都是大於1的，所以會全盤掃描，此時使用use index不可用，所以使用force index。
比如：select * from tab_name force index(index_name) where id > 1;
4、常用SQL技巧
4.1 正則表達式的使用
- ^ 在字符串的開始處進行匹配
匹配是否已a開頭
- $ 在字符串的末尾處進行匹配。
- . 匹配任意單個字符，包括換行符。
- [...] 匹配出括號內的任意字符。
- [^...] 不匹配[]內的任意字符
真實例子：
使用like格式如下：SELECT first_name, email FROM customer WHERE email LIKE "%@163.com" OR email LIKE "%@163,com";
4.2 利用RAND()提取隨機行
隨機抽取n條數據：SELECT * FROM tab_name ORDER BY RAND() LIMIT n;
4.3 GROUP BY的WITH ROLLUP
WITH ROLLUP可以檢索出更多的分組聚合信息。
比如查詢經手員工每日的支付金額的統計。不使用WITH ROLLUP如下：
加入 WITH ROLLUP如下：
WITH ROLLUP反映的是一種OLAP思想，可以滿足用戶想要得到的任何一個分組以及分組組合的聚合信息值。上個例子中，WITH ROLLUP幫用戶統計了每日的總金額和所有的總金額。注意：ROLLUP不能和ORDER BY使用，且limit在ROLLUP後面。
4.4 資料庫名、表名大小寫問題
由於Windows、Mac OS、Unix對庫表名已經查詢使用的大小寫敏感不一致，所以最好將庫表進行規範保存，且查詢語句也規範使用。