问题简介
两张表的关联，bbb表180W记录，fff表10条记录，关联字段均有索引，但是bbb大表使用的全表扫描
环境说明
tidb v7.1.0环境
问题详细说明
bbb和fff表，选择率也可以，统计信息正常，但是bbb表使用table full scan全表扫描的方式，即使使用hint /*+ inl_join(fff,bbb)*/，也只是修改为了index full scan，没有根据关联字段进行范围扫描。
解决方案
查看表结构，两个表的collate不同，关联列字段collate排序规则不同导致，理论上调整规则一致就会走符合要求的执行计划，但是要评估修改collate的影响。
拓展
测试信息：fff和bbb表结构基本相同，fff 10条数据，bbb 180W。两列，table_name 的collate为utf8mb4_general_ci，new_table_name的collate为utf8mb4_bin。fff.table_name的collate为utf8mb4_bin，查询计划差别很大。
执行计划分别如下
不同collate排序规则字段关联查询
explain analyze select * from fff f,bbb b where f.TABLE_NAME=b.TABLE_NAME and f.TABLE_NAME='UPFS_ORDER_INFO_COPY';
bbb表全表扫描
使用hint强制使用INL_JOIN连接
bbb表变成index full scan
explain analyze select /*+ inl_join(fff,bbb) /f. from fff f,bbb b where f.TABLE_NAME=b.TABLE_NAME and f.TABLE_NAME='UPFS_ORDER_INFO_COPY';
相同collate排序规则字段关联查询
explain analyze select * from fff f,bbb b where f.TABLE_NAME=b.new_table_name and f.TABLE_NAME='UPFS_ORDER_INFO_COPY';
SQL上进行collate的转换可以正常走大表的索引
explain analyze select * from ccc c ,ddd d where c.TABLE_NAME =d.new_table_name collate utf8mb4_general_ci and d.new_table_name='shop_cart';
字段上带有索引则不允许修改collate
mysql> alter table ddd modify new_table_name varchar(64) collate utf8mb4_general_ci;
ERROR 8200 (HY000): Unsupported modifying collation of column 'new_table_name' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.
bbb使用的是information_schema.tables的表结构。
| bbb | CREATE TABLE bbb (
TABLE_CATALOG varchar(512) DEFAULT NULL,
TABLE_SCHEMA varchar(64) DEFAULT NULL,
table_name varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
TABLE_TYPE varchar(64) DEFAULT NULL,
ENGINE varchar(64) DEFAULT NULL,
VERSION bigint(21) DEFAULT NULL,
ROW_FORMAT varchar(10) DEFAULT NULL,
TABLE_ROWS bigint(21) DEFAULT NULL,
AVG_ROW_LENGTH bigint(21) DEFAULT NULL,
DATA_LENGTH bigint(21) DEFAULT NULL,
MAX_DATA_LENGTH bigint(21) DEFAULT NULL,
INDEX_LENGTH bigint(21) DEFAULT NULL,
DATA_FREE bigint(21) DEFAULT NULL,
AUTO_INCREMENT bigint(21) DEFAULT NULL,
CREATE_TIME datetime DEFAULT NULL,
UPDATE_TIME datetime DEFAULT NULL,
CHECK_TIME datetime DEFAULT NULL,
TABLE_COLLATION varchar(32) DEFAULT 'utf8mb4_bin',
CHECKSUM bigint(21) DEFAULT NULL,
CREATE_OPTIONS varchar(255) DEFAULT NULL,
TABLE_COMMENT varchar(2048) DEFAULT NULL,
TIDB_TABLE_ID bigint(21) DEFAULT NULL,
TIDB_ROW_ID_SHARDING_INFO varchar(255) DEFAULT NULL,
TIDB_PK_TYPE varchar(64) DEFAULT NULL,
TIDB_PLACEMENT_POLICY_NAME varchar(64) DEFAULT NULL,
new_table_name varchar(64) DEFAULT NULL,
new_col1 varchar(64) DEFAULT NULL,
KEY i_1 (table_name),
KEY i_11 (new_table_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
;