# 前言 接下來是查詢優化，用戶80%的操作基本都在查詢，我們有什么理由不去優化他呢？？所以我們將會講解大量的查詢優化（索引以及庫表結構優化等高級用法后面再講），先講單表查優化，再講多表查優化。 # 明確搜索優化的整體思路以及查詢優化的因素 ## 搜索優化的整體思路 索引優化，查詢優化，查詢緩存，服務器設置優化，操作系統和硬件優化，應用層面優化（web服務器，緩存）等等。對于一個整體項目而言只有這些齊頭并進，才能實現mysql高性能。 ## 查詢優化的因素思路 #### 是否向數據庫請求了不需要的數據。 也就是說不要輕易使用select * from ，能明確多少數據就查多少個 #### mysql是否掃描額外的紀錄 查詢是否掃描了過多的數據。 最簡單的衡量查詢開銷三個指標如下： * 響應時間； * 掃描的行數； * 返回的行數。 沒有哪個指標能夠完美地衡量查詢的開銷，但它們大致反映了mysql在內部執行查詢時需要多少數據，并可以推算出查詢運行的時間。 這三個指標都會記錄到mysql的慢日志中，所以檢查慢日志記錄是找出掃描行數過多的查詢的好辦法。 響應時間是兩個部分之和：服務時間和排隊時間。 服務時間是指數據庫處理這個查詢真正花了多長時間。 排隊時間是指服務器因為等待某些資源而沒有真正執行查詢的時間。 —可能是等io操作完成，也可能是等待行鎖，等等。 掃描的行數和返回的行數：分析查詢時，查看該查詢掃描的行數是非常有幫助的。這在一定程度上能夠說明該查詢找到需要的數據的效率高不高。 掃描的行數和訪問類型：　在expain語句中的type列反應了訪問類型。 訪問類型有很多種，從全表掃描（ALL）到索引掃描（index）到范圍掃描（INDEX RANGE SCAN）到唯一索引查詢到常數引用等。這里列的這些，速度由慢到快，掃描的行數也是從小到大。 如果發現查詢需要掃描大量的數據但只返回少數的行，那么通常可以嘗試下面的技巧去優化它： 1. 使用索引覆蓋掃描。 2. 改變庫表結構。例如使用單獨的匯總表。 3. 重寫這個復雜的查詢。讓mysql優化器能夠以更優化的方式執行這個查詢。 #### 查詢方式 1. 一個復雜查詢 or 多個簡單查詢 設計查詢的時候一個需要考慮的重要問題是，是否需要將一個復雜的查詢分成多個簡單的查詢。 2. 切分查詢 有時候對于一個大查詢我們需要“分而治之”，將大查詢切分為小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。 3. 分解關聯查詢 ~~~ select * from tag 　　　　　　　join tag_post on tag_post.tag_id = tag.id 　　　　　　　join post on tag_post.post_id = post.id 　　　　where tag.tag = 'mysql' 　　　　可以分解成下面這些查詢來代替： 　　　　> select * from tag where tag = 'mysql' 　　　　> select * from tag_post where tag_id = 1234 　　　　> select * from post where post_id in (123, 456, 567, 9098, 8904)　 ~~~ 分解關聯查詢讓緩存的效率更高。 將查詢分解后，執行單個查詢可以減少鎖的競爭。 在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展。 查詢本身效率也可能會有所提升。 可以減少冗余記錄的查詢， 更進一步，這樣做相當于在應用中實現了哈希關聯，而不是使用mysql的嵌套循環關聯。 4. 設計查詢的流程(后端) 客戶端發送一條查詢給服務器 服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果，否則進入下一階段。 服務器進行SQL解析，預處理，再由優化器生成對應的執行計劃， mysql根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。 將結果返回給客戶端。 # 優化查詢前的說明 ## 查看MySQL整體狀態： ~~~ show status; ——顯示狀態信息（擴展show status like ‘XXX’） show variables ——顯示系統變量（擴展show variables like ‘XXX’） show ENGINE INNODB status; ——顯示InnoDB存儲引擎的狀態 show processlist ——查看當前SQL執行，包括執行狀態、是否鎖表等 mysqladmin variables -u username -p password——顯示系統變量 mysqladmin extended-status -u username -p password——顯示狀態信息 mysqld –verbose –help [|more #逐行顯示] 查看狀態變量及幫助 ~~~ ## 開啟慢查詢日志 1. 在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個配置參數 log-slow-queries={自己想存放的日志路徑}/slow-query.log long_query_time=2 *注*：log-slow-queries參數為慢查詢日志存放的位置，一般這個目錄要有mysql的運行帳號的可寫權限，一般都將這個目錄設置為mysql的數據存放目錄； long_query_time=2中的2表示查詢超過兩秒才記錄； 在my.cnf或者my.ini中添加log-queries-not-using-indexes參數，表示記錄下沒有使用索引的查詢。 log-slow-queries=/data/mysqldata/slow-query.log long_query_time=10 log-queries-not-using-indexes 2. 查看日志啟動狀態：show variables like “slow%”; 3. 設置慢日志開啟: set global slow_query_log = ON; 4. 查詢long_query_time 的值 ： show variables like “long%”; 5. 為了方便測試，可以將修改慢查詢時間為1秒。（小點容易比較，畢竟mysql處理那么快） 6. 以后就往我們設置的日志路徑去訪問日志即可 ## explain查詢分析 使用 EXPLAIN 關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。通過explain命令可以得到: * 表的讀取順序 * 數據讀取操作的操作類型 * 哪些索引可以使用 * 哪些索引被實際使用 * 表之間的引用 * 每張表有多少行被優化器查詢 ### EXPLAIN查詢出來的字段解析： 1）Table：顯示這一行的數據是關于哪張表的 2）possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句。 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用，因為MySQL內部優化器有自己的抉擇。 該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。 如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引并且再次用EXPLAIN檢查查詢 3）key：實際使用的索引。如果為NULL，則沒有使用索引。MYSQL很少會選擇優化不足的索引，此時可以在SELECT語句中使用USE INDEX（index）來強制使用一個索引或者用IGNORE INDEX（index）來強制忽略索引 4）key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好 表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的） 不損失精確性的情況下，長度越短越好 5）ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。 6）rows：MySQL認為必須檢索的用來返回請求數據的行數 表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數 7）select_type：查詢中每個select子句的類型 (1) SIMPLE(簡單SELECT,不使用UNION或子查詢等) (2) PRIMARY(查詢中若包含任何復雜的子部分,最外層的select被標記為PRIMARY) (3) UNION(UNION中的第二個或后面的SELECT語句) (4) DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢) (5) UNION RESULT(UNION的結果) (6) SUBQUERY(子查詢中的第一個SELECT) (7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢) (8) DERIVED(派生表的SELECT, FROM子句的子查詢) (9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外鏈接的第一行) 8）type：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為NULL、system、const、eq_ref、ref、range、index和ALL NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。 system、const：可以將查詢的變量轉為常量. 如id=1; id為 主鍵或唯一鍵。當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量,system是const類型的特例，當查詢的表只有一行的情況下，使用system eq_ref：訪問索引,返回某單一行的數據.(通常在聯接時出現，查詢使用的索引為主鍵或惟一鍵)。類似ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關聯條件 ref：訪問索引,返回某個值的數據.(可以返回多行) 通常使用=時發生。表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值 range：這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西，并且該字段上建有索引時發生的情況(注:不一定好于index)。只檢索給定范圍的行，使用一個索引來選擇行。 index：以索引的順序進行全表掃描，優點是不用排序,缺點是還要全表掃描。index與ALL區別為index類型只遍歷索引樹 ALL：全表掃描，應該盡量避免。 MySQL將遍歷全表以找到匹配的行。 9）Extra：關于MYSQL如何解析查詢的額外信息，主要有以下幾種 using index：只用到索引,可以避免訪問表.。表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯 using where：使用到where來過慮數據. 不是所有的where clause都要顯示using where. 如以=方式訪問索引. using tmporary：查詢有使用臨時表, 一般出現于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優化. using filesort：用到額外的排序. (當使用order by v1,而沒用到索引時,就會使用額外的排序)。MySQL中無法利用索引完成的排序操作稱為“文件排序” range checked for eache record(index map:N)：沒有好的索引. Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。 Impossible where：這個值強調了where語句會導致沒有符合條件的行。 # profiling查詢分析 通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態。 如果覺得explain的信息不夠詳細，可以同通過profiling命令得到更準確的SQL執行消耗系統資源的信息。 profiling默認是關閉的。可以通過以下語句查看： select @@profiling; 打開profiling查詢分析：set profiling = 1; 然后我們隨便寫幾條select語句，再查看：show profiles\G; show profiles\G; 可以得到被執行的SQL語句的時間和ID show profile for query 1; 得到對應SQL語句執行的詳細信息 ~~~ Show Profile命令格式： SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type參數： |ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS ~~~ 測試完畢以后 ，關閉參數：mysql> set profiling=0 # 單表查詢步步優化 ~~~ //最傻的查詢方式 select * from tables ~~~ ## 明確需要的字段，要多少就寫多少字段： ~~~ select d.Good_ID , d.Classify_ID, d.Good_Name, d.Monthsale_Num, d.Store_Name, d.Comment_Num, d.Good_Brand, d.Ishas_License, ifnull(d.Good_Hot,0), d.Good_Price, d.Store_Add, d.Store_Age, d.Seller_Credit, d.Classify_Description from Commodity_list d; ~~~ ## 使用分頁語句：limit start , count 或者條件 where子句 有什么可限制的條件盡量加上，查一條就limit一條。做到不多拿不亂拿。 明確子句的執行順序先： ~~~ SELECT select_list　　　 FROM table_name　　 [ WHERE search_condition ]　　 [ GROUP BY group_by_expression ]　　 [ HAVING search_condition ]　　 [ ORDER BY order_expression [ ASC | DESC ] ] [limit m,n] ~~~ **例子：** ~~~ select d.Good_ID , d.Classify_ID, d.Good_Name, d.Monthsale_Num, d.Store_Name, d.Comment_Num, d.Good_Brand, d.Ishas_License, ifnull(d.Good_Hot,0), d.Good_Price, d.Store_Add, d.Store_Age, d.Seller_Credit, d.Classify_Description from Commodity_list d where Classify_ID=23 limit 1,1000 ; ~~~ 補充： 1）limit語句的查詢時間與起始記錄的位置成正比 2）mysql的limit語句是很方便，但是對記錄很多的表并不適合直接使用。 對limit分頁性能優化分析： 偏移量越大，查詢越費時。 原因： * 每條數據的實際存儲長度不一樣（所以必須要依次遍歷，不能直接跳過前面的一部分） * 哪怕是每條數據存儲長度一樣，如果之前有過delete操作，那索引上的排列就有gap * 所以數據不是定長存儲，不能像數組那樣用index來訪問，只能依次遍歷，就導致偏移量越大查詢越費時 對limit的使用再優化 ： 利用自增主鍵，避免offset的使用（演示在積分表score，商品表設計得不太好），約是上面方法的1/3時間。 ~~~ select * from score WHERE id>0 LIMIT 10000 ; select * from score WHERE id>10000 LIMIT 10000 ; select * from score WHERE id>20000 LIMIT 10000 ; ~~~ ## 如果是有序的查詢，可使用ORDER BY ~~~ select * from score WHERE id>0 ORDER BY score ASC LIMIT 10000 ; ~~~