|
《MySQL死锁分析的两个工具》中，举了一个强制类型转换导致死锁的例子，有朋友询问是不是类型转换都不能命中索引，花1分钟细说一下。
第一类：“列类型”与“where值类型”不符，不能命中索引，会导致全表扫描(full table scan)。
数据准备：
- create table t1 (
- cell varchar(3) primary key
- )engine=innodb default charset=utf8;
-
- insert into t1(cell) values ('111'),('222'),('333');
- cell属性为varchar类型;
- cell为主键，即聚簇索引(clustered index);
- t1插入3条测试数据;
测试语句：
- explain select * from t1 where cell=111;
- explain select * from t1 where cell='111';
- 第一个语句，where后的值类型是整数(与表cell类型不符);
- 第二个语句，where后的值类型是字符串(与表cell类型一致);
测试结果：
- 强制类型转换，不能命中索引，需要全表扫描，即3条记录;
- 类型相同，命中索引，1条记录;
画外音：关于explain，详见《MySQL死锁分析的两个工具》。
第二类：相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算(nested loop)。
数据准备：
- create table t2 (
- cell varchar(3) primary key
- )engine=innodb default charset=latin1;
-
- insert into t2(cell) values ('111'),('222'),('333'),('444'),('555'),('666');
-
- create table t3 (
- cell varchar(3) primary key
- )engine=innodb default charset=utf8;
-
- insert into t3(cell) values ('111'),('222'),('333'),('444'),('555'),('666');
- t2和t1字符集不同，插入6条测试数据;
- t3和t1字符集相同，也插入6条测试数据;
- 除此之外，t1，t2，t3表结构完全相同;
测试语句：
- explain select * from t1,t2 where t1.cell=t2.cell;
- explain select * from t1,t3 where t1.cell=t3.cell;
- 第一个join，连表t1和t2(字符集不同)，关联属性是cell;
- 第一个join，连表t1和t3(字符集相同)，关联属性是cell;
测试结果：
- t1和t2字符集不同，存储空间不同;
- t1和t2相join时，遍历了t1的所有记录3条，t1的每一条记录又要遍历t2的所有记录6条，实际进行了笛卡尔积循环计算(nested loop)，索引无效;
- t1和t3相join时，遍历了t1的所有记录3条，t1的每一条记录使用t3索引，即扫描1行记录;
画外音：图片请放大。
总结
两类隐蔽的不能利用索引的case：
- 表列类型，与where值类型，不一致;
- join表的字符编码不同;
画外音：本文测试于MySQL5.6。
【本文为51CTO专栏作者“58沈剑”原创稿件，转载请联系原作者】
戳这里，看该作者更多好文 【编辑推荐】 - MySQL：硬盘在24 * 7工作中罢工了，我该怎么办？
- 心里没点B树，怎能吃透数据库索引底层原理？
- 两个小工具，MySQL死锁分析，新技能又Get！
- 如何避免回表查询？什么是索引覆盖？ | 1分钟MySQL优化系列
【责任编辑：赵宁宁 TEL：（010）68476606】
点赞 0 （编辑：辽源站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
