MYSQL分页查询优化

最新推荐文章于 2024-04-21 19:30:31 发布

king1701007

最新推荐文章于 2024-04-21 19:30:31 发布

阅读量236

点赞数

分类专栏： MYSQL 问题文章标签： mysql sql java

本文链接：https://blog.csdn.net/king1701007/article/details/115628562

版权

问题同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

MYSQL

2 篇文章 0 订阅

订阅专栏

MYSQL查询优化记录

项目上遇到一个千万级别的数据库查询，仅查询id就100秒以上。故而看看怎么优化。
然后新学到分页时查询用SQL_CALC_FOUND_ROWS，可一步查询总量；
每页数量不多时，分页查询条件匹配仅查询ID，再根据ID查询匹配具体列数据。

问题分析
1、根据查询语句，添加相应索引
2、查看具体执行语句，是否为全表查询，是否使用索引，索引列是否使用函数，是否可以从逻辑上优化，不只是从SQL上。必要时使用强制索引force index(索引名)
3、表设计是否合理，类型，大小等。
4、是否使用框架自带分页
5、通过explain 排查，SQL执行情况

开始优化：
1、准备测试数据，通过insert into select
Insert into 表名(id,列1,列2…) select MD5(uuid()),列1,列2… from 表名
其中使用MD5(uuid())确保不重复的ID，直接使用replace(uuid(),’-’,’’)在Navicat中可能会出现主键重复不可插入。
2、优化语句
原语句：

select *
 from t_openapilogs ts where 1=1
<if test="null == type or '' == type">
    <if test="null != ips and '' != ips">
        and (ts.TYPE in (1,3) or (ts.TYPE = 2 and find_in_set(ts.ip, #{ips})))
    </if>
    <if test="null == ips or '' == ips">
        and ts.TYPE in (1,3)
    </if>
</if>
<if test="null != statusCode and '' != statusCode">
    and ts.STATUS_CODE =#{statusCode}
</if>
<if test="null != start and '' != start">
    and date_format(ts.CREATE_DATE,'%Y-%m-%d') &gt;= #{start}
</if>
<if test="null != end and '' != end">
    <![CDATA[ and date_format(ts.CREATE_DATE,'%Y-%m-%d') <= #{end} ]]>
</if>
order by ts.CREATE_DATE desc limit ${startIndex},${rows}

分析：
1、查询了所有列 ----改成仅查询展示需要的列

select ID, OPERATION_CODE, CREATE_DATE, TYPE,  EXECUTE_TIME, DESCRIPTION, STATUS_CODE
 from t_openapilogs ts

2、主要用到TYPE ，CREATE_DATE为条件和排序 —将type ，CREATE_DATE加为索引和组合索引
3、CREATE_DATE使用了函数处理 ---- 索引列不要有函数
数据库中CREATE_DATE列存储的长度精确到秒，但是传入的时间精确到日，可以处理传入的数据，开始时间补充00:00:00，结束时间补充 23:59:59，这样就不需要通过函数处理。

select id from t_openapilogs where CREATE_DATE >='2020-08-08 00:00:00' and CREATE_DATE <='2020-09-08 23:59:59' order by CREATE_DATE desc

4、t_openapilogs表结构，里面有varchar长度较大和longtext列。—根据业务修改列的长度，longtext列抽离出表，需要是通过关联查询

5、数据量较大时不直接查询内容列，而根据条件先查询出匹配ID，再根据ID关联查出需要的内容列
– 优先通过条件查出列ID，然后再关联查具体列

select a.ID,OPERATION_CODE, CREATE_DATE, TYPE, IP, EXECUTE_TIME, DESCRIPTION, sm3, STATUS_CODE,RESULT, SUBORDINATE_ID,SUBORDINATE_NAME, seal_id
 from  t_openapilogs a
inner join 
(
SELECT id
 FROM `t_openapilogs` order by CREATE_DATE desc  LIMIT 2180000, 100
) b
on b.id=a.id order by CREATE_DATE desc;

6、重写框架自带分页查询
查询数据执行一遍SQL，查询总量又执行一遍SQL。可通过查询数据时添加
SQL_CALC_FOUND_ROWS，然后通过***select FOUND_ROWS()***;获取总数据，这样不需要再单独查询一遍总量。


```xml
<!-- 1、先根据条件检索出ID，以及总数，接着查询总数， -->
<select id="findPageIds" parameterType="map" resultType="string">
    SELECT SQL_CALC_FOUND_ROWS id from t_openapilogs ts
    where 1=1
    <include refid="Param_findPage"/>
    order by ts.CREATE_DATE desc limit ${startIndex},${rows}
</select>
<!-- 2、 findPageIds后接着查询总数。 -->
<select id="getPageCounts" resultType="integer">
    SELECT FOUND_ROWS();
</select>

<select id="findPageInSetIds" parameterType="string" resultMap="BaseResultMap">
 select  ID, OPERATION_CODE, CREATE_DATE, TYPE, IP, EXECUTE_TIME, DESCRIPTION,
 sm3, STATUS_CODE, RESULT, SUBORDINATE_ID, SUBORDINATE_NAME, seal_id
from t_openapilogs
where id in
    <foreach item="param" index="index" collection="param" open="(" separator="," close=")">
        #{param,jdbcType=VARCHAR}
    </foreach>
    order by CREATE_DATE desc
</select>


service调用时，先调用findPageIds ，然后接着调用getPageCounts，获取返回的总量，再根据查询到的id,匹配具体列值。

百度查询到，加上SQL_CALC_FOUND_ROWS之后，即使你用了limit n,m ，SELECT FOUND_ROWS()仍会返回满足条件记录的总数。这样，你执行完 select SQL_CALC_FOUND_ROWS 之后，再取一下记录总数就行了。