文档章节

MySql 性能优化备忘

夏至如沫
 夏至如沫
发布于 2017/07/27 14:54
字数 1193
阅读 14
收藏 0
点赞 0
评论 0

有一个数据采集的项目,最初因为功能简单并且终端和服务部将署在一台终端机上,便选用了体积小、安装 简单的MySQL数据库。前期一切顺利,但是现场部署时发现产生的数据量,远比预想的多得多,一个细粒度的子表每天产生将近1万条数据,把所有的业务数据算进去,一个月下来就是一百万的数据量。回头测试一下细粒度的查询视图,果然懵逼了。每次都是超时,连COUNT操作都不能快速返回了。因为之前大多用的都是Oracle 和 SQLServer,也简单做过百万级的优化,就是查询索引和过滤条件的调整,但是这里这些调整在MySQL里的分页查询不起作用了,可能跟 Limit 关键字的机制相关吧。

百万级查询

  • 使用场景,分页视图 分页视图中使用了三个主要的业务表,A 为基础数据表,关联一些ID和名称,B为主业务数据表,是每一次任务的记录,C为详细的业务数据表,是一次任务中包含的全部操作内容,大概每次任务有200行上下。查询条件有在 A 表的 Col1,B表的Col2,C表的Col3、Col4、Col5、Col6,其中Col5、Col6 经常为并且条件。

最初的索引比较简单,只有一个GUID类型(实际存储的是varchar(36))的主键聚集索引和一个时间字段的非聚集索引。当现场采集一周的数据拷贝回来时,大概30万,每次的分页查询已经超过半分钟了。最初的做法是调整索引,按照查询条件中的命中关系和主次关系建立和多个索引,可是发现几乎没有任何效果。然后只能问百度了,最后在很多优化文章中看到,Limit m,n 步长越大,耗时越长并且Select 的字段和索引关系很大,索引中加入该字段会对查询提升很多。最终的方案采用 先使用Limit m,n 取出命中行主键,然后使用关联查询(或者IN的方式)获取全部所需要的字段。最终的结果可能类似于这个样子:

SELECT 
    S.GUID,
    S.HisGUID,
    S.RefGUID,
    A.AddressId,
    A.AddressName
FROM
    T_SNAPDIAGINFO S,
    T_ADDRESS A,
    ( SELECT S.GUID FROM T_SNAPDIAGINFO S 
    WHERE S.SnapTime BETWEEN str_to_date('2017-07-23 00:00:00', '%Y-%m-%d %H:%i:%s') AND str_to_date('2017-07-26 17:25:00', '%Y-%m-%d %H:%i:%s') 
    ORDER BY S.SnapTime DESC  LIMIT 0,10) PT 
    WHERE  A.AddressId = S.AddressId AND S.GUID = PT.GUID

这里还有两个细节的

  • 索引优化

    在基于时间的排序时,如果顺序是已知,比如软件的信息列表优先显示最新的数据记录,这时候在建立索引时,可以将这个时间字段分配为逆序(DESC)。这个操作在默认查询时也有不小的性能提升。

  • 查询优化

    在_LIMIT_字句中应当是单表查询,并且最好只返回主键字段

这里还有一个问题,如果查询字段比较多的话,在建立索引时是它们全部建立在一个索引上,还是按照依赖关系分组建立呢?(不考虑插入效率的印象)

批量数据插入

从现场采集回来的日志中,发现每次任务数据保存的时间有2-5秒,虽说好像对于200行的数据来说,也不是什么大问题,但是总觉得还有提升的空间。o(╯□╰)o真实原因是现场日志中经常还有IO占用的异常,搞得我数据库保存都不自信了。 批量插入的方法比较简单,采用MySQL特有批量插入的语法格式。

INSERT INTO TABLE 
(A, B, C, D) 
VALUES
( a,b,c,d),
(a,bc,d),
...

我这里单表的插入每次最多有60行左右的样子,所以就直接全部拼接在一个SQL中了。如果同时插入上千条甚至上万条的话,需要分批提交,最终的代码可能如下:

        /// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>sql2000数据库
        /// <param name="SQLStringList">多条SQL语句</param>
        public static void ExecuteSqlTran(List<string> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(MySqlHelper.ConnStr))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    //tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

查考文章

Mysql批量插入

博客园 -- MySql百万级数据优化查询

© 著作权归作者所有

共有 人打赏支持
夏至如沫
粉丝 11
博文 37
码字总数 23521
作品 0
郑州
后端工程师
后台开发常问面试题集锦(问题搬运工,附链接)

Java基础问题 String的’+’的性能及原理 java之yield(),sleep(),wait()区别详解-备忘笔记 深入理解Java Stream流水线 抽象 & abstract关键字 Java final 修饰符知识点总结(必看篇) Java中的...

大黄有故事 ⋅ 2017/11/18 ⋅ 0

查看linux默认能最多开启多少个文件数量

ulimit –a ulimit –n #vi /erc/security/limits.conf (修改文件开启的限制) # #<domain> <type> <item> <value> # #* soft core 0 #* hard rss 10000 #@student hard nproc 20 #@faculty ......

90xa ⋅ 2014/05/16 ⋅ 0

《高性能MySQL》第三章MySQL服务器性能剖析学习笔记

MySQL性能优化介绍 什么是性能优化呢?其实我们往往从广义的定义是觉得一个MySQL系统的非功能性的优化都会看作是性能优化,比如我们会将数据库服务器的稳定性、每秒执行的SQL查询数目、系统的...

杨武兵 ⋅ 2015/09/24 ⋅ 2

MySQL数据库的性能的影响分析及其优化

MySQL数据库的性能的影响 一. 服务器的硬件的限制 二. 服务器所使用的操作系统 三. 服务器的所配置的参数设置不同 四. 数据库存储引擎的选择 五. 数据库的参数配置的不同 六. (重点)数据库的...

Panda_Jerry ⋅ 2017/11/04 ⋅ 0

Ajax & PHP 边学边练 之三 数据库

在上一篇备忘日历实例中,实现了当鼠标放在某个日期上时,如果当天有备忘信息则会显示出来,但是这些信息是为了测试方便事先写在数组中的数据,不能体现其实时性。本篇将继续通过该实例讲解与...

junwong ⋅ 2012/03/09 ⋅ 0

存储&缓存_Index

MySql分库分表策略 关于如何形成一个好的数据库设计 写给开发者看的关系型数据库设计 数据库性能优化之SQL语句优化1 数据库性能优化之SQL语句优化2 数据库性能优化之SQL语句优化3 数据库性能...

陶邦仁 ⋅ 2014/04/03 ⋅ 0

读《面向程序员的数据库访问性能优化法则》

刚刚看了一下2014年中国数据库技术大会(DTCC)PPT,网易杭州研究院的一位同学介绍了一下数据库的优化与调优。 网易的Mysql优化做的很好,而且还写了本书和大家分享这些经验《深入浅出MySQL...

ifeixiang ⋅ 2015/01/04 ⋅ 0

MySQL 5.6 有什么新特性

昨天 MySQL 发布了 5.6.2 测试版本,详情请看这里。 今天我们来详细介绍一下 MySQL 5.6 有或者将会有什么样的改进。 MySQL 5.6 主要在查询性能的优化、InnoDB改进以支持高吞吐量的事务、NoS...

红薯 ⋅ 2011/04/13 ⋅ 1

kloxo安装方法和安全配置

第一种 安装Kloxo和中文语言包,只要1行命令: wget http://www.CTOHome.com/linux-vps-pack/kloxowithchineselanguage.sh;sh ./kloxowithchineselanguage.sh; 将上面1行复制,粘贴到您的lin...

mickelfeng ⋅ 2012/11/14 ⋅ 0

性能调优概述

大纲: 一、概述 二、什么是性能调优?(what) 三、为什么需要性能调优?(why) 四、什么时候需要性能调优?(when) 五、什么地方需要性能调优?(where) 六、什么人来进行性能调优?(who) 七、怎...

陈明乾 ⋅ 2014/07/14 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

vim编辑模式、命令模式

编辑模式 vim要从一般模式进入编辑模式只要按字母 i 、I、a、A、o、O键就可以了 要从编辑模式回到一般模式按键盘上的Esc键即可。 按键 作用 i 在当前字符前插入 I 在光标所在行的行首插入 o ...

黄昏残影 ⋅ 22分钟前 ⋅ 0

OSChina 周五乱弹 —— 如果有一天不当程序员了

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @guanglun :分享off的单曲《我唱情歌给你听》 《我唱情歌给你听》- off 手机党少年们想听歌,请使劲儿戳(这里) @小小编辑 :#如果不做程序...

小小编辑 ⋅ 29分钟前 ⋅ 4

从 Confluence 5.3 及其早期版本中恢复空间

如果你需要从 Confluence 5.3 及其早期版本中的导出文件恢复到晚于 Confluence 5.3 的 Confluence 中的话。你可以使用临时的 Confluence 空间安装,然后将这个 Confluence 安装实例升级到你现...

honeymose ⋅ 今天 ⋅ 0

Java8新增的DateTimeFormatter与SimpleDateFormat的区别

两者最大的区别是,Java8的DateTimeFormatter也是线程安全的,而SimpleDateFormat并不是线程安全。 在并发环境下使用SimpleDateFormat 为了能够在多线程环境下使用SimpleDateFormat,有这三种...

人觉非常君 ⋅ 今天 ⋅ 0

多线程如何控制执行顺序

线程的生命周期说明: 当线程被创建并启动以后,它既不是一启动就进入了执行状态,也不是一直处于执行状态,在线程的生命周期中,它要经过新建(New)、就绪(Runnable)、运行(Running)、...

MarinJ_Shao ⋅ 今天 ⋅ 0

用ZBLOG2.3博客写读书笔记网站能创造今日头条的辉煌吗?

最近两年,著名的自媒体网站今日头条可以说是火得一塌糊涂,虽然从目前来看也遇到了一点瓶颈,毕竟发展到了一定的规模,继续增长就更加难了,但如今的今日头条规模和流量已经非常大了。 我们...

原创小博客 ⋅ 今天 ⋅ 0

MyBatis四大核心概念

本文讲解 MyBatis 四大核心概念(SqlSessionFactoryBuilder、SqlSessionFactory、SqlSession、Mapper)。 MyBatis 作为互联网数据库映射工具界的“上古神器”,训有四大“神兽”,谓之:Sql...

waylau ⋅ 今天 ⋅ 0

以太坊java开发包web3j简介

web3j(org.web3j)是Java版本的以太坊JSON RPC接口协议封装实现,如果需要将你的Java应用或安卓应用接入以太坊,或者希望用java开发一个钱包应用,那么用web3j就对了。 web3j的功能相当完整...

汇智网教程 ⋅ 今天 ⋅ 0

2个线程交替打印100以内的数字

重点提示: 线程的本质上只是一个壳子,真正的逻辑其实在“竞态条件”中。 举个例子,比如本题中的打印,那么在竞态条件中,我只需要一个方法即可; 假如我的需求是2个线程,一个+1,一个-1,...

Germmy ⋅ 今天 ⋅ 0

Django第一期

安装Django 去https://www.djangoproject.com/download/ 下载最新版的Django,然后解压放到Anaconda\Lib\site-packages目录下,然后cmd进入此目录,输入安装命令: python setup.py install ...

大不了敲一辈子代码 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部