Mysql-overwrite index, mysql-Index

Mysql-overwrite index, mysql-Index
What is overwriting index?

• Interpretation 1: select data columns can be obtained only from the index without reading from the data table. In other words, the query column must be overwritten by the index used.
• Explanation 2: indexing is an efficient way to find rows. When you can retrieve the index, you can read the desired data, and you do not need to read the row from the data table. If an index contains (or overwrites) data that meets the fields and conditions in the query statement, it is called overwriting index.
• Explanation 3: a form of non-clustered Composite Index, it includes all the columns used by the Select, Join, and Where clauses in the query (that is, the index creation field exactly overwrites the data involved in the query statement [select clause] and query condition [Where clause], that is, the index contains all the data being queried ).

Not all types of indexes can be covered indexes. To overwrite an index, you must store the index column, but the hash index, spatial index, and full-text index do not store the index column value. Therefore, MySQL can only use the B-Tree index to overwrite the index.

When an index-covered query (also called an index-covered query) is initiated, the "Using index" information is displayed in the Extra column of the EXPLAIN statement.

　　

Several optimization scenarios: 1. Query Optimization without the WHERE condition:

In the execution plan, if type is ALL, full table scan is performed.

How can we improve it? The optimization method is simple, that is, to create an index for this query column. As follows,

ALERT TABLE t1 ADD KEY(staff_id);

 

• Let's look at the execution plan.

explain select sql_no_cache count(staff_id) from t1\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: indexpossible_keys: NULL          key: staff_id      key_len: 1          ref: NULL         rows: 1023849
　　　　　 Extra: Using index
1 row in set (0.00 sec)

 

Possible_key: NULL, indicating that the query optimizer cannot retrieve data through the index without the WHERE condition. Here, another advantage of the index is used, that is, obtaining data from the index, reduces the number of data blocks read. For queries without the where condition, indexes can be used to overwrite the query. However, the prerequisite is that the number of fields returned by the query is small enough, let alone select. After all, creating an index with a long key length is never a good thing.

• Query consumption

　　

In terms of time, the sec is reduced by 0.13.

2. Secondary search optimization

This query is as follows:

select sql_no_cache rental_date from t1 where inventory_id<80000;……| 2005-08-23 15:08:00 || 2005-08-23 15:09:17 || 2005-08-23 15:10:42 || 2005-08-23 15:15:02 || 2005-08-23 15:15:19 || 2005-08-23 15:16:32 |+---------------------+79999 rows in set (0.13 sec)

 

Execution Plan:

explain select sql_no_cache rental_date from t1 where inventory_id<80000\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: rangepossible_keys: inventory_id          key: inventory_id      key_len: 3          ref: NULL         rows: 153734        Extra: Using index condition1 row in set (0.00 sec)

 

Extra: Using index condition indicates that the index is used for secondary retrieval, that is, 79999 bookmarks are used for back-to-table query. It can be imagined that there will still be a certain amount of performance consumption

Try to create a joint index for this SQL statement as follows:

alter table t1 add key(inventory_id,rental_date);

 

Execution Plan:

explain select sql_no_cache rental_date from t1 where inventory_id<80000\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: rangepossible_keys: inventory_id,inventory_id_2          key: inventory_id_2      key_len: 3          ref: NULL         rows: 162884        Extra: Using index1 row in set (0.00 sec)

 

Extra: Using index indicates that no tag query is performed and index coverage is achieved.

3. Paging Query Optimization

The following query scenario

select tid,return_date from t1 order by inventory_id limit 50000,10;+-------+---------------------+| tid   | return_date         |+-------+---------------------+| 50001 | 2005-06-17 23:04:36 || 50002 | 2005-06-23 03:16:12 || 50003 | 2005-06-20 22:41:03 || 50004 | 2005-06-23 04:39:28 || 50005 | 2005-06-24 04:41:20 || 50006 | 2005-06-22 22:54:10 || 50007 | 2005-06-18 07:21:51 || 50008 | 2005-06-25 21:51:16 || 50009 | 2005-06-21 03:44:32 || 50010 | 2005-06-19 00:00:34 |+-------+---------------------+10 rows in set (0.75 sec)

 

Before optimization, we can see that its execution plan is so bad.

　　

explain select tid,return_date from t1 order by inventory_id limit 50000,10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1023675        1 row in set (0.00 sec)

It is a full table scan. With the addition of sorting, the performance consumption is not low.

How to optimize the coverage index?

We create an index that contains the sorting column and the returned column. Because tid is a primary key field, the following composite index contains the field value of tid.

alter table t1 add index liu(inventory_id,return_date);

 

So what is the effect?

select tid,return_date from t1 order by inventory_id limit 50000,10;+-------+---------------------+| tid   | return_date         |+-------+---------------------+| 50001 | 2005-06-17 23:04:36 || 50002 | 2005-06-23 03:16:12 || 50003 | 2005-06-20 22:41:03 || 50004 | 2005-06-23 04:39:28 || 50005 | 2005-06-24 04:41:20 || 50006 | 2005-06-22 22:54:10 || 50007 | 2005-06-18 07:21:51 || 50008 | 2005-06-25 21:51:16 || 50009 | 2005-06-21 03:44:32 || 50010 | 2005-06-19 00:00:34 |+-------+---------------------+10 rows in set (0.03 sec)

 

We can find that after adding a composite index, the speed is increased by 0.7 s! Let's take a look at the improved execution plan.

explain select tid,return_date from t1 order by inventory_id limit 50000,10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: indexpossible_keys: NULL          key: liu      key_len: 9          ref: NULL         rows: 50010

　　　　Extra: Using index

1 row in set (0.00 sec)

The execution plan also shows that the composite index is used and the table does not need to be returned.

Compare the following rewrite SQL statement, with the idea that sorting is eliminated through indexes.

select a.tid,a.return_date from  t1 a inner join (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;

 

Then, we create an index for the inventory_id column and delete the overwrite index.

alter table t1 add index idx_inid(inventory_id)；drop index liu;

 

Then collect statistics.

select a.tid,a.return_date from  t1 a inner join  (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;+--------+---------------------+| tid    | return_date         |+--------+---------------------+| 800001 | 2005-08-24 13:09:34 || 800002 | 2005-08-27 11:41:03 || 800003 | 2005-08-22 18:10:22 || 800004 | 2005-08-22 16:47:23 || 800005 | 2005-08-26 20:32:02 || 800006 | 2005-08-21 14:55:42 || 800007 | 2005-08-28 14:45:55 || 800008 | 2005-08-29 12:37:32 || 800009 | 2005-08-24 10:38:06 || 800010 | 2005-08-23 12:10:57 |+--------+---------------------+

 

This optimization method consumes about 140 ms compared with the former. Although this optimization method eliminates sorting by using indexes, it still needs to return the primary key value to the table for query. Therefore, when select returns a small number of columns or the column width is small, we can create a composite index to optimize the paging query, which is better because it does not need to return to the table!

References:

Kangaroo Cloud technology team blog, https://yq.aliyun.com/articles/62419

[2] Baron Schwartz; translated by Ninghai yuan; high-performance MySQL (version 3rd); Electronic Industry Press, 2013