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

This article mainly explains "what is the difference between primary key index and non-primary key index in Mysql database". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what's the difference between primary key indexes and non-primary key indexes in Mysql databases?"

Catalogue

What is an index?

The difference between primary key index and normal index

Which data structure is used in the index?

InnoDB uses the index model of B+ Tree, so why use B+ tree? What are the advantages and disadvantages of this compared to Hash indexes?

What can be stored in the leaf nodes of B + Tree?

Is there a difference between clustered index and non-clustered index when querying data?

Index Condition Pushdown (index push-down)

Query optimizer

Questions about the index

What is an index?

Definition of MySql official index: index (Index) is a data structure that helps MySql to obtain data efficiently. The purpose of index is to improve query efficiency and analogy dictionary; in fact, an index is also a table, which stores the primary key and index fields and points to the record of the entity table, and the index column also takes up space.

The difference between primary key index and normal index

1. The primary key index indexes the data, while the normal index indexes the primary key ID value (this is in innodb, but in the case of myisam, there is no difference between the primary key index and the normal index and the data is directly indexed)

two。 When you query using where id=x, you only need to scan the primary key index once and get the corresponding data, but if it is a normal index of the query, you will first scan the ordinary index to get the primary key value, and then scan the primary key index to get the required data. This process is called returning to the table.

Which data structure is used in the index?

The common MySQL has two main structures: Hash index and B + Tree index. We use the InnoDB engine, and the default is B+ tree.

InnoDB uses the index model of B+ Tree, so why use B+ tree? What are the advantages and disadvantages of this compared to Hash indexes?

The difference between B + Tree index and Hash index hash index is suitable for equivalent query, but it is not possible to query the range. Hash index cannot be sorted by index. The leftmost matching rule of multi-column joint index is not supported by hash index. If there are a large number of duplicate keys worth it, the efficiency of hash index will be very low, because of the hash collision problem.

What can be stored in the leaf nodes of B + Tree?

In InnoDB, the leaf node of index B + Tree stores the entire row of data is the primary key index, also known as clustered index. The leaf node of index B + Tree stores the non-primary key index, which is also called non-clustered index.

Is there a difference between clustered index and non-clustered index when querying data?

Clustered index queries will be faster

Because the leaf node of the primary key index tree is directly the entire row of data we want to query. The leaf node of the non-primary key index is the value of the primary key, and after finding the value of the primary key, we need to query again through the value of the primary key, a process called back to the table.

Must non-primary key indexes be queried multiple times?

An overlay index can also be queried only once. An overlay index (covering index) means that the execution of a query statement can be obtained only from the index and does not have to be read from the data table. It can also be called the implementation of index coverage. When a query statement meets the condition of covering the index, MySQL only needs to return the data needed by the query through the index, which avoids the operation of returning the table after finding the index, reduces the Ibind O and improves the efficiency. For example, there is a normal index idx_key1_key2 (key1,key2) in the table covering_index_sample. When we use the SQL statement: select key2 from covering_index_sample where key1 = 'keytest';, we can query by overriding the index without having to return to the table.

Index Condition Pushdown (index push-down)

MySQL 5.6introduces index push-down optimization, which is on by default and can be turned off using SET optimizer_switch = 'index_condition_pushdown=off';. The examples and explanations given in the official documentation are as follows: the people table (zipcode,lastname,firstname) forms an index.

WHERE zipcode='95054' AND lastname LIKE'% etrunia%' AND address LIKE'% Main Street%'

If the index push-down technology is not used, MySQL will query the corresponding data from the storage engine through zipcode='95054' and return it to the MySQL server, and then the MySQL server will determine whether the data meets the conditions based on lastname LIKE'% etrunia%' and address LIKE'% Main Street%'. If index push-down technology is used, MYSQL will first return indexes that conform to zipcode='95054', then filter out eligible indexes based on lastname LIKE'% etrunia%' and then return them to MySQL server, and then MySQL server will determine whether the data meets the conditions based on address LIKE'% Main Street%', so that the number of indexes returned to MySQL server will be reduced. With index push-down optimization, you can reduce the number of returns to the table when there are like conditional queries.

Query optimizer

The query of a SQL statement can have different execution schemes. As to which scheme to choose in the end, it needs to be selected by the optimizer to choose the scheme with the lowest execution cost. Before a single-table query statement is actually executed, MySQL's query optimizer finds all possible scenarios for executing the statement, and then finds the one with the lowest cost. The lowest-cost solution is the so-called implementation plan. The optimization process is roughly as follows: 1. According to the search conditions, find out all possible indexes 2, calculate the cost of full table scan 3, calculate the cost of executing queries using different indexes 4, compare the costs of various execution schemes, and find the one with the lowest cost.

Questions about the index

CREATE TABLE `geek` (`a` int (11) NOT NULL, `b` int (11) NOT NULL, `c` int (11) NOT NULL, `d` int (11) NOT NULL, PRIMARY KEY (`a`, `b`), KEY `c` (`c`), KEY `ca` (`c`, `a`), KEY `cb` (`c`, `b`) ENGINE=InnoDB

For historical reasons, this table requires an and b to be joint primary keys.

That means that creating an index on field c alone already contains three fields, so why create the two indexes "ca" and "cb"?

Select * from geek where cations N order by a limit 1 * select * from geek where cations N order by b limit 1

Question: is this colleague's explanation correct? Which indexes are unnecessary and can be deleted?

Answer:

The main results are as follows: (1) the order of clustering index of primary key aformab is equivalent to that of order by aformab, that is, it is sorted first by a, then by b, and c is out of order.

-a muri-|-bmurf-|-cmure-|-dmurf-

1 2 3 d

1 3 2 d

1 4 3 d

2 1 3 d

2 2 2 d

2 3 4 d

(2) the organization of the index ca is to sort by c, then a, and record the primary key at the same time.

-Cmuri-|-a muri-|-primary key part bMel-(note that this is not ab, but only b)

2 1 3

2 2 2

3 1 2

3 1 4

3 2 1

4 2 3

The above index ca is identical to the data of index c.

(3) the organization of index cb is to sort by c and b, and record the primary key at the same time

-Cmure-|-bMui-|-Primary key part aMui-(ibid.)

2 2 2

2 3 1

3 1 2

3 2 1

3 4 1

4 3 2

So the conclusion is that ca can be removed and cb can be retained.

At this point, I believe that everyone on the "Mysql database primary key index and non-primary key index what is the difference" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.