Three, Mysql high-frequency examination site index booklet | free learning

The interviewer always asks again what is an index

When it comes to indexes, I will immediately think of B+ tree indexes, but I have no idea about indexes themselves

Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table.

An index is a data structure. A database index is a sorted data structure in a database management system to help query and update data in a database table quickly. Indexes are usually implemented using B trees and their variant B+ trees. More generally, an index is a table of contents. In order to facilitate the search of the contents of the book, through the content of the index to form a catalog. And the database index is a file, whether it is to maintain his time consumption, or his own space consumption, it is a small price for higher efficiency

MySQL has several index types

• B tree
• R tree (for geographic space)
• hash
• Full text index (MyISAM only available)

Why not B+ trees, why not B trees, why not red black trees, why not hash trees

• Why not use a red-black tree

Red and black trees must exist in memory, and binary tree is too deep, frequent I/O read

• Why not B? B plus is lower than B

B+ trees have fewer levels; B+ tree query speed is more stable [all have to go to the leaf node], with sorting function, the whole node traversal fast

Since only leaf nodes have data, non-leaf nodes are small, so one IO reads more nodes.

• Why not hash?

Hash cannot be used for range query. The B+ tree can be searched in batches without loading all of them into memory. The results of the B+ tree are ordered.

Hash indexing requires all columns as indexes, so it is difficult to support partial index lookup

Clustered index and non-clustered index

• Data and indexes together: clustering
• The primary key and index of the data are together: non-clustered

Our own indexes are mostly non-clustered indexes

The order of the union index

When we sort things, the simplest things are numbers

It’s got to be hundreds, then tens, then ones.

Therefore, when we use a joint index (MySQL can use multiple fields to create an index at the same time, called a joint index), in a joint index, we need to use the fields in the same order as when creating the index; otherwise, the index cannot be matched.

So (a, b, c) index can be used for not only, also can be used to (a, b), (a), (a, c) [right can be used for ac]

(b, a) (b,c)

In general, columns with frequent query requirements or high field selectivity are placed first.

Bridge bean sack! Why can the index of (a,b,c) be ac

indexing

Query ac

SELECT * FROM user_address WHERE province = 'in guangdong' 
AND district = 'Nanxiong city'
Copy the code

Conclusion: The possible_keys column can show that the possible_keys column uses the possible_keys because C in AC cannot match the possible_keys joint index of these three fields

Again, basic understanding of leftmost matching

• Mysql will keep matching to the right until it encounters range queries (>, <, between, like) and then stops matching
• Theta and in can be out of order

If a=3 and b=4 and c>5 and d=6 if a=3 and b=4 and C >5 and d=6 if a=3 and b=4 and C >5 and d=6 if a=3 and b=4 and C >5 and D =6 if a=3 and b=4 and C >5 and D =6 if a=3 and b=4 and C >5 and D =6

MySQL > select * from index

The Type field is important because it provides an important basis for determining whether a query is efficient. Through the type field, we can determine whether the query is a full table scan or an index scan, etc. Such as const(primary key index or unique secondary index for equivalence matching),ref(ordinary secondary index column for equivalence matching constant),index(scan full index overwrite index).

Generally speaking, the performance relationship of different types of types is as follows: ALL < index < range ~ index_merge < ref < eq_ref < const < system

How indexes are created

• The index is created when the CREATE TABLE is executed

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

Copy the code

• Use the ALTER TABLE command to add indexes.

ALTER TABLE table_name ADD INDEX index_name (column_list);
Copy the code

ALTER TABLE creates a normal, UNIQUE, or PRIMARY KEY index.

Table_name indicates the name of the table to which the index is to be added. Column_list indicates the column to which the index is to be added. If there are multiple columns, the columns are separated by commas.

The index name index_name is self-naming. By default, MySQL assigns a name based on the first index column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.

• Run the CREATE INDEX command to CREATE the INDEX.

CREATE INDEX index_name ON table_name (column_list);
Copy the code

Principles for index creation

• The higher the value dispersion of a field in a table, the more suitable the field is to be selected as the key for the index
• Fields that occupy less storage space are better keywords for indexes.
• Fields with fixed storage space are better candidates for indexing keywords
• Frequently used fields in the Where clause should be indexed
• Fields that are frequently updated are not suitable for creating indexes
• Invalid index with NULL value

Please add the following text and link at the end of the article: This article is participating in the “Gold Digging Booklet free learning!” Event, click to view details of the event