Slow query problem caused by converting int type to varchar type in MySQL database-Mysql Tutorial-php.cn

In the past week, we have processed two slow queries one after another due to the inability to use index when converting int to varchar.

CREATE TABLE `appstat_day_prototype_201305` (
`day_key` date NOT NULL DEFAULT &#39;1900-01-01&#39;,
`appkey` varchar(20) NOT NULL DEFAULT &#39;&#39;,
`user_total` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`user_activity` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`times_total` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`times_activity` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`incr_login_daily` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`unbind_total` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
`unbind_activitys` bigint(20) NOT NULL DEFAULT &#39;0&#39;,
PRIMARY KEY (`appkey`,`day_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = xxxxx and day_key between &#39;2013-05-23&#39; and &#39;2013-05-30&#39;;
+----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | appstat_day_prototype_201305 | ALL | PRIMARY | NULL | NULL | NULL | 19285787 | Using where |
+----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = &#39;xxxxx&#39; and day_key between &#39;2013-05-23&#39; and &#39;2013-05-30&#39;;
+----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | appstat_day_prototype_201305 | range | PRIMARY | PRIMARY | 65 | NULL | 1 | Using where |
+----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Copy after login

It can be clearly seen from the above that because the appkey is varchar, and not adding '' to the where condition, it will trigger a full table query. If it is added, the index can be used. The number of rows scanned is very different, and the pressure on the server and the response time are also very different.

Let’s look at another example:

*************************** 1. row ***************************
Table: poll_joined_151
Create Table: CREATE TABLE `poll_joined_151` (
`poll_id` bigint(11) NOT NULL,
`uid` bigint(11) NOT NULL,
`item_id` varchar(60) NOT NULL,
`add_time` int(11) NOT NULL DEFAULT &#39;0&#39;,
`anonymous` tinyint(1) NOT NULL DEFAULT &#39;0&#39;,
`sub_item` varchar(1200) NOT NULL DEFAULT &#39;&#39;,
KEY `idx_poll_id_uid_add_time` (`poll_id`,`uid`,`add_time`),
KEY `idx_anonymous_id_addtime` (`anonymous`,`poll_id`,`add_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT * FROM poll_joined_151 WHERE poll_id = &#39;2348993&#39; AND anonymous =0 ORDER BY add_time DESC LIMIT 0 , 3
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: poll_joined_151
type: ref
possible_keys: idx_poll_id_uid_add_time,idx_anonymous_id_addtime
key: idx_anonymous_id_addtime
key_len: 9
ref: const,const
rows: 30240
Extra: Using where

Copy after login

From the above example, although the type of poll_id is bigint, '' is added to the SQL. But this statement still uses the index. Although there are many rows to be scanned, it is good SQL to use the index.

Why does a small '' have such a big impact? The fundamental reason is that MySQL performs implicit type conversion when comparing text types and numeric types.

The following is the description of the 5.5 official manual:

If both arguments in a comparison operation are strings, they are compared as strings.
两个参数都是字符串，会按照字符串来比较，不做类型转换。
If both arguments are integers, they are compared as integers.
两个参数都是整数，按照整数来比较，不做类型转换。
Hexadecimal values are treated as binary strings if not compared to a number.
十六进制的值和非数字做比较时，会被当做二进制串。
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
In all other cases, the arguments are compared as floating-point (real) numbers.所有其他情况下，两个参数都会被转换为浮点数再进行比较

Copy after login

According to the above description, when the type of the value after the where condition is inconsistent with the table structure, MySQL will do implicit type conversion and convert it to a floating point number for comparison.

For the first case:

For example where string = 1;

Need to convert the string in the index into a floating point number, but due to '1',' 1','1a' will all be converted into 1, so MySQL cannot use the index and can only perform a full table scan, resulting in slow queries.

mysql> SELECT CAST(&#39; 1&#39; AS SIGNED)=1;
+-------------------------+
| CAST(&#39; 1&#39; AS SIGNED)=1 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(&#39; 1a&#39; AS SIGNED)=1;
+--------------------------+
| CAST(&#39; 1a&#39; AS SIGNED)=1 |
+--------------------------+
| 1 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT CAST(&#39;1&#39; AS SIGNED)=1;
+-----------------------+
| CAST(&#39;1&#39; AS SIGNED)=1 |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)

Copy after login

At the same time, it should be noted that since they will be converted into floating point numbers for comparison, and floating point numbers are only 53 bits, when the maximum value is exceeded, problems will occur in the comparison.

For the second case:

Since the index is based on int, and the purely numerical string can be converted into a number 100%, it can When an index is used, although certain conversions will be performed and certain resources are consumed, the index is still used in the end and slow queries will not occur.

mysql> select CAST( &#39;30&#39; as SIGNED) = 30;
+----------------------------+
| CAST( &#39;30&#39; as SIGNED) = 30 |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)

Copy after login

The above is the detailed content of Slow query problem caused by converting int type to varchar type in MySQL database. For more information, please follow other related articles on the PHP Chinese website!