|
一、我們可以且應(yīng)該優(yōu)化什么？
硬件 操作系統(tǒng)/軟件庫(kù) SQL服務(wù)器(設(shè)置和查詢(xún)) 應(yīng)用編程接口(API) 應(yīng)用程序 -------------------------------------------------------------------------------- 二、優(yōu)化硬件 如果你需要龐大的數(shù)據(jù)庫(kù)表(>2G)，你應(yīng)該考慮使用64位的硬件結(jié)構(gòu)，像Alpha、Sparc或即將推出的IA64。因?yàn)镸ySQL內(nèi)部使用大量64位的整數(shù)，64位的CPU將提供更好的性能。 對(duì)大數(shù)據(jù)庫(kù)，優(yōu)化的次序一般是RAM、快速硬盤(pán)、CPU能力。 更多的內(nèi)存通過(guò)將最常用的鍵碼頁(yè)面存放在內(nèi)存中可以加速鍵碼的更新。 如果不使用事務(wù)安全(transaction-safe)的表或有大表并且想避免長(zhǎng)文件檢查，一臺(tái)UPS就能夠在電源故障時(shí)讓系統(tǒng)安全關(guān)閉。 對(duì)于數(shù)據(jù)庫(kù)存放在一個(gè)專(zhuān)用服務(wù)器的系統(tǒng)，應(yīng)該考慮1G的以太網(wǎng)。延遲與吞吐量同樣重要。 -------------------------------------------------------------------------------- 三、優(yōu)化磁盤(pán) 為系統(tǒng)、程序和臨時(shí)文件配備一個(gè)專(zhuān)用磁盤(pán)，如果確是進(jìn)行很多修改工作，將更新日志和事務(wù)日志放在專(zhuān)用磁盤(pán)上。 低尋道時(shí)間對(duì)數(shù)據(jù)庫(kù)磁盤(pán)非常重要。對(duì)與大表，你可以估計(jì)你將需要log(行數(shù))/log(索引塊長(zhǎng)度/3*2/(鍵碼長(zhǎng)度 + 數(shù)據(jù)指針長(zhǎng)度))+1次尋到才能找到一行。對(duì)于有500000行的表，索引Mediun int類(lèi)型的列，需要log(500000) / log(1024/3*2/(3 + 2))+1=4次尋道。上述索引需要500000*7*3/2=5.2M的空間。實(shí)際上，大多數(shù)塊將被緩存，所以大概只需要1-2次尋道。 然而對(duì)于寫(xiě)入（如上），你將需要4次尋道請(qǐng)求來(lái)找到在哪里存放新鍵碼，而且一般要2次尋道來(lái)更新索引并寫(xiě)入一行。 對(duì)于非常大的數(shù)據(jù)庫(kù)，你的應(yīng)用將受到磁盤(pán)尋道速度的限制，隨著數(shù)據(jù)量的增加呈N log N數(shù)據(jù)級(jí)遞增。 將數(shù)據(jù)庫(kù)和表分在不同的磁盤(pán)上。在MySQL中，你可以為此而使用符號(hào)鏈接。 條列磁盤(pán)(RAID 0)將提高讀和寫(xiě)的吞吐量。 帶鏡像的條列(RAID 0+1)將更安全并提高讀取的吞吐量。寫(xiě)入的吞吐量將有所降低。 不要對(duì)臨時(shí)文件或可以很容易地重建的數(shù)據(jù)所在的磁盤(pán)使用鏡像或RAID(除了RAID 0)。 在Linux上，在引導(dǎo)時(shí)對(duì)磁盤(pán)使用命令hdparm -m16 -d1以啟用同時(shí)讀寫(xiě)多個(gè)扇區(qū)和DMA功能。這可以將響應(yīng)時(shí)間提高5~50%。 在Linux上，用async (默認(rèn))和noatime掛載磁盤(pán)(mount)。 對(duì)于某些特定應(yīng)用，可以對(duì)某些特定表使用內(nèi)存磁盤(pán)，但通常不需要。 -------------------------------------------------------------------------------- 四、優(yōu)化操作系統(tǒng) 不要交換區(qū)。如果內(nèi)存不足，增加更多的內(nèi)存或配置你的系統(tǒng)使用較少內(nèi)存。 不要使用NFS磁盤(pán)(會(huì)有NFS鎖定的問(wèn)題)。 增加系統(tǒng)和MySQL服務(wù)器的打開(kāi)文件數(shù)量。(在safe_mysqld腳本中加入ulimit -n #)。 增加系統(tǒng)的進(jìn)程和線程數(shù)量。 如果你有相對(duì)較少的大表，告訴文件系統(tǒng)不要將文件打碎在不同的磁道上(Solaris)。 使用支持大文件的文件系統(tǒng)(Solaris)。 選擇使用哪種文件系統(tǒng)。在Linux上的Reiserfs對(duì)于打開(kāi)、讀寫(xiě)都非常快。文件檢查只需幾秒種。 -------------------------------------------------------------------------------- 五、選擇應(yīng)用編程接口 PERL 可在不同的操作系統(tǒng)和數(shù)據(jù)庫(kù)之間移植。 適宜快速原型。 應(yīng)該使用DBI/DBD接口。 PHP 比PERL易學(xué)。 使用比PERL少的資源。 通過(guò)升級(jí)到PHP4可以獲得更快的速度。 C MySQL的原生接口。 較快并賦予更多的控制。 低層，所以必須付出更多。 C++ 較高層次，給你更多的時(shí)間來(lái)編寫(xiě)應(yīng)用。 仍在開(kāi)發(fā)中 ODBC 運(yùn)行在Windows和Unix上。 幾乎可在不同的SQL服務(wù)器間移植。 較慢。MyODBC只是簡(jiǎn)單的直通驅(qū)動(dòng)程序，比用原生接口慢19%。 有很多方法做同樣的事。很難像很多ODBC驅(qū)動(dòng)程序那樣運(yùn)行，在不同的領(lǐng)域還有不同的錯(cuò)誤。 問(wèn)題成堆。Microsoft偶爾還會(huì)改變接口。 不明朗的未來(lái)。(Microsoft更推崇OLE而非ODBC) ODBC 運(yùn)行在Windows和Unix上。 幾乎可在不同的SQL服務(wù)器間移植。 較慢。MyODBC只是簡(jiǎn)單的直通驅(qū)動(dòng)程序，比用原生接口慢19%。 有很多方法做同樣的事。很難像很多ODBC驅(qū)動(dòng)程序那樣運(yùn)行，在不同的領(lǐng)域還有不同的錯(cuò)誤。 問(wèn)題成堆。Microsoft偶爾還會(huì)改變接口。 不明朗的未來(lái)。(Microsoft更推崇OLE而非ODBC) JDBC 理論上可在不同的操作系統(tǒng)何時(shí)據(jù)庫(kù)間移植。 可以運(yùn)行在web客戶端。 Python和其他 可能不錯(cuò)，可我們不用它們。 -------------------------------------------------------------------------------- 六、優(yōu)化應(yīng)用 應(yīng)該集中精力解決問(wèn)題。 在編寫(xiě)應(yīng)用時(shí)，應(yīng)該決定什么是最重要的： 速度 操作系統(tǒng)間的可移植性 SQL服務(wù)器間的可移植性 使用持續(xù)的連接。. 緩存應(yīng)用中的數(shù)據(jù)以減少SQL服務(wù)器的負(fù)載。 不要查詢(xún)應(yīng)用中不需要的列。 不要使用SELECT * FROM table_name... 測(cè)試應(yīng)用的所有部分，但將大部分精力放在在可能最壞的合理的負(fù)載下的測(cè)試整體應(yīng)用。通過(guò)以一種模塊化的方式進(jìn)行，你應(yīng)該能用一個(gè)快速“啞模塊”替代找到的瓶頸，然后很容易地標(biāo)出下一個(gè)瓶頸。 如果在一個(gè)批處理中進(jìn)行大量修改，使用LOCK TABLES。例如將多個(gè)UPDATES或DELETES集中在一起。 -------------------------------------------------------------------------------- 七、應(yīng)該使用可移植的應(yīng)用 Perl DBI/DBD ODBC JDBC Python(或其他有普遍SQL接口的語(yǔ)言) 你應(yīng)該只使用存在于所有目的SQL服務(wù)器中或可以很容易地用其他構(gòu)造模擬的SQL構(gòu)造。www.mysql.com上的Crash-me頁(yè)可以幫助你。 為操作系統(tǒng)/SQL服務(wù)器編寫(xiě)包裝程序來(lái)提供缺少的功能。 -------------------------------------------------------------------------------- 八、如果你需要更快的速度，你應(yīng)該： 找出瓶頸(CPU、磁盤(pán)、內(nèi)存、SQL服務(wù)器、操作系統(tǒng)、API或應(yīng)用)并集中全力解決。 使用給予你更快速度/靈活性的擴(kuò)展。 逐漸了解SQL服務(wù)器以便能為你的問(wèn)題使用可能最快的SQL構(gòu)造并避免瓶頸。 優(yōu)化表布局和查詢(xún)。 使用復(fù)制以獲得更快的選擇(select)速度。 如果你有一個(gè)慢速的網(wǎng)絡(luò)連接數(shù)據(jù)庫(kù)，使用壓縮客戶/服務(wù)器協(xié)議。 不要害怕時(shí)應(yīng)用的第一個(gè)版本不能完美地移植，在你解決問(wèn)題時(shí)，你總是可以在以后優(yōu)化它。 -------------------------------------------------------------------------------- 九、優(yōu)化MySQL 挑選編譯器和編譯選項(xiàng)。 位你的系統(tǒng)尋找最好的啟動(dòng)選項(xiàng)。 通讀MySQL參考手冊(cè)并閱讀Paul DuBios的《MySQL》一書(shū)。(已有中文版-譯注) 多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。 了解查詢(xún)優(yōu)化器的工作原理。 優(yōu)化表的格式。 維護(hù)你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE) 使用MySQL的擴(kuò)展功能以讓一切快速完成。 如果你注意到了你將在很多場(chǎng)合需要某些函數(shù)，編寫(xiě)MySQL UDF函數(shù)。 不要使用表級(jí)或列級(jí)的GRANT，除非你確實(shí)需要。 購(gòu)買(mǎi)MySQL技術(shù)支持以幫助你解決問(wèn)題  -------------------------------------------------------------------------------- 十、編譯和安裝MySQL 通過(guò)位你的系統(tǒng)挑選可能最好的編譯器，你通?？梢垣@得10-30%的性能提高。 在Linux/Intel平臺(tái)上，用pgcc(gcc的奔騰芯片優(yōu)化版)編譯MySQL。然而，二進(jìn)制代碼將只能運(yùn)行在Intel奔騰CPU上。 對(duì)于一種特定的平臺(tái)，使用MySQL參考手冊(cè)上推薦的優(yōu)化選項(xiàng)。 一般地，對(duì)特定CPU的原生編譯器(如Sparc的Sun Workshop)應(yīng)該比gcc提供更好的性能，但不總是這樣。 用你將使用的字符集編譯MySQL。 靜態(tài)編譯生成mysqld的執(zhí)行文件(用--with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最終的執(zhí)行文件。 注意，既然MySQL不使用C++擴(kuò)展，不帶擴(kuò)展支持編譯MySQL將贏得巨大的性能提高。 如果操作系統(tǒng)支持原生線程，使用原生線程(而不用mit-pthreads)。 用MySQL基準(zhǔn)測(cè)試來(lái)測(cè)試最終的二進(jìn)制代碼。 -------------------------------------------------------------------------------- 十一、維護(hù) 如果可能，偶爾運(yùn)行一下OPTIMIZE table，這對(duì)大量更新的變長(zhǎng)行非常重要。 偶爾用myisamchk -a更新一下表中的鍵碼分布統(tǒng)計(jì)。記住在做之前關(guān)掉MySQL。 如果有碎片文件，可能值得將所有文件復(fù)制到另一個(gè)磁盤(pán)上，清除原來(lái)的磁盤(pán)并拷回文件。 如果遇到問(wèn)題，用myisamchk或CHECK table檢查表。 用mysqladmin -i10 precesslist extended-status監(jiān)控MySQL的狀態(tài)。 用MySQL GUI客戶程序，你可以在不同的窗口內(nèi)監(jiān)控進(jìn)程列表和狀態(tài)。 使用mysqladmin debug獲得有關(guān)鎖定和性能的信息。 -------------------------------------------------------------------------------- 十二、優(yōu)化SQL 揚(yáng)SQL之長(zhǎng)，其它事情交由應(yīng)用去做。使用SQL服務(wù)器來(lái)做： 找出基于WHERE子句的行。 JOIN表 GROUP BY ORDER BY DISTINCT 不要使用SQL來(lái)做： 檢驗(yàn)數(shù)據(jù)(如日期) 成為一只計(jì)算器 技巧： 明智地使用鍵碼。 鍵碼適合搜索，但不適合索引列的插入/更新。 保持?jǐn)?shù)據(jù)為數(shù)據(jù)庫(kù)第三范式，但不要擔(dān)心冗余信息或這如果你需要更快的速度，創(chuàng)建總結(jié)表。 在大表上不做GROUP BY，相反創(chuàng)建大表的總結(jié)表并查詢(xún)它。 UPDATE table set count=count+1 where key_column=constant非?？?。 對(duì)于大表，或許最好偶爾生成總結(jié)表而不是一直保持總結(jié)表。 充分利用INSERT的默認(rèn)值。 -------------------------------------------------------------------------------- 十三、不同SQL服務(wù)器的速度差別（以秒計(jì)） +--------------------------+--------+---------+ |通過(guò)鍵碼讀取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 | | +--------------------------+--------+---------+ 在上述測(cè)試中，MySQL配置8M高速緩存運(yùn)行，其他數(shù)據(jù)庫(kù)以默認(rèn)安裝運(yùn)行。 -------------------------------------------------------------------------------- 十四、重要的MySQL啟動(dòng)選項(xiàng) back_log 如果需要大量新連接，修改它。 thread_cache_size 如果需要大量新連接，修改它。 key_buffer_size 索引頁(yè)池，可以設(shè)成很大。 bdb_cache_size BDB表使用的記錄和鍵嗎高速緩存。 table_cache 如果有很多的表和并發(fā)連接，修改它。 delay_key_write 如果需要緩存所有鍵碼寫(xiě)入，設(shè)置它。 log_slow_queries 找出需花大量時(shí)間的查詢(xún)。 max_heap_table_size 用于GROUP BY sort_buffer 用于ORDER BY和GROUP BY myisam_sort_buffer_size 用于REPAIR TABLE join_buffer_size 在進(jìn)行無(wú)鍵嗎的聯(lián)結(jié)時(shí)使用。 -------------------------------------------------------------------------------- 十五、優(yōu)化表 MySQL擁有一套豐富的類(lèi)型。你應(yīng)該對(duì)每一列嘗試使用最有效的類(lèi)型。 ANALYSE過(guò)程可以幫助你找到表的最優(yōu)類(lèi)型：SELECT * FROM table_name PROCEDURE ANALYSE()。 對(duì)于不保存NULL值的列使用NOT NULL，這對(duì)你想索引的列尤其重要。 將ISAM類(lèi)型的表改為MyISAM。 如果可能，用固定的表格式創(chuàng)建表。 不要索引你不想用的東西。 利用MySQL能按一個(gè)索引的前綴進(jìn)行查詢(xún)的事實(shí)。如果你有索引INDEX(a,b)，你不需要在a上的索引。 不在長(zhǎng)CHAR/VARCHAR列上創(chuàng)建索引，而只索引列的一個(gè)前綴以節(jié)省存儲(chǔ)空間。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10))) 對(duì)每個(gè)表使用最有效的表格式。 在不同表中保存相同信息的列應(yīng)該有同樣的定義并具有相同的列名。 -------------------------------------------------------------------------------- 十六、MySQL如何次存儲(chǔ)數(shù)據(jù) 數(shù)據(jù)庫(kù)以目錄存儲(chǔ)。 表以文件存儲(chǔ)。 列以變長(zhǎng)或定長(zhǎng)格式存儲(chǔ)在文件中。對(duì)BDB表，數(shù)據(jù)以頁(yè)面形式存儲(chǔ)。 支持基于內(nèi)存的表。 數(shù)據(jù)庫(kù)和表可在不同的磁盤(pán)上用符號(hào)連接起來(lái)。 在Windows上，MySQL支持用.sym文件內(nèi)部符號(hào)連接數(shù)據(jù)庫(kù)。 -------------------------------------------------------------------------------- 十七、MySQL表類(lèi)型 HEAP表：固定行長(zhǎng)的表，只存儲(chǔ)在內(nèi)存中并用HASH索引進(jìn)行索引。 ISAM表：MySQL 3.22中的早期B-tree表格式。 MyIASM：IASM表的新版本，有如下擴(kuò)展： 二進(jìn)制層次的可移植性。 NULL列索引。 對(duì)變長(zhǎng)行比ISAM表有更少的碎片。 支持大文件。 更好的索引壓縮。 更好的鍵嗎統(tǒng)計(jì)分布。 更好和更快的auto_increment處理。 來(lái)自Sleepcat的Berkeley DB(BDB)表：事務(wù)安全(有BEGIN WORK/COMMIT|ROLLBACK)。 -------------------------------------------------------------------------------- 十八、MySQL行類(lèi)型（專(zhuān)指IASM/MyIASM表） 如果所有列是定長(zhǎng)格式(沒(méi)有VARCHAR、BLOB或TEXT)，MySQL將以定長(zhǎng)表格式創(chuàng)建表，否則表以動(dòng)態(tài)長(zhǎng)度格式創(chuàng)建。 定長(zhǎng)格式比動(dòng)態(tài)長(zhǎng)度格式快很多并更安全。 動(dòng)態(tài)長(zhǎng)度行格式一般占用較少的存儲(chǔ)空間，但如果表頻繁更新，會(huì)產(chǎn)生碎片。 在某些情況下，不值得將所有VARCHAR、BLOB和TEXT列轉(zhuǎn)移到另一個(gè)表中，只是獲得主表上的更快速度。 利用myiasmchk（對(duì)ISAM，pack_iasm），可以創(chuàng)建只讀壓縮表，這使磁盤(pán)使用率最小，但使用慢速磁盤(pán)時(shí)，這非常不錯(cuò)。壓縮表充分地利用將不再更新的日志表 -------------------------------------------------------------------------------- 十九、MySQL高速緩存（所有線程共享，一次性分配） 鍵碼緩存：key_buffer_size，默認(rèn)8M。 表緩存：table_cache，默認(rèn)64。 線程緩存：thread_cache_size，默認(rèn)0。 主機(jī)名緩存：可在編譯時(shí)修改，默認(rèn)128。 內(nèi)存映射表：目前僅用于壓縮表。 注意：MySQL沒(méi)有運(yùn)行高速緩存，而讓操作系統(tǒng)處理。 -------------------------------------------------------------------------------- 二十、MySQL緩存區(qū)變量（非共享，按需分配） sort_buffer：ORDER BY/GROUP BY record_buffer：掃描表。 join_buffer_size：無(wú)鍵聯(lián)結(jié) myisam_sort_buffer_size：REPAIR TABLE net_buffer_length:對(duì)于讀SQL語(yǔ)句并緩存結(jié)果。 tmp_table_size：臨時(shí)結(jié)果的HEAP表大小。 -------------------------------------------------------------------------------- 二十一、MySQL表高速緩存工作原理 每個(gè)MyISAM表的打開(kāi)實(shí)例(instance)使用一個(gè)索引文件和一個(gè)數(shù)據(jù)文件。如果表被兩個(gè)線程使用或在同一條查詢(xún)中使用兩次，MyIASM將共享索引文件而是打開(kāi)數(shù)據(jù)文件的另一個(gè)實(shí)例。 如果所有在高速緩存中的表都在使用，緩存將臨時(shí)增加到比表緩存尺寸大些。如果是這樣，下一個(gè)被釋放的表將被關(guān)閉。 你可以通過(guò)檢查mysqld的Opened_tables變量以檢查表緩存是否太小。如果該值太高，你應(yīng)該增大表高速緩存。 -------------------------------------------------------------------------------- 二十二、MySQL擴(kuò)展/優(yōu)化-提供更快的速度 使用優(yōu)化的表類(lèi)型（HEAP、MyIASM或BDB表）。 對(duì)數(shù)據(jù)使用優(yōu)化的列。 如果可能使用定長(zhǎng)行。 使用不同的鎖定類(lèi)型（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可在某些情況下使用鍵碼。 如果只查詢(xún)?cè)谝粋€(gè)索引中的列，將只使用索引樹(shù)解決查詢(xún)。 聯(lián)結(jié)一般比子查詢(xún)快（對(duì)大多數(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可同時(shí)運(yùn)行。 UDF函數(shù)可裝載進(jìn)一個(gè)正在運(yùn)行的服務(wù)器。 壓縮只讀表。 CREATE TEMPORARY TABLE CREATE TABLE .. SELECT 帶RAID選項(xiàng)的MyIASM表將文件分割成很多文件以突破某些文件系統(tǒng)的2G限制。 Delay_keys 復(fù)制功能 -------------------------------------------------------------------------------- 二十二、MySQL何時(shí)使用索引 對(duì)一個(gè)鍵碼使用>, >=, =, <, <=, IF NULL和BETWEEN SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; SELECT * FROM table_name WHERE key_part1 IS NULL; 當(dāng)使用不以通配符開(kāi)始的LIKE SELECT * FROM table_name WHERE key_part1 LIKE ‘jani%‘ 在進(jìn)行聯(lián)結(jié)時(shí)從另一個(gè)表中提取行時(shí) SELECT * from t1,t2 where t1.col=t2.key_part 找出指定索引的MAX()或MIN()值 SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 一個(gè)鍵碼的前綴使用ORDER BY或GROUP BY SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 在所有用在查詢(xún)中的列是鍵碼的一部分時(shí)間 SELECT key_part3 FROM table_name WHERE key_part1=1 -------------------------------------------------------------------------------- 二十三、MySQL何時(shí)不使用索引 如果MySQL能估計(jì)出它將可能比掃描整張表還要快時(shí)，則不使用索引。例如如果key_part1均勻分布在1和100之間，下列查詢(xún)中使用索引就不是很好： SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 如果使用HEAP表且不用=搜索所有鍵碼部分。 在HEAP表上使用ORDER BY。 如果不是用鍵碼第一部分 SELECT * FROM table_name WHERE key_part2=1 如果使用以一個(gè)通配符開(kāi)始的LIKE SELECT * FROM table_name WHERE key_part1 LIKE ‘%jani%‘ 搜索一個(gè)索引而在另一個(gè)索引上做ORDER BY SELECT * from table_name WHERE key_part1 = # ORDER BY key2 -------------------------------------------------------------------------------- 二十四、學(xué)會(huì)使用EXPLAIN 對(duì)于每一條你認(rèn)為太慢的查詢(xún)使用EXPLAIN! mysql> explain select t3.DateOfAction, t1.TransactionID -> from t1 join t2 join t3 -> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID -> order 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和范圍類(lèi)型提示一個(gè)潛在的問(wèn)題。 -------------------------------------------------------------------------------- 二十五、學(xué)會(huì)使用SHOW PROCESSLIST 使用SHOW processlist來(lái)發(fā)現(xiàn)正在做什么： +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ | 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 | | 8 | monty | localhost | | Query | 0 | | show processlist | +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ 在mysql或mysqladmin中用KILL來(lái)殺死溜掉的線程。 -------------------------------------------------------------------------------- 二十六、如何知曉MySQL解決一條查詢(xún) 運(yùn)行項(xiàng)列命令并試圖弄明白其輸出： SHOW VARIABLES; SHOW COLUMNS FROM ...\G EXPLAIN SELECT ...\G FLUSH STATUS; SELECT ...; SHOW STATUS; -------------------------------------------------------------------------------- 二十七、MySQL非常不錯(cuò) 日志 在進(jìn)行很多連接時(shí)，連接非常快。 同時(shí)使用SELECT和INSERT的場(chǎng)合。 在不把更新與耗時(shí)太長(zhǎng)的選擇結(jié)合時(shí)。 在大多數(shù)選擇/更新使用唯一鍵碼時(shí)。 在使用沒(méi)有長(zhǎng)時(shí)間沖突鎖定的多個(gè)表時(shí)。 在用大表時(shí)(MySQL使用一個(gè)非常緊湊的表格式)。 -------------------------------------------------------------------------------- 二十八、MySQL應(yīng)避免的事情 用刪掉的行更新或插入表，結(jié)合要耗時(shí)長(zhǎng)的SELECT。 在能放在WHERE子句中的列上用HAVING。 不使用鍵碼或鍵碼不夠唯一而進(jìn)行JOIN。 在不同列類(lèi)型的列上JOIN。 在不使用=匹配整個(gè)鍵碼時(shí)使用HEAP表。 在MySQL監(jiān)控程序中忘記在UPDATE或DELETE中使用一條WHERE子句。如果想這樣做，使用mysql客戶程序的--i-am-a-dummy選項(xiàng)。 -------------------------------------------------------------------------------- 二十九、MySQL各種鎖定 內(nèi)部表鎖定 LOCK TABLES（所有表類(lèi)型適用） GET LOCK()/RELEASE LOCK() 頁(yè)面鎖定（對(duì)BDB表） ALTER TABLE也在BDB表上進(jìn)行表鎖定 LOCK TABLES允許一個(gè)表有多個(gè)讀者和一個(gè)寫(xiě)者。 一般WHERE鎖定具有比READ鎖定高的優(yōu)先級(jí)以避免讓寫(xiě)入方干等。對(duì)于不重要的寫(xiě)入方，可以使用LOW_PRIORITY關(guān)鍵字讓鎖定處理器優(yōu)選讀取方。 UPDATE LOW_PRIORITY SET value=10 WHERE id=10; -------------------------------------------------------------------------------- 三十、給MySQL更多信息以更好地解決問(wèn)題的技巧 注意你總能去掉(加注釋)MySQL功能以使查詢(xún)可移植： SELECT /*! SQL_BUFFER_RESULTS */ ... SELECT SQL_BUFFER_RESULTS ... 將強(qiáng)制MySQL生成一個(gè)臨時(shí)結(jié)果集。只要所有臨時(shí)結(jié)果集生成后，所有表上的鎖定均被釋放。這能在遇到表鎖定問(wèn)題時(shí)或要花很長(zhǎng)時(shí)間將結(jié)果傳給客戶端時(shí)有所幫助。 SELECT SQL_SMALL_RESULT ... GROUP BY ... 告訴優(yōu)化器結(jié)果集將只包含很少的行。 SELECT SQL_BIG_RESULT ... GROUP BY ... 告訴優(yōu)化器結(jié)果集將包含很多行。 SELECT STRAIGHT_JOIN ... 強(qiáng)制優(yōu)化器以出現(xiàn)在FROM子句中的次序聯(lián)結(jié)表。 SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2 強(qiáng)制MySQL使用/忽略列出的索引。 -------------------------------------------------------------------------------- 三十一、事務(wù)的例子 MyIASM表如何進(jìn)行事務(wù)處理: mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES; BDB表如何進(jìn)行事務(wù): mysql> BEGIN WORK; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> COMMIT; 注意你可以通過(guò)下列語(yǔ)句回避事務(wù): UPDATE customer SET value=value+new_value WHERE customer_id=some_id; -------------------------------------------------------------------------------- 三十二、使用REPLACE的例子 REPLACE的功能極像INSERT，除了如果一條老記錄在一個(gè)唯一索引上具有與新紀(jì)錄相同的值，那么老記錄在新紀(jì)錄插入前則被刪除。不使用 SELECT 1 FROM t1 WHERE key=# IF found-row LOCK TABLES t1 DELETE FROM t1 WHERE key1=# INSERT INTO t1 VALUES (...) UNLOCK TABLES t1; ENDIF 而用 REPLACE INTO t1 VALUES (...) -------------------------------------------------------------------------------- 三十三、一般技巧 使用短主鍵。聯(lián)結(jié)表時(shí)使用數(shù)字而非字符串。 當(dāng)使用多部分鍵碼時(shí)，第一部分應(yīng)該時(shí)最常用的部分。 有疑問(wèn)時(shí)，首先使用更多重復(fù)的列以獲得更好地鍵碼壓縮。 如果在同一臺(tái)機(jī)器上運(yùn)行MySQL客戶和服務(wù)器，那么在連接MySQL時(shí)則使用套接字而不是TCP/IP（這可以提高性能7.5%）?？稍谶B接MySQL服務(wù)器時(shí)不指定主機(jī)名或主機(jī)名為localhost來(lái)做到。 如果可能，使用--skip-locking(在某些OS上為默認(rèn))，這將關(guān)閉外部鎖定并將提高性能。 使用應(yīng)用層哈希值而非長(zhǎng)鍵碼： SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1=‘constant‘ AND col_2=‘constant‘ 在文件中保存需要以文件形式訪問(wèn)的BLOB，在數(shù)據(jù)庫(kù)中只保存文件名。 刪除所有行比刪除一大部分行要快。 如果SQL不夠快，研究一下訪問(wèn)數(shù)據(jù)的較底層接口。 -------------------------------------------------------------------------------- 三十四、使用MySQL 3.23的好處 MyISAM：可移植的大表格式 HEAP：內(nèi)存中的表 Berkeley DB：支持事務(wù)的表。 眾多提高的限制 動(dòng)態(tài)字符集 更多的STATUS變量 CHECK和REPAIR表 更快的GROUP BY和DISTINCT LEFT JOIN ... IF NULL的優(yōu)化 CREATE TABLE ... SELECT CREATE TEMPORARY table_name (...) 臨時(shí)HEAP表到MyISAM表的自動(dòng)轉(zhuǎn)換 復(fù)制 mysqlhotcopy腳本 -------------------------------------------------------------------------------- 三十五、正在積極開(kāi)發(fā)的重要功能 改進(jìn)事務(wù)處理 失敗安全的復(fù)制 正文搜索 多個(gè)表的刪除(之后完成多個(gè)表的更新) 更好的鍵碼緩存 原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo) 查詢(xún)高速緩存 MERGE TABLES 一個(gè)更好的GUI客戶程序 |
|
|
