MySQL 인덱스란 무엇입니까? mysql 인덱스 관련 지식 소개

이 기사에서는 mysql 인덱스가 무엇인지 설명합니다. mysql 인덱스에 대한 관련 지식을 소개하면 도움이 될 만한 참고 자료가 될 것입니다.

인덱스란?

인덱스는 책의 목차와 같습니다.

인덱스는 인덱스를 사용하지 않고 특정 열에서 특정 값을 가진 행을 빠르게 찾는 데 사용됩니다. 첫 번째 항목부터 시작해야 합니다. 레코드는 관련 행을 찾을 때까지 전체 테이블을 읽기 시작합니다. 테이블이 클수록 테이블의 쿼리된 열에 인덱스가 있으면 데이터를 쿼리하는 데 더 많은 시간이 걸립니다. 데이터 파일을 검색할 위치로 이동하면 모든 데이터를 볼 필요가 없으므로 많은 시간이 절약됩니다.

장점과 단점

장점

1. 쿼리 속도가 크게 향상됩니다.

2. 모든 필드 유형을 색인화할 수 있습니다.

단점

1. 시간이 많이 걸립니다. 저장 공간을 차지하는 인덱스가 많을수록 데이터 테이블의 데이터도 최대 온라인 설정을 갖습니다. 인덱스 수가 많으면 인덱스 파일이 데이터 파일보다 빠르게 온라인 값에 도달할 수 있습니다.

3. 테이블을 페어링할 때 데이터가 추가되거나 삭제되거나 수정될 때 인덱스도 동적으로 유지 관리해야 하므로 데이터 유지 관리 속도가 감소합니다

사용 원칙 및 시나리오

1. 상황에 따라 다르겠지만

2. 자주 업데이트되는 테이블은 인덱스를 최대한 적게 사용하세요

3. 쿼리에 자주 사용되는 필드에 대해서는 인덱스를 구성하세요

4. 모든 데이터를 쿼리하는 것은 인덱스된 데이터를 순회하는 것보다 시간이 더 걸립니다. 짧은 경우 인덱스는 최적화 효과가 없습니다

5. 예를 들어 성별 필드에는 인덱스를 사용하지 마세요. 남성과 여성에 대해 두 가지 다른 가치관이 있을 뿐입니다.

인덱스 분류

참고: 인덱스는 스토리지 엔진에서 구현됩니다. 즉, 서로 다른 스토리지 엔진이 서로 다른 인덱스를 사용한다는 의미입니다.

MyISAM 및 InnoDB 스토리지 엔진: BTREE 인덱스만 지원합니다. 즉, 기본적으로 BTREE로 사용되며 인덱스가 될 수 없습니다. 대체

MEMORY/HEAP 스토리지 엔진: HASH 및 BTREE 인덱스 지원

1. 단일 열 인덱스

인덱스는 단일 열만 포함하지만 테이블에는 여러 개의 단일 열 인덱스가 있을 수 있습니다

1.1.

MySQL의 기본 인덱스 유형에는 제한이 없으며 순전히 데이터를 더 빠르게 쿼리할 목적으로 인덱스를 정의하는 열에 중복 값과 null 값을 삽입할 수 있습니다.

1.2. 고유 인덱스

인덱스 열의 값은 고유해야 하지만 Null 값은 허용됩니다

1.3 기본 키 인덱스

는 Null 값을 허용하지 않는 특수 고유 인덱스입니다

2. 조합 인덱스

테이블의 여러 필드 조합에서 생성된 인덱스는 해당 필드의 왼쪽 필드가 쿼리 조건에 사용되는 경우에만 사용됩니다. 조합 인덱스를 사용할 때는 가장 왼쪽 접두사 규칙을 따르세요.

3. 전체 텍스트 인덱스

전체 텍스트 인덱스는 MyISAM 엔진에서만 사용할 수 있습니다. 전체 텍스트 인덱스는 CHAR, VARCHAR 및 TEXT 유형 필드에서만 사용할 수 있습니다. 전체 텍스트 색인이란 텍스트 더미 속에서 "당신은 큰 나쁜 펜입니다, 이류 사람입니다..."와 같은 특정 키워드를 통해 해당 필드가 속한 기록 행을 찾을 수 있음을 의미합니다. 큰 나쁜 펜, 가능할 수도 있습니다. 이 레코드를 찾으세요

4. 공간 인덱스

공간 인덱스는 공간 데이터 유형의 필드에 대해 설정된 인덱스입니다. MySQL에는 GEOMETRY, POINT, LINESTRING 및 4가지 공간 데이터 유형이 있습니다. 다각형. 공간 인덱스를 생성할 때 SPATIAL 키워드를 사용하세요. 요구사항: 엔진은 MyISAM이고, 공간 인덱스를 생성하는 데 사용되는 컬럼은 반드시 NOT NULL

Index method

으로 선언되어야 합니다. 사용 원칙: 값의 차이가 큰 경우, 동일한 값으로 검색합니다. (=, <=> , in), 해시 인덱스는 더 효율적인 선택이며, 값의 차이가 상대적으로 낮고 범위 검색이 주요 초점인 경우 검색 복잡도가 O(1)입니다. B-트리가 더 나은 선택입니다. 범위 검색을 지원합니다. B-트리 인덱스

B-트리 인덱스에는 범위 검색 및 접두어 검색 기능이 있습니다. N 노드가 있는 B-트리의 경우 레코드 검색 복잡도는 O(LogN)입니다. 이진 검색과 동일합니다.

해시 인덱스

해시 인덱스는 동일 검색만 할 수 있지만, 해시 테이블이 아무리 커도 검색 복잡도는 O(1)입니다.

인덱스 생성 및 삭제

Create테이블 생성 시 생성

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

예:

CREATE TABLE `NewTable` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (255) NOT NULL,
    `name` VARCHAR (255) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT 0,
    `address` VARCHAR (255) NULL,
    PRIMARY KEY (`id`), # 主键索引
    INDEX `name` (`name`) USING BTREE, # 普通索引
    UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引
    INDEX `u_n_a` (`username`,    `name`,`address`) USING BTREE # 组合索引
);

기존 테이블 생성

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

예:

ALTER TABLE `test`
ADD PRIMARY KEY (`id`),  # 主键索引
ADD INDEX `name` (`name`) USING BTREE , # 普通索引
ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引
ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引

인덱스 삭제

ALTER TABLE 表名 DROP INDEX 索引名。

예:

ALTER TABLE `test`
DROP PRIMARY KEY,
DROP INDEX `username`,
DROP INDEX `name`,
DROP INDEX `u_n_a`;

색인 업데이트

삭제 후 빌드

ALTER TABLE `test`
DROP INDEX `username` ,
ADD UNIQUE INDEX `username1` (`username`) USING BTREE ,
DROP INDEX `name` ,
ADD INDEX `name2` (`name`) USING BTREE ,
DROP INDEX `u_n_a` ,
ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

색인 실패 상황

1. 결합된 필드가 가장 왼쪽 접두사 규칙을 따르지 않습니다2. 퍼지 쿼리(예: '%test

)

# 索引生效
select * from `test` where `name` like "123";
# 索引生效
select * from `test` where `name` like "123%";
# 索引失效
select * from `test` where `name` like "%123";
# 索引失效
select * from `test` where `name` like "%123%";

3. 在索引列上做如下任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范围索引(>,<,between and)后，无法命中组合索引右边的列

构建索引

ALTER TABLE `test`
ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;

示例：

# 命中全部
select * from `test` where `sex` = 1 and `name` = 'a';
# 命中部分，sex命中，name失效
select * from `test` where `sex` > 1 and `name` = 'a';</p>
<p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p>
<p style="white-space: normal;">6. 字符串字段的值不加单引号（数字不报错，英文报错）索引失效</p>
<p>构建索引</p>
<pre class="brush:php;toolbar:false">ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 条件导致索引失效

构建索引

ALTER TABLE `test`
ADD INDEX `sex` (`sex`) USING BTREE ;
ADD INDEX `n_u` (`name`, `username`) USING BTREE ;

示例：

# 索引不生效
select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 
# 索引sex生效
select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )

附录

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

如下构建索引

ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

如下查询情况

# 命中部分
select * from `test` where `username` = 'aaa';
# 命中部分
select * from `test` where `username` = 'aa' and `address` = 'aaa';
# 全命中
select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a';
# 不命中，第一条件字段不是username
select * from `test` where  `address` = 'aaa';

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