MYSQL 索引优化坑一（字符集不一致导致不走索引）

1、不走索引的原因是如何发现的

由于字符集不一样（utf8,utf8mb4）导致表之间得join操作执行计划显示没有走索引
查看树形执行计划得知

EXPLAIN FORMAT=TREE
SELECT * FROM(
WITH order_data AS 
(SELECT 
  r.`order_id`,
  r.`team_name`,
  r.`tid`,
  r.`patient_head_url`,
  r.`patient_name`,
  r.`inquiry_type`,
  r.`patient_user_id`,
  r.`doctor_accid`,
  r.`patient_accid`,
  urc.`un_read_count` AS un_read_num,
  lm.`msg_id` 
FROM
  team_order_relation r 
  INNER JOIN team_order_last_message lm 
    ON r.`order_id` = lm.`order_id` 
  INNER JOIN team_order_un_read_count urc 
    ON r.`order_id` = urc.`order_id` 
WHERE r.`doctor_user_id` = 1596005852493486) 
SELECT 
  od.`order_id`,
               m.`send_time` AS lastMessageTime,
               m.`message` AS lastMessage,
               m.`from_accid`,
               m.`from_role`,
               m.`type`,
               m.`custom_message_type`,
               od.`team_name`,
               od.`tid`,
               od.`patient_head_url`,
               od.`patient_name`,
               od.`inquiry_type`,
               od.`patient_user_id`,
               od.`doctor_accid`,
               od.`patient_accid`,
               od.`un_read_num` AS un_read_num
FROM
  order_data od 
  INNER JOIN team_message m 
    ON od.`msg_id` = m.`msgid` 
WHERE m.`delete_flag` = 0 ) t0

结果如下

-> Inner hash join (lm.msg_id = convert(m.msgid using utf8mb4))  (cost=6498.80 rows=2417)
    -> Filter: (m.delete_flag = 0)  (cost=746.44 rows=4028)
        -> Table scan on m  (cost=746.44 rows=40277)
    -> Hash
        -> Nested loop inner join  (cost=6.30 rows=6)
            -> Nested loop inner join  (cost=4.20 rows=6)
                -> Index lookup on r using idx_user_id_order_id (doctor_user_id=1596005852493486), with index condition: (r.order_id is not null)  (cost=2.10 rows=6)
                -> Single-row index lookup on lm using uk_order_id (order_id=r.order_id)  (cost=0.27 rows=1)
            -> Single-row index lookup on urc using uk_order_id (order_id=r.order_id)  (cost=0.27 rows=1)

其中convert(m.msgid using utf8mb4)提示字符集不一致 并且没有走索引

查看执行计划得知

其中type为All，全表扫描，没有走索引，效率低下

2、为了让其走索引做了哪些工作
将字符集改成一致（utf8mb4）后

再次执行执行计划

此处已走索引

3、走索引总结

除了需要给指定的字段加索引外，表连接还需要看字符串字符编码是否一致，不然Mysql会去做转换导致不能走索引，大的问题背后是些看不起眼的小问题组成的，平时做好每一件小事，就可以规避很多大问题