MySQL 데이터베이스에서 int 유형을 varchar 유형으로 변환하면 쿼리가 느려지는 문제가 발생합니다.

지난 한 주 동안 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비트에 불과하므로 주의해야 합니다. , 비교에 문제가 발생합니다.

두 번째 경우:

인덱스가 int 기반이므로 순수 숫자열은 100% 숫자로 변환이 가능하므로 인덱스를 사용할 때 , 특정 변환이 수행되고 특정 리소스가 소비되지만 결국 인덱스는 계속 사용되며 느린 쿼리는 발생하지 않습니다.

아아아아

위 내용은 MySQL 데이터베이스에서 int 유형을 varchar 유형으로 변환하면 쿼리가 느려지는 문제가 발생합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!