EPPDEV-MANAGE

本页面只读。您可以查看源文件，但不能更改它。如果您觉得这是系统错误，请联系管理员。
====== 2018092901-一次left join导致的性能问题 ======

===== 背景说明 =====

==== 基础环境介绍 ====

* 应用环境: 基于springcloud的java应用
* 数据库：mysql
* DAO: mybatis+pagehelper

==== 主要库表结构 ====

* leads_info 销售线索主表，数据量约40万条，本次涉及的关键字段:
  * id 线索编号
  * phone_no 客户电话号码
  * cust_name 客户姓名
  * first_duplicate_id 本线索第一条重复线索的id
  * last_duplicate_id 本线索的最后一条重复线索
  * enquire_date 线索留资时间
* cust_base_info 若客户姓名有修改记录，数据量约1300条
  * id 修改记录id
  * leads_id 线索id
  * name 客户姓名

==== 主要业务逻辑：====

  - 重复线索：若有同样电话的销售线索，则新插入一条数据，将新插入的数据的first_group_id设置为第一条同样号码的线索；第一条线索的last_group_id设置为最新的一条线索id
  - 修改客户姓名：
    - 在cust_base_info中查找对应线索id的数据，若有则更新数据，若无则插入一条数据；
    - 若此线索为重复线索，则所有修改只针对其first_group_id来做

> PS：再次吐槽这样的设计



===== 问题发现 =====

==== 问题症状 ====

原来系统在执行的过程中，就发现系统在列表与分页的过程中，发现系统比较慢，但是还可以接受，后来发现系统越来越慢，直至导致springcloud超时（我们设置了15秒的超长超时时间）

### 关键sql
经过查询日志，发现出现问题的列表sql如下

<code sql>
select
	a.id as "id",
	a.site_id as "siteId",
	a.name as "name",
        ……
	c.name as "custBaseInfo.name"
from
	leads_info a
left join sys_user b on
	a.next_track_user = b.id
left join cust_base_info c on
	(a.id = c.leads_id
	      or a.first_duplicate_id = c.leads_id)
	and c.del_flag = 0
WHERE
	a.del_flag = 0
	and a.site_id = ?
	and (a.last_duplicate_id is null
	or a.last_duplicate_id is not null
	and a.id = a.last_duplicate_id)
order by
	a.enquire_date desc
LIMIT 20
</code>

> where条件主要是为了只取最新的一条数据
> join的条件是为了保证在新线索来以前修改的客户姓名

===== 问题分析与解决 =====
====  第一步，分析leads_info ====
发现leads_info表除了id是primary key以外，无任何索引，先给他加上索引

* 索引一
  * site_id
  * enquire_date
* 索引二
  * site_id
  * last_duplicate_id
  * enquire_date

添加索引后，发现速度有所提升，但是并没有明显的提升

==== 第二部，分析join表 ====
首先去掉left join cust_base_info，执行下面的sql

<code sql>
select
	a.id as "id",
	a.site_id as "siteId",
	a.name as "name",
        ……
	c.name as "custBaseInfo.name"
from
	leads_info a
left join sys_user b on
	a.next_track_user = b.id
WHERE
	a.del_flag = 0
	and a.site_id = ?
	and (a.last_duplicate_id is null
	or a.last_duplicate_id is not null
	and a.id = a.last_duplicate_id)
order by
	a.enquire_date desc
LIMIT 20
</code>

发现速度飞快，可以确定是这个join的问题

查询cust_base_info表，发现此表的id是主键，leads_id无索引，left join可能存在一对多，对此表的leads_id设置为unique索引

建立索引后，发现上述sql性能有了飞速的提升，在dbeaver中查询时间降低到了ms级别，但是在生产系统修改了库表以后，发现前端展示仍未能解决，相应时间有所降低，但是仍然无法忍受，需要等待将近10s

==== 最终解决 ====
继续查询系统日志，发现因为分页的需要，在执行列表查询以前，系统需要首先做一个count的操作，具体sql如下：

<code sql>
select
    count(1)
from
    leads_info a
left join sys_user b on
    a.next_track_user = b.id
left join cust_base_info c on
    (a.id = c.leads_id
        or a.first_duplicate_id = c.leads_id)
    and c.del_flag = 0
</code>

此sql的响应时间为大概9s，找到原因，发现就出在了left join cust_base_info的条件上，因为里面有or条件，故此mysql在执行的时候因为无法保证left join不增加数据条数，所以需要把所有的数据全部完成join以后才能进行count统计，果断将这个条件作了修改：

<code sql>
select
	a.id as "id",
	a.site_id as "siteId",
	a.name as "name",
        ……
	c.name as "custBaseInfo.name"
from
	leads_info a
left join sys_user b on
	a.next_track_user = b.id
left join cust_base_info c on
	ifnull(a.fist_duplicate_id, a.id) = c.leads_id
	and c.del_flag = 0
WHERE
	a.del_flag = 0
	and a.site_id = ?
	and (a.last_duplicate_id is null
	or a.last_duplicate_id is not null
	and a.id = a.last_duplicate_id)
order by
	a.enquire_date desc
</code>

修改后，系统性能得到飞速提升，列表页面响应时间可以控制在1s以内了。

===== 总结 =====
总结本次问题，得出要想在分页时得到最佳的性能，总原则如下：

  - 通过主表（本例中的leads_info)与从表（本例中的cust_base_info）关键，要尽可能的使用left join操作，少用inner join，避免使用outer join
  - 主表与其他表left join的条件最好是主表的某一个字段去关联主表的主键或unique的属性值
  - 关联条件中不要有or条件
  - 查询条件集中在主表上，尽量不要涉及从表的字段；唯一可以有的是判断是否关联上，本例中可以用 where b.leads_is is null，对于必须关键上的条件，可以写成 inner join
EPPDEV-MANAGE

用户工具

站点工具

页面工具
