MySQL database converts int to varchar for slow query, mysqlvarchar

MySQL database converts int to varchar for slow query, mysqlvarchar

Two slow queries are processed in the last week because the int type cannot be converted to varchar.

CREATE TABLE `appstat_day_prototype_201305` (`day_key` date NOT NULL DEFAULT '1900-01-01',`appkey` varchar(20) NOT NULL DEFAULT '',`user_total` bigint(20) NOT NULL DEFAULT '0',`user_activity` bigint(20) NOT NULL DEFAULT '0',`times_total` bigint(20) NOT NULL DEFAULT '0',`times_activity` bigint(20) NOT NULL DEFAULT '0',`incr_login_daily` bigint(20) NOT NULL DEFAULT '0',`unbind_total` bigint(20) NOT NULL DEFAULT '0',`unbind_activitys` bigint(20) NOT NULL DEFAULT '0',PRIMARY KEY (`appkey`,`day_key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = xxxxx and day_key between '2013-05-23' and '2013-05-30';+----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+| 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 = 'xxxxx' and day_key between '2013-05-23' and '2013-05-30';+----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+| 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) 

From the above, we can see that because the appkey is varchar, and ''' is not added in the where condition, a full table query is triggered, and an index can be used when it is added, the number of rows scanned here is different in days. The load and response time on the server are naturally different in days.

Let's look at another example:

*************************** 1. row ***************************Table: poll_joined_151Create 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 '0',`anonymous` tinyint(1) NOT NULL DEFAULT '0',`sub_item` varchar(1200) NOT NULL DEFAULT '',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=utf8SELECT * FROM poll_joined_151 WHERE poll_id = '2348993' AND anonymous =0 ORDER BY add_time DESC LIMIT 0 , 3*************************** 1. row ***************************id: 1select_type: SIMPLEtable: poll_joined_151type: refpossible_keys: idx_poll_id_uid_add_time,idx_anonymous_id_addtimekey: idx_anonymous_id_addtimekey_len: 9ref: const,constrows: 30240Extra: Using where 

As shown in the preceding example, although the poll_id type is bigint, ''is added to the SQL statement, but the index is still used. Although there are many rows to scan, however, using indexes is a good SQL statement.

So why is there such a big impact on a small? The root cause is that MySQL performs implicit type conversion when comparing text and numeric types.

Instructions in the 5.5 official manual are as follows:

If both arguments in a comparison operation are strings, they are compared as strings. both parameters are strings and will be compared by strings without type conversion. If both arguments are integers, they are compared as integers. both parameters are integers, which are compared by integers without type conversion. Hexadecimal values are treated as binary strings if not compared to a number. When the Hexadecimal value is compared with a non-numeric value, it is treated as a binary string. 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 already med. 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. one parameter is TIMESTAMP or DATETIME, And the other parameter is a constant, the constant will be converted to timestampIf 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. one parameter is of the decimal type. If another parameter is of the decimal or Integer type, the integer is converted to the decimal type for comparison. If the other parameter is of the floating point type, the decimal is converted to a floating point number for comparison In all other cases, the arguments are compared as floating-point (real) numbers. in all other cases, both parameters are converted to floating-point numbers for comparison.

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

For the first case:

For example, where string = 1;

You need to convert the string in the index to a floating point number. However, because the ratio of '1', '1', and '1a is converted to 1, MySQL cannot use the index to scan the entire table, therefore, slow queries are generated.

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

At the same time, it should be noted that, because all data will be converted to a floating point number for comparison, and the floating point number is only 53bit, when the maximum value is exceeded, the comparison will fail.

For the second case:

Because the index is built on the basis of int, and the string of pure numbers can be converted into numbers, it can be used to index, although it will also be converted to consume a certain amount of resources, however, the index is still used and no slow query is generated.

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

The above section describes the slow query caused by converting int to varchar In the MySQL database. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!