Shulou(Shulou.com)06/01 Report--

The following together to understand the MySql index-related knowledge, I believe we will certainly benefit from reading, the text is not more refined, I hope MySql index-related knowledge this short content is what you want.

1 What is an index?

A database index is an ordered data structure in a database management system that facilitates rapid querying and updating of data in database tables. Just like the Xinhua dictionary directory we used before, it can help us quickly find a certain word.

II. Classification of indexes

Classification Angle Index Name Data Structure B+ Tree, Hash Index, R-Tree and other storage layers

clustered index, unclustered index

Logical level primary index, ordinary index, composite index, unique index, spatial index, etc. III. Index instance analysis (taking InnoDB as an example)

3.1 Index structure under InnoDB

Under InnoDB, tables are stored in the form of indexes according to the primary key order. This data storage method is also called clustered index."Clustering" means that data rows and adjacent key values are stored together compactly, that is, data rows are actually stored in leaf pages of the index. We create a table to actually illustrate the index structure under InnoDB. The table creation statement is as follows:

create table person(id int primary key, age int not nullindex (age)engine=InnoDB;

Then we insert five pieces of data as (1,15),(2,17),(6,20),(10,18),(19,21), and the tree structure of the index is as follows:

The above figure shows two parts. The first figure shows the contents of the clustered index (primary key index). You can see that the data is sorted according to the size of Id, and the corresponding index will contain the entire row of data of this index.

The second figure shows the index structure diagram with age as index, that is, non-clustered index (non-primary key index), you can see that the index is sorted by age, but different from the primary key index, the age index corresponds to Id, so we can know that the content of the non-primary key index record is the primary key index value.

Some students may have questions here, if I did not specify the primary key when I built the table, what is the index structure? In fact, in InnoDB, if there is no primary key defined, then he will choose a unique non-empty index instead. If there is no such index, then he implicitly defines a primary key as the cluster index. So whether you set the primary key or not, InnoDB will help you index data in the form shown above. Next, we analyze the flow of index queries.

3.2 index query analysis

Suppose we execute a query statement select * from person where ID = 6, because the primary key ID query is directly used, so we will use the primary key index, because the primary key index is directly associated with all the data in the whole row, so the engine can query the result as long as it is executed once.

If the sql statement executed is a non-primary key index

select * from person where age = 18

The process of retrieving the primary key index from the ordinary index and then querying the data is called returning to the table. Because returning to the table requires one more query, which is why primary key indexes are faster than ordinary indexes, we should try to use primary key queries. The above statement will go through the ordinary index of age. The index first searches for index records equal to 18 according to age, finds records with ID=10, and then searches once in the primary key index, and then takes out the data to be queried.

3.3 coverage indices

We usually create an index based on the query where conditions, but this is only our usual practice, we can know according to the above analysis, if you want to query high efficiency, first, use the primary key index, second, avoid back to the table, that is, as much as possible in the index to get the desired data. If an index contains fields that need to be queried, it is called an "overlay index."

So how do you create an overlay index? The answer is to achieve through the union index, through the union index field to cover the field to be queried, so as to achieve the effect of index coverage.

Let's transform the above table creation statement to analyze how to implement an overlay index.

CREATE TABLE `person` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `sex` varchar(1) DEFAULT NULL,

Above, I created a joint index of name and age. The index structure diagram is shown as follows:

We can know from the diagram that the union index is related to the order in which the index fields are created. The above example is to sort by name first, then the same name and then sort by age. So how do we achieve the effect of overwriting the index after we create the table? I believe some students already know how to write sql can achieve the effect of coverage index, sql is as follows:

select name,age from person where name = "Barry"

Because we need to query the field name and age, can be directly queried in the index, so do not need to find the primary key ID, and then back to the table.

See here, there must be students will say, since this is the case, I need to query all the field combinations are not built on the joint index line? The answer is no. Because indexes also consume space, and maintaining indexes also costs money, which I will mention in the pros and cons below. So is there another way to achieve the effect of not returning to the table as much as possible? Here we introduce the leftmost prefix principle of MySql.

What is the leftmost prefix principle? That is, in the matching of indexes, the leftmost N fields of indexes can also be the leftmost N characters of string indexes. For example, in the above figure, to query the name starting with A, the query statement is

select name from person where name like 'A%'

At this time, the leftmost prefix rule can be satisfied to use the index query, where it will rely on the index query to the first name with the initial letter A, and then traverse backwards until the condition is not satisfied.

So what do the leftmost N fields mean? This means that the index (name,age) can be used as a separate index directly using name, only part of the fields of the joint index can be used, but the order must be consistent, such as index (a,b,c), if you want to use the leftmost prefix rule, you can use index a,ab.

We can also use this rule to maintain one or more indexes less, for example, we need a,ab,abc query, then only need (a,b,c) joint index to meet the requirements.

3.4 index push down

MySql 5.6 introduced a new feature called index condition pushdown, also known as index pushdown. So what's the index push down? In fact, the name "index condition push" can indicate that this feature can make conditional judgment on the fields in the index, and then filter records that do not meet the conditions to reduce the number of return tables.

For example, the data in the above figure shall prevail, sql is as follows:

select * from person where name like 'A%' and age =19;

If there is no index push down, all records with names beginning with A will be queried according to the index, then the ID will be queried, and then the corresponding ID record will be queried back to the table, and finally age=19 will be determined, and the statement meeting the condition will be returned. Because there are two records that meet the beginning of A, in this case, the table will be returned twice.

In the case of index push down, InnoDB will directly determine whether age=19 meets the condition inside the index and filter out records that do not meet the condition, so only one record is returned, that is, only one return is required. This improves performance.

3.5 Advantages and disadvantages of indexing

Having said so much about indexing, let's talk about the pros and cons of indexing.

Advantages:

Reduce the amount of data that Cloud Virtual Machine needs to scan Indexing helps Cloud Virtual Machine avoid sorting and temporary table indexing turns random IO into sequential IO

disadvantages

Indexing will take up extra storage space. Index maintenance requires a certain cost. After inserting data, it is necessary to ensure that the original index is in order, so it will also affect certain database performance.

After reading MySql index related knowledge this article, many readers will definitely want to know more related content, if you need to get more industry information, you can pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.