萬盛學電腦網 >> 數據庫 >> mysql教程 >> Mysql優化之語句及查詢優化
Mysql優化之語句及查詢優化
減少對Mysql的訪問
一、避免對同一數據做重復檢索：
應用中需要理清楚對數據庫的訪問邏輯，需要對相同表的訪問，盡量集中在相同sql訪問，一次提取結果，減少對數據庫的重復訪問。
二、使用mysql query cache：
作用：查詢緩存存儲SELECT查詢的文本以及發送給客戶端的相應結果。如果隨後收到一個相同的查詢，服務器從查詢緩存中重新得到查詢結果，而不再需要解析和執行查詢。
適用范圍：不發生數據更新的表。當表更改（包括表結構和表數據）後，查詢緩存值的相關條目被清空。
查詢緩存的主要參數設置：
show variables like ‘%query_cache%’;
have_query_cache表明服務器在安裝使已經配置了高速緩存
query_cache_size表明緩存區大小，單位為M
query_cache_type的變量值從0到2，含義分別為
0或者off（緩存關閉）
1或者on（緩存打開，使用sql_no_cache的select除外）
2或者demand（只有帶sql_cache的select語句提供高速緩存）
在 SHOW STATUS 中，你可以監視查詢緩存的性能：
變量 含義
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優化語句優化
優化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關鍵字忽略重復記錄。
優化Group By語句
默認情況下，MySQL排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL可以毫不減速地對它進行優化，盡管仍然進行排序。
如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。例如：
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a 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和DESC
②SELECT * 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語句的影響。
