`

MySQL不同存储引擎和不同分区字段对于查询的影响

mysqlMyISAMInnoDB分区查询效率 
阅读更多

前提：每种表类型准备了200万条相同的数据。

表一 InnoDB & PARTITION BY RANGE (id) 

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) */;

 

查询结果：

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) 

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) */;

 

查询结果：

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) 

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) */;

 

查询结果：

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) 

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) */;

 

查询结果：

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性能总体占优，但是不支持事务处理、外键约束等。

分享到：
| MySQL 如何快速插入大量数据
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    mysql存储引擎介绍

    MySQL 存储引擎详解 ...不同的存储引擎都有其特点和优缺，了解 MySQL 存储引擎的特点和优缺是非常重要的，因为它们直接影响着数据库的性能和稳定性。选择合适的存储引擎可以提高应用的性能和稳定性。

    mysql表分区

    MySQL表分区是数据库管理系统（DBMS）中用于提高大数据量表的存储性能和管理效率的一种技术。通过对一个大表进行分区，可以将数据分散存储到多个物理区域中，从而改善性能，尤其是在涉及大量读写操作的场景中。MySQL...

    hive数据分区时分区字段不可为中文。.doc

    - **关键点回顾**：本文介绍了 Hive 数据分区的概念、分区字段的要求以及如何解决中文字符作为分区字段的问题。 - **最佳实践**：始终确保配置文件中的字符编码设置正确，并且在修改任何配置之前都进行充分的备份。 ...

    mysql-存储分区-实验八.docx

    MySQL存储分区是数据库管理系统中一种优化查询性能和管理大量数据的技术。通过将大表分成多个较小、更易管理和处理的部分，存储分区可以提高查询效率，减少维护成本，并有助于数据的组织和备份。以下是对实验内容的...

    mysql和Oracle在对clob和blob字段的处理

    查询`CLOB`和`BLOB`字段时，MySQL会根据需要加载部分或全部数据到内存，这可能导致I/O性能的影响。 2. Oracle对`CLOB`和`BLOB`的处理： Oracle数据库提供了`CLOB`和`BLOB`数据类型，同样用于存储大量文本和二进制...

    mysql 实现定时给表追加分区

    实现mysql 每天定时自动给数据库表追加分区，包含存储计划和存储过程

    MySQL分区字段列有必要再单独建索引吗？

    分区字段是决定数据存储在哪个分区的关键因素，而索引则是提高查询性能的重要手段。那么，针对分区字段是否需要单独建立索引，这个问题需要从多个角度来分析。 首先，我们要理解分区本身已经提供了一种快速访问数据...

    MySQL 分区

    MySQL分区是一种数据库优化技术，它将大型表分成更小、更易管理的部分，每个部分称为一个分区。这种技术有助于提高查询性能，特别是...合理的设计和使用能够帮助开发者优化数据存储和查询，从而提高整个系统的效率。

    mysql 数据库表分区

    - 选择合适的索引策略可以进一步优化分区查询，例如，为经常查询的分区字段创建索引。 5. **查询优化**： - 分区有助于减少扫描的数据量，因为查询仅需涉及满足条件的分区，而不是整个表。 - 使用IN、BETWEEN或...

    mysql分区.pdf

    5. MyISAM分区：在MySQL 5.1之前的版本中，MyISAM存储引擎支持分区，但只支持RANGE分区，且分区的索引是全局的。MyISAM分区在创建时需要注意分区的规则，因为分区表中的所有索引都必须是全局索引。 创建分区的SQL...

    mysql分区文档

    MySQL 分区是数据库管理系统中的一种优化策略，它将大型表的数据分布在不同的物理存储上，以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分，但用户在操作时仍然将其视为单个表。MySQL ...

    mysql如何进行分区-mysql分区有哪些方法.pdf

    - 数据类型：所有分区字段应为整数或日期类型，以便于计算和存储。 - 分区数量：不要过多，以免增加维护复杂性。合理数量的分区可以平衡查询性能和管理成本。 以下是一些示例代码，展示了如何创建不同类型的分区： ...

    mysql如何进行分区-mysql分区有哪些方法.docx

    为了解决这一问题，MySQL 提供了分区（Partitioning）功能，以提高查询效率和管理大量数据的能力。 分区是将一个大表逻辑上划分为多个独立的部分，每个部分称为一个分区，可以分布在不同的物理存储上。这样做有以下...

    图片存储到MySQL数据库中

    2. 分区和索引：如果表很大，可以考虑对id字段进行分区，以提高查询效率。同时，避免在BLOB字段上建立索引，因为这会降低写操作性能。 3. 压缩存储：在存储前对图片进行压缩，减少数据库空间占用。 4. CDN加速：使用...

    MySQL分区分表的设计及实现-收藏备用.pdf

    本文提出的优化分区分表算法主要基于MySQL中的range分区和Merge存储引擎。通过合理的分区策略和高效的存储机制，该算法能够有效提升大规模数据查询的操作效率。 ##### 3.1 Range分区 Range分区是一种常见的分区...

    MySQL 5.7 中文文档

    1. **InnoDB存储引擎**：MySQL 5.7默认的存储引擎是InnoDB，它支持事务处理、行级锁定以及外键约束，这使得InnoDB成为处理并发操作和确保数据完整性的理想选择。5.7版本中，InnoDB在性能方面有所提升，例如更快的...

Magicbox
Global site tag (gtag.js) - Google Analytics