MYSQL 下 count(*)、count(列）、 count(1) 的理解

 

先说结论：

执行效果上：  

1、count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
2、count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
3、count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：  
1、列名为主键，count(列名)会比count(1)快  （待商榷）
2、列名不为主键，count(1)会比count(列名)快  （确定）
3、如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  （待商榷）
4、如果有主键，则 select count（主键）的执行效率是最优的  （待商榷）
5、如果表只有一个字段，则 select count（*）最优。（待商榷）

 

建表语句

create database test;
use test;
create table t(id int primary key , age int , name varchar(18) );

 

create procedure sp_name()
begin
declare i int default 0;
start transaction;
while i<10000000 do
insert into t(id,age,name)values(i,i,'zhangsan');
set i=i+1;
end while;
commit;
end;

 

二.调用存储过程
1.基本语法：

call sp_name();

注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程
1.基本语法：

drop procedure sp_name;

2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

使用示例：

create procedure sp_name()
begin
declare i int default 0;
start transaction;
while i<10000000 do
insert into t(id,age,name)values(i,i,'zhangsan');
set i=i+1;
end while;
commit;
end;
Query OK, 0 rows affected

mysql> call sp_name();
Query OK, 0 rows affected

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set

验证执行效果

 cout(*)和count(1) 没区别，但是cout(列名） 不统计为 null 的

select count(name) from t;
+-------------+
| count(name) |
+-------------+
|     9999998 |
+-------------+
1 row in set

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set

mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set

验证执行效率

执行时间

 

分析一下 执行计划，然后看下执行时间， 无条件查询情况下 

可以得到结论

 count(主键） count(*)  count(1) 效率远高于   count(非主键列）

  

1、count(*） count(1), count(列,主键)  执行计划基本上是一样的

2、count(列名（非主键）） 比如 count*name 的执行计划type = All  是进行的全表扫描，而count(*） count(1), count(列,主键) 的type 是null，执行时甚至不用访问表或索引

MYSQL执行计划怎么看

查看执行时间

1 show profiles; 
2 show variables;查看profiling 是否是on状态； 
3 如果是off，则 set profiling = 1； 
4 执行自己的sql语句； 
5 show profiles；就可以查到sql语句的执行时间；

查看方法： show variables like "%pro%";

设置开启方法： set profiling = 1;

查看操作了多少行

 Id：包含一组数字，表示查询中执行select子句或操作表的顺序；  执行顺序从大到小执行；

select_type:表示查询中每个select子句的类型（简单OR复杂）

SIMPLE：查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY

DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）

UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；

DERIVED:若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

UNION RESULT：从UNION表获取结果的SELECT被标记为：UNION RESULT

Type：表示MySQL在表中找到所需行的方式

ALL：Full Table Scan， MySQL将进行全表扫描；

 index：Full Index Scan，index与ALL区别为index类型只遍历索引树；

range：range Index Scan，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询；

 ref：非唯一性索引扫描，返回匹配摸个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找；

 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

const system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用；

key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。当查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

ref：表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值；

rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；

Extra：包含不适合在其他列中显示但十分重要的额外信息；

 Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

 Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询；

 Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”；

关于MySQL执行计划的局限性：

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。

 

查看执行时间

查询表的存储引擎信息

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

事务的隔离级别查询

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

MySQL5.7文档中有一段话：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

InnoDB以同样的方式处理SELECT COUNT(*)和SELECT COUNT(1)操作。两者没有性能差异。

对于MyISAM表，如果SELECT从一个表中检索，没有检索其他列，也没有WHERE子句，那么COUNT(*)被优化为快速返回。这种优化只适用于MyISAM表，因为这个存储引擎存储了准确的行数，并且可以非常快速地访问。COUNT(1)只有在第一列被定义为NOT NULL时才进行与COUNT(*)相同的优化。