楼主执行 这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。 注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。创建完索引后,请再贴出 explain, show index 的结果,不要加\G, 直接行输出这样容易分析。
楼主执行 这个创建索引的语句。从 show index 中没有看到需要创建的这个索引。 注意 (deviceid ,CreatedDateTime)索引与 (CreatedDateTime,deviceid ) 是不一样的。创建完索引后,请再贴出 explain, show index 的结果,不要加\G, 直接行输出这样容易分析。
另外建议贴出 explain select ...及 show index from ... 以供分析。
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秒。
*************************** 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语句中索引的使用。 或者随便找本学校的数据库教科书阅读一下。否则这个解释起来估计得几千字。
+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+
| 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