【Mysql调优】6、分区表和分库分表概念介绍

1、分区表

1）什么是分区表？

分区表：把大表按条件单独存储到多个不同的“物理小表”中，再构建出的完整“逻辑表”。

查询是先查询逻辑表，快速过滤出数据在那张表，然后查询指定的物理表即可。

2）分区表的优点

• 更少的数据检索范围
• 拆分超级大的表，可以将部分数据加载到内存当中（常见的就是将最近的数据加载到mysql缓存中）
• 分区表更容易维护
• 分区表的数据文件可以分布在不同的硬盘上，提高并发IO
• 减少锁的范围，避免大表锁表，避免影响大范围数据暂时不能够使用
• 可独立备份，恢复分区数据

3）案例

（1）分区前的查询

# 创建数据库表
create table test_partition(
    id int(11) not null ,
    create_time datetime not null ,
    cyear int,
    primary key (id,create_time,cyear)
)engine = innodb default character set = utf8;

# 数据准备
insert into test_partition values (1,"20130722000000",2013);
insert into test_partition values (1,"20140722000000",2014);
insert into test_partition values (1,"20150722000000",2015);
insert into test_partition values (1,"20160722000000",2016);
insert into test_partition values (1,"20170722000000",2017);
insert into test_partition values (1,"20180722000000",2018);
insert into test_partition values (1,"20190722000000",2019);
insert into test_partition values (1,"20200722000000",2020);
insert into test_partition values (1,"20210722000000",2021);
insert into test_partition values (1,"20220722000000",2022);

• 假设数据量按照上述年份，每一年的数据都有很多，这时后我们执行sqlselect * from test_partition where cyear > 2016;，此时对这条SQL进行优化，大多数人想的是在cyear字段上加索引；
• 但是explain的进行执行计划的解析后会发现，即使cyear上存在索引，但是还是进行了全表扫描，这是为什么呢？
• 前边的章节里提到过【查询优化器的优化选择性问题】，我们的目标结果集数量太大，占据表中数据的一半还要多，查询优化器觉得使用索引还不如全表扫描（一般来说结果集不能超过表数据的25%）的速度快，所以没有使用索引。

（2）分区后的查询

# 创建分区表，使用cyear作为范围划分，将2014年前的数据放在一个ibd物理数据文件中，2020年之前的数据每年都单独放在一个ibd物理
# 数据文件中，2020年之后的数据放在一个ibd物理数据文件中
create table test_partition(
    id int(11) not null ,
    create_time datetime not null ,
    cyear int,
    primary key (id,create_time,cyear)
)engine = innodb default character set = utf8
partition by range (cyear)
(
partition y14before values less than (2014),
partition y14_15 values less than (2015),
partition y15_16 values less than (2016),
partition y16_17 values less than (2017),
partition y17_18 values less than (2018),
partition y18_19 values less than (2019),
partition y19_20 values less than (2020),
partition y20after values less than maxvalue engine = innodb);

# 数据准备
insert into test_partition values (1,"20130722000000",2013);
insert into test_partition values (1,"20140722000000",2014);
insert into test_partition values (1,"20150722000000",2015);
insert into test_partition values (1,"20160722000000",2016);
insert into test_partition values (1,"20170722000000",2017);
insert into test_partition values (1,"20180722000000",2018);
insert into test_partition values (1,"20190722000000",2019);
insert into test_partition values (1,"20200722000000",2020);
insert into test_partition values (1,"20210722000000",2021);
insert into test_partition values (1,"20220722000000",2022);

在数据插入分区表之后，我们就能通过ibd数据文件看到显著的变化：

然后对查询SQLselect * from test_partition where cyear > 2016;进行explain执行计划的分析，可以看到，使用到了索引：

（3）查询分区表数据情况

select PARTITION_NAME as "分区", TABLE_ROWS as "行数"
from information_schema.PARTITIONS
where TABLE_SCHEMA = "testdb"
  and TABLE_NAME = "test_partition";

（4）分区表的使用限制

分区

1. 查询必须包含分区列，分区列不允许进行计算（如果进行计算，索引可以继续使用，但是扫描的是全部的分区表）
2. 分区列必须是数字类型（分区表建立时数据筛选条件需要使用 less than 进行数字运算）
3. 分区表不支持建立外键索引*（MYSQL强制）
4. 建表时必须包含所有的列（MYSQL强制）
5. 分区表最多只能分1024个列（MYSQL强制）

2、分库分表

分库：将数据进行切分，将一个数据库拆分为多个数据库，将多个数据库分别放在不同的MySQL服务器当中。
分库的缺点：数据分布不均匀，仍然不能解决海量数据的问题。

分表：将一张数据库数据量很大的大表按照一定的规则去分门别类地进行拆分，存储在不同服务器相同类型数据库相同表结构的表中。
分表的缺点：操作数据库的时候需要写不同的SQL，造成编码问题，为了解决这种问题，就衍生出来了分库分表框架/中间件。

3、分库分表框架/中间件