MySQL在两个表之间进行简单查询太慢 [英] Mysql too slow on simple query between two tables
问题描述
早上好， 我有两个表，具有1462632条记录的ANALISI和具有1408146条记录的PAZIENTE，使用PAZIENTE索引之一进行的这种简单计数需要大约30秒才能返回大约65000条记录
Good morning, I've two tables, ANALISI with 1462632 records and PAZIENTE with 1408146 records, this simple count using one of the index of PAZIENTE require about 30 seconds to give back about 65000 records
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%')
我也尝试在analisi0_.ID_PAZIENTE上添加索引，但效果不佳. 有没有办法提高性能?
I've also tried adding an index on analisi0_.ID_PAZIENTE but with no good results. Is there a way to enhance performance?
这对我来说似乎可以解释
This is the corrisponding explain that seems ok to me
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;
推荐答案
在InnoDB中，每个索引都隐式包含主键.
In InnoDB every index contains the primary key implicitly.
解释计划显示表Paziente上使用了索引IDX_NOME. DBMS在索引中查找名称，然后在索引中找到ID_PAZIENTE，这是我们访问另一个表所需的键.因此，没有什么可添加的. (在另一个DBMS中，我们会在(NOME, ID_PAZIENTE)上添加一个复合索引，以实现此目的.)
The explain plan shows that index IDX_NOME is used on table Paziente. The DBMS looks up the name in the index and finds ID_PAZIENTE in there, which is the key we need to access the other table. So there is nothing to add. (In another DBMS we would have added a composite index on (NOME, ID_PAZIENTE) for this to happen.)
然后是要考虑的表Analisi.我们通过FK_ANALISI_PAZIENTE找到一条记录，其中包含用于查找匹配项的ID_PAZIENTE和隐式主键ID_ANALISI(可用于访问该表)，但这不是必需的，因为我们拥有所有我们从索引中获取的信息.表格中没有任何剩余内容. (同样，在另一个DBMS中，我们将在(ID_PAZIENTE, ID_ANALISI)上添加一个复合索引以具有覆盖索引.)
Then there is table Analisi to consider. We find a record via FK_ANALISI_PAZIENTE which contains the ID_PAZIENTE which is used to find the match, and implicitly the primary key ID_ANALISI which could be used to access the table, but this is not even necessary, beacuse we have all information we need from the index. There is nothing left that we need to find in the table. (Again, in another DBMS we would have added a composite index on (ID_PAZIENTE, ID_ANALISI) to have a covering index.)
所以发生的事情仅仅是:读取一个索引以便读取另一个索引以便计数.完美的.没有什么可添加的.
So what happens is merely: read one index in order to read the other index in order to count. Perfect. There is nothing to add.
我们可以用COUNT(*)替换COUNT(analisi0_.ID_ANALISI)，因为前者只说计数ID_ANALISI不为空的记录"，这总是如此，因为ID_ANALISI是表的主要对象钥匙.因此，使用后者并说计数记录"更为简单.但是，我完全不希望这会大大加快查询的速度.
We could replace COUNT(analisi0_.ID_ANALISI) with COUNT(*) as the former only says "count records where ID_ANALISI is not null", which is always the case as ID_ANALISI is the table's primary key. So it's simpler to use the latter and say "count records". However, I don't expect this to speed up the query significantly if at all.
因此，从查询的角度来看，没有什么可以加快此速度的.这是我想到的其他事情:
So from a query point of view, there is nothing to speed this up. Here are further things that come to mind:
- 分区表?不，我认为这样做没有任何好处.那时在并行线程中执行查询可能会更快，但是据我所知，MySQL中的多个分区上没有并行执行. (尽管我可能是错的.)
- 整理表碎片?不，在查询中甚至无法访问表本身.
- 这给我们留下了:购买更好的硬件. (很抱歉没有更好的建议给您.)
这篇关于MySQL在两个表之间进行简单查询太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
