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
dafuyang
V2EX  ›  MySQL

兄弟们,关于 sql 优化的问题想请教一下

  •  
  •   dafuyang · 2022-11-24 02:51:38 +08:00 · 2929 次点击
    这是一个创建于 490 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近学习了一下 sql 语句的优化入门,这里有个场景存在疑惑:
    比如一个简单的查询,比如
    select a,b,c,d from table order by d desc limit 0,100;
    在 d 字段上创建普通索引,另外由于 b 字段可能会保存很大的数据,所以我认为创建联合索引(a,b,c,d)进行索引覆盖查询可能不好?
    问题如下:
    1.这个查询属于 filesort ,效率不高,在表数据量不大的时候不会暴露问题,但是在数据量大的时候,时间就长了,这个场景下改如何优化呢?
    2.我目前只知道强制使用 d 索引,或者创建我上面说的联合索引进行索引覆盖外,还有没有别的方法呢,另外,当一个字段保存的数据较多时,有必要创建联合索引吗,好像重复了上面的疑问。。求解。。

    23 条回复    2022-12-29 09:38:52 +08:00
    opengps
        1
    opengps  
       2022-11-24 05:56:06 +08:00 via Android
    b 既然过大,不建议加入联合索引,abcd 联合索引,索引大小甚至等于整个表,加大了一倍存储占用,如果真想用联合索引一定要把 b 放在最后一个(最末使用)。
    必要的话 b 可以单独一个表用单独的索引,校门用于接近全文索引的场景
    dafuyang
        2
    dafuyang  
    OP
       2022-11-24 09:19:29 +08:00 via Android
    @opengps 那正常情况下要干掉这个 filesort 要采用哪种方案呢🤔
    opengps
        3
    opengps  
       2022-11-24 09:22:11 +08:00
    @dafuyang 如果你这个表很大,那么常见方案不再是 sql 层面,考虑换其他全文索引查询方案,比如 ES
    wolfie
        4
    wolfie  
       2022-11-24 09:29:30 +08:00
    大字段
    - 全部匹配:加个 md5 字段。
    - 前缀匹配:前缀索引(前提前缀重复性小)
    Karte
        5
    Karte  
       2022-11-24 09:37:48 +08:00
    排序可以分为:
    1. 全字段排序. 指的是将查询的所有字段都存入 sort_buffer 然后进行排序, 如果数据过大, 会使用分治算法将数据拆分为小块存到磁盘进行排序.
    2. rowId 排序. 排序时只将排序字段和当前主键 id (没有就是 rowId) 进行排序, 排序完成通过主键进行排序返回.

    具体选择的是哪种排序则是通过你字段的长度判断的, 在 InnoDB 中有一个 max_length_for_sort_data 用于表示使用何种排序. 超过这个字段的值就会使用 rowId 排序.
    同样, 还有 sort_buffer_size 用于控制 sorf_buffer 的大小.
    Karte
        6
    Karte  
       2022-11-24 09:38:51 +08:00
    补充下, rowId 排序好之后会通过主键回表将数据返回. 上面说错了.
    Karte
        7
    Karte  
       2022-11-24 09:46:47 +08:00
    你可以通过设定 max_length_for_sort_data 强制走 rowId 排序, 这时候只有 2 个字段参与了排序. 而全字段排序则会导入你查询的所有列数据, 体积差异比较大.
    在 rowId 排序完后会自动回表查询数据. 这样走的话应该不会触发 filesort 排序
    xiangyuecn
        8
    xiangyuecn  
       2022-11-24 09:54:12 +08:00
    简单粗暴的解决办法:
    1. 先走索引查出主键
    2. 再走聚集索引查出需要的字段数据

    select 主键 from table order by d desc limit 0,100

    select * from table where 主键 in(xxx, xxx, xxx, xxx )

    结论:给简单的字段加索引就可以了,有什么好优化的😂
    zoharSoul
        9
    zoharSoul  
       2022-11-24 10:28:25 +08:00
    都建立了 d 的索引了, 不会 filesort
    shanghai1943
        10
    shanghai1943  
       2022-11-24 10:59:44 +08:00
    赞同 8 楼老哥的做法
    dafuyang
        11
    dafuyang  
    OP
       2022-11-24 11:03:00 +08:00
    @zoharSoul 没有索引覆盖会 filesort 的。。不然我发帖来问干嘛。。
    zoharSoul
        12
    zoharSoul  
       2022-11-24 11:04:14 +08:00
    @dafuyang #11 不会的,
    dafuyang
        13
    dafuyang  
    OP
       2022-11-24 11:05:43 +08:00
    @xiangyuecn 老哥这个方案我看行,不过你结论说的给简单字段加索引就可以,但是在我这个提问的查询下不是没用上吗,求解啥意思
    wxf666
        14
    wxf666  
       2022-11-24 11:15:03 +08:00
    @xiangyuecn 这个不就是楼主所说的『强制使用 d 索引』嘛。。

    另外,为嘛要分开写呢?直接 `select a,b,c,d from table order by d desc limit 0,100;` 不行吗?
    jeesk
        15
    jeesk  
       2022-11-24 11:32:12 +08:00
    可能会很大, 到底有多大? 如果类似于文章这种其实建议使用 es search 去单独索引了。Mysql 字段内容太大, 内存也抗不住。
    jeesk
        16
    jeesk  
       2022-11-24 11:33:53 +08:00
    千万不要类比 wordpress 用 myslq 能够抗住搜索,wordpress 的搜索文章顶多几 w 条数据而已。
    xiangyuecn
        17
    xiangyuecn  
       2022-11-24 13:35:03 +08:00
    @wxf666 #14 select * from xxx where 主键 in ( 子查询 ) order by 相同排序
    通吃,查询性能比较稳定,有时候比较慢的查询 换个写法 查询速度立马就快了,优化器比较弱智 换一下写法往往有奇效

    不单是 mysql ,常见数据库都适用,优先查询出尽可能小和少的数据,在通过主键查询出需要的数据。如果通过主键来读取数据都是很慢,怎么优化 sql 都是白搭
    wxf666
        18
    wxf666  
       2022-11-24 14:08:35 +08:00
    @xiangyuecn `select a,b,c,d from table order by d desc limit 0,100;` 不是先走索引 d 查出主键,再查出对应的 a, b, c, d 吗?

    实在不行,使用 `FORCE INDEX`,或者 `SELECT a, b, c, d FROM (SELECT id FROM table ORDER BY d DESC LIMIT 0, 100) t JOIN table USING(id)` ,感觉性能可以更好啊?(至少能减少数据传输?)
    8355
        19
    8355  
       2022-11-24 16:10:53 +08:00
    楼上方案差不多了 所以我比较好奇 D 是什么数据?
    时间? 还是什么类型字符串还是 int? 这个跟具体的方案还是有区别
    还有就是你所说的效率不高到什么程度多少毫秒?

    补充说明一个你的方案 abcd 联合索引没有意义只会拖慢写入时间 只是单纯为这一个 sql 减少回表的话意义不大
    paireye
        20
    paireye  
       2022-11-25 14:06:46 +08:00
    大字段可以单独放一张表去处理
    dafuyang
        21
    dafuyang  
    OP
       2022-11-25 16:25:13 +08:00
    @xiangyuecn 大哥,刚刚用你说的语句试了一下,发现第二步的查询没有排序啊。。
    dafuyang
        22
    dafuyang  
    OP
       2022-11-25 16:28:27 +08:00
    @8355 d 是一个自定义排序字段 int ,刚刚试了一下 8 楼老哥的方法发现第二步查询没有排序。。
    860670496
        23
    860670496  
       2022-12-29 09:38:52 +08:00
    @dafuyang 肯定不排序啊,子查询的排序跟你主查询的排序一毛钱关系都没有,8 楼老哥只是帮你解决索引的问题,排序是另外一个问题,#17 已经给你写出来了,order by 要写在主查询里
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3272 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 14:07 · PVG 22:07 · LAX 07:07 · JFK 10:07
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.