대공개! MySQL 데이터베이스 인덱스

1. 개요

인덱스는 레코드를 빠르게 찾기 위해 스토리지 엔진에서 사용하는 데이터 구조입니다. 시스템의 접근 성능에 있어서, 다음은

MySql 데이터베이스의 인덱스 유형과 보다 합리적이고 효율적인 인덱스 기법을 만드는 방법을 주로 소개합니다.

참고: 여기서 주요 초점은 InnoDB 스토리지 엔진의 B+Tree 인덱스 데이터 구조입니다

2 인덱스의 장점# 🎜🎜#

서버가 스캔해야 하는 데이터의 양을 대폭 줄여 데이터 검색 속도를 향상

서버가 정렬 및 임시 테이블을 방지하도록 도와줍니다#🎜🎜 #

#🎜 🎜#랜덤 I/O를 순차 I/O로 전환 가능

3.인덱스 생성

#🎜 🎜##🎜🎜 #

3.1, 기본 키 인덱스

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

3.2, 고유 인덱스

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

3.3, 일반 인덱스

# 🎜🎜#

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

#🎜 🎜#3.4, 전체 텍스트 색인

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

3.5, 결합 색인

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

#🎜 🎜#

4, B+Tree 인덱스 규칙

테스트 사용자 테이블 만들기

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;

결합된 인덱스 생성: ALTER TABLE user_test ADD INDEX idx_user(user_name, city, age);

4.1, index valid query

#🎜 🎜#4.1.1. 전체 값 일치

전체 값 일치는 위에서 만든 인덱스를 예로 들어 인덱스의 모든 열과 일치하는 것을 의미합니다. where 조건 다음에 (user_name, city, age) 조건이 인 데이터를 동시에 쿼리할 수 있습니다.

참고: where 뒤의 쿼리 조건 순서와는 아무런 관련이 없습니다. 많은 학생들이 오해하기 쉬운 부분입니다.

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

# 🎜🎜#4.1.2. 가장 왼쪽 접두사 일치

가장 왼쪽 접두사 일치는 가장 왼쪽 인덱스 열 일치를 먼저 의미합니다. 쿼리 조건: (user_name ), (user_name, city), (user_name, city, age)

참고: 가장 왼쪽 접두사 쿼리 조건을 만족하는 순서는 인덱스 열의 순서(예: (city, user_name ), (age, city, user_name)

4.1.3, 일치하는 열 접두사 # 🎜🎜#

#🎜 🎜#은 일치하는 열 값의 시작을 나타냅니다. 예: 사용자 이름이 feinik

#🎜로 시작하는 모든 사용자를 쿼리합니다. 🎜#

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

4.1.4. 일치 범위 값

예: 사용자 이름이 feinik으로 시작하는 모든 사용자를 쿼리합니다. , 인덱스

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

#🎜의 첫 번째 열이 사용됩니다 🎜#

4.2. 인덱스 제한

1. 인덱스 열에 인덱스 열이 있는 경우 다음과 같은 인덱스 쿼리를 사용할 수 없습니다. 2. 어디가 가장 왼쪽 인덱스 열인지에 대한 쿼리 조건이 있어도 인덱스 쿼리를 사용할 수 없습니다. 사용자 이름이 feinik

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

3으로 끝나는 사용자인 경우. where 쿼리 조건의 특정 열에서는 오른쪽의 모든 열을

SELECT * FROM user_test WHERE age= 26;

5와 같은 인덱스 최적화를 사용하여 쿼리할 수 없습니다. 🎜🎜#

5.1. 인덱스 열은 표현식의 일부로 사용할 수 없습니다. 그렇지 않으면 인덱스 쿼리를 사용할 수 없습니다.

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

5.2, prefix index

때때로 매우 긴 문자 열을 색인화해야 하는 경우가 있습니다. 증가 인덱스의 저장 공간을 줄이고 인덱스의 효율성을 줄이기 위해 해시 인덱스를 사용하는 전략과 접두사 인덱스를 사용하는 전략이 있습니다. 접두사 인덱스는 문자 열의 처음 n자를 인덱스로 선택하며, 이는 인덱스 공간을 크게 절약하고 인덱싱 효율성을 향상시킬 수 있습니다.

5.2.1. 접두사 색인의 선택성

접두사 색인은 높은 선택 성별을 보장할 만큼 충분히 긴 접두사를 선택해야 합니다. 동시에 너무 길지 않게 다음과 같은 방법으로 적절한 접두사 인덱스의 선택 길이 값을 계산할 수 있습니다.

(1) # 🎜 🎜#

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

참고: 접두사 지수의 선택성 비율은 위 방법을 통해 계산됩니다. 비율이 높을수록 지수가 더 효율적입니다.

(2)

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

#🎜 🎜#

참고: 위의 문을 통해 점차적으로 (1)의 접두사 인덱스에 가장 가까운 선택 비율을 찾은 다음 해당 문자 차단 길이를 사용하여 접두사 인덱스를 수행할 수 있습니다

5.2.2. 접두사 색인 생성

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

5.2.3. 🎜 #Prefix 인덱스는 인덱스를 더 작고 빠르게 만드는 효과적인 방법이지만 MySql은 ORDER BY 및 GROUP BY에 접두사 인덱스를 사용할 수 없고 적용 범위에 접두사 인덱스를 사용할 수 없습니다
#🎜 🎜# 주사.

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树结构的索引规则，不同索引的创建，以及如何正确的创建出高效的索引技巧来尽可能的提高查询速度，当然了

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

위 내용은 대공개! MySQL 데이터베이스 인덱스의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!