mysql中的隐式类型转换

Posted on |

mysql中的隐式类型转换？

mysql innodb有行锁的实现，行锁会针对当前读进行加排他锁。

1.对索引字段做函数操作会导致索引失效，破坏索引的有序性，优化器不走b+树搜索

1
select * from trade_log where month(t_modify)=7;

上面的语句没办法使用到索引，不可以在索引的字段上加函数操作，这里复杂的函数操作导致了破坏索引的扫描，但是即使类似where id+1=1000这种操作优化器也并不会做优化操作，需要写成where id= 1000-1。

隐式字符编码转换导致索引失效

在应用过程中通常会有字段字符集不统一的问题，如a表字段order_code为utf8mb4，关联 b表retail_order_code字段字符集为utf8。utf8mb4为utf8的超集合，也就是如果用a表取关联b表：

1
select * from a,b where a.order_code=b.retail_order_code;

实际上这句话在被连接的表上的逻辑==：

1
select * from b where convert(b.retail_order_code using utf8mb4) =a.value;

所以隐式字符编码转换索引失效的条件：连接过程中被驱动的表的索引字段加函数操作。

解决的方法：

1.改字符集，改成同样的字符集。

2.将驱动表的字符集用函数操作改为被驱动表的字符集。

1
select * from a,b where convert(a.order_code using utf8) = b.retail_order_code;

上面的语句在被连接表的逻辑变成：

1
select * from b where b.retail_order_code=a.value;//字符集一致，不需要做索引函数操作

隐式类型转换

在mysql的数据结构中，varchar和int的类型转换级别是不一样的，参考上面的分析方法，我们通过实践语句:

1
select "10">9;//输出 1代表将字符串转成了数字

那么我们可以得出结论，如果一个表a中有个varchar(32)的字段为索引字段order_code, 但是我们执行语句：

1
select * from a where order_code=1111;//会导致全表扫描。

便于理解，我们将其定义为一个父集一个子集，父集可以自动convert转换为子集。上面utf8为父集，utf8mb4为子集，int为子集，varchar为父集。如果父集为索引字段，子集为查询的字段，那么会出现索引失效。

问题记录

1
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

虽然都是utf8，但是mysql在比较的时候无法用具体的字符集进行比较，需要强制指定字符集：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- convert(t1.order_code using utf8)
-- convert(t1.order_code using uft8) COLLATE utf8_unicode_ci
SELECT t1.retail_order_code
	, (
		SELECT CASE t2.order_status
				WHEN 150 THEN 3
				ELSE 4
			END
	), t1.tmail_order_id, 1,t1.step_count, t1.disburse_fee
	, t1.voucher_price, t1.deleted, t1.sku_id, t1.date_create, t1.date_update
	, 'normal', 'retainge', 50, '3393340311', '大搜车旗舰店'
	, 50
FROM retail_marketing_order t1
	LEFT JOIN retail_order t2 ON convert(t1.retail_order_code USING utf8) = t2.order_code;

值得注意的是convert(t1.order_code using uft8) COLLATE utf8_unicode_ci和convert(t1.order_code using utf8)虽然让字符集标准化了，通过explain我们发现：

1
2
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	186	100.00	NULL
1	SIMPLE	t2	NULL	index	order_code_index	sync_index	107	NULL	113582	100.00	Using where; Using index; Using join buffer (Block Nested Loop)

sql查询日志：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Query_time: 0.002302  Lock_time: 0.000244 Rows_sent: 186  Rows_examined: 372  一次回表查询
SET timestamp=1548079631;
SELECT t1.retail_order_code
  , (
    SELECT CASE t2.order_status
        WHEN 150 THEN 3
        ELSE 4
      END
  ), t1.tmail_order_id, 1,t1.step_count, t1.disburse_fee
  , t1.voucher_price, t1.deleted, t1.sku_id, t1.date_create, t1.date_update
  , 'distributor', 'retainge', 50
  , 50
FROM retail_marketing_order t1
  LEFT JOIN retail_order t2 ON convert(t1.retail_order_code USING utf8) = t2.order_code;

join操作在驱动表上加父集合（convert using utf8）使用索引。

Using join buffer (Block Nested Loop)

BNL算法建立在join操作的基础上，join操作有一个内表一个外表，外表和内表两层循环。BNL算法取出100条放到join buffer中，内层循环直接从buffer中全部匹配出来100条，这样操作减少了磁盘io