Mysql 日期型，索引查询的问题

Question

表中，有一个日期字段WorkDate（Date YYYY-MM-DD格式），现在我把它建成了索引，在检索条件时，WorkDate='YYYY-MM-DD' 时，用EXPLAIN分析，能看到使用了索引，但是当我想按月查询时，
写成 WorkDate like 'YYYY-MM%'
或者 SUBSTR(WorkDate,1,7) = 'YYYY-MM' 
或者  DATE_FORMAT(WorkDate,'%Y%m') = 'YYYYMM' 
用EXPLAIN分析的结果是都没有使用索引。。现在我想实现按月查询也能使用上索引该怎么办呀。。高手指点！！

Answer 1

第二个肯定用不到索引  至于第一个为什么用不到索引是因为mysql考虑走索引比不走索引消耗要高

Answer 2

不走索引的，当mysql与到消耗比较大的查询时候是不走索引。

Answer 3

索引字段操作符左边用函数后不走索引，要走索引可以 WorkDate >='2012-01-01' and WorkDate <'2012-02-01'

Answer 4

这个方法我也试过了，还是不走索引，EXPLAIN分析的结果是Type是All， possible_key 是WorkDate 。

Answer 5

列出你的show index from table与explain select ...

Answer 6

你对这个字段进行了运算，无法使用索引，查询某月，可以使用where WorkDate  between 'YYYY-MM-01' and 'YYYY-MM-31'这样就可以使用索引了。

Answer 7

show INDEX  FROM TAppointMst
table         Non_unique Key_name  Seq_in_index Column_name  collation Cardinality NULL Index_type
TAppointMst 0 PRIMARY 1 AppId A 84568 BTREE
TAppointMst 1 AcceptId 1 AcceptId A 84568 YES BTREE
TAppointMst 1 DayRootId 1 DayRootId A 28189 YES BTREE
TAppointMst 1 AppDate 1 AppDate A 355 YES BTREE
TAppointMst 1 WorkUserId 1 WorkUserId A 93 YES BTREE
TAppointMst 1 BookId 1 BookId A 84568 YES BTREEEXPLAIN
SELECT * from 
TAppointMst
where 
AppDate BETWEEN '2012-01-01' and '2012-01-31'
id select_type table type possible_keys rows Extra
1 SIMPLE TAppointMst ALL AppDate 85315 Using whereAppDate就是我说的WorkDate
望大家指点。。

Answer 8

SELECT * from 
TAppointMst
where 
AppDate BETWEEN '2012-01-01' and '2012-01-31'
这个查出来有多少记录，如果占了表中所有数据的大部分，全表扫描效率更高，Mysql优化器会自动选择不走索引

Answer 9

那也不多呀，全表8W多数就，用上面的SQL问查出来1W多点数据。。也没有占大部分呀。
莫非我的索引有问题？？下面是建表SQl文。。CREATE TABLE `TAppointMst` (
  `AppId` int(11) NOT NULL AUTO_INCREMENT,
  `AcceptId` int(11) DEFAULT NULL,
  `DayRootId` int(11) DEFAULT NULL,
  `BookId` int(11) DEFAULT '0',
  `WorkUserId` int(11) DEFAULT '0',
  `AppDate` date DEFAULT NULL,
  `FromTime` varchar(4) DEFAULT NULL,
  `ToTime` varchar(4) DEFAULT NULL,
  `FreeTime` int(11) DEFAULT '0',
  `DisPlayIndex` int(11) DEFAULT NULL,
  `DisPlayEndIndex` int(11) DEFAULT NULL,
  `DisPlayLineSpan` varchar(50) DEFAULT NULL,
  `MemberCnt` int(11) DEFAULT '0',
  `CarType` int(1) DEFAULT '0',
  `AppIntentionId` int(3) DEFAULT '0',
  `AppResutId` int(3) DEFAULT '0',
  `Sales` decimal(10,0) DEFAULT NULL,
  `CardSales` decimal(10,0) DEFAULT NULL,
  `FirstMenoy` decimal(10,0) DEFAULT NULL,
  `LeavMenoy` decimal(10,0) DEFAULT NULL,
  `LeavMenoyDate` date DEFAULT NULL,
  `Comment` text,
  `Comment2` text,
  `Comment3` text,
  `ReportUser` text,
  `OverHour` varchar(2) DEFAULT NULL,
  `OverMinute` varchar(2) DEFAULT NULL,
  `Status` int(1) DEFAULT '0',
  `HelpAppId` int(11) DEFAULT '0',
  `ConfirmUserId` int(11) DEFAULT NULL,
  `ParentAppId` int(11) DEFAULT '0',
  `ChildAppId` int(11) DEFAULT '0',
  `ParentPlanDate` date DEFAULT NULL,
  `ChildPlanDate` date DEFAULT NULL,
  `Yobi1` int(11) DEFAULT '0',
  `Yobi2` int(11) DEFAULT '0',
  `Yobi3` int(11) DEFAULT '0',
  `Yobi4` int(11) DEFAULT '0',
  `Yobi5` int(11) DEFAULT '0',
  `Yobi6` varchar(50) DEFAULT NULL,
  `Yobi7` varchar(50) DEFAULT NULL,
  `Yobi8` varchar(50) DEFAULT NULL,
  `Yobi9` varchar(50) DEFAULT NULL,
  `Yobi10` varchar(50) DEFAULT NULL,
  `Yobi11` date DEFAULT NULL,
  `Yobi12` date DEFAULT NULL,
  `Yobi13` datetime DEFAULT NULL,
  `InsertDate` datetime NOT NULL,
  `SakuseiSyaId` int(11) NOT NULL,
  `UpdateDate` datetime NOT NULL,
  `UpdateUserId` int(11) NOT NULL,
  PRIMARY KEY (`AppId`)
) ENGINE=InnoDB AUTO_INCREMENT=308 DEFAULT CHARSET=utf8;

Answer 10

上面的Key的部分漏了。。CREATE TABLE `TAppointMst` (
  `AppId` int(11) NOT NULL AUTO_INCREMENT,
  `AcceptId` int(11) DEFAULT NULL,
  `DayRootId` int(11) DEFAULT NULL,
  `BookId` int(11) DEFAULT '0',
  `WorkUserId` int(11) DEFAULT '0',
  `AppDate` date DEFAULT NULL,
  `FromTime` varchar(4) DEFAULT NULL,
  `ToTime` varchar(4) DEFAULT NULL,
  `FreeTime` int(11) DEFAULT '0',
  `DisPlayIndex` int(11) DEFAULT NULL,
  `DisPlayEndIndex` int(11) DEFAULT NULL,
  `DisPlayLineSpan` varchar(50) DEFAULT NULL,
  `MemberCnt` int(11) DEFAULT '0',
  `CarType` int(1) DEFAULT '0',
  `AppIntentionId` int(3) DEFAULT '0',
  `AppResutId` int(3) DEFAULT '0',
  `Sales` decimal(10,0) DEFAULT NULL,
  `CardSales` decimal(10,0) DEFAULT NULL,
  `FirstMenoy` decimal(10,0) DEFAULT NULL,
  `LeavMenoy` decimal(10,0) DEFAULT NULL,
  `LeavMenoyDate` date DEFAULT NULL,
  `Comment` text,
  `Comment2` text,
  `Comment3` text,
  `ReportUser` text,
  `OverHour` varchar(2) DEFAULT NULL,
  `OverMinute` varchar(2) DEFAULT NULL,
  `Status` int(1) DEFAULT '0',
  `HelpAppId` int(11) DEFAULT '0',
  `ConfirmUserId` int(11) DEFAULT NULL,
  `ParentAppId` int(11) DEFAULT '0',
  `ChildAppId` int(11) DEFAULT '0',
  `ParentPlanDate` date DEFAULT NULL,
  `ChildPlanDate` date DEFAULT NULL,
  `Yobi1` int(11) DEFAULT '0',
  `Yobi2` int(11) DEFAULT '0',
  `Yobi3` int(11) DEFAULT '0',
  `Yobi4` int(11) DEFAULT '0',
  `Yobi5` int(11) DEFAULT '0',
  `Yobi6` varchar(50) DEFAULT NULL,
  `Yobi7` varchar(50) DEFAULT NULL,
  `Yobi8` varchar(50) DEFAULT NULL,
  `Yobi9` varchar(50) DEFAULT NULL,
  `Yobi10` varchar(50) DEFAULT NULL,
  `Yobi11` date DEFAULT NULL,
  `Yobi12` date DEFAULT NULL,
  `Yobi13` datetime DEFAULT NULL,
  `InsertDate` datetime NOT NULL,
  `SakuseiSyaId` int(11) NOT NULL,
  `UpdateDate` datetime NOT NULL,
  `UpdateUserId` int(11) NOT NULL,
  PRIMARY KEY (`AppId`),
  KEY `AcceptId` (`AcceptId`),
  KEY `DayRootId` (`DayRootId`),
  KEY `AppDate` (`AppDate`),
  KEY `WorkUserId` (`WorkUserId`),
  KEY `BookId` (`BookId`)
) ENGINE=InnoDB AUTO_INCREMENT=84191 DEFAULT CHARSET=utf8;

Answer 11

可以试下强制走索引。
SELECT * FROM table USE INDEX(index_name) WHERE ...
具体资料可以网上查查

Answer 12

试过，用explain 分析，确实Sql文影响到的行数变少了，但是Sql的执行速度没有提高。。有时候还变慢了。唉呀呀。。

Answer 13

mysql对时间的索引处理得不好，建议对时间进行长整型转换后保存。

Answer 14

写成 WorkDate like 'YYYY-MM%'
或者 SUBSTR(WorkDate,1,7) = 'YYYY-MM' 
或者  DATE_FORMAT(WorkDate,'%Y%m') = 'YYYYMM' sql语句中出现like字段后 不走索引；
=左边不能出现表达式，否则也不走索引
详细情况请看http://www.cnblogs.com/luxf/archive/2012/02/08/2343345.html