MySQL不同存储引擎和不同分区字段对于查询的影响_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL不同存储引擎和不同分区字段对于查询的影响_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9450字，纯文字阅读大概需要14分钟。
内容图文
bitsCN.com
MySQL不同存储引擎和不同分区字段对于查询的影响
前提：每种表类型准备了200万条相同的数据。
表一 InnoDB & PARTITION BY RANGE (id)
Sql代码
CREATE TABLE `customer_innodb_id` (
`id` int(11) NOT NULL,
`email` varchar(64) NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`phone` varchar(13) DEFAULT NULL,
`birth` date DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
`avatar` blob,
`address` varchar(64) DEFAULT NULL,
`regtime` datetime DEFAULT NULL,
`lastip` varchar(15) DEFAULT NULL,
`modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (500000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1000000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1500000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2000000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
查询结果：
Sql代码
mysql> select count(*) from customer_innodb_id where id > 50000 and id < 500000;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (1.19 sec)
mysql> select count(*) from customer_innodb_id where id > 50000 and id < 500000;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (0.28 sec)
mysql> select count(*) from customer_innodb_id where regtime > '1995-01-01 00:00
:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (4.74 sec)
mysql> select count(*) from customer_innodb_id where regtime > '1995-01-01 00:00
:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (5.28 sec)
表二 InnoDB & PARTITION BY RANGE (year)
Sql代码
CREATE TABLE `customer_innodb_year` (
`id` int(11) NOT NULL,
`email` varchar(64) NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`phone` varchar(13) DEFAULT NULL,
`birth` date DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
`avatar` blob,
`address` varchar(64) DEFAULT NULL,
`regtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastip` varchar(15) DEFAULT NULL,
`modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`regtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(regtime ))
(PARTITION p0 VALUES LESS THAN (1996) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1997) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1998) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1999) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2002) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
查询结果：
Sql代码
mysql> select count(*) from customer_innodb_year where id > 50000 and id < 50000
0;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (5.31 sec)
mysql> select count(*) from customer_innodb_year where id > 50000 and id < 50000
0;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (0.31 sec)
mysql> select count(*) from customer_innodb_year where regtime > '1995-01-01 00:
00:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (0.47 sec)
mysql> select count(*) from customer_innodb_year where regtime > '1995-01-01 00:
00:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (0.19 sec)
表三 MyISAM & PARTITION BY RANGE (id)
Sql代码
CREATE TABLE `customer_myisam_id` (
`id` int(11) NOT NULL,
`email` varchar(64) NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`phone` varchar(13) DEFAULT NULL,
`birth` date DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
`avatar` blob,
`address` varchar(64) DEFAULT NULL,
`regtime` datetime DEFAULT NULL,
`lastip` varchar(15) DEFAULT NULL,
`modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (100000) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (500000) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1000000) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (1500000) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2000000) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
查询结果：
Sql代码
mysql> select count(*) from customer_myisam_id where id > 50000 and id < 500000;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (0.59 sec)
mysql> select count(*) from customer_myisam_id where id > 50000 and id < 500000;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (0.16 sec)
mysql> select count(*) from customer_myisam_id where regtime > '1995-01-01 00:00
:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (34.17 sec)
mysql> select count(*) from customer_myisam_id where regtime > '1995-01-01 00:00
:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (34.06 sec)
表四 MyISAM & PARTITION BY RANGE (year)
Sql代码
CREATE TABLE `customer_myisam_year` (
`id` int(11) NOT NULL,
`email` varchar(64) NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`phone` varchar(13) DEFAULT NULL,
`birth` date DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
`avatar` blob,
`address` varchar(64) DEFAULT NULL,
`regtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastip` varchar(15) DEFAULT NULL,
`modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`regtime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(regtime ))
(PARTITION p0 VALUES LESS THAN (1996) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1997) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1998) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (1999) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2001) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;
查询结果：
Sql代码
mysql> select count(*) from customer_myisam_year where id > 50000 and id < 50000
0;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (2.08 sec)
mysql> select count(*) from customer_myisam_year where id > 50000 and id < 50000
0;
+----------+
| count(*) |
+----------+
| 449999 |
+----------+
1 row in set (0.17 sec)
mysql> select count(*) from customer_myisam_year where regtime > '1995-01-01 00:
00:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (0.56 sec)
mysql> select count(*) from customer_myisam_year where regtime > '1995-01-01 00:
00:00' and regtime < '1996-01-01 00:00:00';
+----------+
| count(*) |
+----------+
| 199349 |
+----------+
1 row in set (0.13 sec)
结果汇总
序号 存储引擎 分区函数 查询条件 一次查询(sec) 二次查询(sec)
1 InnoDB id id 1.19 0.28
2 InnoDB id regtime 4.74 5.28
3 InnoDB year id 5.31 0.31
4 InnoDB year regtime 0.47 0.19
5 MyISAM id id 0.59 0.16
6 MyISAM id regtime 34.17 34.06
7 MyISAM year id 2.08 0.17
8 MyISAM year regtime 0.56 0.13
总结
1、对于按照时间区间来查询的，建议采用按照时间来分区，减少查询范围。
2、MyISAM性能总体占优，但是不支持事务处理、外键约束等。
bitsCN.com
内容总结
以上是互联网集市为您收集整理的MySQL不同存储引擎和不同分区字段对于查询的影响_MySQL全部内容，希望文章能够帮你解决MySQL不同存储引擎和不同分区字段对于查询的影响_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。