directory

    • Back to the table
    • Cover index
    • Left-most prefix rule
    • How to arrange the order of the fields in the index when combining indexes?
    • An index pushdown
    • Rebuild index problem
    • Join primary key index and InnoDB index organization table problem
    • The difference between in and between

Back to the table

The process of going back to the primary key index tree is called back to the table.

Cover index

An overwrite index is a query in which the desired data is already on the leaf of the index tree.

select ID from T where k between 3 and 5
Copy the code



The value of ID is already in the k index tree, so the query results can be provided directly without the need to return to the table.

Because overwriting indexes can reduce the number of tree searches and significantly improve query performance, it is a common performance optimization method to use overwriting indexes.

The second use of overwriting an index is to use it on a federated index and to avoid back to the table. If there is a high-frequency request now, look up the citizen’s name based on his ID number. We can set up a joint index (id number, name). It can be used on this high frequency request, eliminating the need to go back to the table to look up the entire row, reducing statement execution time.

Left-most prefix rule

The union index sorts by the first field, and by the second field if the first field has the same value.



Indexes can be used to speed up retrieval as long as the leftmost prefix is satisfied. The leftmost prefix could beJoint indexThe leftmost N field of, orString indexThe leftmost M characters of.

How to arrange the order of the fields in the index when combining indexes?

First rule: If you can maintain one less index by adjusting the order, it is usually the first order to adopt. If we have a frequent request to find the person’s ID by name, then we should create a joint index: (name,ID)

An index pushdown

Index coverage is when the information you want to look up is already in the secondary index, so there is no need to return to the table. Index push-down is part of your filter conditions conform to the leftmost prefix, so will use index, if the part is not in conformity with the leftmost prefix just have joint index in field, so in the use of the most left prefix of indexing query at the same time, according to these fields do more step filter, reduce the number of index search out article, which decrease the number back to the table. Such as:


mysql> select * from tuser where name like 'a %' and age=10 and ismale=1;
Copy the code
1 needs to return to the table every time 2 push down, filter out the inconsistent, and then back to the table

Rebuild index problem

Suppose we have a table with a primary key column ID, a field K in the table, and an index on k.

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
Copy the code

If you want to rebuild index k:

alter table T drop index k;
alter table T add index(k);
Copy the code

If you want to rebuild the primary key index:

alter table T drop primary key;
alter table T add primary key(id);
Copy the code

What do the above two attempts to rebuild indexes mean? Why rebuild the index? An index may be deleted, or a page split, resulting in a void in the data page. The process of reconstructing an index creates a new index and inserts the data in order, so that the page utilization is maximized. This means that the index is more compact and saves space. Understand that whether a primary key is deleted or created, the entire table is rebuilt. So if you execute these two statements in tandem, the first statement is done for nothing. Recommended use:

alter table T engine=InnoDB
Copy the code

Join primary key index and InnoDB index organization table problem

Here’s a table:


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;
Copy the code

Select * from primary key (a, b, ca, cb); select * from primary key (b, CA, cb); His colleagues told him it was because they had two statements in their business:


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

Are both indexes required for both query modes? Why is that? Order by a, B; order by B; order by C; order by B;

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

The ca index is organized first by C and then by A, while recording the primary key: this is exactly the same as index C.

c a b
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

Select * from cb; select * from cb; select * from cb; select * from cb;

c b a
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

So, the conclusion is ca can get rid of, cb needs to stay.

The difference between in and between

--1.
select * from T where k in(1.2.3.4.5)
--2.
select * from T where k between 1 and 5
Copy the code

例 句 : The numbers inside are unknown, so you have to look at them one by one. 2. A known ascending, range query, only need to locate the first value, the following traversal line.