单表千万数据，搜索条件跨越多表，并发不高，如何优化？

Question

如上图示例：

1. 搜索条件跨越多张表（三张或三张以上），条件复杂；
2. 单表数据1千万左右；
3. 列表数据来源多张表（三张或三张以上）；
4. 列表分页按时间排序；
5. 并发并不是很大；

历史原因：

1. 现在不想重构表结构，动的太大；

现在做法：

1. 列表(不带搜索条件)只能单表查询，两张表join已经出现时间问题了，然后再独立查询其他表；
2. 带搜索并且搜索条件跨越多表的列表，暂时是join查询，30多秒出来数据；

技术栈：LNMP

问题：
因为是做的后台项目，整个系统几乎都是这样类似的页面；
如何优化呢？
或者采用什么技术能解决此场景下的性能瓶颈问题？

多谢各位大神！

Answer 1

单表千万已经是比较大的数据量了，提几个思路：
1、数据能否归档，如只保留最近3个月数据，将单表的数据量降下来；
2、查询条件限制有必填字段，且这些字段在数据库中有合适的索引；
3、考虑表适当做字段冗余，避免表关联查询；

硬件方面如有条件，表数据文件放到SSD硬盘。

Answer 2

千万级数据 30多秒查询

提供一下几个观点：

• 硬件层优化：SSD

• 数据层优化：

  • 分表：可以考虑以某个关键值为 hash 进行分表，来降低单个表的数据量；
  • 索引：join 关联的条件，所建索引是否合适；
  • SQL 优化：现有的 SQL 是否存在优化空间？是否能用到索引的，没有用索引？是否只查必要的字段值？
  • 字段冗余：因为某个字段而引起的大表联结，可以考虑新增字段，适当冗余；
  • 读写分离；
  • 是否可以将联表的 SQL 在业务层优化为多条 SELECT

• 结构层优化：

  • 是否可以将查询的数据做缓存，比如以 SQL hash 结果为 key，以查询结果为 value，存入 redis

Answer 3

只要join的时候能确保用到被连接表的主键或者唯一索引，其实join并不慢

Answer 4

联查的时候确保用上主键与外键， 建相对应的搜索字段的索引

Answer 5

1.读写分离
2.建立相关的索引字段
3.常用列表页和搜索项建立缓存机制
4.加硬件吧(服务器问题影响很大)。

Answer 6

补充点，排序最好不要用order by created_at desc,改用自增主键排序order by id desc,效果一样，性能差多

Answer 7

用搜索引擎吧

Answer 8

仅供参考

Answer 9

有模糊搜索吧？
sphinx
阿里云的话走开放搜索

Answer 10

1. 把常用的查询字段做成多字段索引
2. 只查询需要的列
3. 查询的时候把限制范围最长的字段放在最前面

Answer 11

如果索引做的差不多，但是效果并不是很明显的话，可以使用一些sql的函数替代本身的sql查询，达到sql拆分的目的（exists 替代in查询等），其他的也没有什么太好的建议
ps：作为一个问题的读者，请再具体一下问题的描述，这种描述其实很笼统，没有办法直接定位问题的所在

Answer 12

根据描述其实只能给出比较通用的优化方式，建议给出性能问题比较严峻的表的表结构，以及查询使用的SQL

Answer 13

我也面临过这种环境，会员优惠券单表达到快千万级，像like之类的业务，速度慢就不用说了。常用字段添加索引的方式，起到很大作用；并发使用的业务，最好能够减少对数据表的频繁使用。最好能根据业务分离一下数据。当然，服务器配置的强大，是根本。

Answer 14

“不带搜索条件，两张表join都出现时间问题了”

单表千万，但只要索引合理join也是没有任何性能问题的，这锅不是千万数据量和join的。

多有是你join的字段的索引问题，explain一下那条sql看看，对照着结果优化一下索引。

如果join字段两边已经都是索引，那么就该升配置了

Answer 15

单表千万级数据，是需要开始考虑分表的问题了！

就目前形式快速解决提供我的几个思路：

• 所有表关联用到 join 的地方不要用 join （我们都知道多张表使用join是做笛卡尔积的，想象下多恐怖）所以不要用join，换成单表查
• 用到其他表的数据取范围查询所需字段对应的值（如分页50/页，该50条数据的某个ID和其他表的值做表驱动查询）
• 注意索引，是否有不合适的索引和一些必要索引的建立（利用expain查看性能分析）
• 数据归档，历史数据（比如将2016年前的数据独立出去）的拆分
• 分页需要考虑总数不要查sql，写个固定的常量值（根据你业务场景）
• 其他的有些人都回答了可以参考思路

Answer 16

刚好也在做这么一个分表改造,我的业务实际比你还复杂,我针对app接口查询维度,分成3类表,并且是每类表都是拆分n个表,这就不细说了,我拆成3类表是为了app查询方便,同时后台聚合列表数据因为实时要求不高,我用搜索引擎来处理