MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

MySQL: 大表行数查询


基于 InnoDB 存储引擎的表,在数据量很大之后,用 count 查询函数会变得很慢。

我们新建一张表进行测试(测试结果因硬件配置不同,会有所差异):

USE test;
CREATE TABLE `test_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

参考 MySQL: 使用 load data 快速导入数据,在 test_table 中插入 1999_9999 条数据。

方案1

使用 count :

mysql> select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 19999999 |
+----------+
1 row in set
Time: 3.437s

耗时 3.4 秒。

方案2

mysql> SELECT SQL_CALC_FOUND_ROWS id FROM test_table limit 1;
+----+
| id |
+----+
| 1  |
+----+
1 row in set
Time: 3.707s

mysql> SELECT found_rows() AS result;
+--------+
| result |
+--------+
| 1      |
+--------+
1 row in set
Time: 0.006s

耗时还是比较长。

方案3: 预估值

使用下面的 SQL:

select TABLE_NAME, TABLE_ROWS
from  
    INFORMATION_SCHEMA.PARTITIONS  
where 
    TABLE_SCHEMA = 'test' 
    and TABLE_NAME='test_table';

结果为:

+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| test_table | 19508174   |
+------------+------------+

19508174 是一个预估的值。

方案4:预估

因为主键id是逐渐加1递增的,在确保表中数据几乎没有跳id、删数据的情况下,直接用最大id,减最小id:

mysql> select id from test_table order by id asc limit 1;
+----+
| id |
+----+
| 1  |
+----+
1 row in set
Time: 0.006s

mysql> select id from test_table order by id desc limit 1;
+----------+
| id       |
+----------+
| 19999999 |
+----------+

19999999 - 1 + 1 = 19999999 ,所以该表约有 19999999 条数据。


( 本文完 )

文章目录