649
技術社區[雲棲]
RDS for MySQL 字符序（collation）引發的性能問題
在幫客戶排查問題的時候，經常會遇到的 RDS 實例性能問題（比如 RDS 實例 CPU 使用率高），而其中有一類是由於字符集的字符排序規則不一致導致的。從處理的過程中可以看出來，這類問題比較容易出現但不容易定位排查，所以今天通過兩個實戰案例來分析的下“RDS for MySQL 字符序（collation）引發的性能問題”。
首先介紹下背景知識： 字符集 和 字符序。
1. 字符集（character set）和字符序（collation）
字符集是一組符號和編碼，用來保存和解釋 MySQL 的字符類型數據，比如 varchar 類型的數據。
字符序是一組在指定字符集中進行字符比較的規則，比如是否忽略大小寫，是否按二進製比較字符等等。
2. 字符序基本比較規則
兩組字符類型數據進行比較，需要一致的字符集（character set）和 字符序（collation），否則需要進行隱式轉換。
3. 實戰案例分析
-
案例分析一：實例 CPU 使用率達到 100%，業務響應時間長，影響使用體驗。
問題原因定位到一條普通查詢語句：
select
aid, ip, adid, openudid
from
`tab01`
where
`reg_time` between '2016-10-12 00:00:00' and '2016-10-12 23:59:59'該語句在上線前通過 MySQL 命令行進行過測試，執行時間在 20 MS（毫秒）左右。
但在生產環境由 PHP Lavravel 框架提交執行需要 20 Sec（秒）以上才可以完成； 大量該類型查詢執行導致連接堆積，RDS 實例 CPU 使用率 100%。
首先在 MySQL 命令行下，檢查表結構：
CREATE TABLE `tab01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ip` char(15) NOT NULL,
`aid` bigint(20) NOT NULL,
`adid` varchar(255) NOT NULL,
`openudid` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `reg_time` (`reg_time`),
KEY `aid` (`aid`),
KEY `adid` (`adid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11964136 DEFAULT CHARSET=utf8檢查執行計劃，未見異常：
請用戶協助捕捉 PHP Laravel 框架提交查詢的網絡通信過程：
在網絡交互過程中，發現應用在連接建立後執行了下麵的語句，然後間隔部分其他查詢後才執行的上述查詢：
set names utf8 collate utf8_unicode_ci;那麼這條命令具體修改了什麼，可以通過 MySQL 命令行連接來模擬驗證下 ：
可以看到，該條命令將連接的字符序（collation_connection）從 utf8_general_ci （默認值）修改為 utf8_unicode_ci ；而表中數據使用的是默認字符序（utf8_general_ci，在表的 create 定義語句中如果沒有指定，則使用字符集的默認字符序），兩者並不相同。
注：
RDS for MySQL 支持的字符序可以通過下麵的命令獲取：
-- 查看 RDS for MySQL 支持的所有字符序
show collation;
-- 查看 RDS for MySQL 支持的某一字符集對應的字符序
show collation like 'utf8%'; 
在修改了字符序後，語句的執行計劃就變為全索引掃描：
請注意查詢的執行成本由 8427 改變為 13771569，增加了 1633 倍。
修改框架的字符序設置後，查詢執行時間恢複正常，RDS 實例 CPU 使用率過高的問題解決。
案例分析二：RDS 實例 CPU 使用率波動性打高，導致業務卡頓。
定位到下麵的查詢，檢查語句執行計劃，發現優化器對表 tab03 選擇了全表掃描的方式來訪問數據。
explain
SELECT
r.org_no,
r.cp_no,
r.NAME cp_name
FROM
tab02 r
LEFT JOIN tab03 a ON r.cp_no = a.cp_no
AND A.SHARD_NO = r.shard_no
WHERE
r.shard_no = '41401'
AND r.org_no LIKE '41401%'
limit 100;
+----+-------------+-------+------+---------------------------------------------+-------------------------+---------+-------+-------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------------------+-------------------------+---------+-------+-------+------------------------------------------------+
| 1 | SIMPLE | r | ref | auto_shard_key_shard_no | auto_shard_key_shard_no | 99 | const | 30637 | Using index condition; Using where |
| 1 | SIMPLE | a | ALL | R_CP_TAB03_UK,auto_shard_key_shard_no | | | | 13221 | Range checked for each record (index map: 0xA) |
+----+-------------+-------+------+---------------------------------------------+-------------------------+---------+-------+-------+------------------------------------------------+
共返回 2 行記錄,花費 2.23 ms.而表 tab03 上有合適的唯一索引 R_CP_TAB03_UK：
CREATE TABLE `tab03` (
`TERMINAL_ID` bigint(16) NOT NULL,
`CP_NO` varchar(16) NOT NULL,
`CP_NAME` varchar(256) DEFAULT NULL,
`DATA_SRC` varchar(8) DEFAULT NULL,
`IS_DIRECT` varchar(8) DEFAULT NULL,
`SHARD_NO` varchar(32) DEFAULT NULL,
PRIMARY KEY (`TERMINAL_ID`),
UNIQUE KEY `R_CP_TAB03_UK` (`CP_NO`),
KEY `auto_shard_key_shard_no` (`SHARD_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8而且 Extra 字段給出的是 Range checked for each record（index map:0xA），說明存在潛在可以使用的索引，但由於某種原因無法使用。
查看表 tab02 的定義：
CREATE TABLE `tab02` (
`cp_no` varchar(32)CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(512)CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`data_src` varchar(16)CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`shard_no` varchar(32) DEFAULT NULL,
PRIMARY KEY (`cp_no`),
KEY `auto_shard_key_shard_no` (`shard_no`),
KEY `INDX_TAB02_NAME` (`name` (255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8表 tab02 的 cp_no 字段采用 utf8_bin（按二進製比較，不忽略大小寫） 字符序，而表 tab03 的 cp_no 字段采用 utf8_general_ci（默認）字符序，兩者字符序不匹配，因此無法使用正確的索引。
修改表 tab03 的 cp_no 字段字符序為 utf8_bin，執行計劃恢複正常，RDS 實例 CPU 波動性打高的問題解決。
從以上的案例可以看到，正確的執行計劃相較調整前的執行計劃效率大約提升了 13221 倍。字符序不僅僅可以導致 CPU 使用率問題，也可能引入比如 IOPS 使用率高 等其他問題。因此建議應用開發保持統一的字符集和字符序使用規範，避免規範不統一引入性能問題。
最後更新：2017-05-17 18:01:41