MySQL 機能優化的最好20多條經歷分享。本站提示廣大學習愛好者：（MySQL 機能優化的最好20多條經歷分享）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL 機能優化的最好20多條經歷分享正文
當我們去設計數據庫表構造，對操作數據庫時（特別是查表時的SQL語句），我們都須要留意數據操作的機能。這裡，我們不會講過量的SQL語句的優化，而只是針對MySQL這一Web運用最多的數據庫。願望上面的這些優化技能對你有效。
1. 為查詢緩存優化你的查詢
年夜多半的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'");
下面兩條SQL語句的差異就是 CURDATE() ，MySQL的查詢緩存對這個函數不起感化。所以，像 NOW() 和 RAND() 或是其它的諸如斯類的SQL函數都不會開啟查詢緩存，由於這些函數的前往是會不定的易變的。所以，你所須要的就是用一個變量來取代MySQL的函數，從而開啟緩存。
2. EXPLAIN 你的 SELECT 查詢
應用 EXPLAIN 症結字可讓你曉得MySQL是若何處置你的SQL語句的。這可以幫你剖析你的查詢語句或是表構造的機能瓶頸。
EXPLAIN 的查詢成果還會告知你你的索引主鍵被若何應用的，你的數據表是若何被搜刮和排序的……等等，等等。
挑一個你的SELECT語句（推舉遴選誰人最龐雜的，有多表聯接的），把症結字EXPLAIN加到後面。你可使用phpmyadmin來做這個事。然後，你會看到一張表格。上面的這個示例中，我們忘卻加上了group_id索引，而且有表聯接：
當我們為 group_id 字段加上索引後：
我們可以看到，前一個成果顯示搜刮了 7883 行，爾後一個只是搜刮了兩個表的 9 和 16 行。檢查rows列可讓我們找到潛伏的機能成績。
3. 當只需一行數據時應用 LIMIT 1
當你查詢表的有些時刻，你曾經曉得成果只會有一條成果，但由於你能夠須要去fetch游標，或是你或許會去檢討前往的記載數。
在這類情形下，加上 LIMIT 1 可以增長機能。如許一樣，MySQL數據庫引擎會在找到一條數據後停滯搜刮，而不是持續往後查少下一條相符記載的數據。
上面的示例，只是為了找一下能否有“中國”的用戶，很顯著，前面的會比後面的更有用率。（請留意，第一條中是Select *，第二條是Select 1）
// 沒有用率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有用率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}
4. 為搜刮字段建索引
索引其實不必定就是給主鍵或是獨一的字段。假如在你的表中，有某個字段你總要會常常用來做搜刮，那末，請為其樹立索引吧。
從上圖你可以看到誰人搜刮字串 “last_name LIKE ‘a%'”，一個是建了索引，一個是沒有索引，機能差了4倍閣下。
別的，你應當也須要曉得甚麼樣的搜刮是不克不及應用正常的索引的。例如，當你須要在一篇年夜的文章中搜刮一個詞時，如： “WHERE post_content LIKE ‘%apple%'”，索引能夠是沒成心義的。你能夠須要應用MySQL全文索引 或是本身做一個索引（好比說：搜刮症結詞或是Tag甚麼的）
5. 在Join表的時刻應用相當類型的例，並將其索引
假如你的運用法式有許多 JOIN 查詢，你應當確認兩個表中Join的字段是被建過索引的。如許，MySQL外部會啟動為你優化Join的SQL語句的機制。
並且，這些被用來Join的字段，應當是雷同的類型的。例如：假如你要把 DECIMAL 字段和一個 INT 字段Join在一路，MySQL就沒法應用它們的索引。關於那些STRING類型，還須要有雷同的字符集才行。（兩個表的字符集有能夠紛歧樣）
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 兩個 state 字段應當是被建過索引的，並且應當是相當的類型，雷同的字符集。
6. 萬萬不要 ORDER BY RAND()
想打亂前往的數據行？隨機挑一個數據？真不曉得誰創造了這類用法，但許多老手很愛好如許用。但你確不懂得如許做有何等恐怖的機能成績。
假如你真的想把前往的數據行打亂了，你有N種辦法可以到達這個目標。如許應用只讓你的數據庫的機能呈指數級的降低。這裡的成績是：MySQL會不能不去履行RAND()函數（很耗CPU時光），並且這是為了每行記載去記行，然後再對其排序。就算是你用了Limit 1也杯水車薪（由於要排序）
上面的示例是隨機挑一筆記錄
// 萬萬不要如許做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 這要會更好：
$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辦事器是兩台自力的辦事器的話，這還會增長收集傳輸的負載。
所以，你應當養成一個須要甚麼就取甚麼的好的習氣。
// 不推舉
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推舉
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
8. 永久為每張表設置一個ID
我們應當為數據庫裡的每張表都設置一個ID做為其主鍵，並且最好的是一個INT型的（推舉應用UNSIGNED），並設置上主動增長的 AUTO_INCREMENT標記。
就算是你 users 表有一個主鍵叫 “email”的字段，你也別讓它成為主鍵。應用 VARCHAR 類型來當主鍵會應用得機能降低。別的，在你的法式中，你應當應用表的ID來結構你的數據構造。
並且，在MySQL數據引擎下，還有一些操作須要應用主鍵，在這些情形下，主鍵的機能和設置變得異常主要，好比，集群，分區……
在這裡，只要一個情形是破例，那就是“聯系關系表”的“外鍵”，也就是說，這個表的主鍵，經由過程若干個體的表的主鍵組成。我們把這個情形叫做“外鍵”。好比：有一個“先生表”有先生的ID，有一個“課程表”有課程ID，那末，“成就表”就是“聯系關系表”了，其聯系關系了先生表和課程表，在成就表中，先生ID和課程ID叫“外鍵”其配合構成主鍵。
9. 應用 ENUM 而不是 VARCHAR
ENUM 類型長短常快和緊湊的。在現實上，其保留的是 TINYINT，但其表面上顯示為字符串。如許一來，用這個字段來做一些選項列表變得相當的完善。
假如你有一個字段，好比“性別”，“國度”，“平易近族”，“狀況”或“部分”，你曉得這些字段的取值是無限並且固定的，那末，你應當應用 ENUM 而不是 VARCHAR。
MySQL也有一個“建議”（見第十條）告知你怎樣去從新組織你的表構造。當你有一個 VARCHAR 字段時，這個建議會告知你把其改成 ENUM 類型。應用 PROCEDURE ANALYSE() 你可以獲得相干的建議。
10. 從 PROCEDURE ANALYSE() 獲得建議
PROCEDURE ANALYSE() 會讓 MySQL 幫你去剖析你的字段和其現實的數據，並會給你一些有效的建議。只要表中有現實的數據，這些建議才會變得有效，由於要做一些年夜的決議是須要稀有據作為基本的。
例如，假如你創立了一個 INT 字段作為你的主鍵，但是並沒有太多的數據，那末，PROCEDURE ANALYSE()會建議你把這個字段的類型改成 MEDIUMINT 。或是你應用了一個 VARCHAR 字段，由於數據不多，你能夠會獲得一個讓你把它改成 ENUM 的建議。這些建議，都是能夠由於數據不敷多，所以決議計劃做得就不敷准。
在phpmyadmin裡，你可以在檢查表時，點擊 “Propose table structure” 來檢查這些建議
必定要留意，這些只是建議，只要當你的內外的數據愈來愈多時，這些建議才會變得精確。必定要記住，你才是終究做決議的人。
11. 盡量的應用 NOT NULL
除非你有一個很特殊的緣由去應用 NULL 值，你應當老是讓你的字段堅持 NOT NULL。這看起來似乎有點爭議，請往下看。
起首，問問你本身“Empty”和“NULL”有多年夜的差別（假如是INT，那就是0和NULL）？假如你認為它們之間沒有甚麼差別，那末你就不要應用NULL。（你曉得嗎？在 Oracle 裡，NULL 和 Empty 的字符串是一樣的！)
不要認為 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.”
12. 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.
// 創立 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// 綁定參數
$stmt->bind_param("s", $state);
// 履行
$stmt->execute();
// 綁定成果
$stmt->bind_result($username);
// 挪動游標
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
13. 無緩沖的查詢
正常的情形下，當你在當你在你的劇本中履行一個SQL語句的時刻，你的法式會停在那邊直到沒這個SQL語句前往，然後你的法式再往下持續履行。你可使用無緩沖查詢來轉變這個行動。
關於這個工作，在PHP的文檔中有一個異常不錯的解釋： mysql_unbuffered_query() 函數：
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed.”
下面那句話翻譯過去是說，mysql_unbuffered_query() 發送一個SQL語句到MySQL而其實不像mysql_query()一樣去主動fethch懈弛存成果。這會相當勤儉許多可不雅的內存，特別是那些會發生年夜量成果的查詢語句，而且，你不須要比及一切的成果都前往，只須要第一行數據前往的時刻，你便可以開端立時開端任務於查詢成果了。
但是，這會有一些限制。由於你要末把一切行都讀走，或是你要在停止下一次的查詢前挪用 mysql_free_result() 消除成果。並且， mysql_num_rows() 或 mysql_data_seek() 將沒法應用。所以，能否應用無緩沖的查詢你須要細心斟酌。
14. 把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()。
1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
15. 固定長度的表會更快
假如表中的一切字段都是“固定長度”的，全部表會被以為是 “static” 或 “fixed-length”。 例如，表中沒有以下類型的字段： VARCHAR，TEXT，BLOB。只需你包含了個中一個這些字段，那末這個表就不是“固定長度靜態表”了，如許，MySQL 引擎會用另外一種辦法來處置。
固定長度的表會進步機能，由於MySQL搜索得會更快一些，由於這些固定的長度是很輕易盤算下一個數據的偏移量的，所以讀取的天然也會很快。而假如字段不是定長的，那末，每主要找下一條的話，須要法式找到主鍵。
而且，固定長度的表也更輕易被緩存和重建。不外，獨一的反作用是，固定長度的字段會糟蹋一些空間，由於定長的字段不管你用不消，他都是要分派那末多的空間。
應用“垂直朋分”技巧（見下一條），你可以朋分你的表成為兩個一個是定長的，一個則是不定長的。
16. 垂直朋分
“垂直朋分”是一種把數據庫中的表按列釀成幾張表的辦法，如許可以下降表的龐雜度和字段的數量，從而到達優化的目標。（之前，在銀行做過項目，見過一張表有100多個字段，很恐懼）
示例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，比擬起，並且你在數據庫操作的時刻除小我信息外，你其實不須要常常讀取或是改寫這個字段。那末，為何不把他放到別的一張表中呢？ 如許會讓你的表有更好的機能，年夜家想一想是否是，年夜量的時刻，我關於用戶表來講，只要用戶ID，用戶名，口令，用戶腳色等會被常常應用。小一點的表老是會有好的機能。
示例二： 你有一個叫 “last_login” 的字段，它會在每次用戶登錄時被更新。然則，每次更新時會招致該表的查詢緩存被清空。所以，你可以把這個字段放到另外一個表中，如許就不會影響你對用戶 ID，用戶名，用戶腳色的一直地讀取了，由於查詢緩存會幫你增長許多機能。
別的，你須要留意的是，這些被分出去的字段所構成的表，你不會常常性地去Join他們，否則的話，如許的機能會比不朋分時還要差，並且，會是極數級的降低。
17. 拆分年夜的 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);
}
18. 越小的列會越快
關於年夜多半的數據庫引擎來講，硬盤操作能夠是最嚴重的瓶頸。所以，把你的數據變得緊湊會對這類情形異常有贊助，由於這削減了對硬盤的拜訪。
參看 MySQL 的文檔 Storage Requirements 檢查一切的數據類型。
假如一個表只會有幾列而已（好比說字典表，設置裝備擺設表），那末，我們就沒有來由應用 INT 來做主鍵，應用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。假如你不須要記載時光，應用 DATE 要比 DATETIME 好很多。
固然，你也須要留夠足夠的擴大空間，否則，你往後來干這個事，你會逝世的很好看，參看Slashdot的例子（2009年11月06 日），一個簡略的ALTER TABLE語句花了3個多小時，由於外面有一千六百萬條數據。
19. 選擇准確的存儲引擎
在 MySQL 中有兩個存儲引擎 MyISAM 和 InnoDB，每一個引擎都有益有弊。酷殼之前文章《MySQL: InnoDB 照樣 MyISAM?》評論辯論和這個工作。
MyISAM 合適於一些須要年夜量查詢的運用，但其關於有年夜量寫操作其實不是很好。乃至你只是須要update一個字段，全部表都邑被鎖起來，而其余過程，就算是讀過程都沒法操作直到讀操作完成。別的，MyISAM 關於 SELECT COUNT(*) 這類的盤算是超快非常的。
InnoDB 的趨向會是一個異常龐雜的存儲引擎，關於一些小的運用，它會比 MyISAM 還慢。他是它支撐“行鎖” ，因而在寫操作比擬多的時刻，會更優良。而且，他還支撐更多的高等運用，好比：事務。
上面是MySQL的手冊
* target=”_blank”MyISAM Storage Engine
* InnoDB Storage Engine
20. 應用一個對象關系映照器（Object Relational Mapper）
應用 ORM (Object Relational Mapper)，你可以或許取得靠得住的機能增漲。一個ORM可以做的一切工作，也能被手動的編寫出來。然則，這須要一個高等專家。
ORM 的最主要的是“Lazy Loading”，也就是說，只要在須要的去取值的時刻才會去真實的去做。但你也須要當心這類機制的反作用，由於這很有能夠會由於要去創立許多許多小的查詢反而會下降機能。
ORM 還可以把你的SQL語句打包成一個事務，這會比零丁履行他們快很多很多。
今朝，小我最愛好的PHP的ORM是：Doctrine。
21. 當心“永遠鏈接”
“永遠鏈接”的目標是用來削減從新創立MySQL鏈接的次數。當一個鏈接被創立了，它會永久處在銜接的狀況，就算是數據庫操作曾經停止了。並且，自從我們的Apache開端重用它的子過程後——也就是說，下一次的HTTP要求會重用Apache的子過程，偏重用雷同的 MySQL 鏈接。
* PHP手冊：mysql_pconnect()
在實際下去說，這聽起來異常的不錯。然則從小我經歷（也是年夜多半人的）下去說，這個功效制作出來的費事事更多。由於，你只要無限的鏈接數，內存成績，文件句柄數，等等。
並且，Apache 運轉在極端並行的情況中，會創立許多許多的了過程。這就是為何這類“永遠鏈接”的機制任務地欠好的緣由。在你決議要應用“永遠鏈接”之前，你須要好好地斟酌一下你的全部體系的架構。
文章：起源
