MySQL 인덱스 유형과 장점 및 단점

1 인덱스의 데이터 유형 선택

MySQL은 다양한 데이터 유형을 지원하여 데이터를 저장하는 것이 성능에 큰 영향을 미칩니다. 일반적으로 다음과 같은 몇 가지 지침을 따를 수 있습니다.

(1) 일반적으로 데이터 유형이 작을수록 좋습니다. 데이터 유형이 작을수록 일반적으로 디스크, 메모리 및 CPU 캐시에서 더 적은 공간이 필요합니다.

(2) 단순한 데이터 유형이 더 좋습니다. 문자열 비교가 더 복잡하기 때문에 정수 데이터는 문자보다 처리 오버헤드가 적습니다. MySQL에서는 문자열 대신 내장된 날짜 및 시간 데이터 유형을 사용하여 시간을 저장하고 정수 데이터 유형을 사용하여 IP 주소를 저장해야 합니다.

(3) NULL을 피하십시오. NULL을 저장하려는 경우가 아니면 열을 NOT NULL로 지정해야 합니다. MySQL에서 null 값이 포함된 열은 인덱스, 인덱스 통계, 비교 연산을 복잡하게 만들어 쿼리를 최적화하기 어렵습니다. null 값을 0, 특수 값 또는 빈 문자열로 바꿔야 합니다.

2
인덱스 종류

인덱스는 서버 계층이 아닌 스토리지 엔진에서 구현됩니다. 따라서 각 스토리지 엔진의 인덱스가 반드시 동일할 필요는 없으며, 모든 스토리지 엔진이 모든 인덱스 유형을 지원하는 것은 아닙니다

(1) 일반 인덱스

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

인덱스 생성

CREATE INDEX indexName ON mytable(username(length)); CHAR, VARCHAR 유형인 경우 길이는 실제 길이보다 작을 수 있습니다. 필드인 경우 BLOB 및 TEXT 유형의 경우 아래와 동일하게 길이를 지정해야 합니다.

테이블 구조 수정

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;

(2) 고유 인덱스

인덱스 열의 값이 고유해야 한다는 점만 빼면 이전 일반 인덱스와 비슷하지만, 비어 있는 값은 허용됩니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다. 다음과 같은 생성 방법이 있습니다.

인덱스 생성

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   );

(3) 기본 키 인덱스

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

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );  
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

(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); 테이블을 생성할 때 사용자 이름 길이는 16이고 여기서는 10이 사용됩니다. . 이는 일반적으로 이름 길이가 10을 초과하지 않으므로 인덱스 쿼리 속도가 빨라지고 인덱스 파일 크기가 줄어들며 INSERT의 업데이트 속도가 향상되기 때문입니다.

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

이러한 결합 색인을 생성하는 것은 실제로 다음 세 가지 결합 색인 세트를 설정하는 것과 같습니다.

usernname,city,age usernname,city usernname 왜 city,age와 같은 결합 색인이 없는 걸까요? ? 이는 MySQL 복합 인덱스의 "가장 왼쪽 접두사"의 결과입니다. 간단한 이해는 가장 왼쪽부터 조합을 시작하는 것입니다. (이것은 인터뷰 질문 중 하나이며 당시에 올바르게 대답했어야 했습니다.) 이 세 개의 열을 포함하는 쿼리는 이 결합 인덱스를 사용할 뿐만 아니라 다음 SQL도 이 결합 인덱스를 사용합니다:

SELECT FROM mytable WHREE username="admin" AND city="郑州"  SELECT FROM mytable WHREE username="admin" 而下面几个则不会用到：
SELECT FROM mytable WHREE age=20 AND city="郑州"  SELECT FROM mytable WHREE city="郑州"

3
인덱스 생성 시간

지금까지 인덱스 생성 방법을 배웠는데, 어떤 상황에서 인덱스를 생성해야 할까요? 일반적으로 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='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

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

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

4
The 단점 of indexes

위에서 언급한 사용 인덱스 이점이 있지만 인덱스를 과도하게 사용하면 남용이 발생할 수 있습니다. 따라서 인덱스에도 단점이 있습니다.

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

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

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

5
인덱스 사용 시 주의사항

인덱스 사용 시 다음과 같은 몇 가지 팁과 주의사항이 있습니다.

인덱스에는 다음과 같은 열이 포함되지 않습니다. NULL 값.

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算

select * from users where YEAR(adddate)<2015; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2015-01-01’;

不使用NOT IN和<>操作

以上就是MySQL索引类型与优缺点的内容，更多相关内容请关注PHP中文网（www.php.cn）！