Shulou(Shulou.com)06/01 Report--

This article shows an example of mysql index overrides. Share with you for your reference, the details are as follows:

Index overlay

If the column of the query happens to be part of the index, the query only needs to be done on the index file and does not need to go back to disk to find the data. This kind of query is very fast and is called "index overwrite".

Suppose there is a T15 table in which a federated index is created: cp (cat_id,price)

When we use the following sql statement, an index overwrite occurs. If you don't believe it, we can take a look. Using index is shown in the Extra here, indicating that this sql statement happens to use index overrides.

Select price from T15 where cat_id = 1

To take a look at this, create a T11 table with an index in the email column

Suppose we use a query statement like this:

Select eamil from T11 where right (email,4) = '.com'

Query Analysis:

First of all, take a look at Extra, there is Using index, which means that index coverage is used here, and the reason why possible_keys is NULL is because the function in mysql is used, so the email index is not used in the query, but key is email, which means the index is used for sorting. Do not believe me to print the data.

The data here is sorted. The original data is like this.

The problem of index coverage

Create table A (id varchar (64) primary key,ver int, …)

There are several very long fields varbinary (3000) in the table, and there are joint indexes on id and ver, with a total of 10000 pieces of data.

Why is select id from An order by id so slow?

And select id from An order by id,ver is very fast.

Question: id, (id,ver) all have indexes, and select id should all produce the effect of "index coverage". Why is the former slow and the latter fast?

Idea: innodb clustering is different from myisam index, and index coverage is considered from these two angles.

(1) assuming that this table uses an index of Myisam, then neither of these sql statements need to backtrack to find data, so they should be about the same speed.

(2) assuming that the table uses the index of InnoDB, then the sentence select id from An order by id uses the primary key index. Because each primary key of InnoDB mounts the data of each row, and there are several very large fields in this question, you will need to go relatively slowly when looking for id. And select id from An order by id,ver this sql uses the id,ver federated index, in the InnoDB storage engine, the secondary index saves the application of the primary key index, so the secondary index does not mount the data of the row, then in the (id,ver) index to find id will be fast, when finding the corresponding node tree, only need to find the location of the primary key index, you can get the data of the row, so faster.

Infer:

(1) if the table is a myisam engine, two statements, there will be no significant difference in speed.

(2) innodb table because of clustering index, id index has to span N blocks on disk, which leads to slow speed.

(3) even for the innodb engine, without those long varbinay columns, there would be no significant difference in the speed of the two statements.

T12 table, storage engine MyISAM, primary key index and (id,ver) match index, and several large variable length fields, test corollary 1

T13 table, storage engine InnoDB, primary key index and (id,ver) match index, and several large variable length field corollary 2

T14 table, storage engine InnoDB, primary key index and (id,ver) match index, few large variable length fields corollary 3

There are 1W pieces of data in each table, and then the test results are as follows. Our inference is correct.

Readers who are interested in MySQL-related content can check out this site's special topics: "Summary of MySQL Index Operation skills", "Summary of MySQL Common functions", "Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.