需求
有个简单的需求，查询操作记录。
涉及两张表：
1).操作记录表
CREATE TABLE `action_log` (
`id` char(40) NOT NULL,
`i_id` char(40) NOT NULL COMMENT '设备id',
`c_id` char(40) DEFAULT NULL COMMENT '公司id',
`b_id` bigint(20) NOT NULL COMMENT '客户id',
`action` varchar(16) NOT NULL COMMENT '操作类型',
`start_time` datetime NOT NULL COMMENT '动作起始时间',
`end_time` datetime DEFAULT NULL COMMENT '动作结束时间',
`operator` varchar(64) NOT NULL COMMENT '操作者',
`log_json` varchar(8192) DEFAULT NULL COMMENT 'log信息',
PRIMARY KEY (`id`),
KEY `idx_i_id` (`i_id`) USING BTREE,
KEY `idx_c_id` (`c_id`) USING BTREE,
KEY `idx_b_id` (`b_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='操作记录表';
2).用户表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT 'ID',
`name` varchar(512) DEFAULT NULL COMMENT '名称',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modify_time` datetime DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息';
操作记录表有50w ~ 100w条，用户表有200条记录
支持通过界面查询，希望尽可能快。
过程
1.查询语句
SELECT XXX
FROM action_log l
JOIN user u ON l.b_id = l.id
WHERE XXXXXX
ORDER BY l.id ASC, l.start_time DESC
第一个问题
1.SELECT COUNT 时间太长，需要超过6秒
因为用到了INNER JOIN ,SQL Explain 一下，用到了 action_log 的主键 和b_id 的索引
经过分析，在SELECT COUNT 的时候，只想知道记录数目，不需要INNER JOIN 用户表，去除 用户表后，
SELECT XXX
FROM action_log l
WHERE XXXXXX
ORDER BY l.id ASC, l.start_time DESC
SELECT COUNT 时间缩短为1秒。
第二个问题
1.在分页查询的时候，比如查询第506380条记录开始的后20条记录，特别的慢
语句如下：
SELECT l.*
FROM action_log l
WHERE XXXXXX
ORDER BY l.id ASC, l.start_time DESC
LIMIT 506380,10
查询时间接近9.5秒
经分析，select 后面带了很多列,尤其是log_json 里面内容太多，严重影响查询性能。
可以做如下修改，先取到第506380条记录开始的后20条记录 的id ，然后再单独查询这写id 的所有记录。
操作如下：
SELECT l.id
FROM action_log l
WHERE XXXXXX
ORDER BY l.id ASC, l.start_time DESC
LIMIT 506380,10
SELECT l.*
FROM action_log l
WHERE XXXXXX
AND l.id in(xxxxxx)
如此修改过后，两个语句共耗时不到1秒。
第三个问题
1.WHERE c_id 耗时太久
查询语句类似：
where c_id IN (
SELECT id FROM XXXX WHERE YYY = xxxx
)
即，查询中还有子查询。这种导致按条件查询一下子耗时增加到了9秒以上。
这个处理会稍微繁琐一点，需要把子查询具体转换到 c_id 中，即
where c_id IN ('abc','cdd',....)
总耗时不到1秒
第四个问题
后续的优化，可以加缓存件等
网友评论