三度查询优化，cypher优化
发布于 6 年前 作者 feng1990liu 4897 次浏览 来自 问答

match(u:User{phone:’’})-[r:CALL]-(f)-[r1:CALL]-(f1)-[r2:CALL]-(u1:User) where size(f.phone)=11 and size(f1.phone)=11 and substring(f.phone,0,2) in [‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’] and substring(f1.phone,0,2) in [‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’] and u1.apply_time <= u.apply_time and not u=u1 return count(distinct u1) as cnt phone和apply_time属性都有索引，但查询起来还是很慢，有什么优化方向吗？

12 回复

把路径匹配放进where里试试 如： match (f:User),(f1:User),(u:User{phone:’’}),(u1:User) where size(f.phone)=11 and size(f1.phone)=11 and substring(f.phone,0,2) in [‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’] and substring(f1.phone,0,2) in [‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’] and u1.apply_time <= u.apply_time and not u=u1 and exist （(u )-[r:CALL]-(f)-[r1:CALL]-(f1)-[r2:CALL]-(u1) ） return count(distinct u1) as cnt

你在你cypher上 加个“profile” 看看命令执行瓶颈

@pangguoming image.png 我原来的cypher加profile会崩，我试试你的哈，谢谢

数据量多少？什么系统配置？内存多少？另外，尽量不要使用prop IN […]因为效率不高。还有，可以为关系加上方向。 这个模型挺有意思：(:User) -[:CALL]-> (:Phone) -[:CALL]-> (:Phone) -[:CALL]- (:User)。建议用不同的关系类型表示User和Phone、以及Phone和Phone之间的关系。 加个属性给Phone标识手机还是座机，而不要用size()或substring()来判断。你定义的索引基本上都没用到。

profile加在第一行上，cypher语句另起一行

@graphway image.png3亿节点，8亿关系，服务器是512G固态，32G内存，日常启动，内存占用70%，因为User和Friend都有phone属性，而用户关联可能通过User也可能通过friend关联，我优化下size和substring哈，多谢大佬

@pangguoming image.png profile match (f:Friend),(f1:Friend),(u:User{phone:‘13051*******8’}),(u1:User{black:‘1’}) where u1.apply_time <= u.apply_time and not u=u1 and exist ((u)-[r:CALL]-(f)-[r1:CALL]-(f1)-[r2:CALL]-(u1)) return count(distinct u1) as cnt
提示错误 WebSocket readyState is: 3

数据库被断开了？重新启动下

把debug.log和neo4j.log贴上来。感觉是内存溢出了。

@graphway neo4j.log如图image.png debug日志如下，应该是2018-08-03 06点的日志 2018-08-03 06:53:27.453+0000 INFO [o.n.k.i.DiagnosticsManager] index-5: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.453+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:52:20+0800 - 40.00 kB 2018-08-03 06:53:27.453+0000 INFO [o.n.k.i.DiagnosticsManager] spatial-1.0: 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] 1-4326: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:52:20+0800 - 40.00 kB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] string-1.0: 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] index-5: 2018-08-03T14:53:24+0800 - 3.25 GB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:52:20+0800 - 3.25 GB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] temporal-1.0: 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] date: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:52:20+0800 - 40.00 kB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:52:20+0800 - 3.25 GB 2018-08-03 06:53:27.454+0000 INFO [o.n.k.i.DiagnosticsManager] 6: 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] lucene-1.0: 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] 1: 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] segments_c: 2018-08-03T14:53:11+0800 - 85.00 B 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] write.lock: 2018-07-18T14:56:03+0800 - 0.00 B 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-08-03T14:53:11+0800 - 85.00 B 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] failure-message: 2018-07-18T14:56:03+0800 - 16.00 kB 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 16.08 kB 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] native-1.0: 2018-08-03 06:53:27.455+0000 INFO [o.n.k.i.DiagnosticsManager] index-6: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 40.00 kB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] spatial-1.0: 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] 1-4326: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 40.00 kB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] string-1.0: 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] index-6: 2018-08-03T14:53:24+0800 - 42.66 MB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 42.66 MB 2018-08-03 06:53:27.456+0000 INFO [o.n.k.i.DiagnosticsManager] temporal-1.0: 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] date: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 40.00 kB 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:03+0800 - 42.80 MB 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] 8: 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] lucene-1.0: 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] 1: 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] segments_c: 2018-08-03T14:53:11+0800 - 85.00 B 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] write.lock: 2018-07-18T14:56:14+0800 - 0.00 B 2018-08-03 06:53:27.457+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-08-03T14:53:11+0800 - 85.00 B 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] failure-message: 2018-07-18T14:56:14+0800 - 16.00 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 16.08 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] native-1.0: 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] index-8: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 40.00 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] spatial-1.0: 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] 1-4326: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 40.00 kB 2018-08-03 06:53:27.458+0000 INFO [o.n.k.i.DiagnosticsManager] string-1.0: 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] index-8: 2018-08-03T14:53:24+0800 - 27.35 MB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 27.35 MB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] temporal-1.0: 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] date: 2018-08-03T14:53:24+0800 - 40.00 kB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 40.00 kB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:56:14+0800 - 27.48 MB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-20T11:13:12+0800 - 10.37 GB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:50:19+0800 - 10.37 GB 2018-08-03 06:53:27.459+0000 INFO [o.n.k.i.DiagnosticsManager] - Total: 2018-07-18T14:50:19+0800 - 10.37 GB 2018-08-03 06:53:27.460+0000 INFO [o.n.k.i.DiagnosticsManager] store_lock: 2018-07-18T14:48:16+0800 - 0.00 B 2018-08-03 06:53:27.460+0000 INFO [o.n.k.i.DiagnosticsManager] Storage summary: 2018-08-03 06:53:27.460+0000 INFO [o.n.k.i.DiagnosticsManager] Total size of store: 120.72 GB 2018-08-03 06:53:27.460+0000 INFO [o.n.k.i.DiagnosticsManager] Total size of mapped files: 109.97 GB 2018-08-03 06:53:27.460+0000 INFO [o.n.k.i.DiagnosticsManager] — STARTED diagnostics for KernelDiagnostics:StoreFiles END — 2018-08-03 06:53:29.383+0000 INFO [o.n.k.i.DiagnosticsManager] — SERVER STARTED START — 2018-08-03 06:53:30.322+0000 INFO [o.n.k.i.DiagnosticsManager] — SERVER STARTED END — 2018-08-03 07:08:31.924+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0x576ca8fb, L:/10.10.10.2:7687 - R:/36.110.147.196:14121] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 07:08:32.942+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xcd78998f, L:/10.10.10.2:7687 - R:/36.110.147.196:14134] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 07:08:47.278+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xa3b530dd, L:/10.10.10.2:7687 - R:/36.110.147.196:14849] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 09:16:39.237+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xfdb8ac22, L:/10.10.10.2:7687 - R:/36.110.147.196:58065] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 09:16:40.253+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0x6d9a1fe8, L:/10.10.10.2:7687 - R:/36.110.147.196:57542] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 10:20:20.782+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0x1097d850, L:/10.10.10.2:7687 - R:/36.110.147.196:35333] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 10:20:21.799+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xa83d97e3, L:/10.10.10.2:7687 - R:/36.110.147.196:35340] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 10:20:21.802+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xdf3ee7c9, L:/10.10.10.2:7687 - R:/36.110.147.196:35358] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 10:20:21.806+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xa8e60f21, L:/10.10.10.2:7687 - R:/36.110.147.196:35365] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 12:04:02.011+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0xc1411868, L:/10.10.10.2:7687 - R:/36.110.147.196:50015] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 12:04:02.013+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0x4cfbe43e, L:/10.10.10.2:7687 - R:/36.110.147.196:50036] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source) 2018-08-03 12:04:02.018+0000 ERROR [o.n.b.t.p.HouseKeeper] Fatal error occurred when handling a client connection: [id: 0x069e7b97, L:/10.10.10.2:7687 - R:/36.110.147.196:50022] syscall:read(…) failed: Connection reset by peer io.netty.channel.unix.Errors$NativeIoException: syscall:read(…) failed: Connection reset by peer at io.netty.channel.unix.FileDescriptor.readAddress(…)(Unknown Source)

@pangguoming 服务没有停，可能自动启动，可能临时断了一会

这个是全部日志吗？你查一下是否有大量GC出现在日志里面? 或者把EXPLAIN得到的执行计划贴上来？

@graphway 谢谢大佬，我优化了下cypher,explain效果如下，frofile未出结果前会报错serviceUnavailable,但实际服务未停止，别的查询程序没有报错。 explain match(u:User{phone:‘1305138****’})-[r:CALL]->(f:Friend)<-[r1:CALL]-(f1:User)-[r2:CALL]->(u1:User{black:‘1’}) where not u=f1 and size(f.phone)=11 and substring(f.phone,0,2) in [‘13’,‘14’,‘15’,‘16’,‘17’,‘18’,‘19’]
and u1.apply_time <= u.apply_time and not u=u1 return count(distinct u1) as cnt, count(distinct f) as fri_cnt image.png

回到顶部