MySQL 인덱스 생성 원리 분석 예시

1. 인덱스 생성에 적합

1. 필드 값에는 고유성 제한이 있습니다.

Alibaba 사양에 따르면 비즈니스에서 고유한 특성을 갖는 필드는 결합된 필드라도 고유한 인덱스로 구축되어야 합니다.

예를 들어 학생 테이블의 학생 번호는 고유한 필드입니다. 이 필드에 대한 고유 인덱스를 생성하면 해당 이름이 사용되는 경우 특정 학생의 정보를 빠르게 쿼리할 수 있습니다. 이름을 지정하므로 쿼리 속도가 느려집니다.

2. Where 쿼리 조건으로 자주 사용되는 필드

Select 문의 Where 조건에서 필드가 자주 사용되는 경우, 특히 데이터 양이 많은 경우 해당 필드에 대한 인덱스를 생성해야 합니다. , 일반 인덱스를 만드는 것만으로도 쿼리 효율성을 크게 향상시킬 수 있습니다.

예를 들어, Student_info에 100만 개의 데이터가 있다고 가정하면, Student_id=112322라는 사용자 정보를 쿼리한다고 가정하면, Student_id 필드에 인덱스가 생성되지 않으면 쿼리 결과는 다음과 같습니다.

select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花费211ms

After Student_id에 대한 인덱스를 생성하면 쿼리 결과는 다음과 같습니다:

alter table student_info add index idx_sid(student_id);
select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花费3ms

3. Group by 및 Order by

인덱스 열을 사용하면 특정 순서로 데이터를 저장하거나 검색할 수 있으므로 Group by를 사용할 때 데이터를 그룹화하거나 정렬 기준을 사용하여 데이터를 정렬하려면 그룹화 또는 정렬 필드를 색인화해야 합니다. 정렬할 열이 여러 개인 경우 이러한 열에 결합된 인덱스를 작성할 수 있습니다.

예를 들어, Student_id에 따라 학생들이 선택한 강좌를 그룹화하고, 서로 다른 Student_id와 강좌 수를 표시하고, 100개의 항목을 표시합니다. Student_id에 대한 인덱스를 생성하지 않은 경우 쿼리 결과는 다음과 같습니다.

select student_id,count(*) as num from student_info group by student_id limit 100;#花费2.466s

student_id에 대한 인덱스를 생성한 후 쿼리 결과는 다음과 같습니다.

alter table student_info add index idx_sid(student_id);
select student_id,count(*) as num from student_info group by student_id limit 100;#花费6ms

둘을 모두 포함하는 쿼리 문의 경우 group by 및 order by의 경우에는 Joint 인덱스를 생성하고 "가장 왼쪽 접두사 일치 원칙"을 충족하기 위해 order by 필드 앞에 필드를 group by로 배치하여 인덱스 활용률을 높이는 것이 좋습니다. 동시에 자연 쿼리 효율성도 높아집니다. 8.0 이후 버전에서는 내림차순 인덱스를 지원합니다. order by 이후의 필드가 내림차순으로 되어 있는 경우 직접 내림차순 인덱스를 생성하는 것도 고려해 볼 수 있으며 이는 쿼리 효율성도 향상시킵니다.

4. 업데이트 및 삭제의 where 조건 열

특정 조건에 따라 데이터를 쿼리한 후 업데이트 또는 삭제 작업을 수행합니다. Where 필드에 대해 인덱스가 생성되면 응답이 향상되어 효율성이 향상될 수 있습니다. . 그 이유는 이 레코드를 Where 조건 열을 기준으로 먼저 검색한 후 업데이트하거나 삭제해야 하기 때문입니다. 업데이트 시 업데이트된 필드가 인덱스가 아닌 필드인 경우 인덱스 필드 업데이트에 유지 관리가 필요하지 않기 때문에 효율성 향상이 더욱 분명해집니다.

예를 들어, Student_info 테이블의 name 필드가 sdfasdfas123123이라면, Student_id는 110119로 수정됩니다. name 필드를 인덱싱하지 않은 경우 실행 상황은 다음과 같습니다.

update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花费549ms

인덱스 추가 후 실행 상황

alter table student_info add index idx_name(name);
update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花费2ms

5. 고유 필드는 인덱스를 생성해야 합니다.

특정 필드를 중복 제거해야 하는 경우도 있습니다. 이 필드에 대한 인덱스를 생성하면 쿼리 효율성도 향상됩니다.

예를 들어, 강좌 일정에서 서로 다른 Student_id를 쿼리합니다. Student_id에 대한 인덱스가 생성되지 않은 경우 실행 상황은 다음과 같습니다.

select distinct(student_id) from student_id;#花费2ms

인덱스 생성 후 실행 상황은 다음과 같습니다.

alter table student_info add index idx_sid(student_id);
select distinct(student_id) from student_id;#花费0.1ms

6. 다중 테이블 Join 연결 작업 중 인덱스 생성 시 주의 사항

우선 연결 테이블의 데이터 양은 3개를 초과해서는 안 됩니다. 각 추가 테이블은 중첩 루프를 추가하는 것과 동일하며, 규모가 매우 빠르게 증가하여 쿼리 효율성에 심각한 영향을 미칩니다. 둘째, Where 조건에 대한 인덱스를 생성합니다. 왜냐하면 Where는 데이터 조건에 대한 필터이기 때문입니다. 데이터의 양이 매우 많으면 필터링을 위한 Where 조건이 없으면 매우 무서울 것입니다. 필드를 다시 변경하고 여러 테이블의 유형이 일관되어야 합니다.

예를 들어, Student_id에 대해서만 인덱스를 생성하면 쿼리 결과는 다음과 같습니다.

select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花费176ms

이름 필드에 대한 인덱스를 생성한 후 쿼리 결과는 다음과 같습니다.

alter table student_info add index idx_name(name);
select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花费2ms

7、使用列的类型小的创建索引

这里所说的类型小值意思是该类型表示的数据范围的大小。比如在定义表结构的时候要显示的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，他们占用的存储空间依次递增，能表示的数据范围也是一次递增。如果相对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，例如能使用INT不要使用BIGINT，能使用MEDIUMINT不使用INT，原因如下：

• 数据类型越小，在查询时进行的比较操作越快

• 数据类型越小，索引占用的空间就越少，在一个数据页内就可以存下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以存储更多的数据在数据页中，提高读写效率。

上述对于主键来说很合适，因为在聚簇索引中既存储了数据，也存储了索引，可以很好的减少磁盘I/O；而对于二级索引来说，还需要一次回表操作才能查到完整的数据，也就能加了一次磁盘I/O。

8、使用字符串前缀创建索引

根据Alibaba开发手册，在字符串上建立索引时，必须指定索引长度，没有必要对全字段建立索引。

比如有一张商品表，表中的商品描述字段较长，在描述字段上建立前缀索引如下：

create table product(id int, desc varchar(120) not null);
alter table product add index(desc(12));

区分度的计算可以使用count(distinct left(列名, 索引长度))/count(*)来确定。

9、区分度高的列适合作为索引

列的基数值得时某一列中不重复数据的个数，比如说某个列包含值2，5，3，6，2，7，2，虽然有7条记录，但该列的基数却是5，也就是说，在记录行数一定的情况下，列的基数越大，该列中的值就越分散；列的基数越小，该列中的值就越集中。这里列的基数指标非常重要，直接影响是否能有效利用索引。最好为列的基数大的列建立索引，为基数太小的列建立索引效果反而不好。

可以使用公式select count(distinct col)/count(*) from table 来计算区分度，越接近1区分度越好。

10、使用最频繁的列放到联合索引的左侧

这条就是通常说的最左前缀匹配原则。 通俗来讲就是将Where条件后经常使用的条件字段放在索引的最左边，将使用频率相对低的放到右边。

11、在多个字段都要创建索引的情况下，联合索引由于单值索引

二、不适合创建索引

1、在where中使用不到的字段不要设置索引

通常索引的建立是有代价的，如果建立索引的字段没有出现在where条件（包括group by、order by)中，建议一开始就不要创建索引或将索引删除，因为索引的存在也会占用空间。

2、数据量小的表最好不要使用索引

3、有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如学生表中的性别字段，只有男和女两种值，因此无需建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

4、避免对经常更新的表创建过多的索引

• 频繁更新的字段不一定要创建索引，因为更新数据的时候，索引也要跟着更新，如果索引太多，更新的时候会造成服务器压力，从而影响效率。

• 避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时虽然提高了查询速度，同时也会降低更新表的速度。

5、不建议用无序的值作为索引

例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

6、删除不在使用或很少使用的索引

表中的数据被大量更新或者数据的使用方式被改变后，原有的一些索引可能不会被使用到。DBA应定期找出这些索引并将之删除，从而较少无用索引对更新操作的影响。

7、不要定义冗余或重复的索引

例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

8、删除不在使用或很少使用的索引

表中的数据被大量更新或者数据的使用方式被改变后，原有的一些索引可能不会被使用到。DBA应定期找出这些索引并将之删除，从而较少无用索引对更新操作的影响。

9. 중복되거나 중복된 인덱스를 정의하지 마세요

위 내용은 MySQL 인덱스 생성 원리 분석 예시의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!