mysql大表统计查询优化

大表统计查询优化

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

字段名说明
id记录id
table_name表名
table_count行数

使用触发器

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

觉得好看,打赏一下小弟吧:

©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页