A case study on MySQL optimization

1. Background

A sql-killer process is set up on each OLTP database instance of Youzan to kill SQL statements whose execution time exceeds a certain threshold. In the afternoon, the developer received an error message that sql was killed, and helped the developer to troubleshoot. This article introduces this case.

Second scenario analysis

Table structure:

CREATE TABLE `xxx_info` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0',

  `nick_name` varchar(30) NOT NULL DEFAULT '' COMMENT 'Nickname',

  `is_del` tinyint(5) NOT NULL DEFAULT '0' COMMENT '0: data is valid, 1: data is logically deleted',

  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',

  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'modification time',

  PRIMARY KEY (`id`),

  KEY `idx_userid_groupid` (`user_id`,`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1382032 DEFAULT CHARSET=utf8mb4 ;

The problem sql is as follows

 SELECT id, name,status FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

When I first saw the SQL, I checked the table structure and index user_id, which was of numeric type and the index was ok. Then the manual execution plan did not use the idx_userid_groupid index.

It is suspected that user_id in two different types of fields causes "implicit conversion". Change the parameter values ​​to numeric type or string or use user_id=numeric type or user_id=string and execute again.

The execution plans are correct. We need to solve two problems

So why doesn't the index go through when user_id in (X,Y,Z) is of different types?

We use optimizer_trace to trace the execution plan.

set session optimizer_trace='enabled=on';

SELECT id, nick_name, is_del FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

select * from information_schema.optimizer_trace;

SELECT id, nick_name, is_del FROM xxx_info WHERE user_id IN (670039223,'373149878') AND group_id = 1 AND is_del = 0;

select * from information_schema.optimizer_trace;

set session optimizer_trace='enabled=off';

Get the execution plans of two SQL statements and compare them. The results are shown in

Seeing the results, I said

I have looked through https://bugs.mysql.com but have not found any relevant results.

How to generate different types of values ​​in the code?

The following is the developer's own test

The current solution is to communicate with the developers and ask them to do parameter type consistency checks in the program and convert them all to int/long types.

Special reminder of common scenarios where implicit conversion causes index failure

1 where the left side of the judgment symbol is a string and the right side is a value, such as

where name = 123

2 The field types of the multi-table join conditions are inconsistent, similar to 1

3 The character set types of multi-table join conditions are different. for example

a table order_no is utf8mb4, b table order_no is utf8

Friends who are interested can test more, and welcome to discuss other cases.

The above is the details of a MySQL optimization case. For more information about MySQL optimization cases, please pay attention to other related articles on 123WORDPRESS.COM!