Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to use mysql's count statistical query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Preface

1. Someone asked me why count query is sometimes fast and sometimes slow.

2. First of all, we should understand the real function of count query.

2-1: count the number of columns

2-1: count rows

Second, principle

1. When counting a column, it will query the number of columns that are not empty. It needs to be calculated.

2. When counting rows, it can get the result directly without adding where, because it can obtain this value directly by using the characteristics of the storage engine, such as count (*).

3. When counting a column, if you know that the column cannot be empty, it will be converted to count (*), thus speeding up the speed.

So: you need to know what you are going to do and how to use it

Expansion: why is it slow? Innodb is a clustered index and supports things at the same time, and it uses real-time statistics in the implementation of count instructions. In the case of no secondary indexes available, executing count causes MySQL to scan the whole table data, which is very inefficient when there are large fields or more fields in the data (each page can only contain fewer pieces of data and more physical pages need to be accessed)

III. Practical application

1, 500000 pieces of data, no index, takes about 13 minutes

2, 500000 pieces of data, indexed, takes about 354ms

3, principle

One data page of mysql is: 16k (default)

Can store 16cm 1024 / (4x8) = 1365 pieces of data (why 4x8 can see how mysql stores pages)

Assume that each page of data consumption is not full, 50%

500000 pieces of data, 500000 paces 1365mm 2 for 732 physical pages

The size is 16k*732 11.4m

Mysql space is allocated by zone. One zone is 1m. If it is continuous, 12 zones need to be read continuously.

Pc hard disk speed 7200 rpm / min = 70m/s

Io addressing time (11.4*8.5ms=96) + read time (11.4m/70m=162ms) = 9600162 = 258ms

This is the end of the content of "how to use mysql's count Statistical query". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.