當前位置：首頁 > 短網址資訊 > 正文內容

MySQL升級5.6 SQL變慢調優案例

www.virginiabusinesslawupdate.com5年前 (2017-07-21)短網址資訊1629

背景：

某業務DB從5.5升級5.6后，同一sql執行時間由毫秒級飆升到了20秒，sql文本如下

select * from big_table as t
where ( t.plan_TYPE = 1 or t.plan_TYPE=3 )
and t.limit_TYPE in (10)
and t.xx_ID = 25773208367
and t.USER_ID in (133174222100)
plan by t.gmt_create desc , t.ID desc limit 1,10

以下是解決過程：

查看短網址當前執行計劃

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: idx_xx_id,idx_gmt_create
          key: idx_gmt_create
      key_len: 17
          ref: NULL
         rows: 6816016
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

表上短網址數據庫的索引分布

  PRIMARY KEY (`id`),
  KEY `idx_xx_id` (`xx_id`,`plan_type`,`user_id`),
  ……
  KEY `idx_gmt_create` (`limit_type`,`xx_id`,`gmt_create`)

該sql選擇索引idx_gmt_create，因其符合最左前綴策略，故排序沒有使用到filesort，其訪問路徑大致如下：

反向掃描idx_gmt_create葉子結點，搜尋(limit_type,xx_id)=(10,25773208367)的元組；

回表，驗證該元組對應的主鍵記錄是否滿足約束(plan_type,user_id)=(1 or 3, 133174222100)，滿足則計數加1否則丟棄，繼續掃描下一個元組；

當計數達到10時，停止掃描，將對應的10條記錄返回給客戶端；

root@ 03:20:56>select limit_type,count(*) from big_table group by limit_type;
+-------------+----------+
| limit_type | count(*) |
+-------------+----------+
| NULL | 226865 |
| 9 | 463346 |
| 10 | 13353116 |
+-------------+----------+
3 rows in set (3.13 sec)

作為復合索引的引導列，limit_type字段的選擇性驚人的低，這是查詢變慢的主要原因之一。之所以要強調”之一”，是因為針對本例，只要其他字段足夠給力，即便limit_type=10也能很快執行完畢，查看xx_id的分布情況，也是比較畸形，該sql又很不幸的選擇了候選行最多的那個。

root@ 04:01:12>select xx_id,count(*) from big_table where limit_type =10 group by xx_id order by xx_id desc;
+-------------+----------+
| xx_id | count(*) |
+-------------+----------+
| 25773208367 | 13352433 |
| 25770261347 | 2 |
| 258809681 | 148 |
| 1 | 2100 |
+-------------+----------+
4 rows in set (5.79 sec)

如果xx_id=1，該sql最多只需要比較2100條記錄即可返回，會很快執行完畢，然后返回短網址背后對應的原始長地址；
即便xx= 25773208367，如果能快速找出滿足非索引字段約束的主鍵記錄，sql也會很快執行完畢，mysql是在驗證了海量的(limit_type,xx_id)=(10,25773208367)元組后，才湊齊10條同時滿足(plan_type,user_id)約束的主鍵記錄，據此我們可以反推出最早滿足所有約束條件的user_id，其查詢邏輯如下:
select user_id,count(*) from big_table t
where limit_type =10 and xx_id =25773208367
and ( t.plan_TYPE = 1 or t.plan_TYPE=3 )
group by user_id having count(*)>=10 order by gmt_create desc limit 1,5;
5 rows in set (1 min 12.42 sec)

執行結果:

+------------+----------+
| user_id | count(*) |
+------------+----------+
| 1851362558 | 15 |
| 2118141658 | 11 |
| 2641244918 | 14 |
| 2448823838 | 17 |
| 16375410 | 32 |
+------------+----------+
5 rows in set (1 min 12.42 sec)

隨便挑一個替換25773208367，比如1851362558，執行計劃沒有變，原本需要運行20多秒的sql卻在200毫秒內執行完畢。

而5.5版本的執行計劃為:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: idx_xx_id
          key: idx_xx_id
      key_len: 18
          ref: NULL
         rows: 116
        Extra: Using where; Using filesort

雖然采用了filesort，但是經索引idx_xx_id過濾后的候選行非常少，故執行時間很短。

解決方案

修改sql，添加force index (idx_xx_id)，此方案不夠靈活；

修改sql，將排序字段從gmt_create改為gmt_modified，因無法采用索引排序5.6會選擇idx_xx_id，此方案可能造成返回數據有誤；

修改sql，將t.limit_TYPE in (10)改為t.limit_TYPE >9 and t.limit_TYPE <11，優化器會認為sql沒有滿足索引最左前綴便不再使用idx_gmt_create,這招似乎有點賤，同時說明MySQL優化器還不夠智能。

結束語

5.6優化器做了大量改進，以本sql為例，讓其選擇了idx_gmt_create從而省去了filesort，之所以運行變慢了是因為表字段數據分布太不均勻，而本sql又湊巧滿足了各種坑，這算是一個意外吧。