mysql optimization (2) index optimization strategy

1: Index type

Index: used for quick query;

Node level 1, 2 raised to the 0th power

Node layer 1, 2 to the 1st power

Node layer 3, 2 to the 2nd power

Node layer 4, 2 to the 3rd power

Node layer 5 layers, 2 to the 4th power

. . .

. . .

. . .

Node layer 31, 2 to the power of 32

adds up to 4.2 billion

That is to say, 4.2 billion numbers can be checked up to 32 times

Ordinary queries require 2.1 billion times



This is-----》B-tree index

Note: It is called btree index, from a broad perspective , both use balanced trees, but in terms of specific implementation, each engine is slightly different,

For example, strictly speaking, the NDB engine uses T-tree

Myisam, innodb , the B-tree index is used by default



But abstractly---the B-tree system can be understood as a "sorted fast search structure".



1.2 hash index spring hahahaha. . . Nima Nima. . .

In the memory table, the default is hash index,
The theoretical query time complexity of hash is O(1)



Question: Since hash search is like this Efficient, why not use hash index?

Answer:

1: The result calculated by the hash function is random. If the data is placed on the disk,
use the algorithm. . . . .

For example, if the primary key is id, then as the id increases, the rows corresponding to
id are randomly placed on the disk. They are scattered irregularly! !

Hash algorithm There is no rule in allocating disk space! ! !

2: The range query cannot be optimized. 3: The prefix index cannot be used.

For example, in btree, the value of the field column is "hellopworld" and the index is added

To query xx=helloword, you can naturally use the index, xx=hello, or you can also use the index.
(left prefix index)

Because hash('helloword') and hash('hello') are both The relationship between the two is still random

4: The sorting cannot be optimized.

5: The row must be returned. That is to say, the data location is obtained through the index, and the data must be returned to the table

------》If you go back and search, it means that the directory is just a dictionary and you must actually turn the page again



2: Common misunderstandings of btree indexes

2.1 Add indexes to the columns commonly used in where conditions

Example: where cat_id=3 and price>100; //Query the third column, products above 100 yuan

Error : Indexes are added to cat_id, and, and price.

Error: Only cat_id or Price index can be used, because they are independent indexes, and only one can be used at the same time.

alter table add index(cat_id)

alter table add index(price)

alter table add index(goods_id) ----------------- ----------Only one can be used at the same time. . . . The joint index treats multiple columns as the overall value

index (cat_id, goods_name, price) ------------------------- -- Treat multiple columns as the overall value



2.2 After creating an index on multiple columns, the index will work no matter which column is queried

Error: Multiple On the column index, for the index to work, it needs to meet the left prefix requirement.
///Make prefix requirements

Take index(a,b,c) as an example, (note that it depends on the order)

Statement

Does the index work?

Where a=3

Yes, only column a is used

Where a=3 and b=5

Yes, columns a and b are used

Where a=3 and b=5 and c=4

Yes, abc

is used

Where b=3 / where c=4

No

Where a=3 and c=4

Column a can function as an index, but column c cannot

Where a=3 and b>10 and c=7

A can be used, b can be used, C cannot be used

Same as above, where a=3 and b like 'xxxx%' and c=7

A can be used, B can be used, C cannot be used

For ease of understanding, assume that ABC is a 10-meter-long plank each, and the river is 30 meters wide.

Exact match, then the board is 10 meters long,

Like, left prefix and range query, then the board is 5 meters long,



Splice it yourself, you can If you cross the other side of the river, you will know whether the index can be used.

As in the above example, where a=3 and b>10, and c=7,

A board is 10 meters long, A The column index works

Board A is connected to board B normally, and the index of board B is working

Board B is short and cannot be connected to board C,
The index of column C does not work.

The above is the content of mysql optimization (2) index optimization strategy. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!