Mysql 架构及优化之-表分区

写在前面

单张表超过1000W行已经算作是大数据存储场景
常规海量数据优化：大表拆小表、sql语句优化
今天我们重点介绍大表拆小表的优化

拆表方式

• 水平拆表

将表user中的1000w行数据拆成user1表和user2表，每张表500w行数据
但是这样做法就是导致sql语句需要更改为 select user1,user2 ...
拆的越多,sql语句越长,所以不推荐此法拆表

• 垂直分表

如将user表100个字段拆成表user_base(30字段)、表user_extend(20字段)

sql语句优化成 ... user_base left join user_extend on user_base.id = user_extend.id..

• 表分区

对行水平进行分表,物理存储上分区存储,每个分表有独立的文件,应用程序上还是一张表
Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的
数据区域进行分区。例如可以建立一个对表主键进行分区的表。
Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。

基于range分区

基于给定连续的区间的值对行进行分区

• 新建表user 基于salary区间进行表分区

以字段salary为准 按照区间 [0,1000] [1000,3000] [3000,..] 将表分三个区

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      `salary` int(11) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`,`salary`), #如果不给salary加主键会报错
      KEY `index_name` (`name`) USING BTREE
    )partition by range(salary)(
     partition s1000 values less than (1000),  #小于1000一张表
     partition s2000 values less than (3000),  #大于1000小于3000一张表
     partition s3000 values less than maxvalue #大于3000一张表
    );

对已有的表创建分区

    alter table user partition by range(salary)(
     partition s1000 values less than (1000),  
     partition s2000 values less than (3000),   
     partition s3000 values less than maxvalue  
    );

• 可能遇到的错误

这里的提示已经很明确了,分区的列必须是个主键列

所以我们给salary添加主键

• 查看现在数据库文件

[root@localhost smudge]# cd /usr/local/mysql/var

• 插入数据测试

可以插入更多的数据 观察 分区文件的大小
使用 watch -n1 ls -lh 每秒监测文件大小的变化

基于list分区

• 分区语句

以area_id列为准, 按照华南和华北 将表分成两个区

   create table shop(name varchar(50),area_id int)
        engine=myisam default charset=utf8
         partition by list(area_id)(
         partition pnorth values in (1,3,5),
         partition psouth values in (20,36,55)
   )

• 表文件

基于hash分区

常用于对主键的快速分区

• 分区语句

以主键id为准,hash算法将表平均分成4个区

     create table student (
      id int(11) not null auto_increment,
      name varchar(50) default null,
      age int(11) default null,
      primary key (id)
     ) engine=myisam default charset=utf8
     partition by hash(id) partitions 4;

• 表文件

基于key分区

• 建表分区

和hash分区类似将表分成4个区

    create table teacher (
      id int(11) not null auto_increment,
      name varchar(50) default null,
      age int(11) default null,
      primary key (id)
    ) engine=myisam default charset=utf8
     partition by key(id) partitions 4;

• 添加分区

alter table teacher partition by key(id) partitions 4;

• 表文件

对于Innodb引擎表的分区

• Innodb表默认是共享存储空间

默认my.cnf文件

默认情况下Innodb是使用的共享表空间

当在库smudge中, 新建一张Innodb引擎的shop表

cd usr/local/mysql/var/smudge

只有一个文件 shop.frm

shop表的索引和数据都存在ibdata1文件中

共享存储空间的Innodb不可以分区！

所以我们要将Innodb表设置成独立表空间
索引和数据都存放在ibd文件中

• 设置成独立表空间

添加一行 innodb_file_per_table

service mysql restart 重启mysql服务

• 添加表分区

 alter table shop partition by hash(id) partitions 4 ;

• 查看表文件

可见分区成功啦~~~~