mysql optimization (4) index coverage and optimal index

Index coverage** A very important concept is to search on the index! ! !

If the queried column happens to be part of the index, then the query only needs to be performed on the index file, and there is no need to go back to the disk to find data.

This kind of query is very fast and is called For "index coverage"

Non-clustered index index files correspond to data retrieval, which wastes time

The difference between index and data

The index is an efficiently organized tree , node, leaf structure search is better than data

The index can be imported into the memory for query

The index itself has a simple data structure and it is very fast to put it into the memory

. . . . . . . . . As a result, it was full as soon as I returned. . . I wipe. . .

So if you create two indexes index(uid, aid)

If the data you want has been covered in the index tree, such as select uid from msg where aid = 1

The index has covered what you want to look up, so the speed and efficiency of fetching the index directly from the memory are very fast and naturally there is no need to go back and search

Optimal index:

Query frequently, distinguish High degree, small length

Try to cover common fields, ------》Index coverage

The left prefix is ​​not easy to distinguish, reverse the url to get the content; the left prefix is ​​highly distinguishable

Pseudo hash crc32() function to uniquely distinguish crc32 ('http://wwww.baidu.com'); build an index for urlcrc instead of url to avoid indexing

Learn to explain to view Effect;

Pseudo hash index effect

Save url_hash column at the same time

create table t10 (
id int primary key,
url char(60) not null default ''
);

insert into t10 values 
(1,'http://www.baidu.com'),
(2,'http://www.sina.com'),
(3,'http://www.sohu.com.cn'),
(4,'http://www.onlinedown.net'),
(5,'http://www.gov.cn');
alter table t10 add urlcrc int unsigned not null;

When sql is stored, crcurl== crc32(url),

Because the result of crc is a 32-bit int unsigned number, when the data exceeds 4 billion, there will be duplication, but it is worth it.

(Index length is int4 bytes)



Multiple index design

But from the actual business of the mall, customers generally choose large categories first ->small categories->brands,

Finally choose (1)index(cat_id,brand_id), + index(cat_id,shop_price) to build the index

You can even add (3)index(cat_id,brand_id,shop_price), 3 redundant Redundant index fields. . .



But the first 2 columns in (3) are the same as the first 2 columns in (1), then remove (1)



index (cat_id,price) and index(cat_id,brand_id,shop_price); that is, the left prefix principle can be redundant but cannot be repeated

The above is mysql optimization ( 4) Index coverage and optimal index content. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!