MySQL Query Optimization _ MySQL

In practice, query optimization is always a hot topic for both Database Systems (DBMS) and database application systems (DBAS. The development of a successful database application system will certainly put a lot of effort into query optimization. Query Optimization not only affects MySQL-optimized MySQL queries

In practice, query optimization is always a hot topic for both Database Systems (DBMS) and database application systems (DBAS. The development of a successful database application system will certainly put a lot of effort into query optimization. Query Optimization not only affects database efficiency, but also brings tangible benefits to the company. MySQL is an open-source software, but its performance is no inferior to that of commercial databases, and its speed is already quite fast. However, to optimize the query, you must consider some technical skills. The following are some of my thoughts on optimizing queries since I learned and used MySQL.

First, what factors should we consider to affect query optimization? It can be divided into machine hardware, indexing, system parameters, and query techniques. Which of the following aspects should we consider first for a query optimization problem? The first index should be taken into consideration. without a doubt, the practice of using other methods to greatly improve performance without using indexes is often very rare, it is a waste of time, but the facts are not absolute. in some cases, it is necessary to suppress the behavior of the optimization program.

Let's talk about indexes first. One of the reasons why indexes can improve the query efficiency is that it allows us to know where the last qualified data row is, and the subsequent data rows do not need to be checked, another reason is that many positioning algorithms have been invented to quickly find out where the first qualified data row is located, and a matching item does not need to be located through linear scanning from the beginning of the index.

When MySQL creates an index, it has different details for different data tables: MyISAM data table, the data will be saved in the data file, and its index value will be saved in the index file, the BDB processing program saves the data value and index value of the same BDB data table in the same file, the InnoDB processing program stores the data and index values of all InnoDB data tables in the same tablespace. These seem to have nothing to do with index building, but they are actually very important. I will discuss them later. In addition, indexing not only benefits document watchband, but also benefits of indexing for associated queries involving multiple data tables.

You must also consider its disadvantages when creating an index. First, indexes consume disk space (which is already very cheap). The more indexes, the more space they consume. For MyISAM data tables, too many indexes will give priority to the index file and the data file size limit. For a BDB data table, because it stores both the data value and index value in the same file, increasing the index will make it faster to reach the size limit of the BDB data file. InnoDB data tables share the storage space in the InnoDB tablespace. Therefore, increasing indexes will inevitably increase the InnoDB tablespace consumption speed. However, as long as the disk space can be increased, the InnoDB tablespace can be expanded by adding components.

Second, too many indexes will slow down the insert, delete, and modify operations on indexed data columns. This is because MySQL must modify all indexes related to it when writing records.

What data columns should we choose to create indexes? How can I create the most used index? How to deal with the impact of data types on indexes? The principle is:

1. The data columns involved in operations such as search, sorting, and grouping should be created. only the data columns that appear in the output report are not good candidates.

2. use unique indexes whenever possible. Because if there are many duplicate values in the data column, the index created on it will not have a good effect.

3. try to compare shorter values for indexing.

4. for a composite index, MySQL will first match its first index column. if it does not match, it will not be used.

5. do not create too many indexes.

6. consider how to compare the data column. For the HEAP data table, it only uses "=" for comparison, and other comparison operations won't help.

7. use slow query logs to find the queries with poor performance. But do not think this is absolute, because MySQL will write all queries that do not use indexes into this log, you need to choose to view this log.

8. try to declare the data column as not null, so that it can be excluded from the query of NULL values. in some cases, it can play a major role.

9. if the value of a string data column is limited, the ENUM type should be given priority, because MySQL uses the numeric method to process this string, the speed is much faster than processing strings.