This is the 14th day of my participation in the August More Text Challenge. For details, see:August is more challenging

preface

When it comes to slow data query, our first reaction is to add indexes. Indeed, adding indexes can significantly improve query speed, but why indexes can optimize query speed, and how to do it at the bottom.

Index to life

When we look up a dictionary, we will first go through the table of contents, locate the specific page number, and then quickly find, so that the table of contents can be regarded as an index of all the words.

The purpose of the data table index

  • An index is a special “table of contents” for a table
  • The purpose of indexes is to prevent Full table scans.
  • The type of index storage is determined by the storage engine

Data table index classification

1. From the storage structure:

BTree index, Hash index, full-index, and R-tree index

2. Partition from application layer:

Common index, unique index, compound index

3. Divide data by physical storage order:

Clustered index, non-non-clustered index

Commonly used indexes

  • The B+Tree index applies to range search
  • Hash indexes are used for precise lookups
  • MySQLIn theInnoDbwithMyISAMUSES aB+TreeThe index
  • B+Tree index uses Tree linked list structure to build data “directory”

Why is it officially recommended to use an auto-increasing primary key as an index?

After new data is added, the scope of data update is the smallest

What are the differences between InnoDB and MyISAM BTree indexes?

InnoDB organizes data by Id. MyISAM organizes data by physical address

The difference between b-tree and B+Tree

How do I know if the current SQL execution is indexed?

Use the explain keyword to view the execution plan

EXPLAIN SELECT * FROM `sys_user` WHERE user_id = 1
Copy the code

Use basic skills

  • Precise matching, allowing the BTree index
  • Range matching, allowing BTree indexes
  • Ensure that the data type in the query conditions is consistent with the type defined by the field. Otherwise, the index may become invalid
  • String field BTree index allows “prefix query” LIKE “XXX %”
  • Suffix queries with fuzzy matches such as “LIKE “% XXX %” or “% XXX” are not supported
  • The compound index query condition must contain the left column
  • <> and not in cause the index to be inapplicable