读mysql45讲-数据类型转换

条件字段函数操作

创建一个week表：

CREATE TABLE `week` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `ds` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `week_ds_IDX` (`ds`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='周表'

插入数据的存储过程：

CREATE DEFINER=`zhuruilin_dev`@`%` PROCEDURE `dev`.`test1`()
begin declare i int;
set i = 0;

while(i <= 1000)do
insert into week(name,ds) 
values(concat(char(97+(i div 1000)),char(97+(i%1000 div 100))),date_add(date_format(NOW(),'%Y-%m-%d'), interval 3 day) );
set i = i + 1;
end while;
end

我调用了三次存储过程，每次调用完之后，都将interval 3 day中增加的天数给加一天，为了区分出ds字段。

分析一个简单的sql：

可以看到rows那一列的值是1001，也就是预估扫描1001行数据，Extra那一列Using index表示走了覆盖索引。

接下来修改一下sql：

区别就是where条件中将ds='2022-03-07’换成了day(ds)=‘7’；使用了日期函数。
可以看到分析结果rows那一列的值是3003，也就是预估扫描30003行，也就是需要全表扫描(上面我只插入了2022-03-06，2022-03-07，2022-03-08)三天的值。

如果对字段做了函数计算，就 用不上索引了，这是MySQL的规定。

我的理解是ds索引树上存的是ds整个值的字段，也就是索引项上存的是[ds=2022-03-07,id=xxx]这样，又因为索引树存储的值是有序的，所以可以很快定位到ds=2022-03-07的两个临界值，开始值和结束值的地方。但是如果用了日期函数day(ds)，就需要先将ds的值取出来再算出day=7的值，对于计算过程索引是没法作用的，所以会进行全表扫描。

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

但是放弃索引树搜索，并不是就放弃使用这个索引。优化器可以选择遍历索引树，通过比较ds索引树和主键索引树，如果ds索引树更小的话就选择遍历ds索引树。

隐式类型转换

之前创建week表的id字段是用的bigint字段，执行一个简单sql：

可以看到rows那一列的扫描行数是1，Extra也表示根据主键索引直接搜索到的，所以由此得出应该是将 ‘2022’这个字符串转换为了数值类型，然后直接用id搜索的。

查询表换一下，查询一个表的age字段，这个字段是varchar类型的，并且给这个字段加了索引，这个表有5000条数据。

可以看到key那一列表示是走了t_user_info_age_IDX这个索引的，并且扫描行数也就是1行。
接着修改下查询sql：

sql条件的区别就是 age = 2022 ;2022直接就是一个数字；可以发现这个时候没有走索引，并且预估扫描行数是5138，也就是全表扫描了。

• 如果索引字段是数值类型，和字符串进行比较，会将字符串转换成数值类型，并且走索引。
• 如果索引字段是字符串类型，和数字进行比较，会将索引字段转换为数值类型，不会走索引。

也可以用简单sql来验证下：

返回1说明是将‘10’转换为了数字和9进行比较的。因为如果是 ‘10’和‘9’进行比较是返回0的。

所以我想是不是将索引字段设置为数值类型也方便了点，如果实际开发中，后端拼接条件传值就是‘2022’，那也是可以走索引的；但是如果索引字段是字符串类型，那只有传过来的也是字符串类型的才会走索引，后端查询之前就多了一个转换类型的操作。

创建表的DDL语句中指定的字符集不同的话，也会出现类型转换导致不走索引的问题。
如果驱动表和被驱动表的连接字段的数据类型或者字符集不一样，就需要手动将驱动表的连接字段的数据类型转换为被驱动表的连接字段的数据类型，这样就会让被驱动表走连接字段的索引。