代码之家 › 专栏 › 技术社区 › Cláudio Ribeiro

提高Mysql查询性能(使用explain在类型中索引)

query-performance performance mysql

Cláudio Ribeiro · 技术社区 · 6 年前

大家好,我有以下数据库:

`Users`
'id', 'char(36)', 'NO', 'PRI', NULL, ''
'password', 'varchar(64)', 'NO', '', NULL, ''
'email', 'varchar(60)', 'NO', 'UNI', NULL, ''
'roles', 'longtext', 'NO', '', NULL, ''
'is_active', 'tinyint(1)', 'NO', '', NULL, ''

`Galleries`
'id', 'char(36)', 'NO', 'PRI', NULL, ''
'user_id', 'char(36)', 'NO', 'MUL', NULL, ''
'name', 'varchar(255)', 'NO', '', NULL, ''
'description', 'longtext', 'YES', '', NULL, ''
'created_at', 'datetime', 'NO', '', NULL, ''

`Images`
'id', 'char(36)', 'NO', 'PRI', NULL, ''
'gallery_id', 'char(36)', 'YES', 'MUL', NULL, ''
'original_filename', 'varchar(255)', 'NO', '', NULL, ''
'filename', 'varchar(255)', 'NO', '', NULL, ''
'description', 'longtext', 'YES', '', NULL, ''

以及以下查询:

SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

使用时 explain 在这个查询中,我得到 index 导致 type 用户查询的字段。我的问题是,有什么方法可以改进这个查询,从而改进这个结果吗。我理解 指数 这意味着它将遍历该特定表上的所有条目,在这种情况下可能是必要的。

但你们有没有看到其他改进方法?

编辑:显示库和图像表的索引:

画廊:

'galleries', '0', 'PRIMARY', '1', 'id', 'A', '1', NULL, NULL, '', 'BTREE', '', ''
'galleries', '0', 'UNIQ_F70E6EB7BF396750', '1', 'id', 'A', '1', NULL, NULL, '', 'BTREE', '', ''
'galleries', '1', 'IDX_F70E6EB7A76ED395', '1', 'user_id', 'A', '1', NULL, NULL, '', 'BTREE', '', ''

图像:

'images', '0', 'PRIMARY', '1', 'id', 'A', '4', NULL, NULL, '', 'BTREE', '', ''
'images', '0', 'UNIQ_E01FBE6ABF396750', '1', 'id', 'A', '4', NULL, NULL, '', 'BTREE', '', ''
'images', '1', 'IDX_E01FBE6A4E7AF8F', '1', 'gallery_id', 'A', '4', NULL, NULL, 'YES', 'BTREE', '', ''

在没有用户表的情况下,对查询进行解释:

'1', 'SIMPLE', 'gal', NULL, 'ALL', 'PRIMARY,UNIQ_F70E6EB7BF396750', NULL, NULL, NULL, '1', '100.00', NULL
'1', 'SIMPLE', 'img', NULL, 'ref', 'IDX_E01FBE6A4E7AF8F', 'IDX_E01FBE6A4E7AF8F', '109', 'homestead.gal.id', '1', '25.00', 'Using where'

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

首先,您可以更改 left join 到 inner join --the where 子句已经在这样做了,但要明确。你也可以摆脱 users ,因为它没有被使用。

SELECT g.name, g.description, i.filename, i.description
FROM `homestead`.`galleries` g INNER JOIN 
     `homestead`.`images` i 
     ON i.gallery_id = g.id
WHERE i.description LIKE '%dog%';

如果索引处于打开状态,则此查询应该可以 images(gallery_id) .

如果性能确实是一个问题,那么您可能需要调查全文索引,以便可以更改 like 到 match() .

Rick James 6 年前

(这部分与戈登的回答不一致。)

自 WHERE 仅提及 images ( WHERE i.description LIKE '%dog%' ),优化器可能会从该表开始。没有常规索引对此有用。(戈登对 FULLTEXT 是改善这一点的“正确”方法。)

第二张桌子很容易找到,如果 id 是 PRIMARY KEY . 请提供 SHOW CREATE TABLE 我们可以看看你们有什么索引。

(我同意戈登的其余回答。)

另一个问题。。。如果表变得“巨大”,您会发现UUID的性能很差(因为随机性)。一百万行就可以了,十亿行会很慢。