本人負責的項目主要採用阿里雲資料庫 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 約束。
- 全文索引:用於海量文本的查詢,MySQL 5.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<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 ++;
}
②操作符 <> 優化
通常 <> 操作符無法使用索引,舉例如下,查詢金額不為 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 < 100 and 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 資料庫,他們已經足夠成熟並被廣泛採用,能解決特定場景下的性能瓶頸。
作者:編碼磚家
編輯:陶家龍
出處:www.cnblogs.com/xiaoyangjia/p/11267191.html