The purpose of the optimizer in choosing an index is to find the most optimal execution plan and execute the statement at the smallest cost. In the database, the number of rows scanned is one of the factors that affects the execution cost. The fewer the number of rows scanned, the less the number of times the disk data is accessed and the less the CPU resources consumed.
Before MySQL starts executing a statement, it cannot accurately know how many records satisfy the condition, but can only estimate the number of records based on statistical information.
This statistical information is the "distinguishability" of the index. The more different values on an index, the better the distinguishability of the index. And the number of different values on an index is called "cardinality" (cardinality). That is to say, the larger this cardinality, the better the distinguishability of the index.
InnoDB will by default select N data pages, count the different values on these pages to get an average, and then multiply it by the number of pages in this index to get the cardinality of this index. Since the data table will continue to be updated, the index statistics will not remain unchanged. So, when the number of changed data rows exceeds 1/M, it will automatically trigger a re-indexing statistics.
In MySQL, there are two ways to store index statistics. You can choose by setting the value of the parameter innodb_stats_persistent:
- When set to on, the statistics information will be persistently stored. At this time, the default N is 20 and M is 10.
- When set to off, the statistical information is only stored in memory. At this time, the default N is 8 and M is 16.
MySQL selects the wrong index because it fails to accurately judge the number of rows scanned.
Index selection anomalies and handling
- Adopt force index to forcibly select an index. The index specified by force index is in the candidate index list, and this index will be directly selected without evaluating the execution cost of other indexes.
- Modify the statement to guide MySQL to use the index we expect.
- Create a more suitable index to provide the optimizer with a choice, or delete the misused index.
For string indexes, use prefix indexes. Defining a good length can save space and does not require too much additional query cost. Using a prefix index will not be able to make use of the query performance optimization of the covering index.
Other methods:
- The first method is to use reverse storage.
- The second method is to use a hash field.
- Both methods do not support range queries.
Their differences are mainly reflected in the following three aspects:
- In terms of the extra space occupied, the reverse storage method will not consume extra storage space on the primary key index, while the hash field method needs to add a field. Of course, the prefix length of 4 bytes used in the reverse storage method is not enough. If it is longer, this consumption is almost offset by the extra hash field.
- In terms of CPU consumption, the reverse method requires an extra call to the reverse function each time it is written and read, while the hash field method requires an extra call to the crc32() function. If you only look at the computational complexity of these two functions, the extra CPU resources consumed by the reverse function will be less.
- In terms of query efficiency, the query performance of the hash field method is relatively more stable. Because the value calculated by crc32 has a probability of conflict, but the probability is very small, it can be assumed that the average number of rows scanned per query is close to 1. The reverse storage method is still using the prefix index method, which is to say it will increase the number of rows scanned.
When the content of the memory data page is inconsistent with the disk data page, we call this memory page a "dirty page". After the memory data is written to the disk, the content of the data pages in memory and on the disk is consistent, which is called a "clean page". The process of writing memory data to disk is called flush.
Situations that trigger the database flush:
- InnoDB's redo log is full. At this time, the system will stop all update operations, advance the checkpoint, and leave room in the redo log for continued writing.
- The system memory is insufficient. When a new memory page is needed and there is not enough memory, some data pages need to be eliminated to free up memory for other data pages to use. If the eliminated one is a "dirty page", it needs to be written to the disk first.
- When MySQL thinks the system is "idle".
- The case of normal MySQL shutdown. At this time, MySQL will flush all dirty pages in memory to the disk, so the next time MySQL starts, it can directly read data from the disk, and the startup speed will be very fast.
The first is "the redo log is full, and you need to flush the dirty page". This situation is what InnoDB tries to avoid. Because when this happens, the whole system can no longer accept updates, all updates must be blocked. If viewed from monitoring, the number of updates will drop to 0 at this time.
The second is "there is not enough memory, you need to write the dirty page to the disk first", this situation is actually normal. InnoDB uses a buffer pool to manage memory, and there are three states of memory pages in the buffer pool:
- The first kind are the pages that haven't been used yet.
- The second kind are the pages that have been used and are clean.
- The third kind are the pages that have been used and are dirty.
Too many dirty pages that need to be evicted can lead to significantly longer response times for a query.
If the log is full and all updates are blocked, the write performance drops to zero. This situation is unacceptable for sensitive businesses.
Control Strategy for InnoDB Flushing Dirty Pages
Correctly inform the host where InnoDB resides of its IO capability so that InnoDB knows how fast it can flush dirty pages when necessary.
By using the
innodb_io_capacity parameter, you can inform InnoDB of your disk capacity. This value should ideally be set to the IOPS of the disk. Disk IOPS can be tested using the fio tool.Controlling the Speed of Flushing Dirty Pages
The flushing speed of InnoDB depends on two factors: the ratio of dirty pages and the speed of writing to the redo log.
The
innodb_max_dirty_pages_pct parameter represents the upper limit for the dirty page ratio, with a default value of 75%. InnoDB calculates a number in the range of 0 to 100 based on the current dirty page ratio (let's assume it as M).InnoDB assigns a serial number to each log written. Let's assume N as the difference between the current serial number and the serial number corresponding to the checkpoint. InnoDB calculates a number in the range of 0 to 100 based on this N.
The engine then controls the speed of flushing dirty pages according to the maximum of F1(M) and F2(N) denoted as R, multiplying by the capacity defined by
innodb_io_capacity times R%.There's a mechanism in MySQL that may slow down your queries: when preparing to flush a dirty page, if the neighboring page happens to be dirty, it will also be flushed together. This mechanism of 'sinking the neighbor' can propagate further, that is, for each neighboring data page, if its adjacent page is also dirty, it will also be included in the flush.
In InnoDB, the
innodb_flush_neighbors parameter controls this behavior. When its value is 1, the 'guilt by association' mechanism mentioned above will be in effect. When its value is 0, it implies that each page is responsible for its own flushing.This optimization was very meaningful in the era of mechanical hard drives, as it could greatly reduce random IO.
However, if you are using devices like SSDs with higher IOPS (IO per second), it is recommended that you set the value of
innodb_flush_neighbors to 0. Because at this time, IOPS is often not a bottleneck, and 'only flushing yourself' can complete the necessary flushing of dirty pages more quickly, reducing the response time of SQL statements.In MySQL 8.0, the default value of the
innodb_flush_neighbors parameter is already 0.
