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

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             |
# +--+-----------+-----+----------+------+-----------------+-----------------+-------+--------------------+----+--------+------------------------+

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页