记一次客户信息查询时长过长的问题(mysql字符集不一致)

前言

记一次在公司做java开发时遇到的mysql查询语句过慢的排查，在此记录一下。

数据库版本（mysql5.7,本地复现8.0版本）

1. 地址表的结构如下
   
   
   
2. 客户表结构如下
   
   
   

测试以及分析

sql语句查询如下：

select
    c.Id, c.CorpId, c2.CorpName as CorpName,c.CustomerName, c.CustomerNo, c.Gender, c.Birthday, c.Email, c.ProvinceCode, c.CityCode, c.
    RegionCode, c.Address, c.Telephone, c.Mobile, c.Job, c.RegSource, c.OpenId, c.IsDeleted, c.CreateId, c.CreateBy,
    c.CreateTime, c.ModifyId, c.ModifyBy, c.ModifyTime, concat(ca.Name,'-',ca2.Name) as PlaceName,
    CASE c.Gender WHEN '1' THEN '男' ELSE '女' END as GenderValue,
    year(CURRENT_DATE)-IFNULL(year(c.Birthday),0) as Age
    from customer c
    left join corp c2 on c2.Id = c.CorpId and c2.IsDeleted = false
    left join china_address ca on ca.AddCode = c.ProvinceCode and ca.ParentId = '0'
    left join china_address ca2 on ca2.AddCode = c.CityCode and ca2.ParentId = c.ProvinceCode

工具查询时长平均时长超过2S

原因分析：

select
    c.Id, c.CorpId, c2.CorpName as CorpName,c.CustomerName, c.CustomerNo, c.Gender, c.Birthday, c.Email, c.ProvinceCode, c.CityCode, c.
    RegionCode, c.Address, c.Telephone, c.Mobile, c.Job, c.RegSource, c.OpenId, c.IsDeleted, c.CreateId, c.CreateBy,
    c.CreateTime, c.ModifyId, c.ModifyBy, c.ModifyTime, 
    CASE c.Gender WHEN '1' THEN '男' ELSE '女' END as GenderValue,
    year(CURRENT_DATE)-IFNULL(year(c.Birthday),0) as Age
    from customer c
    left join corp c2 on c2.Id = c.CorpId and c2.IsDeleted = false

在没有join到china_address地址表时，我们发现查询的时间的平均为：0.020 s，所以问题可能出现表连接上。

尝试在连接的字段上建立索引：

ALTER table china_address ADD INDEX ADDCODE_INDEX(AddCode)
ALTER table china_address ADD INDEX PARENTID_INDEX(ParentId)
ALTER table customer ADD INDEX PROVINCECODE_INDEX(ProvinceCode)
ALTER table customer ADD INDEX CITYCODE_INDEX(CityCode)

加上索引后进行查询：发现时间缩短了一半，但是还达不到要求，为什么呢？？？

通过explain我们可以看到的并没有使用到索引，于是查找索引失效的原因
索引失效的原因

索引前：

索引后：

通过排查发现：地址表的字符集和客户表的字符集不一样导致了连接索引失效


表中字段的索引字符集不一致：将其改成 utf8mb4 utf8mb4_general_ci即可


修改后的查询时间如下：

explain查看使用了ADDCODE_INDEX索引

总结

一般索引失效有可能是表数据太少，或查询的结果集太大（比如超过全表2/3），或条件查询不等于，或查询条件使用了函数，或两列字符集不相等。

具体失效原因请查看该博客： https://www.jb51.net/article/176633.htm

https://blog.csdn.net/horses/article/details/107243447