EAV数据库模型的性能优化

Question

我正在使用下面的查询，它目前花费了0.7秒。但是，当对200个并发用户运行性能测试时，查询所花费的时间超过60秒。我已经定义了所需的复合索引，但仍然没有考虑索引。

我们使用的是RDS MYSQL服务(16 GB RAM 8 vCPU)

查询

SELECT 
    patient.voided AS voided, COUNT(*) AS count FROM patient patient
        INNER JOIN person person ON patient.patient_id = person.person_id
        INNER JOIN person_attribute attr ON patient.patient_id = attr.person_id
        INNER JOIN person_attribute_type attr_type ON attr.person_attribute_type_id = attr_type.person_attribute_type_id
WHERE    
    attr.value = 'd31fe20e-6736-42ff-a3ed-b3e622e80842'
        AND attr_type.name = 'LocationAttribute'
GROUP BY patient.voided;

执行计划

​

是否可以在不更改查询和添加索引或任何db优化的情况下提高查询性能？因为查询是由hibernate框架生成的，任何代码更改都需要测试工作。

下面是来自OpenMRS 1.6的表的定义。

CREATE TABLE IF NOT EXISTS `openmrs`.`person_attribute` (
  `person_attribute_id` INT(11) NOT NULL AUTO_INCREMENT,
  `person_id` INT(11) NOT NULL DEFAULT '0',
  `value` VARCHAR(50) NOT NULL DEFAULT '',
  `person_attribute_type_id` INT(11) NOT NULL DEFAULT '0',
  `creator` INT(11) NOT NULL DEFAULT '0',
  `date_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `changed_by` INT(11) NULL DEFAULT NULL,
  `date_changed` DATETIME NULL DEFAULT NULL,
  `voided` SMALLINT(6) NOT NULL DEFAULT '0',
  `voided_by` INT(11) NULL DEFAULT NULL,
  `date_voided` DATETIME NULL DEFAULT NULL,
  `void_reason` VARCHAR(255) NULL DEFAULT NULL,
  `uuid` CHAR(38) NOT NULL,
  PRIMARY KEY (`person_attribute_id`),
  UNIQUE INDEX `person_attribute_uuid_index` (`uuid` ASC) VISIBLE,
  INDEX `identifies_person_idx` (`person_id` ASC) VISIBLE,
  INDEX `defines_attribute_type_idx` (`person_attribute_type_id` ASC) VISIBLE,
  INDEX `attribute_creator` (`creator` ASC) VISIBLE,
  INDEX `attribute_changer` (`changed_by` ASC) VISIBLE,
  INDEX `attribute_voider` (`voided_by` ASC) VISIBLE,
  CONSTRAINT `defines_attribute_type`
    FOREIGN KEY (`person_attribute_type_id`)
    REFERENCES `openmrs`.`person_attribute_type` (`person_attribute_type_id`),
  CONSTRAINT `identifies_person`
    FOREIGN KEY (`person_id`)
    REFERENCES `openmrs`.`person` (`person_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `openmrs`.`person_attribute_type` (
  `person_attribute_type_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `description` TEXT NOT NULL,
  `format` VARCHAR(50) NULL DEFAULT NULL,
  `foreign_key` INT(11) NULL DEFAULT NULL,
  `searchable` SMALLINT(6) NOT NULL DEFAULT '0',
  `creator` INT(11) NOT NULL DEFAULT '0',
  `date_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `changed_by` INT(11) NULL DEFAULT NULL,
  `date_changed` DATETIME NULL DEFAULT NULL,
  `retired` SMALLINT(6) NOT NULL DEFAULT '0',
  `retired_by` INT(11) NULL DEFAULT NULL,
  `date_retired` DATETIME NULL DEFAULT NULL,
  `retire_reason` VARCHAR(255) NULL DEFAULT NULL,
  `edit_privilege` VARCHAR(255) NULL DEFAULT NULL,
  `uuid` CHAR(38) NOT NULL,
  `sort_weight` DOUBLE NULL DEFAULT NULL,
  PRIMARY KEY (`person_attribute_type_id`),
  UNIQUE INDEX `person_attribute_type_uuid_index` (`uuid` ASC) VISIBLE,
  INDEX `name_of_attribute` (`name` ASC) VISIBLE,
  INDEX `type_creator` (`creator` ASC) VISIBLE,
  INDEX `attribute_type_changer` (`changed_by` ASC) VISIBLE,
  INDEX `attribute_is_searchable` (`searchable` ASC) VISIBLE,
  INDEX `user_who_retired_person_attribute_type` (`retired_by` ASC) VISIBLE,
  INDEX `person_attribute_type_retired_status` (`retired` ASC) VISIBLE,
  INDEX `privilege_which_can_edit` (`edit_privilege` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Answer 1

过度正常化。

您需要重新构造架构，以避免过多地运行到其他表。注意，WHERE需要对两个表进行测试。除了将名称和值移动到相同的表中外，几乎没有任何方法可以对其进行优化。就这一点而言，person_id也应该在那张桌子上。

如果框架生成模式和查询，那么应该将其降为“玩具”项目，而不是像您这样的中型数据集。

我添加了一个标签，它将链接到EAV的其他讨论。即使在清理了这些桌子之后，当有数百万行时，EAV也会发出呻吟声。

这些综合指数可能有助于：

attr:  INDEX(value, person_id,  person_attribute_type_id)
attr_type:  INDEX(name, person_attribute_type_id)

但是，如果value是TEXT，则第一个不能添加到表中。

如需进一步讨论，请为每个表提供SHOW CREATE TABLE。