MySQL视图count速度优化

问题描述

背景:程序列表页展示信息，需后台多表关联读取视图，视图内sql已优化(索引、语句)，外层增加查询条件后速度基本一致。VIEW:

CREATE VIEW aASSELECT *FROM b FORCE INDEX (`idx_b`)JOIN a ON a.CId = b.IdJOIN c ON c.CId = b.IdLEFT JOIN s ON s.No = c.NoLEFT JOIN d ON d.CId = a.Id AND d.EId = c.IdLEFT JOIN e FORCE INDEX (`idx_e`) ON e.CId = a.IdWHERE b.isdeleted = 0

环境:1).mysql 5.7.10 InnoDB引擎 2核4G2).阿里云RDS 4核8G (貌似效果更差<分片性能会衰减>)问题:1.外层增加排序order by条件后，速度变慢，查看执行计划为将试图内数据转化为temp_table后再进行sort;2.直接count视图的话更加缓慢，已达不能接受地步，列表分页肯定需展示总条数，抛除视图，直接使用sql也是很缓慢。大神们指点指点怎样优化mysql多表关联的count

问题解答

根据你写的sql语句，有几点建议：1、b表使用了force index以强制使用索引，在表条目数较多的情况下，索引查询未必是最优的方式，一般情况下让数据库自己选择就可以了。或者自己验证下，用isdeleted = 0的条件查b单表，确认加上force index性能更好，否则就去掉force index。2、统计数量的sql语句，仅保留a,b,c张表就可以了，其他的几张表是left join，不影响统计结果3、根据上一步取出的b表字段的主键字段，在php中循环取详情信息。因分页的数量一般较少，多次通过主键取数据的方式速度可能会更快。

另外，如果想让大家分析，最好把sql的执行计划贴出来。

把你的sql语句贴出来，让大伙看看。

多次查询，然后合计，得出记录总数。怎么样？？（没测试过....）

例如，每次查询的数据量为： 8000条记录

PHP 代码：

// 单位查询数据量 $unit_num = 8000; // 查询批次 $count = 1; // 总数据量 $total_record = 0;while (true) { $sql = ’select count(*) from tb_name limit ’ . ($count - 1) * $unit_num . ’ , ’ . $unit_num; $part_num = $db->getOne($sql); # 部分数量// 如果数量 和 查询的单位量相等，这说明，总数据可能更多..，继续查询 if ($part_num === $unit_num) {$total_record += $part_num; }// 如果数量 比 查询的单位量更少，说明，已经是最后一批数据了 // 跳出循环 if ($part_num < $unit_num) {$total_record += $part_num;break; }# 更新查询的批次 $count++;} echo ’总数据量：’ . $total_record;回答4：

你最终查询的是b表的数据，如果count b表为啥还要join其他表呢，count本来就是全表扫描，不加where基本上没有优化办法。

最开始是研发将总条数做成异步加载，数据和页码先出来，这样只要不是刻意查数量没什么太大影响;后来看楼上建议改了下视图，加载总数也会更快些;还有那几个强制索引也是为使用视图时外面加条件能够走索引才加的;这样优化下来暂时还能接受。