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

[dafuyang]

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

[opengps]

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

[dafuyang]

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

[opengps]

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

[wolfie]

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

[Karte]

排序可以分为:
1. 全字段排序. 指的是将查询的所有字段都存入 sort_buffer 然后进行排序, 如果数据过大, 会使用分治算法将数据拆分为小块存到磁盘进行排序.
2. rowId 排序. 排序时只将排序字段和当前主键 id (没有就是 rowId) 进行排序, 排序完成通过主键进行排序返回.

具体选择的是哪种排序则是通过你字段的长度判断的, 在 InnoDB 中有一个 max_length_for_sort_data 用于表示使用何种排序. 超过这个字段的值就会使用 rowId 排序.
同样, 还有 sort_buffer_size 用于控制 sorf_buffer 的大小.

[Karte]

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

[Karte]

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

[xiangyuecn]

简单粗暴的解决办法：
1. 先走索引查出主键
2. 再走聚集索引查出需要的字段数据

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

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

结论：给简单的字段加索引就可以了，有什么好优化的😂

[zoharSoul]

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

[shanghai1943]

赞同 8 楼老哥的做法

[dafuyang]

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

[zoharSoul]

@dafuyang #11 不会的,

[dafuyang]

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

[wxf666]

@xiangyuecn 这个不就是楼主所说的『强制使用 d 索引』嘛。。

另外，为嘛要分开写呢？直接 `select a,b,c,d from table order by d desc limit 0,100;` 不行吗？

[jeesk]

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

[jeesk]

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

[xiangyuecn]

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

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

[wxf666]

@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]

楼上方案差不多了 所以我比较好奇 D 是什么数据?
时间? 还是什么类型字符串还是 int? 这个跟具体的方案还是有区别
还有就是你所说的效率不高到什么程度多少毫秒?

补充说明一个你的方案 abcd 联合索引没有意义只会拖慢写入时间 只是单纯为这一个 sql 减少回表的话意义不大

[paireye]

大字段可以单独放一张表去处理

[dafuyang]

@xiangyuecn 大哥，刚刚用你说的语句试了一下，发现第二步的查询没有排序啊。。

[dafuyang]

@8355 d 是一个自定义排序字段 int ，刚刚试了一下 8 楼老哥的方法发现第二步查询没有排序。。

[860670496]

@dafuyang 肯定不排序啊，子查询的排序跟你主查询的排序一毛钱关系都没有，8 楼老哥只是帮你解决索引的问题，排序是另外一个问题，#17 已经给你写出来了，order by 要写在主查询里