SQL語言的設(shè)計(jì)初衷是把關(guān)系數(shù)據(jù)庫的細(xì)節(jié)隱藏起來，解耦操作邏輯與數(shù)據(jù)展示，暴露給用戶一個(gè)簡單的交互接口。嚴(yán)格來說，SQL不是編程語言而是表達(dá)式，很多非程序員也可以快速掌握。一些產(chǎn)品經(jīng)理直接通過SQL查詢運(yùn)營數(shù)據(jù)，不用等到程序員開發(fā)后臺界面。

當(dāng)數(shù)據(jù)量大的時(shí)候，優(yōu)化SQL語句和數(shù)據(jù)庫結(jié)構(gòu)才是一門學(xué)問。傳統(tǒng)關(guān)系型數(shù)據(jù)庫就像體育課上的女同學(xué)，跑兩步就氣喘吁吁 - 容量小并發(fā)低 ，常常身體不適要請假 - 約束太多。大家都會搞點(diǎn)分布式，擴(kuò)容應(yīng)用程序比數(shù)據(jù)庫要容易得多，所以傳統(tǒng)關(guān)系型數(shù)據(jù)庫的實(shí)施原則是數(shù)據(jù)庫少干活，應(yīng)用程序多干活，如下幾點(diǎn)細(xì)則：

• 充分利用但不濫用索引，須知索引也消耗磁盤和CPU。
• 不推薦使用數(shù)據(jù)庫函數(shù)格式化數(shù)據(jù)，交給應(yīng)用程序處理。
• 不推薦使用外鍵約束，用應(yīng)用程序保證數(shù)據(jù)準(zhǔn)確性。
• 寫多讀少的場景，不推薦使用唯一索引，用應(yīng)用程序保證唯一性。
• 適當(dāng)冗余字段，用應(yīng)用程序計(jì)算中間結(jié)果，用空間換時(shí)間。
• 不允許執(zhí)行極度耗時(shí)的事務(wù)，在應(yīng)用程序中拆分成更小的事務(wù)。
• 預(yù)估重要數(shù)據(jù)表（比如訂單表）的負(fù)載和數(shù)據(jù)增長態(tài)勢，做好優(yōu)化預(yù)案。

以下以MySQL為例，分享幾點(diǎn)數(shù)據(jù)庫優(yōu)化的措施。

1 MySQL配置

1.1 單表數(shù)據(jù)量

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

《阿里巴巴Java開發(fā)手冊》提出單表行數(shù)超過500萬行或者單表容量超過2GB，才推薦分庫分表。性能由綜合因素決定，拋開業(yè)務(wù)復(fù)雜度，影響程度依次是硬件配置、MySQL配置、數(shù)據(jù)表設(shè)計(jì)、索引優(yōu)化。500萬這個(gè)值僅供參考，并非鐵律。我曾經(jīng)操作過超過4億行數(shù)據(jù)的單表，分頁查詢最新的20條記錄耗時(shí)0.6秒，SQL語句大致是select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20，prePageMinId是上一頁數(shù)據(jù)記錄的最小ID。雖然當(dāng)時(shí)查詢速度還湊合，隨著數(shù)據(jù)不斷增長，有朝一日必定不堪重負(fù)。分庫分表是個(gè)周期長而風(fēng)險(xiǎn)高的大活兒，應(yīng)該盡可能在當(dāng)前結(jié)構(gòu)上優(yōu)化，比如升級硬件、遷移歷史數(shù)據(jù)等等，實(shí)在沒轍了再分。

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

在MySQL中，每個(gè)連接通常都對應(yīng)著一個(gè)線程，并發(fā)數(shù)代表著一定時(shí)間段內(nèi)，允許訪問數(shù)據(jù)庫的線程的最大數(shù)，由參數(shù) max_connections 和 max_user_connections 決定。max_connections是指數(shù)據(jù)庫實(shí)例的最大連接數(shù)，上限值是16384，max_user_connections是指每個(gè)數(shù)據(jù)庫用戶的最大連接數(shù)。MySQL會為每個(gè)連接提供緩沖區(qū)，意味著消耗更多的內(nèi)存。如果連接數(shù)設(shè)置太高硬件吃不消，太低又不能充分利用硬件。一般要求兩者比值超過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 = 100
max_used_connections = 20

1.3 慢查詢?nèi)罩?/h4>

用戶體驗(yàn)有一個(gè)3秒原則，如果用戶的操作3秒內(nèi)沒有響應(yīng)，將會厭煩甚至退出。響應(yīng)時(shí)間=客戶端UI渲染耗時(shí)+網(wǎng)絡(luò)請求耗時(shí)+應(yīng)用程序處理耗時(shí)+查詢數(shù)據(jù)庫耗時(shí)，0.5秒就是留給數(shù)據(jù)庫1/6的處理時(shí)間。建議將單次查詢耗時(shí)控制在0.5秒以內(nèi)，并且配置慢查詢?nèi)罩尽?/p>

2 數(shù)據(jù)表優(yōu)化

2.1 數(shù)據(jù)類型

盡可能采用更簡單或者占用空間更小的數(shù)據(jù)類型：

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

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

2.2 避免空值

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

2.3 優(yōu)化text類型

text字段用于儲存大量數(shù)據(jù)，容易導(dǎo)致單表容量過快膨脹，影響其他字段的查詢性能。建議抽取出來放在子表里，用業(yè)務(wù)主鍵關(guān)聯(lián)。

3 索引優(yōu)化

3.1 索引分類

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

3.2 索引優(yōu)化

• 單次查詢數(shù)據(jù)量超過30%時(shí)，優(yōu)化器認(rèn)為全表掃描比走索引更好，此時(shí)索引失效。
• 單表索引數(shù)不超過5個(gè)、單個(gè)索引字段數(shù)不超過5個(gè)。
• 字符串可使用前綴索引，前綴長度控制在5-8個(gè)字符。
• 字段唯一性太低，增加索引沒有意義，比如性別。
• 合理使用覆蓋索引，如下所示：

select login_name, nick_name from member where login_name = ?

login_name, nick_name兩個(gè)字段建立組合索引，比login_name簡單索引要更快

4 SQL優(yōu)化

4.1 分批處理

你見過魚塘挖開堤岸放水嗎？水面有各種漂浮物比如浮萍、樹葉、樹枝，浮萍總能順利通過出水口，而樹枝可能卡住出口，擋住其他物體通過。數(shù)據(jù)庫是魚塘，最大并發(fā)數(shù)就是出水口，一般的用戶SQL是浮萍，影響大量數(shù)據(jù)行的select、update操作是樹枝，舉例如下：

更新用戶所有已過期的優(yōu)惠券為不可用狀態(tài)。
update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

如果大量優(yōu)惠券需要更新為不可用狀態(tài)，執(zhí)行這條SQL可能會堵死其他SQL，分批處理偽代碼如下：

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List<Integer> 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 ++;
}

4.2 操作符優(yōu)化

通常<>操作符無法使用索引，舉例如下，查詢金額不為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 < 100 and amount > 0)

4.3 OR優(yōu)化

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

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

OR無法命中mobile_no + user_id的組合索引，可采用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字段都有索引，查詢才最高效。

4.4 IN優(yōu)化

IN適合主表大子表小，EXIST適合主表小子表大。由于查詢優(yōu)化器的不斷升級，很多場景這兩者性能差不多一樣了。舉例如下：

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';

4.5 不做列運(yùn)算

在查詢條件列運(yùn)算會導(dǎo)致索引失效，如下所示：

查詢當(dāng)日訂單
select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';

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

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

4.6 避免Select all

如果不查詢表中所有的列，避免使用SELECT *，它會進(jìn)行全表掃描，不能有效利用索引。

4.7 Like優(yōu)化

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

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

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

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

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

4.8 Join優(yōu)化

Join的原理通過驅(qū)動表的結(jié)果集作為基礎(chǔ)數(shù)據(jù)，將該結(jié)果數(shù)據(jù)作為過濾條件到下一個(gè)表中循環(huán)查詢數(shù)據(jù)，然后合并結(jié)果。如果有多個(gè)join，則將前面的結(jié)果集作為循環(huán)數(shù)據(jù)，再次到后一個(gè)表中查詢數(shù)據(jù)。

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

4.9 Limit優(yōu)化

limit用于分頁查詢時(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秒

5 NoSQL數(shù)據(jù)庫

NoSQL 數(shù)據(jù)庫通常指非關(guān)系型數(shù)據(jù)庫，是一種基于數(shù)據(jù)鍵值對存儲、高度分布式、支持動態(tài)查詢的數(shù)據(jù)管理系統(tǒng)。NoSQL 數(shù)據(jù)庫的設(shè)計(jì)目的是為了解決傳統(tǒng)關(guān)系型數(shù)據(jù)庫無法處理的大型應(yīng)用程序的數(shù)據(jù)存儲和管理問題。它們通常具有以下特點(diǎn)：

• 靈活性：NoSQL 數(shù)據(jù)庫沒有固定的表結(jié)構(gòu)和查詢語言，允許在一個(gè)數(shù)據(jù)元素里存儲不同類型的數(shù)據(jù)，從而支持靈活的數(shù)據(jù)存儲和管理。
• 可擴(kuò)展性：NoSQL 數(shù)據(jù)庫通常采用分布式存儲和并行處理技術(shù)，可以在需要時(shí)輕松擴(kuò)展以支持更大的數(shù)據(jù)量和更高的并發(fā)訪問。
• 高可用：NoSQL 數(shù)據(jù)庫通常采用多副本復(fù)制技術(shù)，以確保數(shù)據(jù)的高可用性和容錯(cuò)能力。
• 弱一致性：與傳統(tǒng)的關(guān)系型數(shù)據(jù)庫不同，NoSQL 數(shù)據(jù)庫通常采用最終一致性模型，這意味著在分布式系統(tǒng)中，數(shù)據(jù)可能不會立即同步，但在一段時(shí)間后將趨于一致。

后端開發(fā)人員不光要精通MySQL或Oracle等傳統(tǒng)關(guān)系數(shù)據(jù)庫，也要學(xué)會采用NoSQL數(shù)據(jù)庫解決特定場景下的性能瓶頸。