mysql - 提高 MySQL 中的查询性能

Question

我发布此主题是为了获得有关我的 SQL 查询性能的一些建议。我实际上有 2 个表，一个称为 HGVS_SNP，大约有 44657169 行，另一个在 run 表上，平均有 2000 行。当我尝试更新我的 run table 的字段 Comment 时，执行查询需要花费很多时间。我想知道是否有任何方法可以增加我的 SQL 查询。

HGVS_SNP表的结构:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| snp_id    | int(11)     | YES  | MUL | NULL    |       |
| hgvs_name | text        | YES  |     | NULL    |       |
| source    | varchar(8)  | NO   |     | NULL    |       |
| upd_time  | varchar(32) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

我的运行表结构如下:

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| ID                   | varchar(7)   | YES  |     | NULL    |       |
| Reference            | varchar(7)   | YES  | MUL | NULL    |       |
| HGVSvar2             | varchar(120) | YES  | MUL | NULL    |       |
| Comment              | varchar(120) | YES  |     | NULL    |       |
| Compute              | varchar(20)  | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+

这是我的查询:

UPDATE run
INNER JOIN SNP_HGVS 
ON run.HGVSvar2=SNP_HGVS.hgvs_name
SET run.Comment=concat('rs',SNP_HGVS.snp_id) WHERE run.Compute not like 'tron'

Answer 1

我猜，因为您加入了一个带有 VARCHAR(120) 列的文本列，所以您实际上并不需要文本列。将其设为 VARCHAR，以便您可以对其编制索引

ALTER TABLE `HGVS_SNP` modify hgvs_name VARCHAR(120);

ALTER TABLE `HGVS_SNP` ADD KEY  idx_hgvs_name (hgvs_name);

这在大表上需要一段时间

现在你的 JOIN 应该快得多，还要在计算列上添加索引

ALTER TABLE `run` ADD KEY  idx_compute  (compute);

LIKE不需要，改成

WHERE run.Compute != 'tron'