Mysql的强制类型转换导致全表扫描

MySQL在查询的时候要注意“强制类型转换导致死锁”的case，这里的强制类型转换主要有如下几种：

列类型与where值类型不符

我们先创建一个表，比如叫article:

CREATE TABLE IF NOT EXISTS article( 
	`aid` INT UNSIGNED AUTO_INCREMENT, 
	`title` VARCHAR(100) NOT NULL, 
	`content` VARCHAR(2000) NOT NULL,
	`categories` NVARCHAR(2000) NOT NULL,
	`uid` INT NOT NULL,
	`tid` INT NOT NULL,
 	PRIMARY KEY ( `aid` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们都知道mysql自增的字段必须是主键,而表的主键一般都要使用自增id，不建议使用业务id，是因为使用自增id可以避免页分裂。创建表之后，CREATE INDEX titleindex ON article (title)，给title字段设定成索引。

再往表里插入一点数据，数据如下：

这是测试一下如下语句：

1 2	explain select uid,tid from article where title = 'bbb'; #'bbb'是字符串跟title的varchar一致 explain select uid,tid from article where title = 123; #123是int与title的varchar不一致

测试效果：

title是我们设定的索引列，第一个情况where的值与title类型相同，成功命中索引。第二个SQL就要面临强制类型转换，没有命中索引，扫描了全表。

这里补充一下，如果你直接使用select uid,tid from article where title = 123;会产生4个warnings，如图：

其实这几个警告就是通知你，title这一列是varchar的，却在上面的sql语句里直接用了int类型赋予它。

再建一个表：

CREATE TABLE IF NOT EXISTS `user`( 
	`uid` INT UNSIGNED AUTO_INCREMENT, 
	`username` VARCHAR(100) NOT NULL, 
	`email` VARCHAR(40) NOT NULL, 
	PRIMARY KEY ( `uid` )
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

查看CHARSET不能用desc XXX，而是用show create table XXX。如图：

测试一下语句：explain select * from article a,user b where a.uid=b.uid;,如图：

发现article的uid列不是索引，所以就要全表扫描，扫描了4行，但是article的每一条记录使用b即user的索引，所以只扫描一行。

再测试一下：explain select * from article a,user b where a.uid=b.username;，如图：

发现uid跟username类型根本对不上，所以两个都要扫描全表。这里要注意！NVARCHAR跟VARCHAR不是同一个类型，也会触发扫描全表。

插播一句，如果要后期修改CHARSET,语句是ALTER TABLE user DEFAULT CHARACTER SET utf8;。

还有一种情况比较隐蔽，就是两个表字符集不同，比如一个是utf8另一个是lartin1。或者是字符集相同都是utf8但是排序规则不同，比如一个是utf8_esperanto_ci，另一个是默认的utf8_general_ci.那么这样即使对应的两个关联属性相同都是主键，也不会命中索引，只会导致笛卡尔积的循环计算(nested loop)。详情可见https://blog.csdn.net/weixin_32187037/article/details/113595144 的第二个例子。我用的是mysql8,这个问题已经被修复了，字符集不再影响索引了。

最后推荐一个很有含金量的一个case：https://www.cnblogs.com/MYSQLZOUQI/articles/3837828.html，可以重点学习一下它的排查过程。

查看sql执行的时长

在工作里经常要查看某个语句执行的时长，一般来说都是用show profiles + show profile for query XXX的方法，这里说一个新一点的：

1
2
3

set @d=now();
你要执行的那个SQL语句;
select timestampdiff(second,@d,now());

有一点要注意，就是在最后面，一定要多copy一个空行，不然最后一个sql要你自己按回车执行，这样就不准了。

如果是数据比较少，SQL执行比较快，用毫秒为单位，那就是把最后一句改成select timestampdiff(MICROSECOND, @d, now(3)) / 1000 as '耗时(ms)';即可以统计耗时的毫秒数。如图：

参考资料

https://blog.csdn.net/qq_29663071/article/details/75021980
https://www.cnblogs.com/guodongdidi/p/6290782.html
https://www.sudops.com/mysql-console-can-not-type-chinese.html
https://mp.weixin.qq.com/s/LvIvSUf-BVKU5ORNm_qUcA （mysql军规）