MYSQL語句和表的最佳化

-- 最佳化SQL的一般步驟-- 一、通過show status和應用特點瞭解各種SQL的執行頻率/*通過SHOW STATUS可以提供伺服器狀態資訊，也可以使用mysqladmin extended-status命令獲得。SHOW STATUS可以根據需要顯示session層級的統計結果和global層級的統計結果。以下幾個參數對Myisam和Innodb儲存引擎都計數：1.Com_select  執行select操作的次數，一次查詢只累加1；2.Com_insert 執行insert操作的次數，對於批量插入的insert操作，只累加一次；3.Com_update 執行update操作的次數；4.Com_delete　執行delete操作的次數；*/SHOW STATUS WHERE Variable_name = 'Com_select';/*以下幾個參數是針對Innodb儲存引擎計數的，累加的演算法也略有不同：1.Innodb_rows_read select查詢返回的行數；2.Innodb_rows_inserted 執行Insert操作插入的行數；3.Innodb_rows_updated 執行update操作更新的行數；4.Innodb_rows_deleted 執行delete操作刪除的行數；通過以上幾個參數，可以很容易的瞭解當前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各種類型的SQL大致的執行比例是多少。對於更新操作的計數，是對執行次數的計數，不論提交還是復原都會累加。對於事務型的應用，通過 Com_commit 和 Com_rollback 可以瞭解事務提交和復原的情況，對於復原操作非常頻繁的資料庫，可能意味著應用編寫存在問題。此外，以下幾個參數便於我們瞭解資料庫的基本情況：1.Connections 試圖串連Mysql伺服器的次數2.Uptime 伺服器工作時間3.Slow_queries 慢查詢的次數二、定位執行效率較低的SQL語句可以通過以下兩種方式定位執行效率較低的SQL語句：1.可以通過慢查詢日誌定位那些執行效率較低的sql語句，用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的記錄檔。可以連結到管理維護中的相關章節。2.慢查詢日誌在查詢結束以後才紀錄，所以在應用反映執行效率出現問題的時候查詢慢查詢日誌並不能定位問題，可以使用show processlist命令查看當前MySQL在進行的線程，包括線程的狀態，是否鎖表等等，可以即時的查看SQL執行情況，同時對一些鎖表操作進行最佳化。*/SHOW PROCESSLIST;/*三、通過EXPLAIN分析低效SQL的執行計畫通過以上步驟查詢到效率低的SQL後，我們可以通過explain或者desc 擷取MySQL如何執行SELECT語句的資訊，包括select語句執行過程表如何串連和串連的次序。explain 可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。*/EXPLAIN SELECT * FROM message a LEFT JOIN mytable b ON a.id = b.id WHERE a.id=1;/*返回結果+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+|   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+|   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              ||   1    | SIMPLE        | b     | ALL   | NULL         | NULL           | NULL      |       | 9999   |              |+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+select_type：select 類型table：      輸出結果集的表type：       表示表的連線類型①當表中僅有一行是type的值為system是最佳的連線類型；②當select操作中使用索引進行表串連時type的值為ref；③當select的表串連沒有使用索引時，經常會看到type的值為ALL，表示對該表進行了全表掃描，這時需要考慮通過建立索引來提高表串連的效率。possible_keys：表示查詢時,可以使用的索引列.key：          表示使用的索引key_len：      索引長度rows：         掃描範圍Extra：執行情況的說明和描述四、確定問題，並採取相應的最佳化措施經過以上步驟，基本可以確認問題出現的原因，可以根據情況採取相應的措施，進行最佳化提高執行的效率。例如上面的例子，我們確認是對b表的全表掃描導致效率的不理想，我們對b表的 id 欄位建立了索引，查詢需要掃描的行數明顯較少。返回結果+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+|   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+|   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              ||   1    | SIMPLE        | b     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+大批量插入資料時最佳化SQL語句==============================================一、對於Myisam類型的表，可以通過以下步驟快速的匯入大量的資料。     ALTER TABLE tablename DISABLE KEYS;    批量插入資料    ALTER TABLE tablename ENABLE KEYS;前後兩個命令用來開啟或者關閉Myisam表非唯一索引的更新。在匯入大量的資料到一個非空的Myisam表時，通過設定這兩個命令，可以提高匯入的效率。對於匯入大量資料到一個空的Myisam表，預設就是先匯入資料然後才建立索引的，所以不用進行設定。*/ALTER TABLE mytable DISABLE KEYS;INSERT INTO mytable(id, username, city, age) VALUES(1, 'name1', 'city1', 10),(2, 'name2', 'city2', 20),(3, 'name3', 'city3', 30);ALTER TABLE mytable ENABLE KEYS;/*二、而對於Innodb類型的表，這種方式並不能提高匯入資料的效率。對於Innodb類型的表，我們有以下幾種方式可以提高匯入的效率：①因為Innodb類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效提高匯入資料的效率。如果Innodb表沒有主鍵，那麼系統會預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。②在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SET UNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。③如果應用使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動認可，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。*/SET UNIQUE_CHECKS=0;SET UNIQUE_CHECKS=1;SET AUTOCOMMIT=0;SET AUTOCOMMIT=1;/*最佳化insert語句==============================================1、如果同時插入很多行，請使用多個值的INSERT語句。這比使用分開INSERT語句快(在一些情況中幾倍)。Insert into test values(1,2),(1,3),(1,4)…2、如果從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。 Delayed 的含義是讓insert 語句馬上執行，其實資料都被放在記憶體的隊列中，並沒有真正寫入磁碟；這比每條語句分別插入要快的多；LOW_PRIORITY 剛好相反，在所有其他使用者對錶的讀寫完後才進行插入；3、將索引檔案和資料檔案分在不同的磁碟上存放（利用建表中的選項）；4、如果批量插入，可以增加bulk_insert_buffer_size變數值的方法來提高速度，但是，這隻能對myisam表使用；5、當從一個文字檔裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；6、根據應用情況使用 replace 語句代替 insert；7、根據應用情況使用 ignore 關鍵字忽略重複記錄。*/INSERT DELAYED INTO mytable(id, username, city, age) VALUES(4, 'name4', 'city4', 40);INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);REPLACE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);INSERT IGNORE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);/*最佳化group by語句==============================================預設情況下，MySQL排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY  col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL可以毫不減速地對它進行最佳化，儘管仍然進行排序。如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定 ORDER BY NULL禁止排序。例如：*/SELECT * FROM mytable GROUP BY username ORDER BY NULL;/*最佳化order by語句==============================================在某些情況中，MySQL可以使用一個索引在 ORDER BY 子句中，而不需要額外的排序。where條件和order by使用相同的索引，並且order by的順序和索引順序相同，並且order by的欄位都是升序或者都是降序。例如：下列sql可以使用索引。    SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; -- 同一複合式索引的某一部分    SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; 但是以下情況不使用索引：SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；--order by的欄位混合ASC和DESCSELECT * FROM t1 WHERE key2=constant ORDER BY key1；--用於查詢行的關鍵字與ORDER BY中所使用的不相同SELECT * FROM t1 ORDER BY key1, key2；--對不同的索引使用ORDER BY：最佳化join語句==============================================Mysql4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的串連(JOIN).. 替代。假設我們要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：    SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 如果使用串連(JOIN).. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：    SELECT * FROM customerinfo    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID    WHERE salesinfo.CustomerID IS NULL  串連(JOIN).. 之所以更有效率一些，是因為 MySQL不需要在記憶體中建立暫存資料表來完成這個邏輯上的需要兩個步驟的查詢工作。insert、update、delete的使用順序==============================================MySQL還允許改變語句調度的優先順序，它可以使來自多個用戶端的查詢更好地協作，這樣單個用戶端就不會由於鎖定而等待很長時間。改變優先順序還可以確保特定類型的查詢被處理得更快。我們首先應該確定應用的類型，判斷應用是以查詢為主還是以更新為主的，是確保查詢效率還是確保更新的效率，決定是查詢優先還是更新優先。下面我們提到的改變調度策略的方法主要是針對Myisam儲存引擎的，對於Innodb儲存引擎，語句的執行是由獲得行鎖的順序決定的。 MySQL的預設的調度策略可用總結如下：1.寫入操作優先於讀取操作。2.對某張資料表的寫入操作某一時刻只能發生一次，寫入請求按照它們到達的次序來處理。3.對某張資料表的多個讀取操作可以同時地進行。MySQL提供了幾個語句調節符，允許你修改它的調度策略：1.LOW_PRIORITY 關鍵字應用於 DELETE 、 INSERT 、 LOAD DATA 、 REPLACE和UPDATE 。2.HIGH_PRIORITY關鍵字應用於SELECT和INSERT語句。3.DELAYED關鍵字應用於INSERT和REPLACE語句。 如果寫入操作是一個LOW_PRIORITY（低優先順序）請求，那麼系統就不會認為它的優先順序高於讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。只有在沒有其它的讀取者的時候，才允許寫入者開始操作。這種調度修改可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。SELECT查詢的HIGH_PRIORITY（高優先順序）關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先順序更高。另外一種影響是，高優先順序的SELECT在正常的SELECT語句之前執行，因為這些語句會被寫入操作阻塞。如果你希望所有支援LOW_PRIORITY選項的語句都預設地按照低優先順序來處理，那麼請使用--low-priority-updates選項來啟動伺服器。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先順序，可以消除該選項對單個INSERT語句的影響。*/INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(7, 'name7', 'city7', 70);/*最佳化資料表==============================================一、最佳化表的資料類型表需要使用何種資料類型，是需要根據應用來判斷的。雖然應用設計的時候需要考慮欄位的長度留有一定的冗餘，但是不推薦讓很多欄位都留有大量的冗餘，這樣即浪費儲存也浪費記憶體。我們可以使用PROCEDURE ANALYSE()對當前已有應用的表類型的判斷，該函數可以對資料表中的列的資料類型提出最佳化建議，可以根據應用的實際情況酌情考慮是否實施最佳化。文法：   SELECT * FROM tbl_name PROCEDURE ANALYSE();   SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);輸出的每一列資訊都會對資料表中的列的資料類型提出最佳化建議。第二個例子告訴PROCEDURE ANALYSE()不要為那些包含的值多於16個或者256位元組的ENUM類型提出建議。如果沒有這樣的限制，輸出資訊可能很長；ENUM定義通常很難閱讀。在對欄位類型進行最佳化時，可以根據統計資訊並結合應用的實際情況對其進行最佳化。二、通過拆分，提高表的訪問效率這裡我們所說的拆分，主要是針對Myisam類型的表，拆分的方法可以分成兩種情況：1、縱向拆分：縱向拆分是只按照應用訪問的頻度，將表中經常訪問的欄位和不經常訪問的欄位拆分成兩個表，經常訪問的欄位盡量是定長的，這樣可以有效提高表的查詢和更新的效率。2、橫向拆分：橫向拆分是指按照應用的情況，有目的的將資料橫向拆分成幾個表或者通過分區分到多個分區中，這樣可以有效避免Myisam表的讀取和更新導致的鎖問題。三、逆正常化資料庫的正常化設計強調資料的獨立性，資料應該儘可能少地冗餘，因為存在過多的冗餘資料，這就意味著要佔用了更多的物理空間，同時也對資料的維護和一致性檢查帶來了問題。對於查詢操作很多的應用，一次查詢可能需要訪問多表進行，如果通過冗餘相同資料紀錄在一個表中，更新的代價增加不多，但是查詢操作效率可以有明顯提高，這種情況就可以考慮通過冗餘資料來提高效率。四、使用冗餘統計表使用create temporary table文法，它是基於session的表，表的資料儲存在記憶體裡面，當session斷掉後，表自然消除。對於大表的統計分析，如果統計的資料量不大，利用insert。。。select將資料移到暫存資料表中比直接在大表上做統計要效率更高。五、選擇更合適的表類型1、如果應用出現比較嚴重的鎖衝突，請考慮是否更改儲存引擎到innodb，行鎖機制可以有效減少鎖衝突的出現。2、如果應用查詢操作很多，且對事務完整性要求不嚴格，則可以考慮使用Myisam儲存引擎。*/SELECT * FROM mytable PROCEDURE ANALYSE(16,256);/*其他最佳化措施使用串連池==============================================對於訪問資料庫來說，建立串連的代價比較昂貴，因此，我們有必要建立"串連池"以提高訪問的效能。我們可以把串連當作對象或者裝置，池中又有許多已經建立的串連，訪問本來需要與資料庫的串連的地方，都改為和池相連，池臨時分配串連供訪問使用，結果返回後，訪問將串連交還。減少對Mysql的訪問==============================================一、避免對同一資料做重複檢索：應用中需要理清楚對資料庫的訪問邏輯，需要對相同表的訪問，盡量集中在相同sql訪問，一次提取結果，減少對資料庫的重複訪問。二、使用mysql query cache：作用：查詢快取儲存SELECT查詢的文本以及發送給用戶端的相應結果。如果隨後收到一個相同的查詢，伺服器從查詢快取中重新得到查詢結果，而不再需要解析和執行查詢。適用範圍：不發生資料更新的表。當表更改（包括表結構和表資料）後，查詢快取值的相關條目被清空。查詢快取的主要參數設定：*/SHOW VARIABLES LIKE '%query_cache%'; -- 也可以寫成 SHOW VARIABLES WHERE Variable_name LIKE '%query_cache%';/*have_query_cache  表明伺服器在安裝使已經配置了快取query_cache_size  表明緩衝區大小，單位為Mquery_cache_type  值從0到2，含義分別為                  0或者off（緩衝關閉）                  1或者on（緩衝開啟，使用sql_no_cache的select除外）                  2或者demand（只有帶sql_cache的select語句提供快取）在 SHOW STATUS 中，你可以監視查詢快取的效能*/SHOW STATUS LIKE '%Qcache%';/*Qcache_queries_in_cache  在緩衝中登入的查詢數目Qcache_inserts           被加入到緩衝中的查詢數目Qcache_hits              緩衝採樣數數目Qcache_lowmem_prunes     因為缺少記憶體而被從緩衝中刪除的查詢數目Qcache_not_cached        沒有被緩衝的查詢數目 (不能被緩衝的，或由於 QUERY_CACHE_TYPE)Qcache_free_memory       查詢快取的空閑記憶體總數Qcache_free_blocks       查詢快取中的空閑記憶體塊的數目Qcache_total_blocks      查詢快取中的塊的總數目三、加cache層：Cache（快取）、Memory（記憶體）、Hard disk（硬碟）都是資料存取單元，但存取速度卻有很大差異，呈依次遞減的順序。對於CPU來說，它可以從距離自己最近的Cache高速地存取資料，而不是從記憶體和硬碟以低幾個數量級的速度來存取資料。而Cache中所儲存的資料，往往是CPU要反覆存取的資料，有特定的機制（或程式）來保證Cache內資料的命中率（Hit Rate）。因此，CPU存取資料的速度在應用快取後得到了巨大的提高。因為將資料寫入快取的任務由Cache Manager負責，所以對使用者來說快取的內容肯定是唯讀。需要你做的工作很少，程式中的SQL語句和直接存取DBMS時沒有分別，返回的結果也看不出有什麼差別。而資料庫廠商往往會在DB Server的設定檔中提供與Cache相關的參數，通過修改它們，可針對我們的應用最佳化Cache的管理。均衡負載==============================================一、利用mysql 複製分流查詢操作：利用mysql的主從複製可以有效分流更新操作和查詢操作，具體的實現是一個主伺服器，承擔更新操作，多台從伺服器，承擔查詢操作，主從之間通過複製實現資料的同步。多台從伺服器一方面用來確保可用性，一方面可以建立不同的索引滿足不同查詢的需要。對於主從之間不需要複製全部表的情況，可以通過在主的伺服器上搭建一個虛擬從伺服器，將需要複製到從伺服器的表設定成blackhole引擎，然後定義replicate-do-table參數只複製這些表，這樣就過濾出需要複製的binlog，減少了傳輸binlog的頻寬。因為搭建的虛擬從伺服器只起到過濾binlog的作用，並沒有實際紀錄任何資料，所以對主要資料庫伺服器的效能影響也非常的有限。通過複製分流查詢的存在的問題是主要資料庫上更新頻繁或者網路出現問題的時候，主從之間的資料可能存在差異，造成查詢結果的異議，應用在設計的時候需要有所考慮。二、採用分散式資料庫架構：mysql從5.0.3開始支援分散式交易，當前分散式交易只對Innodb儲存引擎支援。分布式的資料庫結構描述適合大資料量，負載高的情況，有良好的擴充性和高可用性。通過在多台伺服器之間分布資料實現在多台伺服器之間的負載平均，提高了訪問的執行效率。具體實現的時候，可以使用mysql的Cluster功能（NDB引擎）或者自己編寫程式來實現全域事務。*/