ppt做的比较好的网站,python是做网站的吗,成都平台网站开发公司,怎样做网站卖网站文章目录 1 大量数据优化1.1 引言1.2 评估表数据体量1.2.1 表容量1.2.2 磁盘空间1.2.3 实例容量 1.3 出现问题的原因1.4 解决问题1.4.1 数据表分区1.4.1.1 简介1.4.1.2 分区限制和执行计划1.4.1.3 分区表的索引1.4.1.4 为什么分区键必须是主键的一部分1.4.1.5 操作分区1.4.1.5.… 文章目录 1 大量数据优化1.1 引言1.2 评估表数据体量1.2.1 表容量1.2.2 磁盘空间1.2.3 实例容量 1.3 出现问题的原因1.4 解决问题1.4.1 数据表分区1.4.1.1 简介1.4.1.2 分区限制和执行计划1.4.1.3 分区表的索引1.4.1.4 为什么分区键必须是主键的一部分1.4.1.5 操作分区1.4.1.5.1 建表分区1.4.1.5.2 建索引分区1.4.1.5.3 删除分区1.4.1.5.4 备份分区 1.4.2 数据库分表1.4.2.1 简介1.4.2.2 分库分表方案1.4.2.2.1 取模方案1.4.2.2.2 range 范围方案1.4.2.2.3 hash取模和range方案结合 1.4.2.3 分区分表区别1.4.2.4 分区分表的联系1.4.2.5 分库分表存在的问题 1.4.3 冷热归档1.4.3.1 简介1.4.3.2 操作 1.5 实际选择 1 大量数据优化
1.1 引言
当我们业务数据库表中的数据越来越多如果你也和我遇到了以下类似场景那让我们一起来解决这个问题
数据的插入,查询时长较长后续业务需求的扩展 在表中新增字段 影响较大表中的数据并不是所有的都为有效数据 需求只查询时间区间内的
1.2 评估表数据体量
我们可以从表容量/磁盘空间/实例容量三方面评估数据体量接下来让我们分别展开来看看
1.2.1 表容量
表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表建议单表不要超过2000W行数据量总大小15G以内。访问量单表读写量在1600/s以内
查询行数据的方式我们一般查询表数据有多少数据时用到的经典sql语句如下
select count(*) from tableselect count(1) from table
但是当数据量过大的时候这样的查询就可能会超时所以我们要换一种查询方式
use 库名show table status like 表名 ; 或 show table status like 表名\G ;
上述方法不仅可以查询表的数据还可以输出表的详细信息 , 加 \G 可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等
1.2.2 磁盘空间
查看指定数据库容量大小
select
table_schema as 数据库,
table_name as 表名,
table_rows as 记录数,
truncate(data_length/1024/1024, 2) as 数据容量(MB),
truncate(index_length/1024/1024, 2) as 索引容量(MB)
from information_schema.tables
order by data_length desc, index_length desc;查询单个库中所有表磁盘占用大小
select
table_schema as 数据库,
table_name as 表名,
table_rows as 记录数,
truncate(data_length/1024/1024, 2) as 数据容量(MB),
truncate(index_length/1024/1024, 2) as 索引容量(MB)
from information_schema.tables
where table_schemamysql
order by data_length desc, index_length desc;查询出的结果如下
建议数据量占磁盘使用率的70%以内。同时对于一些数据增长较快可以考虑使用大的慢盘进行数据归档
1.2.3 实例容量
MySQL是基于线程的服务模型因此在一些并发较高的场景下单实例并不能充分利用服务器的CPU资源吞吐量反而会卡在mysql层可以根据业务考虑自己的实例模式
1.3 出现问题的原因
上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢
点击了解 MySQL 一棵B树可以存多少数据量
1.4 解决问题
这里提供了三种解决方案包括数据表分区分库分表冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案
1.4.1 数据表分区
1.4.1.1 简介
表分区可以在区间内查询对应的数据降低查询范围 并且索引分区也可以进一步提高命中率提升查询效率 分区是指将一个表的数据按照条件分布到不同的文件上面未分区前都是存放在一个文件上面的但是它还是指向的同一张表只是把数据分散到了不同文件而已。
表分区有什么好处
与单个磁盘或文件系统分区相比可以存储更多的数据。对于那些已经失去保存意义的数据通常可以通过删除与那些数据有关的分区很容易地删除那些数据。相反地在某些情况下添加新数据的过程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。一些查询可以得到极大的优化这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改所以在第一次配置分区方案时还不曾这么做时可以重新组织数据来提高那些常用查询的效率。涉及到例如SUM()和COUNT()这样聚合函数的查询可以很容易地进行并行处理。这种查询的一个简单例子如 SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id。通过并行这意味着该查询可以在每个分区上同时进行最终结果只需通过统计所有分区得到的结果。通过跨多个磁盘来分散数据查询来获得更大的查询吞吐量。
1.4.1.2 分区限制和执行计划
表分区的限制因素
一个表最多只能有1024个分区。MySQL5.1中分区表达式必须是整数或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。如果分区字段中有主键或者唯一索引的列那么含有主键列和唯一索引列都必须包含进来。即分区字段要么不包含主键或者索引列要么包含全部主键和索引列。分区表中无法使用外键约束。MySQL的分区适用于一个表的所有数据和索引不能只对表数据分区而不对索引分区也不能只对索引分区而不对表分区也不能只对表的一部分数据分区。
MySQL 如何选择执行计划根据多个因素来选择是否使用索引包括
分区键是否与查询条件匹配如果查询中涉及的列是分区键如 order_dateMySQL 可以智能地跳过某些分区从而提高查询效率。索引列与查询条件的匹配如果查询涉及的列有索引MySQL 会优先使用该索引来优化查询。分区裁剪Partition Pruning如果查询中的条件涉及分区键MySQL 会在查询执行前排除掉不相关的分区这样可以减少扫描的分区数量。
1.4.1.3 分区表的索引
在 MySQL 中当创建分区表时索引的创建有两个方面需要注意
主键索引如果表有主键MySQL 会自动将主键分配到每个分区。分区键必须是主键的一部分。否则MySQL 会报错要求修改表使分区键成为主键的一部分。二级索引普通索引 创建普通索引时需要指定LOCAL如果没有指定 LOCAL 选项MySQL 会创建一个全局索引这意味着索引在所有分区之间共享。而如果使用 LOCAL 选项它会在每个分区上创建一个局部索引。也就是说局部索引仅在当前分区内有效。 在每个分区内创建局部索引适用于希望索引仅在当前分区内有效的场景。
全局索引与本地索引 (Local Index) 的区别
全局索引Global Index全局索引是跨分区的即它将所有分区的数据当作一个整体进行索引。这意味着无论数据在哪个分区索引都会在整个表上保持一致。MySQL 默认情况下为分区表创建的是全局索引。本地索引Local Index本地索引是分区级别的即每个分区有自己独立的索引。这意味着索引只会在特定的分区内进行查找不会跨分区。如果想要创建本地索引可以通过 LOCAL 关键字显式地声明 通常情况下如果希望索引更高效地工作尤其是在有大量数据的分区表中使用 LOCAL 索引
CREATE TABLE orders (id INT PRIMARY KEY, order_date DATE,amount DECIMAL(10, 2),//INDEX idx_order_date (order_date)// 全局索引INDEX idx_order_date (order_date) LOCAL// 分区索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);另外注意索引不需要和分区列一样按分区进行索引MySQL 会根据查询条件智能选择最优的执行计划。
1.4.1.4 为什么分区键必须是主键的一部分
在 MySQL 中分区表会将数据分散到多个物理分区中而每个分区必须是一个完整的表。这意味着在进行分区时MySQL 必须知道如何将数据分配到不同的分区上因此它需要依据某个键即分区键来判断数据属于哪个分区。
主键与分区的关系
主键的约束主键要求每条记录必须是唯一的并且必须有一个明确的唯一标识符。因此主键的所有列在物理存储上必须能够唯一标识一行数据。分区键的作用分区键决定了数据行在哪个分区中。因此分区键的值需要和主键一同存在以便 MySQL 可以唯一地标识每条记录并且可以将数据正确地分配到相应的分区中。
CREATE TABLE orders (id INT PRIMARY KEY, order_date DATE,amount DECIMAL(10, 2),INDEX idx_order_date (order_date)// 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);在这个例子中id 是主键YEAR(order_date) 是分区键。 如果 YEAR(order_date) 不是主键的一部分 在 MySQL 中如果你的表使用了分区分区键必须是主键的一部分。因此你不能仅通过 id 作为主键且不包含 YEAR(order_date) 作为主键的一部分。如果你这样做会导致 MySQL 报错类似于
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function这是因为 MySQL 必须使用分区键来确定数据行的位置。如果分区键没有包含在主键中MySQL 就不能正确地对每行数据进行唯一标识和分配因此无法进行分区。
解决方法 可以将 order_date 的 YEAR(order_date) 或者 order_date 本身添加到主键中使得分区键是主键的一部分。例如
CREATE TABLE orders (id INT,order_date DATE,amount DECIMAL(10, 2),PRIMARY KEY (id, YEAR(order_date)), -- 将分区键加入主键INDEX idx_order_date (order_date)// 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);1.4.1.5 操作分区
在进行分区之前可以用如下方法 看下数据库表是否支持分区
mysql show variables like %partition%;
--------------------------
| Variable_name | Value |
--------------------------
| have_partitioning | YES |
--------------------------
1 row in set (0.00 sec)1.4.1.5.1 建表分区
CREATE TABLE test_table (id INT,name VARCHAR(50),created_at DATETIME
)
PARTITION BY RANGE (YEAR(created_at))
(PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022),...PARTITION pn VALUES LESS THAN MAXVALUE
);1.4.1.5.2 建索引分区
创建分区索引的语法与创建普通索引类似。我们可以在创建表时同时创建分区索引也可以在创建表后单独创建分区索引 在建表时建立索引分区
-- 在创建表时创建分区索引
CREATE TABLE orders (id INT,order_date DATE,amount DECIMAL(10, 2),INDEX idx_order_date (order_date) // 全局索引
)
PARTITION BY RANGE (YEAR(order_date)) (PARTITION p2018 VALUES LESS THAN (2019),PARTITION p2019 VALUES LESS THAN (2020),PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022)
);
-- 创建表后单独创建分区索引
ALTER TABLE orders ADD INDEX idx_order_date (order_date) LOCAL;//局部索引在建表后建立索引分区
-- 建立索引分区
ALTER TABLE your_table
PARTITION BY RANGE (YEAR(created_at))
(PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022),...PARTITION pn VALUES LESS THAN MAXVALUE
)
PARTITION BY KEY(id) PARTITIONS 4;查询时可以添加分区也可以不添加分区
SELECT * FROM t_order PARTITION (p0);
或者
SELECT * FROM t_order1.4.1.5.3 删除分区
在 MySQL 中删除分区时数据会被删除这是一个非常重要的注意事项。具体来说删除分区不仅仅是删除分区的定义还会导致该分区中所有数据的删除。
假设你有一个按 YEAR(order_date) 分区的表 orders并且你有以下分区
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)执行下面的 SQL 语句来删除 2020 年的分区
ALTER TABLE orders DROP PARTITION p2020;1.4.1.5.4 备份分区
将数据转移到其他分区 在删除分区前可以通过 INSERT INTO ... SELECT 将数据从删除的分区转移到其他分区。备份数据 在删除分区之前可以通过 SELECT INTO OUTFILE 或其他备份方法将数据导出并备份删除分区后再将数据导入回去。使用 REORGANIZE PARTITION 可以使用 REORGANIZE PARTITION 语句将数据从一个分区移动到另一个分区而不是删除数据。
ALTER TABLE orders REORGANIZE PARTITION p2020 INTO (PARTITION p2020_new VALUES LESS THAN (2021)
);1.4.2 数据库分表
1.4.2.1 简介
分表后显而易见单表数据量降低树的高度变低查询经历的磁盘io变少则可以提高效率 分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题将原来独立的数据库拆分成若干数据库组成 将数据大表拆分成若干数据表组成使得单一数据库、单一数据表的数据量变小从而达到提升数据库性能的目的。
mysql 分表分为两种水平分表和垂直分表
水平分表 定义数据表行的拆分通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。比如一个表有4000万数据查询很慢可以分到四个表每个表有1000万数据 垂直分表 定义列的拆分根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分然后利用主键关联。或者一个数据库里面有订单表和用户表数据量都很大进行垂直拆分用户库存用户表的数据订单库存订单表的数据 缺点垂直分隔的缺点比较明显数据不在一张表中会增加 join 或 union 之类的操作 知道了两个知识后我们来看一下分库分表的方案
1.4.2.2 分库分表方案
1.4.2.2.1 取模方案
拆分之前先预估一下数据量。比如用户表有4000w数据现在要把这些数据分到4个表user1 user2 uesr3 user4。比如id 1717对4取模为1加上 所以这条数据存到user2表。 注意进行水平拆分后的表要去掉auto_increment自增长。这时候的id可以用一个id 自增长临时表获得或者使用 redis incr的方法。 优点数据均匀的分到各个表中出现热点问题的概率很低。 缺点以后的数据扩容迁移比较困难难当数据量变大之后以前分到4个表现在要分到8个表取模的值就变了需要重新进行数据迁移。 点击了解 数据库之Sharding分库分表操作详解
1.4.2.2.2 range 范围方案
以范围进行拆分数据就是在某个范围内的订单存放到某个表中。比如id12存放到user1表id1300万的存放到user2 表。
优点有利于将来对数据的扩容 缺点如果热点数据都存在一个表中则压力都在一个表中其他表没有压力。 我们看到以上两种方案 都存在缺点 但是却又是互补的那么我们将这两个方案结合会怎样呢
1.4.2.2.3 hash取模和range方案结合
如下图 我们可以看到 group 组存放id 为 0~4000万 的数据然后有三个数据库 DB0 DB1 DB2DB0里面有四个数据库DB1 和DB2 有三个数据库
假如id为15000 然后对10取模为啥对10 取模 因为有10个表取0 然后 落在DB_0,然后在根据range 范围落在Table_0 里面。 总结采用 hash 取模和 range 方案结合 既可以避免热点数据的问题也有利于将来对数据的扩容
1.4.2.3 分区分表区别
分区分表区别
实现方式上 mysql 的分表是真正的分表一张表分成很多表后每一个小表都是完整的一张表都对应三个文件一个.MYD数据文件.MYI索引文件.frm表结构分区不一样一张大表进行分区后它还是一张表不会变成两张表但是它存放数据的区块变多了。 提高性能上 分表重点是存取数据时如何提高mysql并发能力上分区是如何突破磁盘的读写能力从而达到提高mysql性能的目的。 实现的难易度上 分表的方法有很多用merge来分表是最简单的一种方式。这种方式根分区难易度差不多并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。分区实现是比较简单的建立分区表和建平常的表没什么区别并且对开代码端来说是透明的
1.4.2.4 分区分表的联系
都能提高mysql的性高在高并发状态下都有一个良好的表现。分表和分区不矛盾可以相互配合的对于那些大访问量并且表数据比较多的表我们可以采取分表和分区结合的方式访问量不大但是表数据很多的表我们可以采取分区的方式等。
1.4.2.5 分库分表存在的问题
事务问题 在执行分库分表之后由于数据存储到了不同的库上数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务将付出高昂的性能代价如果由应用程序去协助控制形成程序逻辑上的事务又会造成编程方面的负担。跨库跨表的join问题 在执行了分库分表之后难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上这时表的关联操作将受到限制我们无法join位于不同分库的表也无法join分表粒度不同的表结果原本一次查询能够完成的业务可能需要多次查询才能完成。额外的数据管理负担和数据运算压力 额外的数据管理负担最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题这些都可以通过应用程序解决但必然引起额外的逻辑运算例如对于一个记录用户成绩的用户数据表userTable业务要求查出成绩最好的100位在进行分表之前只需一个order by语句就可以搞定但是在进行分表之后将需要n个order by语句分别查出每一个分表的前100名用户数据然后再对这些数据进行合并计算才能得出结果。
1.4.3 冷热归档
1.4.3.1 简介
为什么要冷热归档其实原因和方案二类似都是降低单表数据量树的高度变低查询经历的磁盘io变少则可以提高效率 如果大家的业务数据有明显的冷热区分比如只需要展示近一周或一个月的数据。那么这种情况这一周和一个月的数据我们称之为热数据其余数据为冷数据。那么我们可以将冷数据归档在其他的库表中提高我们热数据的操作效率。
1.4.3.2 操作
创建归档表 创建的归档表 原则上要与原表保持一致 归档表数据的初始化
业务增量数据处理过程 数据的获取过程
1.5 实际选择
方案试用场景优点缺点数据表分区1.数据量较大 2.查询场景只在某个区 3.没有联合查询的场景分区分表是在物理上对数据表所对应的文件进行拆分对应的表名是不变的所以不会影响到之前业务逻辑的sql分表后的查询等业务会创建对应的对象也会造成一定的开销分区数据若要聚合的话 耗费时间也较长;使用范围不适合数据量千万级以上的数据表分表数据量较大,无法区分明显冷热区 且数据可以完整按照区间划分适用于对冷热分区的界限不是很明显的数据对后续类似的数据可以采用该方式将大表拆分成小表 提高查询插入等效率若大数据表逐渐增多 那么对应的数据库表越来越多 每个表都需要分表; 区间的划分较为固定若后续单表的数据量大起来 也会对性能造成影响;实现复杂度相对方案三比较复杂 需要测试整人实现过程 在编码层处理 对原有业务有影影响冷热归档分库数据量较大;数据冷热分区明显;冷数据使用频率极低;数据迁移的过程对业务的影影响较小 开发量也较少减少成本需要确认分表规则
