Mysql 인덱스가 실패하면 어떻게 해야 합니까? 실패 원인에 대한 간략한 분석

이 글은 모두를 위한 Mysql 인덱스 실패를 기록하고 Mysql 인덱스 실패 원인을 분석하는 글이 여러분에게 도움이 되기를 바랍니다!

이 글에는 Mysql의 Where 조건 쿼리 실행 프로세스, 조인트 인덱스 매칭을 중지하는 범위 쿼리, 테이블 반환 연산 분석, 일반적인 인덱스 실패 시나리오, 추가 분석 및 기타 지식이 포함되어 있습니다. [관련 추천 : mysql 동영상 튜토리얼]

Background

6천만 개의 데이터가 포함된 데이터 테이블에 전체 쿼리가 나타났습니다. sql 문을 복제해 보니 해당 쿼리가 인덱스를 거치지 않고 전체를 거치는 것으로 나타났습니다. 테이블 쿼리를 통해 인덱스 실패 이유를 알아보세요.

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

order_recipient_extend_tab 테이블에는 6천만 개의 데이터가 있습니다. 느린 쿼리의 쿼리 필드에는 start_date, station_id, status가 포함됩니다. 인덱스 설계의 원래 의도에 따르면 실제로 실패하는 인덱스는

joint 인덱스입니다.	필드 1	필드 2	필드 3
idx_date_station_driver	start_date	station_id	driver_id

Where 조건 쿼리 실행 프로세스

Mysql이 Where 조건 쿼리를 어떻게 실행하는지 이해하고, 인덱스 실패 원인에 대해 더 빠르고 명확하게 통찰력을 얻을 수 있습니다. 이 느린 쿼리에서 일치도가 높은 인덱스는 idx_date_station_driver입니다. 이 느린 쿼리에서 where 조건 쿼리의 실행 과정을 분석해 보세요.

Mysql의 조건 추출 규칙은 색인 키(첫 번째 키 및 마지막 키), 색인 필터, 테이블 필터의 세 가지 주요 범주로 요약할 수 있습니다.

색인 키

색인 키는 인덱스 트리에서 이 SQL 쿼리의 범위를 결정하는 데 사용됩니다. 범위에는 시작과 끝이 포함되며, Index First Key는 인덱스 쿼리의 시작 범위를 찾는 데 사용되고, Index Last Key는 인덱스 쿼리의 끝 범위를 찾는 데 사용됩니다.

• Index First Key

  추출 규칙: 인덱스의 첫 번째 필드부터 시작하여 해당 필드가 where 조건에 존재하는지 확인하고 조건이 =, >=인 경우 해당 조건을 Index에 추가합니다. First Key, 인덱스의 다음 필드가 존재하고 조건이 >이면 계속해서 읽고, Index First Key에 해당 조건을 추가한 후, 존재하지 않으면 Index First Key 추출도 종료합니다. Index First Key 추출 추출.

• Index Last Key

  는 Index First Key와 정반대입니다. 추출 규칙은 인덱스의 첫 번째 필드부터 시작하여 where 조건에 존재하는지 확인하고 조건이 =,

인덱스 키 추출 규칙에 따르면 이 느린 쿼리에서 추출된 마지막 인덱스 키는 start_date>'1628442000'이고, 마지막 인덱스 키는 start_date

Index First Key는 인덱스 B+ 트리의 루트 노드에서 시작하여 Index First Key 조건을 사용하고 이진 검색 방법을 사용하여 인덱스의 시작 범위를 찾는 데만 사용됩니다. . Where 쿼리 프로세스 동안 Index First Key는 한 번만 판단됩니다.

Index Last Key는 인덱스의 끝 범위를 찾는 데 사용됩니다. 따라서 시작 범위 이후에 읽혀지는 각 인덱스 레코드에 대해 Index Last Key의 범위를 초과했는지 여부를 확인해야 합니다. 쿼리가 종료됩니다.

Index Filter

Index Key에 의해 결정된 인덱스 범위에서 모든 인덱스 레코드가 쿼리 조건을 만족하는 것은 아닙니다. 예를 들어 Index Last Key 및 Index Last Key 범위에서 모든 인덱스 레코드가 station_id = '1809'를 만족하는 것은 아닙니다. 이때 Index Filter를 사용해야 합니다.

인덱스 필터(index pushdown라고도 함) 는 쿼리 조건을 충족하지 않는 인덱스 쿼리 범위의 레코드를 필터링하는 데 사용됩니다. 인덱스 범위의 각 레코드에 대해 인덱스 필터와 비교해야 하며, 인덱스 필터를 충족하지 않으면 직접 폐기되고 인덱스의 다음 레코드를 계속 읽습니다.

인덱스 필터 추출 규칙: 인덱스의 첫 번째 필드부터 시작하여 where 조건에 존재하는지 확인하고, 존재하고 조건이 =인 경우 첫 번째 필드를 건너뛰고 인덱스의 다음 필드를 계속 확인합니다. 다음 색인 열은 동일한 추출 규칙을 채택합니다(설명: = 조건이 있는 필드는 색인 키에서 필터링되었습니다). 조건이 >=, >,

인덱스 필터의 추출 규칙에 따르면, 이 느린 쿼리에서 추출된 인덱스 필터는 station_id='1809'입니다. Index Key에 의해 결정된 인덱스 쿼리 범위에서 인덱스 레코드를 순회할 때 station_id='1809'를 비교해야 합니다. 이 조건이 충족되지 않으면 바로 손실되고 인덱스의 다음 레코드를 계속해서 읽습니다. .

테이블 필터

테이블 필터는 인덱스로 필터링할 수 없는 데이터를 필터링하는 데 사용됩니다. Secondary Index의 Primary Key를 통해 레코드의 행 전체를 조회한 후 해당 레코드가 Table Filter 조건을 만족하는지 판단하여 조건을 만족하지 않으면 해당 레코드는 사라지고 다음 레코드가 계속 유지된다. 판단. 추출 규칙은 매우 간단합니다. 인덱스 필드에 속하지 않는 모든 쿼리 조건은 테이블 필터로 분류됩니다. Table Filter의 추출 규칙에 따르면 이 쿼리의 Table Filter는 status='2'입니다.

요약 및 보충

인덱스 키는 인덱스 스캔 범위를 결정하는 데 사용됩니다. 인덱스 필터는 인덱스를 필터링하는 데 사용됩니다. 테이블을 반환한 후 MySQL 서버에서 필터링해야 합니다.

Index Key 및 Index Filter는 InnoDB 스토리지 계층에서 발생하고 Table Filter는 Mysql Server 계층에서 발생합니다.

MySQL5.6 이전에는 Index Filter와 Table Filter의 구분이 없었습니다. Index First Key와 Index Last Key 범위 내의 모든 인덱스 레코드를 테이블로 반환하여 전체 레코드를 읽은 후 MySQL 서버로 반환했습니다. 필터링을 위한 레이어입니다.

MySQL 5.6 이상에서는 인덱스 필터가 테이블 필터와 분리되어 필터링을 위해 InnoDB의 스토리지 엔진 계층으로 떨어지므로 테이블 반환과 레코드를 MySQL 서버 계층으로 반환하는 상호 작용 오버헤드가 줄어들고 성능이 향상됩니다. SQL의 실행 효율성.

인덱스 실패 원인 분석

첫 번째는 count()입니다. 이때 와일드카드 *는 최적화 후 모든 열을 확장하지 않습니다. 실제로는 모든 열이 무시되고 행 수가 늘어납니다. 직접 계산했습니다. 따라서 행 개수만 수집하고 싶다면 count()를 사용하는 것이 가장 좋습니다.

다음으로 where 문을 분석합니다. 이 느린 쿼리는 위의 where 조건 쿼리의 실행 과정에 따라 보조 인덱스 idx_date_station_driver를 사용한다고 가정합니다. 느린 쿼리의 Index First Key는 start_date>'1628442000'이고 Index Last Key는 is: start_dateidx_date_station_driver，按照上面where条件查询的执行过程，该慢查询的Index First Key为start_date>'1628442000'，Index Last Key为： start_date

提取Index First Key后在索引B+树上定位索引起始范围就是索引匹配的过程，在索引B+树上使用二分搜索方法快速定位符合查询条件的起始叶子节点。通过上文Where条件查询执行过程，我们知道该慢查询的where条件（start_date>'1628442000' and start_date，只匹配了索引<code>idx_date_station_driver(start_date, station_id, driver_id)的第一个字段，即只匹配了idx_date_station_driver(start_date)，station_id='1809‘精确查询并没有作用到匹配索引上，而是在Index Filter即索引下推过程中发挥了作用。实际上这里是因为范围查询使联合索引停止匹配。

范围查询导致联合索引停止匹配

为什么范围查询会使联合索引停止匹配？这里涉及到最左前缀匹配原理。假设建立一个联合索引 index(a, b)，会先对a进行排序，在a相等的情况下对b进行排序，如下图所示。在该索引树上，a是全局有序的，而b则处于全局无序、局部有序状态。从全局来看，b的值为1、2、1、4、1、2，只有 b=2 查询条件无法直接使用该索引；从局部来看，当a的值确定时，b则是有序状态，a=2 && b=4Index First Key를 추출한 후, 인덱스 B+ 트리에서 인덱스의 시작 범위를 찾는 것이 인덱스 매칭 과정입니다

. 인덱스 B+ 트리에서 이진 검색 방법을 사용하여 해당 인덱스에 맞는 시작 리프 노드를 빠르게 찾습니다. 쿼리 조건. 위의 Where 조건 쿼리 실행 과정을 통해 느린 쿼리 (start_date>'1628442000' and start_date의 where 조건을 알 수 있습니다. , 인덱스 <code>idx_date_station_driver(start_date, station_id,driver_id)의 첫 번째 필드만 일치합니다. 즉, idx_date_station_driver(start_date)만 일치합니다. station_id=의 정확한 쿼리입니다. '1809'는 일치하는 인덱스에 대해서는 동작하지 않지만, Index Filter, 즉 인덱스 푸시다운 과정에서 역할을 합니다. 여기서 실제로 일어나는 일은

범위 쿼리로 인해 통합 인덱스 일치가 중단된다는 것입니다 .

범위 쿼리로 인해 결합 인덱스 일치가 중지됨

범위 쿼리로 인해 결합 인덱스 일치가 중지되는 이유는 무엇인가요? 여기에는 가장 왼쪽 접두사 일치 원칙이 포함됩니다. 결합지수 인덱스(a, b)가 설정되었다고 가정하면 아래 그림과 같이 a가 먼저 정렬되고, a가 같으면 b가 정렬된다. 이 인덱스 트리에서 a는 전역적으로 정렬된 반면, b는 전역적으로 정렬되지 않고 로컬로 정렬된 상태입니다. 전역 관점에서 b 값은 1, 2, 1, 4, 1, 2이며 b=2 쿼리 조건만 로컬 관점에서 이 인덱스를 직접 사용할 수 없습니다. a가 결정되면 b가 정렬된 상태일 때 a=2 && b=4는 이 인덱스를 사용할 수 있습니다. 따라서 범위 쿼리로 인해 조인트 인덱스의 일치가 중단되는 근본적인 이유는 인덱스 트리에서 첫 번째가 아닌 필드의 정렬 상태가 이전 필드의 동일성에 의존하고 범위 쿼리가 인덱스 트리의 로컬 정렬 상태를 파괴하기 때문입니다. 다음 인덱스 필드로 인해 인덱스 일치가 중단됩니다. 쿼리 조건데이터 볼륨비율모든 데이터6,367만100%start_timestamp_of_date> ;'1628442000' 및 start_timestamp_of_date1,742만 27.35%station_id='1809'

	범위 쿼리는 조인트 인덱스의 일치를 중지하고 인덱스가 일치할 때 station_id가 '1809'와 같지 않은 데이터를 필터링할 수 없으므로 인덱스의 Mysql의 검색 범위 Index First Key 및 Index Last Key가 start_timestamp_of_date에 의해 완전히 결정됩니다. 시간이 결정합니다. start_timestamp_of_date 범위 쿼리는 데이터 볼륨의 73%를 필터링할 수 있는 반면 station_id='1809' 정확한 쿼리는 데이터 볼륨의 99%를 필터링할 수 있습니다.

🎜80,000🎜🎜0.16%🎜🎜🎜🎜

테이블 반환 작업의 오버헤드

상태 필드가 인덱스 idx_date_station_driver 필드에 없기 때문에 인덱스로 필터링된 데이터를 쿼리하려면 테이블을 반환해야 하며, 데이터가 MySQL 서비스 계층의 쿼리 조건을 충족합니다. idx_date_station_driver字段上，所以需要回表查询索引过滤的数据，在Mysql服务层判数据是否符合查询条件。

Mysql的优化器在执行sql语句时会先估算走匹配度高的索引的开销，如果走索引的开销比查全表还大，那么Mysql会选择全表扫描。这个结论可能反常识，在我们印象中索引就是用来提高查询效率的。这里主要涉及两个因素：

• 当查询条件或查找的字段不在二级索引的字段上时，会执行回表操作，会走：二级索引+主键索引。

• 磁盘随机I/O的性能低于顺序I/O。回表查询在主键索引上是随机I/O，全表扫描在主键索引上是顺序I/O。

做实验分析回表操作的开销是否是索引失效的直接原因？

去除status='0'查询条件，explain查看该查询是否使用到了索引idx_date_station_driver

Mysql의 옵티마이저는 SQL 문을 실행할 때 먼저 인덱싱 비용을 높은 매칭 수준으로 추정합니다. 인덱싱 비용이 전체 테이블 검색보다 클 경우 Mysql은 전체 테이블 스캔을 선택합니다. 이 결론은 직관적이지 않을 수 있습니다. 인덱스는 쿼리 효율성을 향상시키는 데 사용됩니다. 여기에는 주로 두 가지 요소가 관련됩니다.

쿼리 조건 또는 검색 중인 필드가 보조 인덱스의 필드에 없으면 보조 인덱스 + 기본 키 인덱스의 테이블 반환 작업이 수행됩니다.

디스크 랜덤 I/O의 성능은 순차 I/O보다 낮습니다. 테이블 반환 쿼리는 기본 키 인덱스에 대한 무작위 I/O이고, 전체 테이블 스캔은 기본 키 인덱스에 대한 순차 I/O입니다.

테이블 반환 작업 비용이 인덱스 실패의 직접적인 원인인지 실험하고 분석합니까?

status='0' 쿼리 조건을 제거하고 쿼리가 idx_date_station_driver 인덱스를 사용하는지 설명하세요. 결과는 아래 그림과 같습니다. 테이블 반환 작업의 오버헤드가 줄어들고 인덱스가 무효화되지 않습니다.

요약

위의 분석과 종합하면 인덱스 실패 원인을 요약하면, 범위 쿼리로 인해 공동 인덱스의 매칭이 중단되고, 인덱스로 매칭 및 필터링된 데이터가 부족하여 Mysql 최적화 프로그램은 Table Filter의 테이블 반환 작업 비용이 전체 Table 쿼리보다 크다고 추정하여 전체 테이블 쿼리를 선택했습니다. 조인트 인덱스의 매칭을 중단시키는 범위 쿼리가 인덱스 실패의 원인이며, 테이블 반환 작업 비용이 인덱스 실패의 직접적인 원인입니다.

인덱스 최적화

느린 쿼리 인덱스 실패의 원인은 범위 쿼리로 인해 결합 인덱스가 일치하지 않는다는 것입니다. 범위 쿼리의 필드를 정확한 쿼리의 필드 뒤로 조정하기만 하면 됩니다. 즉,

공동 인덱스
• idx_date_station_driver(start_date, station_id,driver_id)

  가

  idx_station_date_driver(station_id, start_date,driver_id)
로 수정되었습니다. 최적화된 결과는 아래 그림과 같습니다.

• Extension

• 인덱스 오류의 일반적인 시나리오

• 가장 왼쪽 접두사 일치 원칙을 위반합니다. 예를 들어 인덱스 index(a,b)가 있는데 쿼리 조건에는 b 필드만 있습니다.

• 계산, 함수, 유형 변환 등을 포함하여 인덱스 열에 대한 모든 작업을 수행합니다.

• Range 쿼리로 인해 통합 인덱스 일치가 중지됩니다.

• select* 사용을 줄이세요. 불필요한 테이블 반환 작업 오버헤드를 방지하려면 포함 인덱스를 사용해 보세요.

• 같지 않음(!=, )을 사용하여 사용하거나 연산합니다.

작은따옴표가 없는 문자열 인덱스는 유효하지 않습니다.

like는 와일드카드 문자 '%abc'로 시작합니다. 'abc%'와 같이 색인을 생성할 수 있습니다.

order by는 가장 왼쪽 일치 원칙을 위반하고 비인덱스 필드 정렬을 포함하므로 파일 정렬이 발생합니다. 느린 쿼리 분석은 mysql explain 문과 분리될 수 없습니다. 주로 Type과 Extra의 두 가지 필드에 중점을 둡니다. Type은 데이터에 액세스하는 방법을 나타내고 Extra는 데이터를 필터링하고 구성하는 방법을 나타냅니다. 쉽게 검색할 수 있도록 여기에 나열됩니다. TypeExtraALL전체 테이블 스캔ql 서비스 레이어 사용index인덱스 트리 전체 스캔 where를 사용하면 스토리지 엔진 계층에서 데이터를 가져오고 where 쿼리 조건을 사용하여 Mysql 서비스 계층의 데이터를 필터링합니다. range인덱스 트리 범위 스캔Where 사용; 인덱스 사용인덱스 범위 스캔. 인덱스 스캔은 전체 테이블 스캔과 유사하지만 서로 다른 수준에서 발생합니다. ref고유 인덱스의 고유하지 않은 인덱스 및 고유하지 않은 접두사와 같은 고유하지 않은 인덱스 스캔인덱스 조건 사용인덱스 푸시다운을 사용하여 쿼리 인덱스 필드를 최대한 활용하여 스토리지 엔진 레이어eq_ref고유 인덱스, 기본 키 인덱스와 같은 고유 인덱스 스캔임시 사용임시 테이블은 쿼리 정렬 및 그룹화에 사용되는 결과를 저장합니다const쿼리를 상수로 변환 파일 정렬 사용

	group by는 가장 왼쪽 일치 원칙을 위반하고 인덱스가 아닌 필드 그룹화를 포함하므로 임시 테이블이 생성됩니다.		Explain 분석

파일 정렬, 정렬용

🎜NULL🎜🎜테이블이나 인덱스에 액세스할 필요 없음🎜🎜NULL🎜🎜테이블로 돌아가기🎜🎜🎜🎜🎜더 많은 프로그래밍 관련 지식을 보려면 다음을 방문하세요. 🎜소개 프로그래밍🎜까지! ! 🎜

위 내용은 Mysql 인덱스가 실패하면 어떻게 해야 합니까? 실패 원인에 대한 간략한 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!