测试验证datatime字段是否走索引

 测试验证datatime字段是否走索引？

(product)root@localhost [lots]> show create table t_order\G
*************************** 1. row ***************************
       Table: t_order
Create Table: CREATE TABLE `t_order` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `CUSTOMER_ID` varchar(50) DEFAULT NULL COMMENT '客户号',
 .....................................................................
  `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
.....................................................................
  PRIMARY KEY (`ID`),
.....................................................................
  KEY `index_create_date` (`CREATE_DATE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2360435 DEFAULT CHARSET=gbk COMMENT='订单表'
1 row in set (0.00 sec)

查看下面有走索引：
(product)root@localhost [lots]> explain select customer_id from t_order where create_date='2015-10-19 21:03:40';
+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_order | ref  | index_create_date | index_create_date | 6       | const | 1194 | NULL  |
+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

(product)root@localhost [lots]> explain select customer_id from t_order where create_date=date_format('2015-10-19 21:03:40',' %Y-%m-%d %H:%i:%S');

+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_order | ref  | index_create_date | index_create_date | 6       | const | 1194 | NULL  |
+----+-------------+---------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

为什么不会存在字段类型的隐式转换呢？

当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

。

说明mysql本身就支持这种显示格式，且只能说明是上面SQL发生类型转换的是在右边的值部分'2015-10-19 21:03:40'，只要左边字段不发生转换，就能用上索引。

测试oracle的行为：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

SQL> explain plan for select * from zeng_test where datelock ='2009-3-11';

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 822430127

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     4 |    52 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ZENG_TEST    |     4 |    52 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DATELOCK_IX1 |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATELOCK"='2009-3-11')

14 rows selected

以上测试说明，mysql和oralce下只要左边字段不发生转换，就能用上索引。