來源:數據管道整理

最近和很多朋友交流關於數據分析中的SQL技能需求,昨天看了這篇文章,寫的很好,給大家推薦一下。其中關於數據表設計、SQL優化部分需要重點閱讀,第一部分「MySQL性能」瞭解即可。

01 MySQL性能

1. 最大數據量

拋開數據量和併發數,談性能都是耍流氓。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。雖然當時查詢速度還湊合,隨著數據不斷增長,有朝一日必定不堪重負。分庫分表是個週期長而風險高的大活兒,應該盡可能在當前結構上優化,比如升級硬體、遷移歷史數據等等,實在沒轍了再分。

對分庫分表感興趣的同學可以閱讀分庫分表的基本思想:

https://www.cnblogs.com/jshen/p/7682502.html

2. 最大併發數

併發數是指同一時刻資料庫能處理多少個請求,由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 = 100max_used_connections = 20

3. 查詢耗時0.5秒

建議將單次查詢耗時控制在0.5秒以內,0.5秒是個經驗值,源於用戶體驗的3秒原則。如果使用者的操作3秒內沒有回應,將會厭煩甚至退出。回應時間=客戶端UI渲染耗時+網路請求耗時+應用程式處理耗時+查詢資料庫耗時,0.5秒就是留給資料庫1/6的處理時間。

4. 實施原則

相比NoSQL資料庫,MySQL是個嬌氣脆弱的傢伙。它就像體育課上的女同學,一點糾紛就和同學鬧彆扭(擴容難),跑兩步就氣喘吁吁(容量小併發低),常常身體不適要請假(SQL約束太多)。如今大家都會搞點分佈式,應用程式擴容比資料庫要容易得多,所以實施原則是資料庫少幹活,應用程式多幹活。

  • 充分利用但不濫用索引,須知索引也消耗磁片和CPU。
  • 不推薦使用資料庫函數格式化數據,交給應用程式處理。
  • 不推薦使用外鍵約束,用應用程式保證數據準確性。
  • 寫多讀少的場景,不推薦使用唯一索引,用應用程式保證唯一性。
  • 適當冗餘字段,嘗試創建中間表,用應用程式計算中間結果,用空間換時間。
  • 不允許執行極度耗時的事務,配合應用程式拆分成更小的事務。
  • 預估重要數據表(比如訂單表)的負載和數據增長態勢,提前優化。

02 數據表設計

1. 數據類型

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

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

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

2. 避免空值

MySQL中字段為NULL時依然佔用空間,會使索引、索引統計更加複雜。從NULL值更新到非NULL無法做到原地更新,容易發生索引分裂影響性能。盡可能將NULL值用有意義的值代替,也能避免SQL語句裏面包含is not null的判斷。

3. text 類型優化

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

4. 索引優化

1) 索引分類

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

組合索引:多個字段上建立的索引,能夠加速複合查詢條件的檢索。

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

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

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

全文索引:用於海量文本的查詢,MySQL5.6之後的InnoDB和MyISAM均支持全文索引。由於查詢精度以及擴展性不佳,更多的企業選擇Elasticsearch。

2)索引優化

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

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

字串可使用首碼索引,首碼長度控制在5–8個字元。

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

合理使用覆蓋索引,如下所示:

select login_name, nick_name from member where login_name = ?

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

5. SQL 優化

1)分批處理

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

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 ++;}

2)操作符<>優化

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

3)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字段都有索引,查詢才最高效。

4)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’;

5)不做列運算

通詢條件列運算會導致索引失效,如下所示:

查詢當日訂單

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

6) 避免 select all

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

7)Like 優化

like 用於模糊查詢,舉個例子(field已建立索引):

SELECT column FROM table WHERE field like ‘%keyword%’;

這個查詢未命中索引,換成下麵的寫法:

SELECT column FROM table WHERE field like ‘keyword%’;

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

8)Join 優化

join的實現是採用Nested Loop Join演算法,就是通過驅動表的結果集作為基礎數據,通過該結數據作為過濾條件到下一個表中迴圈查詢數據,然後合併結果。如果有多個join,則將前面的結果集作為迴圈數據,再次到後一個表中查詢數據。

  1. 驅動表和被驅動表盡可能增加查詢條件,滿足ON的條件而少用Where,用小結果集驅動大結果集。
  2. 被驅動表的join字段上加上索引,無法建立索引的時候,設置足夠的Join Buffer Size。
  3. 禁止join連接三個以上的表,嘗試增加冗餘字段。

9) 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使用游標實現分頁查詢的方法

https://www.cnblogs.com/firstdream/p/7732656.html

03 其他數據庫

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

回顧系列文:

我是「數據分析那些事」。常年分享數據分析乾貨,不定期分享好用的職場技能工具。按贊我的臉書,期待你與我的互動!

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store