mysql 데이터베이스의 인덱스 유형은 무엇입니까? 구축방법 및 장점과 단점

이 글에서는 4가지 유형의 masql 데이터베이스 인덱스에 대해 중점적으로 설명합니다. 데이터베이스 인덱스를 생성하는 방법은 무엇인가요? WHERE 및 JOIN에 나타나는 열은 색인이 필요하지만 MySQL은 <, <=, =, >, >=, BETWEEN, IN 및 때로는 LIKE만 사용하기 때문에 완전히 그럴 필요는 없습니다. 이 기사가 모든 사람에게 도움이 되기를 바랍니다. 먼저 인덱스가 무엇인지 이해해 보겠습니다. 한 문장으로 요약하면 인덱스는 빠른 검색의 핵심입니다.

MySQL 인덱스의 구축은 MySQL의 효율적인 운영을 위해 매우 중요합니다. 다음은 몇 가지 일반적인 MySQL 인덱스 유형을 소개합니다

데이터베이스 테이블에서 인덱싱 필드는 쿼리 속도를 크게 향상시킬 수 있습니다. mytable 테이블을 생성한다고 가정해 보겠습니다.

코드는 다음과 같습니다:

CREATE TABLE mytable( ID INT NOT NULL, 사용자 이름 VARCHAR(16) NOT NULL );

5555를 포함하여 10,000개의 레코드를 무작위로 삽입했습니다. 관리자.

username="admin" SELECT * FROM mytable WHERE username='admin';의 레코드를 검색할 때, username에 인덱스가 설정되어 있으면 MySQL은 스캔 없이 정확하게 레코드를 찾을 수 있습니다. 반대로 MySQL은 모든 레코드를 검색합니다. 즉, 10,000개의 레코드가 쿼리됩니다.

인덱스는 단일 열 인덱스와 결합 인덱스로 구분됩니다. 단일 열 인덱스는 인덱스가 단일 열만 포함한다는 의미입니다. 테이블은 여러 개의 단일 열 인덱스를 가질 수 있지만 이는 결합된 인덱스가 아닙니다. 결합 인덱스, 즉 하나의 인덱스에 여러 열이 포함됩니다.

MySQL 인덱스 유형은 다음과 같습니다.

1. 일반 인덱스

이것은 가장 기본적인 인덱스이며 제한이 없습니다. 생성 방법은 다음과 같습니다.

1. 인덱스 생성

코드는 다음과 같습니다.

CREATE INDEX indexName ON mytable(username(length));

CHAR, VARCHAR 유형인 경우 길이는 더 작을 수 있습니다. 필드의 실제 길이보다 BLOB 및 TEXT 유형인 경우 아래와 동일하게 길이를 지정해야 합니다.

2. 테이블 구조를 수정합니다

코드는 다음과 같습니다.
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 테이블 생성 시 직접 지정

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16 ) NOT NULL, INDEX [indexName] (username(length)) )

-- 인덱스 삭제 구문:

DROP INDEX [indexName] ON mytable; 이전 일반과 동일합니다. 인덱스 열의 값이 고유해야 한다는 점만 빼면 인덱스와 유사하지만 null 값이 허용됩니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다. 생성 방법은 다음과 같습니다.

코드는 다음과 같습니다. CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 테이블 구조 수정

ALTER mytable ADD UNIQUE [indexName] ON (username(length) )

- -

CREATE TABLE mytable( ID INT NOT NULL, 사용자 이름 VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

3을 직접 지정합니다. 기본 키 인덱스


A입니다. null 값을 허용하지 않는 특수 고유 인덱스입니다. 일반적으로 기본 키 인덱스는 테이블 생성과 동시에 생성됩니다.

코드는 다음과 같습니다. CREATE TABLE mytable( ID INT NOT NULL, 사용자 이름 VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ) ;

물론 ALTER Order를 사용할 수도 있습니다. 기억하세요: 테이블에는 기본 키가 하나만 있을 수 있습니다.

4. 복합 인덱스

단일 컬럼 인덱스와 복합 인덱스를 생생하게 비교하기 위해 테이블에 여러 필드를 추가합니다.

코드는 다음과 같습니다. CREATE TABLE mytable( ID INT NOT NULL, 사용자 이름 VARCHAR (16) NOT NULL , city VARCHAR(50) NOT NULL, age INT NOT NULL )

MySQL의 효율성을 더욱 끌어내기 위해서는 결합 인덱스 구축을 고려해야 합니다. 이름, 도시, 나이를 인덱스로 작성하세요.

코드는 다음과 같습니다.

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]

테이블을 생성할 때, 사용자 이름은 16입니다. 여기서는 10을 사용하세요. 이는 일반적으로 이름 길이가 10을 초과하지 않으므로 인덱스 쿼리 속도가 빨라지고 인덱스 파일 크기가 줄어들며 INSERT의 업데이트 속도가 향상되기 때문입니다.

사용자 이름, 도시, 연령에 대해 각각 단일 열 인덱스를 생성하여 테이블에 3개의 단일 열 인덱스가 있는 경우 쿼리 효율성은 위의 결합 인덱스와 매우 다르며 이는 결합 인덱스보다 훨씬 낮습니다. . 현재 3개의 인덱스가 있지만 MySQL은 가장 효율적이라고 생각되는 단일 열 인덱스만 사용할 수 있습니다.

이러한 결합 지수를 구축하는 것은 실제로 다음 세 가지 결합 지수 세트를 구축하는 것과 같습니다.

usernname,city,age usernname,city usernname 왜 도시와 나이 같은 통합 인덱스가 없나요? 이는 MySQL 복합 인덱스의 "가장 왼쪽 접두사"의 결과입니다. 간단한 이해는 가장 왼쪽부터 조합을 시작하는 것입니다. 이 세 개의 열을 포함하는 쿼리가 이 결합 인덱스를 사용할 뿐만 아니라 다음 SQL도 이 결합 인덱스를 사용합니다.
[code]
SELECT * FROM mytable WHREE username="admin" AND city="Zhengzhou" SELECT * FROM mytable WHREE 사용자 이름="admin"

다음 항목은 사용되지 않습니다.

코드는 다음과 같습니다.
SELECT * FROM mytable WHREE age= 20 AND city="Zhengzhou" SELECT * FROM mytable WHREE city="Zhengzhou"

5. 인덱스 생성 방법

#🎜 🎜#여기서 지금까지 인덱스 생성 방법을 배웠는데, 어떤 상황에서 인덱스를 생성해야 할까요? 일반적으로 WHERE 및 JOIN에 나타나는 열은 인덱싱되어야 하지만 MySQL은 <, <=, =, >, >=, BETWEEN, IN 및 때로는 LIKE 인덱스만 사용하기 때문에 이는 완전히 사실이 아닙니다. 색인. 예:

코드는 다음과 같습니다:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city ='Zhengzhou'

이때 mytable 테이블의 사용자 이름도 JOIN 절에 나타나므로 도시와 나이에 대한 인덱스도 필요하다. .

지금 LIKE는 특정 시간에만 색인을 생성해야 한다고 언급했습니다. MySQL은 와일드카드 문자 % 및 _로 시작하는 쿼리를 만들 때 인덱스를 사용하지 않기 때문입니다. 예를 들어, 다음 문장에서는 인덱스를 사용합니다:

코드는 다음과 같습니다:

SELECT * FROM mytable WHERE username like'admin%'
다음 문장에서는 인덱스를 사용하지 않습니다. it:
#🎜 🎜#코드는 다음과 같습니다:

SELECT * FROM mytable WHEREt Name like'%admin'

따라서 주의해야 할 점은 LIKE를 사용할 때 위의 차이점이 있습니다.

6. 인덱스 단점

위에서 설명한 내용은 모두 인덱스 사용의 이점에 대한 설명이지만, 인덱스를 과도하게 사용하면 악용이 발생할 수 있습니다. 따라서 인덱스에도

1이라는 단점이 있습니다. 인덱스를 사용하면 쿼리 속도가 크게 향상되지만 테이블에 대한 INSERT, UPDATE, DELETE 등의 테이블 업데이트 속도도 느려집니다. . 왜냐하면 테이블을 업데이트할 때 MySQL은 데이터를 저장할 뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다.

2. 인덱스 파일을 생성하면 디스크 공간을 차지하게 됩니다. 일반적으로 이 문제는 심각하지 않지만, 큰 테이블에 여러 개의 결합된 인덱스를 생성하면 인덱스 파일이 빠르게 확장됩니다.

인덱스는 효율성을 향상시키는 한 가지 요소일 뿐입니다. MySQL에 많은 양의 데이터 테이블이 있는 경우 최고의 인덱스를 조사하고 구축하거나 쿼리 문을 최적화하는 데 시간을 투자해야 합니다.

7. 인덱스 사용 시 주의할 점:

인덱스 사용 시 다음과 같은 팁과 주의사항이 있습니다. #🎜 🎜##🎜 🎜#1. 인덱스에는 NULL 값이 있는 열이 포함되지 않습니다

열에 NULL 값이 포함되어 있는 한 인덱스에 포함되지 않습니다. NULL 값을 포함하는 복합 인덱스에서 이 A 열은 이 복합 인덱스에 유효하지 않습니다. 따라서 데이터베이스를 설계할 때 해당 필드의 기본값이 NULL이 되도록 해서는 안 됩니다.

2. 짧은 색인

을 사용하여 문자열을 색인화하고 가능하면 접두사 길이를 지정해야 합니다. 예를 들어 CHAR(255) 열이 있는 경우 대부분의 값이 처음 10자 또는 20자 내에서 고유한 경우 전체 열을 인덱싱하지 마세요. 짧은 인덱스는 쿼리 속도를 향상시킬 뿐만 아니라 디스크 공간과 I/O 작업을 절약합니다.

3. 인덱스 열 정렬

MySQL 쿼리는 하나의 인덱스만 사용하므로 where 절에 해당 인덱스가 사용된 경우에는 다음 순서의 열은 사용되지 않습니다. . 따라서 데이터베이스의 기본 정렬이 요구 사항을 충족할 수 있으면 정렬 작업을 사용하지 마십시오. 필요한 경우 이러한 열에 대한 복합 인덱스를 만드는 것이 가장 좋습니다.

4. Like 문 연산

일반적으로 like 연산을 사용해야 한다면 어떻게 사용하는지도 문제입니다. "%aaa%"는 인덱스를 사용하지 않지만 "aaa%"는 인덱스를 사용합니다.

5. 열에 작업을 수행하지 마세요.

코드는 다음과 같습니다:

select * from users where YEAR(adddate)<2007;#🎜🎜 ##🎜 🎜#은 각 행에서 작동하므로 인덱스가 실패하고 전체 테이블 스캔을 수행하므로 다음과 같이 변경할 수 있습니다.

코드는 다음과 같습니다.

select * from users where adddate<'2007-01-01';

6 위에서 NOT IN 및 <> 연산을 사용하지 않고

MySQL 인덱스 유형이 도입되었습니다. 그것이 모두에게 도움이 되기를 바랍니다.

두 가지 색인 생성 방법:

B-트리 색인 참고 : 넓은 관점에서는 모두 균형 트리를 사용하지만, 구체적인 구현 측면에서는 각 스토리지 엔진이 조금씩 다릅니다. 예를 들어 엄밀히 말하면 NDB 엔진은 T-tree Myisam을 사용합니다. , innodb는 기본적으로 B-트리 인덱스를 사용합니다. B-트리의 이론적 쿼리 시간 복잡도는 O(log2 (N-1))이고, N은 데이터 테이블의 행 수

hash입니다. index # 🎜🎜#메모리 저장 엔진을 사용하는 테이블의 경우 기본값은 해시 인덱스입니다. 해시의 이론적 쿼리 시간 복잡도는 O(1)

관련 권장 사항:

# 🎜🎜 #mysql을 이용한 인덱스 생성 방법에 대한 자세한 설명과 장단점 분석

인덱싱 페이지의 장점과 단점 1/2

위 내용은 mysql 데이터베이스의 인덱스 유형은 무엇입니까? 구축방법 및 장점과 단점의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!