首先，一件事情影響所有的詢問。你有的許可系統設置越複雜，你得到更多的開銷。
如果你不讓任何GRANT語句執行，MySQL將稍微優化許可檢查。因此如果你有很大量，值得花時間來避免授權，否則更多的許可檢查有更大的開銷。
如果你的問題是與一些明顯的MySQL函數有關，你總能在MySQL客戶中計算其時間：
mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
上面顯示MySQL能在PentiumII 400MHz上以0.32秒執行1,000,000個+表達式。
所有MySQL函數應該被高度優化，但是以可能有一些例外並且benchmark(loop_count,expression)是找出是否你的查詢有問題的一個極好工具。
10.5.1 估計查詢性能
在大多數情況下，你能通過計算磁盤尋道估計性能。對小的表，你通常能在1次磁盤尋道中找到行(因為這個索引可能被緩衝)。對更大的表，你能估計它(使用 B++ 樹索引)，你將需要：log(row_count)/log(index_block_length/3*2/(index_length + data_pointer_length))+1次尋道找到行。
在MySQL中，索引塊通常是1024個字節且數據指針通常是4個字節，這對一個有一個索引長度為3(中等整數)的 500,000 行的表給你：log(500,000)/log(1024/3*2/(3+4)) + 1= 4 次尋道。
象上面的索引將要求大約 500,000 * 7 * 3/2 = 5.2M，(假設索引緩衝區被充滿到2/3(它是典型的))，你將可能在內存中有索引的大部分並且你將可能僅需要1-2調用從OS讀數據來找出行。
然而對於寫，你將需要 4 次尋道請求(如上)來找到在哪兒存放新索引並且通常需2次尋道更新這個索引並且寫入行。
注意，上述不意味著你的應用程序將緩慢地以 N log N 退化！當表格變得更大時，只要一切被OS或SQL服務器緩衝，事情將僅僅或多或少地更慢。在數據變得太大不能被緩衝後，事情將開始變得更慢直到你的應用程 序僅僅受磁盤尋道限制(它以N log N增加)。為了避免這個增加，索引緩衝隨數據增加而增加。見10.2.3 調節服務器參數。
10.5.2 select查詢的速度
總的來說，當你想要使一個較慢的select ... where更快，檢查的第一件事情是你是否能增加一個索引。見10.4 MySQL 索引的使用。在不同表之間的所有引用通常應該用索引完成。你可以使用EXPLAIN來確定哪個索引用於一條select語句。見7.22 EXPLAIN句法(得到關於一條select的信息)。
一些一般的建議：
為了幫助MySQL更好地優化查詢，在它已經裝載了相關數據後，在一個表上運行myisamchk --analyze。這為每一個更新一個值，指出有相同值地平均行數（當然，對唯一索引，這總是1。）
為了根據一個索引排序一個索引和數據，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序)。如果你有一個唯一索引，你想要根據該索引地次序讀取所有的記錄，這是使它更快的一個好方 法。然而注意，這個排序沒有被最佳地編寫，並且對一個大表將花很長時間！
10.5.3 MySQL怎樣優化where子句
where優化被放在select中，因為他們最主要在那裡使用裡，但是同樣的優化被用於delete和update語句。
也要注意，本節是不完全的。MySQL確實作了許多優化而我們沒有時間全部記錄他們。
由MySQL實施的一些優化列在下面：
刪除不必要的括號：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
常數調入：
(a-> b>5 AND b=c AND a=5
刪除常數條件(因常數調入所需)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
索引使用的常數表達式僅計算一次。
在一個單個表上的沒有一個where的COUNT(*)直接從表中檢索信息。當僅使用一個表時，對任何NOT NULL表達式也這樣做。
無效常數表達式的早期檢測。MySQL快速檢測某些select語句是不可能的並且不返回行。
如果你不使用GROUP BY或分組函數(COUNT()、MIN()……)，HAVING與where合併。
為每個子聯結(sub join)，構造一個更簡單的where以得到一個更快的where計算並且也盡快跳過記錄。
所有常數的表在查詢中的任何其他表前被首先讀出。一個常數的表是：
一個空表或一個有1行的表。
與在一個UNIQUE索引、或一個PRIMARY KEY的where子句一起使用的表，這裡所有的索引部分使用一個常數表達式並且索引部分被定義為NOT NULL。
所有下列的表用作常數表：
mysql> select * FROM t where primary_key=1;
mysql> select * FROM t1,t2
where t1.primary_key=1 AND t2.primary_key=t1.id;
對聯結表的最好聯結組合是通過嘗試所有可能性來找到:(。如果所有在ORDER BY和GROUP BY的列來自同一個表，那麼當廉潔時，該表首先被選中。
如果有一個ORDER BY子句和一個不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含不是來自聯結隊列中的第一個表的其他表的列，創建一個臨時表。
如果你使用SQL_SMALL_RESULT，MySQL將使用一個在內存中的表。
因為DISTINCT被變換到在所有的列上的一個GROUP BY，DISTINCT與ORDER BY結合也將在許多情況下需要一張臨時表。
每個表的索引被查詢並且使用跨越少於30% 的行的索引。如果這樣的索引沒能找到，使用一個快速的表掃瞄。
在一些情況下，MySQL能從索引中讀出行，甚至不諮詢數據文件。如果索引使用的所有列是數字的，那麼只有索引樹被用來解答查詢。
在每個記錄被輸出前，那些不匹配HAVING子句的行被跳過。
下面是一些很快的查詢例子：
mysql> select COUNT(*) FROM tbl_name;
mysql> select MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> select MAX(key_part2) FROM tbl_name
where key_part_1=constant;
mysql> select ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> select ... FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
下列查詢僅使用索引樹就可解決(假設索引列是數字的)：
mysql> select key_part1,key_part2 FROM tbl_name where key_part1=val;
mysql> select COUNT(*) FROM tbl_name
where key_part1=val1 AND key_part2=val2;
mysql> select key_part2 FROM tbl_name GROUP BY key_part1;
下列查詢使用索引以排序順序檢索，不用一次另外的排序：
mysql> select ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> select ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...
10.5.4 MySQL怎樣優化LEFT join
在MySQL中，A LEFT join B實現如下：
表B被設置為依賴於表A。
表A被設置為依賴於所有用在LEFT join條件的表(除B外)。
所有LEFT join條件被移到where子句中。
進行所有標準的聯結優化，除了一個表總是在所有它依賴的表之後被讀取。如果有一個循環依賴，MySQL將發出一個錯誤。
進行所有標準的where優化。
如果在A中有一行匹配where子句，但是在B中沒有任何行匹配LEFT join條件，那麼在B中生成所有列設置為NULL的一行。
如果你使用LEFT join來找出在某些表中不存在的行並且在where部分你有下列測試：column_name IS NULL，這裡column_name 被聲明為NOT NULL的列，那麼MySQL在它已經找到了匹配LEFT join條件的一行後，將停止在更多的行後尋找(對一特定的鍵組合)。
10.5.5 MySQL怎樣優化LIMIT
在一些情況中，當你使用LIMIT #而不使用HAVING時，MySQL將以不同方式處理查詢。
如果你用LIMIT只選擇一些行，當MySQL一般比較喜歡做完整的表掃瞄時，它將在一些情況下使用索引。
如果你使用LIMIT #與ORDER BY，MySQL一旦找到了第一個 # 行，將結束排序而不是排序整個表。
當結合LIMIT #和DISTINCT時，MySQL一旦找到#個唯一的行，它將停止。
在一些情況下，一個GROUP BY能通過順序讀取鍵(或在鍵上做排序)來解決，並然後計算摘要直到鍵值改變。在這種情況下，LIMIT #將不計算任何不必要的GROUP。
只要MySQL已經發送了第一個#行到客戶，它將放棄查詢。
LIMIT 0將總是快速返回一個空集合。這對檢查查詢並且得到結果列的列類型是有用的。
臨時表的大小使用LIMIT #計算需要多少空間來解決查詢。
10.5.6 insert查詢的速度
插入一個記錄的時間由下列組成：
連接：(3)
發送查詢給服務器：(2)
分析查詢：(2)
插入記錄：（1 x 記錄大小）
插入索引：（1 x 索引）
關閉：(1)
這裡的數字有點與總體時間成正比。這不考慮打開表的初始開銷(它為每個併發運行的查詢做一次)。
表的大小以N log N (B 樹)的速度減慢索引的插入。
加快插入的一些方法：
如果你同時從同一客戶插入很多行，使用多個值表的insert語句。這比使用分開insert語句快(在一些情況中幾倍)。
如果你從不同客戶插入很多行，你能通過使用insert DELAYED語句得到更高的速度。見7.14 insert句法。
注意，用MyISAM，如果在表中沒有刪除的行，能在select:s正在運行的同時插入行。
當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多insert語句快20倍。見7.16 LOAD DATA INFILE句法。
當表有很多索引時，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列過程：
有選擇地用create TABLE創建表。例如使用mysql或Perl-DBI。
執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從表中刪除所有索引的使用。
用LOAD DATA INFILE把數據插入到表中，這將不更新任何索引，因此很快。
如果你有myisampack並且想要壓縮表，在它上面運行myisampack。見10.6.3 壓縮表的特徵。
用myisamchk -r -q /path/to/db/tbl_name再創建索引。這將在將它寫入磁盤前在內存中創建索引樹，並且它更快，因為避免大量磁盤尋道。結果索引樹也被完美地平衡。
執行FLUSH TABLES，或外殼命令mysqladmin flush-tables。
這個過程將被構造進在MySQL的某個未來版本的LOAD DATA INFILE。
你可以鎖定你的表以加速插入。
mysql> LOCK TABLES a WRITE;
mysql> insert INTO a VALUES (1,23),(2,34),(4,33);
mysql> insert INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;
主要的速度差別是索引緩衝區僅被清洗到磁盤上一次，在所有insert語句完成後。一般有與有不同的insert語句那樣奪的索引緩衝區清洗。如果你能用 一個單個語句插入所有的行，鎖定就不需要。鎖定也將降低多連接測試的整體時間，但是對某些線程最大等待時間將上升(因為他們等待鎖)。例如：
thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts
如果你不使用鎖定，2、3和4將在1和5前完成。如果你使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。因為insert, update和delete操作在MySQL中是很快的，通過為多於大約5次連續不斷地插入或更新一行的東西加鎖，你將獲得更好的整體性能。如果你做很多 一行的插入，你可以做一個LOCK TABLES，偶爾隨後做一個UNLOCK TABLES(大約每1000行)以允許另外的線程存取表。這仍然將導致獲得好的性能。當然，LOAD DATA INFILE對裝載數據仍然是更快的。
為了對LOAD DATA INFILE和insert得到一些更快的速度，擴大關鍵字緩衝區。見10.2.3 調節服務器參數。
10.5.7 update查詢的速度
更改查詢被優化為有一個寫開銷的一個select查詢。寫速度依賴於被更新數據大小和被更新索引的數量。
使更改更快的另一個方法是推遲更改並且然後一行一行地做很多更改。如果你鎖定表，做一行一行地很多更改比一次做一個快。
注意，動態記錄格式的更改一個較長總長的記錄，可能切開記錄。因此如果你經常這樣做，時不時地OPTIMIZE TABLE是非常重要的。見7.9 OPTIMIZE TABLE句法。
10.5.8 delete查詢的速度
刪除一個記錄的時間精確地與索引數量成正比。為了更快速地刪除記錄，你可以增加索引緩存的大小。見10.2.3 調節服務器參數。
從一個表刪除所有行比刪除行的一大部分也要得多。
沒有留言:
張貼留言