Mysql-SQL优化-统计某种类型的个数
2023-09-11 14:14:43 时间
有时我们想统计某种类型有多少个,会用这个SQL。全表扫描之余,还要filesort。耗时1.34秒。
mysql> select country,count(*) from t1 group by country; +---------+----------+ | country | count(*) | +---------+----------+ | NULL | 32 | | africa | 524288 | | america | 524288 | | china | 524288 | +---------+----------+ 4 rows in set (1.34 sec) mysql> desc select country,count(*) from t1 group by country; +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ 1 row in set (0.00 sec)
下面是两种优化方法,都是全表扫描,但预计count()要比sum()耗的CPU少点,方法更佳。
mysql> select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1; +--------+---------+--------+ | africa | america | china | +--------+---------+--------+ | 524288 | 524288 | 524288 | +--------+---------+--------+ 1 row in set (0.78 sec) mysql> desc select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1; +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0.00 sec) mysql> select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1; +--------+---------+----------------------+ | africa | america | sum(country='china') | +--------+---------+----------------------+ | 524288 | 524288 | 524288 | +--------+---------+----------------------+ 1 row in set (0.86 sec) mysql> desc select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1; +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1573382 | | +----+-------------+-------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0.00 sec)
版本号mysql5.5.30
相关文章
- 【MySQL高级】Mysql锁问题
- MySQL 用 explain 关注哪些字段
- Mysql错误check the manual that corresponds to your MySQL server version for the right syntax
- 【MySQL进阶-10】mysql语句的执行流程以及集群的高可用
- 【MySQL进阶-02】mysql的explain执行计划以及索引优化
- 在腾讯云轻量云服务器上搭建mysql数据库
- 发现个工具,查询Mongo数据使用mysql语法
- Mysql日期统计自动补充日期,Mysql日期统计日期不连续解决方案
- mac ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- mysql的执行顺序
- python操作mysql数据库系列-操作MySql数据库(五)
- sql: MySQL and Microsoft SQL Server Stored Procedures IN, OUT using csharp code
- idea 社区版连接mysql数据库教程,Cannot connect to “MySQL-5.1.43“. CLIENT_PLUGIN_AUTH is required
- mysql慢调用查询统计开启
- MySql表大小、行大小和列大小的限制
- 【MySQL】MySQL无法插入中文、不持支中文、输入中文报错的解决方法
- MySQL存储过程详解 mysql 存储过程
- mysql group by 与order by的实例分析(mysql分组统计后最大值)
- liunux mysql MySQL表名不区分大小写的设置方法
- mysql分组后占比、累计占比和排序统计各年龄段人数
- mysql按月查询统计(统计近12个月的项目个数)
- mysql 存储过程 演示样例代码
- 【MySQL】mysql更换root密码,全网唯一有用!
- (5.5)mysql高可用系列——MySQL半同步复制(实践)
- (4.2)mysql备份还原——备份概述
- [Mysql] WITH ROOLUP