字符编码,原来是SQL不走索引的元凶之一!
共 2209字,需浏览 5分钟
·
2021-03-22 12:40
击关注上方“SQL数据库开发”,
设为“置顶或星标”,第一时间送达干货
来源:my.oschina.net/floor/blog/4960148
引言
2021年春节后的某个忙(mo)碌(yu)的下午,旁边的刘哥(老江湖,从业5年+)突然发出了一声叹息:“哎,mysql 出bug了,有索引不走”。
作为一个热心的人,我立即说到:“是不是,对索引字段做了函数操作”。
刘哥沉思了2秒,略有玩味的小眼神看了看我,慢慢说道:“温兄,常规的情况,对索引字段做函数操作,或者 字符串与数字比较造成的隐式转换,这次的SQL都不涉及”。
我一听顿时来了兴趣,略带兴奋的跟刘哥说:“刘哥,您发SQL,我也研究下”。
刘哥略有无奈的看了看我后,依旧把SQL发给了我:
SELECT
*
FROM
oc_order oo
JOIN orders_detail od ON oo.order_id = od.order_id
不过须臾(近20分钟),我便查明了原因,装作大师的模样,一字一句的说到:”这是,字符集隐式转换问题”
刘哥听后,百度了下,直拍大腿,“对对,怎么把这个给忘了”。
定位问题
1.首先执行explain,查看执行计划
explain
SELECT
*
FROM
oc_order oo
JOIN orders_detail od ON oo.order_id = od.order_id
确实被驱动表orders_detail 没走索引
第一个表就是驱动表,后边的都是被驱动表,会从驱动板取出数据作为参数,到被驱动表查询匹配的记录。
2. 查看被驱动表的索引
SHOW INDEX FROM test.orders_detail;
3.查看字段字符集
SELECT
COLUMN_NAME,
character_set_name,
collation_name
FROM
INformation_schema.`COLUMNS`
WHERE
TABLE_NAME = 'oc_order'
AND COLUMN_NAME = 'order_id';
SELECT
COLUMN_NAME,
character_set_name,
collation_name
FROM
INformation_schema.`COLUMNS`
WHERE
TABLE_NAME = 'orders_detail'
AND COLUMN_NAME = 'order_id';
Mysql字符集说明
utf8m4是utf8超集,utf8,与utf8mb4会比较,utf8会转换为utf8mb4。
验证
调整SQL语句,将oc_orders的order_id强制转换为utf8。
explain
SELECT
*
FROM
oc_order oo
JOIN orders_detail od ON CONVERT ( oo.order_id USING UTF8 ) = od.order_id
查看执行计划,确实走了索引
解决方式
调整SQL语句
SELECT
*
FROM
oc_order oo
JOIN orders_detail od ON CONVERT ( oo.order_id USING UTF8 ) = od.order_id
调整字符集一致,建议
总结
可能不走索引的3种情况
对索引字段做函数操作
隐式类型转换,字符串与数字比较,字符串会转换为数字
隐式字符集转换,utf8m4是utf8超集,utf8,与utf8mb4会比较,utf8会转换为utf8mb4.
最后给大家分享我写的SQL两件套:《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等,非常通俗易懂,方便大家跟着一起来实操。
有需要的读者可以下载学习,在下面的公众号「数据前线」(非本号)后台回复关键字:SQL,就行
数据前线
后台回复关键字:1024,获取一份精心整理的技术干货
后台回复关键字:进群,带你进入高手如云的交流群
记得帮忙点「赞」和「在看」↓
谢谢啦