Mysql在两个表之间进行简单查询时速度太慢

Question

早上好，我有两张表，ANALISI有1462632记录，帕齐恩特有1408146记录，这个简单的计数使用帕津特的索引之一需要大约30秒才能回馈大约65000条记录

SELECT COUNT(analisi0_.ID_ANALISI) AS col_0_0_
FROM Analisi analisi0_
  INNER JOIN Paziente paziente1_ ON analisi0_.ID_PAZIENTE = paziente1_.ID_PAZIENTE
WHERE (paziente1_.nome LIKE 'MARIA%')

我还尝试在分析中添加索引。ID_PAZIENTE，但没有好的结果。是否有提高绩效的方法？

这是在我看来没问题的腐蚀性解释

CREATE TABLE ANALISI
(
   ID_ANALISI               INT             UNSIGNED NOT NULL AUTO_INCREMENT,
   ID_PAZIENTE              INT             UNSIGNED NOT NULL,
   ID_SESSIONE              INT             UNSIGNED NOT NULL,
   TRACCIATO                TINYINT         UNSIGNED NOT NULL,
   CAMPIONE                 VARCHAR(30),
   ID_PATOLOGICO            TINYINT         UNSIGNED,
   REPARTO                  VARCHAR(40),
   TOTALE_PROTEINE          FLOAT,   
   RAPP_AG                  FLOAT,
   ID_ANALISI_LINK          INT             UNSIGNED,
   ID_ANALISI_IFE           INT             UNSIGNED,   
   ID_ANALISI_DATI          INT             UNSIGNED,  
   ID_ANALISI_NOTA          INT             UNSIGNED,
   DATA_MODIFICA            DATETIME,      
   ID_UTENTE_MODIFICA       SMALLINT        UNSIGNED,
   DATA_VALIDAZIONE         DATETIME,
   ID_TIPO_VALIDAZIONE      TINYINT         UNSIGNED NOT NULL,
   ID_UTENTE_VALIDAZIONE    SMALLINT        UNSIGNED,   
   DATA_CANCELLAZIONE       DATETIME,
   ID_UTENTE_CANCELLAZIONE  SMALLINT        UNSIGNED,      
   PRIMARY KEY (ID_ANALISI), 
   INDEX IDX_CAMPIONE (CAMPIONE),
   INDEX IDX_REPARTO (REPARTO),   
   CONSTRAINT FK_ANALISI_PAZIENTE             FOREIGN KEY (ID_PAZIENTE)             REFERENCES PAZIENTE(ID_PAZIENTE),
   CONSTRAINT FK_ANALISI_SESSIONE             FOREIGN KEY (ID_SESSIONE)             REFERENCES SESSIONE(ID_SESSIONE),
   CONSTRAINT FK_ANALISI_PATOLOGICO           FOREIGN KEY (ID_PATOLOGICO)           REFERENCES PATOLOGICO(ID_PATOLOGICO),
   CONSTRAINT FK_ANALISI_TIPO_VALIDAZIONE     FOREIGN KEY (ID_TIPO_VALIDAZIONE)     REFERENCES TIPO_VALIDAZIONE(ID_TIPO_VALIDAZIONE),
   CONSTRAINT FK_ANALISI_UTENTE_MODIFICA      FOREIGN KEY (ID_UTENTE_MODIFICA)      REFERENCES UTENTE(ID_UTENTE), 
   CONSTRAINT FK_ANALISI_UTENTE_VALIDAZIONE   FOREIGN KEY (ID_UTENTE_VALIDAZIONE)   REFERENCES UTENTE(ID_UTENTE), 
   CONSTRAINT FK_ANALISI_UTENTE_CANCELLAZIONE FOREIGN KEY (ID_UTENTE_CANCELLAZIONE) REFERENCES UTENTE(ID_UTENTE),    
   CONSTRAINT FK_ANALISI_ANALISI_LINK         FOREIGN KEY (ID_ANALISI_LINK)         REFERENCES ANALISI(ID_ANALISI),
   CONSTRAINT FK_ANALISI_ANALISI_IFE          FOREIGN KEY (ID_ANALISI_IFE)          REFERENCES ANALISI_IFE(ID_ANALISI_IFE),
   CONSTRAINT FK_ANALISI_ANALISI_NOTA         FOREIGN KEY (ID_ANALISI_NOTA)         REFERENCES ANALISI_NOTA(ID_ANALISI_NOTA),
   CONSTRAINT FK_ANALISI_ANALISI_DATI         FOREIGN KEY (ID_ANALISI_DATI)         REFERENCES ANALISI_DATI(ID_ANALISI_DATI)
)
ENGINE=InnoDB;

CREATE TABLE PAZIENTE
(
   ID_PAZIENTE          INT           UNSIGNED NOT NULL AUTO_INCREMENT,
   ID_PAZIENTE_LAB      VARCHAR(20),
   COGNOME              VARCHAR(30),
   NOME                 VARCHAR(30),
   DATA_NASCITA         DATE,
   ID_SESSO             TINYINT UNSIGNED NOT NULL,
   RECAPITO             VARCHAR(50),
   CODICE_FISCALE       VARCHAR(30),
   ID_SPECIE            TINYINT UNSIGNED NOT NULL,
   PRIMARY KEY (ID_PAZIENTE), 
   INDEX IDX_DATA_NASCITA (DATA_NASCITA),
   INDEX IDX_COGNOME (COGNOME),
   INDEX IDX_NOME (NOME),
   INDEX IDX_SESSO (ID_SESSO),
   CONSTRAINT FK_PAZIENTE_SPECIE FOREIGN KEY (ID_SPECIE) REFERENCES SPECIE(ID_SPECIE),  
   CONSTRAINT FK_PAZIENTE_SESSO FOREIGN KEY (ID_SESSO) REFERENCES SESSO(ID_SESSO)   
)
ENGINE=InnoDB;

Answer 1

在InnoDB中，每个索引都隐式包含主键。

解释计划显示索引IDX_NOME用于表 Paziente。DBMS 在索引中查找名称，并在其中查找ID_PAZIENTE，这是我们访问另一个表所需的密钥。（在另一个DBMS中，我们会添加一个复合索引（NOME，ID_PAZIENTE）来发生这种情况。

然后还有表< code >分析要考虑。我们通过< code>FK_ANALISI_PAZIENTE找到一条记录，该记录包含用于查找匹配项的< code>ID_PAZIENTE,以及可用于访问表的隐含主键< code>ID_ANALISI,但这甚至不是必需的，因为我们已经从索引中获得了所需的所有信息。我们不需要在表中找到任何东西。(同样，在另一个DBMS中，我们将在< code>(ID_PAZIENTE，ID_ANALISI)上添加一个复合索引，以获得一个覆盖索引。)

所以所发生的只是：读取一个索引，然后读取另一个索引进行计数。完美的没有什么可补充的。

我们可以将 COUNT（analisi0_.ID_ANALISI） 替换为 COUNT（*），因为前者只说“ID_ANALISI不为空的计数记录”，这总是如此，因为ID_ANALISI是表的主键。因此，使用后者并说“计数记录”更简单。但是，我不认为这会显着加快查询速度。

所以从查询的角度来看，没有什么可以加快速度。下面是我想到的更多的事情：

• 分区表？不，我看不出这有什么好处。如果在并行线程中执行查询可能会更快，但据我所知，MySQL中没有在多个分区上并行执行。（不过我可能错了。）
• 碎片整理表？不，表本身甚至在查询中都没有被访问。
• 这就给我们留下了：买更好的硬件。（抱歉没有更好的建议给你。）