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

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

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

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

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

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

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 ...

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秒。

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

楼主啊，能否直接了当的把 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)

楼主执行这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。创建完索引后，请再贴出 explain, show index 的结果，不要加\G, 直接行输出这样容易分析。

楼主执行这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。创建完索引后，请再贴出 explain, show index 的结果，不要加\G, 直接行输出这样容易分析。

这个联合索引是有的，就是名称为 idx_A_20140919175031A_1 的那个索引
另外，你说(DeviceId, CreatedDateTime) 和 (CreatedDateTime, DeviceId)是不一样的？你是说两个字段出现的次序对查询效率有区别？创建索引要一个多小时，不晓得今天下班前搞得完不！！

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

索引建好了，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

调试易

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

解决方案 »