V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
zephyru
V2EX  ›  MySQL

求教一个 sql 优化问题

  •  
  •   zephyru · 162 天前 · 1441 次点击
    这是一个创建于 162 天前的主题,其中的信息可能已经有所发展或是发生改变。

    使用了 nodejs 的 orm 库 sequelize ,在一条查询中生成了如下 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 似乎会很麻烦..求解

    5 条回复    2022-06-20 09:56:25 +08:00
    akira
        1
    akira  
       162 天前
    `ImagesTags` AS `tags->ImagesTags` INNER JOIN `imgTags` AS `tags` ON `tags`.`id` = `tags->ImagesTags`.`TagId`

    这个是啥来的,没见过呢
    blankmiss
        2
    blankmiss  
       162 天前
    ImagesTags AS tags->ImagesTags 是什么意思?
    pengtdyd
        3
    pengtdyd  
       162 天前
    mysql 如果一个表的数据经常修改或者增加那么添加索引是起不到什么作用的
    lookStupiToForce
        4
    lookStupiToForce  
       162 天前
    @akira #1 盲猜这是 mysql 方言
    ImagesTags 是张表,这个子查询省略了 select *。

    @zephyru op ,有两个法子你试下
    1. 把 ImagesTags 这张表整个子查询解开,放到外面去 left join
    2. mysql 如果支持 lateral join 就把 lateral 加上,在这个子查询里加上 where `tags->ImagesTags`.`ImageId` = `Images`.`id`
    Dlin
        5
    Dlin  
       162 天前
    `tags->ImagesTags`.`ImageId`
    这是获取 json 结构中的某个属性值,是走不了索引的,当然就要全表扫描啦。

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

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

    具体使用方法可以去百度。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3737 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 36ms · UTC 04:49 · PVG 12:49 · LAX 20:49 · JFK 23:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.