mysql 查询 写入 性能极致优化 3000万数据3分钟 深层次优化大数据量
2020-08-28 15:49 skateweb 阅读(1061) 评论(0) 编辑 收藏 举报前言：为什么说是极致优化，不要小瞧上面的3000万说的比较少，为啥，因为我们知道数据量不能代表一切，还要看字段的长度，和字段数量，这3000万数据 分化在60张表里面，核心表6 7张每张数据量大约在300w-800w，字段长度在60-200不等，并且大部分表内部包涵超长文本。这样你还觉得慢吗。
背景：数据库迁移，sqlserver 将数据迁移至mysql。平行迁移。公司切换数据库。
优化方向一：（代码级别优化）
优化点1：批量写入（大家应该都知道，但是这个批量写入还略有不同，区别在下面的优化点慢慢看）
优化点2：批量写入数据库使用的方式，选择了sqlsession（预处理语句）
优化点3：多线程执行操作（将数据库性能发挥到极限）
优化点4：滚动查询（查询效率极高，是普通分页查询的10倍以上）
优化方向二：（数据库引擎参数配置优化）
优化点1：global innodb_flush_log_at_trx_commit（事务提交时 日志设置 效果明显）
优化点2：max_connections 最大连接数 ，这个要综合考虑各种情况包涵服务器节点数 线程数据，服务器核心数 等资源分配情况
优化点3：global bulk_insert_buffer_size （插入缓冲区）
优化点4：innodb_buffer_pool_size（InnoDB缓冲池）
优化点5：innodb_autoextend_increment(tablespace 空间)
优化方式三：（启动分配优化 jvm）
优化点1： -Xmx2g -Xms2g -Xss1m 提前分配一下资源 启动内存等
优化方式四：（服务器方向优化，分布式多节点）
优化点1：多节点部署程序，将性能跑到极限
优化点5:(mysql流写入)
优化点1：目前 最快的写入方式 比较极限了
主干（细讲各个优化点）
优化方向一：（代码级别优化）
优化点1：批量写入
把sql当做一个字符串，提前拼接好 一次批量的条数要根据数量，数据大小，字段长度决定，我这次优化一次50条，因为字段太多，长度太大，大约等于普通表10个字段的 200-500条左右，这个需要自己测试，说一下测试标准
如何看多少条合适，
测试 50 条 100条 200条 500条 1000条 打印出 每次执行的时间，就能看出你的表多少条合适了，当然复杂的场景可以写程序自动判断批量的条数，每一张表 动态设定批量的条数。需要自己把我一个临界点。多次测试你才能得到
你想要的的答案。
优化点2：批量写入数据库使用的方式，选择了sqlsession（预处理语句）
为啥使用sqlsession 而不是使用mybatis 的普通批量。因为需要设置预编译处理。当然你如果在配置文件配置好 也是可以用mybatis for 去批量写入的。
核心点在于 设置openSession(ExecutorType.BATCH); 批量预处理。这样大概是mybatis默认的单条处理的5-7倍以上的效率
mybatis 默认 SIMPLE
还有 REUSE, BATCH
SIMPLE：它为每个语句的执行创建一个新的预处理语句。
REUSE：这个执行器类型会复用预处理语句
BATCH：这个执行器会批量执行所有更新语句
我选择用 BATCH； 并且是没三万条数据 开启关闭一次sqlsession， 当然开启关闭sqlsession 影响不大。对效率。 建议一次开启关闭 也不要处理太多条数据了。
优化点3：多线程执行操作（将数据库性能发挥到极限）
使用线程池 去执行批量写入，建议根据服务其核心数 以及代码耗时等各种因数考虑开启的写入线程数量
没个线程独立管理一个sqlsession。单次执行3万条，循环批量写入。逻辑可以自己控制
我开启的是15个线程 服务器配置 4核心 代码跑起来 使用率在370%左右 cpu
优化点4：滚动查询
什么是滚动查询，不知道大家有没有发现 一张表如果有1000万条数据 你用普通分页查询，一开始很快，越到后面越慢。为了避免这种低效率分页。转而使用了滚动查询
核心依赖 每张表的自增id 每次查询 where条件是 例如 查询第900万开始 后 100条数据 那么就是 where id>9000000 order by id desc limit 100 这样的效率是非常高的，id等于字典里面的目录，
直接把阅读指针 放到9000000万的点， 在这里面 取出前100条数据。 而不是 需要把前900万条数据先 翻一遍 才知道 哦 原来那不是我想要的。 向我写的代码 是一线程任务形式提交，每个任务都会有起始id，和 结束id，所以我还会在
条件加上where id>9000000 and id<9001000 order by id desc limit 100 下一次查询的时候取出 本次查询最大id 当做 起始id 继续往下查。
优化方向二：（数据库引擎参数配置优化）
优化点1：global innodb_flush_log_at_trx_commit（事务提交时 日志设置 效果明显）
0：事务提交时，不做日志写入操作，而是每秒钟将log buffer中的日志写入文件并且flush磁盘一次。
1：每秒钟或者事务提交时，都会引起日志写入和flush磁盘操作，这样设计也是最安全的。
2：每次事务提交时，进行日志写入，但此时没有flush磁盘操作，而是在每秒钟时进行flush磁盘操作。
建议根据情况具体考虑，需要炒鸡丝管理员权限，0的效率最高 但是有一定风险。我选的0 迁移后又改为1
优化点2：max_connections 最大连接数 ，这个要综合考虑各种情况包涵服务器节点数 线程数据，服务器核心数 等资源分配情况
我给调到了200 其实并不大这点连接 mysql 绝对扛得住。 我一个节点 15个线程再跑，最多一个节点占用15个连接 8个节点 大约是120连接数。 考虑 平时还有别人使用所以我 设置为 200 这个大家根据自己的情况去设置。
但是也不建议调到太高。
优化点3：innodb_buffer_pool_size（InnoDB缓冲池）
- 数据缓存
- 索引缓存
- 缓冲 – 更改的数据(通常称为脏数据)在被刷新到硬盘之前先存放到缓冲
- 存储内部结构 – 一些结构如自适应哈希索引或者行锁也都存储在InnoDB缓冲池
如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。
MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。
Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。
和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。
优化点4：innodb_autoextend_increment (tablespace 空间)
此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，
每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
优化点5：事务级别
事务级别调到最低。mysql默认是第三级别
会增加很高的效率 风险高 谨慎使用
优化方式三：（启动分配优化 jvm）
优化点1： -Xmx2g 为jvm启动时分配的内存
-Xms2g 为jvm运行过程中分配的最大内存
-Xss1m 没个线程分配的最大内存
这些根据你自己的需要去调整吧。如果代码不是 太费内存，起始这些参数不改也行。看需求了
优化方式四：（服务器方向优化，分布式多节点）
优化点1：多节点部署程序，将性能跑到极限
一共部署了8个节点，每个节点15个线程，每个服务器 cpu使用率 在 350-370
核心：
八个节点
通过 job 手动触发的。八个节点一起跑，大约 我测试过
查询效率大概单个线程 5毫秒 查询100条数。
也就是1秒 = 一个线程 2万条数 = 15 个线程 30万数据 = 8各节点 240万数据
当然这也只是理论极限，实际并没有这么快 但是也慢不到那里去，我测试的时候最快一次 单节点 4秒钟 查出来 50万条数据。 当然是最快的一次 哈哈。单个节点。多个节点 查不同的表会更快
当然这种效率完全能满足我们的查询需求了
写入效率 就是非常低了 大约是查询的 10倍以上消耗的时间
大约一次耗时20-30ms
但是处理起来 1秒 = 一个线程 2000条写入 = 15个线程 30000 写入 = 8 个节点 24万写入
mysql 这个写入效率还是可以的把 哈哈。
优化点5:(流写入)
使用mysql 官方 自带的流写入
因为根据上面的分析 我们性能的瓶颈在 插入上 那么如何提升
使用mysql 的流写入。
建议用sql 的方方式 传递路径 让mysql 去读取数据文件，因为如果直接不往文件里面写入
以流的方式写入 那么需要消耗非常的内存，因为一般只有数据量大的时候才会 使用这种方式。
所以还是写入文件合适，并且也慢不了多少。
效率 经过测试 大约在每秒钟 40万左右 这个数字 根据 你自身的数据大小 和mysql 缓存区配置都是有关系的。
但是这种方式 是 目前写入最快的一种方式了。
不过 这里坑比较多。建议大家谨慎使用
高版本mysql 驱动 流写入 有bug 一直说没权限 低版本的能用 但是表情符号 存入不进去。
高板本指的是 6以上
低版本值得是6以下 具体的 你自己去试 看场景。
我试了10几个版本发现的这个问题
高版本 mysql 流传递进去的时候 少东西。所以一直不行。
因为用的人太少，所以官方一直也没发现。也没修复。。。
网上能查到的大部分资料 用的基本都是5版本的。
高版本他绝对用不了，不信你试试 哈哈。
本篇文章没有那么多的代码，只是记录一下操作后的 的心得。和优化的整体思路，如果想要具体操作 还需要你有一定的功底，并且 可以根据相关的优化点，自己去查查咋实现。
因为加上代码的话，就太多了。相信我 效率绝对杠杠的。
