本人負(fù)責(zé)的項(xiàng)目主要采用阿里云數(shù)據(jù)庫(kù) MySQL，最近頻繁出現(xiàn)慢 SQL 告警，執(zhí)行時(shí)間最長(zhǎng)的竟然高達(dá) 5 分鐘。

[[327123]]
圖片來(lái)自 Pexels

導(dǎo)出日志后分析，主要原因竟然是沒(méi)有命中索引和沒(méi)有分頁(yè)處理。其實(shí)這是非常低級(jí)的錯(cuò)誤，我不禁后背一涼，團(tuán)隊(duì)成員的技術(shù)水平亟待提高啊。

改造這些 SQL 的過(guò)程中，總結(jié)了一些經(jīng)驗(yàn)分享給大家，如果有錯(cuò)誤歡迎批評(píng)指正。

MySQL 性能

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

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

《阿里巴巴 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)求，由 max_connections 和 max_user_connections 決定。

max_connections 是指 MySQL 實(shí)例的最大連接數(shù)，上限值是 16384，max_user_connections 是指每個(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 = 100 
max_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)單或者占用空間更?。?/p>

• 如果長(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。

相比 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)，MySQL 5.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 login_name, nick_name from member where login_name = ? 

login_name, nick_name 兩個(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 FROM `coupon` 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<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 ++; 
} 

②操作符 <> 優(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) 
 union all 
(select id from orders where amount < 100 and amount > 0) 

③OR 優(yōu)化

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

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

OR 無(wú)法命中 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 字段都有索引，查詢(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 where date_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 limit 100000,10  
耗時(shí)0.4秒 
select * from orders order by id desc limit 1000000,10 
耗時(shí)5.2秒 

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

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秒 

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

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è)查詢(xún)的方法。

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

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

作者：編碼磚家

編輯：陶家龍

出處：www.cnblogs.com/xiaoyangjia/p/11267191.html