A brief discussion on the difference between Mysql primary key index and non-primary key index

What is an index

The official definition of MySql index: Index is a data structure that helps MySql to efficiently obtain data. The purpose of index is to improve query efficiency, analogous to a dictionary. In fact, the index is also a table that stores the primary key and index fields and points to the records of the entity table. 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 if it is myisam, there is no difference between the primary key index and the normal index, both directly index the data)
2. When you query with where id=x, you only need to scan the primary key index once to get the corresponding data. However, if you query with a normal index, you will first scan the normal index to get the primary key value, and then scan the primary key index again to get the required data. This process is called table back

Which data structure is used for the index?

There are two common MySQL structures: Hash index and B+ Tree index. We use the InnoDB engine, and the default is B+ Tree.

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

The difference between B+ Tree index and Hash index Hash index is suitable for equal value query, but cannot perform range query Hash index cannot use index to complete sorting Hash index does not support the leftmost matching rule of multi-column joint index If there are a large number of duplicate key values, the efficiency of hash index will be very low because of hash collision problem

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

In InnoDB, the leaf node of the index B+ Tree that stores the entire row of data is the primary key index, also known as the clustered index. The leaf nodes of the index B+ Tree that store the primary key value are non-primary key indexes, also known as non-clustered indexes.

Are there any differences between clustered indexes and non-clustered indexes when querying data?

Clustered index queries are faster because the leaf nodes of the primary key index tree are 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. After finding the value of the primary key, you need to query it again through the value of the primary key. This process is called table return.
Will non-primary key indexes be queried multiple times?
A covering index can also be queried only once. A covering index means that the execution of a query statement can be obtained only from the index without having to read it from the data table. It can also be said that index coverage is achieved. When a query statement meets the covering index condition, MySQL only needs to use the index to return the data required for the query, thus avoiding the need to return to the table after finding the index, reducing I/O and improving efficiency. For example, there is a common index idx_key1_key2(key1, key2) in the covering_index_sample table. When we use the SQL statement: select key2 from covering_index_sample where key1 = 'keytest';, we can query through the covering index without returning to the table.

Index Condition Pushdown

MySQL 5.6 introduced the index pushdown optimization, which is enabled 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: In the people table, (zipcode, lastname, firstname) constitute an index

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

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

Query Optimizer

A SQL statement query can have different execution plans. As for which plan is finally selected, it needs to be selected by the optimizer to choose the plan with the lowest execution cost. Before a single-table query statement is actually executed, the MySQL query optimizer will find all possible solutions for executing the statement and find the solution with the lowest cost after comparison. This lowest-cost solution is the so-called execution plan. The optimization process is as follows: 1. Find all possible indexes based on the search conditions 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 plans and find the one with the lowest cost

Question about 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;

Due to historical reasons, this table requires a and b as joint primary keys.
That means, if we create an index on field c alone, it will already include three fields. Why do we need to create two indexes "ca" and "cb"?

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

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

Answer:

(1) The organization order of the clustered index of primary keys a and b is equivalent to order by a,b, that is, sort by a first, then by b, and c is out of order.

–a--|–b--|–c--|–d--
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 index ca is to sort by c first, then by a, and record the primary key at the same time.

–c--|–a--|–primary key part b-- (note, it is not ab here, but only b)
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

The data of the index ca above is exactly the same as that of the index c.

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

–c--|–b--|–Primary key part a-- (same as above)
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.

This is the end of this article about the difference between MySQL primary key index and non-primary key index. For more relevant MySQL primary key index and non-primary key index content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!