2018-09-27

Java 分页 MySQL

在此输入正文

count()函数是用来统计表中记录的一个函数，返回匹配条件的行数。

count()语法：

（1）count(*)---包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为NULL的记录。（2）count(1)---忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略列值为NULL的记录。
（3）count(列名)---只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。
（4）count(distinct 列名)---只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(*)&count(1)&count(列名)执行效率比较：

（1）如果列为主键，count(列名)效率优于count(1)
（2）如果列不为主键，count(1)效率优于count(列名)
（3）如果表中存在主键，count(主键列名)效率最优
（4）如果表中只有一列，则count()效率最优
（5）如果表有多列，且不存在主键，则count(1)效率优于count()

可以通过以下两种方式来统计列值为NULL的记录数:

（1）select count(*) from table where is_active is null;
（2）select count(1) from table where is_active is null;

特例：

（1）select count('') from table;-返回表的记录数
（2）select count(0) from table;-返回表的记录数
（3）select count(null) from table;-返回0

针对Innodb表,尽量不执行SELECT COUNT(*)语句，
因为innodb为聚簇索引同时支持事物，其在count指令实现上采用实时统计方式；在无可用的二级索引情况下，执行count会使MySQL扫描全表数据，当数据中存在大字段或字段较多时候，其效率非常低下（每个页只能包含较少的数据条数，需要访问的物理页较多）。

对策：对innob存储引擎的大表进行select count()统计总数操作,业界都会尽量避免。

如果要避免全表扫描查询innodb存储引擎的表总数，要怎么办？

方案一：
使用主键不带条件查询（覆盖索引）

SELECT COUNT('主键') FROM `表名`;

方案二：通过查询mysql的information_schema数据库中INNODB_TABLESTATS表,它记录了innodb类型每个表大致（不准确的）的数据行数。

USE information_schema;
SELECT NUM_ROWS FROM INNODB_TABLESTATS WHERE NAME='数据库名/表名';

方案三：添加一个二级索引

Innodb存储引擎：

(1)     innodb存储引擎的物理结构包含 表空间、段、区、页、行 五个层级，数据文件按照主键排序存储在页中（页在逻辑上连续），主键的位置即为数据存储位置。
(2)     二级索引存储的数据为指定字段的值与主键值。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，故大多数情况下，通过二级索引统计数据效率 >= 基于主键统计效率。
(3)    由于二级索引存储的数据为指定字段的值与主键值，故在无索引覆盖的情况下，查询二级索引后会根据二级索引获取的主键到主键索引中提取数据，此过程可能造成大量的随机io，导致查询速度较慢。
(4)    由于主键索引与数据存储保持一致，故基于主键的查找数据要比通过二级索引查询数据要快（使用二级索引时，查询到的数据条数>总条数的20%时候mysql就选择全表扫描，但在主键索引上，即使符合条件的达到 90%依然会走索引）。

本文来自 vip_hitwu 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/vip_hitwu/article/details/77066688?utm_source=copy