【mysql】表分区(partition)功能实验1

最新推荐文章于 2021-11-19 19:24:01 发布
最新推荐文章于 2021-11-19 19:24:01 发布
阅读量2k 收藏
点赞数
分类专栏： 技术 文章标签： mysql less null duplicates warnings character
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lapiggy/article/details/6409961
版权

mysql的表分区是5.1版本的新功能，可以提高数据库查询性能。分区的实质是按照一定规则，将数据存放在不同的文件中，这些文件也可以存放在不同的磁盘上。分区的优点：

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

 

 实验1

1、建表

 

建立分区表：

CREATE TABLE `T_TRANS_INFO_PART` (
  `MID` varchar(32) NOT NULL DEFAULT '',
  `SCHOOLNO` char(5) NOT NULL DEFAULT '',
  `MOBILENO` char(12) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '???',
  `CARDNO` char(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??',
  `TRANSTYPE` char(2) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '?????01??? 02???',
  `AMOUNT` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `TRANSTIME` char(20) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '?????yyyy-MMdd HH:mm:ss',
  `INTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',
  `MODTIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '????',
  `RESERVED1` varchar(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??1',
  `RESERVED2` varchar(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??2',
  `ORGANIZATIONNO` char(4) NOT NULL DEFAULT '' COMMENT '......',
  `TRANSDATE` int NOT NULL DEFAULT 0 COMMENT '交易日期',
  PRIMARY KEY (`MID`,`SCHOOLNO`,`TRANSDATE`),
  KEY `IDX_AMOUNT` (`AMOUNT`),
  KEY `IDX_TRANSTYPE` (`TRANSTYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
PARTITION BY RANGE(TRANSDATE)
(PARTITION p01 VALUES LESS THAN  (20100500),
PARTITION p02 VALUES LESS THAN  (20100600),
PARTITION p03 VALUES LESS THAN  (20100700),
PARTITION p04 VALUES LESS THAN  (20100800),
PARTITION p05 VALUES LESS THAN  (20100900),
PARTITION p06 VALUES LESS THAN  (20101000),
PARTITION p07 VALUES LESS THAN  (20101100),
PARTITION p08 VALUES LESS THAN  (20101200),
PARTITION p09 VALUES LESS THAN  (20110100),
PARTITION p10 VALUES LESS THAN  (20110200),
PARTITION p11 VALUES LESS THAN  (20110300),
PARTITION p12 VALUES LESS THAN  (20110400),
PARTITION p13 VALUES LESS THAN  (20110500),
PARTITION p14 VALUES LESS THAN MAXVALUE)

 

建立未分区表：

CREATE TABLE `T_TRANS_INFO_NOPART` (
  `MID` varchar(32) NOT NULL DEFAULT '',
  `SCHOOLNO` char(5) NOT NULL DEFAULT '',
  `MOBILENO` char(12) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '???',
  `CARDNO` char(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??',
  `TRANSTYPE` char(2) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '?????01??? 02???',
  `AMOUNT` int(11) NOT NULL DEFAULT '0' COMMENT '????',
  `TRANSTIME` char(20) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '?????yyyy-MMdd HH:mm:ss',
  `INTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',
  `MODTIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '????',
  `RESERVED1` varchar(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??1',
  `RESERVED2` varchar(32) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT '??2',
  `ORGANIZATIONNO` char(4) NOT NULL DEFAULT '' COMMENT '......',
  `TRANSDATE` int NOT NULL DEFAULT 0 COMMENT '交易日期',
  PRIMARY KEY (`MID`,`SCHOOLNO`,`TRANSDATE`),
  KEY `IDX_AMOUNT` (`AMOUNT`),
  KEY `IDX_TRANSTYPE` (`TRANSTYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

 

 

2、准备数据：

 

向未分区表中灌入5200000条数据：

mysql> insert into t_trans_info_nopart select * from t_trans_info limit 0,5200000;

Query OK, 5200000rows affected, 1 warning (23 min 53.31 sec)
Records: 5200000Duplicates: 0  Warnings: 0

向分区表中灌入相同的数据：

mysql> insert into t_trans_info_part select * from t_trans_info_nopart;

Query OK, 5200000 rows affected, 1 warning (55 min 42.48 sec)
Records: 5200000  Duplicates: 0  Warnings: 0

 

3、测试：

mysql> select count(*) from t_trans_info_nopart;
+----------+
| count(*) |
+----------+
|  5200000 |
+----------+
1 row in set (2.19 sec)

mysql> select count(*) from t_trans_info_part;
+----------+
| count(*) |
+----------+
|  5200000 |
+----------+
1 row in set (7.35 sec)

 

mysql> select count(*) from t_trans_info_nopart where transdate<=20110501 and transdate>=20100930;
+----------+
| count(*) |
+----------+
|  4813813 |
+----------+
1 row in set (3.56 sec)

mysql> select count(*) from t_trans_info_part where transdate<=20110501 and transdate>=20100930;
+----------+
| count(*) |
+----------+
|  4813813 |
+----------+
1 row in set (8.08 sec)

 

结果是分区比未分区还慢，暂时不明原因？？？？？？？？？？？？？？？？？？？？？？？？

 

4、建立MyISAM的表再测试一下：

mysql> CREATE TABLE `T_TRANS_INFO_NOPART_MyISAM` (
    ->   `MID` varchar(32) NOT NULL DEFAULT '',
    ->   `SCHOOLNO` char(5) NOT NULL DEFAULT '',
    ->   `MOBILENO` char(12) NOT NULL DEFAULT '' COMMENT '手机号',
    ->   `CARDNO` char(32) NOT NULL DEFAULT '' COMMENT '卡号',
    ->   `TRANSTYPE` char(2) NOT NULL DEFAULT '' COMMENT '交易类型',
    ->   `AMOUNT` int(11) NOT NULL DEFAULT 0 COMMENT '金额',
    ->   `TRANSTIME` char(20) NOT NULL DEFAULT '' COMMENT '交易时间，格式yyyy-MMdd HH:mm:ss',
    ->   `INTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
    ->   `MODTIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
    ->   `RESERVED1` varchar(32) NOT NULL DEFAULT '' COMMENT '扩展字段1',
    ->   `RESERVED2` varchar(32) NOT NULL DEFAULT '' COMMENT '扩展字段2',
    ->   `ORGANIZATIONNO` char(4) NOT NULL DEFAULT '' COMMENT '组织机构编号',
    ->   `TRANSDATE` int NOT NULL DEFAULT 0 COMMENT '交易日期',
    ->   PRIMARY KEY (`MID`,`SCHOOLNO`,`TRANSDATE`),
    ->   KEY `IDX_AMOUNT` (`AMOUNT`),
    ->   KEY `IDX_TRANSTYPE` (`TRANSTYPE`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.03 sec)

 

mysql> CREATE TABLE `T_TRANS_INFO_PART_MyISAM` (
    ->   `MID` varchar(32) NOT NULL DEFAULT '',
    ->   `SCHOOLNO` char(5) NOT NULL DEFAULT '',
    ->   `MOBILENO` char(12) NOT NULL DEFAULT '' COMMENT '手机号',
    ->   `CARDNO` char(32) NOT NULL DEFAULT '' COMMENT '卡号',
    ->   `TRANSTYPE` char(2) NOT NULL DEFAULT '' COMMENT '交易类型',
    ->   `AMOUNT` int(11) NOT NULL DEFAULT 0 COMMENT '金额',
    ->   `TRANSTIME` char(20) NOT NULL DEFAULT '' COMMENT '交易时间，格式yyyy-MMdd HH:mm:ss',
    ->   `INTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
    ->   `MODTIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
    ->   `RESERVED1` varchar(32) NOT NULL DEFAULT '' COMMENT '扩展字段1',
    ->   `RESERVED2` varchar(32) NOT NULL DEFAULT '' COMMENT '扩展字段2',
    ->   `ORGANIZATIONNO` char(4) NOT NULL DEFAULT '' COMMENT '组织机构编号',
    ->   `TRANSDATE` int NOT NULL DEFAULT 0 COMMENT '交易日期',
    ->   PRIMARY KEY (`MID`,`SCHOOLNO`,`TRANSDATE`),
    ->   KEY `IDX_AMOUNT` (`AMOUNT`),
    ->   KEY `IDX_TRANSTYPE` (`TRANSTYPE`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=gbk
    -> PARTITION BY RANGE(TRANSDATE)
    -> (PARTITION p01 VALUES LESS THAN  (20100800),
    -> PARTITION p02 VALUES LESS THAN  (20100900),
    -> PARTITION p03 VALUES LESS THAN  (20101000),
    -> PARTITION p04 VALUES LESS THAN  (20101100),
    -> PARTITION p05 VALUES LESS THAN  (20101200),
    -> PARTITION p06 VALUES LESS THAN  (20110100),
    -> PARTITION p07 VALUES LESS THAN  (20110200),
    -> PARTITION p08 VALUES LESS THAN  (20110300),
    -> PARTITION p09 VALUES LESS THAN  (20110400),
    -> PARTITION p10 VALUES LESS THAN  (20110500),
    -> PARTITION p11 VALUES LESS THAN  (20110600),
    -> PARTITION p12 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.03 sec)

 

5、入数据：

向未分区表中灌入520万条数据：

mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 100000;
Query OK, 100000 rows affected, 1 warning (3.83 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 100000,900000;
Query OK, 900000 rows affected, 1 warning (1 min 14.30 sec)
Records: 900000  Duplicates: 0  Warnings: 0

mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 1000000,1000000;
Query OK, 1000000 rows affected, 1 warning (1 min 33.20 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 2000000,1000000;
Query OK, 1000000 rows affected, 1 warning (2 min 21.41 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 3000000,1000000;
Query OK, 1000000 rows affected, 1 warning (3 min 42.61 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
mysql> insert into t_trans_info_nopart_myisam select * from t_trans_info_nopart limit 4000000,1000000;
Query OK, 1000000 rows affected, 1 warning (1 min 45.17 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

向分区表中灌入520万条数据：

mysql> insert into t_trans_info_part_myisam select * from t_trans_info_nopart;
Query OK, 5200000 rows affected, 1 warning (52 min 45.17 sec)

Records: 5200000 Duplicates: 0  Warnings: 0

 

6、测试：

mysql> select count(*) from t_trans_info_nopart_myisam where transdate>=20101001 and transdate<=20110330 and schoolno='A0001';
+----------+
| count(*) |
+----------+
|    84662 |
+----------+
1 row in set (5.62 sec)

mysql> select count(*) from t_trans_info_part_myisam where transdate>=20101001 and transdate<=20110330 and schoolno='A0001';
+----------+
| count(*) |
+----------+
|    84662 |
+----------+
1 row in set (7.16 sec)


结果还是一样，这是怎么回事？？？？？？？？？？？？？？？？？？

 

 

 实验2

 

考虑到可能是因为分区不均匀造成的上述结果，下面构造一个各分区数据量相同的分区表进行实验

 

1、建表：

 

create table `t_part_test`(
`id` int NOT NULL default 0,
`detail` char(32) NOT NULL default '',
primary key (id)
)default charset=gbk
PARTITION BY HASH(id) PARTITIONS 10;

 

create table `t_nopart`(
`id` int NOT NULL default 0,
`detail` char(32) NOT NULL default '',
primary key (id)
)default charset=gbk;

2、向两张表内各灌入1000万条数据，完成后文件系统：

 

-rw-rw---- 1 mysql mysql      8590 05-10 10:52 t_nopart.frm
-rw-rw---- 1 mysql mysql 690000000 05-12 14:51 t_nopart.MYD
-rw-rw---- 1 mysql mysql 102624256 05-12 14:51 t_nopart.MYI
-rw-rw---- 1 mysql mysql      8590 05-10 10:52 t_part_test.frm
-rw-rw---- 1 mysql mysql        60 05-10 10:52 t_part_test.par
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p0.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p0.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p1.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p1.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p2.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p2.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p3.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p3.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p4.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p4.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p5.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p5.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p6.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p6.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p7.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p7.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p8.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p8.MYI
-rw-rw---- 1 mysql mysql  69000000 05-12 14:43 t_part_test#P#p9.MYD
-rw-rw---- 1 mysql mysql  10263552 05-12 14:43 t_part_test#P#p9.MYI

 

看出各个分区的数据量相同。

 

3、查询测试：

mysql> select count(*) from t_nopart where id>=3000000 and id<=8099900;
+----------+
| count(*) |
+----------+
|  5099901 |
+----------+
1 row in set (3.00 sec)

mysql> select count(*) from t_part_test where id>=3000000 and id<=8099900;
+----------+
| count(*) |
+----------+
|  5099901 |
+----------+
1 row in set (3.45 sec)

 

增加非关联字段：

mysql> select count(*) from t_nopart where id>=1111111 and id<=6788877 and detail like'%10%';
+----------+
| count(*) |
+----------+
|   273065 |
+----------+
1 row in set (5.22 sec)

mysql> select count(*) from t_part_test where id>=1111111 and id<=6788877 and detail like'%10%';
+----------+
| count(*) |
+----------+
|   273065 |
+----------+
1 row in set (5.76 sec)

结果与实验1相同。

 

分析原因，可能是mysql的处理线程只有1个（分区主要是通过并发来提高性能）？？？？？

 

 

实验3

 

建一个以range方法分区的表，三张表进行比较：

 

1、建表：

 

CREATE TABLE `t_part_test_range` (
  `id` int(11) NOT NULL DEFAULT '0',
  `detail` char(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY RANGE (id)
(PARTITION p01 VALUES LESS THAN  (1000000),
PARTITION p02 VALUES LESS THAN  (2000000),
PARTITION p03 VALUES LESS THAN  (3000000),
PARTITION p04 VALUES LESS THAN  (4000000),
PARTITION p05 VALUES LESS THAN  (5000000),
PARTITION p06 VALUES LESS THAN  (6000000),
PARTITION p07 VALUES LESS THAN  (7000000),
PARTITION p08 VALUES LESS THAN  (8000000),
PARTITION p09 VALUES LESS THAN  (9000000),
PARTITION p10 VALUES LESS THAN MAXVALUE);

2

查询条件为非索引、非分区关联字段的情况：

mysql>  select * from t_nopart where  detail='第5555876条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5555876 | 第5555876条记录 |
+---------+-----------------+
1 row in set (2.94 sec)

mysql>  select * from t_part_test where  detail='第5555876条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5555876 | 第5555876条记录 |
+---------+-----------------+
1 row in set (2.93 sec)

mysql>  select * from t_part_test_range where  detail='第5555876条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5555876 | 第5555876条记录 |
+---------+-----------------+
1 row in set (2.94 sec)

性能基本相同

 

只有分区关联字段的范围的情况：

mysql> select count(*) from t_nopart where id>=2000000 and id<=8000000;
+----------+
| count(*) |
+----------+
|  6000001 |
+----------+
1 row in set (4.09 sec)

mysql> select count(*) from t_part_test where id>=2000000 and id<=8000000;
+----------+
| count(*) |
+----------+
|  6000001 |
+----------+
1 row in set (4.75 sec)
mysql> select count(*) from t_part_test_range where id>=2000000 and id<=8000000;
+----------+
| count(*) |
+----------+
|  6000001 |
+----------+
1 row in set (4.89 sec)
分区表性能低

 

查询条件包括分区字段范围、非索引字段的情况：

mysql> select * from t_nopart where id>=2000000 and id<=8000000 and detail='第5546328条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5546328 | 第5546328条记录 |
+---------+-----------------+
1 row in set (3.03 sec)

mysql> select * from t_part_test where id>=2000000 and id<=8000000 and detail='第5546328条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5546328 | 第5546328条记录 |
+---------+-----------------+
1 row in set (3.10 sec)

mysql> select * from t_part_test_range where id>=2000000 and id<=8000000 and detail='第5546328条记录';
+---------+-----------------+
| id      | detail          |
+---------+-----------------+
| 5546328 | 第5546328条记录 |
+---------+-----------------+
1 row in set (2.17 sec)

 

只有这种情况可以看出：用range分区的表比另两种表快30%左右。

 

 

实验4

 

对没有主键的表做实验： 

 

1、建表：

 

CREATE TABLE `t_nopri_part` (
  `id` int(11) NOT NULL DEFAULT '0',
  `detail` char(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY RANGE (id)
(PARTITION p01 VALUES LESS THAN  (1000000),
PARTITION p02 VALUES LESS THAN  (2000000),
PARTITION p03 VALUES LESS THAN  (3000000),
PARTITION p04 VALUES LESS THAN  (4000000),
PARTITION p05 VALUES LESS THAN  (5000000),
PARTITION p06 VALUES LESS THAN  (6000000),
PARTITION p07 VALUES LESS THAN  (7000000),
PARTITION p08 VALUES LESS THAN  (8000000),
PARTITION p09 VALUES LESS THAN  (9000000),
PARTITION p10 VALUES LESS THAN MAXVALUE);

CREATE TABLE `t_nopri_nopart` (
  `id` int(11) NOT NULL DEFAULT '0',
  `detail` char(32) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

 

2、向两张表中各灌入1000万条数据

 

数据文件：

-rw-rw---- 1 mysql mysql      8590 05-13 14:58 t_nopri_nopart.frm
-rw-rw---- 1 mysql mysql 690000000 05-13 15:04 t_nopri_nopart.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:04 t_nopri_nopart.MYI
-rw-rw---- 1 mysql mysql      8590 05-13 14:58 t_nopri_part.frm
-rw-rw---- 1 mysql mysql        68 05-13 14:58 t_nopri_part.par
-rw-rw---- 1 mysql mysql  68999931 05-13 14:59 t_nopri_part#P#p01.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 14:59 t_nopri_part#P#p01.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:00 t_nopri_part#P#p02.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:00 t_nopri_part#P#p02.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:00 t_nopri_part#P#p03.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:00 t_nopri_part#P#p03.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:01 t_nopri_part#P#p04.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:01 t_nopri_part#P#p04.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:01 t_nopri_part#P#p05.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:01 t_nopri_part#P#p05.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:02 t_nopri_part#P#p06.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:02 t_nopri_part#P#p06.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:02 t_nopri_part#P#p07.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:02 t_nopri_part#P#p07.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:02 t_nopri_part#P#p08.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:02 t_nopri_part#P#p08.MYI
-rw-rw---- 1 mysql mysql  69000000 05-13 15:03 t_nopri_part#P#p09.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:03 t_nopri_part#P#p09.MYI
-rw-rw---- 1 mysql mysql  69000069 05-13 15:03 t_nopri_part#P#p10.MYD
-rw-rw---- 1 mysql mysql      1024 05-13 15:03 t_nopri_part#P#p10.MYI

3、测试结果：

 

mysql> select count(*) from t_nopri_part where id between 2200000 and 8000000;
+----------+
| count(*) |
+----------+
|  5800001 |
+----------+
1 row in set (1.46 sec)

mysql> select count(*) from t_nopri_nopart where id between 2200000 and 8000000;
+----------+
| count(*) |
+----------+
|  5800001 |
+----------+
1 row in set (1.90 sec)

mysql>
mysql> select count(*) from t_nopri_part where id between 2200000 and 8000000 and detail='第5676777条记录';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (2.21 sec)

mysql> select count(*) from t_nopri_nopart where id between 2200000 and 8000000 and detail='第5676777条记录';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (3.00 sec)

 

 此时可以看出，分区比不分区性能高

 

 

总结：

 

1、对于有主键表，分区条件必须包括主键中的字段，但是由于主键本身已经有索引，所以分区的查询优化不明显，甚至更低。所以，mysql(5.1.56，其他版本还不清楚)表分区功能用于无主键的表，效率才比较明显；

2、用between函数查找比用“<”“>”“<=”“>=”的效率要高一些，sql语句应尽量使用函数；

3、用“<”“>”与用“<=”“>=”进行查询，效率基本相同，并不会因为后者有了等值连接而变得更快；

4、

 

lapiggy
关注
专栏目录
mysql 自动创建分区表_Mysql分区表及自动创建分区Partition
weixin_36464343的博客
01-21 1943
Range分区表建表语句如下，其中分区键必须和id构成主键和唯一键CREATE TABLE `test1` (`id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘自增主键(guid)‘,`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间‘,...
创建mysql表分区的方法
12-15
【MySQL 表分区详解】 MySQL 表分区是一种高级的数据组织技术，它允许将大型表分成较小、更易管理和处理的部分。这种技术对于处理海量数据，尤其是超过亿级别的数据集非常有效，可以显著提升查询性能和数据管理效率...
mysql表分区实验总结
weixin_33695450的博客
11-03 209
草稿丢失，发表得不完整，稍后重新整理 mysql表分区技术能有效解决水平拆分和垂直拆分的不足，可操作性和效率都更优。以下是一些实验总结。 InnoDB引擎需先在配置文件中设置：innodb_file_per_table=1 --innodb : 主表.frm 保存表结构和分区数目一致的 .ibd 文件，用于保存数据和索引。 --myisam：主表....
mysql 分区实验
weixin_30254435的博客
09-02 104
一、试验一 1、在test数据库中创建t2表，并向表中插入千万条数据，insert into t2 select * from t2; 2、新开一个终端，进入/usr/local/mysql/data/test 下执行：watch -n1 ls -lh 每一秒刷新一次 3、free -m 查看内存的使用 4、top 查看cpu 二、实验二 ...
[转]MySQL5.1新特性翻译系列 - 通过分区（Partition）提升MySQL性能
威尔软件
07-26 142
来源：http://phpv.net/article.php/1514 MySQL5.1新特性翻译系列 - 通过分区（Partition）提升MySQL性能 几年前，俺写过一篇题为“The Foundation of Excellent Performa...
Mysql优化实验（一）-- 分区
驰骋翱翔
07-16 988
开发项目过程中总是提到优化的概念，本篇文章是对Mysql数据优化实践的一次探索旅程，简要介绍了分区原因，方法，分区表管理方法和一次简单的实践。
MySQL表分区
t0m的专栏
01-03 585
MySQL中当数据量较大时，查询速度会下降比较严重，采用表分区，可以有效的提升查询速度。 关于表分区的优点和缺点网上很多，根据自己需要选择是采用表分区还是分表都可以，还可以使用现在流行的非关系型数据库。 表分区可以在表创建时，也可以在创建后修改为分区表。 创建表时语法如下： create table xxxx ( c1 int, c2 .. ) ..partition by rang
Mysql数据表分区技术PARTITION浅析
12-15
MySQL 数据表分区技术是一种优化大型数据库性能的有效方法，它通过将大表逻辑上分为多个部分，使得查询操作能更高效地处理数据。分区技术在 MySQL 5.1 及以后的版本中得到了支持，提供了四种主要的分区类型：RANGE、...
MySql数据分区操作之新增分区操作
12-15
首先，尝试直接通过`ALTER TABLE`语句添加分区到非分区表会导致错误，MySQL会返回`ERROR 1505 <HY000>: Partition management on a not partitioned table is not possible`。正确的方法是创建一个新的具有相同结构...
mysql 5.1.56 报错--with-partition
vah101的专栏
04-27 2052
<br />使用源码包安装了一个mysql，当向其中导入数据库脚本时，报错提示需要用的分区表功能，要用--with-partition选项重新编译mysql。于是找来源码，重新./configure，并且加上了参数--with-partition，然后这时提示该参数不能被识别，这很让人郁闷。仔细看configure的帮助，发现其中有一条--with-plugins，然后这条选项可以加的参数中就有partition，于是在configure时，加入--with-plugins=partition，之后，就可以
mysql5.1,5.5,5.6做partition时支持的函数
天涯路的专栏
03-28 427
mysql5.1支持的partition函数(http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html) ABS() CEILING()(seeCEILING() and FLOOR()) DAY() DAYOFMONTH() DAYOFWEEK() DAY...
分区表实验过程
csubx4572的博客
10-14 108
一、创建测试表CREATE TABLE par_test ( ID NUMBER, NAME VARCHAR2(10), cardid number ) PARTITION BY RANGE ...
Mysql分区？
浅博的凡人
04-18 3632
Mysql分区？        译者：联创网络 4A产品部 工号5151       来源：MySQL 5.1.16 Manual       如有转载，请注明出处。且作者对本文造成的损失不负任何责任。 文档里说，分区的表好像对版本有要求，5.15的分区表也许不能在5.16使用。 分区的大概意思？ Sql标准本身没有提供太多关于数据的物理存储的指导。Sql语
MySql创建分区(按月分区)
hauchun的博客
11-19 4056
mysql表分区
oracle range用法,Oracle 分区表——Range分区
weixin_28706923的博客
04-08 1381
一、什么是分区表Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区，每个分区都是一个独立的段(SEGMENT)，可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。二、什么时候用分...
【mysql分区分表】mysql 按时间分区 【partition】
热门推荐
烤鸭的世界我们不懂的博客
06-18 1万+
大家好，我是烤鸭： 今天分享一下有关 mysql 分区。 需求： 按时间分区。 对千万数据左右的表，进行分区，数据的增加量大概千万/年。 代码实现： 模拟之前已经存在的表： DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT CO...
深入解析MySQL分区（Partition）功能
weixin_34061482的博客
04-20 480
参考  http://blog.csdn.net/tjcyjd/article/details/11194489   第18章：分区 目录 18.1. MySQL中的分区概述 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. LIST分区 18.2.3. HASH分区 18.2.4. KEY分区 18.2.5. 子分区 18.2.6. MySQL分...
MySQL分区（Partition）实例
yuhui_fish的专栏
07-16 6452
mysql分区有以下5种，本实例中为range分区。 RANGE分区 LIST分区 HASH分区 KEY分区 子分区 此为MySQL5.1新特性,通过MySQL分区（Partition）实例,你可以体验它带来效率上的提升。 以下命令可以知道你的数据库是否支持 SHOW VARIABLES LIKE '%partition%'; have_partition_engine
MySQL表分区详解：创建与优化实践
1. **存储效率提升**：相比于不分区的大表，分区可以利用多块硬盘或文件系统的优势，存储更多数据，同时支持删除不再需要的分区，简化数据清理过程。 2. **查询性能优化**：分区有助于特定查询的加速，比如通过...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值