200w的sql优化demo，希望有人解答，学习

这是一张dc3_person的表结构，字段不多，数据有2k条。

CREATE TABLE `dc3_person`(
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `person_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '人员ID',
  `org_index_code` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '所属组织唯一标识码',
  `deleted` tinyint NULL DEFAULT 0 COMMENT '逻辑删标识',
  `tenant_id` bigint NULL DEFAULT NULL COMMENT '租户id',
  `tender_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
);

这是另外一张dc3_person_bind的表结构，和dc3_person是一对多的关系，字段也不多，但是数据有200w条。

CREATE TABLE `dc3_person_bind`(
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `person_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户ID',
  `device_id` bigint NOT NULL COMMENT '设备编号ID',
  `origin_time` datetime NULL DEFAULT NULL COMMENT '采集时间',
  `tenant_id` bigint NULL DEFAULT NULL COMMENT '租户id',
);

重点：下面是需要优化的sql

SELECT
 COUNT( DISTINCT t.id ) num 
FROM
 dc3_person t
 JOIN (
 SELECT
  s.person_id,
  max( s.origin_time ) end_time,
  min( s.origin_time ) start_time 
 FROM
  dc3_person_bind s 
 WHERE
  s.tenant_id = '1506635926490681345' 
  AND ( s.person_id IS NOT NULL AND s.person_id != 'null' ) 
  AND s.device_id IN (
   '1511649614318637057',
   '1511649879646113794',
   '1512039799766224898',
   '1512039951528726530',
   '1512040077731139586',
   '1512040212041142274',
   '1512040745908932610',
   '1513835953176809474',
   '1513836307536777217',
   '1513836480384045057',
   '1513837018324504577',
   '1513837212357201921',
   '1547469746439516161',
   '1563073766925860867',
   '1563073766946832387' 
  ) 
 GROUP BY
  s.person_id 
 ) s ON t.person_id = s.person_id 
WHERE
 t.tenant_id = '1506635926490681345' 
 AND t.deleted = 0 
 AND ( t.tender_name = '一标段' OR '一标段' = '' OR '一标段' IS NULL ) 
ORDER BY
 t.org_index_code

我尝试了很久，但是查询速度依然很慢，有没有人教教我。
我不知道这个问题有没有技术性，但是优化200w的sql对我来说是一次很大的突破，对你我都有很大的帮助！
该问题会永久公开，供大家讨论学习！

数据量大的时候 也可以考虑一下其他层面优化
以下是sql层面优化
1.优化查询条件中的OR语句
当前查询条件中有一个OR语句，可以将其改写为多个条件的形式。例如：

AND ( t.tender_name = '一标段' OR '一标段' = '' OR '一标段' IS NULL ) 
**改为**
AND (
    t.tender_name = '一标段' 
    OR t.tender_name = '' 
    OR t.tender_name IS NULL
)

2.优化子查询
子查询中使用了GROUP BY语句，可以将其改写为INNER JOIN连接查询的方式，这样可以减少子查询的数量。例如

SELECT
    s.person_id,
    max( s.origin_time ) end_time,
    min( s.origin_time ) start_time 
FROM
    dc3_person_bind s 
WHERE
    s.tenant_id = '1506635926490681345' 
    AND ( s.person_id IS NOT NULL AND s.person_id != 'null' ) 
    AND s.device_id IN (
        '1511649614318637057',
        '1511649879646113794',
        '1512039799766224898',
        '1512039951528726530',
        '1512040077731139586',
        '1512040212041142274',
        '1512040745908932610',
        '1513835953176809474',
        '1513836307536777217',
        '1513836480384045057',
        '1513837018324504577',
        '1513837212357201921',
        '1547469746439516161',
        '1563073766925860867',
        '1563073766946832387' 
    ) 
GROUP BY
    s.person_id

**改为**
SELECT
    s.person_id,
    max(s.origin_time) end_time,
    min(s.origin_time) start_time 
FROM
    dc3_person_bind s 
    INNER JOIN dc3_person t ON s.person_id = t.person_id
WHERE
    s.tenant_id = '1506635926490681345' 
    AND ( s.person_id IS NOT NULL AND s.person_id != 'null' ) 
    AND s.device_id IN (
        '1511649614318637057',
        '1511649879646113794',
        '1512039799766224898',
        '1512039951528726530',
        '1512040077731139586',
        '1512040212041142274',
        '1512040745908932610',
        '1513835953176809474',
        '1513836307536777217',
        '1513836480384045057',
        '1513837018324504577',
        '1513837212357201921',
        '1547469746439516161',
        '1563073766925860867',
        '1563073766946832387' 
    )
    AND t.tenant_id = '1506635926490681345'
    AND t.deleted = 0
    AND (
        t.tender_name = '一标段' 
        OR t.tender_name = '' 
        OR t.tender_name IS NULL
    )
GROUP BY
    s.person_id

3.添加索引
可以为dc3_person表的tender_name

3个表的主键、索引都有创建吗？创建了哪些需要列出一下。
dc3_person的tenant_id和deleted使用组合索引。
一个device_id只会属于一个tenant_id吗？如果是：dc3_person_bind上device_id需要索引，tenant_id不需要。查询条件也可以不要tenant_id，但如果考虑device可以转给另一tenant则需要。
dc3_person_bind中person_id not null, 那么sql中的s.person_id IS NOT NULL AND s.person_id != 'null'不需要，后者应该从应用端限制不插入‘null’值。
先这么优化下再看。

img

img


这是两张表的索引列表

1、200W的数据量并不算大，而且还是一张只有5个字段的表，当然这也要数据库引擎以及数据库运行环境相关
2、表的索引不要乱建，索引越多，生产环境的增删改数据越慢，即便是读写分离的查询系统，乱建索引也会浪费磁盘空间
3、你需要明确你的输出目标结果集以及需要满足的查询条件，特别是你前后两个SQL的条件和输出都不同的情况下，让大家觉得无所适从，因为需求上随意增加或减少一个输出列，有可能整个SQL都需要重写
4、你前后两个语句的条件都不同，无法从执行时间上判定哪个语句更优，比如你的 device_id in 部分差得太多……
5、前后两个语句的：tender_name 判定并不一致：

t.tender_name = '一标段' OR '一标段' = '' OR '一标段' IS NULL

这个表示输入参数为：一标段，当输入参数为空（''）或NULL时，tender_name无条件满足
而后面的：

p.tender_name = '一标段' OR p.tender_name IS NULL OR p.tender_name = ''

这个表示tender_name 为：一标段（输入参数）或者表数据中的tender_name字段（而非输入参数） 为NULL或空（''）时满足条件
6、你后面一个SQL中的 HAVING COUNT(*) >= 1 目的是什么？从SQL本身上来看，是多余的
………………

不知道你这个问题是否已经解决, 如果还没有解决的话:

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^