Traps und Regeln für die implizite MySQL-Typkonvertierung

Vorwort

Ich glaube, jeder weiß, dass die implizite Typkonvertierung das Risiko birgt, dass der Index nicht erreicht wird. Bei hoher Parallelität und großem Datenvolumen sind die Folgen des Fehlens des Index sehr schwerwiegend. Die Datenbank wird zerstört, und dann bricht das gesamte System zusammen, was zu schweren Verlusten für Großsysteme führt. Lassen Sie uns in diesem Artikel mehr über die Traps und Regeln für die implizite Typkonvertierung in MySQL erfahren.

1. Beispiel für implizite Typkonvertierung

Heute ist in der Produktionsdatenbank plötzlich ein MySQL-Thread-Count-Alarm aufgetreten, die IOPS waren sehr hoch und in der Instanzsitzung sind viele SQL-Anweisungen wie die folgenden aufgetreten: (Relevante Felder und Werte wurden geändert)

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)

Nach dem Login kopieren

Verwenden Sie EXPLAIN, um die Anzahl der gescannten Zeilen und die Indexauswahl zu überprüfen:

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.

Nach dem Login kopieren

In der Tabelle

t_tb1 gibt es die Indizes uid_type_frid(f_col2_id,f_type) und idx_corp_id_qq1id(f_col1_id,f_qq1_id). Wenn letzterer ausgewählt ist, sollte der Filtereffekt von f_qq1_id sehr gut sein, ersterer ist jedoch ausgewählt. Bei Verwendung von 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.

Nach dem Login kopieren

Die Spalte

rows erreichte 2 Millionen Zeilen, aber es wurde auch das Problem entdeckt: select_type sollte range sein, und key_len sah, dass nur die erste Spalte des Index idx_corpid_qq1id verwendet wurde. In der obigen Erklärung wird extended verwendet. Wenn Sie Warnungen anzeigen, können Sie deutlich erkennen, dass f_qq1_id eine implizite Typkonvertierung aufweist: f_qq1_id ist varchar und der nachfolgende Vergleichswert ist eine Ganzzahl.

Die Lösung für dieses Problem besteht darin, die durch die implizite Typkonvertierung verursachte Unkontrollierbarkeit zu vermeiden: Schreiben Sie den Inhalt von f_qq1_id in als Zeichenfolge:

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.

Nach dem Login kopieren

Die Anzahl der gescannten Zeilen wurde von 1386 auf 40 reduziert.

Es gibt auch einen ähnlichen Fall:

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'

Nach dem Login kopieren

Nach der Optimierung wurden die gescannten Zeilen direkt von 1 Million Zeilen auf 1 reduziert.

Nutzen Sie diese Gelegenheit, um einen systematischen Blick auf die implizite Typkonvertierung in MySQL zu werfen.

2. Implizite MySQL-Konvertierungsregeln

2.1 Regeln

Lassen Sie uns die Regeln der impliziten Konvertierung analysieren:

a. Wenn mindestens einer der beiden Parameter NULL ist, ist das Vergleichsergebnis auch NULL. Die Ausnahme besteht darin, dass beim Vergleich von zwei NULL mit <=> 1 zurückgegeben wird In diesen beiden Fällen ist keine Typkonvertierung erforderlich

b. Beide Parameter sind Strings und werden entsprechend Strings ohne Typkonvertierung verglichen

c. Beide Parameter sind Ganzzahlen und werden als Ganzzahlen ohne Typkonvertierung verglichen

d. Beim Vergleich von Hexadezimalwerten mit nicht numerischen Werten werden diese als Binärzeichenfolgen behandelt

e. Wenn ein Parameter TIMESTAMP oder DATETIME ist und der andere Parameter eine Konstante ist, wird die Konstante in timestamp
konvertiert

f. Ein Parameter ist vom Typ decimal oder eine Ganzzahl, die zum Vergleich in decimal umgewandelt wird in decimal Zum Vergleich in Gleitkommazahl konvertierendecimal umgewandelt werden

g. In allen anderen Fällen werden beide Parameter vor dem Vergleich in Gleitkommazahlen umgewandelt

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)

Nach dem Login kopieren

Wie aus dem Obigen ersichtlich ist, muss 11 + 'aa', da die Typen auf beiden Seiten des Operators unterschiedlich sind und Artikel G entsprechen, in eine Gleitkomma-Dezimalzahl umgewandelt werden. Die Konvertierung schlägt jedoch fehl (Die Buchstaben sind abgeschnitten), und es kann davon ausgegangen werden, dass es in 0 konvertiert wird, die Ganzzahl 11 wird in den Gleitkommatyp oder sich selbst konvertiert, also 11 + 'aa' = 11.

Wenn

0.01a in den Typ

konvertiert wird, wird er auch auf 0,01 gekürzt, also 11 + '0,01a' = 11,01. double

Der Gleichheitsvergleich verdeutlicht auch diesen Punkt: „11a“ und „11.0“ sind nach der Konvertierung beide gleich 11. Aus diesem Grund ist das Beispiel am Anfang des Artikels nicht indiziert:

Typ varchar, konvertiert in Beim Vergleich von Gleitkommatypen gibt es unzählige Situationen, in denen er gleich 12345 ist, z. B. 12345a, 12345.b usw. Der MySQL-Optimierer kann nicht feststellen, ob der Index effektiver ist, und wählt daher andere Lösungen. f_qq1_id

Solange jedoch eine implizite Typkonvertierung erfolgt, führt dies zu ähnlichen Leistungsproblemen wie oben. Letztendlich hängt es davon ab, ob der Index nach der Konvertierung effektiv ausgewählt werden kann. Wie

haben f_id = '654321' und f_mtime between '2016-05-01 00:00:00' keinen Einfluss auf die Indexauswahl, da die frühere f_id eine Ganzzahl ist. Selbst wenn sie mit der nachfolgenden Zeichenfolgentypzahl verglichen wird, kann der Wert von f_id immer noch basierend darauf bestimmt werden auf das Doppelte, und der Index wird immer noch effizient sein. Letzteres liegt daran, dass es Punkt e entspricht, die Konstante auf der rechten Seite jedoch konvertiert wurde. '2016-05-04 23:59:59'

Entwickler haben möglicherweise nur eine solche Falle der impliziten Typkonvertierung, achten jedoch häufig nicht darauf, sodass sie sich nicht so viele Regeln merken müssen, sondern nur den Typ mit dem Typ vergleichen müssen.

2.2 Sicherheitsprobleme der impliziten Typkonvertierung

Implizite Typkonvertierung kann nicht nur Leistungsprobleme, sondern auch Sicherheitsprobleme verursachen.

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

Nach dem Login kopieren

假如应用前端没有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';

Nach dem Login kopieren

攻击者更聪明一点： 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)

Nach dem Login kopieren

总结

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