转载

一个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;

这是优化前后的对比效果图：

一个MySQL优化案例的初步思路

目前对于这个问题的疑问如下：
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或者相关的解决方案（除了使用数字型字符临时替换的方案)