一个MySQL优化案例的初步思路(r8笔记第87天)

今天想起这件同事处理的一个性能优化案例,当时虽然解决了,但是还是留下了几个未解的问题,和大家一起讨论一下。 首先,这个问题是根据反馈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或者相关的解决方案(除了使用数字型字符临时替换的方案)

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-05-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏跟着阿笨一起玩NET

SQL Server不区分大小写的问题

  默认情况下,SQL Server不区分大小写,如果数据表TEST的TNAME列中有数据“abcd”和“Abcd”, 如果使用查询语句:select * fr...

64020
来自专栏积累沉淀

Oracle、 Mysql 、 SQLserver 分页查询

MYSQL 分页最简单了. SELECT * FROM Account  WHERE (usertype='base' or usertype=...

66850
来自专栏LanceToBigData

MySQL(九)之数据表的查询详解(SELECT语法)二

上一篇讲了比较简单的单表查询以及MySQL的组函数,这一篇给大家分享一点比较难得知识了,关于多表查询,子查询,左连接,外连接等等。希望大家能都得到帮助! 在开始...

232100
来自专栏hrscy

SQLite 基础

第1页:limit 0, 5 第2页:limit 5, 5 第3页:limit 10, 5 … 第n页:limit 5*(n-1), 5

19440
来自专栏JMCui

MySQL数据库规约.

一、建表规约 1、表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1 表示是, 0 表示否) 。 2、表...

37550
来自专栏JavaEdge

2018-08-24

函数可以嵌入在sql中使用的,可以在select中调用 存储过程要让sql的query 可以执行, 需要把 mysql_real_connect 的最后一个参...

6620
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle存储过程

Oracle存储过程 oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数...

44450
来自专栏Ryan Miao

Mybatis高级查询之关联查询

learn from:http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps 关联查询 ...

735140
来自专栏python学习路

二、Mysq(二)

内置函数 1、字符串函数 查看字符的ascii码值ascii(str),str是空串时返回0 select ascii('a'); 查看ascii码值对应的字符...

31160
来自专栏Django Scrapy

day2 oracle相关

数据库定义有三: 1 一门计算机学科 2 代表某个软件 3 某一种数据库软件产生的集合 DDL 数据库定义语句 DML 数据库操作语句 创建一张表: ...

32090

扫码关注云+社区

领取腾讯云代金券