Why the MySQL query optimizer might choose the wrong execution plan

The following reasons may cause the MySQL optimizer to choose the wrong execution plan:

A: The statistics are inaccurate,MySQL relies on the statistics provided by the storage engine to evaluate the costs, but some of the information provided by the storage engine is accurate, and some of the engines may have a large bias, such as:InnoDB because of its MVCC architecture, Does not maintain accurate statistics on the number of rows in a data table.

B: The cost estimate in the execution plan is not the same as the cost of actual execution, so even if the statistics are accurate, the execution plan given by the optimizer may not be optimal, such as: Sometimes an execution plan needs to read more pages, but its actual execution cost is smaller, Because if these pages are in order or these pages are in memory, the cost of access will be small

Many. The MySQL layer does not know which pages are in memory and which are on disk, so how many times physical IO is not expected during the actual execution of the query.

C:MySQL's best may be different from what you think is optimal, you might want to do it as short as possible, but MySQL chooses the optimal execution plan based on the cost model, and sometimes this is not the fastest execution plan (because MySQL's cost estimates are based primarily on the number of scanned rows, and if the rows are sequential or in memory, then the scan will be fast, and conversely, if the rows are on disk and are unordered , random reads will occur , even if fewer rows are scanned , it may take longer to execute, while the optimizer does not take into account any level of caching when evaluating costs, assuming that it requires one disk IO to read any data .

D:MySQL never considers other concurrently executed queries, which may affect the speed of the current query

E:MySQL is not always based on cost optimization, and sometimes based on some fixed rules, such as: if there is a match () clause for full-text search, full-text indexing is used when there is a full-text index, even if you sometimes use other indexes and The Where condition can be much faster than this way, andMySQL still uses the corresponding full-text index.

F:MySQL does not consider uncontrolled operating costs, such as executing stored procedures or the cost of a user-defined function

G: The optimizer is sometimes unable to estimate all possible execution plans, so it may miss the actual optimal execution plan.

Why the MySQL query optimizer might choose the wrong execution plan