MySQL 인덱스 원리를 배우는 방법은 무엇입니까? 나만의 색인 생성 경험 요약-MySQL 튜토리얼-php.cn

인덱스란 무엇인가요? Baidu Encyclopedia는 이를 다음과 같이 설명합니다. 인덱스는 테이블의 데이터 행 검색 속도를 높이기 위해 생성된 분산된 데이터 결과입니다. 이는 데이터 페이지의 각 행 이외의 인덱스 페이지로 구성됩니다. 인덱스 페이지에는 물리적 데이터 검색 속도를 높이기 위한 논리적 포인터가 포함되어 있습니다. 이 글에서는 MySQL 인덱스 원리를 배우는 방법을 자세히 설명합니다.

추상: MySQL 인덱스에 대해 이야기해 보겠습니다. 인덱스란 무엇입니까? Baidu Encyclopedia는 이를 다음과 같이 설명합니다. 인덱스는 테이블의 데이터 행 검색 속도를 높이기 위해 생성된 분산된 데이터 결과입니다. 이는 데이터 페이지 이외의 인덱스 페이지로 구성됩니다. 인덱스 페이지에는 물리적 데이터 검색 속도를 높이기 위한 논리적 포인터가 포함되어 있습니다. 실제로 모든 사람은 인덱싱의 개념을 알고 있으며 인덱싱이 쿼리 효율성을 향상시킬 수 있다는 것을 알고 있습니다. 그러나 대부분의 어린이 신발에는 인덱스를 구축하는 방법과 관련하여 다음과 같은 일반적인 질문이 있습니다. 오해: 새 테이블을 생성할 때 인덱스를 생성할 필요가 없으며 나중에 인덱스가 추가됩니다. 단순 SQL에는 인덱스가 필요하지 않으며 공동 쿼리만 필요합니다. 인덱스의 순서는 where 조건 뒤의 필드 순서이며, 상태, 성별 및 기타 필드에도 새 인덱스가 생성됩니다.

MySQL 인덱스에 대해 함께 이야기해 볼까요?

인덱스란 무엇인가요?

Baidu Encyclopedia에서는 다음과 같이 설명합니다.

인덱스는 테이블의 데이터 행 검색 속도를 높이기 위해 생성된 분산된 데이터 결과입니다. 테이블용으로 작성된 페이지 구성입니다. , 인덱스 페이지의 각 행에는 물리적 데이터 검색 속도를 높이기 위한 논리적 포인터가 포함되어 있습니다

사실 모든 사람이 인덱싱의 개념에 대해 매우 명확하고 인덱싱이 쿼리 효율성을 향상시킬 수 있다는 것도 알고 있지만 대부분의 어린이는 어떻게 인덱싱을 수행합니까? 빌드 인덱스? 빌드할 필드에 대해 다음과 같은 일반적인 오해가 있습니다.

새 테이블을 생성할 때 인덱스를 생성할 필요가 없으며 인덱스는 나중에 추가됩니다.

where 조건 뒤의 필드는 모두 인덱스됩니다.

간단한 SQL에는 인덱스가 필요하지 않으며, 조인트 쿼리에만 인덱스가 필요합니다.

조인트 인덱스의 순서는 where 조건 뒤의 필드 순서입니다.

새 인덱스는 다음과 같이 작은 차이가 있는 필드에도 생성됩니다. 상태, 성별 및 기타 필드로.

색인 구별

위의 문제를 이야기하기 전에 먼저 차별이라는 또 다른 개념을 살펴보겠습니다.

Distinction: 데이터베이스의 필드가 중복되지 않는 비율을 나타냅니다.

Distinction은 새 인덱스를 생성할 때 매우 중요한 참조 값을 갖습니다. MySQL에서 차별화를 위한 계산 규칙은 다음과 같습니다.

중복 제거 후. fields 전체 테이블의 총 레코드 수와 총 레코드 수의 몫입니다.

예:

select count(distinct(name))/count(*) from t_base_user;

결과는 다음과 같습니다:

count(distinct(name))/count(* )
1.0000

구별의 최대값은 1.000, 최소값은 0.0000입니다. 구별의 값이 클수록, 즉 데이터 비중복률이 높을수록 기본 키에 대한 새 인덱스 효과가 더 좋습니다. 고유 키는 1.0000이 가장 높습니다. 지위, 성별 등의 항목에 대한 구별값이 가장 작습니다. (이것은 데이터의 양에 따라 다릅니다. 데이터의 양이 적다면 판별력이 상당히 높습니다. 데이터의 양이 많을 경우 판별력은 기본적으로 0.0000입니다. 즉, 이 필드에 인덱스를 추가한 후입니다. , 효과가 좋지 않습니다. )

주의할 점: 테이블에 레코드가 없으면 판별 계산 결과가 null 값이 됩니다. 0.0000-1.0000.

인덱스 구축 방법

(1): 차별
인덱스 구축 시 반드시 필드의 차별성을 먼저 계산할 것을 강력히 권장하는 이유는 다음과 같습니다.

1. 단일 열 인덱스
를 확인할 수 있습니다. 분야의 차별점, 구분의 크기를 기준으로 이 분야의 새로운 지표가 유효한지, 얼마나 효과적인지 대략적으로 알 수도 있습니다. 차별이 클수록 색인 효과는 더욱 분명해집니다.

2. 다중 열 인덱스(결합 인덱스)
실제로 다중 열 인덱스에는 필드 순서의 문제도 있습니다. 일반적으로 차별화가 높은 항목이 먼저 배치되므로 결합 인덱스가 됩니다. 예를 들면 다음과 같습니다.

select * from t_base_user where name="" and status=1;

위 명령문과 마찬가지로 공동 인덱스가 구축되면 다음과 같아야 합니다.

alter table t_base_user add index idx_name_status(name, status);

그리고 그렇지 않은 경우:

alter table t_base_user add index idx_status_name(status,name);

(2) 가장 왼쪽 접두사 일치 원칙
MySQL은 다음을 만날 때까지 오른쪽으로 일치를 계속합니다. 범위 쿼리(>, <, between, like )를 사용하여 일치를 중지합니다(예:

select * from t_base_user where type="10" and Created_at<"2017-11-03" and status=1, (이 문은 는 데모용입니다)

위 명령문에서 status

select * from t_base_user where type=10 and status=1 and Created_at<" 2017-11-03"

상태지수를 사용할 수 있습니다.

(3) 함수 연산
인덱스 열에 함수 연산을 수행하지 마십시오. 그렇지 않으면 인덱스가 유효하지 않게 됩니다. b+ 트리는 모든 필드 값을 데이터 테이블에 저장하기 때문에 검색할 때 비교할 모든 요소에 함수를 적용해야 하는데 이는 분명히 비용이 너무 많이 듭니다.

(4) 확장 먼저
확장 먼저, 새 인덱스를 만들지 말고, 기존 인덱스를 수정해보세요. 다음과 같습니다.

select * from t_base_user where name="andyqian" and email="andytohome"

idx_name 인덱스는 이미 t_base_user 테이블에 존재합니다. idx_name_email 인덱스를 추가해야 하는 경우 새로운 인덱스를 생성하는 대신 idx_name 인덱스를 사용하세요.

오해 수정

위에서 새로운 인덱스를 만드는 방법을 언급했습니다. 이제 첫 번째 단계에서 오해에 답할 수 있습니다.

오해 1: 새 테이블을 생성할 때 인덱스를 생성할 필요가 없으며 인덱스는 나중에 추가됩니다.

답변: 문제가 발생할 때까지 기다리지 않고 처음부터 인덱스 생성을 고려하는 것이 좋은 데이터 테이블 디자인입니다. 나중에 비즈니스 사용에 영향을 미칠 수 있으므로 상황을 저장하기 위해 새 인덱스가 생성되었으며 후속 인덱스 생성 비용은 상대적으로 높습니다. (생산사고가 뿌리내리고 싹트게 할 기회를 주기 위함입니다)

오해 2: where 조건 뒤의 필드는 모두 인덱싱되어 있습니다

답변: 이런 오해는 비교적 흔한데, where 조건 뒤의 필드는 굳이 그럴 필요가 없습니다. 인덱싱을 너무 많이 하면 인덱스 파일이 급격히 증가하여 원하는 효과를 얻을 수 없습니다. 자세한 내용은 위의 인덱스 생성 부분을 참고하세요.

오해 3: 단순 SQL에는 인덱싱이 필요하지 않으며, 공동 쿼리에는 인덱싱이 필요합니다.

답변: 요즘 인터넷 회사, 특히 B/S 아키텍처에서는 비즈니스 로직이 코드에서 제거되어 있으므로 주의 깊게 설명해야 합니다. 논리 계층에서는 실제로 몇 가지 연결 쿼리와 더 많은 단일 테이블 작업이 포함된 간단한 SQL입니다(C/S 아키텍처의 SQL 수준에서 작성된 논리가 많이 있습니다). 간단하다?

오해 4: 결합 인덱스의 순서는 where 조건 뒤의 필드 순서입니다.

답: 방금 말했듯이 결합 인덱스의 순서는 가장 왼쪽 접두사 원칙과 차별화 정도에 따라 결정됩니다. where 조건 이후의 필드와는 다릅니다. 순서는 중요하지 않습니다.

오해 5: 차별화 정도가 작은 필드에 인덱스를 생성합니다

답변: 차별화 정도가 작은 필드에 새 인덱스를 생성하는 것은 기본적으로 효과가 없으며, 인덱스 파일 수도 많이 늘어납니다. 당신은 그것이 이득을 얻을 가치가 없다고 생각합니까?

지수가 중요한가요?

위 내용은 MySQL 인덱스의 개념과 새로운 인덱스 생성 시 몇 가지 팁을 소개한 것입니다. 이런 이론적으로, 사용되지 않거나 비교적 드물게 사용되는 아동용 신발에 대해서는 현재 시점에서 인덱싱의 중요성이 그다지 직관적이지 않을 수 있습니다. 그래서 제가 인덱싱에서 겪은 손실과 함정에 대해 이야기하겠습니다! 인덱스를 구축하지 않는 것도 흔한 문제입니다!

0. 쿼리 속도가 느려집니다.
이 문제는 인덱싱이 없는 경우 흔히 발생하는 문제입니다. (여기에는 암시적 유형 변환 등 많은 세부 정보도 있습니다.)

1. 서비스 시간 초과가 발생합니다.

시나리오:
특정 시간에 발생합니다. 온라인에 접속할 때 서비스 제공자로서 비즈니스 당사자에게 서비스를 제공합니다. 처음에는 단순한 서비스인줄 알았는데, 테스트가 완료되어 오늘 드디어 집에 일찍 갈 수 있게 되어 아직도 은근히 기쁩니다!

설명:
실제로 온라인이 되자마자 비즈니스 당사자가 프로덕션 환경에서 통화를 요청했을 때 각 요청 시간이 초과되었고 이때 데이터가 도착했습니다. 마침내 코드를 검토할 수 있었습니다. 프로덕션에서 느린 쿼리가 발생하여 문제가 발생한 것을 발견했습니다. 10초 이상이 걸렸습니다. 실제로는 단일 테이블 WHERE 조건부 쿼리 문이 얼마나 간단한지 상상도 못할 것입니다. 이런 이유로 서비스를 이용할 수 없다는 말씀이 맞나요, 틀리나요? (좋은 데이터 테이블 디자인을 위해서는 처음부터 새로운 인덱스를 고려해야 한다고 말하는 이유가 여기에 있습니다.)

2. 데이터베이스 서버 CPU 100%

상대적으로 쿼리 빈도가 높은 SQL에서는 인덱스가 구축되지 않아 쿼리 속도가 느려지면 데이터베이스 서버 CPU가 100%가 되어 전체 시스템에 영향을 미치게 됩니다.

요약
위에서 언급한 문제에는 여러 가지 유형이 있습니다. 인덱스를 설정하지 않아 발생하는 문제는 쿼리 속도가 느려 시스템 효율성에 영향을 미치는 것부터 CPU를 100% 사용하게 되어 전체 시스템 사용에 영향을 미치는 심각한 경우까지 다양합니다. 지수가 중요하다고 했지?

마지막으로

위에서 간략히 언급했듯이 인덱스란 무엇인가요? 인덱스의 용도와 인덱스 생성 시 몇 가지 팁은 무엇이며 인덱스의 중요성도 강조합니다. 그렇다면 인덱싱은 매우 중요합니다. 일상적인 코딩에서 이를 피하는 방법은 무엇입니까? 다음은 개인적인 제안입니다.

1. 테이블을 만들 때 외래 키 필드 등의 인덱스 추가를 고려해야 합니다.

2. SQL 작성 후 실행 계획을 꼭 확인하세요. 전체 테이블 스캔을 피하십시오.

3. 기존 테이블에 인덱스를 추가하는 경우 먼저 필드의 구분을 계산해야 합니다.

4. 합동지수, 가장 큰 구별을 앞에 두세요.

5. MySQL 왼쪽 열 접두사 우선순위 원칙을 따르세요

[2]H. Berenson, P. Bernstein, J. Gray, J.Melton, E. O'Neil 및 P. O'Neil. ANSI SQL 격리 수준. SIGMOD 국제 데이터 관리 컨퍼런스 진행, 1995년 5월 1~10페이지.

[3]Michael J. Cahill, Uwe Röhm 및 Alan D.Fekete 2008. 스냅샷 데이터베이스에 대한 직렬화 가능 격리. SIGMOD '08: 데이터 관리에 관한 2008 ACM SIGMOD 국제 컨퍼런스, 729-738페이지, 미국 뉴욕주 ACM. [4] Michael James Cahill. University of Sydney, School of Information Technologies[5] A. Fekete, D. Liarokapis, E. O'Neil, P.O'Neil 및 D. ACM 트랜잭션에서 스냅샷 격리를 직렬화합니다. 데이터베이스 시스템, 39(2)권, 492~528페이지, 2005년 6월.