Mysql implicitly-typed conversion traps and rules _mysql

Objective

I believe we all know that implicit type conversions have the risk of being indexed, and that in the case of high concurrency and large amounts of data, the result of an index is very serious. Dragging the database down, and then crashing the entire system, has been costly for a large system. So here's a good study of the MySQL implicit type of conversion traps and rules.

1. Implicit type-conversion instances

Today, the production library has a sudden number of MySQL thread alarm, high IOPS, the instance session there are many similar to the following SQL: (modified related fields and values)

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)

With explain looked at the number of scan rows and index selection:

 Mysql>explain SELECT f_col3_id,f_qq1_id from D_dbname.t_tb1 WHERE f_col1_id=1226391 A
nd 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 | +------+---------------+---------+--------+--------------------------------+---------------+------------+------ --+--------+------------------------------------+ returns 1 rows of records, costing 11.52 Ms. 

There is an index on the t_tb1 table Uid_type_frid (f_col2_id,f_type) , idx_corp_id_qq1id (f_col1_id,f_qq1_id) , And if you choose the latter, f_qq1_id should have a good filtering effect, but you choose the former. When using 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 rows are returned, costing 17.48 Ms. Mysql>show warnings; +-----------------+----------------+------------------------------------------------------------------------------ -----------------------------------------+
| Level | Code |
message | +-----------------+----------------+--------------------------------------------------------------------------- --------------------------------------------+
|   Warning | 1739 | Cannot use range access on the 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)) | +-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ returns 2 Line records, Cost 10.81 Ms.

The rows column is up to 200w, but the problem has been found: select_type It should be range , and it's only the key_len idx_corpid_qq1id first column of the index. The above explain used extended , so show warnings, you can see explicitly the f_qq1_id implicit type conversion: f_qq1_id Yes varchar , and the subsequent comparison value is integral type.

The solution to this problem is to avoid the uncontrolled type conversion (implicit type conversion): f_qq1_id in write the content as a string:

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_i
D 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 | +-------+---------------+--------+---------+--------------------------------+------------------+-------------+- --------+---------+------------------------------------+ returns 1 rows of records, costing 12.41 Ms.

The number of scanned rows decreased from 1386 to 40.

A similar example has occurred:

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 '

After optimization, drop directly from the scan rows 100w line to 1.

Take this opportunity to systematically look at the implicit type conversions in MySQL.

2. mysql Implicit conversion rules

2.1 Rule

Here's an analysis of the rules for implicit conversions:

A. Two parameters at least one is, the result of the NULL comparison is also NULL , the exception is the use of <=> to two NULL compared to return 1, both of which do not need to do type conversion

B. Two parameters are strings, which are compared by strings and do not type conversions

C. Two parameters are integers, compared by integers, no type conversions

D. 16 values and Non-numeric comparisons are treated as binary strings

E. One argument is TIMESTAMP or DATETIME , and the other is a constant, the constants are converted totimestamp

F. One argument is a decimal type, if another argument is decimal or an integer, the integer is converted to a decimal later comparison, and if another argument is a floating-point number, the decimal conversion is compared to a floating-point number

G. All other cases, two parameters are converted to floating-point numbers and then compared

Mysql> Select + ' One ', one + ' AA ', ' A1 ' + ' BB ', one + ' 0.01a '; +-----------+-----------+-------------+--------------+
| 11 + ' 11 ' | One + ' AA ' | ' A1 ' + ' BB ' |
One + ' 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
' = one, ' 11.0 ' = ' one ', 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)

The above can see one by one + ' AA ', because the operators on both sides of the different types and comply with article G, AA to be converted to floating-point decimal, however, the conversion failed (the letter is truncated), can be considered to turn to 0, Integer 11 is converted to floating-point type or its own, so one + ' aa ' = 11.

The 0.01a conversion double is also truncated to 0.01, so one + ' 0.01a ' = 11.01.

The equation comparison also illustrates this point, ' 11a ' and ' 11.0 ' after the conversion is equal to 11, which is why the beginning of the article why did not go to the index reason: varchar type f_qq1_id , conversion to floating-point type comparison, equal to 12345 of the case there are countless kinds such as 12345a, 12345.b waiting, The MySQL optimizer was unable to determine whether the index was more efficient, so other scenarios were selected.

But not as long as implicit type conversions can cause similar performance problems above, and ultimately to see whether the index can be effectively selected after conversion. Like f_id = '654321' , f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59' will not affect the index selection, because the former f_id is integral type, even if with the subsequent string numbers to double comparison, can still determine the value of f_id based on double, the index is still valid. The latter is because it complies with article E, but the constants on the right are converted.

Developers are likely to have such an implicit type conversion pit, but often do not pay attention to it, so simply do not have to remember so many rules, what type is compared with what type.

2.2 Security issues with implicit type conversions

Implicit type conversion not only can cause performance problems, but it can also create security problems.

mysql> desc T_account;
+-----------+-------------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra   |
+-----------+-------------+------+-----+---------+----------------+
| FID  | int (one)  | 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 |
+-----+-----------+-------------+

If the application front end has no WAF protection, the following SQL is easy to inject:

Mysql> SELECT * from T_account where fname= ' A ';
FName incoming A ' or 1= ' 1 
mysql> select * from T_account where fname= ' A ' or 1 = ' 1 ';

Attackers are smarter: fname incoming A ' + ' B, fpassword incoming 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)

Summarize

The above is for you to summarize the MySQL implicit type of conversion traps and rules, I hope this article for everyone to learn or MySQL can help, if there is doubt you can message exchange, thank you for the cloud Habitat Community support.