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

What I want to share with you in this article is that mysql can be indexed, but I can only rely on working. The content of. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

I. Index data structure

This question is sure to be asked during the interview, why did mysql choose the b + tree as the index? Instead of selecting other indexes, such as b-tree? Hash?

The disk IO below refers to the operation of loading data from the hard disk into memory.

Range query is not supported for hash index, because hash is a key corresponding to a value, so there is no way to query range.

The characteristic of binary tree is that the left subtree is smaller than the root node and the right subtree. If there is a problem with the value of the root node, it may degenerate into a linked list, that is, the tree does not bifurcate, and the tree goes all the way to the left or right, so it cannot be found in half and the number of IO is reduced. Range query is not supported. If the range query is made, it should be traversed from the root every time, and the tree is too high. The higher the tree is, the more frequent the IO operation is. Waste of resources

If the binary tree is balanced, it will not have the disadvantage of reducing the binary tree to a linked list, because the difference between its left and right child nodes is at most 1 layer, but it does not support range search, which is the same as the problem of binary tree.

B tree, compared with the binary tree, the tree is very chunky, the IO operation is reduced, it is a multi-tree, it stores the corresponding row data in each node, but if the column of the data in this row continues to increase, then the nodes stored on this page will become less, because the space occupied continues to increase, the tree will become higher and higher, increase the number of IO operations, and do not support range search at the same time. It would be better if a lot of node data can be stored in the same size of space, so there is the following b + tree

B + tree is a non-leaf node that only stores indexed data and does not store the whole row of data, but leaf nodes are redundant, redundant non-leaf nodes, and leaf nodes are all linked by a two-way linked list, which helps to find sequentially. Compared with b-tree, b + tree is fatter and has fewer disk IO times.

2. Index types in mysql clustered index and non-clustered index

We can simply understand that a clustered index is a primary key index, and a non-clustered index is a general index.

The essential difference is that

The leaf node of a clustered index stores the whole row of data.

Innodb implements the clustered index through the primary key. If there is no primary key, he will choose a unique non-empty index to implement it. If there is no more, he will implicitly generate a primary key to implement the clustered index.

Non-clustered indexes store index values and primary key values

General index A table can have multiple ordinary indexes, and any field can build an index. Most of the indexes we usually build are ordinary indexes.

An index created by the combination of several fields.

Unique index business unique fields are suitable for establishing unique indexes, and there can be multiple unique indexes in a table.

Primary key index is the same as unique index, primary key index is unique, the difference is that a table can only have one primary key index.

3. Sql on the index

Create a primary key index

ALTER TABLE test add PRIMARY KEY (id) copy code

Create a unique index

ALTER TABLE test add UNIQUE idx_id_card (id_card) copy code

Create a normal index

ALTER TABLE test add INDEX idx_name (name) copy code

Create a federated index

ALTER TABLE test add INDEX idx_age_name (age,name) copy code

Modify index name: delete before adding

Delete index (two ways)

ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test-Delete the primary key index DROP PRIMARY key on test ALTER TABLE test DROP PRIMARY key copy code

View indexes in a table

SHOW INDEX FROM test copy code

Analytical index

EXPLAIN SELECT * from test WHERE name = "xhJaver" copy code

Let's first add an index to the name field, which is called idx_name

ALTER TABLE test add INDEX idx_name (name) copy code

View indexes in the test table

SHOW INDEX FROM test copy code

The attributes in it

Table: table name

Non_unique: 1 for repeatable, 0 for non-repeatable, 0 for our primary key and 1 for name, because name can be repeated and primary key cannot be repeated.

Key_name: index name

Seq_in_index: order of columns in the index

Column_name: column name

Collation: how columns are stored, An ascending order, null unordered

Cardinality: the larger the number, the more likely it is to use this index

Sub_part: the number of characters indexed if the column is only partially indexed, or null if the entire column is indexed

Packed: whether the keyword is compressed. Null indicates that it is not compressed.

Null: yes if the column contains null, or no if there is no null

Index_type: index data structure

Comment: multiple commentaries

4. Return to the table to query select * from test where name = "xhJaver" copy code

Let's say that our name field is indexed, and then when we run this sql statement, because we are establishing a general index, the data stored in the leaf node of our b + tree is id, we will find the id of this record in which name is xhJaver, and then according to this id, go to the b + tree indexed by the primary key to query, and when we query the leaf node, we will query this record. We run from one tree to another to continue to check, which is called "back to the table query". Is there any way to check only one tree to find out the results?

5. Overlay index

Of course, there is a way, and that is to overwrite the index, and we noticed that all the elements were queried in this sql statement, if we write it this way.

Select address from test where name = "xhJaver" copy code

If the index we built is (name,address), then the data stored by the leaf node of this b + tree at this time (name,address) includes address, and there is no need to look up the second tree according to the id of name = "xhJaver" at this time, thus avoiding the query back to the table.

VI. Leftmost matching principle

Let's say we write a sql statement like this now.

Select * from test where name = "xhJaver" and age = 23 and address= "JD.com" copy code

And the index we built is (name,address,age) so we can use (name,address,age) the index, but if we want to write it this way,

Select * from test where name = "xhJaver" and age > 23 and address= "JD.com" copy code

This will only use (name,age) these two indexes. Start matching from the left. If you encounter a range query, you will not continue to match the index to the right.

7. Explain analysis index statement

Let's parse the following sql statement with the declare statement

EXPLAIN SELECT * from test WHERE name = "xhJaver" copy code

Its properties are

Id: order of execution

When the id is the same, the id is executed in different order from top to bottom, and the larger id is executed first.

Select_type: type of query

Primary: the outermost query is marked primarysimple: simple query, no other tables associated, just a table subquery: subquery in where or select derived: derived virtual table such as from (subquery) t, the result of this subquery is placed in the virtual table t

Table: about which table

Partitions: zoning related (I don't get it yet)

Type: access type

The order of performance from good to bad is system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL. Generally speaking, a good sql query is at least range level, preferably ref.

System: there is only one row of data in the table

Const: a constant query is usually used to compare that the primary key is equal to a constant, which can be found once with an index query.

Eq_ref: unique index, where each index corresponds to a piece of data, such as a primary key index

Ref: non-unique index, each index may correspond to multiple rows of data, such as a normal index

Range: scope query, using >

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.