Langsames Abfrageproblem, das durch die Konvertierung des Typs „int“ in den Typ „varchar“ in der MySQL-Datenbank verursacht wird

In der letzten Woche haben wir zwei langsame Abfragen nacheinander verarbeitet, da der Index bei der Konvertierung von int in varchar nicht verwendet werden konnte.

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)

Aus dem Obigen geht deutlich hervor, dass eine vollständige Tabellenabfrage ausgelöst wird, da der App-Schlüssel varchar ist und kein '' zur Where-Bedingung hinzugefügt wird Wenn es hinzugefügt wird, kann es verwendet werden Index, die Anzahl der gescannten Zeilen ist sehr unterschiedlich, und auch der Druck auf den Server und die Antwortzeit sind sehr unterschiedlich.

Sehen wir uns ein weiteres Beispiel an:

*************************** 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

Aus dem obigen Beispiel ist zwar der Typ poll_id bigint, '', aber diese Aussage ist immer noch vorhanden verwendet den Index. Obwohl viele Zeilen gescannt werden müssen, ist es eine gute SQL, den Index zu verwenden.

Warum hat ein so kleines „“ so eine große Wirkung? Der Hauptgrund dafür ist, dass MySQL beim Vergleich von Texttypen und numerischen Typen eine implizite Typkonvertierung durchführt.

Das Folgende ist die Beschreibung des offiziellen 5.5-Handbuchs:

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

Gemäß der obigen Beschreibung, wenn der Werttyp nach der Where-Bedingung ist stimmt nicht mit der Tabellenstruktur überein. Zu diesem Zeitpunkt führt MySQL eine implizite Typkonvertierung durch und konvertiert sie vor dem Vergleich in eine Gleitkommazahl.

Für den ersten Fall:

Beispiel: wo string = 1;

Die Zeichenfolge im Index muss in eine Gleitkommazahl umgewandelt werden. Da jedoch „1“, „1“ und „1a“ alle in 1 konvertiert werden, kann MySQL den Index nicht verwenden und kann nur einen vollständigen Tabellenscan durchführen, was zu langsamen Abfragen führt.

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)

Gleichzeitig ist zu beachten, dass sie zum Vergleich in Gleitkommazahlen umgewandelt werden und Gleitkommazahlen nur 53 Bit lang sind, wenn der Maximalwert erreicht ist Wird dieser Wert überschritten, kommt es zu Problemen beim Vergleich.

Für den zweiten Fall:

Da der Index auf int basiert, können reine numerische Zeichenfolgen zu 100 % in Zahlen umgewandelt werden, sodass Sie dies tun können, wenn ein Index verwendet wird Obwohl bestimmte Konvertierungen durchgeführt und bestimmte Ressourcen verbraucht werden, wird der Index am Ende dennoch verwendet und es treten keine langsamen Abfragen auf.

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

Das obige ist der detaillierte Inhalt vonLangsames Abfrageproblem, das durch die Konvertierung des Typs „int“ in den Typ „varchar“ in der MySQL-Datenbank verursacht wird. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!