mysql - 选择 SQL 查询不适用于简单的 Where clouse

问题描述

你是一张表 Mysql Table On which simple sql where date = 'Some date' is not working

已检查日志。重新加载表几次并尝试。

这是记录存在的证据：-

select * from TRN_RP_CONSUMPTION_DAILY limit 1;

+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+
| TRCD_DATE  | TRCD_SPREAD_START_DATE | TRCD_SPREAD_END_DATE | TRCD_SOURCE_CHENNEL_ID | TRCD_CIRCLE_ID | TRCD_CATALOUGE_ID | TRCD_CONTENT_ID | TRCD_SONG_CODE_ID | TRCD_YT_CP_POLICY_ID | TRCD_YT_CHANNEL_ID | TRCD_PRODUCT_NAME_ID | TRCD_TRXN_TYPE_ID | TRCD_TRXN_COUNT | TRCD_TOP_LINE | TRCD_REVENUE |
+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+
| 2018-01-01 | 2018-01-01             | 2018-01-04           |                      5 |              1 |                 1 |          945723 |                 1 |                    1 |                  1 |                  211 |               180 |            1.75 |             0 |            0 |
+------------+------------------------+----------------------+------------------------+----------------+-------------------+-----------------+-------------------+----------------------+--------------------+----------------------+-------------------+-----------------+---------------+--------------+

这证明日期索引存在：-

select * from TRN_RP_CONSUMPTION_DAILY limit 1;
CREATE TABLE `TRN_RP_CONSUMPTION_DAILY` (
  `TRCD_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SPREAD_START_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SPREAD_END_DATE` date NOT NULL DEFAULT '0000-00-00',
  `TRCD_SOURCE_CHENNEL_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_CIRCLE_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_CATALOUGE_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_CONTENT_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_SONG_CODE_ID` int(10) unsigned NOT NULL DEFAULT '1',
  `TRCD_YT_CP_POLICY_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_YT_CHANNEL_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_PRODUCT_NAME_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TRCD_TRXN_TYPE_ID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `TRCD_TRXN_COUNT` double NOT NULL DEFAULT '0',
  `TRCD_TOP_LINE` double NOT NULL DEFAULT '0',
  `TRCD_REVENUE` double NOT NULL DEFAULT '0',
  KEY `IDX_TRCD_DATE` (`TRCD_DATE`),
  KEY `IDX_TRCD_SOURCE_CHENNEL_ID` (`TRCD_SOURCE_CHENNEL_ID`),
  KEY `IDX_TRCD_CATALOUGE_ID` (`TRCD_CATALOUGE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这就是问题所在，它应该给出一些计数但它不会出现（请参阅上面数据的第一个查询结果）：-

select count(*) from TRN_RP_CONSUMPTION_DAILY where TRCD_DATE='2018-01-01';

+----------+
| count(*) |
+----------+
|        0 |
+----------+

证明它正在使用索引：-

explain select count(*) from TRN_RP_CONSUMPTION_DAILY where TRCD_DATE='2018-01-01';
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table                    | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | TRN_RP_CONSUMPTION_DAILY | ref  | IDX_TRCD_DATE | IDX_TRCD_DATE | 3       | const |    1 | Using index |
+----+-------------+--------------------------+------+---------------+---------------+---------+-------+------+-------------+

强制索引也不起作用：-

select count(*) from TRN_RP_CONSUMPTION_DAILY FORCE INDEX(IDX_TRCD_DATE) where TRCD_DATE='2018-01-01';
+----------+
| count(*) |
+----------+
|        0 |
+----------+

是的，它的大桌子：-

select count(*) from TRN_RP_CONSUMPTION_DAILY;
+------------+
| count(*)   |
+------------+
| 2006275044 |
+------------+

表格和索引大小：-

103G = TRN_RP_CONSUMPTION_DAILY.MYD

52G = TRN_RP_CONSUMPTION_DAILY.MYI

令人惊讶的是，这是有效的，但我不能总是这样使用：-

select count(*) from TRN_RP_CONSUMPTION_DAILY where date(TRCD_DATE)='2018-01-01';
+----------+
| count(*) |
+----------+
|  1235523 |
+----------+

我知道这是有效的，因为在该索引列上使用函数时不会考虑索引。

哪个 Percona 服务器：-

服务器版本：5.5.60-38.12-log Percona Server (GPL)，Release 38.12，Revision 26ef816

当查询给出零计数时，没有任何内容出现在错误或警告 mysql 日志中。

Where clouse on other column 有正常工作的索引。有人可以帮助为什么它不能在那个日期列上工作吗？我想在此表上添加更多索引，但这不起作用，所以我停在这里。

标签： mysqlindexingpercona