求教一个 sql 优化问题

SELECT `Images`.*, `tags`.`id` AS `tags.id`, `tags`.`name` AS `tags.name`, `tags`.`translatedName` AS `tags.translatedName`, `tags`.`customName` AS `tags.customName`, `author`.`id` AS `author.id`, `author`.`name` AS `author.name`, `author`.`profileImageUrl` AS `author.profileImageUrl`   


    FROM (SELECT `Images`.`id`, `Images`.`title`, `Images`.`previewUrl`, `Images`.`totalBookmarks`, `Images`.`totalView`, `Images`.`originUrlJson`, `Images`.`authorId` FROM `imgStorage` AS `Images`
            WHERE `Images`.`authorId` = 1096811 
            ORDER BY `Images`.`totalBookmarks` 
            DESC LIMIT '0', '30') AS `Images` 
    LEFT OUTER JOIN ( `ImagesTags` AS `tags->ImagesTags` INNER JOIN `imgTags` AS `tags` ON `tags`.`id` = `tags->ImagesTags`.`TagId`) 
    ON `Images`.`id` = `tags->ImagesTags`.`ImageId` 
    LEFT OUTER JOIN `Author` AS `author` ON `Images`.`authorId` = `author`.`id` ORDER BY `Images`.`totalBookmarks` DESC

EXPLAIN QUERY PLAN SELECT 发现会触发 ImagesTags AS tags->ImagesTags 的全表扫描，随着数据量增加会变的非常慢
几乎所有涉及通过 Images ImagesTags 关联 Tag 的情况下都发生
于是尝试在 ImagesTags 建立如下索引 [ImageId ，TagId],[TagId,ImageId],[ImageId],[TagId]
发现不管是都建还是分别只建一个都不生效
于是想问下，这种情况有办法通过建立索引来解决么？如果可以应该怎么建？
我现在通过，单独查出 Images 然后每一项单独通过 ImagesTags 查 tags 能极大的解决性能问题，但后面如果想再通过 tag 来过滤 Images 似乎会很麻烦..求解

images

imagestags

tags

author

5 条回复 • 2022-06-20 09:56:25 +08:00

akira

2022-06-19 22:20:07 +08:00

`ImagesTags` AS `tags->ImagesTags` INNER JOIN `imgTags` AS `tags` ON `tags`.`id` = `tags->ImagesTags`.`TagId`

这个是啥来的，没见过呢

blankmiss

2022-06-20 04:44:15 +08:00

ImagesTags AS tags->ImagesTags 是什么意思？

pengtdyd

2022-06-20 06:24:02 +08:00

mysql 如果一个表的数据经常修改或者增加那么添加索引是起不到什么作用的

lookStupiToForce

2022-06-20 08:17:19 +08:00

@akira #1 盲猜这是 mysql 方言
ImagesTags 是张表，这个子查询省略了 select *。

@zephyru op ，有两个法子你试下
1. 把 ImagesTags 这张表整个子查询解开，放到外面去 left join
2. mysql 如果支持 lateral join 就把 lateral 加上，在这个子查询里加上 where `tags->ImagesTags`.`ImageId` = `Images`.`id`

Dlin

2022-06-20 09:56:25 +08:00

`tags->ImagesTags`.`ImageId`
这是获取 json 结构中的某个属性值，是走不了索引的，当然就要全表扫描啦。

你可以对此属性单独建一个字段保存，并建立索引。

或是通过 mysql 得字段映射来映射 json 中的属性到某个字段：
<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

具体使用方法可以去百度。