Mysql 百萬級數(shù)據(jù)優(yōu)化資料

一、我們可以且應(yīng)該優(yōu)化什么？

硬件

操作系統(tǒng)/軟件庫

SQL服務(wù)器(設(shè)置和查詢)

應(yīng)用編程接口(API)

應(yīng)用程序

——————————————————————————–

二、優(yōu)化硬件

如果你需要龐大的數(shù)據(jù)庫表(>2G)，你應(yīng)該考慮使用64位的硬件結(jié)構(gòu)，像Alpha、Sparc或即將推出的IA64。因為MySQL內(nèi)部使用大量64位的整數(shù)，64位的CPU將提供更好的性能。

對大數(shù)據(jù)庫，優(yōu)化的次序一般是RAM、快速硬盤、CPU能力。

更多的內(nèi)存通過將最常用的鍵碼頁面存放在內(nèi)存中可以加速鍵碼的更新。

如果不使用事務(wù)安全(transaction-safe)的表或有大表并且想避免長文件檢查，一臺UPS就能夠在電源故障時讓系統(tǒng)安全關(guān)閉。

對于數(shù)據(jù)庫存放在一個專用服務(wù)器的系統(tǒng)，應(yīng)該考慮1G的以太網(wǎng)。延遲與吞吐量同樣重要。

——————————————————————————–

三、優(yōu)化磁盤

為系統(tǒng)、程序和臨時文件配備一個專用磁盤，如果確是進行很多修改工作，將更新日志和事務(wù)日志放在專用磁盤上。

低尋道時間對數(shù)據(jù)庫磁盤非常重要。對與大表，你可以估計你將需要log(行數(shù))/log(索引塊長度/3*2/(鍵碼長度 + 數(shù)據(jù)指針長度))+1次尋到才能找到一行。對于有500000行的表，索引Mediun int類型的列，需要log(500000) / log(1024/3*2/(3 + 2))+1=4次尋道。上述索引需要500000*7*3/2=5.2M的空間。實際上，大多數(shù)塊將被緩存，所以大概只需要1-2次尋道。

然而對于寫入（如上），你將需要4次尋道請求來找到在哪里存放新鍵碼，而且一般要2次尋道來更新索引并寫入一行。

對于非常大的數(shù)據(jù)庫，你的應(yīng)用將受到磁盤尋道速度的限制，隨著數(shù)據(jù)量的增加呈N log N數(shù)據(jù)級遞增。

將數(shù)據(jù)庫和表分在不同的磁盤上。在MySQL中，你可以為此而使用符號鏈接。

條列磁盤(RAID 0)將提高讀和寫的吞吐量。

帶鏡像的條列(RAID 0+1)將更安全并提高讀取的吞吐量。寫入的吞吐量將有所降低。

不要對臨時文件或可以很容易地重建的數(shù)據(jù)所在的磁盤使用鏡像或RAID(除了RAID 0)。

在Linux上，在引導(dǎo)時對磁盤使用命令hdparm -m16 -d1以啟用同時讀寫多個扇區(qū)和DMA功能。這可以將響應(yīng)時間提高5~50%。

在Linux上，用async (默認)和noatime掛載磁盤(mount)。

對于某些特定應(yīng)用，可以對某些特定表使用內(nèi)存磁盤，但通常不需要。

——————————————————————————–

四、優(yōu)化操作系統(tǒng)

不要交換區(qū)。如果內(nèi)存不足，增加更多的內(nèi)存或配置你的系統(tǒng)使用較少內(nèi)存。

不要使用NFS磁盤(會有NFS鎖定的問題)。

增加系統(tǒng)和MySQL服務(wù)器的打開文件數(shù)量。(在safe_mysqld腳本中加入ulimit -n #)。

增加系統(tǒng)的進程和線程數(shù)量。

如果你有相對較少的大表，告訴文件系統(tǒng)不要將文件打碎在不同的磁道上(Solaris)。

使用支持大文件的文件系統(tǒng)(Solaris)。

選擇使用哪種文件系統(tǒng)。在Linux上的Reiserfs對于打開、讀寫都非常快。文件檢查只需幾秒種。

——————————————————————————–

五、選擇應(yīng)用編程接口

PERL

可在不同的操作系統(tǒng)和數(shù)據(jù)庫之間移植。

適宜快速原型。

應(yīng)該使用DBI/DBD接口。

PHP

比PERL易學。

使用比PERL少的資源。

通過升級到PHP4可以獲得更快的速度。

C

MySQL的原生接口。

較快并賦予更多的控制。

低層，所以必須付出更多。

C++

較高層次，給你更多的時間來編寫應(yīng)用。

仍在開發(fā)中

ODBC

運行在Windows和Unix上。

幾乎可在不同的SQL服務(wù)器間移植。

較慢。MyODBC只是簡單的直通驅(qū)動程序，比用原生接口慢19%。

有很多方法做同樣的事。很難像很多ODBC驅(qū)動程序那樣運行，在不同的領(lǐng)域還有不同的錯誤。

問題成堆。Microsoft偶爾還會改變接口。

不明朗的未來。(Microsoft更推崇OLE而非ODBC)

ODBC

運行在Windows和Unix上。

幾乎可在不同的SQL服務(wù)器間移植。

較慢。MyODBC只是簡單的直通驅(qū)動程序，比用原生接口慢19%。

有很多方法做同樣的事。很難像很多ODBC驅(qū)動程序那樣運行，在不同的領(lǐng)域還有不同的錯誤。

問題成堆。Microsoft偶爾還會改變接口。

不明朗的未來。(Microsoft更推崇OLE而非ODBC)

JDBC

理論上可在不同的操作系統(tǒng)何時據(jù)庫間移植。

可以運行在web客戶端。

Python和其他

可能不錯，可我們不用它們。

——————————————————————————–

六、優(yōu)化應(yīng)用

應(yīng)該集中精力解決問題。

在編寫應(yīng)用時，應(yīng)該決定什么是最重要的：

速度

操作系統(tǒng)間的可移植性

SQL服務(wù)器間的可移植性

使用持續(xù)的連接。.

緩存應(yīng)用中的數(shù)據(jù)以減少SQL服務(wù)器的負載。

不要查詢應(yīng)用中不需要的列。

不要使用Select * FROM table_name…

測試應(yīng)用的所有部分，但將大部分精力放在在可能最壞的合理的負載下的測試整體應(yīng)用。通過以一種模塊化的方式進行，你應(yīng)該能用一個快速“啞模塊”替代找到的瓶頸，然后很容易地標出下一個瓶頸。

如果在一個批處理中進行大量修改，使用LOCK TABLES。例如將多個UpdateS或DeleteS集中在一起。

——————————————————————————–

七、應(yīng)該使用可移植的應(yīng)用

Perl DBI/DBD

ODBC

JDBC

Python(或其他有普遍SQL接口的語言)

你應(yīng)該只使用存在于所有目的SQL服務(wù)器中或可以很容易地用其他構(gòu)造模擬的SQL構(gòu)造。www.mysql.com上的Crash-me頁可以幫助你。

為操作系統(tǒng)/SQL服務(wù)器編寫包裝程序來提供缺少的功能。

——————————————————————————–

八、如果你需要更快的速度，你應(yīng)該：

找出瓶頸(CPU、磁盤、內(nèi)存、SQL服務(wù)器、操作系統(tǒng)、API或應(yīng)用)并集中全力解決。

使用給予你更快速度/靈活性的擴展。

逐漸了解SQL服務(wù)器以便能為你的問題使用可能最快的SQL構(gòu)造并避免瓶頸。

優(yōu)化表布局和查詢。

使用復(fù)制以獲得更快的選擇(select)速度。

如果你有一個慢速的網(wǎng)絡(luò)連接數(shù)據(jù)庫，使用壓縮客戶/服務(wù)器協(xié)議。

不要害怕時應(yīng)用的第一個版本不能完美地移植，在你解決問題時，你總是可以在以后優(yōu)化它。

——————————————————————————–

九、優(yōu)化MySQL

挑選編譯器和編譯選項。

位你的系統(tǒng)尋找最好的啟動選項。

通讀MySQL參考手冊并閱讀Paul DuBios的《MySQL》一書。(已有中文版-譯注)

多用EXPLAIN Select、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。

了解查詢優(yōu)化器的工作原理。

優(yōu)化表的格式。

維護你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)

使用MySQL的擴展功能以讓一切快速完成。

如果你注意到了你將在很多場合需要某些函數(shù)，編寫MySQL UDF函數(shù)。

不要使用表級或列級的GRANT，除非你確實需要。

購買MySQL技術(shù)支持以幫助你解決問題憨笑

——————————————————————————–

十、編譯和安裝MySQL

通過位你的系統(tǒng)挑選可能最好的編譯器，你通常可以獲得10-30%的性能提高。

在Linux/Intel平臺上，用pgcc(gcc的奔騰芯片優(yōu)化版)編譯MySQL。然而，二進制代碼將只能運行在Intel奔騰CPU上。

對于一種特定的平臺，使用MySQL參考手冊上推薦的優(yōu)化選項。

一般地，對特定CPU的原生編譯器(如Sparc的Sun Workshop)應(yīng)該比gcc提供更好的性能，但不總是這樣。

用你將使用的字符集編譯MySQL。

靜態(tài)編譯生成mysqld的執(zhí)行文件(用–with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最終的執(zhí)行文件。

注意，既然MySQL不使用C++擴展，不帶擴展支持編譯MySQL將贏得巨大的性能提高。

如果操作系統(tǒng)支持原生線程，使用原生線程(而不用mit-pthreads)。

用MySQL基準測試來測試最終的二進制代碼。

——————————————————————————–

十一、維護

如果可能，偶爾運行一下OPTIMIZE table，這對大量更新的變長行非常重要。

偶爾用myisamchk -a更新一下表中的鍵碼分布統(tǒng)計。記住在做之前關(guān)掉MySQL。

如果有碎片文件，可能值得將所有文件復(fù)制到另一個磁盤上，清除原來的磁盤并拷回文件。

如果遇到問題，用myisamchk或CHECK table檢查表。

用mysqladmin -i10 precesslist extended-status監(jiān)控MySQL的狀態(tài)。

用MySQL GUI客戶程序，你可以在不同的窗口內(nèi)監(jiān)控進程列表和狀態(tài)。

使用mysqladmin debug獲得有關(guān)鎖定和性能的信息。

——————————————————————————–

十二、優(yōu)化SQL

揚SQL之長，其它事情交由應(yīng)用去做。使用SQL服務(wù)器來做：

找出基于Where子句的行。

JOIN表

GROUP BY

orDER BY

DISTINCT

不要使用SQL來做：

檢驗數(shù)據(jù)(如日期)

成為一只計算器

技巧：

明智地使用鍵碼。

鍵碼適合搜索，但不適合索引列的插入/更新。

保持數(shù)據(jù)為數(shù)據(jù)庫第三范式，但不要擔心冗余信息或這如果你需要更快的速度，創(chuàng)建總結(jié)表。

在大表上不做GROUP BY，相反創(chuàng)建大表的總結(jié)表并查詢它。

Update table set count=count+1 where key_column=constant非常快。

對于大表，或許最好偶爾生成總結(jié)表而不是一直保持總結(jié)表。

充分利用Insert的默認值。

——————————————————————————–

十三、不同SQL服務(wù)器的速度差別（以秒計）

+————————–+——–+———+

|通過鍵碼讀取2000000行： | NT | Linux |

+————————–+——–+———+

|mysql | 367 | 249 |

+————————–+——–+———+

|mysql_odbc | 464 | |

+————————–+——–+———+　

|db2_odbc | 1206 | |

+————————–+——–+———+　

|informix_odbc | 121126 | |

+————————–+——–+———+　

|ms-sql_odbc 　 | 1634 | |

+————————–+——–+———+

|oracle_odbc | 20800 | |

+————————–+——–+———+　

|solid_odbc | 877 　 | |

+————————–+——–+———+

|sybase_odbc | 17614 | |

+————————–+——–+———+　

+————————–+——–+———+　

|插入350768行： | NT | Linux |

+————————–+——–+———+

|mysql | 381 | 206 |

+————————–+——–+———+

|mysql_odbc | 619 　 | |

+————————–+——–+———+

|db2_odbc | 3460 　| |

+————————–+——–+———+

|informix_odbc | 2692 　| |

+————————–+——–+———+

|ms-sql_odbc | 4012 　| |

+————————–+——–+———+

|oracle_odbc | 11291 | |

+————————–+——–+———+　

|solid_odbc | 1801 　| |

+————————–+——–+———+

|sybase_odbc | 4802 　| |

+————————–+——–+———+

在上述測試中，MySQL配置8M高速緩存運行，其他數(shù)據(jù)庫以默認安裝運行。

——————————————————————————–

十四、重要的MySQL啟動選項

back_log 如果需要大量新連接，修改它。

thread_cache_size 如果需要大量新連接，修改它。

key_buffer_size 索引頁池，可以設(shè)成很大。

bdb_cache_size BDB表使用的記錄和鍵嗎高速緩存。

table_cache 如果有很多的表和并發(fā)連接，修改它。

delay_key_write 如果需要緩存所有鍵碼寫入，設(shè)置它。

log_slow_queries 找出需花大量時間的查詢。

max_heap_table_size 用于GROUP BY

sort_buffer 用于ORDER BY和GROUP BY

myisam_sort_buffer_size 用于REPAIR TABLE

join_buffer_size 在進行無鍵嗎的聯(lián)結(jié)時使用。

——————————————————————————–

十五、優(yōu)化表

MySQL擁有一套豐富的類型。你應(yīng)該對每一列嘗試使用最有效的類型。

ANALYSE過程可以幫助你找到表的最優(yōu)類型：Select * FROM table_name PROCEDURE ANALYSE()。

對于不保存NULL值的列使用NOT NULL，這對你想索引的列尤其重要。

將ISAM類型的表改為MyISAM。

如果可能，用固定的表格式創(chuàng)建表。

不要索引你不想用的東西。

利用MySQL能按一個索引的前綴進行查詢的事實。如果你有索引INDEX(a,b)，你不需要在a上的索引。

不在長CHAR/VARCHAR列上創(chuàng)建索引，而只索引列的一個前綴以節(jié)省存儲空間。Create TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

對每個表使用最有效的表格式。

在不同表中保存相同信息的列應(yīng)該有同樣的定義并具有相同的列名。

——————————————————————————–

十六、MySQL如何次存儲數(shù)據(jù)

數(shù)據(jù)庫以目錄存儲。

表以文件存儲。

列以變長或定長格式存儲在文件中。對BDB表，數(shù)據(jù)以頁面形式存儲。

支持基于內(nèi)存的表。

數(shù)據(jù)庫和表可在不同的磁盤上用符號連接起來。

在Windows上，MySQL支持用.sym文件內(nèi)部符號連接數(shù)據(jù)庫。

——————————————————————————–

十七、MySQL表類型

HEAP表：固定行長的表，只存儲在內(nèi)存中并用HASH索引進行索引。

ISAM表：MySQL 3.22中的早期B-tree表格式。

MyIASM：IASM表的新版本，有如下擴展：

二進制層次的可移植性。

NULL列索引。

對變長行比ISAM表有更少的碎片。

支持大文件。

更好的索引壓縮。

更好的鍵嗎統(tǒng)計分布。

更好和更快的auto_increment處理。

來自Sleepcat的Berkeley DB(BDB)表：事務(wù)安全(有BEGIN WORK/COMMIT|ROLLBACK)。

——————————————————————————–

十八、MySQL行類型（專指IASM/MyIASM表）

如果所有列是定長格式(沒有VARCHAR、BLOB或TEXT)，MySQL將以定長表格式創(chuàng)建表，否則表以動態(tài)長度格式創(chuàng)建。

定長格式比動態(tài)長度格式快很多并更安全。

動態(tài)長度行格式一般占用較少的存儲空間，但如果表頻繁更新，會產(chǎn)生碎片。

在某些情況下，不值得將所有VARCHAR、BLOB和TEXT列轉(zhuǎn)移到另一個表中，只是獲得主表上的更快速度。

利用myiasmchk（對ISAM，pack_iasm），可以創(chuàng)建只讀壓縮表，這使磁盤使用率最小，但使用慢速磁盤時，這非常不錯。壓縮表充分地利用將不再更新的日志表

——————————————————————————–

十九、MySQL高速緩存（所有線程共享，一次性分配）

鍵碼緩存：key_buffer_size，默認8M。

表緩存：table_cache，默認64。

線程緩存：thread_cache_size，默認0。

主機名緩存：可在編譯時修改，默認128。

內(nèi)存映射表：目前僅用于壓縮表。

注意：MySQL沒有運行高速緩存，而讓操作系統(tǒng)處理。

——————————————————————————–

二十、MySQL緩存區(qū)變量（非共享，按需分配）

sort_buffer：ORDER BY/GROUP BY

record_buffer：掃描表。

join_buffer_size：無鍵聯(lián)結(jié)

myisam_sort_buffer_size：REPAIR TABLE

net_buffer_length:對于讀SQL語句并緩存結(jié)果。

tmp_table_size：臨時結(jié)果的HEAP表大小。

——————————————————————————–

二十一、MySQL表高速緩存工作原理

每個MyISAM表的打開實例(instance)使用一個索引文件和一個數(shù)據(jù)文件。如果表被兩個線程使用或在同一條查詢中使用兩次，MyIASM將共享索引文件而是打開數(shù)據(jù)文件的另一個實例。

如果所有在高速緩存中的表都在使用，緩存將臨時增加到比表緩存尺寸大些。如果是這樣，下一個被釋放的表將被關(guān)閉。

你可以通過檢查mysqld的Opened_tables變量以檢查表緩存是否太小。如果該值太高，你應(yīng)該增大表高速緩存。

——————————————————————————–

二十二、MySQL擴展/優(yōu)化-提供更快的速度

使用優(yōu)化的表類型（HEAP、MyIASM或BDB表）。

對數(shù)據(jù)使用優(yōu)化的列。

如果可能使用定長行。

使用不同的鎖定類型（Select HIGH_PRIORITY，Insert LOW_PRIORITY）

Auto_increment

REPLACE (REPLACE INTO table_name VALUES (…))

Insert DELAYED

LOAD DATA INFILE / LOAD_FILE()

使用多行Insert一次插入多行。

Select INTO OUTFILE

LEFT JOIN, STRAIGHT JOIN

LEFT JOIN ，結(jié)合IS NULL

orDER BY可在某些情況下使用鍵碼。

如果只查詢在一個索引中的列，將只使用索引樹解決查詢。

聯(lián)結(jié)一般比子查詢快（對大多數(shù)SQL服務(wù)器亦如此）。

LIMIT

Select * from table1 Where a > 10 LIMIT 10,20

Delete * from table1 Where a > 10 LIMIT 10

foo IN (常數(shù)列表) 高度優(yōu)化。

GET_LOCK()/RELEASE_LOCK()

LOCK TABLES

Insert和Select可同時運行。

UDF函數(shù)可裝載進一個正在運行的服務(wù)器。

壓縮只讀表。

Create TEMPORARY TABLE

Create TABLE .. Select

帶RAID選項的MyIASM表將文件分割成很多文件以突破某些文件系統(tǒng)的2G限制。

Delay_keys

復(fù)制功能

——————————————————————————–

二十二、MySQL何時使用索引

對一個鍵碼使用>, >=, =, <, <=, IF NULL和BETWEEN

Sel&#101;ct * FROM table_name Wh&#101;re key_part1=1 and key_part2 > 5;

Sel&#101;ct * FROM table_name Wh&#101;re key_part1 IS NULL;

當使用不以通配符開始的LIKE

Sel&#101;ct * FROM table_name Wh&#101;re key_part1 LIKE &#39;jani%&#39;

在進行聯(lián)結(jié)時從另一個表中提取行時

Sel&#101;ct * from t1,t2 wh&#101;re t1.col=t2.key_part

找出指定索引的MAX()或MIN()值

Sel&#101;ct MIN(key_part2),MAX(key_part2) FROM table_name wh&#101;re key_part1=10

一個鍵碼的前綴使用ORDER BY或GROUP BY

Sel&#101;ct * FROM foo o&#114;DER BY key_part1,key_part2,key_part3

在所有用在查詢中的列是鍵碼的一部分時間

Sel&#101;ct key_part3 FROM table_name Wh&#101;re key_part1=1

——————————————————————————–

二十三、MySQL何時不使用索引

如果MySQL能估計出它將可能比掃描整張表還要快時，則不使用索引。例如如果key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好：

Sel&#101;ct * FROM table_name wh&#101;re key_part1 > 1 and key_part1 < 90

如果使用HEAP表且不用=搜索所有鍵碼部分。

在HEAP表上使用ORDER BY。

如果不是用鍵碼第一部分

Sel&#101;ct * FROM table_name Wh&#101;re key_part2=1

如果使用以一個通配符開始的LIKE

Sel&#101;ct * FROM table_name Wh&#101;re key_part1 LIKE &#39;%jani%&#39;

搜索一個索引而在另一個索引上做ORDER BY

Sel&#101;ct * from table_name Wh&#101;re key_part1 = # o&#114;DER BY key2

——————————————————————————–

二十四、學會使用EXPLAIN

對于每一條你認為太慢的查詢使用EXPLAIN!

mysql> explain sel&#101;ct t3.DateOfAction, t1.TransactionID

-> from t1 join t2 join t3

-> wh&#101;re t2.ID = t1.TransactionID and t3.ID = t2.GroupID

-> o&#114;der by t3.DateOfAction, t1.TransactionID;

+——-+——–+—————+———+———+——————+——+———————————+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+——-+——–+—————+———+———+——————+——+———————————+

| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |

| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |

| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |

+——-+——–+—————+———+———+——————+——+———————————+

ALL和范圍類型提示一個潛在的問題。

——————————————————————————–

二十五、學會使用SHOW PROCESSLIST

使用SHOW processlist來發(fā)現(xiàn)正在做什么：

+—-+——-+———–+—-+———+——+————–+————————————-+

| Id | User | Host | db | Command | Time | State | Info |

+—-+——-+———–+—-+———+——+————–+————————————-+

| 6 | monty | localhost | bp | Query | 15 | Sending data | sel&#101;ct * from station,station as s1 |

| 8 | monty | localhost | | Query | 0 | | show processlist |

+—-+——-+———–+—-+———+——+————–+————————————-+

在mysql或mysqladmin中用KILL來殺死溜掉的線程。

——————————————————————————–

二十六、如何知曉MySQL解決一條查詢

運行項列命令并試圖弄明白其輸出：

SHOW VARIABLES;

SHOW COLUMNS FROM …G

EXPLAIN Sel&#101;ct …G

FLUSH STATUS;

Sel&#101;ct …;

SHOW STATUS;

——————————————————————————–

二十七、MySQL非常不錯

日志

在進行很多連接時，連接非常快。

同時使用Sel&#101;ct和Ins&#101;rt的場合。

在不把更新與耗時太長的選擇結(jié)合時。

在大多數(shù)選擇/更新使用唯一鍵碼時。

在使用沒有長時間沖突鎖定的多個表時。

在用大表時(MySQL使用一個非常緊湊的表格式)。

——————————————————————————–

二十八、MySQL應(yīng)避免的事情

用刪掉的行更新或插入表，結(jié)合要耗時長的Sel&#101;ct。

在能放在Wh&#101;re子句中的列上用HAVING。

不使用鍵碼或鍵碼不夠唯一而進行JOIN。

在不同列類型的列上JOIN。

在不使用=匹配整個鍵碼時使用HEAP表。

在MySQL監(jiān)控程序中忘記在Up&#100;ate或Del&#101;te中使用一條Wh&#101;re子句。如果想這樣做，使用mysql客戶程序的–i-am-a-dummy選項。

——————————————————————————–

二十九、MySQL各種鎖定

內(nèi)部表鎖定

LOCK TABLES（所有表類型適用）

GET LOCK()/RELEASE LOCK()

頁面鎖定（對BDB表）

Alt&#101;r TABLE也在BDB表上進行表鎖定

LOCK TABLES允許一個表有多個讀者和一個寫者。

一般Wh&#101;re鎖定具有比READ鎖定高的優(yōu)先級以避免讓寫入方干等。對于不重要的寫入方，可以使用LOW_PRIORITY關(guān)鍵字讓鎖定處理器優(yōu)選讀取方。

Up&#100;ate LOW_PRIORITY SET value=10 Wh&#101;re id=10;

——————————————————————————–

三十、給MySQL更多信息以更好地解決問題的技巧

注意你總能去掉(加注釋)MySQL功能以使查詢可移植：

Sel&#101;ct /*! SQL_BUFFER_RESULTS */ …

Sel&#101;ct SQL_BUFFER_RESULTS …

將強制MySQL生成一個臨時結(jié)果集。只要所有臨時結(jié)果集生成后，所有表上的鎖定均被釋放。這能在遇到表鎖定問題時或要花很長時間將結(jié)果傳給客戶端時有所幫助。

Sel&#101;ct SQL_SMALL_RESULT … GROUP BY …

告訴優(yōu)化器結(jié)果集將只包含很少的行。

Sel&#101;ct SQL_BIG_RESULT … GROUP BY …

告訴優(yōu)化器結(jié)果集將包含很多行。

Sel&#101;ct STRAIGHT_JOIN …

強制優(yōu)化器以出現(xiàn)在FROM子句中的次序聯(lián)結(jié)表。

Sel&#101;ct … FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2

強制MySQL使用/忽略列出的索引。

——————————————————————————–

三十一、事務(wù)的例子

MyIASM表如何進行事務(wù)處理:

mysql> LOCK TABLES trans READ, customer WRITE;

mysql> sel&#101;ct sum(value) from trans wh&#101;re customer_id=some_id;

mysql> up&#100;ate customer set total_value=sum_from_previous_statement

wh&#101;re customer_id=some_id;

mysql> UNLOCK TABLES;

BDB表如何進行事務(wù):

mysql> BEGIN WORK;

mysql> sel&#101;ct sum(value) from trans wh&#101;re customer_id=some_id;

mysql> up&#100;ate customer set total_value=sum_from_previous_statement

wh&#101;re customer_id=some_id;

mysql> COMMIT;

注意你可以通過下列語句回避事務(wù):

Up&#100;ate customer SET value=value+new_value Wh&#101;re customer_id=some_id;

——————————————————————————–

三十二、使用REPLACE的例子

REPLACE的功能極像Ins&#101;rt，除了如果一條老記錄在一個唯一索引上具有與新紀錄相同的值，那么老記錄在新紀錄插入前則被刪除。不使用

Sel&#101;ct 1 FROM t1 Wh&#101;re key=#

IF found-row

LOCK TABLES t1

Del&#101;te FROM t1 Wh&#101;re key1=#

Ins&#101;rt INTO t1 VALUES (…)

UNLOCK TABLES t1;

ENDIF

而用

REPLACE INTO t1 VALUES (…)

——————————————————————————–

三十三、一般技巧

使用短主鍵。聯(lián)結(jié)表時使用數(shù)字而非字符串。

當使用多部分鍵碼時，第一部分應(yīng)該時最常用的部分。

有疑問時，首先使用更多重復(fù)的列以獲得更好地鍵碼壓縮。

如果在同一臺機器上運行MySQL客戶和服務(wù)器，那么在連接MySQL時則使用套接字而不是TCP/IP（這可以提高性能7.5%）。可在連接MySQL服務(wù)器時不指定主機名或主機名為localhost來做到。

如果可能，使用–skip-locking(在某些OS上為默認)，這將關(guān)閉外部鎖定并將提高性能。

使用應(yīng)用層哈希值而非長鍵碼：

Sel&#101;ct * FROM table_name Wh&#101;re hash=MD5(concat(col1,col2)) AND

col_1=&#39;constant&#39; AND col_2=&#39;constant&#39;

在文件中保存需要以文件形式訪問的BLOB，在數(shù)據(jù)庫中只保存文件名。

刪除所有行比刪除一大部分行要快。

如果SQL不夠快，研究一下訪問數(shù)據(jù)的較底層接口。

——————————————————————————–

三十四、使用MySQL 3.23的好處

MyISAM：可移植的大表格式

HEAP：內(nèi)存中的表

Berkeley DB：支持事務(wù)的表。

眾多提高的限制

動態(tài)字符集

更多的STATUS變量

CHECK和REPAIR表

更快的GROUP BY和DISTINCT

LEFT JOIN … IF NULL的優(yōu)化

Cr&#101;ate TABLE … Sel&#101;ct

Cr&#101;ate TEMPORARY table_name (…)

臨時HEAP表到MyISAM表的自動轉(zhuǎn)換

復(fù)制

mysqlhotcopy腳本

——————————————————————————–

三十五、正在積極開發(fā)的重要功能

改進事務(wù)處理

失敗安全的復(fù)制

正文搜索

多個表的刪除(之后完成多個表的更新)

更好的鍵碼緩存

原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo)

查詢高速緩存

MERGE TABLES

一個更好的GUI客戶程序

MySQL優(yōu)化簡明指南

——————————————————————————–

2004-05-25

一、在編譯時優(yōu)化MySQL

如果你從源代碼分發(fā)安裝MySQL，要注意，編譯過程對以后的目標程序性能有重要的影響，不同的編譯方式可能得到類似的目標文件，但性能可能相差很大，因此，在編譯安裝MySQL適應(yīng)仔細根據(jù)你的應(yīng)用類型選擇最可能好的編譯選項。這種定制的MySQL可以為你的應(yīng)用提供最佳性能。

技巧：選用較好的編譯器和較好的編譯器選項，這樣應(yīng)用可提高性能10-30%。（MySQL文檔如是說）

1.1、使用pgcc（Pentium GCC)編譯器

該編譯器（http://www.goof.com/pcg/）針對運行?…繼諳低成杓頻摹?

1.2、僅使用你想使用的字符集編譯MySQL

MySQL目前提供多達24種不同的字符集，為全球用戶以他們自己的語言插入或查看表中的數(shù)據(jù)。卻省情況下，MySQL安裝所有者這些字符集，熱然而，最好的選擇是指選擇一種你需要的。如，禁止除Latin1字符集以外的所有其它字符集：

——————————————————————————–

%>./configure -with-extra-charsets=none [--other-configuration-options]

——————————————————————————–

1.3、將mysqld編譯成靜態(tài)執(zhí)行文件

將mysqld編譯成靜態(tài)執(zhí)行文件而無需共享庫也能獲得更好的性能。通過在配置時指定下列選項，可靜態(tài)編譯mysqld。

——————————————————————————–

%>./configure -with-mysqld-ldflags=-all-static [--other-configuration-options]

——————————————————————————–

1.4、配置樣本

下列配置命令常用于提高性能：

——————————————————————————–

%>CFLAGS=&#34;-O6 -mpentiumpro -fomit-frame-pointer&#34; CXX=gcc CXXFLAGS=&#34;-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti&#34; ./configure –prefix=/usr/local –enable-assembler –with-mysqld-ldflags=-all-static –disable-shared

——————————————————————————–

二、調(diào)整服務(wù)器

確保運用正確的編譯固然重要，但這只是成功的第一步，配置眾多的MySQL變量同樣對服務(wù)器的正常運行起關(guān)鍵作用。你可以將這些變量的賦值存在一個配置文件中，以確保它們在每次啟動MySQL時均起作用，這個配置文件就是my.cnf文件。

MySQL已經(jīng)提供了幾個my.cnf文件的樣本，可在/usr/local/mysqld/share/mysql/目錄下找到。這些文件分別命名為 my-small.cnf、 my-medium.cnf、my-large.cnf和my-huge.cnf，規(guī)模說明可在描述配置文件適用的系統(tǒng)類型標題中找到。如果在只有相當少內(nèi)存的系統(tǒng)上運行MySQL，而且只是偶爾的用一下，那么my-small.cnf會比較理想，因為它命令mysqld只使用最少的資源。類似地，如果你計劃構(gòu)建電子商務(wù)超市，而且系統(tǒng)擁有2G內(nèi)存，那么你可能要用到mysql-huge.cnf文件了。

為了利用這些文件中的一個，你需要復(fù)制一個最適合需求的文件，改名為my.cnf。你可以選擇使用配置文件三種作用范圍的一種：

Global：將my.cnf文件復(fù)制到服務(wù)器的/etc目錄下，這使得配置文件中的變量作用于全局，即對所有服務(wù)器上的MySQL數(shù)據(jù)庫服務(wù)器有效。

Local：將my.cnf文件復(fù)制到[MYSQL-INSTALL-DIR]/var/目錄下，使得my.cnf作用于特定的服務(wù)器。[MYSQL-INSTALL-DIR]表示MySQL安裝目錄。

User：你可以再限制作用于特定的用戶，將my.cnf復(fù)制到用戶的根目錄下。

究竟如何設(shè)置my.cnf中的這些變量呢？更進一步說，你可以設(shè)置哪一個變量。雖然所用變量對MySQL服務(wù)器相對通用，每一個變量與MySQL的的某些組件有更特定的關(guān)系。如變量max_connects歸在mysqld類別下。執(zhí)行下列命令即可知道：

——————————————————————————–

%>/usr/local/mysql/libexec/mysqld –help

——————————————————————————–

它顯示大量的選項及與mysqld相關(guān)的變量。你可以很容易地在該行文字之下找出變量：

——————————————————————————–

Possible variables for option –set-variable (-O) are

——————————————————————————–

然后你可以如下設(shè)置my.cnf中的那些變量：

——————————————————————————–

set-variable = max_connections=100

——————————————————————————–

它設(shè)置MySQL服務(wù)器的最大并發(fā)連接數(shù)為100。要確保在my.cnf文件中的[mysqld]標題下插入變量設(shè)置。

三、表類型

很多MySQL用戶可能很驚訝，MySQL確實為用戶提供5種不同的表類型，稱為DBD、HEAP、ISAM、MERGE和MyIASM。DBD歸為事務(wù)安全類，而其他為非事務(wù)安全類。

3.1、事務(wù)安全

DBD

Berkeley DB(DBD)表是支持事務(wù)處理的表，由Sleepycat軟件公司(http://www.sleepycat.com)開發(fā)。它提供MySQL用戶期待已久的功能-事務(wù)控制。事務(wù)控制在任何數(shù)據(jù)庫系統(tǒng)中都是一個極有價值的功能，因為它們確保一組命令能成功地執(zhí)行。

3.2、非事務(wù)安全

HEAP

HEAP表是MySQL中存取數(shù)據(jù)最快的表。這是因為他們使用存儲在動態(tài)內(nèi)存中的一個哈希索引。另一個要點是如果MySQL或服務(wù)器崩潰，數(shù)據(jù)將丟失。

ISAM

ISAM表是早期MySQL版本的缺省表類型，直到MyIASM開發(fā)出來。建議不要再使用它。

MERGE

MERGE是一個有趣的新類型，在3.23.25之后出現(xiàn)。一個MERGE表實際上是一個相同MyISAM表的集合，合并成一個表，主要是為了效率原因。這樣可以提高速度、搜索效率、修復(fù)效率并節(jié)省磁盤空間。

MyIASM

這是MySQL的缺省表類型。它基于IASM代碼，但有很多有用的擴展。MyIASM比較好的原因：

MyIASM表小于IASM表，所以使用較少資源。

MyIASM表在不同的平臺上二進制層可移植。

更大的鍵碼尺寸，更大的鍵碼上限。

3.3、指定表類型

你可在創(chuàng)建表時指定表的類型。下例創(chuàng)建一個HEAP表：

——————————————————————————–

mysql>Cr&#101;ate TABLE email_addresses TYPE=HEAP (

->email char(55) NOT NULL,

->name char(30) NOT NULL,

->PRIMARY KEY(email) );

——————————————————————————–

BDB表需要一些配置工作，參見http://www.mysql.com/doc/B/D/BDB_overview.html。

3.4、更多的表類型

為了使MySQL管理工作更有趣，即將發(fā)布的MySQL 4.0將提供兩種新的表類型，稱為Innobase和Gemeni。

4、優(yōu)化工具

MySQL服務(wù)器本身提供了幾條內(nèi)置命令用于幫助優(yōu)化。

4.1、SHOW

你可能有興趣知道MySQL服務(wù)器究竟更了什么，下列命令給出一個總結(jié)：

——————————————————————————–

mysql>show status;

——————————————————————————–

它給出了一個相當長的狀態(tài)變量及其值的列表。有些變量包含了異常終止客戶的數(shù)量、異常終止連接的數(shù)量、連接嘗試的次數(shù)、最大并發(fā)連接數(shù)和大量其他有用的信息。這些信息對找出系統(tǒng)問題和低效極具價值。

SHOW還能做更多的事情。它可以顯示關(guān)于日志文件、特定數(shù)據(jù)庫、表、索引、進程和權(quán)限表中有價值的信息。詳見MySQL手冊。

4.2、EXPLAIN

當你面對Sel&#101;ct語句時，EXPLAIN解釋Sel&#101;ct命令如何被處理。這不僅對決定是否應(yīng)該增加一個索引，而且對決定一個復(fù)雜的Join如何被MySQL處理都是有幫助的。

4.3、OPTIMIZE

OPTIMIZE語句允許你恢復(fù)空間和合并數(shù)據(jù)文件碎片，對包含變長行的表進行了大量更新和刪除后，這樣做特別重要。OPTIMIZE目前只工作于MyIASM和BDB表。

本文轉(zhuǎn)自 vfast_chenxy 51CTO博客，原文鏈接：http://blog.51cto.com/chenxy/1111596，如需轉(zhuǎn)載請自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的Mysql 百万级数据优化资料的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。