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

最近学习了一下 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 索引，或者创建我上面说的联合索引进行索引覆盖外，还有没有别的方法呢，另外，当一个字段保存的数据较多时，有必要创建联合索引吗，好像重复了上面的疑问。。求解。。

索引

字段

创建

SQL

23 条回复 • 2022-12-29 09:38:52 +08:00

opengps

2022-11-24 05:56:06 +08:00 via Android

b 既然过大，不建议加入联合索引，abcd 联合索引，索引大小甚至等于整个表，加大了一倍存储占用，如果真想用联合索引一定要把 b 放在最后一个(最末使用)。
必要的话 b 可以单独一个表用单独的索引，校门用于接近全文索引的场景

dafuyang

2022-11-24 09:19:29 +08:00 via Android

@opengps 那正常情况下要干掉这个 filesort 要采用哪种方案呢🤔

opengps

2022-11-24 09:22:11 +08:00

@dafuyang 如果你这个表很大，那么常见方案不再是 sql 层面，考虑换其他全文索引查询方案，比如 ES

wolfie

2022-11-24 09:29:30 +08:00

大字段
- 全部匹配：加个 md5 字段。
- 前缀匹配：前缀索引（前提前缀重复性小）

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

2022-11-24 09:38:51 +08:00

补充下, rowId 排序好之后会通过主键回表将数据返回. 上面说错了.

Karte

2022-11-24 09:46:47 +08:00

你可以通过设定 max_length_for_sort_data 强制走 rowId 排序, 这时候只有 2 个字段参与了排序. 而全字段排序则会导入你查询的所有列数据, 体积差异比较大.
在 rowId 排序完后会自动回表查询数据. 这样走的话应该不会触发 filesort 排序

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

2022-11-24 10:28:25 +08:00

都建立了 d 的索引了, 不会 filesort

shanghai1943

2022-11-24 10:59:44 +08:00

赞同 8 楼老哥的做法

dafuyang

2022-11-24 11:03:00 +08:00

@zoharSoul 没有索引覆盖会 filesort 的。。不然我发帖来问干嘛。。

zoharSoul

2022-11-24 11:04:14 +08:00

@dafuyang #11 不会的,

dafuyang

2022-11-24 11:05:43 +08:00

@xiangyuecn 老哥这个方案我看行，不过你结论说的给简单字段加索引就可以，但是在我这个提问的查询下不是没用上吗，求解啥意思

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

2022-11-24 11:32:12 +08:00

可能会很大，到底有多大？如果类似于文章这种其实建议使用 es search 去单独索引了。Mysql 字段内容太大，内存也抗不住。

jeesk

2022-11-24 11:33:53 +08:00

千万不要类比 wordpress 用 myslq 能够抗住搜索，wordpress 的搜索文章顶多几 w 条数据而已。

xiangyuecn

2022-11-24 13:35:03 +08:00

@wxf666 #14 select * from xxx where 主键 in ( 子查询 ) order by 相同排序
通吃，查询性能比较稳定，有时候比较慢的查询换个写法查询速度立马就快了，优化器比较弱智换一下写法往往有奇效

不单是 mysql ，常见数据库都适用，优先查询出尽可能小和少的数据，在通过主键查询出需要的数据。如果通过主键来读取数据都是很慢，怎么优化 sql 都是白搭

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)` ，感觉性能可以更好啊？（至少能减少数据传输？）