Shulou(Shulou.com)05/31 Report--

How do I use count aggregate functions in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. basic use

The basic functions of count are two:

Count the number of data in a column

Count the number of rows in the result set

The amount of data used to obtain the condition. But there are some situations that are different from the impression in use, such as when count acts on one column, multiple columns, and the use of * to express the whole row.

The sample table is as follows:

CREATE TABLE `NewTable` (`id` int (11) NULL DEFAULT NULL, `name` varchar (30) NULL DEFAULT NULL, `city` varchar (50) NULL DEFAULT NULL, `province` varchar (30) NULL DEFAULT NULL, `city` varchar (30) ENGINE=InnoDB)

1.1 the value of NULL is not calculated

If there is a null value, it will be filtered out in the returned result

Select count (country) from person

The returned result is as follows:

If a qualified data item does not exist, the structure returns 0, which is often used to determine whether there is data that meets the condition; the returned data type is bigint.

1.2 handling of count (*)

The treatment of count (*) is a little different. It returns the amount of all data, but does not filter the null value, and it is not equivalent to expanding into all columns, but directly ignores all columns and counts all rows directly. The statement is as follows:

Select count (*) from person

The returned result is as follows:

It is impossible to use count (*) when you want to return the amount of all data, but do not want to include columns that are all NULL, but is it correct to say that count filters NULL when acting on columns?

Select count (id, `name`, country, province, city) from person

That's wrong. Count can only work on a single column, not on multiple columns, so the above is wrong.

In addition, for the count (*) statement, the MyISAM storage engine is optimized so that the number of rows of each table is stored in the storage engine and can be obtained quickly; but in transactional storage engines, such as InnoDB, because multiple transactions are involved

1.3to count (distinct …) Treatment of

Count (distinct …) The number of rows of data that are different from each other but are not NULL. This is different from using only distinct, because distinct does not filter null values, as detailed in the usage of distinct in MySQL.

-returns 0 if there is no eligible data

-this statement can be applied to multiple columns. When there is a difference between columns, the whole row of data is considered to be different, which has the same effect as when distinct acts on multiple columns.

Select count (DISTINCT country) from person

The returned result is as follows:

But for the combination of count (*) and count (distinct), it is as follows:

Select count (DISTINCT *) from person

This statement is incorrect and cannot be executed, so it is different from select count (DISTINCT *) from person.

Second, performance optimization

In general, the count (*) operation requires a large number of rows in the data table to be scanned, and avoiding scanning large amounts of data is the key to optimizing the statement. To solve this problem, we can consider it from the following two angles.

2.1 optimize at the database level

2.1.1 for count (*)

Optimization has been made for count (*) within MySQL, using the explain query as follows:

EXPLAIN select count (*) from person

You can see that the query does not use full table scans or indexes, or even query data tables. In the sample database above, we know that the storage engine of the library is InnoDB, and there is neither primary key nor index.

2.2 count for a single column

The query is as follows:

EXPLAIN select count (country) from person where id > 2

It is found that the entire table is scanned without primary keys and indexes. To avoid scanning a large number of rows in the data, one of the most direct methods is to use indexes:

When the id is set to the general index: INDEX abc (id) USING BTREE.

Execute the query as follows:

EXPLAIN select count (country) from person where id > 2

The results are as follows:

At this point, it is found that the index is not used, and the full table scan is still performed when the following is performed:

EXPLAIN select count (country) from person where id > 4

The results are as follows:

This is a range query using an index, which is obviously better than the one above.

But the question is, why do you use indexes sometimes and sometimes not? In the first query above, possible key was detected but not used? If there is a big god who knows it, read it!

Set id as the primary key and execute the query as follows:

EXPLAIN select count (country) from person where id > 2

The results are as follows:

2.2 optimize at the application level

To optimize at the application level, we can consider introducing a cache subsystem into the system architecture, such as Memcached, which is commonly used in the past, or Redis, which is very popular now, but this will increase the complexity of the system.

Examples of mysql group by and aggregate functions (sum,count, etc.)

First, let's take a look at the mysql aggregate function.

A special function in mysql: aggregate function, SUM, COUNT, MAX, MIN, AVG, etc. The fundamental difference between these functions and other functions is that they generally act on multiple records. For example:

SELECT SUM (score) FROM table

This sql means to query the sum of all the score columns in the table table.

Then we use an example to illustrate how to use aggregate functions in group by statements.

The book table is as follows:

Idfirst_namelast_namecity1JasonMartinToronto2AlisonMathewsVancouver3JamesMathewsVancouver4CeliaRiceVancouver5DavidLarryNew York

Now that we want to query the city in groups and get how many pieces of data there are in each packet, we need the count aggregate function.

SELECT *, count (*) FROM book GROUP BY city

The result is:

After reading the above idfirst_namelast_namecitycount (*) 1JasonMartinToronto12AlisonMathewsVancouver35DavidLarryNew York1, have you learned how to use the count aggregate function in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.