MySQL implicit conversion traps and rules

MySQL implicit conversion traps and rules

Preface

I believe everyone knows that implicit conversions pose a risk of indexing failure. In the case of high concurrency and large data volumes, the consequences of failed indexing are very serious. The database is dragged to death, and the entire system crashes, causing heavy losses to large-scale systems. So next we will take a good look at the MySQL implicit type conversion traps and rules through this article.

1. implicit type conversion instance

Today, the MySQL thread count alarm suddenly appears on the production database, and the IOPS is very high. There are many SQL statements similar to the following in the instance session: (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)

Use explain to check the number of scanned rows and the selection of indexes:

Mysql> explain SELECT f_col3_id, f_qq1_id FROM explain WHERE f_col1_id = 1226391 and f_col2_id = 1244378 and f_qq1_id in (12345, 23456, 34567,45678, large, 78901,89012, 90123,901231, large ); + ------ + --------------- + --------- + -------- + upper + --------------- + ------------ + -------- + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ------ + --------------- + --------- + -------- + upper + --------------- + ------------ + -------- + upper + | 1 | SIMPLE | t_tb1 | ref | uid_type_frid, idx_corpid_qq1id | uid_type_frid | 8 | const | 1386 | Using index condition; using where | + ------ + --------------- + --------- + -------- + ------------------------------ + --------------- + ------------ + -------- + response + returns a total of 1 line of records, which costs 11.52 ms.

T_tb1 table has an indexuid_type_frid(f_col2_id,f_type) ,idx_corp_id_qq1id(f_col1_id,f_qq1_id)And if the latter is selected,f_qq1_idThe filtering effect should be very good, but the former is selected. When using hint use index(idx_corp_id_qq1id)Hour:

Mysql> explain extended SELECT f_col3_id, f_qq1_id FROM using use index (partition) WHERE f_col1_id = 1226391 and f_col2_id = 1244378 and f_qq1_id in (partition, 78901,89012, 90123,901231, partition ); + ------ + --------------- + -------- + upper + ------------ + ---------- + ------------- + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ------ + --------------- + -------- + upper + ------------------ + ------------ + ---------- + --------------- + upper + | 1 | SIMPLE | t_tb1 | ref | Upper | Lower | 8 | const | 2375752 | Using index condition; using where | + -----+ --------------- + -------- + --------------------- + ---------------- + ------------ + ---------- + ----------- + response + returns 1 line of records, which costs 17.48 ms. mysql> show warnings; + ----------------- + -------------- + upper + | Level | Code | Message | + ----------------- + ---------------- + upper + | Warning | 1739 | Cannot use range access on index 'idx _ weight' 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, large, 78901,89012, 90123,901231, large) | + records + -------------- + records + two rows are returned, 10.81 ms.

The rows column reaches rows, but the problem is also found:select_typeIt should berange Yes,key_lenIt turns out that onlyidx_corpid_qq1idThe first column of the index. The above explain is usedextended, So show warnings; can be clearly seenf_qq1_id Implicit type conversion:f_qq1_idYesvarcharAnd the following comparison value is an integer.

To solve this problem, we can avoid the uncontrollable Effect of implicit type conversion (implicit type conversion ):f_qq1_id inThe content is written 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_id in ('20140901', '20160301', '20160301', '20160301 ', '123', '123', '123', '123', '123', '123 '); + ------- + --------------- + -------- + --------- + hour + ------------- + --------- + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ------- + ------------- + -------- + --------- + upper + ------------------ + ------------- + --------- + lower + | 1 | SIMPLE | t_tb1 | range | uid_type_frid, idx_corpid_qq1id | idx_corpid_qq1id | 70 | 40 | Using index condition; using where | + ------- + --------------- + -------- + --------- + hour + ------------------ + ------------- + --------- + hour + returns a row of records, which costs 12.41 ms.

The number of scanned rows is reduced from 1386 to 40.

A similar case has also 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, the number of rows scanned for rows is reduced from to 1.

Let's take a look at the implicit type conversion in mysql.

2. mysql implicit conversion rules

2.1 rules

The following describes the implicit conversion rules:

A. At least one of the two parameters isNULL The comparison result is alsoNULL, The exception is to use <=> for twoNULL 1 is returned for comparison. No type conversion is required for both cases.

B. Both parameters are strings and will be compared by strings without type conversion.

C. Both parameters are integers, which are compared by integers without type conversion.

D. When the hexadecimal value is compared with a non-numeric value, it is treated as a binary string.

E. There is a parameter that isTIMESTAMP OrDATETIMEAnd another parameter is a constant, which is convertedtimestamp

F. One parameter isdecimal Type. If another parameter isdecimal Or an integer, which is converteddecimal If another parameter is a floating point numberdecimal Convert to floating point for comparison

G. In all other cases, the two parameters are converted to floating point numbers for comparison.

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)

The above shows that 11 + 'AA', because the types on both sides of the operator are different and conform to the g, aa is to be converted to a floating point decimal number, but the conversion fails (the letter is truncated ), it can be considered that it is converted to 0, and the integer 11 is converted to its own floating point type, so 11 + 'A' = 11.

Convert 0.01adoubleIs truncated to 0.01, so 11 + '0. 01a '= 11.01.

Equality comparison also illustrates this. '11a 'and '11. 0' are both converted to '11. 0', which is why the instance at the beginning of the article has not been indexed:varcharTypef_qq1_idWhen it is converted to a floating-point comparison, there are several types of data equal to 12345, such as 12345a and 12345. B, and the MySQL optimizer cannot determine whether the index is more effective, so other solutions are selected.

But not as long as implicit type conversion occurs, it will lead to performance problems like the above, and ultimately it depends on whether the index can be effectively selected after conversion. Imagef_id = '654321',f_mtime between '2016-05-01 00:00:00' And'2016-05-04 23:59:59'The index selection will not be affected because the former f_id is an integer. Even if it is converted to a double string, the f_id value can still be determined based on the double, and the index is still valid. The latter is because it complies with entry e, but the constant on the right is converted.

Developers may not need to remember so many rules as long as there is such a trap of implicit type conversion, but they often do not pay attention to it. Therefore, developers simply do not need to remember so many rules and compare the types with those.

2.2 Security Issues of implicit type conversion

Implicit type conversion may not only cause performance problems, but also security problems.

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

If the application front-end does not have WAF protection, the following SQL statements can be easily injected:

Mysql> select * from t_account where fname = 'a '; fname is passed into A 'OR 1 = '1 mysql> select * from t_account where fname = 'A' OR 1 = '1 ';

Attackers are smarter:fnamePass in a' + 'B,fpasswordPass 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)

Summary

The above is a summary of the MySQL implicit type conversion traps and rules. I hope this article will be helpful to you or mysql. If you have any questions, please leave a message, thank you for your support.