Some common misunderstandings about Mysq

Common misunderstandings

1. ##count(1) and count(primary_key) are better than count(*)

In order to count the number of records, many people use count(1) and count(primary_key) instead of count(*). They think this performs better. In fact, this is a misunderstanding. For some scenarios, this may result in worse performance, because the database has made some special optimizations for the count(*) counting operation.

1. count(column) and count(*) are the same

This misunderstanding even exists among many senior engineers Or it is common among DBAs, and many people take it for granted. In fact, count(column) and count(*) are completely different operations and have completely different meanings.

count(column) indicates how many records in the result set the column field is not empty

count(*) indicates how many records there are in the entire result set

1. Selecting a,b from... can allow the database to access less data than selecting a,b,c from...

This misunderstanding is mainly It exists among a large number of developers. The main reason is that they don't know much about the storage principles of the database.

In fact, most relational databases are stored in rows, and data access operations are based on a fixed-size IO unit (called block or page). Generally 4KB, 8KB... Most of the time, multiple rows are stored in each IO unit, and each row stores all the fields of the row (except for special types of fields such as lob).

So, whether we take one field or multiple fields, the amount of data that the database needs to access in the table is actually the same.

Of course, there are exceptions, that is, our query can be completed in the index. That is to say, when only two fields a and b are fetched, there is no need to return the table, and the field c is not In the index used, it is necessary to return to the table to obtain its data. In this case, the IO volume between the two will be quite different.

1. order by must require a sorting operation

We know that the index data is actually ordered, if our If the required data is in the same order as an index, and our query is executed through this index, the database will generally omit the sorting operation and return the data directly, because the database knows that the data already meets our sorting needs.

In fact, using indexes to optimize SQL with sorting requirements is a very important optimization method

Extended reading: Analysis of the implementation of ORDER BY in MySQL, the basic implementation principle of GROUP BY in MySQL and The basic implementation principle of MySQL DISTINCT has a more in-depth analysis in these three articles, especially the first one

1. If there is filesort in the execution plan, the disk will be processed File sorting

We can’t blame us for this misunderstanding, but it’s because of the wording problem used by MySQL developers. filesort is the information we may see displayed in the "Extra" column when we use the explain command to view the execution plan of a SQL.

In fact, as long as a SQL statement requires a sorting operation, "Using filesort" will be displayed, which does not mean that there will be a file sorting operation.

Extended reading: Understanding filesort in the MySQL Explain command output, I have a more detailed introduction here

• Basic principles

1. As few joins as possible

The advantage of MySQL is simplicity, but this is actually its disadvantage in some aspects. The MySQL optimizer is highly efficient, but due to its limited amount of statistical information, there is more possibility of deviations in the optimizer's work process. For complex multi-table Join, on the one hand, due to its limited optimizer, and on the other hand, insufficient efforts have been made in Join, so the performance is still far behind that of relational database predecessors such as Oracle. But if it is a simple single-table query, this gap will be very small and even better than these database predecessors in some scenarios.

1. Sort as little as possible

Sorting operations will consume more CPU resources, so reducing sorting can reduce cache hits In scenarios with sufficient IO capabilities such as high rates, it will greatly affect the response time of SQL.

For MySQL, there are many ways to reduce sorting, such as:

• The misunderstanding mentioned above is to optimize by using index to sort

• Reduce the number of records participating in sorting

• Do not sort data unless necessary

• Avoid using resource-consuming operations. SQL statements with DISTINCT, UNION, MINUS, INTERSECT, ORDER BY will start the SQL engine execution , a resource-intensive sorting (SORT) function. DISTINCT requires one sorting operation, while others require at least two sorting operations

The above is the detailed content of Some common misunderstandings about Mysq. For more information, please follow other related articles on the PHP Chinese website!