MySQL の暗黙的な型変換トラップとルール_MySQL

はじめに

暗黙的な型変換にはインデックスにヒットしないリスクがあることは誰もが知っていると思いますが、同時実行性が高く、データ量が多い場合、インデックスが欠落した場合の結果は非常に深刻です。データベースは死に至るまで引きずり込まれ、その後システム全体が崩壊し、大規模システムに多大な損失をもたらします。この記事では、MySQL の暗黙的な型変換のトラップとルールについて学びましょう。

1. 暗黙的な型変換の例

今日、MySQL スレッド数アラームが本番データベースに突然表示され、IOPS が非常に高く、次のような SQL がインスタンス セッションに多数表示されました: (関連するフィールドと値が変更されました)

リーリー

Explain を使用して、スキャンされた行の数とインデックスの選択を確認します:

リーリー

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) がある場合:

リーリー

rows 列は 200 万行に達しましたが、select_type应该是 range 才对，key_len看出来只用到了idx_corpid_qq1id索引的第一列。上面explain使用了 extended，所以show warnings;可以很明确的看到 f_qq1_id 出现了隐式类型转换：f_qq1_id是varchar という問題も発見され、その後の比較値は整数になります。

この問題の解決策は、暗黙的な型変換によって引き起こされる制御不可能性を回避することです。f_qq1_id in の内容を文字列に書き込みます。

リーリー

走査線の数が1386本から40本に減少しました。

同様のケースもあります:

リーリー

最適化後、100万行のうちスキャンされた行は直接1行に減りました。

この機会に、mysql の暗黙的な型変換を体系的に見てみましょう。

2. mysql の暗黙的な変換ルール

2.1 ルール

暗黙的な変換のルールを分析してみましょう:

a. 2 つのパラメータのうち少なくとも 1 つは NULL 时，比较的结果也是 NULL，例外是使用 对两个 NULL 比較される場合、どちらの場合も型変換は必要ありません
。

b. 両方のパラメータは文字列であり、型変換なしで文字列に従って比較されます

c. 両方のパラメータは整数であり、型変換なしで整数として比較されます

d. 16 進数値を数値以外と比較する場合、それらはバイナリ文字列として扱われます

パラメータがありますTIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp

f. 1 つのパラメータは decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 比較のために浮動小数点数に変換されます

g. 他のすべての場合、比較前に両方のパラメータが浮動小数点数に変換されます

リーリー

上記から、11 + 'aa' は、演算子の両側の型が異なり、G 条に準拠しているため、aa を浮動小数点 10 進数に変換する必要があることがわかります。ただし、変換は失敗します。文字は切り捨てられます）、 0 に変換されると考えることができ、整数 11 は浮動小数点型またはそれ自体に変換されるため、 11 + 'aa' = 11 となります。

0.01a を double 型に変換すると、これも 0.01 に切り捨てられるため、11 + '0.01a' = 11.01 となります。

この点は、等価性の比較でもわかります。変換後、「11a」と「11.0」は両方とも 11 に等しいため、記事の冒頭の例はインデックス付けされません。varchar型的f_qq1_id、浮動小数点比較に変換すると、インデックスが付けられます。 12345 に等しい 12345a、12345.b などの状況は無数にあります。MySQL オプティマイザはインデックスがより効果的かどうかを判断できないため、他のオプションを選択します。

ただし、暗黙的な型変換が発生する限り、上記と同様のパフォーマンスの問題が発生するというわけではありません。最終的には、変換後にインデックスを効果的に選択できるかどうかによって決まります。 f_id = '654321' 、f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59' と同様に、前の f_id は整数なので、double に変換された後続の文字列番号と比較しても、f_id の値は double とインデックスに基づいて決定できるため、インデックスの選択には影響しません。はまだ有効です。後者はe項に準じますが、右側の定数が変換されているためです。

開発者には暗黙的な型変換という落とし穴しかないかもしれませんが、多くの場合それに注意を払わないため、それほど多くのルールを覚える必要はなく、型と型を比較す​​るだけで十分です。

2.2 暗黙的な型変換のセキュリティ問題

暗黙的な型変換はパフォーマンスの問題だけでなく、セキュリティの問題も引き起こす可能性があります。

リーリー

假如应用前端没有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能有所帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。