【TiDB 版本】4.0.9
【问题描述】
在同样的2个表结构下，在不同的库当中查询的时候，出现一个order by没有数据的现象。
但是如果用左连接的话，就可以了
这是啥原因
1.麻烦提供下 order by 无数据和 left jion 后有数据这两条 SQL 的真实执行计划（explian analyze ）、以及涉及的表结构信息；
2.请核实下在查询这两个 SQL 时 tidb.log 里有无什么异常信息。
能否提供一下和上面 SQL 完全一致的执行计划，这里 SQL 没有包含 limit 限制。
1.请反馈下这条语句在库 ims_b28_weiduoliya 上的执行计划：
explain analyze select * from ims_wash_code_detail a join ims_user b on a.user_id=b.user_id where a.create_time >= '2021-01-30 00:00:00' limit 4;
2.在库 ims_b28_weiduoliya 上执行 ADMIN CHECK TABLE ims_user; ，看下 ims_user 上是否存在索引不一致的情况。
root@mysql 10:13:07 [ims_b01_tiantian]> show index from ims_user;
±---------±-----------±-------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
±---------±-----------±-------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
| ims_user | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| ims_user | 1 | create_time_index | 1 | create_time | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 1 | update_time_index | 1 | update_time | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 1 | level_id_index | 1 | level_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 1 | channel_flag_index | 1 | channel_flag | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 0 | user_id_index | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 1 | user_flag_index | 1 | user_flag | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| ims_user | 1 | phone_index | 1 | phone | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
±---------±-----------±-------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------±-----------+
8 rows in set (0.00 sec)
对比了下在库 ims_b28_weiduoliya 上查询有数据和无数据的几条 SQL 执行计划，发现在查询无数据时走到了ims_user表的 user_id 索引，而查询有数据时都是直接全表扫描 ims_user表。不知道你这边是否方便删掉这个索引或者强制忽略掉该索引（hint 方式）后再执行下 order by limit 4 这个语句，看下是否有查询结果。
果然，删除了索引就可以查询了。
那意思是表和表的关联和走的执行计划有关啊。那这个不是有点鸡肋啊
1.执行计划是如何走的是其次的，目前看起来是原先 user_id 索引有问题，导致根据索引回表时取不到正确结果；
2.如果方便的话麻烦再创建下 user_id 索引 ，然后再查询下 order by limit 4 这个 SQL ，看下执行结果是否正常，同时把执行计划也再提供下。
desc analyze select * from ims_wash_code_detail a join ims_user b on a.user_id=b.user_id where a.create_time >= ‘2021-01-30 00:00:00’ order by a.create_time limit 4;
看看加了 order by 后 desc analyze 的结果呢？
麻烦提供下相关表的 schema，我们试着复现下。
已经定位的原因，确实是个 bug。同样是因为 collation 的问题，后面会修复。可以下加上 hint 绕过去：
select /*+ INL_JOIN(a, b) */ * from ims_wash_code_detail a join ims_user b on a.user_id=b.user_id where a.create_time >= ‘2021-01-30 00:00:00’ order by a.create_time limit 4;
正确的执行计划应该是：
mysql> desc SELECT /*+ INL_JOIN (u, iub) */ u.user_id, u.level_id, u.user_level_lock, u.parent_user_id AS parentUserId, u.real_name, u.image_url, u.status, u.swich_status, u.login_ip, u.register_ip, u.last_login_date, u.create_time, u.phone, u.email, u.qq, u.birthday, u.nick_name, u.is_test_online, u.remark, u.safety_box_passwd, u.vip_id, u.channel_flag, u.channel_flag_sub, u.bank_passwd, u.user_flag, u.device_type, u.vip_exp, iub.count_in_money, iub.count_out_money, iub.total_money, iub.freeze_money, iub.current_dml, iub.chuk_dml, iub.count_valid_touzhu_money FROM ims_user u use index(user_id_index) JOIN ims_user_balance iub ON u.user_id = iub.user_id where u.user_id > "a" order by u.user_id limit 4;
+--------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_9 | 0.33 | root | | test.ims_user.user_id, test.ims_user.level_id, test.ims_user.user_level_lock, test.ims_user.parent_user_id, test.ims_user.real_name, test.ims_user.image_url, test.ims_user.status, test.ims_user.swich_status, test.ims_user.login_ip, test.ims_user.register_ip, test.ims_user.last_login_date, test.ims_user.create_time, test.ims_user.phone, test.ims_user.email, test.ims_user.qq, test.ims_user.birthday, test.ims_user.nick_name, test.ims_user.is_test_online, test.ims_user.remark, test.ims_user.safety_box_passwd, test.ims_user.vip_id, test.ims_user.channel_flag, test.ims_user.channel_flag_sub, test.ims_user.bank_passwd, test.ims_user.user_flag, test.ims_user.device_type, test.ims_user.vip_exp, test.ims_user_balance.count_in_money, test.ims_user_balance.count_out_money, test.ims_user_balance.total_money, test.ims_user_balance.freeze_money, test.ims_user_balance.current_dml, test.ims_user_balance.chuk_dml, test.ims_user_balance.count_valid_touzhu_money |
| └─TopN_12 | 0.33 | root | | test.ims_user.user_id, offset:0, count:4 |
| └─IndexJoin_20 | 0.33 | root | | inner join, inner:IndexLookUp_19, outer key:test.ims_user_balance.user_id, inner key:test.ims_user.user_id, equal cond:eq(test.ims_user_balance.user_id, test.ims_user.user_id) |
| ├─TableReader_31(Build) | 1.00 | root | | data:TableFullScan_30 |
| │ └─TableFullScan_30 | 1.00 | cop[tikv] | table:iub | keep order:false, stats:pseudo |
| └─IndexLookUp_19(Probe) | 1.00 | root | | |
| ├─Selection_18(Build) | 1.00 | cop[tikv] | | gt(test.ims_user.user_id, "a") |
| │ └─IndexRangeScan_16 | 1.00 | cop[tikv] | table:u, index:user_id_index(user_id) | range: decided by [eq(test.ims_user.user_id, test.ims_user_balance.user_id)], keep order:false, stats:pseudo |
| └─TableRowIDScan_17(Probe) | 1.00 | cop[tikv] | table:u | keep order:false, stats:pseudo |
+--------------------------------------+---------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
由于 join key 的 collation 不同，无法使用更好的执行计划。如果后续希望调优，最好使用相同的 collation。
好的，感谢
你好，我想请问，现在我想在线更改collation，但是有索引。所以更改不了，必须要把索引删掉才能更改吗。那还必须停机啊。