Big reveal! MySQL database index

1. Overview

The index is a data structure used by the storage engine to quickly find records. The reasonable use of database indexes can greatly improve the access performance of the system. Next, we will mainly introduce the index types in

MySql database, and how to create more reasonable and efficient index techniques.

Note: The main focus here is the B Tree index data structure of the InnoDB storage engine

2. The advantages of the index

are greatly reduced Reduces the amount of data that the server needs to scan, thereby improving the data retrieval speed

Helps the server avoid sorting and temporary tables

Can turn random I/O into sequential I/O

3. Index creation

3.1. Primary key index

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name' ('column');

3.2. Unique index

ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');

3.3, ordinary index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');

3.4, full-text index

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');

3.5, combined index

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

4. B Tree index rules

Create a test user table

DROP TABLE IF EXISTS user_test;CREATE TABLE user_test(    id int AUTO_INCREMENT PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    sex bit(1) NOT NULL DEFAULT b'1',
    city varchar(50) NOT NULL,
    age int NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a combined index: ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1. Query with valid index

4.1.1. Full value matching

Full value matching refers to matching with all columns in the index. For example: taking the index created above as an example, after the where condition, you can query (user_name, city, age) at the same time as

Conditional data.

Note: It has nothing to do with the order of query conditions after where. This is a place that many students easily misunderstand.

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';

4.1.2. Matching Leftmost prefix

Matching the leftmost prefix means matching the leftmost index column first. For example: the index created above can be used for query conditions: (user_name), (user_name, city), (user_name, city, age)

Note: The order that satisfies the leftmost prefix query conditions has nothing to do with the order of the index columns, such as: (city, user_name), (age, city, user_name)

4.1.3. Matching column prefix

refers to the beginning of the matching column value, for example: query all users whose username starts with feinik

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.1.4. Matching range value

For example: query all users whose username starts with feinik , the first column of the index is used here

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.2. Index restrictions

1. The where query condition does not include the leftmost index column in the index column. Then the index query cannot be used, such as:

SELECT * FROM user_test WHERE city = '广州';

or

SELECT * FROM user_test WHERE age= 26;

or

SELECT * FROM user_test WHERE city = '广州' AND age = '26';

2. Even if the query condition of where is the leftmost index column, it cannot Use the index to query users whose user names end with feinik

SELECT * FROM user_test WHERE user_name like '%feinik';

3. If there is a range query for a certain column in the where query condition, all columns to the right cannot be queried using the index optimization, such as:

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

5. Efficient indexing strategy

5.1. Index columns cannot be part of an expression or a parameter of a function, otherwise Index query cannot be used.

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

5.2. Prefix index

Sometimes it is necessary to index very long character columns, which will increase the storage space of the index and reduce the index For efficiency, one strategy is to use a hash index, and another is to use a prefix index. The prefix index selects the first n characters of the character column as the index, which can greatly save index space, thus Improve indexing efficiency.

5.2.1. Selectivity of prefix index

Prefix index should choose a prefix long enough to ensure high selectivity, but not too long at the same time , we can calculate the selection length value of the appropriate prefix index in the following way:

(1)

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列

Note: Calculated by the above method The selectivity ratio of the prefix index. The higher the ratio, the more efficient the index is.

(2)

SELECTCOUNT(DISTINCT LEFT(index_column,1))/COUNT(*),COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),COUNT(DISTINCT
 LEFT(index_column,3))/COUNT(*)
 ...FROM table_name;

Note: Find it step by step through the above sentences The selectivity ratio closest to the prefix index in (1), then you can use the corresponding character interception length to make the prefix index

5.2.2. Creation of prefix index

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

5.2.3. Notes on using prefix index

Prefix index is an effective way to make the index smaller and faster , but MySql cannot use prefix indexes for ORDER BY and GROUP BY and use prefix indexes for coverage

scans.

5.3、选择合适的索引列顺序

在组合索引的创建中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询方式，对于组合索引的索引顺序可以通过经验

法则来帮助我们完成：将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致，但并不是说所有的组合索引的顺序

都使用该法则就能确定，还需要根据具体的查询场景来确定具体的索引顺序。

5.4 聚集索引与非聚集索引

1、聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，如

果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索

引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了

对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作

为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。

2、非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在

B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。

5.5、覆盖索引

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可

以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

5.6、如何使用索引来排序

在排序操作中如果能使用到索引来排序，那么可以极大的提高排序的速度，要使用索引来排序需要满足以下两点即可。

1、ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致

2、所查询的字段值需要包含在索引列中，及满足覆盖索引

通过例子来具体分析

在user_test表上创建一个组合索引

ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

可以使用到索引排序的案例

1、SELECT user_name, city, age FROM user_test ORDER BY user_name;

2、SELECT user_name, city, age FROM user_test ORDER BY user_name, city;

3、SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;

4、SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

注：第4点比较特殊一点，如果where查询条件为索引列的第一列，且为常量条件，那么也可以使用到索引

无法使用索引排序的案例

1、sex不在索引列中

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2、排序列的方向不一致

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3、所要查询的字段列sex没有包含在索引列中

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

4、where查询条件后的user_name为范围查询，所以无法使用到索引的其他列

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5、多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索

引排序。如：再创建一个用户的扩展表user_test_ext，并建立uid的索引。

DROP TABLE IF EXISTS user_test_ext;CREATE TABLE user_test_ext( id int AUTO_INCREMENT PRIMARY KEY,

 uid int NOT NULL,
 u_password VARCHAR(64) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE user_test_ext ADD INDEX 
 index_user_ext(uid);

走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

不走索引排序

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6、总结

本文主要讲了B+Tree树结构的索引规则，不同索引的创建，以及如何正确的创建出高效的索引技巧来尽可能的提高查询速度，当然了

关于索引的使用技巧不单单只有这些，关于索引的更多技巧还需平时不断的积累相关经验。

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