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

[[327123]]
圖片來自 Pexels

 

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

改造這些 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