SQL优化: 10张表相互关联,OR查询16个字段 (300分悬赏)

snoopycom 2012-07-18 03:25:30
目前手头需要解决这样的一个问题
数据库有10张表相互关联, 每张表10w条数据以上, 聚集索引在主键GUID.

客户需要查询10张表里面的40个字段, 查询条件是16个字段, 查询条件关系为OR, 需要最起码Like 'T%'这种类型的, 期望能够'%T%'的查询.
客户希望能够2秒内获得结果, 可以分页, 但是每页都2秒内返回.

最初查询需要接近2分钟, 现在使用全文搜索后还得要40秒, 有没有方法能够缩短到2秒内返回结果?

多谢各位, 帮顶也有分!

只能发100分, 解决了另送200分, 总计300分悬赏.
...全文
794 34 打赏 收藏 转发到动态 举报
写回复
用AI写文章
34 条回复
切换为时间正序
请发表友善的回复…
发表回复
haitao 2012-08-03
  • 打赏
  • 举报
 回复
把10个表的like后的结果分别存到临时表，加索引
再进行互相的关联（根据guid）
guid本身比较大，而且无序，如果不用多库合并，还是自动递增id的好
Net攻城狮 2012-08-03
  • 打赏
  • 举报
 回复
10个表，如果关联的表有 like 类似的浪费资源的 建议用子查询，如果十个表都有类似情况，建议把这种符合条件的放临时表这样关联起来就快了，可以试试
杰拉尔 2012-07-29
  • 打赏
  • 举报
 回复
主键能不能不用GUID ，逻辑可以都处理在程序中 最终拼成一句SQL直接执行。
snoopycom 2012-07-27
  • 打赏
  • 举报
 回复
多谢各位献计献策~
目前我采取的措施:
1. 重建索引
2. 使用UNION, 避免OR
3. 优化查询和分页
4. 全文索引

之后打算重构一下表结构, 减少关联.
yonyou_rm 2012-07-20
  • 打赏
  • 举报
 回复
1.尽量避免用OR，效率太低，可以考虑用其他逻辑替代；
2.将所有参与join on 的字段都建索引；——这个最为重要！！

按你目前的数据量估算，如果上面的做好了，10秒钟应该差不多能出来。
2秒钟，基本没戏！
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 27 楼 的回复:]

GUID是无序的。所以聚集索引在GUID上是严重的浪费，起不到一点效果。
但是sqlserver和oracle都有生成有序的GUID的机制。你可以把数据库中的主键全用有序的GUID重构一下
[/Quote]

很有启发, 我之前准备将聚集索引放在查询列上, 但是表之间的引用关系又比较麻烦, 如果改了的话对程序也有影响..

我试试有序的GUID~
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 26 楼 的回复:]

LIKE '%A%'之类的好象没法用索引。OR更是性能的大敌。LZ确实是走投无路了。

既然一个OR条件都很慢。建议检查下表基本结构，该定义的主键是否都定义了。

我曾经改善过一个存储过程，原来要20分种处理完的数据包括插入更新等等 经过优化只需要10几秒。里面用到了很多LEFT JOIN.说明LEFT JOIN效率还是可以的。没有传说中那么差。
[/Quote]

你用Left Join估计是系统自动优化为Loop join了, 另外你Join的列是不是有序的? 如果是有序的话, 还是效率很高~
bwangel 2012-07-18
  • 打赏
  • 举报
 回复
GUID是无序的。所以聚集索引在GUID上是严重的浪费，起不到一点效果。
但是sqlserver和oracle都有生成有序的GUID的机制。你可以把数据库中的主键全用有序的GUID重构一下
bwangel 2012-07-18
  • 打赏
  • 举报
 回复
LIKE '%A%'之类的好象没法用索引。OR更是性能的大敌。LZ确实是走投无路了。

既然一个OR条件都很慢。建议检查下表基本结构，该定义的主键是否都定义了。

我曾经改善过一个存储过程，原来要20分种处理完的数据包括插入更新等等 经过优化只需要10几秒。里面用到了很多LEFT JOIN.说明LEFT JOIN效率还是可以的。没有传说中那么差。
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 的回复:]

1、

select 40个字段
From Table1
Left Join Table1.Id = Table2.ID
Left Join.....
Left Join.....
...省略其他的Join....
Where Table1.Name like '%blabla%'

就这一个条件执行需要多少时间，如果可以接受，那么试着改为动态拼接的方式

2、动态

……
[/Quote]


尝试了, 但是时间还是比较长~

我正在尝试能否将搜索字段内容全部拼接到一个新字段里面, 然后只搜索这一个字段.
AcHerat 2012-07-18
  • 打赏
  • 举报
 回复
1、

select 40个字段
From Table1
Left Join Table1.Id = Table2.ID
Left Join.....
Left Join.....
...省略其他的Join....
Where Table1.Name like '%blabla%'

就这一个条件执行需要多少时间，如果可以接受，那么试着改为动态拼接的方式

2、动态



declare @sql varchar(max)

declare @whereflag int

set @sql = 'select 40个字段

From Table1

Left Join Table1.Id = Table2.ID

Left Join.....

Left Join.....

...省略其他的Join....

Where 1 = 1 '



if (@whereflag = 0)

set @sql = @sql + ' and table1.name like ''%aaa%'''

if (@whereflag = 1)

set @sql = @sql + ' and table2.name like ''%aaa%'''

if....





exec(@sql)



-- try !
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 的回复:]

楼主，我的意思是说，如果你的查询语句类似

select *
from a,b
where a.id = b.id and ((a.flag = b.flag and 1 = 1) or (a.flag <> b.flag and a.flag = 1))

那么执行这样子的看看数据，把or去掉，按其中一个条件来执行，看看时间会相差多少。

select *
from a,b
……
[/Quote]

逻辑没有这么复杂, 其实就是
select 40个字段
From Table1
Left Join Table1.Id = Table2.ID
Left Join.....
Left Join.....
...省略其他的Join....
Where Table1.Name like '%blabla%'
Or Table2.Name like '%blabla%'
Or Table3.Name like '%blabla%'
....省略其他的Like....
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 的回复:]

楼主，我的意思是说，如果你的查询语句类似

select *
from a,b
where a.id = b.id and ((a.flag = b.flag and 1 = 1) or (a.flag <> b.flag and a.flag = 1))

那么执行这样子的看看数据，把or去掉，按其中一个条件来执行，看看时间会相差多少。

select *
from a,b
……
[/Quote]

逻辑没有这么复杂, 其实就是
select 40个字段
From Table1
Left Join Table1.Id = Table2.ID
Left Join.....
Left Join.....
...省略其他的Join....
Where Table1.Name like '%blabla%'
Or Table2.Name like '%blabla%'
Or Table3.Name like '%blabla%'
....省略其他的Like....
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 的回复:]

只能用第三方软件，全文搜索速度真的不给力。我们是用hubble的
[/Quote]

唉....
目前的项目不太适合上第三方的东西...

伤神伤身啊~
楠_ 2012-07-18
  • 打赏
  • 举报
 回复
伤神呀，当初我就是被千万数据集弄得我两天，看不到你数据和表结构，弄不了了。
AcHerat 2012-07-18
  • 打赏
  • 举报
 回复
楼主，我的意思是说，如果你的查询语句类似

select *
from a,b
where a.id = b.id and ((a.flag = b.flag and 1 = 1) or (a.flag <> b.flag and a.flag = 1))

那么执行这样子的看看数据，把or去掉，按其中一个条件来执行，看看时间会相差多少。

select *
from a,b
where a.id = b.id and a.flag = b.flag
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 的回复:]

你用了后，用了多少时间
引用 13 楼 的回复:

引用 7 楼 的回复:

你可以看一下这个，我试过查询千万集数据数据库时间也就2秒不到，不知道，你那么情况怎么样。
把存储过程创建后，执行你的 SQL，不明白再问我
http://blog.csdn.net/peng13nannan/article/details/7418941


我目前也是用的ROW_NUMBER(……
[/Quote]

返回101-200条数据, 用了36秒.
snoopycom 2012-07-18
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 的回复:]

楼主先不要管是OR的条件，就以OR中的一个条件为准，看看执行需要多长时间

例如


select *
from a,b
where a.id = b.id and ((a.flag = b.flag and 1 = 1) or (a.flag <> b.flag and a.flag = 1))

以OR中的一个条件为准看看执行时间

select *
from a,b……
[/Quote]

如果只有一个OR的话, 执行速度依然很慢, 如果只返回前100条数据, 就比较快4秒左右.
楠_ 2012-07-18
  • 打赏
  • 举报
 回复
你用了后，用了多少时间
[Quote=引用 13 楼 的回复:]

引用 7 楼 的回复:

你可以看一下这个，我试过查询千万集数据数据库时间也就2秒不到，不知道，你那么情况怎么样。
把存储过程创建后，执行你的 SQL，不明白再问我
http://blog.csdn.net/peng13nannan/article/details/7418941


我目前也是用的ROW_NUMBER()来排序分页的, 但是还是慢..
[/Quote]
AcHerat 2012-07-18
  • 打赏
  • 举报
 回复
楼主先不要管是OR的条件，就以OR中的一个条件为准，看看执行需要多长时间

例如


select *
from a,b
where a.id = b.id and ((a.flag = b.flag and 1 = 1) or (a.flag <> b.flag and a.flag = 1))

以OR中的一个条件为准看看执行时间

select *
from a,b
where a.id = b.id and a.flag = b.flag
加载更多回复（13）
零基础快速上手【软件测试常用SQL语句】实战课程
跟着课程大纲学习，了解数据库，了解软件测试过程中常用SQL语句，在以后的测试工作中，可以通过查询数据，分析数据，定位BUG。课程大纲如下：数据库的概念常用数据库软件+数据库理解数据库服务器连接工具介绍Navicat连接禅道MySQL服务器Navicat工具的筛选查询MySQL-单表查询语法格式MySQL-条件查询MySQL-多表联查语法格式MySQL-多表联查-设置列和表的别名MySQL-多表联查-2张表之间的关联MySQL-子查询-关键字inMySQL-子查询-关键字existsMySQL-进阶提升学习内容
SQL-三张表关联查询（INNER JOIN）
三张表关联查询 使用场景：A\B\C 三张表，现在要查询并展示A表和C表中的某些字段，但是A、C两表没有相同字段，无法关联，此时有B表恰好有两个字段，一个字段和A表一个字段相同，一个字段和C表一个字段相同，我们称B表为“中间表”，因此通过B表把A、C表关联起来 方法一（推荐）： SELECT A1,A2,C1,C2 --展示A表中的A1\A2字段和C表中的C1\C2 FROM B --中间表 INNER JOIN A ON A.A1 = B.
oracle 一对多怎么关联查询,Oracle中SQL优化：left join查询语句，多个字段关联同一个表有关问题...
Oracle中SQL优化：left join查询语句，多个字段关联同一个表问题tabA表，此表字段：code1，code2，code3tabB表：此表字段：id，code，codeName查询：selectb1.codeName,b2.codeName,b3.codeNamefromtabAaleftjointabBb1ona.code1=b1.codeleftjointabBb...
多表关联查询SQL优化
引入 在进行多表关联查询的时候，需要在合适的地方添加索引避免笛卡尔积操作。 select x1 from a left b on a.x1 = b.x1; 针对于以上sql，需要明确哪个是驱动表，哪个是被驱动表，left join 的话，a是驱动表，b是被驱动表，如果将上述的查询过程用java表示的话，可以是如下所示： for(Row row : a表）{ for(Row row2 : b表）{ if(row.x1 == row2.x1){ break; } } } 可以
mysql 10张表左关联查询_mysql left join 左连接查询关联n多张表
left join 左连接即以左表为基准，显示坐标所有的行，右表与左表关联的数据会显示，不关联的则不显示。关键字为left join on。**基本用法如下：select table a left join table b on a.id = b.ta_id**注意：其中on后面关联的字段应该是同一字段(两表关联的外键)由于以左表为基准，左表一条记录如果对应右表多条记录，那查出的数据中右表的数据也...
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章