MySQL의 인덱스에 대한 심층적인 이해(용도, 분류, 매칭 방법)

이 기사는 MySQL의 인덱스에 대한 심층적인 이해를 제공하고 인덱스의 장점, 용도, 분류, 기술 용어 및 매칭 방법을 소개합니다.

고급 개발을 위해서는 복잡한 SQL을 작성해야 하는 경우가 많기 때문에 비효율적인 SQL 작성을 방지하려면 인덱싱에 대한 기본 지식을 이해해야 합니다. 이러한 기본 지식을 통해 우리는 보다 효율적인 SQL을 작성할 수 있습니다. [관련 권장사항: mysql 동영상 튜토리얼]

01 인덱스의 장점

• 서버가 스캔해야 하는 데이터의 양, 즉 IO의 양을 대폭 줄입니다
• 서버가 피하도록 도와주세요 정렬 및 임시 테이블(파일 정렬을 피하고 대신 인덱스 정렬 사용)
• 무작위 IO를 순차 IO로 전환

02 인덱스의 유용성

• where 절과 일치하는 행을 빠르게 찾기
• If 여러 인덱스 중에서 선택할 수 있으며, mysql은 일반적으로 가장 적은 행을 찾는 인덱스를 사용합니다.
• 테이블에 다중 열 인덱스가 있는 경우 최적화 프로그램은 인덱스의 가장 왼쪽 접두사를 사용하여 행을 찾을 수 있습니다.
• 테이블 조인이고, 행 데이터는 다른 테이블에서 검색됩니다.
• 특정 인덱스 열의 최소 및 최대 값을 찾습니다.
• 인덱스의 가장 왼쪽 접두사를 사용하여 정렬 또는 그룹화를 수행할 수 있는 경우 테이블을 정렬하고 그룹화합니다.
• 어떤 경우에는 데이터 행 찾기 없이 데이터 값을 검색하도록 쿼리를 최적화할 수 있습니다

03 인덱스 분류

데이터베이스에서 생성된 기본 인덱스는 고유 키용입니다

• 기본 키 인덱스 (고유하고 null이 아님)
• 고유 인덱스(고유하고 null일 수 있음)
• 일반 인덱스(일반 필드의 인덱스)
• 전체 텍스트 인덱스(일반적으로 varchar, char, 텍스트 유형을 기반으로 구축되지만 거의 사용되지 않음)
• 결합 인덱스(여러 단어로 구성된 인덱스)

04 indexed 기술 용어

1. 테이블 반환

name 필드는 B+ 트리에서 기본 키를 찾습니다. 이름 열의 기본 키의 B+ 트리에서 최종 데이터를 찾습니다. 이것이 테이블 반환입니다. (기본키 인덱스의 리프 노드는 컬럼의 모든 데이터를 저장하지만 일반적으로 모든 리프 노드는 해당 기본키 ID를 저장한다.)

그림과 같이 사용 테이블에서 이름으로 설정된 sql 인덱스 구조는 다음과 같다. select * from use where name='sun' 먼저 비기본키 인덱스 이름을 통해 sun에 해당하는 기본키 Id=2를 찾은 후 기본키에서 전체 행 데이터를 찾습니다. id=2를 통한 키 인덱스가 반환되는 표면입니다. select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，并返回，这个就是回表。

2. 覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

如上图name索引，sql是 select id,name from user where name ="1" ，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name="纪先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

扩展；

如果是下面两个sql怎么建立索引

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

由于最左匹配原则：只需要建立一个组合索引age+name即可

如果是下面三个sql呢

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

建立name+age和age索引，或者建立age+name和name索引,看着两个都可以。

其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，name+age和age+name这两个占用是一样的，但是name和age单独比较，肯定age占用空间更少，name更长（索引越大，IO次数可能更多）

注意！注意！注意！：

在看很多文章的时候，经常看到一些对于最左匹配错误的举例：

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="纪先生"

2. Covering index

필수 필드는 다시 쿼리하기 위해 테이블로 돌아갈 필요 없이 기본 키가 아닌 인덱스에서 쿼리할 수 있으며, 이를 Covering 인덱스라고 합니다.

🎜🎜위의 이름 인덱스에 표시된 것처럼 SQL은 select id,name from user where name = "1"입니다. id의 값은 이미 다음의 비기본 키 인덱스에서 사용할 수 있습니다. 첫 번째 단계이므로 ID를 기반으로 할 필요가 없습니다. 행 데이터를 쿼리하려면 기본 키 인덱스로 이동하세요. 🎜🎜🎜🎜3. 왼쪽 일치 🎜🎜🎜🎜🎜 결합된 인덱스에서는 왼쪽이 먼저 일치된 후 역방향 일치가 계속됩니다. 예를 들어 사용자 테이블에 이름+나이로 구성된 결합 인덱스가 있습니다. , name="Mr. Ji" 및 age = 18인 사용자에서 *를 선택하면 가장 왼쪽 일치 항목과 일치하며 인덱스로 사용할 수 있습니다. 단, 연령 = 18세를 준수하지 않는 사용자로부터 *를 선택하면 이 지수를 사용할 수 없습니다. 🎜🎜🎜확장; 🎜🎜다음 두 가지 경우 인덱스 생성 방법 sql🎜

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

로그인 후 복사

로그인 후 복사

🎜가장 왼쪽 일치 원칙으로 인해: 🎜연령+이름을 결합한 인덱스만 생성하면 됩니다🎜🎜🎜다음과 같다면? three sql🎜

mysql> alter table staff add index index_n1(first_name,last_name,username);

로그인 후 복사

로그인 후 복사

🎜 이름+나이 및 나이 인덱스를 생성하거나 나이+이름 및 이름 인덱스를 생성하면 둘 다 괜찮습니다. 🎜🎜실제로는 🎜이름+나이와 나이가 더 좋습니다🎜. 인덱스도 지속적으로 저장해야 하기 때문에 이름+나이와 나이+이름은 같은 양을 차지하지만 이름과 나이를 비교하면 됩니다. 별도로 나이는 확실히 공간을 덜 차지하고 이름은 길어집니다(인덱스가 클수록 IO 횟수가 많아짐) 🎜🎜🎜주의! 알아채다! 알아채다! ：🎜🎜🎜🎜많은 기사를 읽을 때 가장 왼쪽 일치 오류의 몇 가지 예를 자주 봅니다. 🎜🎜색인이 이름+나이의 결합 색인인 경우 SQL은 select * from user where age = 18 and name ="Mr. Ji"많은 사람들이 이것을 색인화할 수 없다고 생각하지만 실제로는 색인화할 수 있습니다. MySQL의 옵티마이저는 조정 순서를 최적화하여 name="Mr. Ji" 및 age = 18🎜🎜🎜🎜🎜4로 조정합니다. Index pushdown🎜🎜🎜🎜🎜결합된 인덱스에서 인덱스 정보를 최대한 사용하여 Number를 최소화합니다. 테이블로 돌아가야 할 횟수🎜

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引时非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。

更多编程相关知识，请访问：编程视频！！

위 내용은 MySQL의 인덱스에 대한 심층적인 이해(용도, 분류, 매칭 방법)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!