Introduction to mysql index

What is an index?

An index is a data structure that efficiently obtains data.

Type of index

FULLTEXT, (HASH, BTREE[two main types used by mysql]), RTREE.

1. FULLTEXT

is the full-text index, currently only supported by the MyISAM engine. It can be used in CREATE TABLE, ALTER TABLE, and CREATE INDEX, but currently only full-text indexes can be created on CHAR, VARCHAR, and TEXT columns.

Full-text index was not born together with MyISAM. It appeared to solve the problem of low efficiency of fuzzy query for text such as WHERE name LIKE "%word%".

(Free learning video tutorial recommendation: mysql video tutorial)

2. HASH
Due to the uniqueness of HASH (almost 100% unique) and similar key values The right form is suitable for use as an index.

HASH index can be located once and does not need to be searched layer by layer like a tree index, so it is extremely efficient. However, this efficiency is conditional, that is, it is only efficient under the "=" and "in" conditions, and is still not efficient for range queries, sorting, and combined indexes.

3. BTREE

The BTREE index is a method that stores the index value into a tree-shaped data structure (binary tree) according to a certain algorithm. Each query is from the entrance of the tree. Starting from root, traverse the nodes in sequence to obtain the leaf. This is the default and most commonly used index type in MySQL.

4. RTREE

#RTREE is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb, and Archive.

Compared with BTREE, the advantage of RTREE lies in range search.

Index type

Normal index: only accelerates query

Unique index: accelerates query with unique column value (can have null)

Primary key index: Accelerate query column value is unique (cannot have null) There is only one in the table

Combined index: Multiple column values ​​form an index, specially used for combined search, its efficiency is greater than index merging

Full-text index: segment the text content and search

Index use

1. Create an index

1 --创建普通索引CREATE INDEX index_name ON table_name(col_name);
2 --创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);
3 --创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
4 --创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2. By modifying the table Structure creation index

ALTER TABLE table_name ADD INDEX index_name(col_name);

3. Directly specify the index when creating the table

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

4. Delete the index

--直接删除索引DROP INDEX index_name ON table_name;
--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

5. Other commands

- 查看表结构
    desc table_name;
 - 查看生成表的SQL
    show create table table_name;
 - 查看索引
    show index from  table_name;
 - 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

Causes of index failure

1. Full value matching, which is equivalent to the index not being used.

2. Failure to meet the optimal prefix rule may also cause index failure.

3. Doing anything on the index (calculation, function, (automatic or manual) type conversion) will cause the index to fail and lead to a full table scan.

4. Mysql cannot use the index when using not equal to (<>, !=), resulting in a full table scan.

5. Index cannot be used even if is null or is not null.

6. Using wildcard switch like ('�c') will cause index failure and full table scan.

7. If the string index is not enclosed in single quotes, the index will be invalid.

8. Use or less. Using or to connect will cause index failure.

9. Use select * query and try to use covering index.

mysql index specification

1. [Mandatory] Fields with unique characteristics in business, even if they are a combination of multiple fields, must be built into a unique index (Note: Don't think that the unique index affects the insert speed. This speed loss can be ignored,

, but the improvement in search speed is obvious; in addition, even if very complete verification control is done at the application layer, as long as there is no unique index, according to According to Murphy's Law, dirty data must be generated.)

2. [Mandatory] Joining of more than three tables is prohibited. The data types of the fields that need to be joined must be absolutely consistent; when performing multi-table related queries, it is ensured that the related fields need to have indexes.

(Note: Even if you join a double table, you must pay attention to the table index and SQL performance.)

3. [Mandatory] When creating an index on a varchar field, you must specify the index length. There is no need to index the entire field, just determine the index length based on the actual text distinction.

(Note: Index length and discrimination are a pair of contradictions. Generally, for string type data, the index with a length of 20 will have a discrimination of more than 90%.

You can use the distinction of count(distinct left(column name, index length))/count(*) to determine.)

4. [Mandatory] Left fuzzy or full fuzzy is strictly prohibited in page search. If necessary, please Let the search engine solve it.

(Note: The index file has the leftmost prefix matching feature of B-Tree. If the value on the left is not determined, then this index cannot be used.)

Recommended related article tutorials: mysql tutorial

The above is the detailed content of Introduction to mysql index. For more information, please follow other related articles on the PHP Chinese website!