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、
联系客服
