Problème de requête lent causé par la conversion du type int en type varchar dans la base de données MySQL

Au cours de la semaine dernière, nous avons traité 2 requêtes lentes l'une après l'autre en raison de l'impossibilité d'utiliser l'index lors de la conversion de int en 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=utf8
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 | 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)

Il ressort clairement de ce qui précède que, parce que l'appkey est varchar, et n'ajoute pas '' à la condition Where, elle déclenchera une requête de table complète si c'est le cas. est ajouté, il peut être utilisé Index, le nombre de lignes analysées est très différent, et la pression sur le serveur et le temps de réponse sont également très différents.

Regardons un autre exemple :

*************************** 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 '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=utf8
SELECT * FROM poll_joined_151 WHERE poll_id = '2348993' 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

À partir de l'exemple ci-dessus, bien que le type de poll_id soit bigint, ' ', mais cette déclaration reste utilise l'index Bien qu'il y ait de nombreuses lignes à analyser, il est bon pour SQL d'utiliser l'index.

Pourquoi un si petit '' a-t-il un si grand impact ? La raison fondamentale est que MySQL effectue une conversion de type implicite lors de la comparaison des types de texte et des types numériques.

Ce qui suit est la description du manuel officiel 5.5 :

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.所有其他情况下，两个参数都会被转换为浮点数再进行比较

Selon la description ci-dessus, lorsque le type de valeur après la condition Where est Incohérent avec la structure de la table À ce stade, MySQL effectuera une conversion de type implicite et le convertira en nombre à virgule flottante avant de comparer.

Pour le premier cas :

Par exemple où string = 1

Besoin de convertir la chaîne dans l'index en un nombre à virgule flottante, mais comme '1', '1', '1a' seront tous convertis en 1, MySQL ne peut pas utiliser l'index et ne peut effectuer qu'une analyse complète de la table, ce qui entraîne des requêtes lentes.

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)

Dans le même temps, il convient de noter que puisqu'ils seront convertis en nombres à virgule flottante à des fins de comparaison, et que les nombres à virgule flottante ne sont que de 53 bits, lorsque la valeur maximale est dépassée, des problèmes surviendront lors de la comparaison.

Pour le deuxième cas :

Puisque l'index est basé sur un int, les chaînes numériques pures peuvent être converties en nombres à 100 %, vous pouvez donc lorsqu'un index est utilisé , bien que certaines conversions soient effectuées et que certaines ressources soient consommées, l'index est toujours utilisé à la fin et les requêtes lentes ne se produiront pas.

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!