尝试下MySQL水平分区

最新推荐文章于 2024-06-12 13:27:20 发布
最新推荐文章于 2024-06-12 13:27:20 发布
阅读量475 收藏
点赞数
分类专栏： mysql 文章标签： mysql date null merge less insert
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yfgcq/article/details/7620264
版权
看了看MySQL 5.1的新特性就特别想尝试下这个水平分区，Merge存储引擎同样可以将不同的MyISAM表集合起来形成一个逻辑上的大表，并且可以通过将里面的MyISAM表放置到不同的磁盘上来提高性能，但是Merge引擎在某些方面非常缺乏灵活性，比如往Merge表中插入纪录时，只有两种选择，由INSERT_METHOD控制，要么插入到第一张表中（FIRST），要么插入到最后一张表中（LAST）。水平分区可以提供极大的灵活性，它提供几种划分数据的模式，插入纪录时，根据模式判断一下，记录就会被乖巧地插入到它应该存在的表中。
        下面这个例子展示了水平分区的简单使用，分区使用的是range划分模式。首先使用下面这句SQL语句将表建好（不知道InnoDB引擎支不支持水平分区，照理说InnoDB引擎同样会受大文件的影响，也有分区的需求，使用下面的SQL语句，但将引擎改成InnoDB不会报错，本来也想测测其效率的，结果发现一百万行数据对InnoDB来说太痛苦了，用了一刻多钟还没好，要崩溃了，估计过程内部执行出错了，不想管了，MyISAM比起InnoDB确实足够快速）。
create table part_tab(
a int default null,
b varchar(30) default null,
c date default null
) engine=myisam
partition by range(year(c))
(
partition p0 values less than(1980), /*闷骚的70后*/
partition p1 values less than(1990), /*辛苦的80后*/
partition p2 values less than(2000), /*可爱的90后*/
partition p3 values less than(2010), /*传说中的00后*/
partition p4 values less than maxvalue /*即将到来的一代*/
);

"show tables"只显示有一个表，这也是分区同Merge表不同之处
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| part_tab       | 
+----------------+
1 row in set (0.00 sec)

可以看到数据库目录下生成了一对数据文件和索引文件，但是只有一个frm文件。此时可以看到所有的.MYI文件的大小均为1k，而数据文件大小都为0。
mysql> system ls -lh
总用量 40K
-rw-rw---- 1 mysql mysql   65 2009-08-17 21:19 db.opt
-rw-rw---- 1 mysql mysql 8.5K 2009-08-17 23:31 part_tab.frm
-rw-rw---- 1 mysql mysql   40 2009-08-17 23:31 part_tab.par
-rw-rw---- 1 mysql mysql    0 2009-08-17 23:31 part_tab#P#p0.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-17 23:31 part_tab#P#p0.MYI
-rw-rw---- 1 mysql mysql    0 2009-08-17 23:31 part_tab#P#p1.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-17 23:31 part_tab#P#p1.MYI
-rw-rw---- 1 mysql mysql    0 2009-08-17 23:31 part_tab#P#p2.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-17 23:31 part_tab#P#p2.MYI
-rw-rw---- 1 mysql mysql    0 2009-08-17 23:31 part_tab#P#p3.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-17 23:31 part_tab#P#p3.MYI
-rw-rw---- 1 mysql mysql    0 2009-08-17 23:31 part_tab#P#p4.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-17 23:31 part_tab#P#p4.MYI

       使用下面这个过程使劲往表中插入数据，"ROUND( ( rand(v) * 50 ) * 365 ) ) );"用于产生"0-40"年的总天数，执行完这个过程之后，各数据文件的大小差不多大小。记得不要插入过多的数据，否则会等待很久，这儿只插入了一百万个记录，网上的例子插入了八百万行记录，太疯狂了。
delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 1000000
do
insert into part_tab
values (v,'testing partitions',adddate('1970-01-01', ROUND( ( rand(v) * 50 ) * 365 ) ) );

set v = v + 1;
end while;
end
//
delimiter ;

总耗时：
mysql> call load_part_tab;
Query OK, 1 row affected ( 1 min 2.96 sec)

插入记录之后（没有建索引，所有的.MYI文件大小仍然为1K）：
root@ecy-geek:/var/lib/mysql/test# ls -lh
总用量 31M
-rw-rw---- 1 mysql mysql   65 2009-08-17 21:19 db.opt
-rw-rw---- 1 mysql mysql 8.5K 2009-08-18 00:17 part_tab.frm
-rw-rw---- 1 mysql mysql   40 2009-08-18 00:17 part_tab.par
-rw-rw---- 1 mysql mysql  6.2M 2009-08-18 00:19 part_tab#P#p0.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:19 part_tab#P#p0.MYI
-rw-rw---- 1 mysql mysql  6.2M 2009-08-18 00:19 part_tab#P#p1.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:19 part_tab#P#p1.MYI
-rw-rw---- 1 mysql mysql  6.2M 2009-08-18 00:19 part_tab#P#p2.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:19 part_tab#P#p2.MYI
-rw-rw---- 1 mysql mysql  6.2M 2009-08-18 00:19 part_tab#P#p3.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:19 part_tab#P#p3.MYI
-rw-rw---- 1 mysql mysql  6.1M 2009-08-18 00:19 part_tab#P#p4.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:19 part_tab#P#p4.MYI

为了比较效率之差，我们再创建一个没有分区的表，如下：
create table no_part_tab(
a int default null,
b varchar(30) default null,
c date default null
)engine=myisam;

       将上面的那个过程名改成"no_part_tab"，"insert into part_tab"改成"insert into load_no_part_tab"，同样插入一百万行记录，当数据都放到一个单一文件时，速度明显较快，往"no_part_tab"中插入一百万行记录耗时不到一分钟。
mysql> delimiter ;
mysql> call load_no_part_tab;
Query OK, 1 row affected (5 5.27 sec)

       数据文件的大小为32M，差不多是上面所有的分区表大小的总和：
-rw-rw---- 1 mysql mysql   31M 2009-08-18 00:28 no_part_tab.MYD
-rw-rw---- 1 mysql mysql 1.0K 2009-08-18 00:28 no_part_tab.MYI

Ok，现在可以比较二者查询的效率之差有多大了。
mysql> select count(*) from part_tab where c > date '1970-01-01' and c < date '1970-12-31';
+----------+
| count(*) |
+----------+
|    19896 | 
+----------+
1 row in set ( 0.21 sec)

mysql> select count(*) from no_part_tab where c > date '1970-01-01' and c < date '1970-12-31';
+----------+
| count(*) |
+----------+
|    19896 | 
+----------+
1 row in set ( 0.85 sec)

mysql> select count(*) from part_tab where c > date '1970-01-01' and c < date '1980-12-31';
+----------+
| count(*) |
+----------+
|   220035 | 
+----------+
1 row in set ( 0.35 sec)

mysql> select count(*) from no_part_tab where c > date '1970-01-01' and c < date '1980-12-31';
+----------+
| count(*) |
+----------+
|   220035 | 
+----------+
1 row in set ( 0.78 sec)

mysql> select count(*) from part_tab where c > date '1970-01-01';
+----------+
| count(*) |
+----------+
|   999974 | 
+----------+
1 row in set ( 0.72 sec)

mysql> select count(*) from no_part_tab where c > date '1970-01-01';
+----------+
| count(*) |
+----------+
|   999974 | 
+----------+
1 row in set ( 0.66 sec)


我发现这些数据极具规律性，上面主要做了三次比较：
1、查询70后的总个数，part_tab#P#p0.MYD和no_part_tab.MYD中的数据是没有秩序的，而no_part_tab.MYD大小是part_tab#P#p0.MYD的五倍左右，没有分区耗时0.85s，分区后耗时0.21s，相差4倍多点，为什么不接近5倍呢？我想大概是因为这么多分区多了，定位到一个分区也需要一点点时间吧。
2、第二次查询70后和80后的总数，这次二者相差两倍多点。
3、第三次统计所有的人数，发现未分区的表反而速度要快些，这是因为所有的数据都在一个文件中，当然比数据分散的表查询有利些。但是从另一方面看，分区后对于比这个年份大的表无须做判断直接加上其记录数目即可，而未分区的表由于记录完全没有顺序，对每一条记录都要做判断，效率更低才是，MyISAM的速度太快了，这儿的记录数量还是有点少，可能记录再多一点，分区表数量也多点，比较的结果可能就不一样了。
       对于一个大表做N次水平分区，在没有什么任何索引的情况下，效率大约提高N倍。如果没有任何索引，二者插入数据的效率应该相当，如果有索引，显然往分区后的表插入数据要快，因为它的索引要小，每个小索引只索引本分区的记录，更新起来要快。网上那个经典的例子分了10个表，往里面插入八百万行记录后，二者效率相差八九倍，也符合上面的推理。使用explain分析没有看到分区后同没有分区有什么区别来，查询的行数都一模一样，网上的例子则显示分区后只查询应该查询的行数，比如统计所有70后的数量，只会查询大约二十五万行。

mysql> explain select count(*) from part_tab where c > date '1970-01-01' and c < date '1980-12-31' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows:  1000000
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select count(*) from no_part_tab where c > date '1970-01-01' and c < date '1980-12-31' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: no_part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows:  1000000
        Extra: Using where
1 row in set (0.00 sec)

         这儿只是简单地演示了一下水平分区的使用，实际应用中水平分区比这要强大灵活得多，建表时通过"DA TA DIRECTORY"和"INDEX DIRECTORY"可以将不同的分区放到不同的硬盘上，这样可以极大提高数据库的效率。Merge表可以将内含的MyISAM表放到不同的硬盘上，但是它不能只能地将数据插入到数据应该存放的表中，这样就不利于数据的分布存放了。
       "http://hi.baidu.com/jackli00/blog/item/e60371a616a0c09cd14358a1.html"上面有更详细的测试，作者在分区表上为分区表上分别建立索引，然后判断二者的效率，然后对Range,List,Hash,key等几种划分模式有比较详细的说明，值得一看 ^_^
yfgcq
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql垂直分区和水平分区
Tianqinse的博客
06-23 408
它是一种物理数据库设计技术，MySQL数据库默认使用水平分区。 2.1 水平分区：对表的行进行分区，不同分组中物理分隔的数据组合在一起，表中的所有列都可以在每个分区找到，维持了表的属性结构。 2.2 垂直分区：把某些特定的列划分到特定的分区，减少表的宽度，每个分区都保存了其中列所在的行。 分区模式： range：按某个或某些列的范围划分，如年份 hash：根据某些列的hash值进行分区划分，比如海量数据的去重聚合 key：也是hash，不过使用系统的hash函数 list：自定义某列的属性值范围，落在这些值
Mysql初学入门必知必会.zip
06-10
学习MySQL数据库对于初学者来说是一个很好的起点，尤其是在数据分析、软件开发、网站...高级特性：研究存储过程、触发器、视图、分区等MySQL的高级功能。 数据库设计：学习ER模型、规范化理论，提高数据库设计水平。
MySQL 水平分区
章全蛋的博客
06-26 900
分区概念 分区是把表分成几个小部分，即对表的行进行划分。 分区的优点 可以提高数据库的性能； 对大表(行较多)的维护更快、更容易，因为数据分布在不同的逻辑文件上； 删除分区或它的数据是容易的，因为它不影响其他表。 分区类型 分区具有如下4种类型： Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；日期在201906到201907之间； Li...
深入理解MySQL分区技术
最新发布
weixin_40381772的博客
06-12 1187
MySQL的分区技术为处理大规模数据提供了强大的工具。通过合理的分区策略，我们可以显著提升数据库的查询性能和管理效率。然而，分区并非万能，它需要根据具体的数据特性和应用需求来设计。理解和掌握分区的原理和类型，以及如何管理和优化分区，对于任何希望提高数据库性能的数据库管理员来说都是至关重要的。
基于MySQL 水平分区的优化示例
weixin_34355559的博客
07-24 76
我们知道，MYSQL 5.1开始支持水平分区功能。 我们来尝试下如何在已经分区的表上面做查询优化。总体来说，优化的原则和对单独的表做优化是一样的，保证对磁盘上表的扫描次数减小。我们的表结构如下：这里已经插入2W多行数据进行测试。看看这条查询。SELECT * FROM t1 WHERE system_type IN (1,2)UNION ALLSELECT * FROM t1 WHERE syst...
MySQL水平分区表实际操作总结
上帝有好生之德
05-24 168
  本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作. 在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml   MySQL分区表操作代码 (本案例按月分区): ...
mysql水平分区方案_mysql垂直分区和水平分区
weixin_39864261的博客
01-26 126
数据库扩展大概分为以下几个步骤：1、读写分离：当数据库访问量还不是很大的时候，我们可以适当增加服务器，数据库主从复制的方式将读写分离；2、垂直分区：当写入操作一旦增加的时候，那么主从数据库将花更多的时间的放在数据同步上，这个时候服务器也是不堪重负的；那么就有了数据的垂直分区，数据的垂直分区思路是将写入操作比较频繁的数据表，如用户表_user,或者订单表_orders,那么我们就可以把这个两个表分离...
MYSQL学习资源哈哈哈哈哈哈
04-01
同时，参与社区讨论，阅读官方文档，跟踪MySQL的新特性和更新，都能持续提升你的技能水平。 总之，MySQL的学习是一个逐步深入的过程，从基础操作到高级特性，再到最佳实践，都需要时间和努力去掌握。通过《MySQL...
MySQL常用面试题（附答案）
11-19
- 可尝试拆分为多个单独的查询，或使用子查询。 - 使用 UNION ALL 或 UNION 可能更高效。 14. **事务**： - 一组操作的原子单元，要么全部成功，要么全部失败。 - 特性：原子性、一致性、隔离性、持久性。 15....
解决MySql大数据Like查询慢的问题
12-27
6. **分表或分区**：对于超大型表，可以考虑水平分割（分表）或垂直分割（分区），将数据分散到多个较小的表中，减轻单个表的压力。 7. **JAVA模拟LIKE**：如果在JAVA应用中遇到这个问题，可以使用JAVA代码实现...
MySQL性能优化的21个最佳实践.zip
08-25
17. **使用分区表**：对于大数据量的表，按时间、范围或哈希值分区可以提高查询效率。 18. **避免SQL注入**：使用参数化查询或预编译语句，确保应用安全。 19. **定期清理和维护**：定期删除无用的数据，重建索引...
mysql水平分区
weixin_30421809的博客
08-27 151
解决问题：单表数据量过大 ALTER TABLE boc_url_log PARTITION BY RANGE (ulid) ( PARTITION log_1 VALUES LESS THAN (1000000) ENGINE = MyISAM, PARTITION log_2 VALUES LESS THAN (2000000) ENGINE = MyISAM, PART...
MySQL 分区的目的_MySQL数据库分区的概念与2大好处
weixin_28720767的博客
01-19 552
我们大家都知道通过MySQL数据库分区(Partition)可以提升MySQL数据库的性能，那么到底什么是MySQL数据库分区呢？以及其实际应用的好处的表现有哪些呢？以下的文章就是对这些内容的描述。什么是数据库分区？数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。 分区主要...
mysql 自动水平分区_【原创】基于MySQL 水平分区的优化示例
weixin_39668470的博客
01-19 74
我们知道，MYSQL 5.1开始支持水平分区功能。 我们来尝试下如何在已经分区的表上面做查询优化。总体来说，优化的原则和对单独的表做优化是一样的，保证对磁盘上表的扫描次数减小。我们的表结构如下：这里已经插入2W多行数据进行测试。看看这条查询。SELECT * FROM t1 WHERE system_type IN (1,2)UNION ALLSELECT * FROM t1 WHERE syst...
mysql水平分表方案 mysql水平分表查询方法
JmuziA的博客
04-09 1586
原文地址：mysql水平分表方案 mysql水平分表查询方法。
MYSQL之水平分区----MySQL partition分区I（5.1）
weixin_30538029的博客
02-13 82
一、 分区的概念 二、 为什么使用分区？（优点） 三、 分区类型 四、 子分区 五、 对分区进行修改（增加、删除、分解、合并） 六、 不同引擎的分区特性 七、 分区的限制性 分区概念 分区针对不同的数据库，具有不同的特性。在这里专门针对MySQL...
mysql水平分区插件_【原创】基于MySQL 水平分区的优化示例
weixin_35744893的博客
01-21 167
我们知道，MYSQL 5.1开始支持水平分区功能。 我们来尝试下如何在已经分区的表上面做查询优化。总体来说，优化的原则和对单独的表做优化是一样的，保证对磁盘上表的扫描次数减小。 我们的表结构如下： 这里已经插入2W多行数据进行测试。 看看这条查询。 SELECT * FROM t1 WHERE system_type IN (1,2)UNION ALLSELECT * FROM t1 ...
MYSQL--表分区、查看分区(转)
weixin_58286934的博客
01-05 4697
按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。
Mysql 5.5+ 分区操作
xinaml的博客
09-03 413
查询表分区信息： SELECT partition_name as part, partition_expression as expr, partition_description as descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() A...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值