主键优化SQL

合集 - SQL优化(5)

1.MySQL的经典SQL优化13例（更新到2024年5月21日）2021-06-082.2021年8月核心MySQL主库优化总结2021-08-123.2022年3月核心库MySQL优化总结2022-02-28

4.主键优化SQL2021-04-06

5.SQL 语句优化--中间表的使用优化2008-11-17

收起

说明：

      结合去年和今年的SQL优化，数据归档的实战经验，部分无法优化的SQL，提供一种优化思路：利用主键优化SQL。

 

案例1：

update ter_cc set tmk_tongji_id=set_tmk_id where set_tmk_id!=0 and tmk_tongji_id !=set_tmk_id

  去年CC的慢SQL，定时执行，运行每次执行超过1.15秒, 同时随着ol_cc 的数据量持续增加，SQL执行越来越慢。

  第一次优化：

     增加索引： `idx_st` (`set_tmk_id`,`tmk_tongji_id`)，查看SQL的执行计划，从下列执行计划来看，的确走了idx_st复合索引，但是慢SQL还是执行比较慢，超过1秒。

   

     和研发沟通，不能再加条件，本来想加create_time的创建时间条件，缩小范围，结果这表没有创建时间字段，那加一个创建时间，单行的什么时间创建不知道，不太合适。

    如何优化？

    后来研发在SQL语句后加了主键（li_id为表主键）范围限制，提高SQL性能，如SQL修改如下：   

update occ set tmk_tongji_id=set_tmk_id where set_tmk_id!=0 and tmk_tongji_id !=set_tmk_id

and li_id>17283938

   修改后SQL的执行计划如下：

         

   SQL的执行计划变成走PRIMARY主键索引，而不是idx_st索引，扫描的行数，从196万+下降到115万+，执行效率提高。

案例2：

  o_list(1.9亿+条)，归档

    表历史数据归档，只保留2021-1-1(按create_time字段)一天的数据，约有100多万条，按

 单该表只有以下2个索引，没有create_time时间：

      RIMARY KEY (`id`),  UNIQUE KEY `un_tbdc` (`teacher_id`,`begin_time`,`data_type`,`cn_id`)

      归档采用建新表，再rename： insert into o_list1 select * from o_list where  create_time=1609430400 and data_type=1

      但表1.9亿+条，因没有create_time索引，insert时间很长，对主业务库影响大，加一个create_time索引，但表大，加索引也对online实例有影响，而且要加最好晚上12点加影响最小，不能为这个等到晚上12点后。

   有什么更好的办法，不加索引，又提高insert效率？

     最好找到一个办法，在没业务使用的灾备库上查出create_time=1609430400 的最大主键id（446704019），和最小主键id的值（393835328 ），将原有Insert修改如下：  

insert into o_list1

select * from o_list  where begin_time=1609430400 and data_type=1 and id>=393835328 and id<=446704019

 

案例3：

  慢SQL执行1.4秒+
 SELECT COUNT(*) AS tp_count FROM `oe` `live` WHERE `live`.`teacher_id` = 2714 AND `live`.`start_time` BETWEEN 1610812800 AND 1616342399 AND `live`.`status` = 1 limit 1

执行计划：显示使用到teacher_id,status_2这2个索引：

 

 而  ol_live表有近20个索引，已经太多，不适合再加索引，如何优化？

 研发同学在优化时，加了一个主键条件，就解决这个慢SQL：

SELECT COUNT(*) AS tp_count FROM `oe` `live` WHERE  `live`.`teacher_id` = 2714 AND `live`.`start_time`

BETWEEN 1610812800 AND 1616342399  AND `live`.`status` = 1  and id>2655596

执行计划如下：

  

  这时执行计划，使用了teacher_id,PRIMARY这2个索引做合并，PRIMARY主键效率比status_2高很多。

 

 说明：

       1，主键id的范围，能提高SQL效率，但数量持续增长，id值要改变,。

       2，数据量大的表，水平切分（表归档或者分表）是最有效的办法。