目录
count (1)、count (*) 与 count (列名) 的区别
空值与NULL
相信很多用了mysql很久的人，对这两个字段属性的概念还不是很清楚，一般会有以下疑问：
1、我字段类型是not null，为什么我可以插入空值
2、为毛not null的效率比null高
3、判断字段不为空的时候，到底要 select * from table where column <> '' 还是要用 select * from table where column is not null 呢。
首先，我们要搞清楚“空值” 和 “NULL” 的概念：
1、空值是不占用空间的
2、mysql中的NULL其实是占用空间的
下面是来自于MYSQL官方的解释
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。
搞清楚“空值”和“NULL”的概念之后，问题基本就明了了，我们搞个例子测试一下：
CREATE TABLE `test` (
`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = MYISAM ;
插入数据：
INSERT INTO `test` VALUES (null,1);
mysql发生错误：
#1048 - Column 'col1' cannot be null
再来一条
INSERT INTO `test` VALUES ('',1);
成功插入。
可见，NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”，上面的问题1也就有答案了。
对于问题2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。
而且B树索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。
我们再向test的表中插入几条数据：
INSERT INTO `test` VALUES ('', NULL);
INSERT INTO `test` VALUES ('1', '2');
现在表中数据：
现在根据需求，我要统计test表中col1不为空的所有数据，我是该用“<> ''” 还是 “IS NOT NULL” 呢，让我们来看一下结果的区别。
SELECT * FROM `test` WHERE col1 IS NOT NULL
SELECT * FROM `test` WHERE col1 <> ''
可以看到，结果迥然不同，所以我们一定要根据业务需求，搞清楚到底是要用那种搜索条件。
NULL与其他数据的运算
在SQL中，NULL值在于任何其他值甚至NULL值比较时总是假的（FALSE）。
包含NULL的一个表达式总是产生一个NULL值，除非在包含在表达式中的运算符和函数的文档中指出。
在下列例子，所有的列返回NULL：
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
如果你想要寻找值是NULL的列，你不能使用=NULL测试。下列语句不返回任何行，因为对任何表达式，expr = NULL是假的：
mysql> SELECT * FROM my_table WHERE phone = NULL;要想寻找NULL值，你必须使用IS NULL测试。
null只能用is null 或者is not null 来判断，不能用=或者！=来判断null和任何值都不能比较
下例显示如何找出NULL电话号码和空的电话号码：
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";在MySQL中，就像很多其他的SQL服务器一样，你不能索引可以有NULL值的列。你必须声明这样的列为NOT NULL，而且，你不能插入NULL到索引的列中。
当用LOAD DATA INFILE读取数据时，空列用''更新。如果你想要在一个列中有NULL值，你应该在文本文件中使用\N。
当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。
当使用GROUP BY时，所有的NULL值被认为是相等的。
为了有助于NULL的处理，你能使用IS NULL和IS NOT NULL运算符和IFNULL()函数。
ISNULL( )和IFNULL（）
ISNULL( )
MySQL数据库中内置了 ISNULL( ) 函数，其使用方法与MySQL中提供的sum（）等其他内置函数一样。比如，select ISNULL(user_name) from users where user_name = ‘Demrystv' 返回值是 0；
select ISNULL(NULL) 返回值是1
select ISNULL(非null变量) 返回值是0
IFNULL（）
MySQL数据库中内置了 IFNULL( ) 函数，其使用方法与MySQL中提供的sum（）等其他内置函数一样。其主要接收两个参数，第一个参数是要判断空值的字段或值，第二个字段是当第一个参数是空值的情况下要替换返回的另一个值，即如果第一个字段为null，会将其替换成其他值。
比如，select IFNULL(NULL, “java is the best language of the world”)，由于第一个参数的值NULL，因此会输出第二个参数的值，java is the best language of the world；同理，若第一个字段不为空，则会返回第一个字段的值。
count (1)、count (*) 与 count (列名) 的区别
| 表 | count(1) | count(*) | count (列名) |
|---|---|---|---|
| 作用 | 统计表中的所有的记录数 | 会统计表中的所有的记录数 | 统计该字段在表中出现的次数 |
| 是否包含字段为 null | 包含 | 包含 | 不包含 |
| 区别 | count (1) 和 count (*) 基本没差别，实际建议count (*) | count (1) 和 count (*) 基本没差别实际建议count (*) |
效率：
列名为主键，count (列名) 会比 count (1) 快
列名不为主键，count (1) 会比 count (列名) 快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优。
COUNT(1)，COUNT(*),这俩在高版本的MySQL（5.5及以后，5.1的没有考证）是没有什么区别的，也就没有COUN(1)会比COUNT(*)更快这一说了。
结论是：如果该表只有一个主键索引，没有任何二级索引的情况下，那么COUNT(*)和COUNT(1)都是通过通过主键索引来统计行数的。如果该表有二级索引，则COUNT(1)和COUNT(*)都会通过占用空间最小的字段的二级索引进行统计。
也就是使用 COUNT(*) 由于 MySQL 的优化已经保证了它的查询性能是最好的！随带提一句，COUNT(*)是 SQL92 定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT(*)查询表的行数！
索引是一种B+树的结构，以块为单位进行存储。假设块大小是1K，主键索引大小为4B，有一个字段A的索引大小为2B。
同样一个块，能存放256个主键索引，但是能存放512个字段A的索引。
假设总数据是2K条，意味着主键索引占用了8个块，而A字段索引占用了4个块，统计时用主键索引需要经历的块多，IO次数多。效率也比A字段索引慢。
SQL 选用索引的执行成本如何计算
就如前文所述，在有多个索引的情况下， 在查询数据前，MySQL 会选择成本最小原则来选择使用对应的索引，这里的成本主要包含两个方面。
IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关
CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。
实例说明
为了根据以上两个成本来算出使用索引的最终成本，我们先准备一个表（以下操作基于 MySQL 5.7.18）
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `name_score` (`name`(191),`score`),
KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
这个表除了主键索引之外，还有另外两个索引, name_score 及 create_time。然后我们在此表中插入 10 w 行数据，只要写一个存储过程调用即可，如下:
CREATE PROCEDURE insert_person()
begin
declare c_id integer default 1;
while c_id<=100000 do
insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
set c_id=c_id+1;
end while;
end
插入之后我们现在使用 EXPLAIN 来计算下统计总行数到底使用的是哪个索引
EXPLAIN SELECT COUNT(*) FROM person
从结果上看它选择了 create_time 辅助索引，显然 MySQL 认为使用此索引进行查询成本最小，这也是符合我们的预期，使用辅助索引来查询确实是性能最高的！
我们再来看以下 SQL 会使用哪个索引
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
用了全表扫描！理论上应该用 name_score 或者 create_time 索引才对，从 WHERE 的查询条件来看确实都能命中索引，那是否是使用 SELECT * 造成的回表代价太大所致呢，我们改成覆盖索引的形式试一下
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'
结果 MySQL 依然选择了全表扫描！这就比较有意思了，理论上采用了覆盖索引的方式进行查找性能肯定是比全表扫描更好的，为啥 MySQL 选择了全表扫描呢，既然它认为全表扫描比使用覆盖索引的形式性能更好，那我们分别用这两者执行来比较下查询时间吧
-- 全表扫描执行时间: 4.0 ms
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
-- 使用覆盖索引执行时间: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
从实际执行的效果看使用覆盖索引查询比使用全表扫描执行的时间快了一倍！说明 MySQL 在查询前做的成本估算不准！我们先来看看 MySQL 做全表扫描的成本有多少。
前面我们说了成本主要 IO 成本和 CPU 成本有关，对于全表扫描来说也就是分别和聚簇索引占用的页面数和表中的记录数。执行以下命令
SHOW TABLE STATUS LIKE 'person'
可以发现
行数是 100264，我们不是插入了 10 w 行的数据了吗，怎么算出的数据反而多了，其实这里的计算是估算，也有可能这里的行数统计出来比 10 w 少了，估算方式有兴趣大家去网上查找，这里不是本文重点，就不展开了。得知行数，那我们知道 CPU 成本是 100264 * 0.2 = 20052.8。
数据长度是 5783552，InnoDB 每个页面的大小是 16 KB，可以算出页面数量是 353。
也就是说全表扫描的成本是 20052.8 + 353 = 20406。
这个结果对不对呢，我们可以用一个工具验证一下。在 MySQL 5.6 及之后的版本中，我们可以用 optimizer trace 功能来查看优化器生成计划的整个过程 ，它列出了选择每个索引的执行计划成本以及最终的选择结果，我们可以依赖这些信息来进一步优化我们的 SQL。
optimizer_trace 功能使用如下
SET optimizer_trace="enabled=on";
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
执行之后我们主要观察使用 name_score，create_time 索引及全表扫描的成本。
先来看下使用 name_score 索引执行的的预估执行成本:
{
"index": "name_score",
"ranges": [
"name84059 <= name"
],
"index_dives_for_eq_ranges": true,
"rows": 25372,
"cost": 30447
}
可以看到执行成本为 30447，高于我们之前算出来的全表扫描成本：20406。所以没选择此索引执行
注意：这里的 30447 是查询二级索引的 IO 成本和 CPU 成本之和，再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。
再来看下使用 create_time 索引执行的的预估执行成本:
{
"index": "create_time",
"ranges": [
"0x5ec8c516 < create_time"
],
"index_dives_for_eq_ranges": true,
"rows": 50132,
"cost": 60159,
"cause": "cost"
}
可以看到成本是 60159,远大于全表扫描成本 20406，自然也没选择此索引。
再来看计算出的全表扫描成本：
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`person`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100264,
"access_type": "scan",
"resulting_rows": 100264,
"cost": 20406,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100264,
"cost_for_plan": 20406,
"chosen": true
}
]
}
注意看 cost：20406，与我们之前算出来的完全一样！这个值在以上三者算出的执行成本中最小，所以最终 MySQL 选择了用全表扫描的方式来执行此 SQL。
实际上 optimizer trace 详细列出了覆盖索引，回表的成本统计情况，有兴趣的可以去研究一下。
从以上分析可以看出， MySQL 选择的执行计划未必是最佳的，原因有挺多，就比如上文说的行数统计信息不准，再比如 MySQL 认为的最优跟我们认为不一样，我们可以认为执行时间短的是最优的，但 MySQL 认为的成本小未必意味着执行时间短。
各函数对Null的处理
| 函数 | 效果 |
|---|---|
| AVG () | 忽略 NULL 值，而不是将其作为 “0” 参与计算 |
| COUNT(*) | 不忽略NULL 值 |
| COUNT (字段名) | 忽略NULL 值 |
| SUM () | 忽略 NULL 值。且当对多个列运算求和时，如果运算的列中任意一列的值为 NULL，则忽略这行的记录。 |
| GROUP BY | 两个 NULL 值视为相同。 如果运行 ORDER BY ... ASC，则 NULL 值出现在最前，若运行 ORDER BY ... DESC，则 NULL 值出现在最后。 |
| distinct | 无论遇到多少个空值，结果中只返回一个 null |
3513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
