泽民博客 ☰ Menu

mysql的隐式类型转换

隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。



MySQL 的隐式类型转换原则：



– 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
– 两个参数都是字符串，会按照字符串来比较，不做类型转换
– 两个参数都是整数，按照整数来比较，不做类型转换
– 十六进制的值和非数字做比较时，会被当做二进制串，和数字做比较时会按下面的规则处理
– 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
– 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
– 所有其他情况下，两个参数都会被转换为浮点数再进行比较



注意一个安全问题：假如 password 类型为字符串，查询条件为 int 0 则会匹配上。



由于 MySQL 隐式类型转换规则比较复杂，依赖 MySQL 隐式转换很容易出现各种想想不到的问题，而且 MySQL 隐式类型转换本身也是非常耗费 MySQL 服务器性能的，所以非常不推荐这样使用。

当我们对不同类型的值进行比较的时候，为了使得这些数值「可比较」（也可以称为类型的兼容性)，MySQL会做一些隐式转化（Implicit type conversion）。



比如下面的例子：
mysql> SELECT 1+’1’;
-> 2
mysql> SELECT CONCAT(2,’ test’);
-> ‘2 test’
很明显，上面的SQL语句的执行过程中就出现了隐式转化。并且从结果们可以判断出，第一条SQL中，将字符串的“1”转换为数字1，而在第二条的SQL中，将数字2转换为字符串“2”。



MySQL也提供了CAST()函数。我们可以使用它明确的把数值转换为字符串。当使用CONCA()函数的时候，也可能会出现隐式转化，因为它希望的参数为字符串形式，但是如果我们传递的不是字符串呢：
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, ‘38.8’
mysql> SELECT 38.8, CONCAT(38.8);
-> 38.8, ‘38.8’



避免在sql的where中对字段进行转换（函数转换/操作符转换/隐式转换）——索引会失效从而导致慢查询
2017年11月20日 22:20:25 stpeace 阅读数：4935更多
个人分类： s2: 软件Develop进阶 s2: 后台开发 s4: 存储/数据库/SQL/NoSQL
版权声明：本文为博主原创文章，转载时请务必注明本文地址， 禁止用于任何商业用途， 否则会用法律维权。 https://blog.csdn.net/stpeace/article/details/78585005
看例子：



mysql> show create table tb_test;
+———+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+———+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| tb_test | CREATE TABLE tb_test (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘test’,
name varchar(32) NOT NULL COMMENT ‘test’,
score int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘test’,
PRIMARY KEY (id),
KEY id_score (score)
) ENGINE=InnoDB AUTO_INCREMENT=10005 DEFAULT CHARSET=utf8 COMMENT=’测试表， 无实际作用’ |
+———+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)



    可见，  score是索引， 我们继续看： mysql> select * from tb_test where score = 1;         +----+------+-------+ | id | name | score | +----+------+-------+ |  1 | n1   |     1 | +----+------+-------+ 1 row in set (0.00 sec)


mysql> select * from tb_test where score + 1 = 2;

+—-+——+——-+
| id | name | score |
+—-+——+——-+
| 1 | n1 | 1 |
+—-+——+——-+
1 row in set (0.00 sec)



mysql>
mysql>
mysql> show profiles;
+———-+————+—————————————————+
| Query_ID | Duration | Query |
+———-+————+—————————————————+



| 10061 | 0.00076248 | select * from tb_test where score = 1 |
| 10062 | 0.00316482 | select * from tb_test where score + 1 = 2 |
+———-+————+—————————————————+
15 rows in set (0.00 sec)



    可以看到， 时间差距较大， 为什么呢？ 因为对sql的where字段进行转换（函数转换/操作符转换/隐式转换）， 会导致索引失效。来explain看看： mysql> explain select * from tb_test where score = 1;     +------+-------------+---------+------+---------------+----------+---------+-------+------+-------+ | id   | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra | +------+-------------+---------+------+---------------+----------+---------+-------+------+-------+ |    1 | SIMPLE      | tb_test | ref  | id_score      | id_score | 4       | const |    1 |       | +------+-------------+---------+------+---------------+----------+---------+-------+------+-------+ 1 row in set (0.00 sec)


mysql> explain select * from tb_test where score + 1 = 2;
+——+————-+———+——+—————+——+———+——+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+———+——+—————+——+———+——+——-+————-+
| 1 | SIMPLE | tb_test | ALL | NULL | NULL | NULL | NULL | 10224 | Using where |
+——+————-+———+——+—————+——+———+——+——-+————-+
1 row in set (0.01 sec)



   看type, 很显然， 后者进行了全表搜索， 也就是索引失效了。 看看 key和rows也能获知结果。


数据库为了提高查询速度 增加索引是必不可少的 但是有些时候即使你加了索引也不定有效果，索引的使用其实和你的sql语句有很大得关系，例如我们不能够在索引上面增加表达式，或者 索引上有null 值 等这些都会让sql放弃索引进行全表扫描，不过有时候我们会发现一个很奇怪的问题:明明sql 符合规范为啥还是这全表扫描



另外，索引列使用函数（除非建了索引函数）、表达式、绑定变量窥测（由于执行计划共享会使用第一个传入值生成的执行计划，如果第一个生成的执行计划不使用索引，则之后就算传入值使用索引会更高效，也不会使用索引）、统计信息老旧等原因也会导致索引列不会用到索引，也需要注意可能根据索引列查询出的数据量较大，Oracle判断不走索引可能会高效也会忽略索引。在优化过程中，如果碰到索引列没有走索引，不要慌乱，Oracle对索引列不走索引都是有依据的，需要按实际情况进行分析。



1、查询谓词没有使用索引的主要边界,换句话说就是select *，可能会导致不走索引。
比如，你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引，但是优化器会认为需要一行行的扫描会更有效，这个时候，优化器可能会选择TABLE ACCESS FULL，但是如果换成了SELECT Y FROM T WHERE Y = XXX，优化器会直接去索引中找到Y的值，因为从B树中就可以找到相应的值。



2、单键值的b树索引列上存在null值，导致COUNT()不能走索引。
如果在B树索引中有一个空值，那么查询诸如SELECT COUNT() FROM T 的时候，因为HASHSET中不能存储空值的，所以优化器不会走索引，有两种方式可以让索引有效，一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把这个列的属性改为not null (不能为空)。



3、索引列上有函数运算，导致不走索引
如果在T表上有一个索引Y，但是你的查询语句是这样子SELECT * FROM T WHERE FUN(Y) = XXX。这个时候索引也不会被用到，因为你要查询的列中所有的行都需要被计算一遍，因此，如果要让这种sql语句的效率提高的话，在这个表上建立一个基于函数的索引，比如CREATE INDEX IDX FUNT ON T(FUN(Y));这种方式，等于Oracle会建立一个存储所有函数计算结果的值，再进行查询的时候就不需要进行计算了，因为很多函数存在不同返回值，因此必须标明这个函数是有固定返回值的。



4、隐式转换导致不走索引。
索引不适用于隐式转换的情况，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR2的，那么Oracle会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。



5、表的数据库小或者需要选择大部分数据，不走索引
在Oracle的初始化参数中，有一个参数是一次读取的数据块的数目，比如你的表只有几个数据块大小，而且可以被Oracle一次性抓取，那么就没有使用索引的必要了，因为抓取索引还需要去根据rowid从数据块中获取相应的元素值，因此在表特别小的情况下，索引没有用到是情理当中的事情。
6、cbo优化器下统计信息不准确，导致不走索引
很长时间没有做表分析，或者重新收集表状态信息了，在数据字典中，表的统计信息是不准确的，这个情况下，可能会使用错误的索引，这个效率可能也是比较低的。
7、！=或者<>(不等于），可能导致不走索引，也可能走 INDEX FAST FULL SCAN
例如select id from test where id<>100
8、表字段的属性导致不走索引，字符型的索引列会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式，
由于字符型和数值型的在insert的时候排序不同，字符类型导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3200000），按字符类型（’1’…‘32000000’）t排序，在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。
下面展示测试结果，
两个表的数据类型相似（只是ID字段类型不同），各插入了320万数据，ID字段范围为1~3200000。



模拟场景 

相关代码如下：

 



对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。

 



对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。 

解决方法

 



将SQL语句由开放区间扫描（>=），修改为封闭区间（between xxx and max_value）。使得数据在索引局部顺序是“对的”。如果采用这种方式仍然不走索引扫描，还可以进一步细化分段或者采用“逐条提取+批绑定”的方法。 

 

9.建立组合索引，但查询谓词并未使用组合索引的第一列，此处有一个INDEX SKIP SCAN概念, 

10、like '%liu' 百分号在前 

11，not in ,not exist 

可以尝试把not in 或者 not exsts改成左连接的方式（前提是有子查询，并且子查询有where条件）。 

例如：

SELECT

    /*+ INDEX(I CIRCLEICONMAST_IX1)*/

    I.ICONNO,

    I.CIRCLEID,

    I.FILEPATH,

    I.REGDT,

    I.FILEPATH || '/' || I.FILENAME IMGNAME,

    I.FILEPATH || '/' || 'th_160_' || I.FILENAME SMALLIMGNAME,

    I.MEMBERID,

    I.ADMCHK STATUS,

    I.ADMCHK ORIGINALSTATUS,

    ROWNUM RN

     FROM CIRCLEICONMAST I

    WHERE I.REGDT BETWEEN TO_DATE('20120619', 'YYYYMMDD') - 10000 AND

      TO_DATE('20120621', 'YYYYMMDD')

      AND NOT EXISTS (

       SELECT C.VALIDFLG 

         FROM CIRCLEMAST C 

        WHERE C.VALIDFLG IN ('N', 'F') 

        AND I.CIRCLEID = C.CIRCLEID)      

      AND I.ADMCHK = 'N'

 

改成左连接：

SELECT

/*+ INDEX(I CIRCLEICONMAST_IX1)*/

I.ICONNO,

I.CIRCLEID,

I.FILEPATH,

I.REGDT,

I.FILEPATH || '/' || I.FILENAME IMGNAME,

I.FILEPATH || '/' || 'th_160_' || I.FILENAME SMALLIMGNAME,

I.MEMBERID,

I.ADMCHK STATUS,

I.ADMCHK ORIGINALSTATUS,

ROWNUM RN

  FROM CIRCLEICONMAST I, CIRCLEMAST C 

WHERE I.REGDT BETWEEN TO_DATE('20110620', 'YYYYMMDD') AND

       TO_DATE('20120621', 'YYYYMMDD') + 1

   AND C.VALIDFLG NOT IN ('N', 'F') 

   AND I.CIRCLEID = C.CIRCLEID

      

   AND I.ADMCHK = 'N'

总结：oracle中有很多情况会导致index失效，并且走全表扫描的代价是相当大的，所以在写sql的时候一定要注意这个会使索引失效的情况，养成良好的习惯。


检查是否用了索引：
1，explain sql语句
查看执行计划，查看有没有使用索引
2，show warnings



可以看到，无法使用列userName上的索引，由于在该列上发生了类型转换。
从表的定义可以看出，这里mysql隐式的将数值类型转换成了字符串类型，以匹配表。
这也导致的在userName该列上的索引失效，从而导致全表扫描。这是非常可拍的，如果不注意的话，将导致服务器超负荷。



在之前的执行计划中，存在“1 - filter(TO_NUMBER(“STATUS”)=14)”的部分。这说明在进行条件搜索的时候，Oracle发现类型不匹配，隐式的将数据列加入了一个to_number函数。这样，Oracle就需要一个如函数索引的索引列来支持搜索路径，于是索引idx_t_status的搜索成本就大大增加。经过试算，Oracle认为全表扫面的成本相对较低。



显然，这种情况是我们开发人员不希望看到的。我们已经付出了成本来构建维护索引，对关键用例功能不能支持，应该是我们避免的。其实，解决的方案也很容易，就是注意细节。在where条件书写的时候明确清楚属性列类型，这样就可以避免这种情况发生。
我们观察到搜索的访问条件“2 - access(“OBJECT_ID”=1000)”，说明语句生成执行计划的时候，输入条件已经转化为数字类型1000。所以生成的执行计划是不会被隐式类型转化所困扰。



那么，笔者猜想是在Oracle接受到查询语句之后，会有一个SQL改写的过程。在其中根据一些规则条件，对SQL进行改写优化。当Oracle发现这样简单的隐式类型转化后，会自主的将字符串1000转化为类型匹配的数字类型1000。这个例子就告诉我们，一些简单的隐式类型转化也是会走索引的。



Category storage