优化count
创建一张表用来记录日志表的总数据量:
create table log_counter(logcount bigint not null)engine = innodb default CHARSET = utf8;
在每次插入数据之后,更新该表 :
<update id="updateLogCounter" >update log_counter set logcount = logcount + 1</update>
在进行分页查询时, 获取总记录数,从该表中查询既可。
<select id="countLogFromCounter" resultType="long">select logcount from log_counter limit 1</select>
优化 limit
在进行分页时,一般通过创建覆盖索引,能够比较好的提高性能。一个非常常见,而又非常头疼的分页场景就是 "limit 1000000,10" ,此时MySQL需要搜索出前1000010 条记录后,仅仅需要返回第 1000001 到 1000010 条记录,前1000000 记录会被抛弃,查询代价非常大。
当点击比较靠后的页码时,就会出现这个问题,查询效率非常慢。
优化SQL:
select * from operation_log limit 3000000 , 10;
将上述SQL优化为 :
select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;
<select id="selectListByCondition" parameterType="map" resultType="operationLog">selectid ,operate_class as operateClass ,operate_method as operateMethod,return_class as returnClass,operate_user as operateUser,operate_time as operateTime,param_and_value as paramAndValue,cost_time as costTime,return_value as returnValuefrom operation_log t,(select id from operation_log <where><include refid="oplog_where"/></where>order by id limit #{start},#{rows}) b where t.id = b.id </select>