最近学习了一下 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 索引,或者创建我上面说的联合索引进行索引覆盖外,还有没有别的方法呢,另外,当一个字段保存的数据较多时,有必要创建联合索引吗,好像重复了上面的疑问。。求解。。
1
opengps 2022-11-24 05:56:06 +08:00 via Android
b 既然过大,不建议加入联合索引,abcd 联合索引,索引大小甚至等于整个表,加大了一倍存储占用,如果真想用联合索引一定要把 b 放在最后一个(最末使用)。
必要的话 b 可以单独一个表用单独的索引,校门用于接近全文索引的场景 |
4
wolfie 2022-11-24 09:29:30 +08:00
大字段
- 全部匹配:加个 md5 字段。 - 前缀匹配:前缀索引(前提前缀重复性小) |
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 的大小. |
6
Karte 2022-11-24 09:38:51 +08:00
补充下, rowId 排序好之后会通过主键回表将数据返回. 上面说错了.
|
7
Karte 2022-11-24 09:46:47 +08:00
你可以通过设定 max_length_for_sort_data 强制走 rowId 排序, 这时候只有 2 个字段参与了排序. 而全字段排序则会导入你查询的所有列数据, 体积差异比较大.
在 rowId 排序完后会自动回表查询数据. 这样走的话应该不会触发 filesort 排序 |
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 ) 结论:给简单的字段加索引就可以了,有什么好优化的😂 |
9
zoharSoul 2022-11-24 10:28:25 +08:00
都建立了 d 的索引了, 不会 filesort
|
10
shanghai1943 2022-11-24 10:59:44 +08:00
赞同 8 楼老哥的做法
|
13
dafuyang OP @xiangyuecn 老哥这个方案我看行,不过你结论说的给简单字段加索引就可以,但是在我这个提问的查询下不是没用上吗,求解啥意思
|
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;` 不行吗? |
15
jeesk 2022-11-24 11:32:12 +08:00
可能会很大, 到底有多大? 如果类似于文章这种其实建议使用 es search 去单独索引了。Mysql 字段内容太大, 内存也抗不住。
|
16
jeesk 2022-11-24 11:33:53 +08:00
千万不要类比 wordpress 用 myslq 能够抗住搜索,wordpress 的搜索文章顶多几 w 条数据而已。
|
17
xiangyuecn 2022-11-24 13:35:03 +08:00
@wxf666 #14 select * from xxx where 主键 in ( 子查询 ) order by 相同排序
通吃,查询性能比较稳定,有时候比较慢的查询 换个写法 查询速度立马就快了,优化器比较弱智 换一下写法往往有奇效 不单是 mysql ,常见数据库都适用,优先查询出尽可能小和少的数据,在通过主键查询出需要的数据。如果通过主键来读取数据都是很慢,怎么优化 sql 都是白搭 |
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)` ,感觉性能可以更好啊?(至少能减少数据传输?) |
19
8355 2022-11-24 16:10:53 +08:00
楼上方案差不多了 所以我比较好奇 D 是什么数据?
时间? 还是什么类型字符串还是 int? 这个跟具体的方案还是有区别 还有就是你所说的效率不高到什么程度多少毫秒? 补充说明一个你的方案 abcd 联合索引没有意义只会拖慢写入时间 只是单纯为这一个 sql 减少回表的话意义不大 |
20
paireye 2022-11-25 14:06:46 +08:00
大字段可以单独放一张表去处理
|
21
dafuyang OP @xiangyuecn 大哥,刚刚用你说的语句试了一下,发现第二步的查询没有排序啊。。
|