记一次MySql优化问题

柔情只为你懂 2023-07-07 08:05 2阅读 0赞

## 一、背景 ##

此次查询涉及三个大表的联合查询，即: **x\_location\_t, x\_account\_t, x\_application\_t** ,每张表的记录大概200万条记录，现在需求是联合查询三个表的记录筛选出用户所需要的条件,原始的sql查询语句如下:

select 
    al.id, ln.application_id, al.user_account, case al.user_type when 'w3' then "内部用户" when 'others' then '外部用户' end as user_type, al.country_code, al.country_name, al.ip_address, al.province, al.city, al.detail_address, al.earliest_time, al.latest_time, al.create_by, al.create_time, al.update_by, al.update_time, al.source, case al.source when '1' then "userProfile" when '2' then 'IPNET' end as source_desc, a.code appCode, a.name appName 
    from x_location_t al 
    JOIN x_account_t  ln on ln.user_type = al.user_type and ln.user_account = al.user_account 
    JOIN x_application_t   a on ln.application_id = a.id 
    WHERE ln.application_id ="39" and al.user_type = "others"
    order by al.earliest_time desc limit 10;

前端执行查询时间大概30秒左右，执行sql语句大概15秒左右，测试环境执行报网关超时异常。对上述sql语句进行sql分析发现全部命中**索引**：

EXPLAIN select 
    al.id, ln.application_id, al.user_account, case al.user_type when 'w3' then "内部用户" when 'others' then '外部用户' end as user_type, al.country_code, al.country_name, al.ip_address, al.province, al.city, al.detail_address, al.earliest_time, al.latest_time, al.create_by, al.create_time, al.update_by, al.update_time, al.source, case al.source when '1' then "userProfile" when '2' then 'IPNET' end as source_desc, a.code appCode, a.name appName 
    from x_location_t al 
    JOIN x_account_t ln on ln.user_type = al.user_type and ln.user_account = al.user_account 
    JOIN x_application_t a on ln.application_id = a.id 
    WHERE ln.application_id ="39" and al.user_type = "others"
    order by al.earliest_time desc limit 10;

进行分析发现大部分时间在**al.earliest\_time desc 倒排序**且没有走多表联合查询索引

## 二、优化 ##

根据业务需求和表的设计进行相关sql语句进行优化操作，分析每层执行sql所需要的时间，发现三个表的查询语句可以整合为**单表查询**，且查询所花时间不超过1s，优化后的sql语句如下:

select 
    al.id, al.user_account, al.user_type , al.country_code, al.country_name, 
    al.ip_address, al.province, al.city, al.detail_address, 
    al.earliest_time, al.latest_time, al.create_by, al.create_time, al.update_by, al.update_time, al.source
    from x_location_t al 
    WHERE 
    	al.user_type = "others" 
    	and exists (select acc.id from x_account_t acc where  acc.user_account = al.user_account and acc.application_id = 39 )
    order by al.id desc 
    limit 100;

查询命中**主键索引**:

EXPLAIN select 
    al.id, al.user_account, al.user_type , al.country_code, al.country_name, 
    al.ip_address, al.province, al.city, al.detail_address, 
    al.earliest_time, al.latest_time, al.create_by, al.create_time, al.update_by, al.update_time, al.source
    from x_location_t al 
    WHERE 
    	al.user_type = "others" 
    	and exists (select acc.id from x_account_t acc where  acc.user_account = al.user_account and acc.application_id = 39 )
    order by al.id desc 
    limit 100;

对于x\_application\_t 表中特定的应用进行查询可以直接取出应用id(上述.application\_id = 39 )，加入现有的sql语句中作为条件进行查询，总结在做sql优化过程中建立**索引、视图、存储过程**都是在原有的sql语句上做优化，有时并不能很好地提高sql执行效率，有时可以**结合业务需求和重写sql语句来进行优化**，进而从根本上对sql查询的优化。