I went to the interview last month, but failed. Interviewer: Tell me about indexes. I index can be divided into XXX and XXX two types, XXX index is characterized by XXX, and then the interviewer and stare at each other. The interviewer must be thinking: You want to interview for a senior position at this level. When you fail in an interview, you need to make a review

Writing in the front

This article will introduce:

  • MySQL > select * from ‘MySQL’ where ‘index’ is
  • Index creation
  • Explain command

MySQL > select * from ‘MySQL’ where ‘index’ is

MySQL index types can be divided into different classes from different perspectives

Physical Storage Angle

  • Clustered index => Table records are arranged in the same order as indexes
  • Non-clustered index => the order of the table records is not the same as the order of the index, which is simply a non-primary key index

The difference between a clustered index and a non-clustered index is that a clustered index stores data in a table on a leaf node, whereas a non-clustered index stores primary keys and index columns on a leaf node

Data structure perspective

  • B-tree indexes
  • A Hash index
  • The full text indexing

The logical point of view

  • Primary key index => Cannot be repeated and cannot be NULL
  • A plain index is also called a single-column index
  • Multi-column indexes are also called composite indexes
  • Unique index or non-unique index. Unique index only needs to ensure that it does not duplicate. Null is allowed

How to create an index

Index creation needs to be considered from two aspects: index hit, index efficiency, and index storage space

The index hit a

An index is created to improve query efficiency. If an index is created but the index is not queried, it wastes storage space and gains more than gains. The following principles must be met to match an index:

  • The left-most prefix matching principle, mysql will keep matching to the right until it encounters a range query (>, <, between, like) and then stops matching, e.ga = 1 and b = 2 and c > 3 and d = 4SQL > create index (a,b,c,d)b = 2 and c = 3 and d = 4, then the index (a,b,c,d) will be invalid
  • Avoid introducing functions and calculations in WHERE statements such as SQL:select count(*) from t where unix_timestamp(sync_dt) >= 1539101010Unix_timestamp is a built-in function of mysql, which will cause index invalidation. In addition to using a function to cause index invalidation, common calculations can also cause index invalidation, such as SQL:select * from t where a = a + 1
  • Avoid starting with a wildcard character in like. If index A is created, but SQL isselect * from t where a = '%x', the index will be invalidated
  • Avoid using! = and <>, if the index a is created, but SQL isselect * from t where a ! = 'x', the index will be invalidated

The index efficiency

Use the count(DISTINCT Col)/count(*) formula to calculate the distinction, indicating the ratio of distinct columns. The larger the ratio is, the fewer records are scanned. If the index is too small, the optimizer will consider that the efficiency of a walk index query is similar to that of a full table scan and invalidate the index

Index storage space

  • Expand indexes as much as possible, do not create new ones. If you want to add the index (a,b) to a table that already has the index (A, B), you only need to change the original index to (a,b), because (a,b) contains two indexes, a and (a,b)
  • The primary key index field should be as small as possible, because in InnoDB storage engine non-primary key index leaf nodes store primary key index values. The smaller the primary key length is, the smaller the non-primary key index leaf nodes are, and the less storage space non-primary key indexes occupy

Explain command

After creating the index, try to use Explain to see if the query index hit, the number of rows scanned, etc., in order to prevent slow queries online