MYSQL : How can we optimize the select query to fetch more than 1 million data using date range

Question

我有一个包含超过 100 万条数据的表，我正在尝试使用日期范围进行获取。我已经对日期列建立了索引，但仍然搜索整个行。有人可以给出解决这个问题的最佳解决方案吗？

/** Table Structure **/

CREATE TABLE `claim_history` (
`claim_history_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`consultation_id` varchar(50) NOT NULL,
`member_id` int(11) unsigned DEFAULT NULL,
`card_number` char(18) DEFAULT NULL,
`member_name` varchar(200) DEFAULT NULL,
`network_code` int(11) unsigned NOT NULL DEFAULT '0',
`mobile_number` varchar(50) DEFAULT NULL,
`soap_number` varchar(50) DEFAULT NULL,
`diagnosis_id` varchar(50) DEFAULT NULL,
`diagnosis_code` varchar(50) DEFAULT NULL,
`diagnosis_description` text,
`activity_id` int(11) unsigned DEFAULT NULL,
`activity_code` varchar(50) DEFAULT NULL,
`activity_description` text,
`activity_comments` text,
`activity_quantity` int(11) unsigned NOT NULL DEFAULT '0',
`is_erx` tinyint(1) NOT NULL DEFAULT '0',
`lab_id` int(11) unsigned DEFAULT NULL,
`lab_name` varchar(100) DEFAULT NULL,
`session_no` tinyint(1) unsigned NOT NULL DEFAULT '0',
`net_amount` decimal(10,2) DEFAULT NULL,
`copay_pct` decimal(10,2) DEFAULT NULL,
`copay_amt` decimal(10,2) DEFAULT NULL,
`total_cost` decimal(10,2) DEFAULT NULL,
`is_edited` tinyint(1) unsigned DEFAULT '0',
`edit_comments` text,
`is_assigned_mcc` tinyint(1) DEFAULT '0' 
`mcc_user` int(11) unsigned DEFAULT NULL,
`mcc_user_name` varchar(50) DEFAULT NULL,
`rule_name` varchar(200) DEFAULT NULL,
`mcu_assigned_time` timestamp NULL DEFAULT NULL,
`mcu_action_performed_time` timestamp NULL DEFAULT NULL,
`mcu_open_time` timestamp NULL DEFAULT NULL,
`is_reappealed` tinyint(1) unsigned NOT NULL DEFAULT '0',
`reappeal_count` int(11) unsigned NOT NULL,
`denial_code` varchar(50) DEFAULT NULL,
`denial_description` text,
`rejection_comments` text,
`justification_comment` text,
`justification_reply` text,
`justification_count` tinyint(1) unsigned NOT NULL DEFAULT '0',
`edit_comment_reply` text,
`is_referral` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_physiotherapy` tinyint(1) unsigned NOT NULL DEFAULT '0',
`facility_id` int(11) unsigned DEFAULT NULL,
`provider_code` varchar(50) DEFAULT NULL,
`provider_name` varchar(100) DEFAULT NULL,
`doctor_user_id` int(11) DEFAULT NULL,
`doctor_name` varchar(100) DEFAULT NULL,
`referral_doctor_name` varchar(100) DEFAULT NULL,
`referral_clinic_name` varchar(100) DEFAULT NULL,
`pic_id` int(11) unsigned DEFAULT NULL,
`pic_name` varchar(100) DEFAULT NULL,
`ig_id` int(11) unsigned DEFAULT NULL,
`ig_name` varchar(100) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
 `history_created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`history_last_modified_on` timestamp NOT NULL DEFAULT '0000-00-00  00:00:00',
`created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL DEFAULT '0',
`last_modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_modified_by` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`claim_history_id`),
KEY `idx_consultation_id` (`consultation_id`),
KEY `idx_card_number` (`member_id`,`card_number`),
KEY `idx_provider` (`facility_id`),
KEY `idx_soap_number` (`soap_number`),
KEY `idx_created_on` (`created_on`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

/** SQL 查询 **/

SELECT fields FROM claim_history
    WHERE created_on BETWEEN '2017-01-01 00:00:00'
                         AND '2017-05-01 00:00:00';

Answer 1

您提供的查询有语法错误。

查询优化器可能会使用索引，也可能不使用索引，具体取决于您输入查询的范围。尝试非常短的范围，例如一天，然后使用一年，您可能已经看到了差异。

查看此线程以了解更多详细信息: MySQL ignores my index on a timestamp column