mysql介紹及性能優(yōu)化

MySQL介紹及性能優(yōu)化MySQL基本介紹MySQL優(yōu)化方式MySQL技巧分享Q

&

A目錄索引什么是MySQLMySQL是一個小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，開發(fā)者為瑞典MySQLAB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫。MySQL官方網(wǎng)站：誰在用MySQLMySQL歷史1979年，報表工具，數(shù)據(jù)引擎1996年，MySQL1.0(3.11.1)發(fā)布，支持SQL2000年，成立MySQLAB公司2008年1月，Sun公司以10億美元收購MySQLAB公司2009年4月，Oracle公司以74億美元收購Sun公司MySQL里程碑

3.11.1Firstpublicrelease

3.23集成BerkeleyDB,支持事務(wù)，抽象出StorageEngine

4.0集成InnoDB

4.1重大改進(jìn)，子查詢、unicode、c/s通信協(xié)議

5.0storedprocedure、view、triggers、queryoptimizer

5.1FileNDB、recordreplication......MySQL歷史1979年，報表工具，數(shù)據(jù)引擎1996年，MySQL1.0(3.11.1)發(fā)布，支持SQL2000年，成立MySQLAB公司2008年1月，Sun公司以10億美元收購MySQLAB公司2009年4月，Oracle公司以74億美元收購Sun公司MySQL存儲引擎MyISAM：默認(rèn)的MySQL插件式存儲引擎，它是在Web、數(shù)據(jù)倉儲和其他應(yīng)用環(huán)境下最常使用的存儲引擎之一。注意，通過更改STORAGE_ENGINE配置變量，能夠方便地更改MySQL服務(wù)器的默認(rèn)存儲引擎。

InnoDB：用于事務(wù)處理應(yīng)用程序，具有眾多特性

，包括ACID事務(wù)支持。

BDB：可替代InnoDB的事務(wù)引擎，支持COMMIT、ROLLBACK和其他事務(wù)特性。

Memory：將所有數(shù)據(jù)保存在RAM中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。

Merge：允許MySQLDBA或開發(fā)人員將一系列等同的MyISAM表以邏輯方式組合在一起，并作為1個對象引用它們。對于諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分適合。Archive：為大量很少引用的歷史、歸檔、或安全審計信息的存儲和檢索提供了完美的解決方案。Federated：能夠?qū)⒍鄠€分離的MySQL服務(wù)器鏈接起來，從多個物理服務(wù)器創(chuàng)建一個邏輯數(shù)據(jù)庫。十分適合于分布式環(huán)境或數(shù)據(jù)集市環(huán)境。Cluster/NDB：MySQL的簇式數(shù)據(jù)庫引擎，尤其適合于具有高性能查找要求的應(yīng)用程序，這類查找需求還要求具有最高的正常工作時間和可用性。Other：其他存儲引擎包括CSV（引用由逗號隔開的用作數(shù)據(jù)庫表的文件），Blackhole（用于臨時禁止對數(shù)據(jù)庫的應(yīng)用程序輸入），以及Example引擎（可為快速創(chuàng)建定制的插件式存儲引擎提供幫助）。

MyISAM特點(diǎn)MyISAMvsInnoDB

數(shù)據(jù)存儲方式簡單，使用B+Tree進(jìn)行索引

使用三個文件定義一個表：.MYI.MYD.frm

少碎片、支持大文件、能夠進(jìn)行索引壓縮

二進(jìn)制層次的文件可以移植(LinuxWindows)

訪問速度飛快，是所有MySQL文件引擎中速度最快的

不支持一些數(shù)據(jù)庫特性，比如事務(wù)、外鍵約束等Tablelevellock，性能稍差，更適合讀取多的操作表數(shù)據(jù)容量有限，一般建議單表數(shù)據(jù)量介于50w–200wMyISAMvsInnoDBMyISAM

索引結(jié)構(gòu)MyISAMvsInnoDBMyISAM

存儲結(jié)構(gòu)MyISAMvsInnoDBInnoDB特點(diǎn)使用TableSpace的方式來進(jìn)行數(shù)據(jù)存儲(ibdata1,ib_logfile0)

支持事務(wù)、外鍵約束等數(shù)據(jù)庫特性Rowslevellock,讀寫性能都非常優(yōu)秀

能夠承載大數(shù)據(jù)量的存儲和訪問

擁有自己獨(dú)立的緩沖池，能夠緩存數(shù)據(jù)和索引

在關(guān)閉自動提交的情況下，與MyISAM引擎速度差異不大InnoDB數(shù)據(jù)結(jié)構(gòu)MyISAMvsInnoDBMyISAMvsInnoDB性能測試數(shù)據(jù)量/單位:萬MyISAMInnoDB備注:f特殊選項(xiàng)插入：1w3秒219秒innodb_flush_log_at_trx_commit=1插入：10w29秒2092秒innodb_flush_log_at_trx_commit=1插入：100w287秒N/Ainnodb_flush_log_at_trx_commit=1插入：1w3秒3秒innodb_flush_log_at_trx_commit=0插入：10w30秒29秒innodb_flush_log_at_trx_commit=0插入：100w273秒423秒innodb_flush_log_at_trx_commit=0插入：1wN/A3秒innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=256M插入：10WN/A26秒innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=256M插入：100WN/A379秒innodb_flush_log_at_trx_commit=0

innodb_buffer_pool_size=256MMyISAMvsInnoDB性能測試測試結(jié)果可以看出在MySQL5.0里面，MyISAM和InnoDB存儲引擎性能差別并不是很大，針對InnoDB來說，影響性能的主要是

mit這個選項(xiàng)，如果設(shè)置為1的話，那么每次插入數(shù)據(jù)的時候都會自動提交，導(dǎo)致性能急劇下降，應(yīng)該是跟刷新日志有關(guān)系，設(shè)置為0效率能夠看到明顯提升，當(dāng)然，同樣你可以SQL中提交“SETAUTOCOMMIT=0”來設(shè)置達(dá)到好的性能。同時也可以看出值得使用InnoDB來替代MyISAM引擎來進(jìn)行開發(fā)，畢竟InnoDB有多數(shù)據(jù)庫特性、更良好的數(shù)據(jù)存儲性能和查詢性能MySQL優(yōu)化方式系統(tǒng)優(yōu)化：硬件、架構(gòu)服務(wù)優(yōu)化應(yīng)用優(yōu)化MySQL優(yōu)化方式影響性能的因素應(yīng)用程序查詢事務(wù)管理數(shù)據(jù)庫設(shè)計數(shù)據(jù)分布網(wǎng)絡(luò)操作系統(tǒng)硬件使用好的硬件，更快的硬盤、大內(nèi)存、多核CPU，專業(yè)的存儲服務(wù)器（NAS、SAN）設(shè)計合理架構(gòu)，如果MySQL訪問頻繁，考慮Master/Slave讀寫分離；數(shù)據(jù)庫分表、數(shù)據(jù)庫切片（分布式），也考慮使用相應(yīng)緩存服務(wù)幫助MySQL緩解訪問壓力系統(tǒng)優(yōu)化配置合理的MySQL服務(wù)器，盡量在應(yīng)用本身達(dá)到一個MySQL最合理的使用針對MyISAM或InnoDB不同引擎進(jìn)行不同定制性配置針對不同的應(yīng)用情況進(jìn)行合理配置針對f進(jìn)行配置，后面設(shè)置是針對內(nèi)存為2G的服務(wù)器進(jìn)行的合理設(shè)置服務(wù)優(yōu)化MySQL配置原則服務(wù)優(yōu)化公共選項(xiàng)選項(xiàng)缺省值推薦值說明max_connections1001024MySQL服務(wù)器同時處理的數(shù)據(jù)庫連接的最大數(shù)量query_cache_size0(不打開）16M查詢緩存區(qū)的最大長度，按照當(dāng)前需求，一倍一倍增加，本選項(xiàng)比較重要sort_buffer_size512K16M每個線程的排序緩存大小，一般按照內(nèi)存可以設(shè)置為2M以上，推薦是16M，該選項(xiàng)對排序orderby，groupby起作用record_buffer128K16M每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)，可以設(shè)置為2M以上table_cache64512為所有線程打開表的數(shù)量。增加該值能增加mysqld要求的文件描述符的數(shù)量。MySQL對每個唯一打開的表需要2個文件描述符。服務(wù)優(yōu)化MyISAM選項(xiàng)選項(xiàng)缺省值推薦值說明key_buffer_size8M256M用來存放索引區(qū)塊的緩存值,建議128M以上，不要大于內(nèi)存的30%read_buffer_size128K16M用來做MyISAM表全表掃描的緩沖大小.為從數(shù)據(jù)表順序讀取數(shù)據(jù)的讀操作保留的緩存區(qū)的長度myisam_sort_buffer_size16M128M設(shè)置,恢復(fù),修改表的時候使用的緩沖大小，值不要設(shè)的太大服務(wù)優(yōu)化InnoDB選項(xiàng)選項(xiàng)缺省值推薦值說明innodb_buffer_pool_size32M1GInnoDB使用一個緩沖池來保存索引和原始數(shù)據(jù),這里你設(shè)置越大,你在存取表里面數(shù)據(jù)時所需要的磁盤I/O越少，一般是內(nèi)存的一半，不超過2G，否則系統(tǒng)會崩潰，這個參數(shù)非常重要innodb_additional_mem_pool_size2M128MInnoDB用來保存metadata信息,如果內(nèi)存是4G，最好本值超過200Mmit10

0代表日志只大約每秒寫入日志文件并且日志文件刷新到磁盤;1為執(zhí)行完沒執(zhí)行一條SQL馬上commit;2代表日志寫入日志文件在每次提交后,但是日志文件只有大約每秒才會刷新到磁盤上.對速度影響比較大，同時也關(guān)系數(shù)據(jù)完整性innodb_log_file_size8M256M在日志組中每個日志文件的大小,一般是innodb_buffer_pool_size的25%，官方推薦是innodb_buffer_pool_size的40-50%,設(shè)置大一點(diǎn)來避免在日志文件覆寫上不必要的緩沖池刷新行為innodb_log_buffer_size128K8M用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小.推薦是8M，官方推薦該值小于16M，最好是1M-8M之間設(shè)計合理的數(shù)據(jù)表結(jié)構(gòu)：適當(dāng)?shù)臄?shù)據(jù)冗余對數(shù)據(jù)表建立合適有效的數(shù)據(jù)庫索引數(shù)據(jù)查詢：編寫簡潔高效的SQL語句應(yīng)用優(yōu)化應(yīng)用優(yōu)化方式應(yīng)用優(yōu)化表結(jié)構(gòu)設(shè)計原則

選擇合適的數(shù)據(jù)類型：如果能夠定長盡量定長

使用ENUM而不是VARCHAR,ENUM類型是非?？旌途o湊的，在實(shí)際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝?。不要使用無法加索引的類型作為關(guān)鍵字段，比如text類型為了避免聯(lián)表查詢，有時候可以適當(dāng)?shù)臄?shù)據(jù)冗余，比如

郵箱、姓名這些不容易更改的數(shù)據(jù)

選擇合適的表引擎，有時候MyISAM適合，有時候

InnoDB適合為保證查詢性能，最好每個表都建立有auto_increment

字段，建立合適的數(shù)據(jù)庫索引最好給每個字段都設(shè)定default值應(yīng)用優(yōu)化索引建立原則（一）一般針對數(shù)據(jù)分散的關(guān)鍵字進(jìn)行建立索引，比如ID、QQ，

像性別、狀態(tài)值等等建立索引沒有意義字段唯一，最少，不可為null

對大數(shù)據(jù)量表建立聚集索引，避免更新操作帶來的碎片。盡量使用短索引，一般對int、char/varchar、date/time等

類型的字段建立索引

需要的時候建立聯(lián)合索引，但是要注意查詢SQL語句的編寫

謹(jǐn)慎建立unique類型的索引（唯一索引）大文本字段不建立為索引，如果要對大文本字段進(jìn)行檢索，可以考慮全文索引頻繁更新的列不適合建立索引應(yīng)用優(yōu)化索引建立原則（二）orderby字句中的字段，where子句中字段，最常用的sql語句中字段，應(yīng)建立索引。唯一性約束，系統(tǒng)將默認(rèn)為改字段建立索引。對于只是做查詢用的數(shù)據(jù)庫索引越多越好，但對于在線實(shí)時系統(tǒng)建議控制在5個以內(nèi)。索引不僅能提高查詢SQL性能，同時也可以提高帶where字句的update，DeleteSQL性能。Decimal類型字段不要單獨(dú)建立為索引，但覆蓋索引可以包含這些字段。只有建立索引以后，表內(nèi)的行才按照特地的順序存儲，按照需要可以是asc或desc方式。如果索引由多個字段組成將最用來查詢過濾的字段放在前面可能會有更好的性能。應(yīng)用優(yōu)化MSSQL執(zhí)行順序(8)SELECT(9)DISTINCT(11)<TOP_specification><select_list>(1)FROM<left_table>(3)<join_type>JOIN<right_table>(2)ON<join_condition>(4)WHERE<where_condition>(5)GROUPBY<group_by_list>(6)WITH{CUBE|ROLLUP}(7)HAVING<having_condition>(10)ORDERBY<order_by_list>

應(yīng)用優(yōu)化編寫高效的SQL

（一）能夠快速縮小結(jié)果集的WHERE條件寫在前面，如果有恒量條件，

也盡量放在前面盡量避免使用GROUPBY、DISTINCT、OR、IN等語句的使用，

避免使用聯(lián)表查詢和子查詢，因?yàn)閷⑹箞?zhí)行效率大大下降能夠使用索引的字段盡量進(jìn)行有效的合理排列，如果使用了

聯(lián)合索引，請注意提取字段的前后順序針對索引字段使用>,>=,=,<,<=,IFNULL和BETWEEN將會使用

索引，如果對某個索引字段進(jìn)行LIKE查詢，使用LIKE‘%abc%’

不能使用索引，使用LIKE‘a(chǎn)bc%’將能夠使用索引如果在SQL里使用了MySQL部分自帶函數(shù)，索引將失效，同時將無法

使用MySQL的QueryCache，比如LEFT(),SUBSTR(),TO_DAYS()

DATE_FORMAT(),等，如果使用了OR或IN，索引也將失效使用Explain語句來幫助改進(jìn)我們的SQL語句應(yīng)用優(yōu)化編寫高效的SQL

（二）不要在where子句中的“=”左邊進(jìn)行算術(shù)或表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引盡量不要在where條件中使用函數(shù)，否則將不能使用索引避免使用select*,只取需要的字段對于大數(shù)據(jù)量的查詢，盡量避免在SQL語句中使用orderby字句，避免額為的開銷，替代為使用ADO.NET來實(shí)現(xiàn)。如果插入的數(shù)據(jù)量很大，用selectinto替代insertinto能帶來更好的性能采用連接操作，避免過多的子查詢，產(chǎn)生的CPU和IO開銷只關(guān)心需要的表和滿足條件的數(shù)據(jù)適當(dāng)使用臨時表或表變量對于連續(xù)的數(shù)值，使用between代替inwhere字句中盡量不要使用CASE條件盡量不用觸發(fā)器，特別是在大數(shù)據(jù)表上應(yīng)用優(yōu)化編寫高效的SQL

（三）更新觸發(fā)器如果不是所有情況下都需要觸發(fā)，應(yīng)根據(jù)業(yè)務(wù)需要加上必要判斷條件使用unionall操作代替OR操作，注意此時需要注意一點(diǎn)查詢條件可以使用聚集索引，如果是非聚集索引將起到相反的結(jié)果

當(dāng)只要一行數(shù)據(jù)時使用LIMIT1盡可能的使用NOTNULL填充數(shù)據(jù)庫拆分大的DELETE或INSERT語句批量提交SQL語句MySQL技巧分享MySQL技巧分享常用技巧使用Explain/DESC來分析SQL的執(zhí)行情況使用SHOWPROCESSLIST來查看當(dāng)前MySQL服務(wù)器線程

執(zhí)行情況，是否鎖表，查看相應(yīng)的SQL語句設(shè)置f中的long-query-time和log-slow-queries能夠

記錄服務(wù)器那些SQL執(zhí)行速度比較慢另外有用的幾個查詢：SHOWVARIABLES、SHOW

STATUS、SHOWENGINES

使用DESCTABLExxx來查看表結(jié)構(gòu)，使用SHOWINDEX

FROMxxx來查看表索引使用LOADDATA導(dǎo)入數(shù)據(jù)比INSERTINT