公眾號(hào)關(guān)注“杰哥的IT之旅”，選擇“星標(biāo)”，重磅干貨，第一時(shí)間送達(dá)！由于公眾號(hào)的改版，為了保證你們第一時(shí)間能看到我的文章，大家記得將公眾號(hào)?加星標(biāo)置頂?哦！
今天分享一篇SQL優(yōu)化的好文，前面性能相關(guān)可以選擇性瀏覽，后面SQL優(yōu)化建議細(xì)細(xì)品讀：
本人負(fù)責(zé)的項(xiàng)目主要采用阿里云數(shù)據(jù)庫(kù) MySQL，最近頻繁出現(xiàn)慢 SQL 告警，執(zhí)行時(shí)間最長(zhǎng)的竟然高達(dá) 5 分鐘。導(dǎo)出日志后分析，主要原因竟然是沒(méi)有命中索引和沒(méi)有分頁(yè)處理。其實(shí)這是非常低級(jí)的錯(cuò)誤，我不禁后背一涼，團(tuán)隊(duì)成員的技術(shù)水平亟待提高啊。改造這些 SQL 的過(guò)程中，總結(jié)了一些經(jīng)驗(yàn)分享給大家，如果有錯(cuò)誤歡迎批評(píng)指正。①最大數(shù)據(jù)量
拋開數(shù)據(jù)量和并發(fā)數(shù)，談性能都是耍流氓。MySQL 沒(méi)有限制單表最大記錄數(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í)在沒(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ù)用戶的最大連接數(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
③查詢耗時(shí) 0.5 秒
建議將單次查詢耗時(shí)控制在 0.5 秒以內(nèi)，0.5 秒是個(gè)經(jīng)驗(yàn)值，源于用戶體驗(yàn)的 3 秒原則。如果用戶的操作 3 秒內(nèi)沒(mé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ù)類型的選擇原則，更簡(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 中字段為 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)。索引分類如下：
普通索引：最基本的索引。
組合索引：多個(gè)字段上建立的索引，能夠加速?gòu)?fù)合查詢條件的檢索。
唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。
組合唯一索引：列值的組合必須唯一。
主鍵索引：特殊的唯一索引，用于唯一標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄，不允許有空值，一般用 primary key 約束。
全文索引：用于海量文本的查詢，MySQL 5.6 之后的 InnoDB 和 MyISAM 均支持全文索引。由于查詢精度以及擴(kuò)展性不佳，更多的企業(yè)選擇 Elasticsearch。
索引優(yōu)化原則：
分頁(yè)查詢很重要，如果查詢數(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)單索引要更快。①分批處理
博主小時(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)。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?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?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 字段都有索引，查詢才最高效。④IN 優(yōu)化
IN 適合主表大子表小，EXIST 適合主表小子表大。由于查詢優(yōu)化器的不斷升級(jí)，很多場(chǎng)景這兩者性能差不多一樣了。select?id?from?orders?where?user_id?in?(select?id?from?user?where?level?=?'VIP');
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)行全表掃描，不能有效利用索引。⑦Like 優(yōu)化
Like 用于模糊查詢，舉個(gè)例子（field 已建立索引）：SELECT?column?FROM?table?WHERE?field?like?'%keyword%';
SELECT?column?FROM?table?WHERE?field?like?'keyword%';
去除了前面的 % 查詢將會(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)查詢數(shù)據(jù)，然后合并結(jié)果。如果有多個(gè) Join，則將前面的結(jié)果集作為循環(huán)數(shù)據(jù)，再次到后一個(gè)表中查詢數(shù)據(jù)。驅(qū)動(dòng)表和被驅(qū)動(dòng)表盡可能增加查詢條件，滿足 ON 的條件而少用 Where，用小結(jié)果集驅(qū)動(dòng)大結(jié)果集。被驅(qū)動(dòng)表的 Join 字段上加上索引，無(wú)法建立索引的時(shí)候，設(shè)置足夠的 Join Buffer Size。禁止 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秒
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秒
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è)查詢的方法。出處：www.cnblogs.com/xiaoyangjia/p/11267191.html??往期資源回顧 需要可自取
點(diǎn)個(gè)[在看]，是對(duì)杰哥最大的支持！
