MySQL index is under control

This article brings you relevant knowledge about mysql, which mainly introduces related issues about mysql index, including index division according to logical functions, division according to physical implementation, and division according to field individual Index type issues such as number division, I hope it will be helpful to everyone.

Recommended learning: mysql tutorial

In SQL optimization, indexing is a crucial part, which can improve query efficiency It brings a qualitative leap, but indexes are not omnipotent, and unreasonable index design can even slow down query efficiency.

Index definition

The index is a data structure specially designed to help SQL obtain data efficiently. A common example is that the index is similar to the table of contents of a book, which can quickly search for specific values. Locate and search, thereby greatly speeding up the efficiency of data query. In fact, the index is also a table. This table saves the primary key and index fields and points to the records of the entity table (similar to a pointer).

Advantages and disadvantages of index

Advantages

• Index greatly reduces the amount of data that the server needs to scan
• Index can Help the server avoid sorting and temporary tables
• Indexes can turn random IO into sequential IO
• Indexes are very important for InnoDB (which supports row-level locks on indexes), InnoDB only accesses tuples that need to be accessed Locking and indexing can reduce the number of tuples accessed by InnoDB. If the query cannot use an index, MySQL will perform a full table scan and lock every tuple, regardless of whether it is actually needed.

Disadvantages

• Although the index greatly improves the query speed, it will also reduce the speed of updating the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file. Therefore, it is generally not recommended to use indexes for fields that are updated very frequently.
• Creating an index will occupy disk space.
• If a data column contains a lot of repeated content, the indexing effect for it will be very poor. This property is called the selectivity of the index: the ratio of unique index values ​​to the total number of records in the data table. The higher the selectivity of the index, the higher the query efficiency. For example, if you create an index on the gender field, there are only two possibilities for male and female for one million pieces of data. The index selectivity is one in 500,000, and the indexing effect is very poor.
• For very small tables, the index is meaningless. Large, a simple full table scan is more efficient in most cases.

Therefore, only the most frequently queried and most frequently sorted data columns should be indexed. The total number of indexes in the same data table in MySQL is limited to 16.

Index type

Divided by functional logic

Divided from functional logic, the index is mainly divided into ordinary index, unique index, primary key index and full-text index

Normal index

The most basic index, it has no restrictions. The only task of an ordinary index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, indexes should only be created for those data columns that appear most frequently in query conditions (WHERE column = ...) or sorting conditions (ORDER BY column).

There are three ways to create ordinary indexes.

# 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	INDEX idx_username (username) );

Delete index

DROP INDEX idx_username ON user_tbl;

View index

SHOW INDEX FROM user_tbl;

Unique index

It is similar to the previous ordinary index, except that : Ordinary indexes allow the indexed data columns to contain duplicate values. The value of the unique index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique.

The creation of a unique index is similar to a normal index:

#创建索引
CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表结构
ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 创建表的时候直接指定
CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	UNIQUE idx_username (username) 
);

Primary key index

It is a special unique index that does not allow null values. . A table can only have one primary key, which is usually created at the same time when the table is created.

CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	PRIMARY KEY(ID) 
);

Similar to this is the foreign key index. If a foreign key constraint is defined for a foreign key field, MySQL will define an internal index to help it manage and manage in the most efficient way. Use foreign key constraints.

Full-text index

In the previous article MySQL basic syntax, we said that if you use LIKE % at the beginning, the index will be invalid, then when we need to If you need fuzzy search (such as LIKE '%hello%'), you need to use full-text indexing. It should be noted that Innodb only supports full-text indexing after version 5.6.

Creation and deletion of full-text index:

# 创建的两种方法
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 删除的两种方法
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

The syntax of using full-text index for full fuzzy matching is:

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx');
# 比如对 user_tbl 的 user_name 字段加了全文索引
# 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%';
SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

Use explain to check and you can find that the fulltext index is effective.

Divided by physical implementation

Divided by physical implementation, it can usually be divided into clustered indexes and non-clustered indexes.

聚集索引（clustered index）

存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。

聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。

需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。

非聚集索引（non-clustered index）

非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。

非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。

按字段个数划分

按字段个数可以把索引分为单一索引和联合索引。

单一索引

索引字段只有一列时为单一索引，上述所有索引都是单一索引。

联合索引

将多个字段组合在一起创建的索引叫联合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原则

建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：

usernname,city,age
usernname,city
usernname

为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的最左匹配原则，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。

对于 (x,y,z)，只有在以下查询条件联合索引会生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

对于其他情况，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不会匹配联合索引，索引失效，注意对于 WHERE x = 1 AND z = 1，联合索引会对 x 生效，但是对 z 不生效。

可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。

要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

顺便提一下，可以用 explain 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。

如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：

1. MySQL 优化器根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。

按索引结构划分

不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。

使用总结

接下来我们来简单总结一下在什么场景下推荐使用索引。

推荐使用

• WHERE, GROUP BY, ORDER BY 子句中的字段

• 多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。

  联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那么联合索引应该设计为 (a,b,c)，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。

• 多张表 JOIN 的时候，对表连接字段创建索引。

• When there are fields in the SELECT that are not in the index, the primary key value that meets the conditions will be queried through the index first, and then all the fields in the SELECT will be queried through the primary key Return to the table fields, affecting query efficiency. Therefore, if there is very little content in the SELECT, in order to avoid returning the table, you can add all the fields in the SELECT to the joint index. This is the concept of a wide index. However, it should be noted that if there are too many index fields, the cost of storing and maintaining the index will also increase.

Not recommended or index failure

• Table with small data volume

• Fields with a large amount of duplicate data

• Frequently updated fields

• If functions or expression calculations are used for index fields , index invalid

• innodb OR condition does not create index for all conditions, index invalid

• Greater than less than condition <code>>, whether the index takes effect depends on the proportion of the number of hits. If the number of hits is large, the index will take effect. If the number of hits is small, the index will fail.

• is not equal to the condition!= , index invalid

• LIKE value starts with %, index invalid

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL index is under control. For more information, please follow other related articles on the PHP Chinese website!