mysql大表统计查询优化

我就是我 2022-03-01 04:40 458阅读 0赞

> 大表统计查询优化

### 文章目录 ###

*   *   *  1. 统计
         *   *  1.1 使用count
             *  1.2 使用SQL\_CALC\_FOUND\_ROWS和FOUND\_ROWS
             *  1.3 使用information\_schema
             *  1.4 开启mysql查询缓存(8.0已废弃)
             *  1.5 增加统计表
             *  1.6 使用应用程序缓存

### 1. 统计 ###

#### 1.1 使用count ####

select count(*) as num from table_name ;

> 准确率：运行查询100%准确度。  
> 效率：不适合大表查询。  
> MyISAM ：表查询非常快，但是使用`where`后,依然要进行全表扫描  
> InnoDB ：取决于表的大小，因为引擎必须扫描整个表或整个索引以获得准确的计数。表越大，越慢。

eg：

mysql> select count(*) from user_test;
    +----------+
    | count(*) |
    +----------+
    |  5456864 |
    +----------+
    1 row in set (6.94 sec)

#### 1.2 使用SQL\_CALC\_FOUND\_ROWS和FOUND\_ROWS ####

select sql_calc_found_rows * from table_name limit 0 ; 
    select found_rows() as num ;

> 准确率和效率和 1.1 完全一样，但是这里做列表分页查询时，第一次查完列表数据，第二次统计会更快，不需要再次扫描

eg.

mysql> select sql_calc_found_rows * from user_test limit 0;
    Empty set (4.19 sec)
    
    mysql> select found_rows() as num;
    +---------+
    | num     |
    +---------+
    | 5456864 |
    +---------+
    1 row in set (0.00 sec)

#### 1.3 使用information\_schema ####

SELECT `TABLE_ROWS` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='dbname' AND `TABLE_NAME`='table_name';

> 准确率: 结果是近似值。如果表是频繁插入和删除的表，则结果可能与实际计数不同。这可以通过`ANALYZE TABLE`更频繁地运行来改善。改善的结果仍可能不是准确的结果。  
> 效率：非常好，它根本不触及具体表

eg.

mysql> SELECT `TABLE_ROWS` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`='thd' AND `TABLE_NAME`='user_test';
    +------------+
    | TABLE_ROWS |
    +------------+
    |    5294801 |
    +------------+
    1 row in set (0.00 sec)

#### 1.4 开启mysql查询缓存(8.0已废弃) ####

更改`my.ini`

query_cache_type = 1
    query_cache_size = 10M

> 准确率 ：100%  
> 效率 ：第一次查询比较慢，以后的每次查询(数据变更不频繁的情况下)直接返回缓存结果，超级快  
> 但是数据变更比较频繁的话，不建议使用

eg.

mysql> select count(*) from user_test;
    +----------+
    | count(*) |
    +----------+
    |  5456864 |
    +----------+
    1 row in set (7.01 sec)
    
    mysql> select count(*) from user_test;
    +----------+
    | count(*) |
    +----------+
    |  5456864 |
    +----------+
    1 row in set (0.00 sec)

#### 1.5 增加统计表 ####

我们在原有表的基础上增加一个维护数量的统计表

select num from table_statics where table_name = `table_name`

> 准确率 ：100%  
> 效率 ：非常快  
> 缺点：增加数据库操作开销

表名： table\_statics

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>记录id</td> 
  </tr> 
  <tr> 
   <td>table_name</td> 
   <td>表名</td> 
  </tr> 
  <tr> 
   <td>table_count</td> 
   <td>行数</td> 
  </tr> 
 </tbody> 
</table>

使用触发器

DELIMITER $$
     
    CREATE
        TRIGGER `triggerA` AFTER INSERT 
        ON `user_test`
        FOR EACH ROW BEGIN
     
        IF NOT EXISTS(SELECT id FROM table_statics WHERE table_name = 'user_test') THEN  
          INSERT INTO table_statics(table_name,table_count) VALUES('user_test',1);
        ELSE 
          UPDATE table_statics SET table_count = table_count + 1 WHERE table_name  = 'user_test';
        END IF;     
        END$$
     
    DELIMITER ;
    
    
    DELIMITER $$
     
    CREATE
        TRIGGER `triggerB` AFTER DELETE  
        ON `user_test`
        FOR EACH ROW BEGIN
     
        IF NOT EXISTS(SELECT id FROM table_statics WHERE table_name = 'user_test') THEN
          INSERT INTO table_statics(table_name,table_count) VALUES('user_test',1);
        ELSE 
          UPDATE table_statics SET table_count = table_count - 1 WHERE table_name  = 'user_test';
        END IF;     
        END$$
     
    DELIMITER ;

原始数据：

mysql> select * from table_statics where table_name = 'user_test';
    +----+------------+-------------+---------------------+
    | id | table_name | table_count | create_time         |
    +----+------------+-------------+---------------------+
    |  1 | user_test  |     5456860 | 2019-03-21 17:24:06 |
    +----+------------+-------------+---------------------+
    1 row in set (0.00 sec)

删除一条后数据：

mysql> delete from user_test limit 1;
    Query OK, 1 row affected (0.09 sec)
    
    mysql> select * from table_statics where table_name = 'user_test';
    +----+------------+-------------+---------------------+
    | id | table_name | table_count | create_time         |
    +----+------------+-------------+---------------------+
    |  1 | user_test  |     5456859 | 2019-03-21 17:34:14 |
    +----+------------+-------------+---------------------+

增加一条数据：

mysql> insert into user_test(username,`desc`,email,create_time) VALUES('张三30899','这是一段描述30899','zhangsan30899@163.com','2019-03-21 11:49:53');
    Query OK, 1 row affected (0.10 sec)
    
    mysql> select * from table_statics where table_name = 'user_test';
    +----+------------+-------------+---------------------+
    | id | table_name | table_count | create_time         |
    +----+------------+-------------+---------------------+
    |  1 | user_test  |     5456860 | 2019-03-21 17:35:08 |
    +----+------------+-------------+---------------------+
    1 row in set (0.00 sec)

#### 1.6 使用应用程序缓存 ####

$key = 'table_count:user_test';
    $count = $redis->get($key);
    if(empty($count)) { 
        $count = 'from db count';
    	$redis->set($key,$count,10*60);
    }

> 准确率 ：根据数据的更新频率近似 100%  
> 效率 ：非常快  
> 缺点：更新非常频繁的，会有数据统计误差

参考思路来自 ：[https://dba.stackexchange.com/questions/151769/mysql-difference-between-using-count-and-information-schema-tables-for-coun][https_dba.stackexchange.com_questions_151769_mysql-difference-between-using-count-and-information-schema-tables-for-coun]

觉得好看，打赏一下小弟吧：  
![70][]

[https_dba.stackexchange.com_questions_151769_mysql-difference-between-using-count-and-information-schema-tables-for-coun]: https://dba.stackexchange.com/questions/151769/mysql-difference-between-using-count-and-information-schema-tables-for-coun
[70]: /images/20220301/a728014981064e579ad206b83737559e.png