MySQL 的 select count() 优化
来源:互联网 发布:健身能保持年轻吗 知乎 编辑:程序博客网 时间:2024/05/22 23:37
在线应用里面经常要统计某时间段里面的订单量,查询量之类。有的时候为了在同一页面生产不同类别的报表,可能会从不同角度去统计数据库或者在不同的表里面去做统计。这个时候有必要选择是一次查询出所有结果还是多次查表呢?本文我们结合例子来看一下那种方法的效率会高一些。
我们现在有如下的表 transactions:
mysql> desc transactions;+---------------+--------------+------+-----+-------------------+-----------------+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+-------------------+-----------------+| trsid | int(11) | NO | PRI | NULL | auto_increment || usrid | int(11) | YES | MUL | NULL | || trsproperty | varchar(80) | YES | | NULL | || trsvalue | int(8) | YES | | NULL | || trstype | varchar(100) | YES | | NULL | || trsmodifiedts | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || trscreatedts | timestamp | YES | MUL | NULL | |+---------------+--------------+------+-----+-------------------+----------------+
分次查询会比一次大量查询更高效,因为每次的数据量较小,效率反而高
mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE YEAR( trscreatedts) = '2017' AND usrid = '48' AND trsproperty = 'booking';+--------+| total |+--------+| 406557 |+--------+1 row in set (2.15 sec)mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE YEAR( trscreatedts) = '2017' AND usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 3196754 |+---------+1 row in set (9.33 sec)
分开查的时间总和为 11.48 sec,现在我们来看一下如果一次查询:
mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE YEAR( trscreatedts) = '2017' AND usrid in ('48', '66') AND trsproperty = 'booking' group by usrid;+---------+| total |+---------+| 3196731 || 406557 |+---------+2 rows in set (15.76 sec)
在不加WHERE限制条件的情况下,COUNT( * )与COUNT(COL)基本可以认为是等价的;但是在有WHERE限制条件的情况下,COUNT( * )会比COUNT(COL)快, 测试结果如下:
mysql> SELECT COUNT( * ) as total FROM transactions WHERE UNIX_TIMESTAMP( trscreatedts ) BETWEEN 1498867200 AND 1562716800 AND usrid = '66' AND trsproperty = 'booking' ;+---------+| total |+---------+| 1038593 |+---------+1 row in set (9.35 sec)mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE UNIX_TIMESTAMP( trscreatedts ) BETWEEN 1498867200 AND 1562716800 AND usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 1038599 |+---------+1 row in set (9.77 sec)
通过建索引可以提高查询效率。
mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 8793563 |+---------+1 row in set (8.23 sec)mysql> ALTER TABLE `timaticweb2`.`transactions` ADD INDEX `usrid_trsproperty` (`usrid` ASC, `trsproperty` ASC);mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 8793569 |+---------+1 row in set (3.21 sec)
另外,很多人说的用 like ‘ 2017%’代替函数运算YEAR(timestamp) 可以使用索引从而提高查询速度。会用到索引确实,但是查询的时间在这里体现不大。说明了时间运算和普通的查询有所区别。
mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE YEAR( trscreatedts) = '2017' AND usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 3196217 |+---------+1 row in set (8.87 sec)mysql> SELECT COUNT( usrid ) as total FROM transactions WHERE trscreatedts like '2017%' AND usrid = '66' AND trsproperty = 'booking';+---------+| total |+---------+| 3196213 |+---------+1 row in set, 1 warning (9.53 sec)
阅读全文
0 0
- MySQL 的 select count() 优化
- Select count(*) 的优化
- 关于HIVE的SELECT count(*) 优化
- oracle select count(*) 优化
- 优化select count(*) (转oracle培训老师的文章)
- 优化select count(*) (转oracle培训老师的文章)
- MySQL 大表的count()优化
- mysql 5.7中关于count(*)的优化
- Mysql 中SELECT COUNT用法
- mysql优化(count 、rand)
- mysql count 优化
- mysql count 优化
- select count的差异
- Count (*),Select Count(1),Select Count(column)的区别
- MySQL select count(*)、select count(1)效率对比
- mysql select count(*) 与 select count(id)效率比较
- MYSQL语句优化:limit和count的优化
- 一种优化mysql select like %%的方法
- gulp 使用流程
- czl蒻蒟的OI之路9
- 计算机网络第一次作业
- 如何防止网站被攻击
- Linux 防火墙笔记
- MySQL 的 select count() 优化
- 51NOD1799 二分答案 【组合+分块打表】
- 关于数据序列化(3),JSON的方式,FastJson序列化对象和List集合示例
- Freemarker实例教程
- JavaScript:void(0)含义
- PowerPoint, Word, Ole操作
- ajax请求导致status为canceled的原因
- Linux驱动的并发处理---原子操作
- 各种排序算法(JS实现)