最近在工作中遇到了一个“神奇”的问题，可能对大家有所帮助，所以形成了这篇文章。图片来自Pexels。问题可能是我有两个表，TableA和TableB。TableA表有百万行左右（存量业务数据），TableB表有几行（新的业务场景，数据还没有扩充）。从语义上看，TableA.columnA=TableB.columnA，其中在columnA上建立了索引，但是查询速度极慢，基本5-6秒，显然不符合预期。下面我用一个具体的例子来说明，模拟SQL查询的场景。场景再现了user_info表。为了让场景尽可能简单，我只模拟了三列数据。user_score表，其中uid和user_info.uid具有相同的语义。数据情况如下，都是很常见的场景：索引情况如下：查询业务场景：知道user_score.id，需要关联查询user_info对应的信息（先不管这个具体业务场景是否合理或不）。那么对应的SQL自然是如下：请忽略其中的数据。我一开始模拟了100W，后来导入了两次，所以数据有一些重复。300W数据，最后查询也是1.18秒，按道理应该更快，老规矩解释一下，看看发生了什么？发现user_info表没有索引，全表扫描近300W数据？现象是这样的，为什么？大家不妨想一想，如果遇到这种情况，应该如何排查呢？我当时也是“猛操作如虎”，结果没用？我尝试了什么样的SQL写法来完成这个操作。比如更换Join表的顺序（驱动表/驱动表），或者使用子查询。最后，还是没有结果。但是直接单表查询和写SQL确实可以使用索引。解决问题，尝试改变搜索条件，比如改变uid直接关联查询，索引还是不行，差点放弃。正准备向DBA求助前，看了下表的建表语句：我完全有理由怀疑是因为字符集不一致导致索引失效。所以修改小表的字符集（不要乱动线上真实环境）使其与大表保持一致，然后测试：mysql>select*fromuser_scoreus->innerjoinuser_infouionus.uid=ui.uid->whereus.id=5;+-----+------------+--------+--------+----------+---------+|id|uid|分数|id|uid|名称|+----+------------+--------+---------+------------+--------+|5|111111111|100|1|111111111|唐莱||5|111111111|100|3685399|111111111|Tanglei||5|11111111|100|3685400|111111111|Tanglei||5|11111111|100|3685401|3685401|11111111|Tanglei|tanglei||5|11111111|100|100|100|3685402|3685402|11111111|11111111|11111111|11111111111111111111111111111111111111111111111111111111111111111111111111111111111往3685403|111111111|唐磊|+----+------------+--------+--------+-----------+--------+6rowsinset(0.00sec)mysql>explain->select*fromuser_scoreus->innerjoinuser_infouionus.uid=ui.uid->whereus.id=5;+----+-----------+--------+--------+----------------+-----------+--------+--------+------+--------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+------------+--------+-------+--------------------+------------+--------+-------+------+------+|1|SIMPLE|us|const|PRIMARY,index_uid|PRIMARY|4|const|1|NULL||1|SIMPLE|ui|ref|index_uid|index_uid|194|const|6|空|+----+------------+--------+--------+------------------+------------+--------+------+------+-------+2rowsinset(0.00sec)确实有效。挖掘根源，其实挖掘根源就是网上各种MySQL军规/规约中提到的“不参与索引列的计算”。对于这种情况，如果知道explainextended+showwarnings这个工具（之前explain之后还不知道可以加extended参数），可能会尽快“恍然大悟”。（最新的MySQL8.0版本好像不需要加这个关键字了）看看效果：（啊，我要把字符集改回去）mysql>explainextendedselect*fromuser_scoreusinnerjoinuser_infouionus.uid=ui.uidwhereus.id=5;+----+------------+--------+--------+------------------+--------+--------+--------+--------+---------+------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+--------+--------+--------------------+----------+--------+--------+--------+--------+------------+|1|SIMPLE|us|const|PRIMARY,index_uid|PRIMARY|4|const|1|100.00|NULL||1|SIMPLE|ui|ALL|NULL|NULL|NULL|空|2989934|100.00|Usingwhere|+----+------------+------+------+-------------------+--------+--------+--------+--------+-----------+------------+2rowsinset,1warning(0.00sec)mysql>showwarnings;+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|级别|代码|消息|+--------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|注意|1003|/*select#1*/select'5'AS`id`,'111111111'AS`uid`,'100'AS`score`,`test`.`ui`.`id`AS`id`,`test`.`ui`.`uid`AS`uid`,`test`.`ui`.`name`AS`name`from`test`.`user_score``us`join`test`.`user_info``ui`where(('111111111'=convert(`test`.`ui`.`uid`usingutf8mb4)))|+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)索引列参与计算，每次都需要根据字符集进行转换，全表扫描，你觉得能不能快点？至于为什么会出现这个问题？总的来说，是因为历史原因，旧业务场景中的原表是fakeutf8，而新业务中的新表使用的是真正的utf8mb4①在考虑新表时，忽略与原库字符集的比较。其实发现库中不同的表可能有不同的字符集，不同的人在建库的时候可能会根据个人喜好选择不同的字符集。由此可见开发规范的重要性。②虽然我们知道索引列不能参与计算，但是在这个场景下都是同一种类型，varchar(64)在最后的查询过程中还是进行了类型转换。因此，需要将不一致的字段字符集等同于不一致的字段类型。③如果这个case使用了fail-fast的概念，发现不一致，直接join是不是更好？（就像charv.svarchar不能join一样）说明：本文测试场景基于MySQL5.6。另外，本文的案例只是为了说明问题，里面的SQL并不规范（比如尽量不要使用select*之类的），请勿模仿（本人不负责模仿）。最后留下一个思想问题进行讨论，欢迎留言发表你的看法。你能解释一下下面的情况吗？为什么查询结果不一致？注意SQL的执行顺序，查询优化器的工作流程，以及Usingjoinbuffer(BlockNestedLoop)。可以看看MySQL官方手册，了解更多背后的流程和原理：https://dev.mysql.com/doc/refman/5.6/en/于大疆、宜信、腾讯、友盟。编辑：陶家龙来源：转载自公众号节目袁诗诗（ID：tangleithu）
