MySQL delay correlation and index overlay

The tests that were previously done on the latency correlation and index coverage were not recorded at the time, resulting in the recollection of forgetting some of the minutiae behind them. Today, take advantage of the impulse to revisit the test results.

The following is the build table statement:

CREATE TABLE ' human ' (' ID ' int (one) not NULL auto_increment COMMENT ' primary key ', ' name ' varchar () NOT NULL DEFAULT ' COMMENT ' Name ', ' age ' int (one) not null default ' 0 ' COMMENT ' old ', ' sex ' tinyint (4) NOT null default ' 0 ' COMMENT ' sex ', ' occupation ' int ' NOT NULL default ' 0 ' COMMENT ' occupation ', ' education ' int (one) not null default ' 0 ' COMMENT ' education ', ' Birthday ' datetime Not NULL default Current_timestamp COMMENT ' birthday ', ' City ' varchar (a) NOT null default ' COMMENT ' location ', ' adress ' varchar (+) NOT null default ' COMMENT ' Detailed address ', ' town ' varchar (+) NOT null default ' COMMENT ', ' Village ' varchar ' Null default ' COMMENT ' Village ', ' Province ' varchar (a) NOT null default ' COMMENT ' province ', ' district ' varchar (NOT NULL DE) FAULT ' COMMENT ' zone ', PRIMARY key (' ID '), key ' name ' (' name '), Key ' district ' (' District ') USING BTREE, key ' Province_ District ' (' Province ', ' district ') using BTREE, KEY ' City ' (' City ', ' Town ', ' Village ') using BTREE) Engine=innodb AUTO_INCR ement=13765413 DEFAULT CHArset=utf8; 　　 

wrote a web demo that fills in data, and uses HTTP requests to pour data into it. GitHub Address:Https://github.com/Mingo-xoto/IndexTestDemo, interface class name:Com.yhq.web.controller.TestController

Number of records: 13586901

The index is established as follows:

1. Index Overlay

Select id,city from human where city= ' Guangzhou ' limit 10000, 10;

Or

Select Id,city,town, village from human where city= ' Guangzhou ' limit 10000, 10;

It's basically a 0.00-second level.

Since I have built a composite index of city , town, and village , the index MySQL query index can directly return the results without needing to query the specific column, but if you need to query the columns of the composite index, you need to return to the table operation.

Select Id,city,town, village,province from human where city= ' Guangzhou ' limit 10000, 10;

It takes 46.637 seconds to get results.

2. Delayed correlation

The traditional way of querying 10000 pages after 10 records:select * from human where city= ' Guangzhou ' limit 10000, 10; time will be nearly 1 minutes .

After using delay correlation:0.004s, quality improvement!

SELECT * from human h INNER JOIN
(
Select id,city from human where city= ' Guangzhou ' limit 10000,10
) as a on a.id=h.id;

Or

SELECT * from human h INNER JOIN
(
Select id,city from human where city= ' Guangzhou ' limit 10000,10
) as a using (ID);

Both are OK, the test seems to be no different.

As to why Ascension is so fast, it is mentioned in "High-performance MySQL" (images are directly quoted from others ' blogs:http://blog.csdn.net/u012817635/article/details/52277490):

Blogging for the first time in the blog park, combing the knowledge may not be very accurate, just as a personal learning record.

MySQL delay correlation and index overlay