MySQL大表数据查询效率如何提高？

小灰狼 2014-12-31 10:00:34

一个 mysql 5.6，运行在 CentOS 7 上，一共有3.3亿左右条记录，按CreatedDateTime字段进行分区，从2014年1月1日起到2015年1月1日，每10天左右一个分区，在CreatedDateTime上有索引，另外在 UserId, DeviceId 上也创建了索引。整个表中，有2861个DeviceId，均匀分布在在3.3亿条记录中。其中的CreatedDateTime的分布也比较均匀。简单地说，就是表中一共记录了2861个Device，在一年中的运行历史数据，采样周期相对比较稳定，维持在270秒左右。

在进行查询时，花费的时间是相当长，无法满足要求。如要查询一个Device在某一天的运行历史数据（用于生成运行历史曲线图）：



select

 *

from 

	tbAnalog_20140919175031_A

where 

	deviceid = 5355

	and CreatedDateTime >= '2014-01-08 00:00:00' and CreatedDateTime <= '2014-01-09 00:00:00'

最快时需要4.5秒，最慢时，要好几分钟。

请教大家有什么办法优化，系统参数配置、表结构修改，以及其它，谢谢！

表结构如下



CREATE TABLE `tbAnalog_20140919175031_A` (

  `Id` bigint(20) NOT NULL AUTO_INCREMENT,

  `CreatedDateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `UserId` int(11) NOT NULL,

  `DeviceId` int(11) NOT NULL,

  `Col_000` smallint(6) DEFAULT NULL,

  `Col_001` smallint(6) DEFAULT NULL,

  `Col_002` smallint(6) DEFAULT NULL,

  `Col_003` float(9,1) DEFAULT NULL,

  `Col_004` float(9,1) DEFAULT NULL,

  `Col_005` float(9,1) DEFAULT NULL,

  `Col_006` smallint(6) DEFAULT NULL,

  `Col_007` smallint(6) DEFAULT NULL,

  `Col_008` smallint(6) DEFAULT NULL,

  `Col_009` float(9,1) DEFAULT NULL,

  `Col_010` float(9,1) DEFAULT NULL,

  `Col_011` float(9,1) DEFAULT NULL,

  `Col_012` float(9,1) DEFAULT NULL,

  `Col_013` smallint(6) DEFAULT NULL,

  `Col_014` float(9,1) DEFAULT NULL,

  `Col_015` float(9,1) DEFAULT NULL,

  `Col_016` float(9,1) DEFAULT NULL,

  `Col_017` smallint(6) DEFAULT NULL,

  `Col_018` smallint(6) DEFAULT NULL,

  `Col_019` smallint(6) DEFAULT NULL,

  `Col_020` smallint(6) DEFAULT NULL,

  `Col_021` smallint(6) DEFAULT NULL,

  `Col_022` float(9,1) DEFAULT NULL,

  `Col_023` double(18,1) DEFAULT NULL,

  `Col_024` float(18,3) DEFAULT NULL,

  `Col_025` float(9,1) DEFAULT NULL,

  PRIMARY KEY (`Id`,`CreatedDateTime`),

  KEY `idx_A_20140919175041_CDT` (`CreatedDateTime`),

  KEY `idx_A_20140919175041_UId` (`UserId`),

  KEY `idx_A_20140919175041_DId` (`DeviceId`)

) ENGINE=InnoDB AUTO_INCREMENT=331831721 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (Year(`CreatedDateTime`)*10000 + Month(`CreatedDateTime`)*100 + Day(`CreatedDateTime`))

(PARTITION pl20140110 VALUES LESS THAN (20140110) ENGINE = InnoDB,

 PARTITION pl20140120 VALUES LESS THAN (20140120) ENGINE = InnoDB,

 PARTITION pl20140130 VALUES LESS THAN (20140130) ENGINE = InnoDB,

 PARTITION pl20140210 VALUES LESS THAN (20140210) ENGINE = InnoDB,

 PARTITION pl20140220 VALUES LESS THAN (20140220) ENGINE = InnoDB,

 PARTITION pl20140230 VALUES LESS THAN (20140230) ENGINE = InnoDB,

 PARTITION pl20140310 VALUES LESS THAN (20140310) ENGINE = InnoDB,

 PARTITION pl20140320 VALUES LESS THAN (20140320) ENGINE = InnoDB,

 PARTITION pl20140330 VALUES LESS THAN (20140330) ENGINE = InnoDB,

 PARTITION pl20140410 VALUES LESS THAN (20140410) ENGINE = InnoDB,

 PARTITION pl20140420 VALUES LESS THAN (20140420) ENGINE = InnoDB,

 PARTITION pl20140430 VALUES LESS THAN (20140430) ENGINE = InnoDB,

 PARTITION pl20140510 VALUES LESS THAN (20140510) ENGINE = InnoDB,

 PARTITION pl20140520 VALUES LESS THAN (20140520) ENGINE = InnoDB,

 PARTITION pl20140530 VALUES LESS THAN (20140530) ENGINE = InnoDB,

 PARTITION pl20140610 VALUES LESS THAN (20140610) ENGINE = InnoDB,

 PARTITION pl20140620 VALUES LESS THAN (20140620) ENGINE = InnoDB,

 PARTITION pl20140630 VALUES LESS THAN (20140630) ENGINE = InnoDB,

 PARTITION pl20140710 VALUES LESS THAN (20140710) ENGINE = InnoDB,

 PARTITION pl20140720 VALUES LESS THAN (20140720) ENGINE = InnoDB,

 PARTITION pl20140730 VALUES LESS THAN (20140730) ENGINE = InnoDB,

 PARTITION pl20140810 VALUES LESS THAN (20140810) ENGINE = InnoDB,

 PARTITION pl20140820 VALUES LESS THAN (20140820) ENGINE = InnoDB,

 PARTITION pl20140830 VALUES LESS THAN (20140830) ENGINE = InnoDB,

 PARTITION pl20140910 VALUES LESS THAN (20140910) ENGINE = InnoDB,

 PARTITION pl20140920 VALUES LESS THAN (20140920) ENGINE = InnoDB,

 PARTITION pl20140930 VALUES LESS THAN (20140930) ENGINE = InnoDB,

 PARTITION pl20141010 VALUES LESS THAN (20141010) ENGINE = InnoDB,

 PARTITION pl20141020 VALUES LESS THAN (20141020) ENGINE = InnoDB,

 PARTITION pl20141030 VALUES LESS THAN (20141030) ENGINE = InnoDB,

 PARTITION pl20141110 VALUES LESS THAN (20141110) ENGINE = InnoDB,

 PARTITION pl20141120 VALUES LESS THAN (20141120) ENGINE = InnoDB,

 PARTITION pl20141130 VALUES LESS THAN (20141130) ENGINE = InnoDB,

 PARTITION pl20141210 VALUES LESS THAN (20141210) ENGINE = InnoDB,

 PARTITION pl20141220 VALUES LESS THAN (20141220) ENGINE = InnoDB,

 PARTITION pl20141230 VALUES LESS THAN (20141230) ENGINE = InnoDB,

 PARTITION pl20150110 VALUES LESS THAN (20150110) ENGINE = InnoDB,

 PARTITION pl20150120 VALUES LESS THAN (20150120) ENGINE = InnoDB,

 PARTITION pl20150130 VALUES LESS THAN (20150130) ENGINE = InnoDB,

 PARTITION pl20150210 VALUES LESS THAN (20150210) ENGINE = InnoDB,

 PARTITION pl20150220 VALUES LESS THAN (20150220) ENGINE = InnoDB,

 PARTITION pl20150230 VALUES LESS THAN (20150230) ENGINE = InnoDB,

 PARTITION pl20150310 VALUES LESS THAN (20150310) ENGINE = InnoDB,

 PARTITION pl20150320 VALUES LESS THAN (20150320) ENGINE = InnoDB,

 PARTITION pl20150330 VALUES LESS THAN (20150330) ENGINE = InnoDB) */;

...全文

641 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

ACMAIN_CHM 2014-12-31

打赏
举报

引用 7 楼 hemowolf 的回复:

[quote=引用 2 楼 ACMAIN_CHM 的回复:] create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

小灰狼 2014-12-31

打赏
举报

引用 2 楼 ACMAIN_CHM 的回复:

create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

1 SIMPLE tbAnalog_20140919175031_A ref idx_A_20140919175041_CDT,idx_A_20140919175041_DId idx_A_20140919175041_DId 4 const 116621 Using where

小灰狼 2014-12-31

打赏
举报

引用 5 楼 dujie4752041 的回复:

再还有一种办法, 就是将多少数据合成一条记录插入, 这样从根本上减少了记录条. 当然, 这需要你业务支持。

这是一个测试表，专用来进行压力测试的生成数据时，是一条语句插入所有的 DeviceId 的数据

dujie4752041 2014-12-31

打赏
举报

再还有一种办法, 就是将多少数据合成一条记录插入, 这样从根本上减少了记录条. 当然, 这需要你业务支持。

dujie4752041 2014-12-31

打赏
举报

1.减少主键, 比如将 (`UserId`) (`DeviceId`) 合成一个字段, 这样少了一个主键, 2. 将 (`CreatedDateTime`) 不要以时间形式插入, 直接换算成秒或者ns 这样查询会变快. 3. 如果数据量大就直接用 MYSAM 类型数据库表类型, 由于 INNODB 是单文件操作, 肯定会比多文件操作的 MYSAM 慢.

rucypli 2014-12-31

打赏
举报

explain下是否使用了createtime字段上的索引

ACMAIN_CHM 2014-12-31

打赏
举报

create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

benluobo 2014-12-31

打赏
举报

看需求应该是类似于NMS系统网络设备的性能历史表吧根据查询条件在性能表上建立对应的主键比如设备ID，指标名称，采集时间

小灰狼 2014-12-31

打赏
举报

索引建好了，show index from ...结果如下，表名列和后面几个全部为空的列就不列出来了 +------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+ | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | +------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+ | 0 | PRIMARY | 1 | Id | A | 327215970 | NULL | NULL | | BTREE | | 0 | PRIMARY | 2 | CreatedDateTime | A | 327215970 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175041_CDT | 1 | CreatedDateTime | A | 65443194 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175041_UId | 1 | UserId | A | 822150 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175041_DId | 1 | DeviceId | A | 1065850 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175031A_1 | 1 | CreatedDateTime | A | 65443194 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175031A_1 | 2 | DeviceId | A | 327215970 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175031A_2 | 1 | DeviceId | A | 867946 | NULL | NULL | | BTREE | | 1 | idx_A_20140919175031A_2 | 2 | CreatedDateTime | A | 327215970 | NULL | NULL | | BTREE | +------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+ 创建这个索引之后，每次查询强制使用(DeviceId, CreatedDateTime)快了很多，基本上在4秒以内，但这只是单线程测试，还是不太满意啊： select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 212 and CreatedDateTime >= '2014-01-21 00:00:00' and CreatedDateTime <= '2014-01-22 00:00:00'; 受影响的行: 0 时间: 2.592s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 1994 and CreatedDateTime >= '2014-02-17 00:00:00' and CreatedDateTime <= '2014-02-18 00:00:00'; 受影响的行: 0 时间: 2.530s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 2965 and CreatedDateTime >= '2014-03-11 00:00:00' and CreatedDateTime <= '2014-03-12 00:00:00'; 受影响的行: 0 时间: 2.269s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 4511 and CreatedDateTime >= '2014-04-08 00:00:00' and CreatedDateTime <= '2014-04-09 00:00:00'; 受影响的行: 0 时间: 2.230s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 5304 and CreatedDateTime >= '2014-05-15 00:00:00' and CreatedDateTime <= '2014-05-16 00:00:00'; 受影响的行: 0 时间: 1.742s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 5473 and CreatedDateTime >= '2014-06-27 00:00:00' and CreatedDateTime <= '2014-06-28 00:00:00'; 受影响的行: 0 时间: 2.526s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 2557 and CreatedDateTime >= '2014-07-17 00:00:00' and CreatedDateTime <= '2014-07-18 00:00:00'; 受影响的行: 0 时间: 2.302s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 5254 and CreatedDateTime >= '2014-08-07 00:00:00' and CreatedDateTime <= '2014-08-08 00:00:00'; 受影响的行: 0 时间: 2.237s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 5473 and CreatedDateTime >= '2014-09-21 00:00:00' and CreatedDateTime <= '2014-09-22 00:00:00'; 受影响的行: 0 时间: 1.893s [SQL] select * from tbAnalog_20140919175031_A Use Index (idx_A_20140919175031A_2) where DeviceId = 5095 and CreatedDateTime >= '2014-10-24 00:00:00' and CreatedDateTime <= '2014-10-25 00:00:00'; 受影响的行: 0 时间: 2.282s

ACMAIN_CHM 2014-12-31

打赏
举报

引用

(DeviceId, CreatedDateTime) 和 (CreatedDateTime, DeviceId)是不一样的？你是说两个字段出现的次序对查询效率有区别？

是的，不一样。如果需要更详细的说明，建议自行参考MYSQL官方免费手册中的第七章，优化中的SELECT语句中索引的使用。或者随便找本学校的数据库教科书阅读一下。否则这个解释起来估计得几千字。

小灰狼 2014-12-31

打赏
举报

引用 12 楼 ACMAIN_CHM 的回复:

[quote=引用 2 楼 ACMAIN_CHM 的回复:] create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

楼主执行这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。创建完索引后，请再贴出 explain, show index 的结果，不要加\G, 直接行输出这样容易分析。 [/quote] 这个联合索引是有的，就是名称为 idx_A_20140919175031A_1 的那个索引另外，你说(DeviceId, CreatedDateTime) 和 (CreatedDateTime, DeviceId)是不一样的？你是说两个字段出现的次序对查询效率有区别？创建索引要一个多小时，不晓得今天下班前搞得完不！！

ACMAIN_CHM 2014-12-31

打赏
举报

引用 2 楼 ACMAIN_CHM 的回复:

create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

小灰狼 2014-12-31

打赏
举报

引用 10 楼 ACMAIN_CHM 的回复:

[quote=引用 9 楼 hemowolf 的回复:] 是有索引的： PRIMARY KEY (`Id`,`CreatedDateTime`), KEY `idx_A_20140919175041_CDT` (`CreatedDateTime`), KEY `idx_A_20140919175041_UId` (`UserId`), KEY `idx_A_20140919175041_DId` (`DeviceId`) 另外，根据楼上的建议，又花了一个多小时的时间给 CreatedDateTime 和 DeviceId 创建了联合索引再查询时，发现奇怪的现象，在更换 DeviceId 和 CreatedDatetime 的范围后，要么查询相对较快（只是相对，其实也没满足我们的要求），需要4~6秒，要么非常慢，需要550~650秒。

楼主啊，能否直接了当的把 show index from ...的结果贴出来啊。这其中有很多信息是有助于分析执行的啊。为什么死活不肯贴呢？

[/quote] mysql> show index from tbAnalog_20140919175031_A \G; *************************** 1. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: Id Collation: A Cardinality: 327215970 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: CreatedDateTime Collation: A Cardinality: 327215970 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 1 Key_name: idx_A_20140919175041_CDT Seq_in_index: 1 Column_name: CreatedDateTime Collation: A Cardinality: 65443194 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 1 Key_name: idx_A_20140919175041_UId Seq_in_index: 1 Column_name: UserId Collation: A Cardinality: 822150 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 1 Key_name: idx_A_20140919175041_DId Seq_in_index: 1 Column_name: DeviceId Collation: A Cardinality: 1065850 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 1 Key_name: idx_A_20140919175031A_1 Seq_in_index: 1 Column_name: CreatedDateTime Collation: A Cardinality: 65443194 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: tbAnalog_20140919175031_A Non_unique: 1 Key_name: idx_A_20140919175031A_1 Seq_in_index: 2 Column_name: DeviceId Collation: A Cardinality: 327215970 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 7 rows in set (0.00 sec)

ACMAIN_CHM 2014-12-31

打赏
举报

引用 9 楼 hemowolf 的回复:

是有索引的： PRIMARY KEY (`Id`,`CreatedDateTime`), KEY `idx_A_20140919175041_CDT` (`CreatedDateTime`), KEY `idx_A_20140919175041_UId` (`UserId`), KEY `idx_A_20140919175041_DId` (`DeviceId`) 另外，根据楼上的建议，又花了一个多小时的时间给 CreatedDateTime 和 DeviceId 创建了联合索引再查询时，发现奇怪的现象，在更换 DeviceId 和 CreatedDatetime 的范围后，要么查询相对较快（只是相对，其实也没满足我们的要求），需要4~6秒，要么非常慢，需要550~650秒。

楼主啊，能否直接了当的把 show index from ...的结果贴出来啊。这其中有很多信息是有助于分析执行的啊。为什么死活不肯贴呢？

小灰狼 2014-12-31

打赏
举报

引用 8 楼 ACMAIN_CHM 的回复:

[quote=引用 7 楼 hemowolf 的回复:] [quote=引用 2 楼 ACMAIN_CHM 的回复:] create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime) 另外建议贴出 explain select ...及 show index from ... 以供分析。

1 SIMPLE tbAnalog_20140919175031_A ref idx_A_20140919175041_CDT,idx_A_20140919175041_DId idx_A_20140919175041_DId 4 const 116621 Using where [/quote]索引创建了吗？另外没看到你贴的show index ...[/quote] 是有索引的： PRIMARY KEY (`Id`,`CreatedDateTime`), KEY `idx_A_20140919175041_CDT` (`CreatedDateTime`), KEY `idx_A_20140919175041_UId` (`UserId`), KEY `idx_A_20140919175041_DId` (`DeviceId`) 另外，根据楼上的建议，又花了一个多小时的时间给 CreatedDateTime 和 DeviceId 创建了联合索引再查询时，发现奇怪的现象，在更换 DeviceId 和 CreatedDatetime 的范围后，要么查询相对较快（只是相对，其实也没满足我们的要求），需要4~6秒，要么非常慢，需要550~650秒。

MySQL官方提供的驱动包 mysql-connector-java-5.1.30.zip