MySQL Interview Cheat sheet

I am fat brother, an unprofessional interviewer!

I am embarrassed, a rookie who is actively looking for a job!

Embarrassed said: the most afraid of the interview is that the interviewer asked the knowledge point is too general, they can not quickly locate the key question point!!

This is the main interview question

What does the interviewer ask about the index maintenance process? Pages divided? Pages?Copy the code

Q: What is the process of B+ tree index search?Copy the code

What is the answer sheet?Copy the code

What is index coverage? Usage scenarios?Copy the code

Under what circumstances will the index fail?Copy the code

Under what circumstances might an interviewer face index failure?Copy the code

What are the scenarios of index failure and index failure?Copy the code

Under what circumstances does the interviewer ask for an index to be created?Copy the code

The left-most prefix principle for the interviewer's joint index?Copy the code

Is the index of the interviewer's test points pushed down?Copy the code

What does the interviewer ask about the index maintenance process? Pages divided? Pages?

B+ tree in order to maintain index order, in the insertion and deletion of the time need to do the necessary maintenance, if necessary may involve page splitting, page merge process!

First, assume that each leaf node (data page or disk block) can store only three indexes and data records, as shown

Case 1: Add row records, ID=3. At this time [data page 1] is not full, only need to add row records with ID=3 after data2, the overall structure of B+ tree does not need to be adjusted

Case 2: Add row record, ID=8, now [data page 2] is full, at this time, need to apply for a new data page, and then move some data to the past. This process is called page splitting.

The page splitting process consumes performance and space utilization is reduced

There is split and merge, when two adjacent pages due to the deletion of data, the utilization is very low, the data pages will be merged. The process of merging can be regarded as the reverse of the process of splitting.

When two adjacent pages have low utilization due to data deletion, the data pages are merged. The process of merging can be regarded as the reverse of the process of splitting.

[Data page 2] deleted the row records with ID=7 and ID=8. At this time, the utilization rate of [data page 2] [data page 3] is very low, and the page merge will be carried out.

Q: What is the process of B+ tree index search?

Prepare a user table with id as the primary key and age as the normal index

CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Select * from user where age=22

Assume the record to be queried

Id = 5, name = "* *", the age = 22Copy the code

MySQL maintains a B+Tree for each index.

Primary key index non-leaf nodes maintain index keys, while leaf nodes store row data.

Non-primary key indexes are also called secondary indexes. Non-leaf nodes store primary keys.

B+ tree index search process

If age=22, select idx_AGE index, load idx_age index tree, find the record whose age=22, and obtain id=5

Select * from primary key; select * from primary key where id=22

What is the answer sheet?

Idx_age The process of searching for the primary key in the secondary index tree is called back to the table.

Not all non-primary key index searches require a back-table search, which is called index overwriting below.

What is index coverage? Usage scenarios?

In the example mentioned above, the data required for the query result is only available on the primary key index, so it has to be returned to the table.

If the data column in the query, directly from the index column can get the desired result, there is no need to go back to the table to look up, also known as index overwrite!

Advantages of index coverage

Secondary queries on Innodb primary key indexes can be avoided

System calls to MyISAM tables can be avoided

The cache can be optimized to reduce disk I/O operations

Modify the above chestnut to meet the index coverage condition?

select id, age from user where age=22
Copy the code

The queried information, id, and age can be obtained directly from the IDX_AGE index tree without searching back to the table.

Because overwriting indexes can reduce the number of tree searches and significantly improve query performance, it is a common performance optimization method to use overwriting indexes.

Index is a double-edged sword, while providing quick sort search, index field maintenance also has to pay the corresponding price.

Therefore, there are trade-offs when creating redundant indexes to support overwriting indexes

Is the index of the interviewer’s test site invalid?

Is the created index valid or does the SQL statement use the index query?

One of the most common query scenarios is to create an idx_name index

select * from t_user where user_name like '%mayun100%';
Copy the code

Is this query indexed?

select * from t_user where user_name like 'mayun100%';
Copy the code

Is this query indexed?

What are the situations in which an interviewer might face index failure?

Like wildcard, open on the left, full table scan

Or condition filtering, which may cause index invalidation

Mysql > select * from ‘where’

Where operations on index columns (such as +, -, *, /) are invalid

Type inconsistencies, implicit type conversions, resulting in index invalidation

The index column in the WHERE statement uses a negative query, which may cause index failure. Negative queries include: NOT,! =, <>! <,! >, NOT IN, NOT LIKE, etc. Among them!
is SQLServer syntax.

The index field can be null. Using IS NULL or IS not NULL may invalidate the index

Index invalidation due to an implicit character encoding conversion

In a federated index, where columns violate the leftmost matching rule, and the index will be invalidated

MySQL optimizer’s ultimate choice, do not walk the index

What are the scenarios of index failure and index failure?

What are the scenarios in which an index goes or an index fails?

OR joins the same field with the same index

explain select * from t_user where user_name = 'mayun10' or user_name = 'mayun1000'
Copy the code

OR joins two different fields, not the index

Add index to address column

alter table t_user add index idx_address(address); Explain select * from t_user where user_name = 'mayun10' or address = 'hangzhou '; explain select * from t_user where user_name = 'mayun10' or address =' Hangzhou ';Copy the code

OR joins two different fields. If both fields have indexes, go to the index

(Insert next issue: MySQL Interview Cheat sheet)

Please pay attention to: Jiong yao fat matter

Interview cheat sheet series.

Under what circumstances does the interviewer ask for an index to be created?

1. Unique indexes are automatically created for primary keys

2. Frequently queried fields

3.JOIN Associative query to create indexes for columns that are used as foreign keys

4. The selection of single-key/composite indexes tends to create composite indexes in high concurrency, and the creation follows the left-most prefix matching principle

Select * from ORDER BY; select * from ORDER BY

6.GROUP BY Indicates the field to be grouped or the statistical field in the query

The left – most prefix principle for the interviewer’s joint index

MySQL establishes multiple column indexes (synth indexes) with the left-most prefix principle

When MySQL creates a joint index (a, B,c), what are the MySQL tree building rules?

We know that MySQL maintains a B+Tree for each index. Non-leaf nodes store index keys and leaf nodes store row data.

MySQL > create index Tree (a,b,c); MySQL > create index Tree (a,b, C); MySQL > create index Tree (a, B, C);

Match index case one

** If (a,b,c) ** is searching for values (‘ zhang SAN ‘, 21, 100), the order of matching is a,b, and C.

B+Tree will compare A first to determine the next direction to search. If A is the same, B and C will be compared in turn to obtain the retrieved data.

Matching index case two

** If (a,c) ** is searching for a value (‘ zhang SAN ‘, 100), the order of matching is a,b, and C.

Select * from B+Tree where a = 100; select * from B+Tree where a = 100; select * from B+Tree where a = 100;

Match index case three

** Suppose (b,c) ** index to search for the value (‘ zhang SAN ‘, 21), when retrieving data, there is no match order

B+Tree does not know which node to search next, because a is the first comparison factor when the search Tree is established, and it is necessary to search according to A to know where to search next. Index invalid!

Index entries are sorted by the fields that appear in the index definition. The leftmost prefix can be the leftmost N fields of a joint index or the leftmost M characters of a string index.

Is the index of the interviewer’s test points pushed down?

Index push down, that is, reduce the secondary index back table search times!!

Generally speaking, it reduces the number of primary key index tree queries and reduces disk I/O

Create the federated index idx_age_weight

select * from user where age = 11 and weight = 98
Copy the code

The search process before 5.6 is

Select * from idx_age_weight; select * from idx_age_weight; select * from idx_age_weight

In the following figure, three back table searches are required

The search process after 5.6 is to match all age = 11 indexes in the idx_AGe_weight index tree, judge the weight field, filter out the weight = 100 records, and then search back to the table.

As shown below, only two back-table searches are required

Read the original article: