工作中SQL优化实例1

CREATE TABLE `c_done_code` (
  `done_code` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '业务流水号',
  `login_sn` bigint(20) DEFAULT NULL COMMENT '登陆流水号(从服务端session信息中提取)',
  `busi_code` int(11) DEFAULT NULL COMMENT '业务编号',
  `user_id` int(11) DEFAULT NULL COMMENT '用户id',
  `program_id` int(11) DEFAULT NULL COMMENT '节目id',
  `room_id` int(11) DEFAULT NULL COMMENT '直播间id',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `user_type` varchar(20) CHARACTER SET utf8 DEFAULT '' COMMENT '操作时点的用户类型(用于区分是否管理员操作的记录)',
  PRIMARY KEY (`done_code`)
) ENGINE=InnoDB AUTO_INCREMENT=10980079 DEFAULT CHARSET=utf8mb4;

慢SQL，原表有1000W+的数据，Limit中的offset过大，光扫描就4775382行。执行时间达到了11秒

SELECT * FROM c_done_code WHERE user_type = 'VIEWER' ORDER BY done_code ASC LIMIT 2000000,10;

2019021401.png

优化

1. 添加复合索引 KEY idx_user_type_done_code (user_type,done_code) USING BTREE。注意字段顺序不能颠倒
2. 子查询优化，时间提升为0.7秒

SELECT
    c.*
FROM
    (
        SELECT
            done_code
        FROM
            c_done_code
        WHERE
            user_type = 'VIEWER'
        ORDER BY
            done_code ASC
        LIMIT 2000000,
        10
    ) a
LEFT JOIN c_done_code c ON a.done_code = c.done_code;

2019021402.png