blog | percy

life

0%

mysql 分区分表分库入门

发表于 更新于 分类于

[TOC]

以下无特殊说明，都是 mysql InnoDB 引擎下讨论。

水平和垂直的概念

水平：同一张表的不同行记录分配到不同的物理文件中

垂直：同一张表的不同列记录分配到不同的物理文件中

分区概述

1
2
# 查看是否开启了分区
show variables like '%partition%';

range 分区

MySQL 在 5.1 支持了分区，且分区不是在存储引擎层完成的。分区就是将一个表或者索引拆分为多个文件，将一个文件分解为几个物理分区的文件。MySQL 只支持水平分区，不支持垂直分区。MySQL 是局部分区，即一个分区中即存放了数据，也存放了索引。而全局分区是指，数据存放在各个分区中，所有数据的索引存在在同一个文件中。

range分区

1
2
3
4
5
6
7
8
9
10
11
12
use mydb;
drop table mytable;
create table mytable(id int) engine=innodb partition by range(id)(partition p0 values less than(10), partition p1 values less than(20));

insert into mytable values(1);
insert into mytable values(11);
# 这里执行会报错
insert into mytable values(30); 

# 注意看两个的分区不同
explain partitions select * from mytable where id=1;
explain partitions select * from mytable where id<30;

执行结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE mytable p0 ALL NULL NULL NULL NULL 1 100.00 Using where
2 SIMPLE mytable p0,p1 ALL NULL NULL NULL NULL 2 50.00 Using where

启用分区后，表不再是由一个ibd文件组成，而是由建立分区时的各个分区ibd文件。

list 分区

hash 分区

分库分表概述

数据库的瓶颈

IO 瓶颈

  • 磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。
  • 网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

CPU瓶颈

  • SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。
  • 单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。

水平分库

以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

  • 场景
    • 系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

水平分表

以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

  • 场景
    • 系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。

垂直分库

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

  • 场景
    • 系统绝对并发量上来了，并且可以抽象出单独的业务模块。4.分析：到这一步，基本上就可以服务化了。

垂直分表

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

  • 场景
    • 系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。

参考

分库分表

《MySQL技术内幕(InnoDB存储引擎)第2版》