最近学习了一下 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 索引，或者创建我上面说的联合索引进行索引覆盖外，还有没有别的方法呢，另外，当一个字段保存的数据较多时，有必要创建联合索引吗，好像重复了上面的疑问。。求解。。
› 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
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 1410 days ago, the information mentioned may be changed or developed.
 |
1
opengps Nov 24, 2022 via Android
b 既然过大，不建议加入联合索引，abcd 联合索引，索引大小甚至等于整个表，加大了一倍存储占用，如果真想用联合索引一定要把 b 放在最后一个(最末使用)。
必要的话 b 可以单独一个表用单独的索引，校门用于接近全文索引的场景 |
 |
4
wolfie Nov 24, 2022
大字段
- 全部匹配：加个 md5 字段。 - 前缀匹配：前缀索引（前提前缀重复性小） |
 |
5
Karte Nov 24, 2022
排序可以分为:
1. 全字段排序. 指的是将查询的所有字段都存入 sort_buffer 然后进行排序, 如果数据过大, 会使用分治算法将数据拆分为小块存到磁盘进行排序. 2. rowId 排序. 排序时只将排序字段和当前主键 id (没有就是 rowId) 进行排序, 排序完成通过主键进行排序返回. 具体选择的是哪种排序则是通过你字段的长度判断的, 在 InnoDB 中有一个 max_length_for_sort_data 用于表示使用何种排序. 超过这个字段的值就会使用 rowId 排序. 同样, 还有 sort_buffer_size 用于控制 sorf_buffer 的大小. |
|
6
Karte Nov 24, 2022
补充下, rowId 排序好之后会通过主键回表将数据返回. 上面说错了.
|
|
7
Karte Nov 24, 2022
你可以通过设定 max_length_for_sort_data 强制走 rowId 排序, 这时候只有 2 个字段参与了排序. 而全字段排序则会导入你查询的所有列数据, 体积差异比较大.
在 rowId 排序完后会自动回表查询数据. 这样走的话应该不会触发 filesort 排序 |
 |
8
xiangyuecn Nov 24, 2022
简单粗暴的解决办法：
1. 先走索引查出主键 2. 再走聚集索引查出需要的字段数据 select 主键 from table order by d desc limit 0,100 select * from table where 主键 in(xxx, xxx, xxx, xxx ) 结论：给简单的字段加索引就可以了，有什么好优化的😂 |
 |
9
zoharSoul Nov 24, 2022
都建立了 d 的索引了, 不会 filesort
|
 |
10
shanghai1943 Nov 24, 2022
赞同 8 楼老哥的做法
|
 |
13
dafuyang OP @xiangyuecn 老哥这个方案我看行，不过你结论说的给简单字段加索引就可以，但是在我这个提问的查询下不是没用上吗，求解啥意思
|
 |
14
wxf666 Nov 24, 2022
@xiangyuecn 这个不就是楼主所说的『强制使用 d 索引』嘛。。
另外，为嘛要分开写呢？直接 `select a,b,c,d from table order by d desc limit 0,100;` 不行吗？ |
 |
15
jeesk Nov 24, 2022
可能会很大， 到底有多大？ 如果类似于文章这种其实建议使用 es search 去单独索引了。Mysql 字段内容太大， 内存也抗不住。
|
|
16
jeesk Nov 24, 2022
千万不要类比 wordpress 用 myslq 能够抗住搜索，wordpress 的搜索文章顶多几 w 条数据而已。
|
|
17
xiangyuecn Nov 24, 2022
@wxf666 #14 select * from xxx where 主键 in ( 子查询 ) order by 相同排序
通吃，查询性能比较稳定，有时候比较慢的查询 换个写法 查询速度立马就快了，优化器比较弱智 换一下写法往往有奇效 不单是 mysql ，常见数据库都适用，优先查询出尽可能小和少的数据，在通过主键查询出需要的数据。如果通过主键来读取数据都是很慢，怎么优化 sql 都是白搭 |
|
18
wxf666 Nov 24, 2022
@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 Nov 24, 2022
楼上方案差不多了 所以我比较好奇 D 是什么数据?
时间? 还是什么类型字符串还是 int? 这个跟具体的方案还是有区别 还有就是你所说的效率不高到什么程度多少毫秒? 补充说明一个你的方案 abcd 联合索引没有意义只会拖慢写入时间 只是单纯为这一个 sql 减少回表的话意义不大 |
 |
20
paireye Nov 25, 2022
大字段可以单独放一张表去处理
|
|
21
dafuyang OP @xiangyuecn 大哥，刚刚用你说的语句试了一下，发现第二步的查询没有排序啊。。
|
Select Language