【MySql】记一次千万级数据处理的优化过程

最新推荐文章于 2024-05-16 23:12:49 发布
最新推荐文章于 2024-05-16 23:12:49 发布
阅读量589 收藏 2
点赞数 1
分类专栏： 调调bug 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dai_jiyong/article/details/106873209
版权

需求

从mongodb数据库的A表（或者叫集合）读取数据

经过处理后导入MySQL的B表中

其中

A表有近2000万条的数据

需要对每条数据进行分析处理，分析处理过程就不细说了

每条A表数据分析处理后可提取生成数条B表数据

B表数据的字段中有school、speciality和post三个字段，和一个字段number

导入B表中的数据需要通过这三个字段联合去重，并统计重复的条数number

最终将生成的B表数据以及频率字段number存储到MySQL数据库中

需求不复杂

image.png

解决过程

思路

逐步读取那2000万条数据，并进行处理

定义一个Map，key值为school、speciality和post三个字段的拼接值，value为出现的次数

循环遍历2000万条数据

通过Map的key值进行判断

如果不存在，则将数据通过insert语句插入到MySQL数据库，同时put到Map中，key值为school、speciality和post三个字段的拼接值，value为1

如果存在了，则从Map中取出对应的value，value++，并将value值通过update语句更新到MySQL数据库中，同时更新Map中的value

貌似没问题，运行，程序快速的运行起来了

image.png

遇到问题1

通过控制台打印的日志，我发现一个问题

所有的insert语句都是都是哗哗哗的打印

update却有明显的停顿感

很明显update语句影响了执行效率

特别是当MySQL表中数据量达到200万+之后

数据插入速度明显减慢了

image.png

这么搞下去可能要耽误事儿了

优化1

update B表 set number = ？ where school = ? and speciality = ? and post = ?

明显这个语句是执行速度的短板，所以应该从分析这条sql语句开始

因为用到了where关键字，所以条件查询应该是时间瓶颈

最先应该想到的就是建立索引

所以建立了school、speciality、post三个字段的联合索引，索引类型是normal

晚上执行的建立索引的语句，然后就去睡觉了，第二天早上起来建立好了

200多万数据，忘了看执行时间了

继续运行，果然速度快了很多

image.png

遇到问题2

当数据量逐渐变大，达到近千万级时

用来存储频率否Map也的大小也随之达到了千万级

从内存使用和效率方面，性能都有所下降

所以我就考虑不在通过Map记录数据的出现频率

想通过update B表 set number = number + 1 where  school = ? and speciality = ? and post = ?来解决

但是这个时候我发现，由于前期代码问题，在程序中断又重启过好多次，  school、speciality、post这三个字段的内容并不是唯一存在的

如果直接使用上边的语句，会导致我统计的number数字不准确

image.png

所以第一步需要解决的问题就是去重

可以通过以下语句对数据进行多字段判断的去重


DELETE 

FROM

    speciality_post 

WHERE

    ( school, speciality, post ) IN (

    SELECT

        school,

        speciality,

        post 

    FROM

        ( SELECT school, speciality, post FROM speciality_post GROUP BY school, speciality, post HAVING COUNT( * ) > 1 ) s1 

    ) 

    AND id NOT IN ( SELECT id FROM ( SELECT id FROM speciality_post GROUP BY school, speciality, post HAVING COUNT( * ) > 1 ) s2 );

然后再根据那三个字段建立唯一索引

但是建立索引也是一个很漫长的过程

并且去重之后，如果不重新遍历2000万的原始数据

number也是统计的不准确的

image.png

优化2

最终决定

雄关漫道真如铁

而今迈步从头越

新建B表，并将school、speciality、post这三个字段建立联合唯一索引

同时改进sql语句，使用insert … on duplicate key update语句，简化代码

然后修改代码，运行

有时候从头再来也是一种策略

可以很大程度的挽回损失或停止损失

总结

1、为什么insert语句要比update语句要快？

update语句需要先定位数据行位置，需要根据主键索引或条件索引逐行扫描，然后再更新特定字段

而insert语句是没有这些开销的

所以insert语句肯定要update语句快的多

2、为什么将update语句中的where条件字段建立索引后，更新速度就变快了？

因为如果update语句中的where条件字段没有建立索引，在执行update语句的时候是要进行全表扫描的，扫描的过程中对每一行数据进行加锁判断释放锁，这个过程耗时会随着数据量的增加直线上升

而如果加了索引，就能快速准确定位到目标数据行，而且MySQL底层使用的是B+树建立的索引，所以稳定性也特别好

3、解释一下insert … on duplicate key update语句

如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句

判断数据是否存在是通过判断是否导致了一个UNIQUE索引或者primary key(主键)出现重复，所以update语句是不能跟where条件判断的

##测试

接下来才是我想说的内容

image.png

针对这个过程中的出现的几种情况

我对update语句的效率做了一个测试和对比

目标表B表中的数据量有2,013,035条

执行更新语句5000次(测试的时候才发现，如果是5000条我得等一天)

分别测试无索引、normal索引、unique索引下update和insert…on duplicate key update语句的耗时

话不多说上代码

主函数方法


    public static void main(String[] args) {

        DruidUtils.init("localhost", "3306", "test", "root", "root");

        String specialityPostNoIndex = "speciality_post_no_index";

        String specialityPostUniqueIndex = "speciality_post_unique_index";

        String specialityPostNormalIndex = "speciality_post_normal_index";

        List<SpecialityPost> list = select();

        long start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            update(specialityPost, specialityPostNoIndex);

        }

        long end = System.currentTimeMillis();

        System.out.println("update语句在无索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("update语句在无索引情况下平均耗时：\t" + ((end - start) / 50)  + "毫秒\n\n\n\n");

        start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            insertOrUpdate(specialityPost, specialityPostNoIndex);

        }

        end = System.currentTimeMillis();

        System.out.println("insert·· on duplicate key update语句在无索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("insert·· on duplicate key update语句在无索引情况下平均耗时：\t" + ((end - start) / 50)  + "毫秒\n\n\n\n");

        start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            update(specialityPost, specialityPostNormalIndex);

        }

        end = System.currentTimeMillis();

        System.out.println("update语句在normal索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("update语句在normal索引情况下平均耗时：\t" + ((end - start) / 50)  + "毫秒\n\n\n\n");

        start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            insertOrUpdate(specialityPost, specialityPostNormalIndex);

        }

        end = System.currentTimeMillis();

        System.out.println("insert·· on duplicate key update语句在normal索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("insert·· on duplicate key update语句在normal索引情况下平均耗时：\t" + ((end - start) / 50) + "毫秒\n\n\n\n");

        start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            update(specialityPost, specialityPostUniqueIndex);

        }

        end = System.currentTimeMillis();

        System.out.println("update语句在unique索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("update语句在unique索引情况下平均耗时：\t" + ((end - start) / 50) + "毫秒\n\n\n\n");

        start = System.currentTimeMillis();

        for (SpecialityPost specialityPost : list) {

            insertOrUpdate(specialityPost, specialityPostUniqueIndex);

        }

        end = System.currentTimeMillis();

        System.out.println("insert·· on duplicate key update语句在unique索引情况下耗时：\t" + (end - start) + "毫秒\n");

        System.out.println("insert·· on duplicate key update语句在unique索引情况下平均耗时：\t" + ((end - start) / 50) + "毫秒\n\n\n\n");

    }

其他用到的方法


private static int update(SpecialityPost specialityPost, String table) {

        String sql = "update " + table + " set number = number+1 where school = ? and speciality = ? and post = ?";

        Connection connection = DruidUtils.getConnection();

        PreparedStatement preparedStatement = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, specialityPost.getSchool());

            preparedStatement.setString(2, specialityPost.getSpeciality());

            preparedStatement.setString(3, specialityPost.getPost());

            System.out.println(preparedStatement.toString());

            return preparedStatement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        } finally {

            DruidUtils.closeAll(connection, preparedStatement, null);

        }

        return 0;

    }



private static int insertOrUpdate(SpecialityPost specialityPost, String table) {

        String sql = "INSERT INTO " + table + " (area,school,degree,original_speciality,speciality,original_post,post,number) " +

                "VALUE" +

                "(?,?,?,?,?,?,?,?) " +

                "ON DUPLICATE KEY UPDATE " +

                "number = number+1;";

        Connection connection = DruidUtils.getConnection();

        PreparedStatement preparedStatement = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, specialityPost.getArea());

            preparedStatement.setString(2, specialityPost.getSchool());

            preparedStatement.setString(3, specialityPost.getDegree());

            preparedStatement.setString(4, specialityPost.getOriginalSpeciality());

            preparedStatement.setString(5, specialityPost.getSpeciality());

            preparedStatement.setString(6, specialityPost.getOriginalPost());

            preparedStatement.setString(7, specialityPost.getPost());

            preparedStatement.setInt(8, specialityPost.getNumber());

            System.out.println(preparedStatement.toString());

            return preparedStatement.executeUpdate();

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        } finally {

            DruidUtils.closeAll(connection, preparedStatement, null);

        }

        return 0;

    }



private static List<SpecialityPost> select() {

        String sql = "select * from  speciality_post_unique_index order by number limit 50";

        Connection connection = DruidUtils.getConnection();

        List<SpecialityPost> list = new ArrayList<>();

        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;

        try {

            preparedStatement = connection.prepareStatement(sql);

            System.out.println(preparedStatement.toString());

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                SpecialityPost specialityPost = new SpecialityPost();

                specialityPost.setId(resultSet.getInt("id"));

                specialityPost.setNumber(resultSet.getInt("number"));

                specialityPost.setSchool(resultSet.getString("school"));

                specialityPost.setSpeciality(resultSet.getString("speciality"));

                specialityPost.setPost(resultSet.getString("post"));

                specialityPost.setArea(resultSet.getString("area"));

                specialityPost.setOriginalSpeciality(resultSet.getString("original_speciality"));

                specialityPost.setOriginalPost(resultSet.getString("original_post"));

                list.add(specialityPost);

            }

        } catch (SQLException throwables) {

            throwables.printStackTrace();

        } finally {

            DruidUtils.closeAll(connection, preparedStatement, resultSet);

        }

        return list;

    }

###无索引情况下的update语句

image.png

###无索引情况下的insert … on duplicate key update语句

image.png

注意：这个之所以执行这么快，不是因为sql语句的优化的好，前边说到了，这个语句判断是否更新是判断是否与唯一索引冲突，在这里是没冲突的，所以其实执行的是插入操作

###normal索引情况下的update语句

image.png

###normal索引情况下的 insert … on duplicate key update语句

image.png

注意：这个之所以执行这么快，不是因为sql语句的优化的好，前边说到了，这个语句判断是否更新是判断是否与唯一索引冲突，在这里是没冲突的，所以其实执行的是插入操作

###unique索引情况下的update语句

image.png

###unique索引情况下的 insert … on duplicate key update语句

image.png

可以看到使用索引和insert … on duplicate key update语句的效率还是极高的

在做类似数据操作的时候，可以参考一下

你有其他更好的办法吗？

文/戴先生@2020年6月20日

—end—

更多精彩推荐

公众号：你好戴先生

你好戴先生
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
你知道MySQL是如何处理千万级数据的吗？
weixin_49163826的博客
08-10 1万+
mysql 分表思路 一张一亿的订单表，可以分成五张表，这样每张表就只有两千万数据，分担了原来一张表的压力，分表需要根据某个条件进行分，这里可以根据地区来分表，需要一个中间件来控制到底是去哪张表去找到自己想要的数据。 **中间件：**根据主表的自增 id 作为中间件（什么样的字段适合做中间件？要具备唯一性） **怎么分发？**主表插入之后返回一个 id，根据这个 id 和表的数量进行取模，余数是几就往哪张表中插入数据。 **注意：**子表中的 id 要与主表的 id 保持一致 以后只有插入操作会用到主表，
MySQL千万数据解决方案
04-11
方案概述 方案一：优化现有mysql数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。 方案二：升级数据库类型，换一种100%兼容mysql的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱 方案三：一步到位，大数据解决方案，更换newsql/nosql数据库。优点：扩展性强，成本低，没有数据容量瓶颈，缺点：需要修改源程序代码 以上三种方案，按顺序使用即可，数据量在亿级别一下的没必要换nosql，开发成本太高。三种方案我都试了一遍，而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 :)
mysql8.0千万数据量性能优化指南（二）：创建高性能索引
最新发布
oheyec_的博客
05-16 937
在处理大规模数据时，索引的选择和优化对MySQL数据库性能至关重要。本文基于InnoDB引擎，介绍了MySQL 8.0中常用的索引类型（如主键索引和非主键索引），并提供了一系列优化索引的方法，包括覆盖索引、最左前缀原则和索引下推等。
MySQL 千万级数据处理
在森林里麋了鹿
11-01 4320
MySQL 处理千万级数据
MySQL处理千万级数据查询、分页(有些地方值得参考,不过原作者好像走歪了)
houzhyan-博客
07-19 1159
原文:http://www.2cto.com/database/201305/207782.html 方法尚未验证,过两天实验后上传结果. MySQL处理千万级数据查询、分页   MySQL数据库优化处理实现千万级快速分页分析，来看下吧。   数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text,
mysql处理千万级别的数据优化
qq_35726305的博客
07-26 1014
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t ...
mysql千万级数据大表该如何优化?
12-15
MySQL数据库在处理千万级数据大表时，优化是至关重要的，因为这直接影响到系统的性能和响应速度。以下是一些关键的优化策略： 1. **数据容量规划**：预测未来1-3年内数据增长情况，计算每条数据的平均大小，以此来...
30个mysql千万级大数据SQL查询优化技巧详解
09-09
MySQL在处理大数据量时，查询优化至关重要，尤其是对于拥有千万级数据的表。以下是对文章内容的详细解读和扩展： 1. **建立索引**：为`WHERE`和`ORDER BY`涉及的列创建索引可以显著提升查询速度，避免全表扫描。 2...
mysql千万级数据分页查询性能优化
09-09
综上所述，针对MySQL千万级数据分页查询的性能优化，主要是通过合理使用索引、调整查询语句、数据归档、读写分离、缓存以及预计算等策略，以减少不必要的数据扫描和提高查询效率。在实际应用中，需要根据具体的业务...
Mysql数据库千万级数据处理优化
PersonalM的博客
08-07 224
1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id ...
mysql优化（千万级数据）
李小文的博客
05-07 237
mysql数据库数据优化，优化查询
Mysql千万级别数据优化方案
04-11
Mysql千万级别数据优化方案
你知道MySQL是如何处理千万级数据的吗？这个太强了！
爬遍所有网站
08-19 661
mysql 分表思路 一张一亿的订单表，可以分成五张表，这样每张表就只有两千万数据，分担了原来一张表的压力，分表需要根据某个条件进行分，这里可以根据地区来分表，需要一个中间件来控制到底是去哪张表去找到自己想要的数据。 **中间件：**根据主表的自增 id 作为中间件（什么样的字段适合做中间件？要具备唯一性） **怎么分发？**主表插入之后返回一个 id，根据这个 id 和表的数量进行取模，余数是几就往哪张表中插入数据。 **注意：**子表中的 id 要与主表的 id 保持一致 以后只有插入操
【MySQL】MySQL对于千万级的数据库或者大表怎么处理?
miles067的博客
09-09 448
【MySQL】MySQL对于千万级的数据库或者大表怎么处理?
mysql中千万级数据迁移
cymx66688的博客
04-15 414
步骤： 一、数据下载 mysqldump -u [用户名] -h[主机] -P [端口号] -p['密码'] 数据库名称 表名> 保存路径 如：mysqldump -u dev -h192.168.101.100 -P 3306 -p’123456’ datacenter_dev edu_employment > sql/edu_employment_record.sql 二、数据迁移 1. 连接MySQL mysql -h[主机] -u[用户名] -P[端口号] -p['密码'] 2. 连
MySQL千万级数据优化方案
.
05-23 4188
数据库千万级数据、查询优化。
mysql千万级大数据SQL查询优化
AinUser的博客
05-06 348
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t ...
MySQL处理千万级数据分页查询的优化方案
360linker
12-07 797
数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。 最后collect 为 10万条记录，数据库表占用硬盘1.6G。OK ,看下面这条sql语句： select id,tit...
MySQL千万级数据深度分页优化
04-15
好的，我会尽力回答你的问题。分页查找是很常见的操作，但是当数据量很大的时候，会存在性能问题。为了优化性能，可以考虑通过辅助索引、缓存等方式来减少数据库的查询次数，提高查询效率。此外，还可以根据业务需求来进行数据分片、异步处理等操作，来提高系统并发能力和性能表现。以上是一些常用的优化策略，具体实现还需要根据具体情况而定。希望这些信息能对你有所帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值