What is a mysql index? Introduction to related knowledge of mysql index

This article brings you what is the mysql index? The introduction of relevant knowledge about mysql index has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

What is an index

The index is like the table of contents of a book

The index is used for quick search To find a row with a specific value in a column, without using an index, MySQL must read the entire table starting from the first record until it finds the relevant row. The larger the table, the more time it takes to query the data. If the queried column in the table has an index, MySQL can quickly get to a location to search the data file without having to look at all the data, which will save a lot of time.

Advantages and Disadvantages

Advantages

1. Greatly speed up querying

2. All field types can be indexed

Disadvantages

1. It takes time to create and maintain indexes. The more data, the more time-consuming

2. Indexes occupy storage space, and the data in the data table will also There is a maximum online setting. If we have a large number of indexes, the index file may reach the online value faster than the data file

3. When adding, deleting, or modifying data in the table, the index also needs Dynamic maintenance reduces the data maintenance speed

Usage principles and scenarios

1. The more indexes, the better, it depends on the situation

2. Frequently updated tables should have as few indexes as possible

3. Construct indexes for fields that are frequently used for queries

4. Try not to use indexes for fields with small amounts of data. Query all The time spent on data is shorter than that of traversing the index data, and the index will have no optimization effect

5. Try not to use indexes for fields with few different values, such as the gender field which only has two different values ​​for men and women.

Index classification

Note: The index is implemented in the storage engine, which means that different storage engines will use different indexes

MyISAM and InnoDB storage engines: only support BTREE indexes, which means BTREE is used by default and cannot be replaced.

MEMORY/HEAP storage engines: supports HASH and BTREE indexes

1. Single column Index

An index only contains a single column, but there can be multiple single-column indexes in a table

1.1. Ordinary index

The basic index type in MySQL, no What restrictions are allowed to insert duplicate values ​​and null values ​​​​in the columns where the index is defined, purely to query the data faster.

1.2. Unique index

The value in the index column must be unique, but null values ​​are allowed

1.3. Primary key index

is a Special unique index, no null values ​​allowed

2. Combined index

An index created on a combination of multiple fields in the table, only in the query conditions The index will only be used when the left field of these fields is used. When using the combined index, follow the best left prefix rule

3. Full-text index

Full-text index, It can only be used on the MyISAM engine, and full-text indexes can only be used on CHAR, VARCHAR, and TEXT type fields. Full-text index means that in a pile of text, you can find the record line to which the field belongs through a certain keyword, for example, "You are a big bad pen, a second-rate person..." Through the big bad pen, it may be possible Find the record

4. Spatial index

The spatial index is an index established for fields of spatial data types. There are four spatial data types in MySQL, GEOMETRY , POINT, LINESTRING, POLYGON. When creating a spatial index, use the SPATIAL keyword. Requirements: The engine is MyISAM. The column used to create a spatial index must be declared as NOT NULL

Index method

Usage principle: If the difference in values ​​is large, and Mainly based on equal value search (=, <=>, in), Hash index is a more efficient choice, it has O(1) search complexity; if the difference of values ​​is relatively poor, and range search is Mainly, B-tree is a better choice, it supports range search.

B-Tree Index

B-Tree index has the capabilities of range search and prefix search. For a B-tree with N nodes, the complexity of retrieving a record is O(LogN). Equivalent to binary search.

Hash Index

Hash index can only perform equal searches, but no matter how big the Hash table is, the search complexity is O(1).

Index creation and deletion

Creation

Created when creating a table

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

Example:

CREATE TABLE `NewTable` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (255) NOT NULL,
    `name` VARCHAR (255) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT 0,
    `address` VARCHAR (255) NULL,
    PRIMARY KEY (`id`), # 主键索引
    INDEX `name` (`name`) USING BTREE, # 普通索引
    UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引
    INDEX `u_n_a` (`username`,    `name`,`address`) USING BTREE # 组合索引
);

Existing table creation

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

Example:

ALTER TABLE `test`
ADD PRIMARY KEY (`id`),  # 主键索引
ADD INDEX `name` (`name`) USING BTREE , # 普通索引
ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引
ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引

Delete index

ALTER TABLE 表名 DROP INDEX 索引名。

Example:

ALTER TABLE `test`
DROP PRIMARY KEY,
DROP INDEX `username`,
DROP INDEX `name`,
DROP INDEX `u_n_a`;

Update index

Delete first and then build

ALTER TABLE `test`
DROP INDEX `username` ,
ADD UNIQUE INDEX `username1` (`username`) USING BTREE ,
DROP INDEX `name` ,
ADD INDEX `name2` (`name`) USING BTREE ,
DROP INDEX `u_n_a` ,
ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

Index failure situation

1. The combined field does not follow the optimal left prefix rule

2. Fuzzy query, such as like '%test

# 索引生效
select * from `test` where `name` like "123";
# 索引生效
select * from `test` where `name` like "123%";
# 索引失效
select * from `test` where `name` like "%123";
# 索引失效
select * from `test` where `name` like "%123%";

3. 在索引列上做如下任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范围索引(>,<,between and)后，无法命中组合索引右边的列

构建索引

ALTER TABLE `test`
ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;

示例：

# 命中全部
select * from `test` where `sex` = 1 and `name` = 'a';
# 命中部分，sex命中，name失效
select * from `test` where `sex` > 1 and `name` = 'a';</p>
<p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p>
<p style="white-space: normal;">6. 字符串字段的值不加单引号（数字不报错，英文报错）索引失效</p>
<p>构建索引</p>
<pre class="brush:php;toolbar:false">ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 条件导致索引失效

构建索引

ALTER TABLE `test`
ADD INDEX `sex` (`sex`) USING BTREE ;
ADD INDEX `n_u` (`name`, `username`) USING BTREE ;

示例：

# 索引不生效
select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 
# 索引sex生效
select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )

附录

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

如下构建索引

ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

如下查询情况

# 命中部分
select * from `test` where `username` = 'aaa';
# 命中部分
select * from `test` where `username` = 'aa' and `address` = 'aaa';
# 全命中
select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a';
# 不命中，第一条件字段不是username
select * from `test` where  `address` = 'aaa';

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