mysql - 如何在索引中选择正确的列以获得更好的性能

Question

我们正在尝试根据某些条件检索数据，但问题在于为列提供索引。已有一些索引，但尚未使用。

查询:

select `UK_Generic_Demand_Details`.`genericDemandId` AS `genericDemandId`,
    `UK_Generic_Demand_Details`.`customerReference` AS `customerReference`,
    `UK_Generic_Demand_Details`.`policyNumber` AS `policyNumber`,
    `UK_Generic_Demand_Details`.`demandTypeId` AS `demandTypeId`,
    `UK_Generic_Demand_Details`.`initiator` AS `initiator`,
    `UK_Generic_Demand_Details`.`demandCreatedDateTime` AS `demandCreatedDateTime`,
    `UK_Generic_Demand_Details`.`demandEndDateTime` AS `demandEndDateTime`,
    `UK_Generic_Demand_Details`.`startDateTime` AS `startDateTime`,
    `UK_Generic_Demand_Details`.`endDateTime` AS `endDateTime`,
    `UK_Generic_Demand_Details`.`status` AS `status`,
    `UK_Generic_Demand_Details`.`isUrgent` AS `isUrgent`,
    `UK_Generic_Demand_Details`.`modifiedCreatedDateTime` AS `modifiedCreatedDateTime`,
    `UK_Generic_Demand_Details`.`demandSource` AS `demandSource`,
    `UK_Generic_Demand_Details`.`id` AS `Id`,
    `UK_Generic_Demand_Details`.`schemeReference` AS `schemeReference`,
    `UK_Generic_Demand_Details`.`categoryReference` AS `categoryReference`,
    `UK_Generic_Demand_Details`.`aggregationKey` AS `aggregationKey` 
from `UK_Generic_Demand_Details` 
where (((`UK_Generic_Demand_Details`.`customerReference` is not null) 
    and (`UK_Generic_Demand_Details`.`customerReference` <> '') 
    and (`UK_Generic_Demand_Details`.`policyNumber` is not null) 
    and (`UK_Generic_Demand_Details`.`policyNumber` <> '') 
    and (`UK_Generic_Demand_Details`.`genericDemandId` is not null)) 
    or ((`UK_Generic_Demand_Details`.`schemeReference` is not null) 
    and (`UK_Generic_Demand_Details`.`schemeReference` <> '') 
    and (`UK_Generic_Demand_Details`.`genericDemandId` is not null)))

存在的索引是:

PRIMARY KEY (`id`),
 KEY `IX7_UK_Generic_Demand_Details` (`genericDemandId`),
 KEY `IX8_UK_Generic_Demand_Details` (`initiator`,`status`) USING BTREE,
 KEY `IX9_UK_Generic_Demand_Details` (`aggregationKey`,`status`,`genericDemandId`) USING BTREE,
 KEY `IX10_UK_Generic_Demand_Details` (`categoryReference`),
 KEY `IX11_UK_Generic_Demand_Details` (`aggregationKey`),
 KEY `IX1_UK_Generic_Demand_Details` (`status`,`aggregationKey`) USING BTREE,
 KEY `IX2_UK_Generic_Demand_Details` (`customerReference`),
 KEY `IX4_UK_Generic_Demand_Details` (`schemeReference`) USING BTREE,
 KEY `IX12_UK_Generic_Demand_Details` (`demandTypeId`,`contractSystem`,`productGroup`,`isBusinessException`) USING BTREE,
 KEY `IX3_UK_Generic_Demand_Details` (`policyNumber`,`demandTypeId`,`status`) USING BTREE,
 KEY `IX5_UK_Generic_Demand_Details` (`demandTypeId`,`demandId`) USING BTREE

查询的解释是:

有人建议我为正确的列建立索引以获得更好的性能。

完整的表结构是:

CREATE TABLE `UK_Generic_Demand_Details` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `policyNumber` varchar(255) DEFAULT NULL,
 `customerReference` varchar(255) DEFAULT NULL,
 `demandTypeId` int(11) DEFAULT NULL,
 `demandId` int(11) DEFAULT NULL,
 `initiator` varchar(255) DEFAULT NULL,
 `startDateTime` datetime DEFAULT NULL,
 `endDateTime` datetime DEFAULT NULL,
 `isActive` tinyint(1) DEFAULT NULL,
 `demandCreatedDateTime` datetime DEFAULT NULL,
 `demandEndDateTime` datetime DEFAULT NULL,
 `status` int(11) DEFAULT NULL,
 `documentUniqueReference` varchar(20) DEFAULT NULL,
 `isUrgent` bit(1) DEFAULT NULL,
 `modifiedCreatedDateTime` datetime DEFAULT NULL,
 `demandSource` varchar(20) DEFAULT NULL,
 `genericDemandId` int(11) DEFAULT NULL,
 `schemeReference` varchar(8) NOT NULL,
 `categoryReference` varchar(8) DEFAULT NULL,
 `aggregationKey` varchar(255) DEFAULT NULL,
 `contractSystem` varchar(50) NOT NULL,
 `productGroup` varchar(50) NOT NULL,
 `isBusinessException` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `IX7_UK_Generic_Demand_Details` (`genericDemandId`),
 KEY `IX8_UK_Generic_Demand_Details` (`initiator`,`status`) USING BTREE,
 KEY `IX9_UK_Generic_Demand_Details` (`aggregationKey`,`status`,`genericDemandId`) USING BTREE,
 KEY `IX10_UK_Generic_Demand_Details` (`categoryReference`),
 KEY `IX11_UK_Generic_Demand_Details` (`aggregationKey`),
 KEY `IX1_UK_Generic_Demand_Details` (`status`,`aggregationKey`) USING BTREE,
 KEY `IX2_UK_Generic_Demand_Details` (`customerReference`),
 KEY `IX4_UK_Generic_Demand_Details` (`schemeReference`) USING BTREE,
 KEY `IX12_UK_Generic_Demand_Details` (`demandTypeId`,`contractSystem`,`productGroup`,`isBusinessException`) USING BTREE,
 KEY `IX3_UK_Generic_Demand_Details` (`policyNumber`,`demandTypeId`,`status`) USING BTREE,
 KEY `IX5_UK_Generic_Demand_Details` (`demandTypeId`,`demandId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35350 DEFAULT CHARSET=utf8

Answer 1

索引无法帮助您处理此类过滤条件。

让我介绍一下也许能阐明这个主题的引言:

Searching in a database index is like searching in a printed telephone directory. The key concept is that all entries are arranged in a well-defined order. Finding data in an ordered data set is fast and easy because the sort order determines each entry's position.

你明白了吗？空值/空值根本超出了范围，因此引擎必须对表进行全面扫描。

如果这是一个多模型数据库引擎，它可能(!)至少将空值聚集在一起，从而避免获取这些字段 - 但行模型让您没有机会。