The first lesson in mysql is definitely indexes. The most important thing for work, again, is indexing. When a DBA sees a slow log and throws it at you for optimization, the first thing that comes to mind is indexes. However, many people learn it many times and still don’t really understand it.

First, you need to know how indexes are implemented.

Why does MySQL use B+ trees?

If you have read this article, you probably know Innodb uses B+ tree, natural order, when we query, we only need binary search, find the leaf node. Now that we’ve done that, it makes sense.

The index of common sense

The index classification

By storage structure:

  • Btree index: multi-path balanced query tree, natural order, suitable for range query.
  • Hash index: The hash index has no order relation and is suitable for equivalent query. The range query requires scanning the whole table.
  • Full-index: indicates the full-text index.

By application layer:

  • Common index: single column index.
  • Unique index: The value of the index column must be unique, but empty values are allowed.
  • Composite index: multiple column indexes.

Clustered indexes and non-clustered indexes

Many people are confused by these two words, in fact, it is very simple, one is the primary key index, one is not the primary key index.

The first leaf node holds the full data, and the second leaf node only holds the primary key.

Select *from T where ID=500; select *from T where ID=500;

If the statement is select *from T where k=5, you need to search the k index tree first and obtain the ID of 500, and then search the ID index tree again. This process is called a callback.

So:

  • Use an increment Int as the primary key as much as possible: apending is convenient, and if you insert it halfway, you need to move all the way back, which can lead to unevenly distributed pages and split pages.
  • Use primary keys when you can: reduce back table.

How to reduce the return table?

Overwrite the index, that is, the index can get the value we need.

Select *from T where k between 3 and 5 select *from T where k between 3 and 5

Select ID from T where k between 3 and 5 select ID from T where k between 3 and 5

Joint index

  • Put the index with the highest reuse rate on the left.
  • Put the ones that can screen out more data on the left.
  • Put the little space on the left.

(Filter data from left to right. The earlier you filter out useless data, the better)

Index condition pushdown

During index traversal, you can first judge the fields in the index and directly filter out the records that do not meet the conditions, reducing the number of times to return to the table.

When screening data with index A, judge with index B directly at the same time.

For example, query the surname zhang, age less than 10.

  • Turns out we’re looking for all the Zhangs, checking them out, judging their ages.
  • Now is a direct judgment skip, will be a lot less times back to the table.

String index

  1. Create the full index directly, which may compare space;
  2. Create a prefix index to save space, but increase the number of queries and scans, and overwrite indexes cannot be used.
  3. In reverse order, create a prefix index, used to bypass the string itself prefix is not enough to distinguish the problem;
  4. Creating hash field indexes provides stable query performance, additional storage and computing costs, and, like the third method, does not support range scanning.

When does an index fail?

We know that the index stores the actual value, so when we match it, we match it by value. Then, if an operation is added, the index becomes invalid.

Such as:

  • After Where, the index is processed by functions, such as dates and mathematical operations.
  • The index is a string and an integer written where. (With the type conversion function)
  • If null is encountered, I don’t know whether it is large or small

Others may fail (see the execution plan for yourself)

  • Is not equal to
  • like
  • First range and then equivalence match

How to optimize indexes

  • Join indexes: Because indexes are naturally ordered, it is best to create a join index if you use these keys to sort queries frequently.
  • Try to use a unique self-increasing key.
  • If necessary, create an override index.

Samsung System:

  • The index puts related records together into one star.
  • If the order of the data in the index is the same as the order of the search, it is two stars. (Targeted joint index)
  • If the columns in the index contain all the columns needed in the query, the index is three times larger. (Overwrite index)

conclusion

If something is wrong, you are welcome to correct it.

If you don’t understand anything, you are welcome to point out and I will add chestnuts.

If you feel OK, you can like it and let more people see it.

Related reading:

  • Table design is the final mountain to learn mysql
  • Learn MySQL’s first mountain – index
  • Learn about MySQL’s second mountain — locks and transactions
  • Why does MySQL use B+ trees?