一個MySQL優化案例的初步思路

今天想起這件同事處理的一個效能優化案例，當時雖然解決了，但是還是留下了幾個未解的問題，和大家一起討論一下。
首先，這個問題是根據反饋sql響應很慢，已經開始影響前端應用的登入了。稍後DBA介入，發現是由於CPU使用率過高導致，為了能夠延緩問題和進一步分析，因為資料庫中的資料量不大，直接就遷移到了另外一臺配置不錯的伺服器上，但是遷移之後，CPU配置好了很多，問題依舊，同時也在進行問題的診斷和分析。
得到的慢日誌如下，發現大多數的響應時間都耗費在了兩個SQL上，其實出自同一個儲存過程。
1、慢日誌
# Profile
# Rank Query ID           Response time   Calls R/Call  V/M   Item
# ==== ================== =============== ===== ======= ===== ============
#    1 0x26EEFEA86049462C 7667.3733 44.3%   189 40.5681  6.88 CALL p_register_check_1021e
#    2 0x6D5C3CEFC40B5E28 7518.4182 43.5%   189 39.7800  6.10 UPDATE push_list_s

兩個查詢的統計資訊如下：
# Query 1: 0.30 QPS, 12.15x concurrency, ID 0x26EEFEA86049462C at byte 976472

# This item is included in the report because it matches --limit.

# Scores: V/M = 6.88

# Time range: 2015-11-02 21:41:53 to 21:52:24

# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count          3     189

# Exec time     44   7667s      1s     90s     41s     57s     17s     45s

# Query 2: 0.30 QPS, 11.92x concurrency, ID 0x6D5C3CEFC40B5E28 at byte 1397182

# This item is included in the report because it matches --limit.

# Scores: V/M = 6.10

# Time range: 2015-11-02 21:41:53 to 21:52:24

# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count          3     189

# Exec time     43   7518s      1s     77s     40s     57s     16s     45s

# Lock time     30     65s    13us     19s   343ms    21us      2s    18us

相關的SQL語句如下

# Converted for EXPLAIN

# EXPLAIN /*!50100 PARTITIONS*/

select  APNS_PUSH_ID = `ID` from push_list_s where  APNS_PUSH_ID =  NAME_CONST('i_apnsPushId',_utf8'eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351' COLLATE 'utf8_general_ci')\G

涉及的表只有一個，表結構如下：

Create Table: CREATE TABLE `push_list_s` (

  `ID` int(10) NOT NULL AUTO_INCREMENT,

  `SN_LIST_ID` int(10) NOT NULL DEFAULT '0',

  。。。

  `APNS_PUSH_ID` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT '""',

 。。。

  PRIMARY KEY (`ID`),

  UNIQUE KEY `INDEX_SN_LIST_ID` (`SN_LIST_ID`),

  UNIQUE KEY `APNS_PUSH_ID` (`APNS_PUSH_ID`),

  KEY `INDEX_CABLE_PUSH_ID` (`CABLE_PUSH_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=2181938 DEFAULT CHARSET=utf8

整個呼叫過程的要點如下，裡面有一個update操作，欄位APNS_PUSH_ID為varchar

     IF (LENGTH(i_apnsPushId)=64) THEN

        UPDATE push_list_s SET APNS_PUSH_ID = `ID` WHERE APNS_PUSH_ID = i_apnsPushId;

     END IF;

執行的語句類似下面的形式:
UPDATE push_list_s SET APNS_PUSH_ID = `ID` WHERE APNS_PUSH_ID =  'eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351';
初步的分析懷疑是由於索引為字元過長導致，所以根據表的結構資訊，其實就是轉換到了數字型別的欄位上。
修改後的部分如下：
IF (LENGTH(i_apnsPushId)=64) THEN

        select ID into v_id from  push_list_s WHERE APNS_PUSH_ID = i_apnsPushId;

        IF (v_id > 0) THEN

            UPDATE push_list_s SET APNS_PUSH_ID = v_id WHERE ID = v_id;

        END IF;

     END IF;

這是優化前後的對比效果圖：

目前對於這個問題的疑問如下：
1.對於字元型欄位作為索引，目前來看沒有很直接的原因使得字元型索引和數字型索引存在巨大的差別。從後來我單獨得到的執行計劃和華寧復現情況來看，沒有發現存在很巨大的差別。
2.對於慢日誌中得到的語句，看到內部已經做了轉換。
UPDATE push_list_s SET APNS_PUSH_ID = `ID` WHERE APNS_PUSH_ID =  NAME_CONST('i_apnsPushId',_utf8'eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351' COLLATE 'utf8_general_ci')\G
而對於這種轉換，可能關注點都在NAME_CONST這個部分，在檢視了一些資料之後，發現在其他版本和環境中，主要是和字符集轉換有關，但是我檢視了當前環境的這些配置資訊，沒有發現有相匹配的資訊
3.關於這個問題，在5.1版本中發現了相應的bug描述，但是目前的環境是在5.6，所以應該也不是相關。
關於這個問題的進一步分析，我希望得到一些確切的資訊，能夠復現，能夠找到一些相關的bug或者相關的解決方案（除了使用數字型字元臨時替換的方案)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2092359/，如需轉載，請註明出處，否則將追究法律責任。