Covering indexes of MySQL SQL optimization and optimizing indexes of mysqlsql

Covering indexes of MySQL SQL optimization and optimizing indexes of mysqlsql

Content Overview

Using the primary index to improve SQL query efficiency is a frequently used technique, but sometimes the execution plan provided by MySQL is completely unexpected, we expect that MySQL will complete the query through index scanning, but the execution plan provided by MySQL is completed through full table scanning. In some of these scenarios, we can use the overwriting index for optimization.

A colleague told me a few days ago: "I wrote an SQL statement, which is very simple, but the query speed is very slow. I created an index for the query conditions, but the index does not work, can you help me see if there is any way to optimize it? ".

I optimized his case and sorted out the optimization process.

Table structure, data volume, SQL, execution plan, and execution time before optimization

Table Structure

Create table 't_ order '(

'Id' bigint (20) unsigned not null AUTO_INCREMENT,

'Order _ Code' char (12) not null,

'Order _ amount 'decimal (12, 2) not null,

Primary key ('id '),

Unique key 'uni _ order_code '('order _ Code') using btree)

ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8;

After hiding some irrelevant fields, we can see that the table is simple enough and the unique index uni_order_code is created on order_code.

Data volume: 316977

This data size is relatively small, but if the SQL is poor enough, the query will be slow.

SQL

Select order_code,

Order_amount from t_order order by order_code limit 1000;

Wow, SQL is simple enough, but sometimes the simpler it is, the harder it is to optimize it.

Execution Plan

Full table scanning and File Sorting are destined to slow query!

Why didn't MySQL use the index (uni_order_code) scan to complete the query? MySQL considers that index scanning is not the optimal result in this scenario. Let's take a look at the execution time and then analyze why index scanning is not used.

Running time: 260 ms

Indeed, the execution time is too long. If the table data size continues to grow, the performance will get worse and worse.

Differences between full table scanning, File Sorting, index scanning, and index sorting

Full table scan and File Sorting:

Although it is a full table scan, the scan is sequential (whether the mechanical hard disk or SSD sequential read/write performance is high), and the data volume is not particularly large, therefore, this part of the time consumed should not be particularly large, and the main consumption should be in sorting.

Use index scanning and index order:

Uni_order_code is a secondary index, and the index is saved (order_code, id). Each index scanned must be located (random IO) based on the index id to read order_amount from the data row, the query can be completed only after 1000 random I/O requests, and the efficiency of random I/O operations on a mechanical hard disk is extremely low (the mechanical hard disk can be addressable several hundred times per second ).

It is better to select full table Scan Based on our own analysis. If you change limit 1000 to limit 10, the execution plan will be completely different.

Since we already know that index cannot be used due to random I/O, is there a way to eliminate random I/O?

Yes, covering the index.

Optimized index, execution plan, and execution time

Create an index

Alter table 't_ order'

Add index 'idx _ ordercode_orderamount 'using btree ('order _ Code' ASC, 'order _ amount' ASC );

The composite index idx_ordercode_orderamount (order_code, order_amount) is created, and the select column order_amount is also placed in the index.

Execution Plan

The execution plan shows that the query will overwrite the index and only scan 1000 rows of data. The query performance should be very good.

Execution time: 13 ms

From the execution time point of view, the SQL Execution time has been increased to 1/20, which has reached our expectation.

Summary

Overwrite index: select data columns can be obtained only from the index without reading data rows. In other words, the query column must be overwritten by the index. Indexed fields include not only query columns, but also query conditions and sorting.

To write SQL statements with good performance, you must not only learn SQL, but also understand the database Execution Plan and the database Execution Process and index data structure.

Reposted from: Captain Mr

Original article: https://my.oschina.net/loujinhe/blog/1528233#comment-list

Related reading:

The impact of more than 20 indexes on remote database tables

How to understand and correctly use MySql Indexes

CBO's choice for indexes with the same Cost value

Structure of Oracle index branch blocks

Composite Index and variable binding

Download Resources

Public Account: Data and cloud (OraNews) reply keyword acquisition

'Dedicated DTC', Dig DTC Conference PPT

'Dbalife', "DBA's Day" Poster

'Dba04', DBA Manual 4, classic chapter ebook

'Internal', Oracle RAC PPT

'122arch', Oracle 12.2 architecture Diagram

'Drawing oow', Oracle OpenWorld documents

'Prection', Lecture hall Course Materials