mysql數據庫優化總結(心得)。本站提示廣大學習愛好者：（mysql數據庫優化總結(心得)）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql數據庫優化總結(心得)正文
1. 優化你的MySQL查詢緩存
在MySQL服務器上停止查詢，可以啟用高速查詢緩存。讓數據庫引擎在後台悄然的處置是進步功能的最無效辦法之一。當同一個查詢被執行屢次時，假如後果是從緩存中提取，那是相當快的。
但次要的問題是，它是那麼容易被隱藏起來以致於我們大少數順序員會疏忽它。在有些處置義務中，我們實踐上是可以阻止查詢緩存任務的。
// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
2. 用EXPLAIN使你的SELECT查詢愈加明晰
運用EXPLAIN關鍵字是另一個MySQL優化技巧，可以讓你理解MySQL正在停止什麼樣的查詢操作，這可以協助你發現瓶頸的所在，並顯示出查詢或表構造在哪裡出了問題。
EXPLAIN查詢的後果，可以通知你那些索引正在被援用，表是如何被掃描和排序的等等。
完成一個SELECT查詢（最好是比擬復雜的一個，帶joins方式的），在外面添加上你的關鍵詞解釋，在這裡我們可以運用phpMyAdmin，他會通知你表中的後果。舉例來說，假設當我在執行joins時，正遺忘往一個索引中添加列，EXPLAIN能協助我找到問題的所在。
添加索引到group_id field後
3. 應用LIMIT 1獲得獨一行5. 保證銜接的索引是相反的類型
假如使用順序中包括多個銜接查詢，你需求確保你鏈接的列在兩邊的表上都被索引。這會影響MySQL如何優化外部聯接操作。
此外，參加的列，必需是同一類型。例如，你參加一個DECIMAL列，而同時參加另一個表中的int列，MySQL將無法運用其中至多一個目標。即便字符編碼必需同為字符串類型。
// looking for companies in my state
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// both state columns should be indexed
// and they both should be the same type and character encoding
// or MySQL might do full table scans
6. 不要運用BY RAND()命令
這是一個令很多老手順序員會掉出來的圈套。你能夠不知不覺中制造了一個可怕的寧靜。這個圈套在你是用BY RAND（）命令時就開端創立了。
假如您真的需求隨機顯示你的後果，有很多更好的途徑去完成。固然這需求寫更多的代碼，但是能防止功能瓶頸的呈現。問題在於，MySQL能夠會為表中每一個獨立的行執行BY RAND()命令（這會耗費處置器的處置才能），然後給你僅僅前往一行。
// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
7. 盡量防止SELECT *命令
從表中讀取越多的數據，查詢會變得更慢。他添加了磁盤需求操作的時間，還是在數據庫服務器與WEB服務器是獨立分開的狀況下。你將會閱歷十分漫長的網絡延遲，僅僅是由於數據不用要的在服務器之間傳輸。
一直指定你需求的列，這是一個十分良好的習氣。
// not preferred
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// better:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// the differences are more significant with bigger result sets
8. 從PROCEDURE ANALYSE()中取得建議
PROCEDURE ANALYSE()可讓MySQL的柱構造剖析和表中的實踐數據來給你一些建議。假如你的表中曾經存在實踐數據了，能為你的嚴重決策服務。
9. 預備好的語句
預備好的語句，可以從功能優化和平安兩方面對大家有所協助。
預備好的語句在過濾曾經綁定的變量默許狀況下，能給使用順序以無效的維護，避免SQL注入攻擊。當然你也可以手動過濾，不過由於大少數順序員健忘的性情，很難到達效果。
// create a prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// bind parameters
$stmt->bind_param("s", $state);
// execute
$stmt->execute();
// bind result variables
$stmt->bind_result($username);
// fetch value
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
10. 將IP地址存儲為無符號整型
許多順序員在創立一個VARCHAR（15）時並沒無意識到他們可以將IP地址以整數方式來存儲。當你有一個INT類型時，你只占用4個字節的空間，這是一個固定大小的范疇。
你必需確定你所操作的列是一個UNSIGNED INT類型的,由於IP地址將運用32位unsigned integer。
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
11.永遠為每張表設置一個ID
我們應該為數據庫裡的每張表都設置一個ID做為其主鍵，而且最好的是一個INT型的(引薦運用UNSIGNED)，並設置上自動添加的AUTO_INCREMENT標志。
就算是你users表有一個主鍵叫“email”的字段，你也別讓它成為主鍵。運用VARCHAR類型來當主鍵會運用得功能下降。另外，在你的順序中，你應該運用表的ID來結構你的數據構造。
而且，在MySQL數據引擎下，還有一些操作需求運用主鍵，在這些狀況下，主鍵的功能和設置變得十分重要，比方，集群，分區……
在這裡，只要一個狀況是例外，那就是“關聯表”的“外鍵”，也就是說，這個表的主鍵，經過若干一般的表的主鍵構成。我們把這個狀況叫做“外鍵”。比方：有一個“先生表”有先生的ID，有一個“課程表”有課程ID，那麼，“成果表”就是“關聯表”了，其關聯了先生表和課程表，在成果表中，先生ID和課程ID叫“外鍵”其共同組成主鍵。
12.運用ENUM而不是VARCHAR
ENUM類型是十分快和緊湊的。在實踐上，其保管的是TINYINT，但其表面上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完滿。
假如你有一個字段，比方“性別”，“國度”，“民族”，“形態”或“部門”，你知道這些字段的取值是無限而且固定的，那麼，你應該運用ENUM而不是VARCHAR。
MySQL也有一個“建議”(見第十條)通知你怎樣去重新組織你的表構造。當你有一個VARCHAR字段時，這個建議會通知你把其改成ENUM類型。運用PROCEDURE ANALYSE() 你可以失掉相關的建議。
13.從PROCEDURE ANALYSE()獲得建議p順序員站
PROCEDURE ANALYSE() 會讓MySQL幫你去剖析你的字段和其實踐的數據，並會給你一些有用的建議。只要表中有實踐的數據，這些建議才會變得有用，由於要做一些大的決議是需求無數據作為根底的。
例如，假如你創立了一個INT字段作為你的主鍵，但是並沒有太多的數據，那麼，PROCEDURE ANALYSE()會建議你把這個字段的類型改成MEDIUMINT。或是你運用了一個VARCHAR字段，由於數據不多，你能夠會失掉一個讓你把它改成ENUM的建議。這些建議，都是能夠由於數據不夠多，所以決策做得就不夠准。
在phpmyadmin裡，你可以在檢查表時，點擊“Propose table structure”來檢查這些建議
一定要留意，這些只是建議，只要當你的表裡的數據越來越多時，這些建議才會變得精確。一定要記住，你才是最終做決議的人
14.盡能夠的運用NOT NULL php順序員站
除非你有一個很特別的緣由去運用NULL值，你應該總是讓你的字段堅持NOT NULL。這看起來仿佛有點爭議，請往下看。
首先，問問你自己“Empty”和“NULL”有多大的區別(假如是INT，那就是0和NULL)?假如你覺得它們之間沒有什麼區別，那麼你就不要運用NULL。(你知道嗎?在Oracle裡，NULL 和 Empty的字符串是一樣的!)
不要以為 NULL 不需求空間，其需求額定的空間，並且，在你停止比擬的時分，你的順序會更復雜。當然，這裡並不是說你就不能運用NULL了，理想狀況是很復雜的，仍然會有些狀況下，你需求運用NULL值。
上面摘自MySQL自己的文檔：
15. Prepared Statements
Prepared Statements很像存儲進程，是一種運轉在後台的SQL語句集合，我們可以從運用prepared statements取得很多益處，無論是功能問題還是平安問題。
Prepared Statements可以反省一些你綁定好的變量，這樣可以維護你的順序不會遭到“SQL注入式”攻擊。當然，你也可以手動地反省你的這些變量，但是，手動的反省容易出問題，而且很常常會被順序員忘了。當我們運用一些framework或是ORM的時分，這樣的問題會好一些。
在功能方面，當一個相反的查詢被運用屢次的時分，這會為你帶來可觀的功能優勢。你可以給這些Prepared Statements定義一些參數，而MySQL只會解析一次。
雖然最新版本的MySQL在傳輸Prepared Statements是運用二進制情勢，所以這會使得網絡傳輸十分無效率。
當然，也有一些狀況下，我們需求防止運用Prepared Statements，由於其不支持查詢緩存。但聽說版本5.1後支持了。 在PHP中要運用prepared statements，你可以檢查其運用手冊：mysqli擴展或是運用數據庫籠統層，如：PDO.
16.無緩沖的查詢
正常的狀況下，當你在當你在你的腳本中執行一個SQL語句的時分，你的順序會停在那裡直到沒這個SQL語句前往，然後你的順序再往下持續執行。你可以運用無緩沖查詢來改動這個行為。
關於這個事情，在PHP的文檔中有一個十分不錯的闡明：mysql_unbuffered_query()函數：
下面那句話翻譯過去是說，mysql_unbuffered_query()發送一個SQL語句到MySQL而並不像mysql_query()一樣去自動fethch弛緩存後果。這會相當浪費很多可觀的內存，尤其是那些會發生少量後果的查詢語句，並且，你不需求等到一切的後果都前往，只需求第一行數據前往的時分，你就可以開端馬上開端任務於查詢後果了。
但是，這會有一些限制。由於你要麼把一切行都讀走，或是你要在停止下一次的查詢前調用 mysql_free_result() 肅清後果。而且， mysql_num_rows() 或 mysql_data_seek() 將無法運用。所以，能否運用無緩沖的查詢你需求細心思索。
17.把IP地址存成UNSIGNED INT
很多順序員都會創立一個VARCHAR(15) 字段來寄存字符串方式的IP而不是整形的IP。假如你用整形來寄存，只需求4個字節，並且你可以有定長的字段。而且，這會為你帶來查詢上的優勢，尤其是當你需求運用這樣的WHERE條件：IP between ip1 and ip2。
我們必需求運用UNSIGNED INT，由於IP地址會運用整個32位的無符號整形。
而你的查詢，你可以運用 INET_ATON()來把一個字符串IP轉成一個整形，並運用INET_NTOA()把一個整形轉成一個字符串IP。在PHP中，也有這樣的函數 ip2long()和long2ip()。
18.固定長度的表會更快
假如表中的一切字段都是“固定長度”的，整個表會被以為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。只需你包括了其中一個這些字段，那麼這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種辦法來處置。
固定長度的表會進步功能，由於MySQL搜索得會更快一些，由於這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而假如字段不是定長的，那麼，每一主要找下一條的話，需求順序找到主鍵。
並且，固定長度的表也更容易被緩存和重建。不過，獨一的反作用是，固定長度的字段會糜費一些空間，由於定長的字段無論你用不必，他都是要分配那麼多的空間。 php順序員站
運用“垂直聯系”技術(見下一條)，你可以聯系你的表成為兩個一個是定長的，一個則是不定長的。
19.垂直聯系
“垂直聯系”是一種把數據庫中的表按列變成幾張表的辦法，這樣可以降低表的復雜度和字段的數目，從而到達優化的目的。(以前，在銀行做過項目，見過一張表有100多個字段，很恐懼)
示例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起，而且你在數據庫操作的時分除了團體信息外，你並不需求常常讀取或是改寫這個字段。那麼，為什麼不把他放到另外一張表中呢?這樣會讓你的表有更好
