After the table is indexed, sql can select the right index when the amount of data is small, but when the amount of data becomes large, it cannot select the correct index. We analyze the reasons for mysql’s wrong index selection from the following aspects.

Impact the execution of sql cost analysis

  1. Analysis of scanned lines: The fewer scanned lines, the less disk data is accessed and the less CPU resources are consumed.
  2. Optimizer analysis: The optimizer will make a comprehensive judgment based on factors such as whether to use temporary tables and whether to sort.

How to judge the number of scanning lines?

  1. Estimate the number of records according to statistical information.
  2. Discrimination: The more different values on an index, the better the discrimination of the index.
  3. Cardinality: the number of different values on an index. The larger the cardinality, the better the discrimination of the index.

How does MySQL get the cardinality of an index?

  1. Sampling statistics are adoptedBy default, InnoDB will select N data pages, count the different values on these pages, get an average, and then multiply the number of pages in the index to get the base of the index.
  2. Store index statisticsSet the value of the mysql configuration parameter innodb_stats_persistent to select
    1）When set to on, the statistics will be persisted and stored. In this case, the default N is 20 and M is 10.
    2）When set to off, it means that statistical information is only stored in memory. In this case, the default N is 8 and M is 16

The optimizer does not use the correct index reasons

When sql queries, mysql will determine whether to query back to the table, and the optimizer will take this time into account. If the query back to the table is costly, the optimizer will abandon the index.

Index selection exceptions and handling

  1. Use force index to force an index.
  2. Consider modifying the statement to instruct mysql to use the index we expect.
  3. you can create a more appropriate index, to provide the optimizer to make a choice, or delete the misused index.