脚本宝典收集整理的这篇文章主要介绍了

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       );
  • 可能遇到的错误

clipboard.png

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

所以我们给salary添加主键

  • 查看现在数据库文件

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

clipboard.png

  • 插入数据测试

Mysql 架构及优化之-表分区-脚本宝典
clipboard.png

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

clipboard.png

基于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)    )
  • 表文件

clipboard.png

基于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;
  • 表文件

clipboard.png

基于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;
  • 表文件

clipboard.png

对于Innodb引擎表的分区

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

默认my.cnf文件

clipboard.png

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

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

cd usr/local/mysql/var/smudge 

clipboard.png

只有一个文件 shop.frm

clipboard.png

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

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

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

  • 设置成独立表空间

添加一行 innodb_file_per_table

clipboard.png

service mysql restart 重启mysql服务

  • 添加表分区

 alter table shop partition by hash(id) partitions 4 ;
  • 查看表文件

clipboard.png

可见分区成功啦~~~~

总结

以上是脚本宝典为你收集整理的

Mysql 架构及优化之-表分区

全部内容,希望文章能够帮你解决

Mysql 架构及优化之-表分区

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过