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) */;
...全文
642 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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 ... 以供分析。
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 ...
小灰狼 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 ... 以供分析。
楼主执行 这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。 注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。 创建完索引后,请再贴出 explain, show index 的结果,不要加\G, 直接行输出这样容易分析。
小灰狼 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秒。

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧