Mysql-Select中的子查询如何优化?

Question

最近要做一个zipkin数据访问的统计相关表数据，SQL如下：但是执行效率非常低下，应该是子查询的问题，请问该如何进行优化？

SELECT 
gz.name AS 访问路径,
AVG(gz.duration) AS 平均耗时,
MAX(gz.duration) AS 最大耗时,
MIN(gz.duration) AS 最小耗时,
COUNT(*) AS 平均访问次数,
(SELECT COUNT(*) FROM zipkin_spans  zs
WHERE   zs.name LIKE "%service%" AND zs.trace_id IN (
SELECT  z.trace_id
FROM zipkin_spans z
WHERE z.name = gz.name
)) AS RPC调用次数,
(SELECT COUNT(*) FROM zipkin_spans  zs
WHERE   zs.name LIKE "%mapper%" AND zs.trace_id IN (
SELECT  z.trace_id
FROM zipkin_spans z
WHERE z.name = gz.name
)) AS 数据库调用次数
FROM zipkin_spans gz
WHERE gz.name LIKE "get%"
GROUP BY gz.name`

相关EXPLAIN分析结果如下，请高人解答，本人小白一枚！

Answer 1

前置%不走索引，其次统计字段建议用冗余字段累计