`

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 5.1中文手冊

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    MySQL 5.1参考手册

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    MySQL 5.1参考手册中文版

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成...

    MySQL 5.1参考手册 （中文版）

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    MYSQL中文手册

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：...

    MySQL 5.1官方简体中文参考手册

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    mysql官方中文参考手册

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    mysql5.1中文手册

    编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. ...

    MySQL5.1参考手册官方简体中文版

    7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜 7.6.1. 使用符号链接 8. 客户端和实用工具程序 8.1. 客户端脚本和实用工具概述 8.2. myisampack：生成压缩...

    基于MySQL分区性能的详细介绍

    表的不同部分在不同的位置被存储为单独的表。MySQL从5.1.3开始支持Partition。 分区和手动分表对比 手动分表  分区 多张数据表 一张数据表 重复数据的风险 没有数据重复的风险 写入多张表 写入一张表 没有...

    MySQL中表分区技术详细解析

    MySQL 分区技术（是mysql 5.1以版本后开始用-&gt;是甲骨文mysql技术团队维护人员以插件形式插入到mysql里面的技术） 1、概述 数据库单表到达一定量后，性能会有衰减，像mysql\sql server等犹为明显，所以需要把这些数据...

    2017最新老男孩MySQL高级专业DBA实战课程全套【清晰不加密】，看完教程月入40万没毛病

    11-MySQL服务InnoDB引擎调优及不同引擎功能对比 12-MySQL服务引擎种类功能及对应参数配置说明 13-批量修改MySQL服务引擎的多种方案 14-有关MySQL服务存储引擎的面试题等说明 第十一部 MySQL读写分离开发实现及软件...

    Mysql高级性能优化思维导图

    表分区和分片：对于大型数据库，考虑使用表分区和分片技术，将数据分散到多个物理存储上，提高查询和处理性能。 查询缓存优化：根据具体情况考虑是否启用查询缓存，并设置合适的缓存失效策略，避免缓存膨胀和低效...

    MySQL高性能优化规范建议

    1. 所有表必须使用 Innodb 存储引擎 2. 数据库和表的字符集统一使用 UTF8 3. 所有表和字段都需要添加注释 4. 尽量控制单表数据量的大小,建议控制在 500 万以内。 5. 谨慎使用 MySQL 分区表 6.尽量做到冷热数据分离,...

    MySQL管理之道 性能调优、高可用与监控.part2.rar

    以最新的mysql版本为基础，以构建高性能mysql服务器为核心，从故障诊断、表设计、sql优化、性能参数调优、mydumper逻辑、xtrabackup热备份与恢复、mysql高可用集群搭建与管理、mysql服务器性能和服务监控等方面多...

    adminer-4.6.3

    删除索引/外键/视图/存储过程和函数.2:查询,合计,排序数据.3:新增/修改/删除记录.4:支持所有数据类型,包括大字段.5:能够批量执行SQL语句.6:支持将数据,表结构,视图导成SQL或CSV.7:能够外键关联打印数据库概要.8:能够...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    SQL（Structured Query Language)结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。  SQL语言主要包含5个部分  数据定义...

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