preface

No preamble, no bullshit, and get right to the interview

The interview began

Interviewer: What is an index

  • Index is a kind of data structure, which improves retrieval efficiency. B+ trees, hashes

Interviewer: Tell me what you know about B+ trees

  • B+ tree is a balanced multi-fork tree. Compared with B tree, the data of B+ tree only has leaf nodes. Leaf nodes form linked lists and therefore support range queries. The query efficiency of B+ trees is O(logH), where H is the height of B+ numbers.

Interviewer: Why InnoDB uses B+ trees as index structures instead of B trees?

  • First, a B+ tree is compared to a B tree. A B tree has fewer memory pages to store because non-leaf nodes also store data. With the same amount of data, the height of THE B tree is higher than that of the B+ tree, so more I/OS are required, making the overall efficiency of the B tree slower than that of the B+ tree.
  • B trees do not support range queries because the leaves of B trees do not form linked lists.
  • B+ tree data are in the leaf node, query performance is more stable.

Interviewer: What are the categories in the index

  • Primary key index, non-primary key index (normal index, unique index, union index)

Interviewer: What is the difference between a primary key index and a non-primary key index?

  • In InnoDB, primary key indexes are also called clustered indexes. Leaf nodes store entire rows of data
  • In InnoDB, non-primary key indexes are also called secondary indexes. The leaf node contents are the primary key values

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
Copy the code

Interviewer: Do you understand the form

  • Select * from T where ID=300; select * from T where ID=300;
  • If the statement is select * from T where k=3, that is, the common index query mode, search the K index tree first, obtain the ID value 300, and then search the ID index tree. This process is called table back.
  • That is, a query based on a non-primary key index requires one more index tree to be scanned. Therefore, we should try to use primary key queries in our applications.

Interviewer: The principle of hitting an index?

  • Indexes can be used as long as either the overwrite index rule or the left-most prefix rule is met.
  • See MySQL Index Usage rules for details

Interviewer: What is a covered index?

  • Overwriting an index means that the execution of a query can be retrieved only from the index, not from the table. You can also call it index coverage.

Interviewer: Last question, how to solve the slow query?

  • Locate the fault: Query the slow log to determine the SPECIFIC SQL statement
  • Analysis of the problem: then, SQL slow reasons generally have two kinds, one is no index, the second is to use the wrong index
  • Solve the problem: If the index is not used, we can use Explain to analyze the SQL statement and determine whether the index is used. If an index is used, check whether it is the right one, and if it is the wrong one, use force index() to force an index to be used. If the interviewer continues to ask you, why did you use the wrong index? To put it simply, InnoDB chooses which index to use based on a value, which is calculated from a sample value, and since it is a sample, sampling is more random, so errors are not impossible.

Interviewer: After the interview, congratulations on entering the next round

conclusion

In fact, there are many more knowledge points about indexes, which are not expanded here

  • What fields are selected for indexing
  • An index pushdown
  • Explain the meaning of the fields and how to tune according to the fields

omg

Thank you very much for reading here, if you think the article is good, please follow it, please like it and share it (very, very useful to me). If you feel the article needs to be improved, I am looking forward to your suggestions, please leave a comment. If there’s anything you’d like to see, I look forward to hearing from you. Your support and support is the biggest motivation for my creation!

The resources

  • High Performance MySQL
  • MYSQL tutorial 45