MySQL データベースで int 型を varchar 型に変換することによって発生するクエリの遅延の問題

過去 1 週間で、int を varchar に変換するときに index を使用できないため、2 つの遅いクエリを次々に処理してきました。

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)

appkey は varchar であり、where 条件に '' を追加しないため、追加された場合は完全なテーブル クエリがトリガーされることが明確にわかります。スキャンされる行数は膨大です。当然、サーバーへの負荷と応答時間は場所によって大きく異なります。

別の例を見てみましょう:

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

上記の例から、poll_id タイプは bigint ですが、SQL に '' が追加されていますが、このステートメントは引き続きインデックスを使用しますが、スキャンされた行の数は変化しません。同じものはほとんどありませんが、インデックスを使用できれば、それは優れた SQL です。

なぜそのような小さな「」がこれほど大きな影響を与えるのでしょうか?基本的な理由は、MySQL がテキスト型と数値型を比較す​​るときに暗黙的な 型変換 を実行するためです。

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

上記の記述によると、Where条件以降の値の型がテーブル構造と矛盾する場合、MySQLは暗黙的な型変換を行って変換します。浮動小数点数と比較されます。

最初のケースの場合:

たとえば、string = 1;

インデックス内の文字列は浮動小数点数に変換する必要がありますが、「1」、「1」、「1a」は浮動小数点数に変換されます。すべて 1 に変換されるため、MySQL はインデックスを使用できず、テーブル全体のスキャンのみを実行できるため、クエリが遅くなります。

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)

同時に、比較のために浮動小数点数に変換されますが、浮動小数点数は53ビットしかないため、最大値を超えると比較に問題が発生することに注意してください。

2 番目のケースの場合:

インデックスは int に基づいており、純粋な数値文字列は 100% 数値に変換できるため、インデックスを使用できますが、特定の変換も実行され、一定の量が消費されます。ただし、インデックスは最終的に引き続き使用されるため、遅いクエリは発生しません。

りー

以上がMySQL データベースで int 型を varchar 型に変換することによって発生するクエリの遅延の問題の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。