bugs:db:2018092901
差别
这里会显示出您选择的修订版和当前版本之间的差别。
| 两侧同时换到之前的修订记录 前一修订版 后一修订版 | 前一修订版 | ||
|
bugs:db:2018092901 [2019/03/15 10:05] jinlong |
bugs:db:2018092901 [2020/07/12 12:07] (当前版本) |
||
|---|---|---|---|
| 行 1: | 行 1: | ||
| - | # 2018092901-一次left join导致的性能问题 | + | ====== 2018092901-一次left join导致的性能问题 ====== |
| - | ## 背景说明 | + | ===== 背景说明 ===== |
| - | ### 基础环境介绍 | + | ==== 基础环境介绍 ==== |
| - | * 应用环境: 基于springcloud的java应用 | + | * 应用环境: 基于springcloud的java应用 |
| - | * 数据库：mysql | + | * 数据库：mysql |
| - | * DAO: mybatis+pagehelper | + | * DAO: mybatis+pagehelper |
| - | ### 主要库表结构 | + | ==== 主要库表结构 ==== |
| - | * leads_info 销售线索主表，数据量约40万条，本次涉及的关键字段: | + | * leads_info 销售线索主表，数据量约40万条，本次涉及的关键字段: |
| - | * id 线索编号 | + | * id 线索编号 |
| - | * phone_no 客户电话号码 | + | * phone_no 客户电话号码 |
| - | * cust_name 客户姓名 | + | * cust_name 客户姓名 |
| - | * first_duplicate_id 本线索第一条重复线索的id | + | * first_duplicate_id 本线索第一条重复线索的id |
| - | * last_duplicate_id 本线索的最后一条重复线索 | + | * last_duplicate_id 本线索的最后一条重复线索 |
| - | * enquire_date 线索留资时间 | + | * enquire_date 线索留资时间 |
| - | * cust_base_info 若客户姓名有修改记录，数据量约1300条 | + | * cust_base_info 若客户姓名有修改记录，数据量约1300条 |
| - | * id 修改记录id | + | * id 修改记录id |
| - | * leads_id 线索id | + | * leads_id 线索id |
| - | * name 客户姓名 | + | * name 客户姓名 |
| - | ### 主要业务逻辑： | + | ==== 主要业务逻辑：==== |
| - | 1. 重复线索：若有同样电话的销售线索，则新插入一条数据，将新插入的数据的first_group_id设置为第一条同样号码的线索；第一条线索的last_group_id设置为最新的一条线索id | + | - 重复线索：若有同样电话的销售线索，则新插入一条数据，将新插入的数据的first_group_id设置为第一条同样号码的线索；第一条线索的last_group_id设置为最新的一条线索id |
| - | 2. 修改客户姓名： | + | - 修改客户姓名： |
| - | 1. 在cust_base_info中查找对应线索id的数据，若有则更新数据，若无则插入一条数据； | + | - 在cust_base_info中查找对应线索id的数据，若有则更新数据，若无则插入一条数据； |
| - | 2. 若此线索为重复线索，则所有修改只针对其first_group_id来做 | + | - 若此线索为重复线索，则所有修改只针对其first_group_id来做 |
| > PS：再次吐槽这样的设计 | > PS：再次吐槽这样的设计 | ||
| 行 34: | 行 34: | ||
| - | ## 问题发现 | + | ===== 问题发现 ===== |
| - | ### 问题症状 | + | ==== 问题症状 ==== |
| 原来系统在执行的过程中，就发现系统在列表与分页的过程中，发现系统比较慢，但是还可以接受，后来发现系统越来越慢，直至导致springcloud超时（我们设置了15秒的超长超时时间） | 原来系统在执行的过程中，就发现系统在列表与分页的过程中，发现系统比较慢，但是还可以接受，后来发现系统越来越慢，直至导致springcloud超时（我们设置了15秒的超长超时时间） | ||
| 行 42: | 行 42: | ||
| ### 关键sql | ### 关键sql | ||
| 经过查询日志，发现出现问题的列表sql如下 | 经过查询日志，发现出现问题的列表sql如下 | ||
| - | ~~~sql | + | |
| + | <code sql> | ||
| select | select | ||
| a.id as "id", | a.id as "id", | ||
| 行 66: | 行 67: | ||
| a.enquire_date desc | a.enquire_date desc | ||
| LIMIT 20 | LIMIT 20 | ||
| - | ~~~ | + | </code> |
| - | > * where条件主要是为了只取最新的一条数据 | + | > where条件主要是为了只取最新的一条数据 |
| - | > * join的条件是为了保证在新线索来以前修改的客户姓名 | + | > join的条件是为了保证在新线索来以前修改的客户姓名 |
| - | ## 问题分析与解决 | + | ===== 问题分析与解决 ===== |
| - | ### 第一步，分析leads_info | + | ==== 第一步，分析leads_info ==== |
| 发现leads_info表除了id是primary key以外，无任何索引，先给他加上索引 | 发现leads_info表除了id是primary key以外，无任何索引，先给他加上索引 | ||
| 行 85: | 行 86: | ||
| 添加索引后，发现速度有所提升，但是并没有明显的提升 | 添加索引后，发现速度有所提升，但是并没有明显的提升 | ||
| - | ### 第二部，分析join表 | + | ==== 第二部，分析join表 ==== |
| 首先去掉left join cust_base_info，执行下面的sql | 首先去掉left join cust_base_info，执行下面的sql | ||
| - | ~~~sql | + | <code sql> |
| select | select | ||
| a.id as "id", | a.id as "id", | ||
| 行 108: | 行 109: | ||
| a.enquire_date desc | a.enquire_date desc | ||
| LIMIT 20 | LIMIT 20 | ||
| - | ~~~ | + | </code> |
| 发现速度飞快，可以确定是这个join的问题 | 发现速度飞快，可以确定是这个join的问题 | ||
| 行 116: | 行 117: | ||
| 建立索引后，发现上述sql性能有了飞速的提升，在dbeaver中查询时间降低到了ms级别，但是在生产系统修改了库表以后，发现前端展示仍未能解决，相应时间有所降低，但是仍然无法忍受，需要等待将近10s | 建立索引后，发现上述sql性能有了飞速的提升，在dbeaver中查询时间降低到了ms级别，但是在生产系统修改了库表以后，发现前端展示仍未能解决，相应时间有所降低，但是仍然无法忍受，需要等待将近10s | ||
| - | ### 最终解决 | + | ==== 最终解决 ==== |
| 继续查询系统日志，发现因为分页的需要，在执行列表查询以前，系统需要首先做一个count的操作，具体sql如下： | 继续查询系统日志，发现因为分页的需要，在执行列表查询以前，系统需要首先做一个count的操作，具体sql如下： | ||
| - | ~~~sql | + | <code sql> |
| select | select | ||
| - | count(1) | + | count(1) |
| from | from | ||
| - | leads_info a | + | leads_info a |
| left join sys_user b on | left join sys_user b on | ||
| - | a.next_track_user = b.id | + | a.next_track_user = b.id |
| left join cust_base_info c on | left join cust_base_info c on | ||
| - | (a.id = c.leads_id | + | (a.id = c.leads_id |
| - | or a.first_duplicate_id = c.leads_id) | + | or a.first_duplicate_id = c.leads_id) |
| - | and c.del_flag = 0 | + | and c.del_flag = 0 |
| - | ~~~ | + | </code> |
| 此sql的响应时间为大概9s，找到原因，发现就出在了left join cust_base_info的条件上，因为里面有or条件，故此mysql在执行的时候因为无法保证left join不增加数据条数，所以需要把所有的数据全部完成join以后才能进行count统计，果断将这个条件作了修改： | 此sql的响应时间为大概9s，找到原因，发现就出在了left join cust_base_info的条件上，因为里面有or条件，故此mysql在执行的时候因为无法保证left join不增加数据条数，所以需要把所有的数据全部完成join以后才能进行count统计，果断将这个条件作了修改： | ||
| - | ~~~sql | + | <code sql> |
| select | select | ||
| a.id as "id", | a.id as "id", | ||
| 行 156: | 行 157: | ||
| order by | order by | ||
| a.enquire_date desc | a.enquire_date desc | ||
| - | ~~~ | + | </code> |
| 修改后，系统性能得到飞速提升，列表页面响应时间可以控制在1s以内了。 | 修改后，系统性能得到飞速提升，列表页面响应时间可以控制在1s以内了。 | ||
| - | ## 总结 | + | ===== 总结 ===== |
| 总结本次问题，得出要想在分页时得到最佳的性能，总原则如下： | 总结本次问题，得出要想在分页时得到最佳的性能，总原则如下： | ||
| - | 1. 通过主表（本例中的leads_info)与从表（本例中的cust_base_info）关键，要尽可能的使用left join操作，少用inner join，避免使用outer join | + | - 通过主表（本例中的leads_info)与从表（本例中的cust_base_info）关键，要尽可能的使用left join操作，少用inner join，避免使用outer join |
| - | 2. 主表与其他表left join的条件最好是主表的某一个字段去关联主表的主键或unique的属性值 | + | - 主表与其他表left join的条件最好是主表的某一个字段去关联主表的主键或unique的属性值 |
| - | 3. 关联条件中不要有or条件 | + | - 关联条件中不要有or条件 |
| - | 4. 查询条件集中在主表上，尽量不要涉及从表的字段；唯一可以有的是判断是否关联上，本例中可以用 where b.leads_is is null，对于必须关键上的条件，可以写成 inner join | + | - 查询条件集中在主表上，尽量不要涉及从表的字段；唯一可以有的是判断是否关联上，本例中可以用 where b.leads_is is null，对于必须关键上的条件，可以写成 inner join |
bugs/db/2018092901.1552615509.txt.gz · 最后更改: 2020/07/12 12:07 (外部编辑)