MySQL性能

最大數(shù)據(jù)量

拋開(kāi)數(shù)據(jù)量和并發(fā)數(shù)粪糙，談性能都是耍流氓?强霎。MySQL沒(méi)有限制單表最大記錄數(shù)忿项，它取決于操作系統(tǒng)對(duì)文件大小的限制。

文件系統(tǒng)單文件大小限制

FAT32最大4G

NTFS最大64GB

NTFS5.0最大2TB

EXT2塊大小為1024字節(jié)城舞，文件最大容量16GB轩触；塊大小為4096字節(jié)，文件最大容量2TB

EXT3塊大小為4KB家夺，文件最大容量為4TB

EXT4理論可以大于16TB

《阿里巴巴Java開(kāi)發(fā)手冊(cè)》提出單表行數(shù)超過(guò)500萬(wàn)行或者單表容量超過(guò)2GB脱柱，才推薦分庫(kù)分表。性能由綜合因素決定拉馋，拋開(kāi)業(yè)務(wù)復(fù)雜度榨为，影響程度依次是硬件配置惨好、MySQL配置、數(shù)據(jù)表設(shè)計(jì)随闺、索引優(yōu)化日川。500萬(wàn)這個(gè)值僅供參考，并非鐵律矩乐。博主曾經(jīng)操作過(guò)超過(guò)4億行數(shù)據(jù)的單表龄句，分頁(yè)查詢(xún)最新的20條記錄耗時(shí)0.6秒，SQL語(yǔ)句大致是?select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20散罕，prePageMinId是上一頁(yè)數(shù)據(jù)記錄的最小ID分歇。雖然當(dāng)時(shí)查詢(xún)速度還湊合，隨著數(shù)據(jù)不斷增長(zhǎng)欧漱，有朝一日必定不堪重負(fù)职抡。分庫(kù)分表是個(gè)周期長(zhǎng)而風(fēng)險(xiǎn)高的大活兒，應(yīng)該盡可能在當(dāng)前結(jié)構(gòu)上優(yōu)化误甚，比如升級(jí)硬件繁调、遷移歷史數(shù)據(jù)等等，實(shí)在沒(méi)轍了再分靶草。對(duì)分庫(kù)分表感興趣的同學(xué)可以閱讀分庫(kù)分表的基本思想蹄胰。

最大并發(fā)數(shù)

并發(fā)數(shù)是指同一時(shí)刻數(shù)據(jù)庫(kù)能處理多少個(gè)請(qǐng)求，由maxconnections和maxuserconnections決定奕翔。**maxconnections是指MySQL實(shí)例的最大連接數(shù)裕寨，上限值是16384，maxuserconnections是指每個(gè)數(shù)據(jù)庫(kù)用戶(hù)的最大連接數(shù)派继。MySQL會(huì)為每個(gè)連接提供緩沖區(qū)宾袜，意味著消耗更多的內(nèi)存。如果連接數(shù)設(shè)置太高硬件吃不消驾窟，太低又不能充分利用硬件庆猫。一般要求兩者比值超過(guò)10%，計(jì)算方法如下：

max_used_connections / max_connections *100% =3/100*100% ≈3%

查看最大連接數(shù)與響應(yīng)最大連接數(shù)：

show variables like'%max_connections%';show variables like'%max_user_connections%';

在配置文件my.cnf中修改最大連接數(shù)

[mysqld]max_connections =100max_used_connections =20

查詢(xún)耗時(shí)0.5秒

建議將單次查詢(xún)耗時(shí)控制在0.5秒以?xún)?nèi)绅络，0.5秒是個(gè)經(jīng)驗(yàn)值月培，源于用戶(hù)體驗(yàn)的?3秒原則?。如果用戶(hù)的操作3秒內(nèi)沒(méi)有響應(yīng)恩急，將會(huì)厭煩甚至退出杉畜。響應(yīng)時(shí)間=客戶(hù)端UI渲染耗時(shí)+網(wǎng)絡(luò)請(qǐng)求耗時(shí)+應(yīng)用程序處理耗時(shí)+查詢(xún)數(shù)據(jù)庫(kù)耗時(shí)，0.5秒就是留給數(shù)據(jù)庫(kù)1/6的處理時(shí)間衷恭。

實(shí)施原則

相比NoSQL數(shù)據(jù)庫(kù)此叠，MySQL是個(gè)嬌氣脆弱的家伙。它就像體育課上的女同學(xué)随珠，一點(diǎn)糾紛就和同學(xué)鬧別扭(擴(kuò)容難)灭袁，跑兩步就氣喘吁吁(容量小并發(fā)低)猬错，常常身體不適要請(qǐng)假(SQL約束太多)。如今大家都會(huì)搞點(diǎn)分布式茸歧，應(yīng)用程序擴(kuò)容比數(shù)據(jù)庫(kù)要容易得多兔魂，所以實(shí)施原則是?數(shù)據(jù)庫(kù)少干活，應(yīng)用程序多干活?举娩。

充分利用但不濫用索引析校，須知索引也消耗磁盤(pán)和CPU。

不推薦使用數(shù)據(jù)庫(kù)函數(shù)格式化數(shù)據(jù)铜涉，交給應(yīng)用程序處理智玻。

不推薦使用外鍵約束，用應(yīng)用程序保證數(shù)據(jù)準(zhǔn)確性芙代。

寫(xiě)多讀少的場(chǎng)景吊奢，不推薦使用唯一索引，用應(yīng)用程序保證唯一性纹烹。

適當(dāng)冗余字段页滚，嘗試創(chuàng)建中間表，用應(yīng)用程序計(jì)算中間結(jié)果铺呵，用空間換時(shí)間裹驰。

不允許執(zhí)行極度耗時(shí)的事務(wù)，配合應(yīng)用程序拆分成更小的事務(wù)片挂。

預(yù)估重要數(shù)據(jù)表（比如訂單表）的負(fù)載和數(shù)據(jù)增長(zhǎng)態(tài)勢(shì)幻林，提前優(yōu)化。

數(shù)據(jù)表設(shè)計(jì)

數(shù)據(jù)類(lèi)型

數(shù)據(jù)類(lèi)型的選擇原則：更簡(jiǎn)單或者占用空間更小音念。

如果長(zhǎng)度能夠滿(mǎn)足沪饺，整型盡量使用tinyint、smallint闷愤、medium_int而非int整葡。

如果字符串長(zhǎng)度確定，采用char類(lèi)型讥脐。

如果varchar能夠滿(mǎn)足遭居，不采用text類(lèi)型。

精度要求較高的使用decimal類(lèi)型攘烛，也可以使用BIGINT魏滚，比如精確兩位小數(shù)就乘以100后保存。

盡量采用timestamp而非datetime坟漱。

類(lèi)型占據(jù)字節(jié)描述

datetime8字節(jié)'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999

timestamp4字節(jié)'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

相比datetime，timestamp占用更少的空間更哄，以UTC的格式儲(chǔ)存自動(dòng)轉(zhuǎn)換時(shí)區(qū)芋齿。

避免空值

MySQL中字段為NULL時(shí)依然占用空間腥寇，會(huì)使索引、索引統(tǒng)計(jì)更加復(fù)雜觅捆。從NULL值更新到非NULL無(wú)法做到原地更新赦役，容易發(fā)生索引分裂影響性能。盡可能將NULL值用有意義的值代替栅炒，也能避免SQL語(yǔ)句里面包含?is not null的判斷掂摔。

text類(lèi)型優(yōu)化

由于text字段儲(chǔ)存大量數(shù)據(jù)，表容量會(huì)很早漲上去赢赊，影響其他字段的查詢(xún)性能乙漓。建議抽取出來(lái)放在子表里，用業(yè)務(wù)主鍵關(guān)聯(lián)释移。

索引優(yōu)化

索引分類(lèi)

普通索引：最基本的索引叭披。

組合索引：多個(gè)字段上建立的索引，能夠加速?gòu)?fù)合查詢(xún)條件的檢索玩讳。

唯一索引：與普通索引類(lèi)似涩蜘，但索引列的值必須唯一，允許有空值熏纯。

組合唯一索引：列值的組合必須唯一同诫。

主鍵索引：特殊的唯一索引，用于唯一標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄樟澜，不允許有空值剩辟，一般用primary key約束。

全文索引：用于海量文本的查詢(xún)往扔，MySQL5.6之后的InnoDB和MyISAM均支持全文索引贩猎。由于查詢(xún)精度以及擴(kuò)展性不佳，更多的企業(yè)選擇Elasticsearch萍膛。

索引優(yōu)化

分頁(yè)查詢(xún)很重要吭服，如果查詢(xún)數(shù)據(jù)量超過(guò)30%，MYSQL不會(huì)使用索引蝗罗。

單表索引數(shù)不超過(guò)5個(gè)艇棕、單個(gè)索引字段數(shù)不超過(guò)5個(gè)。

字符串可使用前綴索引串塑，前綴長(zhǎng)度控制在5-8個(gè)字符沼琉。

字段唯一性太低，增加索引沒(méi)有意義桩匪，如：是否刪除打瘪、性別。

合理使用覆蓋索引，如下所示：

select loginname, nickname from member where login_name = ?

loginname, nickname兩個(gè)字段建立組合索引闺骚，比login_name簡(jiǎn)單索引要更快

SQL優(yōu)化

分批處理

博主小時(shí)候看到魚(yú)塘挖開(kāi)小口子放水彩扔，水面有各種漂浮物。浮萍和樹(shù)葉總能順利通過(guò)出水口僻爽，而樹(shù)枝會(huì)擋住其他物體通過(guò)虫碉，有時(shí)還會(huì)卡住，需要人工清理胸梆。MySQL就是魚(yú)塘敦捧，最大并發(fā)數(shù)和網(wǎng)絡(luò)帶寬就是出水口，用戶(hù)SQL就是漂浮物碰镜。不帶分頁(yè)參數(shù)的查詢(xún)或者影響大量數(shù)據(jù)的update和delete操作兢卵，都是樹(shù)枝，我們要把它打散分批處理洋措，舉例說(shuō)明：業(yè)務(wù)描述：更新用戶(hù)所有已過(guò)期的優(yōu)惠券為不可用狀態(tài)济蝉。SQL語(yǔ)句：update status=0 FROMcoupon?WHERE expire_date <= #{currentDate} and status=1;如果大量?jī)?yōu)惠券需要更新為不可用狀態(tài)，執(zhí)行這條SQL可能會(huì)堵死其他SQL菠发，分批處理偽代碼如下：

intpageNo =1;

intPAGE_SIZE =100;

while(true) {

List batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');

if(CollectionUtils.isEmpty(batchIdList)) {

return;

}

update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')

pageNo ++;

}

操作符<>優(yōu)化

通常<>操作符無(wú)法使用索引王滤，舉例如下，查詢(xún)金額不為100元的訂單：select id from orders where amount != 100;如果金額為100的訂單極少滓鸠，這種數(shù)據(jù)分布嚴(yán)重不均的情況下雁乡，有可能使用索引。鑒于這種不確定性糜俗，采用union聚合搜索結(jié)果踱稍，改寫(xiě)方法如下：

(select id from orders where amount >100)unionall(select id from orders where amount <100and amount >0)

OR優(yōu)化

在Innodb引擎下or無(wú)法使用組合索引，比如：

select id悠抹，product_name from orders where mobile_no ='13421800407'or user_id =100;

OR無(wú)法命中mobileno + userid的組合索引珠月，可采用union，如下所示：

(select id楔敌，product_name from orders where mobile_no ='13421800407') union(select id啤挎，product_name from orders where user_id =100);

此時(shí)id和product_name字段都有索引，查詢(xún)才最高效卵凑。

IN優(yōu)化

IN適合主表大子表小庆聘，EXIST適合主表小子表大。由于查詢(xún)優(yōu)化器的不斷升級(jí)勺卢，很多場(chǎng)景這兩者性能差不多一樣了伙判。

嘗試改為join查詢(xún)，舉例如下：

select id from orders where user_id in (select id from user where level = 'VIP');

采用JOIN如下所示：

select o.id from orders o left join user u on o.user_id = u.id where u.level ='VIP';

不做列運(yùn)算

通常在查詢(xún)條件列運(yùn)算會(huì)導(dǎo)致索引失效黑忱，如下所示：查詢(xún)當(dāng)日訂單

select id from order wheredate_format(create_time宴抚，'%Y-%m-%d')='2019-07-01';

date_format函數(shù)會(huì)導(dǎo)致這個(gè)查詢(xún)無(wú)法使用索引勒魔，改寫(xiě)后：

select id from order where create_time between'2019-07-01 00:00:00'and'2019-07-01 23:59:59';

避免Select all

如果不查詢(xún)表中所有的列，避免使用?SELECT *酱塔，它會(huì)進(jìn)行全表掃描沥邻，不能有效利用索引危虱。

Like優(yōu)化

like用于模糊查詢(xún)羊娃，舉個(gè)例子（field已建立索引）：

SELECT column FROM table WHERE field like'%keyword%';

這個(gè)查詢(xún)未命中索引，換成下面的寫(xiě)法：

SELECT column FROM table WHERE field like'keyword%';

去除了前面的%查詢(xún)將會(huì)命中索引埃跷，但是產(chǎn)品經(jīng)理一定要前后模糊匹配呢蕊玷？全文索引fulltext可以嘗試一下，但Elasticsearch才是終極武器弥雹。

Join優(yōu)化

join的實(shí)現(xiàn)是采用Nested Loop Join算法垃帅，就是通過(guò)驅(qū)動(dòng)表的結(jié)果集作為基礎(chǔ)數(shù)據(jù)，通過(guò)該結(jié)數(shù)據(jù)作為過(guò)濾條件到下一個(gè)表中循環(huán)查詢(xún)數(shù)據(jù)剪勿，然后合并結(jié)果贸诚。如果有多個(gè)join，則將前面的結(jié)果集作為循環(huán)數(shù)據(jù)厕吉，再次到后一個(gè)表中查詢(xún)數(shù)據(jù)酱固。

驅(qū)動(dòng)表和被驅(qū)動(dòng)表盡可能增加查詢(xún)條件，滿(mǎn)足ON的條件而少用Where头朱，用小結(jié)果集驅(qū)動(dòng)大結(jié)果集运悲。

被驅(qū)動(dòng)表的join字段上加上索引，無(wú)法建立索引的時(shí)候项钮，設(shè)置足夠的Join Buffer Size班眯。

禁止join連接三個(gè)以上的表，嘗試增加冗余字段烁巫。

Limit優(yōu)化

limit用于分頁(yè)查詢(xún)時(shí)越往后翻性能越差署隘，解決的原則：縮小掃描范圍?，如下所示：

select * from orders order by id desc limit100000,10耗時(shí)0.4秒select * from orders order by id desc limit1000000,10耗時(shí)5.2秒

先篩選出ID縮小查詢(xún)范圍亚隙，寫(xiě)法如下：

select * from orders where id > (select id from orders order by id desc? limit1000000,1) order by id desc limit0,10耗時(shí)0.5秒

如果查詢(xún)條件僅有主鍵ID磁餐，寫(xiě)法如下：

select id from orders where id between1000000and1000010order by id desc耗時(shí)0.3秒

如果以上方案依然很慢呢？只好用游標(biāo)了恃鞋，感興趣的朋友閱讀JDBC使用游標(biāo)實(shí)現(xiàn)分頁(yè)查詢(xún)的方法

其他數(shù)據(jù)庫(kù)

作為一名后端開(kāi)發(fā)人員崖媚，務(wù)必精通作為存儲(chǔ)核心的MySQL或SQL Server，也要積極關(guān)注NoSQL數(shù)據(jù)庫(kù)恤浪，他們已經(jīng)足夠成熟并被廣泛采用畅哑，能解決特定場(chǎng)景下的性能瓶頸。

分類(lèi)數(shù)據(jù)庫(kù)特性

鍵值型Memcache用于內(nèi)容緩存水由，大量數(shù)據(jù)的高訪(fǎng)問(wèn)負(fù)載

鍵值型Redis用于內(nèi)容緩存荠呐，比Memcache支持更多的數(shù)據(jù)類(lèi)型，并能持久化數(shù)據(jù)

列式存儲(chǔ)HBaseHadoop體系的核心數(shù)據(jù)庫(kù)，海量結(jié)構(gòu)化數(shù)據(jù)存儲(chǔ)泥张，大數(shù)據(jù)必備呵恢。

文檔型MongoDb知名文檔型數(shù)據(jù)庫(kù)，也可以用于緩存

文檔型CouchDBApache的開(kāi)源項(xiàng)目媚创，專(zhuān)注于易用性渗钉，支持REST API

文檔型SequoiaDB國(guó)內(nèi)知名文檔型數(shù)據(jù)庫(kù)

圖形Neo4J用于社交網(wǎng)絡(luò)構(gòu)建關(guān)系圖譜，推薦