Mysql video tutorial column introduces the real index.

You can also see from the figure that the difference between B+ tree and B tree is:

1. All keywords are stored in leaf nodes, non-leaf nodes do not store real data, so leaf nodes can be quickly located.
2. A chain pointer is added to all leaf nodes, which means that all values ​​​​are stored in order, and the distance from each leaf page to the root is the same, which is very suitable for searching range data.

** Therefore, B+Tree can use indexes for <, , >=, BETWEEN, IN, and LIKE that do not start with wildcards. **

Advantages of B+ tree:

The number of comparisons is balanced, reducing the number of I/O, improving the search speed, and the search is more stable.

• The disk read and write cost of B+ tree is lower
• The query efficiency of B+ tree is more stable

   What you need to know is that you Every time you create a table, the system will automatically create an ID-based clustered index (the above B+ tree) for you to store all the data; every time you add an index, the database will create an additional index (the above B+ tree) for you, index selection The number of fields in is the number of data indexes stored by each node. Note that the index does not store all data.

4. Why does MySQL index choose B+ tree instead of B tree?

1. B+ tree is more suitable for external storage (generally refers to disk storage), because internal nodes (non-leaf nodes) do not store data, so a node can store more internal nodes, and each node can index a wider range Larger is more precise. That is to say, the amount of information in a single disk I/O using the B+ tree is larger than that of the B tree, and the I/O efficiency is higher.
2. Mysql is a relational database, and it often accesses an index column according to the range. The leaf nodes of the B+ tree establish chain pointers in order, which strengthens the accessibility of the range, so the B+ tree pairs the index column The interval range query is very friendly. However, the key and data of each node of the B-tree are together, and range search cannot be performed.

5. Programmers, index knowledge points you should know

1. Back to table query

For example, you created name, age index name_age_index, and used it when querying data

select * from table where name ='  Chen Haha' and age = 26;
 1 

  Since there are only name and age in the additional index, after hitting the index, the database must go back to the clustered index to find other data. This is the return table, which is also the one you memorized: use less select * s reason.

2. Index coverage

It will be better understood when combined with the return table, such as the above name_age_index index, there are queries

select name, age from table where name ='#39;Chen Haha'' and age = 26;
 1 

  At this time, the selected field name and age can be obtained in the index name_age_index, so there is no need to return the table, satisfy the index coverage, and directly return the data in the index, which is highly efficient. It is the preferred optimization method for DBA students to optimize.

3. The leftmost prefix principle

  The node storage index order of the B+ tree is stored from left to right, and it is natural to match when matching Satisfy matching from left to right; usually when we build a joint index, that is, to build an index on multiple fields, I believe that students who have built an index will find that both Oracle and MySQL will let us choose the order of the index, for example, we Want to build a joint index on the three fields of a, b, and c. We can choose the priority we want, a, b, c, or b, a, c or c, a, b, etc. order. Why does the database let us choose the order of the fields? Isn’t it a joint index of three fields? This leads to the principle of the leftmost prefix of the database index.

  In our development, we often encounter the problem that a joint index has been built for this field, but the index will not be used when SQL queries this field. For example, the index abc_index: (a, b, c) is a joint index of the three fields a, b, and c. The following SQL cannot hit the index abc_index when executed;

select * from table where c = '1';

 select * from table where b = '1' and c = '2';
 123 

The following three situations will go to the index:

select * from table where a = '1'  ;

 select * from table where a = '1' and b = '2';

 select * from table where a = '1' and b = '2' and c='3';
 12345 

From the above two examples, do you have any clues?

  Yes, the index abc_index:(a,b,c) will only be used in three types of queries (a), (a,b), (a,b,c). In fact, what is said here is a little ambiguous. In fact, (a,c) will also go, but only the a field index will be used, and the c field will not be used.

  In addition, there is another special case, the following type will only have a and b go to the index, and c will not go.

select * from table where a = '1' and b > '2' and c=&#39  ;3';
 1 

  Like the above type of sql statement, after a and b go through the index, c is out of order, so c cannot go through the index, the optimizer will think that it is not as fast as the full table scan of the c field.

**Leftmost prefix: As the name suggests, it is leftmost first. In the above example, we created a_b_c multi-column index, which is equivalent to creating (a) single-column index, (a,b) Composite index and (a,b,c) composite index. **

   Therefore, when creating a multi-column index, according to business needs, the most frequently used column in the where clause should be placed on the far left.

4. Index pushdown optimization

Or index name_age_index, with the following sql

select * from table where name like '陈%' and age > 26;
 1 

There are two execution possibilities for this statement:

• Hit the name_age_index joint index, query all the data whose name starts with “Chen”, and then return to the table to query all the satisfied data OK.
• Hit the name_age_index joint index, query all the data whose name starts with “Chen”, and then screen out the index of age>20 by the way, and then return to the table to query the entire row of data.

Obviously, in the second way, the number of rows returned to the table query is less, and the number of I/Os will also be reduced. This is the index pushdown. So not all likes will hit the index.

6. Precautions when using indexes

1. Indexes will not contain columns with null values

  As long as the column contains a null value, it will not be included in the index. As long as there is a column in the composite index containing a null value, then this column is invalid for the composite index. Therefore, we recommend not to let the default value of the field be null when designing the database.

2. Use a short index

   to index the string, and if possible, specify a prefix length. For example, if you have a char(255) column, don’t index the entire column if most values ​​are unique within the first 10 or 20 characters. Short indexes can not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

  The query only uses one index, so if the index has been used in the where clause, then the order by The column will not use the index. Therefore, if the default sorting of the database can meet the requirements, do not use the sorting operation; try not to include the sorting of multiple columns, if necessary, it is best to create a composite index for these columns.

4. Like statement operation

  Generally, it is not recommended to use like operation. If it must be used, how to use it is also a problem. like “%Chen%” will not use the index and like “Chen%” can use the index.

5. Do not perform operations on the column

This will cause the index to fail and perform a full table scan, for example

SELECT * FROM table_name WHERE YEAR(column_name)<2017;
 1 

6. Do not use not in and operations

This is not a supported range query condition, and no index will be used.

My experience

   Once, I thought I knew MySQL very well.

  When I first joined the company, I was still a child. I remember that the first requirement was to make a statistical interface to query the website visits at intervals of 5 minutes for nearly two hours. JSONArray returned a total of 24 At that time, I wrote an interface to loop twenty-four times, and sent 24 SQL to check (cover my face). Because of that interface, I was ridiculed by the technical manager~~ said that he wrote more SQL than I ate. . Although we Shandong people basically don’t eat rice, I am still ashamed. .
Then the manager calls a dateTime function to query and process in groups, and it’s ok. The efficiency is dozens of times that of mine. Since then, I have set a goal to learn MySQL in depth, in case I have a chance to go back later?

  Boys, MySQL has a long way to go, and its repair is far away. Never be too high-minded, let’s work together, I hope this article can help you.

The above is crying.. I think I understand the details of MySQL index, please pay attention to other related articles on 1024programmer.com for more!