whyMySQLWould you pick the wrong index?

As you can see from the SQL execution process, it is the optimizer’s job to select the index, and the optimizer will choose the execution plan that it thinks is optimal. The optimal execution plan is generally determined by the number of rows scanned, temporary tables, and sorting. For simple queries, if MySQL selects the wrong index, there must be a problem with the number of rows being scanned.

MySQLPolicy for scanning the number of rows

In general, a normal scan counts all rows, but MySQL does not count the number of rows scanned because scanning all rows does too much disk I/O, resulting in performance degradation. So MySQL uses a scanning strategy like this: MySQL samples indexes by selecting pages with N indexes, counting the number of different values on each page, and then multiplying the average value by the number of pages to obtain the cardinality, which is used as the statistical value. This calculation shows that this cardinality is likely to be an inaccurate value, which may cause the optimizer to choose the wrong index.

The optimizer chose the wrong index solution because of cardinality statistics errors

Analyze Table T can be used to fix this problem. Of course, this can be used when only the index cardinality statistics are inaccurate. For complex statements involving joins and sorts, there are other factors to consider.

How to solve the problem of selecting wrong index for complex statement?

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Copy the code

Assume that this statement still picks the wrong index

• Method 1: Adoptforce indexForce an index to be selected
• Method 2: guide the optimizer to select the appropriate index by optimizing the SQL statement. It needs to be analyzed. I don’t know yet. –