【无标题】

最新推荐文章于 2024-06-11 14:10:40 发布

原创最新推荐文章于 2024-06-11 14:10:40 发布 · 1.2k 阅读

36 ·

CC 4.0 BY-SA版权

文章标签：

#python #eclipse #java

Mysql的一些简单的优化

mysql亿万级数据如何插入最快
- 一、亿万数据的插入
- 二、亿万数据的查询

mysql亿万级数据如何插入最快

日常处理数据的过程分为：查找，修改，删除，添加，这些基本的操作针对数据量少的数据是不需要考虑去优化的，但是随着数据量的不断增多，一些基本的数据优化在我们项目中是非常重要的。那么，以下是我自己对mysql大数据量优化的一些见解，希望对大家有帮助，大家有什么更好的见解也可以分享出来，大家一同学习学习，感谢各位大佬。

一、亿万数据的插入

在了解以下的东西是，我们先了解一下mysql的特性：mysql有几个特性？相信很多人都学过，但是一时间想说出来，并不那么容易。

原子性（atomicity）:

一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency）数据库总是从一个一致性状态转换到下一个一致性状态。在前面的例子中，一致性确保了，即使在执行第3、4条语句之间时系统崩溃，支票账户中也不会损失200美元。如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。
隔离性（isolation）通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。在前面的例子中，当执行完第3条语句、第4条语句还未开始时，此时有另外一个账户汇总程序开始运行，其看到的支票账户的余额并没有被减去200美元。后面我们讨论隔离级别（isolation level）的时候，会发现为什么我们要说“通常来说”是不可见的。
持久性（durability）一旦提交，事务所做的修改就会被永久保存到数据库中。此时即使系统崩溃，数据也不会丢失。持久性是一个有点模糊的概念，实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有100%的持久性保障（如果数据库本身就能做到真正的持久性，那么备份又怎么能增加持久性呢？）。

事务是干什么用的？
事务是访问和更新数据库的程序执行单元，事务中包含一个或者多个SQL语句，这些语句要么都执行，要么就都不执行，也就是要么都成功，要么都不成功。

了解完事务，我们再来了解一下，什么是批处理？
批处理（Batch Processing）是一种计算机处理方式，它涉及将一组或多组任务或作业集合起来，按照预设的顺序和规则自动化地进行处理。这种处理方式特别适用于那些需要重复执行的任务，因为它可以提高计算机的工作效率

根据上面的一些名词定义和我写代码的验证，结论如下：
1、不用批处理，不用事务（三万条数据耗时：即65.02秒）
2、只用批处理，不用事务（平均每 2.1 秒插入十万条数据）
3、只用事务，不用批处理；（10秒插入3完数据）
4、既用事务，也用批处理；（平均每 1.9 秒插入十万条数据）

二、亿万数据的查询

一、分表：
这边的分别分为垂直分表和时间分表
1、垂直分表：
垂直分表：这种方法将数据库表按列进行切分，每个子表只包含原始表的部分列。常用于处理大型表或者需要频繁更新的字段。
优点：
是提高了性能、节省存储空间并加速查询操作；
缺点
是在多个子表之间共享相同的索引会导致复杂度上升。

下面是示例代码：

CREATE TABLE main_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    address VARCHAR(255)
);
 
-- 创建子表1
CREATE TABLE sub_table1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);
 
-- 创建子表2
CREATE TABLE sub_table2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(255)
);
 
-- 向主表插入数据
INSERT INTO main_table (name, age, address) VALUES ('John', 30, 'New York');
 
-- 从主表查询数据到子表1
SELECT * FROM main_table WHERE id = 1;
 
-- 从主表查询数据到子表2
SELECT * FROM main_table WHERE id = 1;

2、时间分表
时间分表：这种方法根据特定的日期或时间条件对数据进行水平切分，使得每个子表都包含指定时间范围内的数据。常用于长期保存历史记录或者按月/年等周期进行统计分析。
优点：
是可以有效地管理大量历史数据，并支持快速删除过期数据；
缺点：
是需要手动创建和维护子表。

下面是示例代码：

-- 创建主表
CREATE TABLE main_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255),
    created_at DATETIME
);
 
-- 创建子表1（包含2021年所有数据）
CREATE TABLE sub_table1 PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    ...
);
 
-- 创建子表2（包含2022年所有数据）
CREATE TABLE sub_table2 PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    ...
);
 
-- 向主表插入数据
INSERT INTO main_table (data, created_at) VALUES ('Data 1', NOW());
 
-- 从主表查询数据到子表1
SELECT * FROM main_table WHERE YEAR(created_at) = 2021;
 
-- 从主表查询数据到子表2
SELECT * FROM main_table WHERE YEAR(created_at) = 2022;