mysql中对一个表的id建立了唯一索引，那么查询的select count(*) ,select count(1) ,select count(id)，select count(列名)

最新推荐文章于 2023-12-26 11:55:09 发布

guoyiguang2

最新推荐文章于 2023-12-26 11:55:09 发布

阅读量576

点赞数

分类专栏： mysql 文章标签： mysql 索引 sql

本文链接：https://blog.csdn.net/qq_38807606/article/details/114432847

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

1.mysql中对一个表的id建立了唯一索引，那么查询的select count(*) ,select count(1) ,select count(id)，select count(列名) 的查询结果又什么不同，及索引的使用情况

先说结论：

①对id 建立索引，select count(*) ,select count(1) 和select count(id) 这三者等效，会统计为null的数据

都会走index 级别的索引，并且不会回表，

因为只做统计数据，没有用到其他字段（Extra的 Using index 就说明了这种情况）

② select count(某个列名)，只统计该列名不为null的情况，如果没有对该列名，建立索引，那么就会全表

用途：在数据量特别大的时候测试，两个表之间有没有关系，就用count(主键) 基本上不用的等待就可以查到想要的结果（尤其是在表数据量特别大的时候特别有用）

比如：

#  最新认知（重要） ：inner join 取交集不是 数目取交集，eg： boys 只有一个 id 为 1 的数据，但是 girl有 8 条 boy_id 为 1 的记录，
#  最后的的结果 数量就是 8，大于 boys 表的一条数据（唯一确定的是小于等于 笛卡尔集 （男生表count*女生表count））
# 8 有女朋友的男生信息
select count(boys.id) from boys inner join girl on  boys.id = girl.boy_id ;

以下是测试sql和脚本：

create table sql_test.boys
(
    id      int                     not null,
    name    varchar(20)             null,
    sex     varchar(20) default '男' null comment '默认男',
    age     varchar(20)             null,
    address int                     null,
    constraint boys_id_uindex
        unique (id)
)
    comment '男生表';

 select * from boys ;
# +--+----+---+---+-------+
# |id|name|sex|age|address|
# +--+----+---+---+-------+
# |1 |慕容皝 |男  |22 |NULL   |
# |2 |慕容冲 |男  |33 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# |4 |慕容博 |男  |55 |NULL   |
# |5 |慕容雪 |女  |55 |NULL   |
# |6 |慕容复 |男  |55 |NULL   |
# |7 |慕容龙城|女  |55 |NULL   |
# +--+----+---+---+-------+

# 因为我对id 建立了唯一索引
# 7 type : index ;key :boys_id_uindex ;Extra :Using index（ps：不需要表，只做统计）
select count(*) from boys;
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type |possible_keys|key           |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |index|NULL         |boys_id_uindex|4      |NULL|8   |100     |Using index|
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# 7
select count(1) from boys;
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type |possible_keys|key           |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |index|NULL         |boys_id_uindex|4      |NULL|8   |100     |Using index|
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# 说明： select count(*) from boys 和  select count(1) from boys; 两条sql 都走了索引，
# 并且没有回表，说明 和下面的sql等效（说明：boys 上 对 id 建立了 唯一索引）
# 7
select count(id) from boys;
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type |possible_keys|key           |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |index|NULL         |boys_id_uindex|4      |NULL|8   |100     |Using index|
# +--+-----------+-----+----------+-----+-------------+--------------+-------+----+----+--------+-----------+


# 0 type:all   ； 因为 address 没加索引，走的是 全表扫描
select count(address) from boys;
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
# |id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
# |1 |SIMPLE     |boys |NULL      |ALL |NULL         |NULL|NULL   |NULL|8   |100     |NULL |
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+

# 增加一条数据

select * from boys ;
# +--+----+---+----+-------+
# |id|name|sex|age |address|
# +--+----+---+----+-------+
# |1 |慕容皝 |男  |22  |NULL   |
# |2 |慕容冲 |男  |33  |NULL   |
# |3 |慕容垂 |男  |44  |NULL   |
# |4 |慕容博 |男  |55  |NULL   |
# |5 |慕容雪 |女  |55  |NULL   |
# |6 |慕容复 |男  |55  |NULL   |
# |7 |慕容龙城|女  |55  |NULL   |
# |8 |慕容吹雪|男  |NULL|NULL   |
# +--+----+---+----+-------+

# 8
select count(*) from boys;
# 8
select count(1) from boys;
# 0
select count(address) from boys;
# 7
select count(age) from boys;








# 题外话： 如果要导出 性别为男生的数据 到另一个表 sql  select * from boys where sex = 男 同时dataGrip 查询结果右上角 extractors（翻译：提取者;抽出器;提取器）
#           改为sql insert 模式 ：例子如下：
select * from boys where sex = '男' ;
# 提取器的为 insert sql 后 查询的结果如下：
# INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (1, '慕容皝', '男', '22', null);
# INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (2, '慕容冲', '男', '33', null);
# INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (3, '慕容垂', '男', '44', null);
# INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (4, '慕容博', '男', '55', null);
# INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (6, '慕容复', '男', '55', null);

#  建表语句
create table sql_test.boys
(
    id      int                     not null,
    name    varchar(20)             null,
    sex     varchar(20) default '男' null comment '默认男',
    age     varchar(20)             null,
    address int                     null,
    constraint boys_id_uindex
        unique (id)
)
    comment '男生表';

# 用datagrip 查询结果 的 提取器 导出 sql语句
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (1, '慕容皝', '男', '22', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (2, '慕容冲', '男', '33', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (3, '慕容垂', '男', '44', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (4, '慕容博', '男', '55', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (5, '慕容雪', '女', '55', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (6, '慕容复', '男', '55', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (7, '慕容龙城', '女', '55', null);
INSERT INTO sql_test.boys (id, name, sex, age, address) VALUES (8, '慕容吹雪', '男', null, null);

查看表关系的sql（尤其是在数据量非常大的情况下使用，非常有效）



select * from girl;
# +--+----+------+---+
# |id|name|boy_id|sex|
# +--+----+------+---+
# |1 |黄蓉  |1     |女  |
# |2 |李莫愁 |2     |女  |
# |3 |陆无双 |2     |女  |
# |4 |程英  |3     |女  |
# |5 |周紫墨 |3     |女  |
# |6 |宋小婉 |3     |女  |
# |7 |侯小妹 |3     |女  |
# |8 |小黄蓉 |3     |女  |
# +--+----+------+---+

# 8 有女朋友的男生信息
select count(boys.id) from boys inner join girl on  boys.id = girl.boy_id ;
#  最新认知（重要） ：inner join 取交集不是 数目取交集，eg： boys 只有一个 id 为 1 的数据，但是 girl有 8 条 boy_id 为 1 的记录，
#  最后的的结果 数量就是 8，大于 boys 表的一条数据（唯一确定的是小于等于 笛卡尔集 （男生表count*女生表count））
select boys.* from boys inner join girl on  boys.id = girl.boy_id ;
# +--+----+---+---+-------+
# |id|name|sex|age|address|
# +--+----+---+---+-------+
# |1 |慕容皝 |男  |22 |NULL   |
# |2 |慕容冲 |男  |33 |NULL   |
# |2 |慕容冲 |男  |33 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# |3 |慕容垂 |男  |44 |NULL   |
# +--+----+---+---+-------+
# 8
select count(*) from boys inner join girl on  boys.id = girl.boy_id ;

# +--+-----------+-----+----------+------+--------------+--------------+-------+--------------------+----+--------+-----------+
# |id|select_type|table|partitions|type  |possible_keys |key           |key_len|ref                 |rows|filtered|Extra      |
# +--+-----------+-----+----------+------+--------------+--------------+-------+--------------------+----+--------+-----------+
# |1 |SIMPLE     |girl |NULL      |ALL   |NULL          |NULL          |NULL   |NULL                |7   |100     |Using where|
# |1 |SIMPLE     |boys |NULL      |eq_ref|boys_id_uindex|boys_id_uindex|4      |sql_test.girl.boy_id|1   |100     |Using index|
# +--+-----------+-----+----------+------+--------------+--------------+-------+--------------------+----+--------+-----------+

create table girl
(
    id     int         null,
    name   varchar(20) null,
    boy_id int         null,
    sex    varchar(40) null
)
    comment '女性表';

#  给 boy_id 建立索引后

create index girl_boy_id_index
    on sql_test.girl (boy_id);


# 给  boy_id 建立唯一 索引后
# 8
select count(boys.id) from boys inner join girl on  boys.id = girl.boy_id ;
# +--+-----------+-----+----------+------+-----------------+-----------------+-------+--------------------+----+--------+------------------------+
# |id|select_type|table|partitions|type  |possible_keys    |key              |key_len|ref                 |rows|filtered|Extra                   |
# +--+-----------+-----+----------+------+-----------------+-----------------+-------+--------------------+----+--------+------------------------+
# |1 |SIMPLE     |girl |NULL      |index |girl_boy_id_index|girl_boy_id_index|5      |NULL                |7   |100     |Using where; Using index|
# |1 |SIMPLE     |boys |NULL      |eq_ref|boys_id_uindex   |boys_id_uindex   |4      |sql_test.girl.boy_id|1   |100     |Using index             |
# +--+-----------+-----+----------+------+-----------------+-----------------+-------+--------------------+----+--------+------------------------+