MySQL视图count速度优化

Question

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

CREATE VIEW a
AS
SELECT *
FROM b FORCE INDEX (`idx_b`)
JOIN a ON a.CId = b.Id
JOIN c ON c.CId = b.Id
LEFT JOIN s ON s.No = c.No
LEFT JOIN d ON d.CId = a.Id AND d.EId = c.Id
LEFT JOIN e FORCE INDEX (`idx_e`) ON e.CId = a.Id
WHERE b.isdeleted = 0

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

Answer 1

Based on the sql statement you wrote, here are some suggestions:
1. Table B uses force index to force the use of indexes. When there are a large number of table entries, index query may not be the optimal way. In general, let Just choose the database yourself. Or verify it yourself, use the condition of isdeleted = 0 to check the b single table, and confirm that the performance is better with the force index, otherwise remove the force index.
2. To count the number of SQL statements, only keep tables a, b, and c. The other tables are left joins, which will not affect the statistical results.
3. According to the primary key field of table b taken out in the previous step, Loop to get detailed information in php. Since the number of pages is generally small, fetching data through the primary key multiple times may be faster.

In addition, if you want everyone to analyze, it is best to post the SQL execution plan.

Answer 2

Post your sql statement for everyone to see.

Answer 3

Query multiple times and then add them up to get the total number of records. How about it? ? (Not tested....)

For example, the amount of data per query is: 8000 records

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;

Answer 4

What you end up querying is the data of table b. If count table b, why do you need to join other tables? count is a full table scan, and there is basically no optimization method without adding where.

Answer 5

In the beginning, the research and development was to make the total number of items loaded asynchronously, with the data and page numbers coming out first, so as long as the number is not deliberately checked, it will not have much impact;
Later, I read the suggestion above to change the view, and the total number will be loaded faster;
There are also several forced indexes that are added to enable external conditions to be indexed when using views;
This is still acceptable for the time being after optimization.