Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you the preliminary ideas about the case of MySQL optimization. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Today, I think of a performance optimization case handled by this colleague. Although it was solved at that time, it still left a few unsolved problems to discuss with you.

First of all, this problem is based on the feedback that the sql response is very slow and has begun to affect the login of the front-end application. Later, DBA intervened and found that it was due to the high utilization of CPU. In order to delay the problem and further analysis, because the amount of data in the database was small, it was directly migrated to another well-configured server, but after the migration, CPU was configured a lot, and the problem remained. At the same time, the problem was also being diagnosed and analyzed.

The resulting slow log is as follows, and it is found that most of the response time is spent on two SQL, which actually comes from the same stored procedure.

1. Slow log

# 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

The statistics for the two queries are as follows:

# 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: VBG 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: VBG 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

The relevant SQL statements are as follows

# Converted for EXPLAIN

# EXPLAIN / *! 50100 PARTITIONS*/

Select APNS_PUSH_ID = `ID` from push_list_s where APNS_PUSH_ID = NAME_CONST ('iDeapnsPushIdcards,' utf8' eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351' COLLATE 'utf8_general_ci')\ G

There is only one table involved, and the table structure is as follows:

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 `SN_LIST_ ID` (`SN_LIST_ ID`)

UNIQUE KEY `APNS_PUSH_ ID` (`APNS_PUSH_ ID`)

KEY `CABLE_PUSH_ ID` (`CABLE_PUSH_ ID`)

) ENGINE=InnoDB AUTO_INCREMENT=2181938 DEFAULT CHARSET=utf8

The main points of the whole calling process are as follows, in which there is a update operation and the field APNS_PUSH_ID is varchar

IF (LENGTH (i_apnsPushId) = 64) THEN

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

END IF

The statement that runs is similar to the following:

UPDATE push_list_s SET APNS_PUSH_ID = `ID`WHERE APNS_PUSH_ID = 'eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351'

The preliminary analysis suspects that the index is too long, so according to the structural information of the table, it is actually converted to a field of numeric type.

The modified part is as follows:

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

This is the comparative effect before and after optimization:

At present, the questions about this question are as follows:

1. For the character field as an index, there is no direct reason why there is a great difference between the character index and the numerical index. Judging from the implementation plan I got separately later and the recurrence of Huanning, I did not find that there was a big difference.

two。 For the statements obtained in the slow log, you can see that the conversion has been done internally.

UPDATE push_list_s SET APNS_PUSH_ID = `ID` WHERE APNS_PUSH_ID = NAME_CONST ('iDeapnsPushIdcards,' utf8' eb43f3f09940de7228a780f69d05eab0a9df98083c701e23d11c7494a980b351' COLLATE 'utf8_general_ci')\ G

For this kind of conversion, the focus may be on NAME_CONST. After looking at some materials, I found that in other versions and environments, it is mainly related to character set conversion, but I looked at the configuration information of the current environment and found no matching information.

3. On this issue, the corresponding bug description was found in version 5.1, but the current environment is 5.6, so it should not be relevant.

For further analysis of this problem, I hope to get some exact information, be able to reproduce, and find some relevant bug or related solutions (except for temporary replacement of numeric characters).

The above is the preliminary idea of the MySQL optimization case shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.