MySQL 암시적 유형 변환 트랩 및 규칙_MySQL

머리말

암시적 유형 변환이 색인에 도달하지 못할 위험이 있다는 것은 모두가 알고 있다고 생각합니다. 동시성이 높고 데이터 양이 많은 경우 색인 누락으로 인한 결과가 매우 심각합니다. 데이터베이스가 질질 끌리고 시스템 전체가 붕괴되어 대규모 시스템에 막대한 손실을 초래하게 됩니다. 그럼 이번 글을 통해 MySQL 암시적 유형 변환 트랩과 규칙에 대해 알아보겠습니다.

1. 암시적 유형 변환 예시

오늘 프로덕션 데이터베이스에 갑자기 MySQL 스레드 수 알람이 나타났고, IOPS가 매우 높았으며, 인스턴스 세션에 다음과 유사한 SQL이 많이 나타났습니다. (관련 필드 및 값이 수정되었습니다.)

SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)

설명을 사용하여 스캔된 행 수와 인덱스 선택을 확인하세요.

mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 
and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233);
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
| id | select_type | table | type | possible_keys     | key   | key_len | ref | rows | Extra        |
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
| 1 | SIMPLE  | t_tb1 | ref | uid_type_frid,idx_corpid_qq1id | uid_type_frid | 8   | const | 1386 | Using index condition; Using where |
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
共返回 1 行记录,花费 11.52 ms.

t_tb1 테이블에는 uid_type_frid(f_col2_id,f_type) , idx_corp_id_qq1id(f_col1_id,f_qq1_id) 인덱스가 있는데 후자를 선택하면 f_qq1_id의 필터링 효과가 매우 좋을텐데 전자를 선택하게 된다. hint use index(idx_corp_id_qq1id) 사용 시:

mysql>explain extended SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 use index(idx_corpid_qq1id) WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233);
+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
| id | select_type | table | type | possible_keys  | key    | key_len | ref  | rows  | Extra        |
+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
| 1 | SIMPLE  | t_tb1 | ref | idx_corpid_qq1id | idx_corpid_qq1id | 8   | const | 2375752  | Using index condition; Using where |
+---- -+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
共返回 1 行记录,花费 17.48 ms.
mysql>show warnings;
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Level   | Code   | Message                            |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Warning   |   1739 | Cannot use range access on index 'idx_corpid_qq1id' due to type or collation conversion on field 'f_qq1_id'   |
| Note   |   1003 | /* select#1 */ select `d_dbname`.`t_tb1`.`f_col3_id` AS `f_col3_id`,`d_dbname`.`t_tb1`.`f_qq1_id` AS `f_qq1_id` from `d_dbname`.`t_tb1` USE INDEX (`idx_corpid_qq1id`) where |
|     |    | ((`d_dbname`.`t_tb1`.`f_col2_id` = 1244378) and (`d_dbname`.`t_tb1`.`f_col1_id` = 1226391) and (`d_dbname`.`t_tb1`.`f_qq1_id` in |
|     |    | (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)))          |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
共返回 2 行记录,花费 10.81 ms.

행 열이 200만 행에 도달했지만 문제도 발견되었습니다. select_type은 range 이어야 하고 key_len은 idx_corpid_qq1id 인덱스의 첫 번째 열만 사용된 것을 확인했습니다. 위의 설명은 extended을 사용하므로 경고를 표시하면 f_qq1_id 에 암시적 유형 변환이 있음을 명확하게 알 수 있습니다. f_qq1_id은 varchar이고 후속 비교 값은 정수입니다.

이 문제에 대한 해결책은 암시적 유형 변환으로 인한 제어 불가능성을 피하는 것입니다. f_qq1_id in의 내용을 문자열로 작성합니다:

mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in ('12345','23456','34567','45678','56789','67890','78901','89012','90123','901231');
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
| id | select_type | table | type | possible_keys     | key    | key_len  | ref  | rows | Extra        |
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
| 1  | SIMPLE  | t_tb1 | range | uid_type_frid,idx_corpid_qq1id | idx_corpid_qq1id | 70   |   | 40  | Using index condition; Using where |
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
共返回 1 行记录,花费 12.41 ms.

스캔 줄 수가 1386줄에서 40줄로 줄었습니다.

비슷한 경우도 있습니다.

SELECT count(0) FROM d_dbname.t_tb2 where f_col1_id= '1931231' AND f_phone in(098890);
| Warning | 1292 | Truncated incorrect DOUBLE value: '1512-98464356'

최적화 후 스캔된 행이 100만 행에서 1개로 직접 줄었습니다.

이 기회에 mysql의 암시적 유형 변환을 체계적으로 살펴보세요.

2. mysql 암시적 변환 규칙

2.1 규칙

암시적 변환 규칙을 분석해 보겠습니다.

a. 두 매개변수 중 하나 이상이 NULL 인 경우 비교 결과도 NULL입니다. 단, 를 사용하여 두 개의 NULL 을 비교할 경우에는 1이 반환됩니다. 이 두 경우에는 유형 변환이 필요하지 않습니다

b. 두 매개변수 모두 문자열이며 유형 변환 없이 문자열에 따라 비교됩니다.

c. 두 매개변수는 모두 정수이며 유형 변환 없이 정수로 비교됩니다.

d. 16진수 값을 숫자가 아닌 값과 비교할 경우 2진수 문자열로 처리됩니다

e. 한 매개변수가 TIMESTAMP 또는 DATETIME이고 다른 매개변수가 상수인 경우 상수는 timestamp
로 변환됩니다.

f. 한 매개변수가 decimal 유형입니다. 다른 매개변수가 decimal 또는 정수이면 비교를 위해 정수가 decimal 로 변환됩니다. decimal 비교를 위해 부동소수점 숫자로 변환
으로 변환됩니다.

g. 다른 모든 경우에는 비교 전에 두 매개변수가 모두 부동 소수점 숫자로 변환됩니다.

mysql> select 11 + '11', 11 + 'aa', 'a1' + 'bb', 11 + '0.01a'; 
+-----------+-----------+-------------+--------------+
| 11 + '11' | 11 + 'aa' | 'a1' + 'bb' | 11 + '0.01a' |
+-----------+-----------+-------------+--------------+
|  22 |  11 |   0 |  11.01 |
+-----------+-----------+-------------+--------------+
1 row in set, 4 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message         |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a1' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0.01a' |
+---------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> select '11a' = 11, '11.0' = 11, '11.0' = '11', NULL = 1;
+------------+-------------+---------------+----------+
| '11a' = 11 | '11.0' = 11 | '11.0' = '11' | NULL = 1 |
+------------+-------------+---------------+----------+
|   1 |   1 |    0 |  NULL |
+------------+-------------+---------------+----------+
1 row in set, 1 warning (0.01 sec)

위에서 볼 수 있듯이 11 + 'aa'는 연산자의 양쪽 유형이 다르고 조항 G를 따르기 때문에 aa를 부동 소수점 소수점으로 변환해야 하지만 변환이 실패합니다. (문자는 잘림) 0으로 변환된다고 볼 수 있는데, 정수 11은 부동소수점형이나 그 자체로 변환되므로 11 + 'aa' = 11이다.

0.01a를 double형으로 변환하면 역시 0.01로 잘려지므로 11 + '0.01a' = 11.01이 됩니다.

동등 비교도 이 점을 보여줍니다. 변환 후 '11a'와 '11.0'은 모두 11입니다. 이것이 기사 시작 부분의 예가 색인화되지 않은 이유입니다. varchar 유형 f_qq1_id. 부동 소수점 유형을 비교할 때 12345a, 12345.b 등 12345와 같은 상황이 셀 수 없이 많습니다. MySQL 옵티마이저는 인덱스가 더 효과적인지 여부를 판단할 수 없으므로 다른 솔루션을 선택합니다.

그러나 암시적 유형 변환이 발생하는 한 위와 유사한 성능 문제가 발생하게 됩니다. 결국 변환 후 인덱스를 효과적으로 선택할 수 있는지 여부에 달려 있습니다. f_id = '654321', f_mtime between '2016-05-01 00:00:00' , '2016-05-04 23:59:59'과 마찬가지로 앞의 f_id가 정수이기 때문에 인덱스 선택에 영향을 미치지 않습니다. double로 변환된 후속 문자열 유형 숫자와 비교하더라도 f_id의 값은 여전히 ​​​​될 수 있습니다. double을 기준으로 결정되며 인덱스는 여전히 효율적입니다. 후자는 e항에 따르지만 오른쪽의 상수가 변환되었기 때문이다.

개발자는 이러한 암시적 유형 변환의 함정에 빠질 수 있지만 이에 대해 주의를 기울이지 않는 경우가 많기 때문에 많은 규칙을 기억할 필요가 없으며 유형과 유형을 비교하기만 하면 됩니다.

2.2 암시적 유형 변환의 보안 문제

암시적 유형 변환은 성능 문제뿐만 아니라 보안 문제도 일으킬 수 있습니다.

mysql> desc t_account;
+-----------+-------------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra   |
+-----------+-------------+------+-----+---------+----------------+
| fid  | int(11)  | NO | PRI | NULL | auto_increment |
| fname  | varchar(20) | YES |  | NULL |    |
| fpassword | varchar(50) | YES |  | NULL |    |
+-----------+-------------+------+-----+---------+----------------+
mysql> select * from t_account;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+

假如应用前端没有WAF防护，那么下面的sql很容易注入：

mysql> select * from t_account where fname='A' ;
fname传入 A' OR 1='1 
mysql> select * from t_account where fname='A' OR 1='1';

攻击者更聪明一点： fname传入 A'+'B ，fpassword传入 ccc'+0 ：

mysql> select * from t_account where fname='A'+'B' and fpassword='ccc'+0;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+
2 rows in set, 7 warnings (0.00 sec)

总结

以上就是为大家总结的MySQL隐式类型的转换陷阱和规则，希望这篇文章对大家学习或者mysql能有所帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。