mysql常見最佳化

標籤：線上的   date   query   唯一性   distinct   子查詢   資料   where   登入   

最佳化Group By語句預設情況下，MySQL 排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL 可以毫不減速地對它進行最佳化，儘管仍然進行排序。如果查詢包括GROUP BY 但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。  最佳化Order by語句在某些情況中，MySQL 可以使用一個索引來滿足ORDER BY 子句，而不需要額外的排序。where 條件和order by 使用相同的索引，並且order by 的順序和索引順序相同，並且order by 的欄位都是升序或者都是降序。  最佳化insert語句如果你同時從同一客戶插入很多行，使用多個值表的INSERT 語句。這比使用分開 INSERT 語句快(在一些情況中幾倍)。Insert into test values(1,2),(1,3),(1,4)… 如果你從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。Delayed 的含義是讓insert 語句馬上執行，其實資料都被放在記憶體的隊列中，並沒有真正的寫入磁碟；這比每條語句都分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他使用者對錶的讀寫完成後才進行插入。 將索引檔案和資料檔案分在不同的磁碟上存放（利用建表中的選項）；如果進行批量插入，可以增加bulk_insert_buffer_size 變數值的方法來提高速度，但是，這隻能對myisam表使用 當從一個文字檔裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；根據應用情況使用replace 語句代替insert；根據應用情況使用ignore 關鍵字忽略重複記錄。  大批量插入資料1. 對於Myisam 類型的表，可以通過以下方式快速的匯入大量的資料。

ALTER TABLE tblname DISABLE KEYS;loading the dataALTER TABLE tblname ENABLE KEYS;

這兩個命令用來開啟或者關閉Myisam 表非唯一索引的更新。在匯入大量的資料到一個非空的Myisam 表時，通過設定這兩個命令，可以提高匯入的效率。對於匯入大量資料到一個空的Myisam 表，預設就是先匯入資料然後才建立索引的，所以不用進行設定。 2. 而對於Innodb 類型的表，這種方式並不能提高匯入資料的效率。對於Innodb 類型的表，我們有以下幾種方式可以提高匯入的效率：a. 因為Innodb 類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效提高匯入資料的效率。如果Innodb 表沒有主鍵，那麼系統會預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高匯入資料的效率。
 b. 在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SETUNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。c. 如果應用使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動認可，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。  查詢的最佳化讀為主可以設定low_priority_updates=1，寫的優先順序調低，告訴MYSQL盡量先處理讀求  為查詢快取最佳化你的查詢

大多數的MySQL伺服器都開啟了查詢快取。這是提高性最有效方法之一，而且這是被MySQL的資料庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩衝中，這樣，後續的相同的查詢就不用動作表而直接存取緩衝結果了。

這裡最主要的問題是，對於程式員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用緩衝。請看下面的樣本：

// 查詢快取不開啟 $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");  // 開啟查詢快取 $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= ‘$today‘"); 

  拆分大的 DELETE 或 INSERT 語句

如果你需要在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

Apache 會有很多的子進程或線程。所以，其工作起來相當有效率，而我們的伺服器也不希望有太多的子進程，線程和資料庫連結，這是極大的占伺服器資源的事情，尤其是記憶體。

如果你把你的表鎖上一段時間，比如30秒鐘，那麼對於一個有很高訪問量的網站來說，這30秒所積累的訪問進程/線程，資料庫連結，開啟的檔案數，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整台伺服器馬上掛了。

所以，如果你有一個大的處理，你定你一定把其拆分，使用 LIMIT 條件是一個好的方法。下面是一個樣本：

while (1) {     //每次只做1000條     mysql_query("DELETE FROM logs WHERE log_date <= ‘2009-11-01‘ LIMIT 1000");     if (mysql_affected_rows() == 0) {         // 沒得可刪了，退出！         break;     }     // 每次都要休息一會兒     usleep(50000); } 

 

where語句的最佳化

1.盡量避免在 where 子句中對欄位進行運算式操作
select id from uinfo_jifen where jifen/60 > 10000;
最佳化後:
Select id from uinfo_jifen where jifen>600000;

2.應盡量避免在where子句中對欄位進行函數操作，這將導致mysql放棄使用索引

select uid from imid where datediff(create_time,‘2011-11-22‘)=0
最佳化後
select uid from imid where create_time> =‘2011-11-21‘ and create_time<‘2011-11-23’;

 

索引的最佳化

MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。

盡量不要寫!=或者<>的sql，用between或> and <代替，否則可能用不到索引

Order by 、Group by 、Distinct 最好在需要這個列上建立索引，利於索引排序

盡量利用mysql索引排序

沒辦法的情況下，使用強制索引Force index(index_name)

盡量避勉innodb用非常大尺寸的欄位作為主鍵

較頻繁的作為查詢條件的欄位應該建立索引;

選擇性高的欄位比較適合建立索引;

作為表關聯欄位一般都需要創索引.

更新非常頻繁的欄位不適合建立索引;

不會出現在 WHERE 子句中的欄位不該建立索引.

選擇性太低的欄位不適合單獨建立索引

 

盡量不要用子查詢

mysql> explain select uid_,count(*) from smember_6 where uid_ in (select uid_ from alluid) group by uid_;| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+| 1 | PRIMARY | smember_6 | index | NULL | PRIMARY | 8 | NULL | 53431264 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | alluid | ALL | NULL | NULL | NULL | NULL | 2448 | Using where |--最佳化後| mysql> explain select a.uid_,count(*) from smember_6 a,alluid b where a.uid_=b.uid_ group by uid_;+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2671 | Using temporary; Using filesort | | 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | ssc.b.uid_ | 1 | Using index

 

Join的最佳化

如果你的應用程式有很多 JOIN 查詢，你應該確認兩個表中Join的欄位是被建過索引的。這樣，MySQL內部會啟動為你最佳化Join的SQL語句的機制。
而且，這些被用來Join的欄位，應該是相同的類型的。例如：如果你要把 DECIMAL 欄位和一個 INT 欄位Join在一起，MySQL就無法使用它們的索引。對於那些STRING類型，還需要有相同的字元集才行。（兩個表的字元集有可能不一樣）

 

表的最佳化

儘可能的使用 NOT NULL
除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的欄位保持 NOT NULL。
不要以為 NULL 不需要空間，其需要額外的空間，並且，在你進行比較的時候，你的程式會更複雜。 
當然，這裡並不是說你就不能使用NULL了，現實情況是很複雜的，依然會有些情況下，你需要使用NULL值。
下面摘自MySQL自己的文檔：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

 

固定長度的表會更快

如果表中的所有欄位都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的欄位： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些欄位，那麼這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種方法來處理。
固定長度的表會提高效能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個資料的位移量的，所以讀取的自然也會很快。而如果欄位不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。
並且，固定長度的表也更容易被緩衝和重建。不過，唯一的副作用是，固定長度的欄位會浪費一些空間，因為定長的欄位無論你用不用，他都是要分配那麼多的空間。

 

垂直分割

"垂直分割"是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和欄位的數目，從而達到最佳化的目的。（以前，在銀行做過項目，見過一張表有100多個欄位，很恐怖）

樣本一：在Users表中有一個欄位是家庭地址，這個欄位是可選欄位，相比起，而且你在資料庫操作的時候除了個人資訊外，你並不需要經常讀取或是改寫這個欄位。那麼，為什麼不把他放到另外一張表中呢？ 這樣會讓你的表有更好的效能，大家想想是不是，大量的時候，我對於使用者表來說，只有使用者ID，使用者名稱，口令，使用者角色等會被經常使用。小一點的表總是會有好的效能。

樣本二： 你有一個叫 “last_login” 的欄位，它會在每次使用者登入時被更新。但是，每次更新時會導致該表的查詢快取被清空。所以，你可以把這個欄位放到另一個表中，這樣就不會影響你對使用者ID，使用者名稱，使用者角色的不停地讀取了，因為查詢快取會幫你增加很多效能。

另外，你需要注意的是，這些被分出去的欄位所形成的表，你不會經常性地去Join他們，不然的話，這樣的效能會比不分割時還要差，而且，會是極數級的下降。

 

越小的列會越快

對於大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。所以，把你的資料變得緊湊會對這種情況非常有協助，因為這減少了對硬碟的訪問。

參看 MySQL 的文檔 Storage Requirements 查看所有的資料類型。

如果一個表只會有幾列罷了（比如說字典表，配置表），那麼，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果你不需要記錄時間，使用 DATE 要比 DATETIME 好得多。

當然，你也需要留夠足夠的擴充空間，不然，你日後來幹這個事，你會死的很難看，參看Slashdot的例子（2009年11月06日），一個簡單的ALTER TABLE語句花了3個多小時，因為裡面有一千六百萬條資料。

mysql常見最佳化