DBA数据库笔记之(六)MySQL数据管理策略之分区分库分表

2024-01-19 本文已影响0人 Mr培

MySQL分而治之-分区和分库分表

分区

分区的优势

优化查询
方便维护管理
单表能存储更多的数据

分区的使用场景

大表的查询性能优化
冷热数据分离
历史数据管理

常用分区类型

分区类型	解释	适用场景距离
范围分区	根据某个字段范围条件将数据分区	订单表按订单日期进行范围分区，日志表按照日志记录的时间进行范围分区
列表分区	基于枚举出的值进行分区	商品表按照商品类别进行列表分区，用户信息根据地区进行列表分区
哈希分区	根据某个列的哈希值进行分区	用户信息按照用户id进行哈希分区，订单表按照订单ID进行哈希分区
按键分区	根据某个列的键值将表数据分割成不同的分区	用户信息表按照用户ID进行按键分区，日志表按照日志ID进行按键分区
子分区	对分区表中的每个分区再进一步划分	日志表对年份进行分区之后，再对日期做哈希分区

分区注意事项

分区表的限制

不支持外键，全文索引，临时表等
分区字段为null，在范围分区中这一行会被分到最小的分区
MySQL分区中如果存在主键或者唯一索引则分区列必须包含在其中，查询不走分区键则可能扫描所有分区

分区键的选择
分区数量的选择

如何分区

范围分区

按照整数类型进行范围分区

# p0 p1 p2 分区名字
create table range_student_scores(
    id int,
    username varchar(10),
    score int
)
partition by range(score)(
    partition p0 values less than (60),
    partition p1 values less than (80),
    partition p2 values less than (101)
);

按照时间进行范围分区

create table range_log_data(
    id int,
    log_message yexy,
    log_date date
)
partition by range(year(log_date))(
# 存放2021之前的数据
    partition p2020 values less than (2021),
# 存放2021的数据
    partition p2021 values less than (2022),
# 存放2022的数据
    partition p2022 values less than (2023)
);

列表分区

按整形进行列表分区

create table list_log_info(
    id int,
    log_message varchar(50),
    log_date date,
    log_type int
)
partition by list (log_type)(
# 存放log_type的值为1的数据
    partition p_info values in (1),
# 存放log_type=2的数据
    partition p_warning values in (2),
# 存放log_type=3的数据
    partition p_err values in (3)
)

按时间进行列表分区

create table list_log_data(
    id int,
    log_message text,
    log_date date
    )
# month函数求出日期的月份
partition by list (month(log_date))(
# 存放月份为1，2，3的数据
    partition p_q1 values in (1,2,3),
# 存放月份为4，5，6的数据
    partition p_q2 values in (4,5,6),
# 存放月份为7，8，9的数据
    partition p_q3 values in (7,8,9),
# 存放月份为10，11，12的数据
    partition p_q4 values in (10,11,12)
)

哈希分区

整数的哈希分区

create table hash_student_info(
    id int,
    student_id int,
    student_name varchar(50)
)
partition by hash(student_id)
# 设置4个分区
partitions 4;

基于时间的哈希分区

create table hash_employees(
    id int not null,
    employees_name varchar(50),
    date_of_birth date
)
partition by hash(year(date_of_birth))
# 设置4个分区
partitions 4;

按键分区

不指定分片键

# 默认主键 > 唯一索引not null
create table key_user_info(
    id int not null primary key,
    name varchar(20)
)
partition by key()
partitions 4;

使用字符串类型字段作为分片键

create table key_studenet_info(
    id int,
    name varchar(20)
)
partition by key(name)
partitions 4;

子分区

按同一个字段创建子分区

create table sub_user_info(
    id int,
    birthdate date
)
partition by range (year(birthdate))
subpartition by hash(to_days(birthdate))
subpartitions 2(
        # 存放出生年份在2000年之前的数据
        partition p0 values less than (2000),
        # 存放出生年份在2000~2009年的数据
        partition p1 values less than (2010),
        # 存放出生年份在2010年之后的数据
        partition p2 values less than maxvalue
);

按不同的字段创建子分区

create table sub_employees(
    id int,
    employee_name varchar(50),
    department_id int
)
# 按部门分成两个区
partition by list (department_id)
subpartition by key (id)
subpartitions 4(
    # 存放部门id为1,2的数据
    partition p1 values in (1,2)(
        # 每个分区对id做按键分区，分四个子分区
        subpartition s1,
        subpartition s2,
        subpartition s3,
        subpartition s4
    ),
    partition p2 values in (3,4)(
        subpartition s5,
        subpartition s6,
        subpartition s7,
        subpartition s8
    )
);

分区的管理

范围分区管理

添加分区

# 查看创建的一个分区
show create table range_log_data;

alter table range_log_data add partition (partition p2023 values less than (2024));

查看指定分区的数据

select * from range_log_data partition (p2023);
# 查看各个分区的数据量
select partition_name,table_rows from information_schema.partitions where table_name = 'range_log_data';

删除分区

alter table range_log_data drop partition p2020;

分区交换

# 一张表的一个分区数据和另一个表的数据交换
# range_log_data_tmp数据要符合 p2021分区的要求
alter table range_log_data exchange partition p2021 with table range_log_data_tmp;

重建分区

alter table range_log_data rebuild partition p2020,p2021,p2022,p2023

列表分区管理

新增一个分区

# 查看创建的列表分区表结构
show create table list_log_info;
alter table list_log_info add partition (partition p_debug values in (0));

查询某个分区的数据

select * from list_log_info partition (p_err);

删除某个分区

alter table list_log_info drop partition p_debug;

哈希分区管理

减少分区

show create table hash_students_info;
alter table hash_students_info coalesce partition 1;

增加分区

# 增加三个分区
alter table hash_students_info add partition partitions 3;

按键分区管理

减少分区

show create table key_user_info;
alter table key_user_info coalesce partition 1;

增加分区

alter table key_user_info add partition partitions 3;

子分区管理

删除某个子分区

show create table sub_user_info;
alter table sub_user_info drop partition p2;

添加子分区

alter table sub_user_info add partition(partition p3 values less than (2020));

调整子分区范围

alter table sub_user_info reorganize partition p3 into (partition p3 values less than maxvalue);

MySQL分库分表

分库分表的场景

数据量太大
高并发读写需求
安全考虑

拆分模式之垂直拆分

拆分模式之垂直拆分.png
常用分片策略

取模分片
范围分片

分库分表工具

工具	特点
MyCAT	基于开源Cobar淮变而来，兼容大多数数据库，遵守MySQ原生协议，基于心跳的自动故障切换，以及支持读/写分离等
DBLE	基于MyCAT二次开发，在兼容性、复杂查询和分布式事务方面做了改进与优化，并修复了一些Bug。提供科学的元数据管理机制，可以更好地支持show、desc等管理命令
Atlas	在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，复了大量Bug，添加了很多功能和特性
MySQL Router	官方推出的轻量级中间件，可以在应用程序和后端MySQL服务之间提供透明路由，主要用来解决MySQL 主节点和从节点的高可用、负载均衡、易扩展等问题
sharding-sphere	sharding-sphere一套开源的分布式数据库中间件解决方案组成的生态圈，由Sharding-JDBC，Sharding-Proxy和Sharding-Sidecar(规划中)3款相互独立的产品组成
TDDL	TDDL是一个基于客户端的数据库中间件产品，基于JDBC规范，没有Server，以client-jar的形式存在

分库分表后面临的问题

事务支持问题
跨库查询问题
中间件高可用问题

MyCAT

MyCAT的作用

读写分离
分库分表
故障切换

MyCAT架构介绍

MyCAT架构介绍.png

MyCAT的安装

以主从架构为例三台机器，mycat单独一台机器

安装jdk
下载MyCAT安装包

# 部署mycat的机器
wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
unzip mycat2-install-template-1.21.zip
cd mycat/lib/
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
mv /usr/src/mycat /usr/local/

在MySQL中创建MyCAT的连接用户

# 在主库
create user 'mycat_rw'@'%' identifiled with mysql_native_password by '123456';
grant all on *.* to 'mycat_rw'@'%';

创建一个用来给MyCAT连接的库

# 在主库
create database mycat;

修改数据源配置文件

# 部署mycat的机器
cd /usr/local/mycat/
cd conf/
cd datasources/
# 修改相关配置，user，password，库名，ip地址
vim prototypeDs.datasource.json

修改MyCAT的用户名密码

cd /usr/local/mycat/conf/
# 修改配置，ip建议为null，不限制ip登录mycat；password；username
vim users/root.user.json

启动mycat

cd /usr/local/mycat/
chmod +x ./bin/*
./bin/mycat start
ps -ef | grep mycat
# 关闭
./bin/mycat stop
# 重启
./bin/mycat restart

查看mycat进程状态

./bin/mycat status
# 查看启动日志
tail -f /usr/local/mycat/logs/wrapper.log

登录MyCat

mysql -umycat -p'1234546' -P8066 -h192.168.12.163

MyCAT实现读写分离

读写分离架构图.png

配置后端数据源

创建写库配置

# 在mycat机器登录mycat
mysql -umycat -p'1234546' -P8066 -h192.168.12.163
# 执行
/*+ mycat:createDataSource{
    "name":"write",
    "url":"jdbc:mysql://192.168.12.161:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;

创建读库配置

/*+ mycat:createDataSource{
    "name":"read",
    "url":"jdbc:mysql://192.168.12.161:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;

确定数据源是否配置成功

/*+ mycat:showDataSources{} */;

配置集群和逻辑库

配置集群

# 在mycat里
/*! mycat:createCluster{"name":"RW","masters":["write"],"replicas":["read"]} */;

# 查看集群节点
/*+ mycat:showClusters{} */;

配置逻辑库

create database martin_rw;
cd /usr/local/mycat/conf/schemas
vim martin_rw.schema.json
# 添加配置
"targetName":"RW"

重启mycat

/usr/local/mycat/bin/mycat restart
/usr/local/mycat/bin/mycat status

测试
... ...

MyCAT实现分库分表

常用管理命令

# 登录到MyCAT里

# 创建用户 ip 能连接的客户端，transactionType事务类型
/*+ mycat:createUser{
    "username":"test_user",
    "password":"123456",
    "ip":"127.0.0.1",
    "transactionType":"xa"
}*/;
# 查看配置的文件
cd /usr/local/mycat/conf/users/
cat test_user.user.json
# 显示用户
/*+ mycat:showUsers */;
# 删除用户
/*+ mycat:dropUser{"username":"test_user"} */;

数据源管理相关命令

# 创建数据源
/*+ mycat:createDataSource{
    "name":"write",
    "url":"jdbc:mysql://192.168.12.161:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;
# 查询数据源
/*+ mycat:showDataSources{} */;
# 删除数据源
/*+ mycat:dropDataSource{"name":"test01"} */;

集群管理相关命令

# 创建集群 masters主库数据源；replicas从库数据源
/*! mycat:createCluster{"name":"c_test_1","masters":["test01"],"replicas":["test01"]} */;
# 查看数据源
/*+ mycat:showClusters{} */;
# 删除数据源
/*+ mycat:dropCluster{"name":"c_test_1"} */;

MyCAT分表环境准备

配置数据源

# 创建第一个数据源,一台主库
/*+ mycat:createDataSource{
    "name":"martin_w0",
    "url":"jdbc:mysql://192.168.12.161:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;
# 创建第二个数据源，是161的从库
/*+ mycat:createDataSource{
    "name":"martin_r0",
    "url":"jdbc:mysql://192.168.12.162:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;
# 创建第三个数据源,第二套MySQL的主库
/*+ mycat:createDataSource{
    "name":"martin_w1",
    "url":"jdbc:mysql://192.168.12.163:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;
# 创建第四个数据源,163的从库
/*+ mycat:createDataSource{
    "name":"martin_r1",
    "url":"jdbc:mysql://192.168.12.164:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    "user":"mycat_rw",
    "password":"123456"
} */;

配置集群

# 集群名字MyCAT2中要以c开头以数字后缀结尾
/*+ mycat:createCluster{"name":"c0","masters":["martin_w0"],"replicas":["martin_r0"]}*/;
/*+ mycat:createCluster{"name":"c1","masters":["martin_w1"],"replicas":["martin_r1"]}*/;

创建逻辑库

# 直接执行create；客户端直接通过sharding_martin库名连接MyCAT
create database sharding_martin;

配置全局表

创建表

create table sharding_martin.global_t1(
    id int nout null primary key,
    name varchar(50),
    age int
)engine=innodb broadcast;

查看配置文件

cd /usr/local/mycat/conf/schemas/
cat sharding_martin.schema.json

数据测试

# 往global_t1表里写入数据，查看MyCAT中表的数据，查看其他MySQL种表的数据
... ...

配置分片表

创建分片表

# dbpartition分片规则；mod_hash(id)对id字段进行hash分片；dbpartitions数据库分片数量；tbpartition表的分片规则
create table hash_t1(
    id bigint not null auto_increment primary key,
    name varchar(20),
    age int
)engine=innodb
dbpartition by mod_hash(id) dbpartitions 1 tbpartition by mod_hash(id) tbpartitions 2;

查看配置文件

cd /usr/local/mycat/conf/schemas/
cat sharding_martin.schema.json

数据测试

# 往表中写入数据,在MyCAT上查看全表数据
select * from hash_t1;
# 再去分片查询数据
... ...

配置ER表

创建测试表

create table student_info(
    id bigint not null auto_increment primary key,
    user_id int,
    name varchar(10)
)engine=innodb dbpartition by mod_hash(user_id) dbpartitions 1 tbpartition by mod_hash(user_id) tbpartitions 4;

create table student_score(
    id bigint not null auto_increment primary key,
    user_id int,
    score int default null
)engine=innodb dbpartition by mod_hash(user_id) dbpartitions 1 tbpartition by mod_hash(user_id) tbpartitions 4;

查看表是否有ER关系

# groupId相同表示这一组表里面有相同的存储分布，也就是ER表
/*+ mycat:showErGroup{}*/;

数据测试

# 往每张表写入三条数据，再去所有分库分表查看数据
... ...

MyCAT文档

DBLE分区和分库分表

DBLE部署

下载安装
github下载地址

cd /usr/src/
wget https://github.com/actiontech/dble/releases/download/3.23.04.0%2Ftag/dble-3.23.04.0-20230619111642-java1.8.0_352-linux.tar.gz
tar zxvf dble-3.23.04.0-20230619111642-java1.8.0_352-linux.tar.gz
mv dble /usr/local/
cd /usr/local/dble/conf/
mv cluster_template.cnf cluster.cnf
mv bootstrap_template.cnf bootstrap.cnf
mv db_template.xml db.xml
mv user_template.xml user.xml
mv sharding_template.xml sharding.xml

编辑数据源

# 三台机器，一台dble，两台主MySQL

# 在两个主mysql库
create user 'dble_rw'@'%' identifiled with mysql_native_password by '123456';
grant all on *.* to 'dble_rw'@'%';

# 再修改MyCAT数据源配置
vim db.xml
# 修改deGroup 两个<dbInstance 中url，user，password，对应两个主库

启动DBLE

/usr/local/dble/bin/dble start
ps -ef | grep dble
# 登录dble的管理端口
mysql -P9066 -h 127.0.0.1 -u man1 -p654321 
# 登录到dble的数据管理端口
mysql -P8066 -h 127.0.0.1 -u root -p123456

配置文件解析

配置文件解析.png

DBLE实现分库分表

分库分表准备

修改后端数据源配置

cd /usr/local/dble/conf/
vim db.xml
# 修改 dbGroup1和dbGroup2对应第一套mysql的主库和第二套mysql的主库

创建schema

# 登录dble管理端口
mysql -P9066 -h 127.0.0.1 -u man1 -p654321 
# 建了虚拟节点对应的schema
create database @@shardingnode='dn$1-6';

# 查看
vim sharding.xml

查看有哪些逻辑库

# 登录查询端口
mysql -P8066 -h 127.0.0.1 -u root -p123456 
show databases;

枚举算法分片举例

... ...

分库分表之后注意的问题

MyCAT的一些注意事项

DDL不支持修改拆分键
分片表不支持外键
不支持多表delete、update
对于for update语句，会把sql中出现的表都加锁
不支持select into outfile
不支持set global级别的变量
不支持触发器

DBLE的一些注意事项

拆分字段的查旬条件尽可能是等值条件
如果SQL语句不带有拆分字段，那么DISTINCT、GROUP BY和ORDER BY在同一个SQL语句中尽量只出现一种
将参与Join的表中数据量较小的表配置成全局表，通过数据冗余避免跨节点
参与Join的表配置相同的拆分规则，查询时将拆分字段作为Join的关联条件，这样Join操作就可以在节点内完成

分库分表之后其它的一些问题

分布式事务
全局主键ID
SQL优化的问题
监控
扩容问题

DBA合理的管理内存和磁盘

MySQL体系结构

MySQL体系结构.png

mysql8.0已经弃用缓存

select * from user;
SELECT * from user;
# 为两个不同的缓存

一条查询语句的生命历程

一条查询语句的生命历程.png
一条更新语句的生命历程

一条更新语句的生命历程.png

缓冲区

缓冲池介绍

缓冲池介绍.png

InnoDB缓存池相关配置

修改缓冲池大小

show global variables like "innodb_buffer_pool_size";
# 设置
set global innodb_buffer_pool_size = 1024 * 1024 *1024;
# 修改配置文件
vim /data/mysql/conf/my.cnf
innodb_buffer_pool_size = 1G

配置多个缓冲区实例

show global variables like "innodb_buffer_pool_instances";
# 不支持动态修改
vim /data/mysql/conf/my.cnf
innodb_buffer_pool_instances = 2
# 重启MySQL

配置旧块的百分比

show global variables like "innodb_old_blocks_pct";
# 37%
set global innodb_old_blocks_pct = 37;

查看缓冲池的状态

通过show engine innodb status 查看缓冲池状态

show engine innodb status\G;

通过系统表查看缓冲池状态

select * from information_schema.innodb_buffer_pool_status\G;

更改缓存（change buffer）的意义

change buffer介绍
[图片上传失败...(image-5a754c-1705568737540)]

关于change buffer的一些问题

为什么更改缓冲区的存在可以减少随机访问I/O呢？

二级索引数据的写入是以页为单位的，每次操作可能位于同一页面，将同一页面的更改操作合并后再写入磁盘。（将多次磁盘写入变成一次磁盘写入）

change buffer有什么好处？

当辅助索引页不在缓冲池时，缓冲辅助索引的变更可以避免立即从磁盘读取受影响的索引页所需要的随机IO，当页面被其它读操作读入缓冲池时，缓存的更改可以在之后批量应用到磁盘

change buffer在什么时候合并？

当页面被读入缓冲池时，对change buffer的更改和buffer pool里面的数据页进行合并，这一页面才可以使用

通过参数设置

# 决定每秒从buffer pool刷新到磁盘的次数，以及合并change buffer的次数
show global variables like "innodb_io_capacity";

在崩溃恢复期间执行合并

什么时候应该使用change buffer？

当整合数据集无法放入缓冲池时或者存在大量修改辅助索引页的操作

什么时候不应该使用change buffer？

二级索引相当较少

change buffer的配置

修改哪些操作会启用change buffer

# all,none,inserts,deletes,changes,purges
show global variables like "innodb_change_buffering";

修改change buffer大小

# 最大设置为50
show global variables like "innodb_change_buffer_max_size";

查看change buffer的状态

通过查询InnoDB的状态查看change buffer的使用情况

show engine innodb status\G;

通过SQL语句查看change buffer的使用情况

SELECT
    ( SELECT count(*) FROM information_schema.innodb_buffer_page WHERE page_type LIKE 'IBUF%' ) AS change_buffer_pages,(
    SELECT
        count(*) 
    FROM
        information_schema.innodb_buffer_page 
        ) AS total_pages,(
SELECT
    (( change_buffer_pages / total_pages )* 100 )) AS change_buffer_percentage;

自适应哈希索引

自适应索引是什么

InnoDB储存引擎会监控表上对索引列的查询，MySQL自行决定什么时候创建自适应哈希索引（我们不能干涉）提高查询速度。

自适应哈希索引的作用

提高热点数据的查询速度，对热点数据创建自适应哈希索引

自适应哈希索引的管理

自适应哈希索引的开关

show global variables like "innodb_adaptuve_hash_index";

查看自适应哈希索引的使用情况

show engine innodb status\G;

控制自适应哈希索引的分区数量

# 最大设置为 512
show global variables like "innodb_adaptive_hash_index_parts";

日志缓冲区及其作用

日志缓冲区的工作原理

MySQL会将写入操作记录写入日志缓冲，然后异步写入磁盘的日志文件

日志缓冲区和change buffer有什么区别

日志缓冲区 :当执行写入操作时MySQL会将相关操作记录写入日志缓冲区再异步写入磁盘的日志文件，可以减少磁盘IO的次数，提高写入性能，同时通过日志缓冲区的操作记录MySQL在发生故障时还原未写入磁盘的数据，保证数据的一致性。（主要优化写入操作性能和提供数据一致性保护）
change buffer :是MySQL InnoDB存储引擎中一个专门优化索引更新的机制，当执行插入更新和删除操作时InnoDB存储引擎会将这些操作的变更记录存储在change buffer中，而不是直接更新索引，可以减少对磁盘的随机IO操作，提高写入性能，当查询操作需要访问被更新的索引时，InnoDB会将change buffer中的变更应用到索引中，保证数据的一致性。（主要优化索引更新操作性能）

相关优化策略

适当调整大小

日志缓冲区的相关配置

设置日志缓冲区的大小

 # 更新插入删除频繁的场景建议设置大一点可以减少IO消耗，默认16M
show global variables like "innodb_log_buffer_size";

控制日志缓冲区的刷新频率

show global variables like "innodb_flush_log%";

innodb_flush_log_at_timeout = 1

每隔1秒就会将日志缓冲区刷新到磁盘

innodb_flush_log_at_trx_commit =2

控制刷新频率，0 表示每秒将日志缓冲区写入日志文件一次，并在日志文件上执行磁盘刷新操作（MySQL崩溃的时候有可能最多会丢失一秒的事务，这是innodb不再符合数据一致性的要求）；1 表示每次提交事务时日志缓冲区都会写入日志文件一次，并在日志文件上执行磁盘刷新操作，最安全的；2 表示每一次提交事务后写入日志并且日志每秒刷新一次到磁盘（MySQL服务宕机但操作系统没有宕机时不会出现数据丢失，操作系统宕机时重启后可能会丢失RedoLog缓冲区中还没有刷新到RedoLog文件中的数据）
性能优先：0
安全性优先：1

查看日志缓冲区的状态

show engine innodb status\G;

系统表空间

初始化配置

# ibdata:1G:autoextend  文件名：大小：自动增长
show variables like "innodb_data_file_path";

增加系统表空间

# 不能修改现有表空间来增加
vim /data/mysql/conf/my.cnf
# 修改或没有就增加 innodb_data_file_path = ibdata:1G:autoextend 为
innodb_data_file_path = ibdata:1G;ibdata1:1G:autoextend
# mysql不支持减小现有表空间大小

独立表空间

独立表空间配置

开启独立表空间

# 默认开启
show global variables like "innodb_file_per_table";
# 关闭
set global innodb_file_per_table=off;

独立表空间的数据文件

如果开启，mysql数据目录下的库目录下会创建每一张表的数据文件

通过独立表空间导入其他实例的数据

前提条件

必须开启独立表空间
表空间的大小必须与目标实例的表空间一样
MySQL版本一样
innodb_default_row_format一样
... ...

导入表

1. 需要在目标实例创建相同表结构的表 importing_table_t1
2. 在目标实例丢弃刚刚创建的表空间
alter table importing_table_t1 discard tablespace;
3. 在原实例上执行
flush table importing_table_t1 for export;
4. 查看生产的表空间文件
ll | grep importing_table_t1
.ibd 数据文件
.cfg 原数据文件
5. 将文件传输到目标实例
scp importing_table_t1* 192.168.12.163:/data/mysql/data/martin
6. 目标实例修改文件属组
chown mysql.mysql importing_table_t1.*
在数据库里,导入表空间
alter table importing_table_t1 import tablespace;

独立表空间文件的优点
跟共享表空间相比，独立表空间有这些优点

执行truncate或者drop操作，可以释放表空间
alter table会释放影子表所占用的空间
可以在单独的存储设备上创建独立表空间文件
可以从另一个MSQL实例导入独立表空间文件中的表
支持的行格式为dynamic与compressed
允许通过监视表空间娄据文件的大小来监视文件系统上的表大小
为单个表的大小增长提供了足够空间

独立表空间文件的缺点
跟共享表空间相比，独立表空间有这些缺点

每张表都会有未使用的空间，如果管理不当，可能会致空间浪费
fsync操作针对每引表的文件，因此无法合并多张表的写入操作
必须为每一个独立表空间打开一个文件句柄，如果有多张表，可能会影响性能
删除独立表空间文件时，会扫描缓冲池，对于大型缓冲池，可能需要几秒时间

通用表空间

通用表空间的作用

与系统表空间类似，通用表空间是能够存储多个表数据的共享表空间
可以考虑使用不同的存储设备，以提高写入性能
支持表空间移动

限制

已经存在的表空间不能更改为通用表空间
不支持创建临时通用表空间
不支持临时表
truncate或者drop table，表空间不会释放，只会重用
alter table...discard tablespace，alter table...importing tablespace语句不支持使用通用表空间中的表
不支持分区表
不支持在数据目录下的子目录中创建通用表空间

通用表空间的使用

use database;
# mysql8.0.14之前 add datafile 必须添加
create tablespace ts1 add datafile 'ts1.idb' engine=innodb;
或者
create tablespace ts2 engine=innodb;

# 创建存放目录
mkdir /data/tablespace
chown mysql.mysql tablespace
# 修改配置文件
vim /data/mysql/conf/my.cnf
# 新增配置
innodb_directories = "/data/tablespace"
# 重启mysql生效
/etc/init.d/mysql.server restart

# 创建表空间
create tablespace ts3 add datafile '/data/tablespace/ts3.idb' engine=innodb;

将表添加到通用表空间

# 创建新表，表空间为 ts1
create table table_t1(id int primary key) tablespace ts1;

# 修改已经存在的表的表空间
create table table_t2(id int primary key);
alter table table_t2 tablespace ts1;

在几种表空间中移动表

独立表空间或系统表空间移动到通用表空间

create table table_t3(id int primary key);
# 移动到通用表空间
alter table table_t3 tablespace ts1;

独立表空间或通用表空间移动到系统表空间

# 移动到系统表空间
alter table table_t3 tablespace innodb_system;

系统表空间或通用表空间移动到独立表空间

alter table table_t3 tablespace innodb_file_per_table;

通用表空间维护

查看有哪些通用表空间

select name from information_schema.innodb_tablespaces where space_type='General';

重命名通用表空间

alter tablespace ts1 rename to ts1_bak;

删除通用表空间

# 保证通用表空间下没有任何表
select a.name as space_name,b.name as table_name from information_schema.innodb_tablespaces a,information_schema.innodb_tables b where a.space=b.space and a.name = 'ts1_bak';
# 将当前表空间下的表移动到其它表空间
alter table table_t1 tablespace ts2;
# 删除通用表空间
drop tablespace ts1_bak;

# 查看
select name from information_schema.innodb_tablespaces where space_type='General';

临时表空间

会话临时表空间

会话临时表空间存放的内容

用户创建的临时表
优化器创建的内部临时表

会话临时表空间的池

mysql在启动时会创建一个10个临时表空间的池，池的大小永远不会缩小并且表空间会根据需要自动添加到池中，在正常关闭或初始化时会删除掉临时表空间的池

# 进入会话临时表空间的文件夹查看
cd /data/mysql/data/#innodb_temp/
# mysql临时表空间的池
ls

查询会话临时表空间的状态

select * from information_schema.innodb_session_temp_tablespace;

全局临时表空间

全局临时表空间存放的内容

用户创建的临时表所做更改的回滚段

全局临时表空间的配置

# 默认放在mysql的数据目录下
show global variables like "innodb_temp_data_file_path";

查询全局临时表空间的状态

select * from information_schema.files where tablespace_name='innodb_temporary'\G;

双写缓冲区 Doublewrite Buffer

什么是Doublewrite Buffer

在应用重做日志前用户需要一个页的副本，当写入失效时先通过页的副本来还原这个页，再进行重做。MySQL8.0.20之前Doublewrite Buffer存储区域位于innodb系统表空间中，从MySQL8.0.20开始双写缓冲区存储区域在双写文件中。

Doublewrite Buffer的原理
MySQL会用系统表空间中的一块连续磁盘空间作为Doublewrite Buffer，当进行脏页刷新时首先将脏页的副本写到系统表空间的Doublewrite Buffer中，之后通过Doublewrite Buffer再分两次，每次1M顺序的写入共享表空间的物理磁盘上，然后调用fsync刷新操作系统的IO缓存，最后innodb后台IO线程将脏页刷新到磁盘数据文件。
因为Doublewrite页是连续的，所以这个过程是顺序写的，开销并不是很大，如果操作系统将页写到磁盘的过程中发生了崩溃在恢复过程中innodb存储引擎会从共享表空间中的Doublewrite Buffer中找到这个页的副本，然后将其复制到表空间文件，在应用重做日志。
Doublewrite Buffer不仅仅在内存中也在磁盘中。

Doublewrite Buffer的相关配置

控制是否启用Doublewrite Buffer

show global variables like "innodb_doublewrite";

定义Doublewrite Buffer文件的目录和数量

# 默认在数据目录下创建双写文件
show global variables like "innodb_doublewrite_dir";
# 进去数据目录查看
cd /data/mysql/data
ll | grep dblwr
# 数量
show global variables like "innodb_doublewrite_files";
# 查看Doublewrite Buffer在缓冲池的数量
show global variables like "innodb_buffer_pool_instances";

查看Doublewrite Buffer的运行情况

show global status like "innodb_dblwr%";

Innodb_dblwr_pages_written :表示Doublewrite Buffer一共写入的页数
Innodb_dblwr_writes :表示Doublewrite Buffer写入的次数

# 查看当前从缓冲池中刷新到磁盘页的数量
show global status like "innodb_buffer_pool_pages_flushed";

关于Doublewrite Buffer的一些问题

Doublewrite Buffer是如何恢复数据的？

在数据库异常关闭的情况下启动时都会做数据库恢复操作，恢复过程中数据库都会检查页面是不是合法，如果发现一个页面检验结果不一致这个时候会用到Doublewrite Buffer的功能吗，对有问题的数据页进行恢复。

可以通过RedoLog恢复数据，为什么还要Doublewrite Buffer？

RedoLog记录的是对数据页的操作，如果页都损坏了就无法执行RedoLog中的恢复操作，所以需要页的副本，如果系统崩溃了可以通过副本把原来的数据页还原回来

Doublewrite Buffer对性能影响大吗？

Doublewrite Buffer页是连续的，这个过程是顺序写的，开销并不是很大。

DBA数据库笔记之(六)MySQL数据管理策略之分区分库分表

MySQL分而治之-分区和分库分表

分区

如何分区

范围分区

列表分区

哈希分区

按键分区

子分区

分区的管理

范围分区管理

列表分区管理

哈希分区管理

按键分区管理

子分区管理

MySQL分库分表

MyCAT

MyCAT的安装

相关配置文件

MyCAT实现读写分离

配置后端数据源

配置集群和逻辑库

MyCAT实现分库分表

常用管理命令

MyCAT分表环境准备

配置全局表

配置分片表

配置ER表

DBLE分区和分库分表

DBLE部署

DBLE实现分库分表

分库分表准备

枚举算法分片举例

分库分表之后注意的问题

MyCAT的一些注意事项

DBLE的一些注意事项

分库分表之后其它的一些问题

DBA合理的管理内存和磁盘

MySQL体系结构

缓冲区

InnoDB缓存池相关配置

查看缓冲池的状态

更改缓存（change buffer）的意义

关于change buffer的一些问题

change buffer的配置

查看change buffer的状态

自适应哈希索引

自适应哈希索引的管理

日志缓冲区及其作用

日志缓冲区的相关配置

系统表空间

独立表空间

独立表空间配置

通过独立表空间导入其他实例的数据

通用表空间

通用表空间的使用

在几种表空间中移动表

通用表空间维护

临时表空间

会话临时表空间

全局临时表空间

双写缓冲区 Doublewrite Buffer

Doublewrite Buffer的相关配置

关于Doublewrite Buffer的一些问题

猜你喜欢

热点阅读