mysql의 접두사 인덱스 란 무엇입니까?

mysql에서 접두사 인덱스는 텍스트의 처음 몇 문자를 인덱스하는 특수 인덱스 유형입니다. 이 인덱스 유형은 인덱스 크기를 어느 정도 줄일 수 있으며 일부 특정 쿼리 작업을 보다 효율적으로 처리할 수 있습니다. 일반적으로 짧은 인덱스는 인덱스의 크기를 줄일 수 있으므로 인덱스 길이는 최대한 짧아야 하지만, 인덱스 길이가 너무 짧으면 인덱스 실패가 발생하여 쿼리 효율성이 저하될 수 있으므로 접두사 인덱스를 사용할 때; 이렇게 하려면 특정 상황에 따라 적절한 인덱스 길이를 선택해야 합니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

1. 프리픽스 인덱스란 무엇인가요?

MySQL은 데이터 저장 및 검색에 있어 효율성과 유연성이 뛰어나 널리 사용되는 관계형 데이터베이스 관리 시스템입니다. MySQL에서 인덱스는 쿼리 작업 속도를 높이는 데 사용되는 데이터 구조로, 쿼리 속도와 효율성을 크게 향상시킬 수 있습니다. 접두사 인덱스는 일부 특정 쿼리 작업을 보다 효율적으로 처리할 수 있는 MySQL의 특수 인덱스 유형입니다.

이른바 접두사 색인은 직설적으로 말하면 텍스트의 처음 몇 글자를 색인화하는 것입니다(구체적으로 이러한 문자는 색인 생성 시 지정됩니다). 인덱스를 구축하는 데 사용됩니다. 이것이 구축되는 방식입니다. 인덱스는 더 작고 쿼리 효율성은 더 빠릅니다.

접두사 인덱싱은 전체 문자열이 아닌 문자열의 접두사 부분만 인덱싱하는 것을 의미합니다. 이 인덱스 유형은 인덱스의 크기를 어느 정도 줄일 수 있습니다. 예를 들어, 천만 개의 문자열이 포함된 테이블이 있는 경우 각 문자열의 길이는 100자입니다. 전체 문자열을 인덱스로 사용하면 인덱스 크기 매우 크고 쿼리 효율성이 매우 낮습니다. 그러나 문자열의 처음 10자만 색인화하면 색인 크기가 크게 줄어들고 쿼리 효율성이 크게 향상됩니다.

MySQL의 접두사 인덱스가 쿼리 중에 내부적으로 일치를 자동으로 완료하고 Left 함수를 사용할 필요가 없다는 점을 제외하면 Oracle의 필드에 Left 함수를 사용하여 함수 인덱스를 만드는 것과 다소 유사합니다.

MySQL에서 접두사 인덱스를 사용하려면 인덱스 길이를 지정해야 합니다. 일반적으로 인덱스 길이가 짧으면 인덱스 크기가 줄어들 수 있으므로 인덱스 길이는 최대한 짧아야 합니다. 그러나 인덱스 길이가 너무 짧으면 인덱스 실패가 발생하여 쿼리 효율성이 저하될 수 있습니다. 따라서 접두사 인덱스를 사용할 때는 특정 상황에 따라 적절한 인덱스 길이를 선택해야 합니다.

2. 접두어 인덱스를 사용하는 이유

어떤 학생들은 전체 필드를 인덱스하지 않느냐고 묻습니다.

일반적으로 특정 필드의 데이터 양이 너무 많고 쿼리가 매우 빈번한 경우 접두사 인덱스를 사용하면 인덱스 파일의 크기를 효과적으로 줄여 각 인덱스 페이지에 더 많은 인덱스 값을 저장할 수 있습니다. 인덱스 쿼리 속도.

예를 들어 일부 고객 매장 이름은 매우 길고 일부는 매우 짧습니다. 인덱스가 전체 적용 범위로 완전히 구축되면 인덱스의 저장 공간이 매우 커질 수 있습니다. 인덱스가 많이 생성되면 인덱스의 저장 공간이 데이터 테이블의 저장 공간보다 훨씬 크기 때문에 긴 텍스트가 있는 이 필드의 경우 처음 몇 글자를 가로채서 인덱스를 만들 수 있습니다. 어느 정도 데이터 쿼리 효율성 요구 사항을 충족할 수 있을 뿐만 아니라 인덱스 저장 공간도 절약할 수 있습니다. 그러나 반면에 접두사 인덱스에도 단점이 있습니다. MySQL에서는 접두사 인덱스를 ORDER BY 및 GROUP BY에 사용할 수 없으며 문자열 자체가 상대적으로 길 수 있는 경우에는 사용할 수 없습니다. 처음 몇 글자는 정확히 동일합니다. 현재로서는 접두사 색인의 장점이 더 이상 명확하지 않으며 접두사 색인을 만들 필요가 없습니다.

이것은 인덱스의

선택성

개념으로 다시 돌아옵니다! 데이터베이스 테이블 인덱스의 선택성에 대해서는 별도의 기사에서 설명하겠습니다. 한 가지만 기억하면 됩니다.

선택성이 높은 인덱스를 사용하면 쿼리 효율성이 높아집니다

. 검색 시 검색을 수행하는 MySQL. 더 많은 행을 필터링하고 데이터를 더 빠르게 쿼리합니다!

특정 필드 내용의 처음 몇 자리 구분이 매우 쉬운 경우, 이때 접두사 인덱스를 사용하면 쿼리 성능 및 공간 저장 측면에서 매우 높은 비용 성능을 달성할 수 있습니다

. 그럼 질문은 접두사 색인을 어떻게 생성하느냐는 것입니다.

3. 프리픽스 인덱스 생성 방법

프리픽스 인덱스를 생성하는 방법은 다음과 같이 매우 간단합니다!

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

매개변수

는 일반적으로 다음과 같은 방법으로 확인됩니다.

prefix_length첫 번째 단계는 필드 전체 열의 판별을 계산하는 것입니다.

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

두 번째 단계는 전체 열의 판별과 가장 유사한 접두사 길이를 계산하는 것입니다

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增！

四、使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

【相关推荐：mysql视频教程】

위 내용은 mysql의 접두사 인덱스 란 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!