Interview question: Talk about how to optimize MYSQL database queries, mysql database_PHP tutorial

Interview question: Talk about how to optimize MYSQL database query, mysql database

1. Optimize data type

There are many data types in MySQL. If you are a DBA, you are strictly checking the data types according to the principle of optimization, but developers may choose the solution they think is the simplest to speed up coding. Speed, or choosing the most obvious choice, so you may not be faced with the best choice, and if possible, you should try to use general guidelines to change these decisions.

　(1) Avoid using NULL

NULL requires special handling for most databases, and MySQL is no exception. It requires more code, more checks, and special index logic. Some developers are completely unaware that NULL is used when creating a table. is the default value, but most of the time you should use NOT NULL, or use a special value such as 0, -1 as the default value.

　(2) Only smaller fields may be used

After MySQL reads the data from the disk, it stores it in memory, and then uses cpu cycles and disk I/O to read it. This means that the smaller the data type, the smaller the space it takes up. Reading from the disk Or packing it into memory is more efficient, but don't be too obsessed with reducing the data type. If there are any changes in the application in the future, there will be no space. Modifying the table will require reconstruction, which may indirectly cause code changes. This is a headache, so a balance needs to be found.

2. Be careful with character set conversion

The character set used by the client or application may be different from the character set of the table itself. This requires MySQL to perform implicit conversion during operation. In addition, it is necessary to determine whether the character set such as UTF-8 is supported. Multibyte characters, so they require more storage space.

3. Optimize count(my_col) and count(*)

If you use a MyISAM table, using count(*) without a where clause is very fast, because the statistics of the number of rows are very accurate, so MySQL will not look for them row by row. , and then get the number of rows. If there is no null value in the my_col column, then the situation will be the same as mentioned before, that is, count(my_col) will be very fast.

If you use count() when there is a where clause, basically no more optimization can be done. In the where clause, the obvious index columns are exceeded. For complex where clauses, you can only use covering indexes. It's useful.

In addition to the suggestions above, you can also use summary tables. They allow you to keep the contents of the table updated. You can use triggers, or application logic to keep the summary table always up to date, or periodically. Run a batch job to keep the population populated with the latest data information. If you do the latter, your information will be very close, but not exact. It depends on how often the batch job is run. This needs to be weighed against the application's need for accurate information. , and the system overhead of keeping data updated, a balance point must be found between the two.

4. Optimize subquery

When encountering subqueries, the MySQL query optimization engine is not always the most efficient. This is why subqueries are often converted into join queries. The optimizer can already handle join queries correctly. Of course One thing to note is to ensure that the connection column of the connection table (the second table) is indexed. On the first table, MySQL usually performs a full table scan relative to the query subset of the second table. This is Part of the nested loop algorithm.

5. Optimize UNION

Using UNION is an interesting optimization method when spanning multiple different databases. UNION returns data from two unrelated tables, which means that there will be no duplicate rows, and it must also be Sorting data, we know that sorting is very resource-intensive, especially sorting large tables.

UNION ALL can greatly speed up the process. If you already know that your data will not include duplicate rows, or you don’t care whether duplicate rows will appear, using UNION ALL is more suitable in both cases. In addition, some methods can be used in the application logic to avoid duplicate rows, so that the results returned by UNION ALL and UNION are the same, but UNION ALL will not be sorted.

Original text from [Bit Network]: http://soft.chinabyte.com/database/254/11335754.shtml

Related reading

MySQL optimization (how to query SQL statements with low execution efficiency in MySQL)

How to perform joint query on multiple tables with the same structure in mysql tens of millions of databases? How to optimize or set up to improve query speed?

Query optimization and paging testing of tens of millions of data in mysql database

mysql learning experience

8 ways to optimize Mysql database