mysql 优化之COUNT(*)效率分析
刚给一个朋友解决他写的Discuz!插件的问题,说到MySQL的COUNT(*)的效率,发现越说越说不清楚,干脆写下来,分享给大家。
COUNT(*)与COUNT(COL)
网上搜索了下,发现各种说法都有:
比如认为COUNT(COL)比COUNT(*)快的;
认为COUNT(*)比COUNT(COL)快的;
还有朋友很搞笑的说到这个其实是看人品的。
在不加WHERE限制条件的情况下,COUNT(*)与COUNT(COL)基本可以认为是等价的;
但是在有WHERE限制条件的情况下,COUNT(*)会比COUNT(COL)快非常多;
具体的数据参考如下:
代码如下 | 复制代码 |
> SELECT COUNT(*) FROM cdb_posts where fid = 604; mysql> SELECT COUNT(tid) FROM cdb_posts where fid = 604; mysql> SELECT COUNT(pid) FROM cdb_posts where fid = 604; |
COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数。还有有区别的。
COUNT时的WHERE解决办法
顺便提下mysql的DISTINCT的关键字有很多你想不到的用处
1.在count 不重复的记录的时候能用到
比如
代码如下 | 复制代码 |
SELECT COUNT( DISTINCT id ) FROM tablename; |
就是计算talbebname表中id不同的记录有多少条
2,在需要返回记录不同的id的具体值的时候可以用
比如
代码如下 | 复制代码 |
SELECT DISTINCT id FROM tablename; |
返回talbebname表中不同的id的具体的值
3.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义
比如
代码如下 | 复制代码 |
SELECT DISTINCT id, type FROM tablename; |
实际上返回的是 id与type同时不相同的结果,也就是DISTINCT同时作用了两个字段,必须得id与tyoe都相同的才被排除了,与我们期望的结果不一样
简单说下,就是COUNT的时候,如果没有WHERE限制的话,MySQL直接返回保存有总的行数
而在有WHERE限制的情况下,总是需要对MySQL进行全表遍历。
优化总结,对于MyISAM表来说:
代码如下 | 复制代码 |
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择; 2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value' 这种查询; 3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = 'value' 的出现。 |

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to improve performance by optimizing the AVG function in MySQL MySQL is a popular relational database management system that contains many powerful functions and functions. The AVG function is widely used in calculating averages, but because this function needs to traverse the entire data set, it will cause performance problems in the case of large-scale data. This article will introduce in detail how to optimize the AVG function through MySQL to improve performance. 1. Using indexes Indexes are the most important part of MySQL optimization.

MySQL is a relational database management system widely used in the field of e-commerce. In e-commerce applications, it is crucial to optimize and secure MySQL. This article will analyze MySQL’s optimization and security project experience in e-commerce applications. 1. Performance optimization database architecture design: In e-commerce applications, database design is the key. Reasonable table structure design and index design can improve the query performance of the database. At the same time, using table splitting and partitioning technology can reduce the amount of data in a single table and improve query efficiency.

mysql count is an aggregate function used to return the number of rows matching specified matching conditions; the syntax of the count function is such as "select count(*) from user;" which means counting all records, including NULL.

MySQL database is a common relational database. As the amount of data in the database increases and query requirements change, underlying optimization becomes particularly important. In the process of underlying optimization of MySQL, SQL statement optimization is a crucial task. This article will discuss common techniques and principles for SQL statement optimization and provide specific code examples. First of all, SQL statement optimization needs to consider the following aspects: index optimization, query statement optimization, stored procedure and trigger optimization, etc. In these aspects, we will have

MySQL is a widely used open source database management system for storing and managing large amounts of data. However, when using MySQL, you may encounter a variety of problems, from simple syntax errors to more complex performance issues and glitches. In this article, we will explore some of the most common MySQL problems and solutions. Connection Problems Connection problems are common. If you cannot connect to the MySQL server, please check the following points: 1) Whether the MySQL server is running 2) Whether the network connection is normal 3) MySQ

How to properly configure and optimize MySQL's double-write buffering technology Introduction: MySQL's double-write buffering technology is an important technology that improves data security and performance. This article will introduce how to properly configure and optimize MySQL's double-write buffering technology to better protect data and improve database performance. 1. What is double-write buffering technology? Double-write buffering technology is an I/O optimization technology of MySQL. It can significantly reduce the number of disk I/O operations and improve the write performance of the database. When MySQL performs a write operation, first

How to optimize MySQL connection number management MySQL is a popular relational database management system that is widely used in various websites and applications. In the actual application process, MySQL connection number management is a very important issue, especially in high concurrency situations. Reasonable management of the number of connections can improve the performance and stability of the system. This article will introduce how to optimize MySQL connection number management, including detailed code examples. 1. Understand connection number management In MySQL, the number of connections refers to the number of connections that the system can connect at the same time.

As the amount of data continues to increase and the number of users continues to increase, databases have become an indispensable part of modern enterprises. Therefore, the improvement and optimization of database efficiency is crucial to the operation and development of enterprises. In the database optimization process, MySql optimization and SQL optimization are the two most common optimization strategies. MySQL is a widely used open source relational database management system. Its optimization strategy is mainly aimed at the MySql system itself, and SQL is a structured query language that is often used with various data
