一手好 SQL 是如何煉成的？

MySQL性能

最大數據量

最大并發數

查詢耗時0.5秒

實施原則

數據表設計

數據類型

避免空值

text類型

索引優化

索引分類

優化原則

SQL優化

分批處理

不做列運算

避免Select *

操作符<>優化

OR優化

IN優化

LIKE優化

JOIN優化

LIMIT優化

其他數據庫

博主負責的項目主要采用阿里云數據庫MySQL，最近頻繁出現慢SQL告警，執行時間最長的竟然高達5分鐘。

導出日志后分析，主要原因竟然是沒有命中索引和沒有分頁處理。其實這是非常低級的錯誤，我不禁后背一涼，團隊成員的技術水平亟待提高啊。

改造這些SQL的過程中，總結了一些經驗分享給大家，如果有錯誤歡迎批評指正。

MySQL性能

最大數據量

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

《阿里巴巴Java開發手冊》提出單表行數超過500萬行或者單表容量超過2GB，才推薦分庫分表。

性能由綜合因素決定，拋開業務復雜度，影響程度依次是硬件配置、MySQL配置、數據表設計、索引優化。500萬這個值僅供參考，并非鐵律。

博主曾經操作過超過4億行數據的單表，分頁查詢最新的20條記錄耗時0.6秒

SQL語句大致是

select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20

prePageMinId是上一頁數據記錄的最小ID。

雖然當時查詢速度還湊合，隨著數據不斷增長，有朝一日必定不堪重負。

分庫分表是個周期長而風險高的大活兒，應該盡可能在當前結構上優化，比如升級硬件、遷移歷史數據等等，實在沒轍了再分。對分庫分表感興趣的同學可以閱讀分庫分表的基本思想。

最大并發數

并發數是指同一時刻數據庫能處理多少個請求，由max_connections和max_user_connections決定。

max_connections是指MySQL實例的最大連接數，上限值是16384，max_user_connections是指每個數據庫用戶的最大連接數。

MySQL會為每個連接提供緩沖區，意味著消耗更多的內存。如果連接數設置太高硬件吃不消，太低又不能充分利用硬件。

一般要求兩者比值超過10%，計算方法如下：

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

查看最大連接數與響應最大連接數：

show?variables?like?'%max_connections%';

show?variables?like?'%max_user_connections%';

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

[mysqld]

max_connections?=?100

max_used_connections?=?20

查詢耗時0.5秒

建議將單次查詢耗時控制在0.5秒以內，0.5秒是個經驗值，源于用戶體驗的3秒原則。如果用戶的操作3秒內沒有響應，將會厭煩甚至退出。

響應時間=客戶端UI渲染耗時+網絡請求耗時+應用程序處理耗時+查詢數據庫耗時，0.5秒就是留給數據庫1/6的處理時間。

實施原則

相比NoSQL數據庫，MySQL是個嬌氣脆弱的家伙。它就像體育課上的***學，一點糾紛就和同學鬧別扭(擴容難)，跑兩步就氣喘吁吁(容量小并發低)，常常身體不適要請假(SQL約束太多)。

如今大家都會搞點分布式，應用程序擴容比數據庫要容易得多，所以實施原則是數據庫少干活，應用程序多干活。

充分利用但不濫用索引，須知索引也消耗磁盤和CPU。

不推薦使用數據庫函數格式化數據，交給應用程序處理。

不推薦使用外鍵約束，用應用程序保證數據準確性。

寫多讀少的場景，不推薦使用唯一索引，用應用程序保證唯一性。

適當冗余字段，嘗試創建中間表，用應用程序計算中間結果，用空間換時間。

不允許執行極度耗時的事務，配合應用程序拆分成更小的事務。

預估重要數據表（比如訂單表）的負載和數據增長態勢，提前優化。

數據表設計

數據類型

數據類型的選擇原則：更簡單或者占用空間更小。

如果長度能夠滿足，整型盡量使用tinyint、smallint、medium_int而非int。

如果字符串長度確定，采用char類型。

如果varchar能夠滿足，不采用text類型。

精度要求較高的使用decimal類型，也可以使用BIGINT，比如精確兩位小數就乘以100后保存。

盡量采用timestamp而非datetime。

相比datetime，timestamp占用更少的空間，以UTC的格式儲存自動轉換時區。

避免空值

MySQL中字段為NULL時依然占用空間，會使索引、索引統計更加復雜。從NULL值更新到非NULL無法做到原地更新，容易發生索引分裂影響性能。

因此盡可能將NULL值用有意義的值代替，也能避免SQL語句里面包含is not null的判斷。

text類型優化

由于text字段儲存大量數據，表容量會很早漲上去，影響其他字段的查詢性能。建議抽取出來放在子表里，用業務主鍵關聯。

索引優化

索引分類

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

組合索引：多個字段上建立的索引，能夠加速復合查詢條件的檢索。

唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。

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

主鍵索引：特殊的唯一索引，用于唯一標識數據表中的某一條記錄，不允許有空值，一般用primary key約束。

全文索引：用于海量文本的查詢，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查詢精度以及擴展性不佳，更多的企業選擇elasticsearch。

索引優化

分頁查詢很重要，如果查詢數據量超過30%，MYSQL不會使用索引。

單表索引數不超過5個、單個索引字段數不超過5個。

字符串可使用前綴索引，前綴長度控制在5-8個字符。

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

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

select?login_name,?nick_name?from?member?where?login_name?=??

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

SQL優化

分批處理

博主小時候看到魚塘挖開小口子放水，水面有各種漂浮物。浮萍和樹葉總能順利通過出水口，而樹枝會擋住其他物體通過，有時還會卡住，需要人工清理。

MySQL就是魚塘，最大并發數和網絡帶寬就是出水口，用戶SQL就是漂浮物。

不帶分頁參數的查詢或者影響大量數據的update和delete操作，都是樹枝，我們要把它打散分批處理，舉例說明：

業務描述：更新用戶所有已過期的優惠券為不可用狀態。

SQL語句：

update?status=0?FROM?`coupon`?WHERE?expire_date?<=?#{currentDate}?and?status=1;

如果大量優惠券需要更新為不可用狀態，執行這條SQL可能會堵死其他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?++;

}

操作符<>優化

通常<>操作符無法使用索引，舉例如下，查詢金額不為100元的訂單：

select?id?from?orders?where?amount?!=?100;

如果金額為100的訂單極少，這種數據分布嚴重不均的情況下，有可能使用索引。

鑒于這種不確定性，采用union聚合搜索結果，改寫方法如下：

(select?id?from?orders?where?amount?>?100)

union?all

(select?id?from?orders?where?amount??0)

OR優化

在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);

此時id和product_name字段都有索引，查詢才最高效。

IN優化

IN適合主表大子表小，EXIST適合主表小子表大。由于查詢優化器的不斷升級，很多場景這兩者性能差不多一樣了。

嘗試改為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';

不做列運算

通常在查詢條件列運算會導致索引失效，如下所示：

查詢當日訂單

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

date_format函數會導致這個查詢無法使用索引，改寫后：

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

避免Select all

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

Like優化

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

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

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

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

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

Join優化

join的實現是采用Nested Loop Join算法，就是通過驅動表的結果集作為基礎數據，通過該結數據作為過濾條件到下一個表中循環查詢數據，然后合并結果。

如果有多個join，則將前面的結果集作為循環數據，再次到后一個表中查詢數據。

驅動表和被驅動表盡可能增加查詢條件，滿足ON的條件而少用Where，用小結果集驅動大結果集。

被驅動表的join字段上加上索引，無法建立索引的時候，設置足夠的Join Buffer Size。

禁止join連接三個以上的表，嘗試增加冗余字段。

Limit優化

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

select?*?from?orders?order?by?id?desc?limit?100000,10

耗時0.4秒

select?*?from?orders?order?by?id?desc?limit?1000000,10

耗時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

耗時0.5秒

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

select?id?from?orders?where?id?between?1000000?and?1000010?order?by?id?desc

耗時0.3秒

如果以上方案依然很慢呢？只好用游標了，感興趣的朋友閱讀JDBC使用游標實現分頁查詢的方法

其他數據庫

作為一名后端開發人員，務必精通作為存儲核心的MySQL或SQL Server，也要積極關注NoSQL數據庫，他們已經足夠成熟并被廣泛采用，能解決特定場景下的性能瓶頸。

參考

https://www.jianshu.com/p/6864abb4d885

MySQL SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。