如何提高mysql数据库查询语句的效率，表里的记录有上亿条，指定了索引还是很慢

Question

SELECT a1.juanhao,a1.c1,a2.c2,a3.c3,a4.c4,a5.c5,a6.c6 FROM"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg1+" AND zigao<"+zg2+" THEN 1 END) AS c1 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a1,"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg2+" AND zigao<"+zg3+" THEN 1 END) AS c2 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a2,"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg3+" AND zigao<"+zg4+" THEN 1 END) AS c3 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a3,"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg4+" AND zigao<"+zg5+" THEN 1 END) AS c4 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a4,"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg5+" AND zigao<"+zg6+" THEN 1 END) AS c5 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a5,"+
"(select juanhao,COUNT(CASE WHEN zigao>="+zg1+" AND zigao<"+zg6+" THEN 1 END) AS c6 FROM juan_information FORCE INDEX(zg) GROUP BY juanhao) AS a6 "+
"where a1.juanhao=a2.juanhao AND a2.juanhao=a3.juanhao AND a3.juanhao=a4.juanhao AND a4.juanhao=a5.juanhao AND a5.juanhao=a6.juanhao

Answer 1

用连接查询，leftjoin，如果条件允许，可考虑分表分库