美文网首页
MySQL数据查询优化实践

MySQL数据查询优化实践

作者: JackSpeed | 来源:发表于2019-08-17 13:12 被阅读0次

背景：
之前自己写的支付宝小程序服务端有一个请求日志表，一开始日志数量并不多，几千几万的情况，再使用日志表排时发现各种一般的查询特别慢，当数量达到两百多万的时候有的sql查询时间几分钟、有的干脆直接卡住不动了。所以得有下面的一通操作解决一下这个问题。

说明一下，服务端项目db模块为mybatis

表名：t_sys_log
记录条数：1287225
表sql：

create table t_sys_log
    (
        id               bigint auto_increment primary key,
        actions          varchar(200)     null comment '操作',
        remarks          varchar(500)     null comment '备注',
        user_id          varchar(240)     null comment '用户id',
        ip_address       varchar(60)      null comment 'ip地址',
        action_address   varchar(500)     null comment '日志是发生在哪个模块的',
        interface_name   varchar(500)     null comment '接口名',
        request_param    longtext         null comment '请求参数',
        response_content longtext         null comment '返回结果',
        consume_time     int              null comment '请求耗时（毫秒）',
        create_time      datetime         null comment '时间',
        system_type      varchar(50)      null comment '系统类型',
        browser_type     varchar(50)      null comment '浏览器类型',
        phone_brand      varchar(100)     null comment '手机品牌',
        phone_model      varchar(100)     null comment '手机型号',
        phone_os_version varchar(50)      null comment '操作系统版本  android 7.2',
        phone_os_type    varchar(50)      null comment '手机系统类型 android  IOS',
        app_version_name varchar(50)      null comment '版本号',
        app_version_code varchar(50)      null comment '版本名称',
        request_status   char default '0' null comment '请求状态，0正常，1异常'
    );

1.未优化前mybatis查询：

1.1.未优化条件数量查询：
  SELECT count(*)
              FROM t_sys_log
              WHERE 1=1
              <if test="userId!=null ">
                  AND user_id=#{userId}
              </if>
              <if test="actions!=null and actions!=''">
                  AND actions=#{actions}
              </if>
              <if test="ipAddress!=null and ipAddress!=''">
                  AND ip_address=#{ipAddress}
              </if>
              <if test="actionAddress!=null and actionAddress!=''">
                  AND action_address=#{actionAddress}
              </if>
              <if test="interfaceName!=null and interfaceName!=''">
                  AND interface_name=#{interfaceName}
              </if>
              <if test="phoneBrand!=null and phoneBrand!=''">
                  AND phone_brand=#{phoneBrand}
              </if>
              <if test="phoneModel!=null and phoneModel!=''">
                  AND phone_model=#{phoneModel}
              </if>
              <if test="phoneOsVersion!=null and phoneOsVersion!=''">
                  AND phone_os_version=#{phoneOsVersion}
              </if>
              <if test="phoneOsType!=null and phoneOsType!=''">
                  AND phone_os_type=#{phoneOsType}
              </if>
              <if test="appVersionName!=null and appVersionName!=''">
                  AND app_version_name=#{appVersionName}
              </if>
              <if test="appVersionCode!=null and appVersionCode!=''">
                  AND app_version_code=#{appVersionCode}
              </if>
              <if test="requestStatus!=null and requestStatus!=''">
                  AND request_status=#{requestStatus}
              </if>
              <if test="startTime!=null and endTime!=null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN #{startTime} AND #{endTime}
              </if>
              <if test="startTime!=null and endTime==null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN #{startTime} AND NOW()
              </if>
              <if test="startTime==null and endTime!=null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN FROM_UNIXTIME(0, '%Y-%m-%d %H:%i:%S') AND #{endTime}
              </if>
1.2.未优化条件结果查询：
 SELECT id,
              actions,
              remarks,
              user_id,
              ip_address,
              action_address,
              interface_name,
              request_param,
              response_content,
              consume_time,
              system_type,
              browser_type,
              create_time,
              phone_brand,
              phone_model,
              phone_os_version,
              phone_os_type,
              app_version_name,
              app_version_code,
              request_status
              FROM t_sys_log
              WHERE 1=1
              <if test="userId!=null ">
                  AND user_id=#{userId}
              </if>
              <if test="actions!=null and actions!=''">
                  AND actions=#{actions}
              </if>
              <if test="ipAddress!=null and ipAddress!=''">
                  AND ip_address=#{ipAddress}
              </if>
              <if test="actionAddress!=null and actionAddress!=''">
                  AND action_address=#{actionAddress}
              </if>
              <if test="interfaceName!=null and interfaceName!=''">
                  AND interface_name=#{interfaceName}
              </if>
              <if test="phoneBrand!=null and phoneBrand!=''">
                  AND phone_brand=#{phoneBrand}
              </if>
              <if test="phoneModel!=null and phoneModel!=''">
                  AND phone_model=#{phoneModel}
              </if>
              <if test="phoneOsVersion!=null and phoneOsVersion!=''">
                  AND phone_os_version=#{phoneOsVersion}
              </if>
              <if test="phoneOsType!=null and phoneOsType!=''">
                  AND phone_os_type=#{phoneOsType}
              </if>
              <if test="appVersionName!=null and appVersionName!=''">
                  AND app_version_name=#{appVersionName}
              </if>
              <if test="appVersionCode!=null and appVersionCode!=''">
                  AND app_version_code=#{appVersionCode}
              </if>
              <if test="requestStatus!=null and requestStatus!=''">
                  AND request_status=#{requestStatus}
              </if>
              <if test="startTime!=null and endTime!=null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN #{startTime} AND #{endTime}
              </if>
              <if test="startTime!=null and endTime==null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN #{startTime} AND NOW()
              </if>
              <if test="startTime==null and endTime!=null">
                  AND date_format(create_time,'%Y-%m-%d') BETWEEN FROM_UNIXTIME(0, '%Y-%m-%d %H:%i:%S') AND #{endTime}
              </if>
              order by id desc

这种写法数据量几千到几万行尚可执行完毕；当数据达到几十万上百万时两个查询就会卡死掉。

2.查询优化

2.1.通过建立分表，解决查询数量（select count ）时耗时过长问题。
2.1.1.创建分表
create table t_sys_log_1
(
    log_id      bigint                             not null comment '主表id'
        primary key,
    create_time datetime default CURRENT_TIMESTAMP null comment '操作'
)
    engine = MyISAM;
2.1.2.主表数据写入分表
INSERT INTO t_sys_log_1 (log_id, create_time)
SELECT id, actions
FROM t_sys_log;
2.1.3.执行查询数量对比
sql> select count(*) from t_sys_log
[2019-08-02 10:29:34] 1 row retrieved starting from 1 in 9 s 341 ms (execution: 9 s 336 ms, fetching: 5 ms)
sql> select count(*) from t_sys_log_1
[2019-08-02 10:29:34] 1 row retrieved starting from 1 in 18 ms (execution: 6 ms, fetching: 12 ms)

很明显，数量级的差距。
分表之后在插入数据的时候逻辑跟原来不同了，需要插入主表之后根据主表t_sys_log的id再插入字表t_sys_log_1

2.2.通过sql查询语句优化，解决分页耗时过长问题；分页查询时，先分离出无查询条件分页查询和条件分页条件查询
2.2.1.无查询条件分页查询

通过前面的select count(*) from t_sys_log_1 得到总量，加上下面的即完成无查询条件分页查询

SELECT
        id,
        actions,
        remarks,
        user_id,
        ip_address,
        action_address,
        interface_name,
        request_param,
        response_content,
        consume_time,
        system_type,
        browser_type,
        create_time,
        phone_brand,
        phone_model,
        phone_os_version,
        phone_os_type,
        app_version_name,
        app_version_code,
        request_status
        FROM t_sys_log
        WHERE
        id > (
        select log_id from t_sys_log_1 order by log_id desc limit #{pageStart}, 1
        )
        limit #{pageLimitSize}

如此下来，在没有查询条件的时候分页查询速度得到了数量级的提高，查询时间低至几十、几百毫秒。

2.2.2.条件分页查询
2.2.2.1.确定性比较强的条件查询优化，结合当前业务和表结构，确定性比较强的字段有
ip_address, interface_name,system_type, browser_type,create_time, phone_brand, phone_model,
phone_os_version, phone_os_type,app_version_name,app_version_code,request_status

通过创建索引，完成条件查询优化的明确条件查询，首先给查询条件比较确定的字段加索引，查询条件模糊的暂时先不考虑，因为索引查询时不支持 like '%field%'，下一步再考虑。
创建索引

create index index_single_app_version_code
    on t_sys_log (app_version_code);

create index index_single_app_version_name
    on t_sys_log (app_version_name);

create index index_single_browser_type
    on t_sys_log (browser_type);

create index index_single_interface_name
    on t_sys_log (interface_name);

create index index_single_ip_address
    on t_sys_log (ip_address);

create index index_single_phone_brand
    on t_sys_log (phone_brand);

create index index_single_phone_model
    on t_sys_log (phone_model);

create index index_single_phone_os_type
    on t_sys_log (phone_os_type);

create index index_single_phone_os_version
    on t_sys_log (phone_os_version);

create index index_single_request_status
    on t_sys_log (request_status);

create index index_single_system_type
    on t_sys_log (system_type);

索引创建完成，开始分析逻辑
条件分页查询分为三步：
第一步：正常节奏查数量count;根据查询条件查询数量;

select count(*)
        from t_sys_log
        WHERE 1=1
        <if test="ipAddress!=null and ipAddress!=''">
            AND ip_address =#{ipAddress}
        </if>
        <if test="interfaceName!=null and interfaceName!=''">
            AND interface_name= #{interfaceName}
        </if>
        <if test="phoneBrand!=null and phoneBrand!=''">
            AND phone_brand=#{phoneBrand}
        </if>
        <if test="phoneModel!=null and phoneModel!=''">
            AND phone_model =#{phoneModel}
        </if>
        <if test="phoneOsVersion!=null and phoneOsVersion!=''">
            AND phone_os_version =#{phoneOsVersion}=
        </if>
        <if test="phoneOsType!=null and phoneOsType!=''">
            AND phone_os_type=#{phoneOsType}
        </if>
        <if test="appVersionName!=null and appVersionName!=''">
            AND app_version_name =#{appVersionName}
        </if>
        <if test="appVersionCode!=null and appVersionCode!=''">
            AND app_version_code=#{appVersionCode}
        </if>
        <if test="requestStatus!=null and requestStatus!=''">
            AND request_status =#{requestStatus}
        </if>

第二步：根据查询条件查询id

select id
        from t_sys_log
        WHERE 1=1
        <if test="ipAddress!=null and ipAddress!=''">
            AND ip_address =#{ipAddress}
        </if>
        <if test="interfaceName!=null and interfaceName!=''">
            AND interface_name= #{interfaceName}
        </if>
        <if test="phoneBrand!=null and phoneBrand!=''">
            AND phone_brand=#{phoneBrand}
        </if>
        <if test="phoneModel!=null and phoneModel!=''">
            AND phone_model =#{phoneModel}
        </if>
        <if test="phoneOsVersion!=null and phoneOsVersion!=''">
            AND phone_os_version =#{phoneOsVersion}=
        </if>
        <if test="phoneOsType!=null and phoneOsType!=''">
            AND phone_os_type=#{phoneOsType}
        </if>
        <if test="appVersionName!=null and appVersionName!=''">
            AND app_version_name =#{appVersionName}
        </if>
        <if test="appVersionCode!=null and appVersionCode!=''">
            AND app_version_code=#{appVersionCode}
        </if>
        <if test="requestStatus!=null and requestStatus!=''">
            AND request_status =#{requestStatus}
        </if>
        <if test="phoneModel!=null and phoneModel!=''">
            AND phone_model =#{phoneModel}
        </if>
        <if test="phoneOsVersion!=null and phoneOsVersion!=''">
            AND phone_os_version =#{phoneOsVersion}=
        </if>
        <if test="phoneOsType!=null and phoneOsType!=''">
            AND phone_os_type=#{phoneOsType}
        </if>
        <if test="appVersionName!=null and appVersionName!=''">
            AND app_version_name =#{appVersionName}
        </if>
        <if test="appVersionCode!=null and appVersionCode!=''">
            AND app_version_code=#{appVersionCode}
        </if>
        <if test="requestStatus!=null and requestStatus!=''">
            AND request_status =#{requestStatus}
        </if>
        order by id desc
        limit #{pageStart}, #{pageLimitSize}

第三步：根据id查询详情

SELECT
        id,
        actions,
        remarks,
        user_id,
        ip_address,
        action_address,
        interface_name,
        request_param,
        response_content,
        consume_time,
        system_type,
        browser_type,
        create_time,
        phone_brand,
        phone_model,
        phone_os_version,
        phone_os_type,
        app_version_name,
        app_version_code,
        request_status
        FROM t_sys_log
        WHERE
        id in
        <foreach collection="idList" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
        order by id desc

运行一下程序，发现条件查询耗时200多毫秒。

2.2.2.2.当查询条件不是很确定时-------未完待续♂静候佳音

相关文章

  • 9月17-MySQL性能优化

    MySQL性能优化策略 1、MySQL内核架构 2、索引原理与查询优化 加速MySQL高效查询数据的数据结构 二分...

  • MySQL千万数据优化分页查询

    操作环境: MySQL: 5.7.26 Navicat for MySQL 目的: MySQL千万级数据的优化查询...

  • MySQL数据查询优化实践

    背景：之前自己写的支付宝小程序服务端有一个请求日志表，一开始日志数量并不多，几千几万的情况，再使用日志表排时发现各...

  • 64MySQL-分页查询&表连接&count统计&索引优化总结

    1 Mysql 分页查询sql 执行原理？ 2，千万级数据mysql 分页查询如何优化 3，Mysql表连接底层实...

  • 高性能MySQL之架构与历史

    MySQL逻辑架构 优化与执行 MySQL会解析查询，创建内部数据结构（解析树），对齐进行优化（重写查询、决定表的...

  • 高性能MySQL之架构与历史

    MySQL逻辑架构 优化与执行 MySQL会解析查询，创建内部数据结构（解析树），对齐进行优化（重写查询、决定表的...

  • MySQL查询缓存

    简介： mysql通过查询缓存优化来优化查询效率，当执行相同的一条sql时，mysql会将数据缓存起来以供下...

  • 千万级MySQL数据库建立索引，提高性能的秘诀

    实践中如何优化MySQL 实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化...

  • MySQL优化

    概述 MySQL优化分为三部分优化： MySQL服务器和配置优化 数据库设计和结构优化 查询优化（重点） MySQ...

  • MySQL性能调优

    MYSQL查询语句优化 mysql的性能优化包罗甚广： 索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬...

网友评论

      本文标题：MySQL数据查询优化实践

      本文链接：https://www.haomeiwen.com/subject/vpebjctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|MySQL数据查询优化实践|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！