今天做了一个MySQL数据库中的SQL优化。
结论是关联字段字符集不同，导致索引不可用。
查询的SQL如下：
select
`Alias`.`Grade`,
`Alias`.`id`,
`Alias`.`Cust_Name`,
`Alias`.`Agent_Code1`
from `database_name1`.`TAB1` as `Alias`
where (
`Alias`.`Agent_Code1` = '1090300496329'
and `Alias`.`id` in (
select `database_name1`.`TAB2`.`B`
from `database_name1`.`TAB2`
join `database_name1`.`TAB3` as `T1`
on `T1`.`id` = `database_name1`.`TAB2`.`A`
where (
`T1`.`Cust_Type` in (
'1200001', '1200002'
)
and `T1`.`id` in (
select `database_name1`.`TAB4`.`B`
from `database_name1`.`TAB4`
join `database_name1`.`TAB5` as `T2`
on `T2`.`id` = `database_name1`.`TAB4`.`A`
where `T2`.`Cont_Meth_Tp_Cd` = '1220001'
)
)
)
)
order by
`Alias`.`Cust_Name` asc,
`Alias`.`id` asc
limit 11
offset 0
SQL的执行计划如下 ：
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Alias
partitions: NULL
type: ref
possible_keys: PRIMARY
key_len: 203
ref: const
rows: 894
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: TAB2
partitions: NULL
type: ref
possible_keys: TAB2_IDX
key: TAB2_IDX
key_len: 103
ref: database_name1.Alias.id
rows: 1
filtered: 100.00
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: HDL_ADD_CUSTOMER_Alias
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 77
ref: func
rows: 1
filtered: 55.42
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: TAB4
partitions: NULL
type: index
possible_keys: NULL
key: TAB4_IDX
key_len: 206
ref: NULL
rows: 5852807
filtered: 100.00
Extra: Using where; Using index
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: T2
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 142
ref: database_name1.TAB5.A
rows: 1
filtered: 50.00
Extra: Using where; FirstMatch(Alias)
5 rows in set, 1 warning (0.01 sec)
由于对MySQL执行计划不熟，看了半天也没看出有啥问题。
但是第4行有一个地方引起我的注意了，possible_keys = NULL ,key = TAB4_IDX
字面理解：可能走的索引没有，实际上走了索引。
实际上是没有走索引范围扫描。后来从MySQL Wordbench 的执行计划里找到了端倪。
在TAB4表上的关联字段ID发生了隐式转换，这个字段的字符集是 gbk的， 而t2.id 字符集是utf8 的；
结果导致转换后，tab4上的索引没法使用。发生了 block nested loops;
从索引中读所有数据到内存。 index full scan ;
确认这段时间可以更新，把ID字段字符集都改成一致即可。
ALTER TABLE `database_name1`.`tab3`
CHANGE COLUMN `id` `id` VARCHAR(25) CHARACTER SET 'gb18030' NOT NULL ;