[MySQL] Using join buffer (Block Nested Loop) in left join due to character set in mysql

I found that the index was not used when querying a sql today

I read it carefully many times, and I have added all the indexes that should be added, but it still doesn't work

View with explain

Why does the index fail

1. Implicit conversion causes index invalidation
2. With the growth of the table, the data from the where condition is too much, more than 15%, which makes the index invalid (it will cause the CBO calculation to use the index to cost more than the whole table)
3. Inconsistent character sets cause the index to fail, one utf8 and one utf8mb4

My main problem here is that the character set is inconsistent and the index is invalid

Convert the character set successfully

ALTER TABLE user CONVERT TO CHARSET utf8mb4;