This is the 8th day of my participation in the August More Text Challenge

The slow query

The slow query log of MySQL is used to record statements whose response time exceeds the threshold. In particular, the SQL whose running time exceeds the value of long_query_time (default value: 10s) is recorded in the slow query log.

Slow query logs record not only select statements but also SQL statements whose running time exceeds the value of long_QUERy_time, such as UPDATE statements.

In MySQL, slow SQL logs are disabled by default. You need to manually enable slow SQL logs

Show variables like '%slow_query_log%'Copy the code

The index

An index is a special file that contains a reference pointer to all the records in a table

An index is a data structure, a database index, that allows us to quickly query and update data in a database table

Indexes can greatly improve the speed of MYSQL retrieval

The index type

  • Storage structure partitioning

    • BTree index (B+Tree index and B-tree index)
    • Hash index: key-value pairs
    • Full text index: only supported by MyISAM engine
    • R-tree index: Only MyISAM, BDb, InnoDb, NDb, and Archive support this type of index

  • Application hierarchy

    • Plain index: accelerates queries only
    • Unique index: accelerated query, unique column value (can have NULL)
    • Primary key index: accelerated query, unique column value (no NULL), and only one in the table
    • Combined index: An index composed of multiple column values that is specifically used for combined searches and is more efficient than index merges
    • Full-text index: segmentation of text content, search

Interview question: Hit the index but still execute slowly

First, let’s answer a question: is there a relationship between using an index and entering a slow query?

There is no necessary connection. A slow query is generated when the execution time exceeds the value of long_query_time. This may be caused by network or database jitter. Using an index represents the execution of an SQL statement. So there is no necessary connection between them.

So, if the index is hit but the SQL is still slow, the possible reasons are:

  • The index that is hit may not be the best index and you need to adjust the index Settings
  • Index fields are duplicate or too empty
  • The query scope is too wide, forming a full index scan
  • Failure to utilize an overwrite index results in a back table
  • Index field data distribution is too random, resulting in a large number of random I/OS even if there is no query back to the table