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

分区

• 分区的优势

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

• 分区的使用场景

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

• 常用分区类型

• 分区注意事项

1. 分区表的限制

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

2. 分区键的选择
3. 分区数量的选择

如何分区

范围分区

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

# 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分库分表

• 分库分表的场景

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

• 拆分模式之垂直拆分

  
  
  拆分模式之垂直拆分.png

• 常用分片策略

1. 取模分片
2. 范围分片

• 分库分表工具

• 分库分表后面临的问题

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

MyCAT

• MyCAT的作用

1. 读写分离
2. 分库分表
3. 故障切换

• 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

相关配置文件

• 服务相关配置

cd /usr/local/mycat/conf/
server.json

• 用户相关配置

/usr/local/mycat/conf/users/root.user.json
# 动态调整事务类型 在mycat里
set transaction_policy='xa';
# 查看事务类型
select @@@transaction_policy;

• 数据源相关配置

vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json

• 逻辑库表相关配置

vim /usr/local/mycat/conf/schemas/mysql.schema.json

• 集群相关配置文件

vim /usr/local/mycat/conf/clusters/prototype.cluster.json

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的一些注意事项

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

DBLE的一些注意事项

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

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

1. 分布式事务
2. 全局主键ID
3. SQL优化的问题
4. 监控
5. 扩容问题

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在什么时候合并？

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

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

3. 在崩溃恢复期间执行合并

• 什么时候应该使用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%";

1. innodb_flush_log_at_timeout = 1

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

2. 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数据目录下的库目录下会创建每一张表的数据文件

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

• 前提条件

1. 必须开启独立表空间
2. 表空间的大小必须与目标实例的表空间一样
3. MySQL版本一样
4. 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;

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

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

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

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

通用表空间

• 通用表空间的作用

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

• 限制

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

通用表空间的使用

• 在数据目录中创建通用表空间

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';

临时表空间

会话临时表空间

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

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

• 会话临时表空间的池

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页是连续的，这个过程是顺序写的，开销并不是很大。