如何寫出一手好 SQL ？很有必要！

背景

最近頻繁出現(xiàn)慢SQL告警，執(zhí)行時(shí)間最長(zhǎng)的竟然高達(dá)5分鐘。導(dǎo)出日志后分析，主要原因竟然是沒有命中索引和沒有分頁(yè)處理 。

其實(shí)這是非常低級(jí)的錯(cuò)誤，我不禁后背一涼，團(tuán)隊(duì)成員的技術(shù)水平亟待提高啊。改造這些SQL的過(guò)程中，總結(jié)了一些經(jīng)驗(yàn)分享給大家，如果有錯(cuò)誤歡迎批評(píng)指正。

MySQL性能

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

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

《阿里巴巴Java開發(fā)手冊(cè)》提出單表行數(shù)超過(guò)500萬(wàn)行或者單表容量超過(guò)2GB，才推薦分庫(kù)分表。性能由綜合因素決定，拋開業(yè)務(wù)復(fù)雜度，影響程度依次是硬件配置、MySQL配置、數(shù)據(jù)表設(shè)計(jì)、索引優(yōu)化。500萬(wàn)這個(gè)值僅供參考，并非鐵律。

博主曾經(jīng)操作過(guò)超過(guò)4億行數(shù)據(jù)的單表，分頁(yè)查詢最新的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í)查詢速度還湊合，隨著數(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í)在沒轍了再分。對(duì)分庫(kù)分表感興趣的同學(xué)可以閱讀分庫(kù)分表的基本思想。

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

并發(fā)數(shù)是指同一時(shí)刻數(shù)據(jù)庫(kù)能處理多少個(gè)請(qǐng)求，由maxconnections和maxuserconnections決定。maxconnections是指MySQL實(shí)例的最大連接數(shù)，上限值是16384，maxuser*connections是指每個(gè)數(shù)據(jù)庫(kù)用戶的最大連接數(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

查詢耗時(shí)0.5秒

建議將單次查詢耗時(shí)控制在0.5秒以內(nèi)，0.5秒是個(gè)經(jīng)驗(yàn)值，源于用戶體驗(yàn)的 3秒原則 。如果用戶的操作3秒內(nèi)沒有響應(yīng)，將會(huì)厭煩甚至退出。響應(yīng)時(shí)間=客戶端UI渲染耗時(shí)+網(wǎng)絡(luò)請(qǐng)求耗時(shí)+應(yīng)用程序處理耗時(shí)+查詢數(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)用程序多干活 。

• 充分利用但不濫用索引，須知索引也消耗磁盤和CPU。
• 不推薦使用數(shù)據(jù)庫(kù)函數(shù)格式化數(shù)據(jù)，交給應(yīng)用程序處理。
• 不推薦使用外鍵約束，用應(yīng)用程序保證數(shù)據(jù)準(zhǔn)確性。
• 寫多讀少的場(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ù)類型

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

• 如果長(zhǎng)度能夠滿足，整型盡量使用tinyint、smallint、medium_int而非int。
• 如果字符串長(zhǎng)度確定，采用char類型。
• 如果varchar能夠滿足，不采用text類型。
• 精度要求較高的使用decimal類型，也可以使用BIGINT，比如精確兩位小數(shù)就乘以100后保存。

盡量采用timestamp而非datetime。

相比datetime，timestamp占用更少的空間，以UTC的格式儲(chǔ)存自動(dòng)轉(zhuǎn)換時(shí)區(qū)。MySQL開發(fā)的 36 條軍規(guī)建議你看下。

避免空值

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

text類型優(yōu)化

由于text字段儲(chǔ)存大量數(shù)據(jù)，表容量會(huì)很早漲上去，影響其他字段的查詢性能。建議抽取出來(lái)放在子表里，用業(yè)務(wù)主鍵關(guān)聯(lián)。最新 MySQL?面試題整理好了，點(diǎn)擊Java面試庫(kù)小程序在線刷題。

索引優(yōu)化

索引分類

1. 普通索引：最基本的索引。
2. 組合索引：多個(gè)字段上建立的索引，能夠加速?gòu)?fù)合查詢條件的檢索。
3. 唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。
4. 組合唯一索引：列值的組合必須唯一。
5. 主鍵索引：特殊的唯一索引，用于唯一標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄，不允許有空值，一般用primary key約束。
6. 全文索引：用于海量文本的查詢，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查詢精度以及擴(kuò)展性不佳，更多的企業(yè)選擇Elasticsearch。

索引優(yōu)化

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

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

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

4. 字段唯一性太低，增加索引沒有意義，如：是否刪除、性別。

5. 合理使用覆蓋索引，如下所示：select loginname, nickname from member where login_name = ?

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

SQL優(yōu)化

分批處理

博主小時(shí)候看到魚塘挖開小口子放水，水面有各種漂浮物。浮萍和樹葉總能順利通過(guò)出水口，而樹枝會(huì)擋住其他物體通過(guò)，有時(shí)還會(huì)卡住，需要人工清理。MySQL就是魚塘，最大并發(fā)數(shù)和網(wǎng)絡(luò)帶寬就是出水口，用戶SQL就是漂浮物。

不帶分頁(yè)參數(shù)的查詢或者影響大量數(shù)據(jù)的update和delete操作，都是樹枝，我們要把它打散分批處理，舉例說(shuō)明：業(yè)務(wù)描述：更新用戶所有已過(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，分批處理偽代碼如下：

int?pageNo?=?1;
int?PAGE_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ú)法使用索引，舉例如下，查詢金額不為100元的訂單：select id from orders where amount != 100;如果金額為100的訂單極少，這種數(shù)據(jù)分布嚴(yán)重不均的情況下，有可能使用索引。鑒于這種不確定性，采用union聚合搜索結(jié)果，改寫方法如下：

(select?id?from?orders?where?amount?>?100)?union?all(select?id?from?orders?where?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字段都有索引，查詢才最高效。

IN優(yōu)化

1. IN適合主表大子表小，EXIST適合主表小子表大。由于查詢優(yōu)化器的不斷升級(jí)，很多場(chǎng)景這兩者性能差不多一樣了。
2. 嘗試改為join查詢，舉例如下：

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)算

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

select?id?from?order?where?date_format(create_time，'%Y-%m-%d')?=?'2019-07-01';

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

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

避免Select all

如果不查詢表中所有的列，避免使用 SELECT *，它會(huì)進(jìn)行全表掃描，不能有效利用索引。最新 MySQL 面試題整理好了，點(diǎn)擊Java面試庫(kù)小程序在線刷題。

Like優(yōu)化

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

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

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

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

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

另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺(tái)發(fā)送：面試，可以在線閱讀。

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)查詢數(shù)據(jù)，然后合并結(jié)果。如果有多個(gè)join，則將前面的結(jié)果集作為循環(huán)數(shù)據(jù)，再次到后一個(gè)表中查詢數(shù)據(jù)。

1. 驅(qū)動(dòng)表和被驅(qū)動(dòng)表盡可能增加查詢條件，滿足ON的條件而少用Where，用小結(jié)果集驅(qū)動(dòng)大結(jié)果集。
2. 被驅(qū)動(dòng)表的join字段上加上索引，無(wú)法建立索引的時(shí)候，設(shè)置足夠的Join Buffer Size。
3. 禁止join連接三個(gè)以上的表，嘗試增加冗余字段。

Limit優(yōu)化

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

select?*?from?orders?order?by?id?desc?limit?100000,10?耗時(shí)0.4秒select?*?from?orders?order?by?id?desc?limit?1000000,10耗時(shí)5.2秒

先篩選出ID縮小查詢范圍，寫法如下：

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

如果查詢條件僅有主鍵ID，寫法如下：

select?id?from?orders?where?id?between?1000000?and?1000010?order?by?id?desc耗時(shí)0.3秒

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

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

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

參考：https://www.jianshu.com/p/6864abb4d885

來(lái)源：編碼磚家 　
鏈接：cnblogs.com/xiaoyangjia/p/11267191.html