MySQL大表数据查询效率如何提高？-CSDN论坛

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261 结帖率 100%

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

0 2014-12-31 10:00:34

回复数 15 只看楼主引用举报楼主

⋅关于Maven默认插件的一点疑问 ⋅关于双层VLAN报文无法分配到网卡队列的问题 ⋅关于volatile和i++的疑问 更多帖子 关注私信空间博客 benluobo 本版专家分：13627 版主 红花 2014年4月其他数据库开发大版内专家分月排行榜第一 黄花 2016年1月其他数据库开发大版内专家分月排行榜第二 2014年12月其他数据库开发大版内专家分月排行榜第二 2014年11月其他数据库开发大版内专家分月排行榜第二 2014年5月其他数据库开发大版内专家分月排行榜第二 蓝花 2014年3月其他数据库开发大版内专家分月排行榜第三: 看需求应该是类似于NMS系统网络设备的性能历史表吧

根据查询条件在性能表上建立对应的主键

比如设备ID，指标名称，采集时间

0 2014-12-31 10:53:29

只看TA 引用举报 #1 得分 0

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：472736 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: create index xxx on tbAnalog_20140919175031_A (deviceid ,CreatedDateTime)

另外建议贴出 explain select ...及 show index from ... 以供分析。

0 2014-12-31 11:23:59

只看TA 引用举报 #2 得分 10

⋅mongodb4.0将支持事务 ⋅csdn越来越恶心了，不仅每个帖子挂超链接，一楼回复直接是推荐链接 ⋅为什么每个帖子的回复都自动加个超链接？？想流量想疯了吧 更多帖子 关注私信空间博客 rucypli 本版专家分：106068 版主 红花 2017年6月其他数据库开发大版内专家分月排行榜第一 2017年2月其他数据库开发大版内专家分月排行榜第一 2013年11月其他数据库开发大版内专家分月排行榜第一 2013年9月其他数据库开发大版内专家分月排行榜第一 2013年2月其他数据库开发大版内专家分月排行榜第一 黄花 2018年2月其他数据库开发大版内专家分月排行榜第二 2018年1月其他数据库开发大版内专家分月排行榜第二 2017年12月其他数据库开发大版内专家分月排行榜第二 2017年11月其他数据库开发大版内专家分月排行榜第二 2017年1月其他数据库开发大版内专家分月排行榜第二 2014年8月其他数据库开发大版内专家分月排行榜第二 2014年2月其他数据库开发大版内专家分月排行榜第二 2014年1月其他数据库开发大版内专家分月排行榜第二 2013年12月其他数据库开发大版内专家分月排行榜第二 2013年10月其他数据库开发大版内专家分月排行榜第二 2013年8月其他数据库开发大版内专家分月排行榜第二 2013年5月其他数据库开发大版内专家分月排行榜第二 2013年1月其他数据库开发大版内专家分月排行榜第二 2012年8月其他数据库开发大版内专家分月排行榜第二 2012年5月其他数据库开发大版内专家分月排行榜第二 2012年4月其他数据库开发大版内专家分月排行榜第二 2012年1月其他数据库开发大版内专家分月排行榜第二 蓝花 2017年9月其他数据库开发大版内专家分月排行榜第三 2017年7月其他数据库开发大版内专家分月排行榜第三 2017年5月其他数据库开发大版内专家分月排行榜第三 2017年3月其他数据库开发大版内专家分月排行榜第三 2016年12月其他数据库开发大版内专家分月排行榜第三 2014年11月其他数据库开发大版内专家分月排行榜第三 2014年7月其他数据库开发大版内专家分月排行榜第三 2014年6月其他数据库开发大版内专家分月排行榜第三 2014年5月其他数据库开发大版内专家分月排行榜第三 2013年7月其他数据库开发大版内专家分月排行榜第三 2013年3月其他数据库开发大版内专家分月排行榜第三 2012年7月其他数据库开发大版内专家分月排行榜第三 2012年6月其他数据库开发大版内专家分月排行榜第三 2011年12月其他数据库开发大版内专家分月排行榜第三: explain下是否使用了createtime字段上的索引

0 2014-12-31 11:25:58

只看TA 引用举报 #3 得分 10

⋅VC 利用 EXCEL 导出自定义图表 ⋅创建系统服务利用 WNetAddConnection2 映射网络共享目录 ⋅C++ 生成的 DLL 回调 C# 所传入的函数指针 更多帖子 关注私信空间博客 dujie4752041 本版专家分：215: 1.减少主键, 比如将 (`UserId`) (`DeviceId`) 合成一个字段, 这样少了一个主键,
2. 将 (`CreatedDateTime`) 不要以时间形式插入, 直接换算成秒或者ns 这样查询会变快.
3. 如果数据量大就直接用 MYSAM 类型数据库表类型, 由于 INNODB 是单文件操作, 肯定会比多文件操作的 MYSAM 慢.

0 2014-12-31 11:32:08

只看TA 引用举报 #4 得分 10

⋅VC 利用 EXCEL 导出自定义图表 ⋅创建系统服务利用 WNetAddConnection2 映射网络共享目录 ⋅C++ 生成的 DLL 回调 C# 所传入的函数指针 更多帖子 关注私信空间博客 dujie4752041 本版专家分：215: 再还有一种办法, 就是将多少数据合成一条记录插入, 这样从根本上减少了记录条. 当然, 这需要你业务支持。

0 2014-12-31 11:33:16

只看TA 引用举报 #5 得分 10

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 引用 5 楼 dujie4752041 的回复:
再还有一种办法, 就是将多少数据合成一条记录插入, 这样从根本上减少了记录条. 当然, 这需要你业务支持。

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

0 2014-12-31 11:36:13

只看TA 引用举报 #6 得分 0

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 引用 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

0 2014-12-31 11:40:03

只看TA 引用举报 #7 得分 0

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：472736 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 引用 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

索引创建了吗？
另外没看到你贴的show index ...

0 2014-12-31 11:54:04

只看TA 引用举报 #8 得分 10

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 引用 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

索引创建了吗？
另外没看到你贴的show index ...

是有索引的：
  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秒。

0 2014-12-31 14:21:34

只看TA 引用举报 #9    得分 0

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：472736 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 引用 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 ...的结果贴出来啊。这其中有很多信息是有助于分析执行的啊。为什么死活不肯贴呢？

0 2014-12-31 14:26:14

只看TA 引用举报 #10    得分 10

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 引用 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 ...的结果贴出来啊。这其中有很多信息是有助于分析执行的啊。为什么死活不肯贴呢？

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)

0 2014-12-31 14:54:41

只看TA 引用举报 #11    得分 0

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：472736 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 引用 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, 直接行输出这样容易分析。

0 2014-12-31 15:04:21

只看TA 引用举报 #12    得分 20

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 引用 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, 直接行输出这样容易分析。

这个联合索引是有的，就是名称为 idx_A_20140919175031A_1 的那个索引
另外，你说(DeviceId, CreatedDateTime) 和 (CreatedDateTime, DeviceId)是不一样的？你是说两个字段出现的次序对查询效率有区别？

创建索引要一个多小时，不晓得今天下班前搞得完不！！

0 2014-12-31 15:31:06

只看TA 引用举报 #13    得分 0

⋅国内大家感觉程序员中有百分之多少不知道CSDN的？ ⋅无法看到某些私信 ⋅无法看到某些私信 更多帖子 关注私信空间博客 ACMAIN_CHM 本版专家分：472736 版主 名人 2012年荣获名人称号 榜眼 2010年总版技术专家分年内排行榜第二 探花 2009年总版技术专家分年内排行榜第三 进士 2013年总版技术专家分年内排行榜第十 2011年总版技术专家分年内排行榜第七: 引用
(DeviceId, CreatedDateTime) 和 (CreatedDateTime, DeviceId)是不一样的？你是说两个字段出现的次序对查询效率有区别？
是的，不一样。
如果需要更详细的说明，建议自行参考MYSQL官方免费手册中的第七章，优化中的SELECT语句中索引的使用。或者随便找本学校的数据库教科书阅读一下。否则这个解释起来估计得几千字。

0 2014-12-31 17:21:21

只看TA 引用举报 #14 得分 20

⋅.net 系统框架是如何管理事务的？ ⋅springboot 如何指定 hibernate 映射文件 ⋅hibernate 生成表时，boolean 字段怎么办？要求跨数据库！ 更多帖子 关注私信空间博客 小灰狼 本版专家分：261: 索引建好了，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

0 2014-12-31 17:22:15

只看TA 引用举报 #15    得分 0

MySQL大表数据查询效率如何提高？ [问题点数：100分，结帖人hemowolf]