MySQL 인덱스가 제어되고 있습니다.

이 기사에서는 mysql에 대한 관련 지식을 제공하며, 논리적 기능에 따른 인덱스 분할, 물리적 구현에 따른 분할, 필드 수에 따른 분할 등 인덱스 유형 문제를 포함하여 mysql 인덱스와 관련된 문제를 주로 소개합니다. , 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 튜토리얼

SQL 최적화에서 인덱스는 쿼리 효율성을 질적으로 향상시킬 수 있는 중요한 부분이지만 인덱스는 전능하지 않고 불합리한 인덱스 설계로 인해 쿼리 효율성이 느려질 수도 있습니다. .

인덱스 정의

인덱스는 SQL이 데이터를 효율적으로 얻을 수 있도록 특별히 설계된 데이터 구조입니다. 일반적인 예는 인덱스가 특정 값을 빠르게 찾고 찾을 수 있는 책의 목차와 유사하다는 것입니다. 데이터 쿼리 효율성을 가속화합니다. 실제로 인덱스도 테이블입니다. 이 테이블은 기본 키와 인덱스 필드를 저장하고 엔터티 테이블의 레코드를 가리킵니다(포인터와 유사).

인덱스의 장점과 단점

장점

• 인덱스는 서버가 스캔해야 하는 데이터의 양을 크게 줄여줍니다.
• 인덱스는 서버가 정렬 및 임시 테이블을 피하는 데 도움이 됩니다.
• 인덱스는 무작위 IO를 순차 IO로 바꿀 수 있습니다
• Index는 InnoDB용입니다(인덱스에 대한 행 수준 잠금 지원). InnoDB는 액세스해야 하는 튜플만 잠그며 인덱스는 InnoDB가 액세스하는 튜플 수를 줄일 수 있습니다. 쿼리가 인덱스를 사용할 수 없는 경우 MySQL은 전체 테이블 스캔을 수행하고 실제로 필요한지 여부에 관계없이 모든 튜플을 잠급니다.

단점

• 인덱스를 사용하면 쿼리 속도가 크게 향상되지만 테이블 업데이트 속도도 느려집니다. 테이블을 업데이트할 때 MySQL은 데이터를 저장할 뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다. 따라서 매우 자주 업데이트되는 필드에는 일반적으로 인덱스를 사용하지 않는 것이 좋습니다.
• 인덱싱은 디스크 공간을 차지합니다.
• 데이터 열에 반복되는 내용이 많으면 이에 대한 인덱싱 효과가 매우 저하됩니다. 이 속성을 인덱스 선택성이라고 합니다. 즉, 데이터의 전체 레코드 수에 대한 고유 인덱스 값의 비율입니다. 테이블. 인덱스의 선택성이 높을수록 쿼리 효율성이 높아집니다. 예를 들어 성별 필드에 대한 인덱스를 생성하면 백만 개의 데이터에 대해 남성과 여성의 가능성이 두 가지뿐입니다. 인덱스 선택성은 500,000분의 1이고 인덱스 효과는 매우 낮습니다
• 아주 작은 테이블의 경우. , 인덱스는 그다지 중요하지 않습니다. 이 경우 간단한 전체 테이블 스캔이 더 효율적입니다.

따라서 가장 자주 쿼리되고 가장 자주 정렬되는 데이터 열만 인덱싱되어야 합니다. MySQL의 동일한 데이터 테이블에 있는 총 인덱스 수는 16개로 제한됩니다.

인덱스 종류

기능적 논리로 구분

기능적 논리로 구분하여 인덱스는 크게 일반 인덱스, 고유 인덱스, 기본 키 인덱스, 전체 텍스트 인덱스로 구분됩니다.

일반 인덱스

가장 기본적인 인덱스, 그것은 제한이 없습니다. 일반 인덱스(키워드 KEY 또는 INDEX로 정의된 인덱스)의 유일한 작업은 데이터에 대한 액세스 속도를 높이는 것입니다. 따라서 쿼리 조건(WHERE 열 = ...)이나 정렬 조건(ORDER BY 열)에서 가장 자주 나타나는 데이터 열에 대해서만 인덱스를 생성해야 합니다.

일반 인덱스를 만드는 방법에는 세 가지가 있습니다.

# 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	INDEX idx_username (username) );

인덱스 삭제

DROP INDEX idx_username ON user_tbl;

인덱스 보기

SHOW INDEX FROM user_tbl;

고유 인덱스

일반 인덱스를 사용하면 인덱스된 데이터 열에 중복 값을 포함할 수 있다는 점을 제외하면 이전 일반 인덱스와 유사합니다. 고유 인덱스 열의 값은 고유해야 하지만 null 값은 허용됩니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다.

고유 인덱스 생성은 일반 인덱스와 유사합니다.

#创建索引
CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表结构
ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 创建表的时候直接指定
CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	UNIQUE idx_username (username) 
);

기본 키 인덱스

null 값을 허용하지 않는 특수 고유 인덱스입니다. 테이블은 하나의 기본 키만 가질 수 있으며 일반적으로 테이블이 생성될 때 동시에 생성됩니다.

CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	PRIMARY KEY(ID) 
);

외래 키 인덱스도 이와 유사합니다. 외래 키 필드에 대해 외래 키 제약 조건이 정의되면 MySQL은 외래 키 제약 조건을 가장 효율적인 방식으로 관리하고 사용할 수 있도록 내부 인덱스를 정의합니다.

전체 텍스트 인덱스

이전 기사 MySQL 기본 구문에서 처음에 LIKE + %를 사용하면 인덱스가 무효화되고 전후에 퍼지 검색 요구 사항이 필요할 때(예: LIKE '%hello %')와 같이 전체 텍스트 인덱스를 사용해야 합니다. Innodb는 버전 5.6 이후에만 전체 텍스트 인덱스를 지원합니다.

전체 텍스트 인덱스 생성 및 삭제:

# 创建的两种方法
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 删除的两种方法
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

전체 퍼지 일치를 위해 전체 텍스트 인덱스를 사용하는 구문은 다음과 같습니다.

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx');
# 比如对 user_tbl 的 user_name 字段加了全文索引
# 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%';
SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

설명을 사용하여 확인하면 전체 텍스트 인덱스가 효과적인 것을 확인할 수 있습니다.

물리적 구현으로 구분

물리적 구현으로 구분하면 일반적으로 클러스터형 인덱스와 비클러스터형 인덱스로 나눌 수 있습니다.

聚集索引（clustered index）

存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。

聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。

需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。

非聚集索引（non-clustered index）

非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。

非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。

按字段个数划分

按字段个数可以把索引分为单一索引和联合索引。

单一索引

索引字段只有一列时为单一索引，上述所有索引都是单一索引。

联合索引

将多个字段组合在一起创建的索引叫联合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原则

建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：

usernname,city,age
usernname,city
usernname

为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的最左匹配原则，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。

对于 (x,y,z)，只有在以下查询条件联合索引会生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

对于其他情况，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不会匹配联合索引，索引失效，注意对于 WHERE x = 1 AND z = 1，联合索引会对 x 生效，但是对 z 不生效。

可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。

要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

顺便提一下，可以用 explain 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。

如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：

1. MySQL 优化器根据搜索条件，找出所有可能使用的索引
2. 计算全表扫描的代价
3. 计算使用不同索引执行查询的代价
4. 对比各种执行方案的代价，找出成本最低的那一个

因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。

按索引结构划分

不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。

使用总结

接下来我们来简单总结一下在什么场景下推荐使用索引。

推荐使用

• WHERE, GROUP BY, ORDER BY 子句中的字段

• 多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。

  联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那么联合索引应该设计为 (a,b,c)，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。

• 多张表 JOIN 的时候，对表连接字段创建索引。

• SELECT에 인덱스에 없는 필드가 있는 경우 조건에 맞는 기본 키 값을 먼저 인덱스를 통해 쿼리한 후 기본 키 테이블로 돌아가기는 쿼리 효율성에 영향을 미칩니다. 따라서 SELECT에 내용이 거의 없을 경우 테이블 반환을 방지하기 위해 SELECT의 모든 필드를 조인트 인덱스에 추가할 수 있는 것이 와이드 인덱스의 개념입니다. 그러나 인덱스 필드가 너무 많으면 인덱스를 저장하고 유지하는 비용도 증가한다는 점에 유의해야 한다. 回表查询出所有的 SELECT 中的字段，影响查询效率。因此如果 SELECT 中的内容很少，为了避免回表，可以把 SELECT 中的字段都加到联合索引中，这也就是宽索引的概念。但是需要注意，如果索引字段过多，存储和维护索引的成本也会增加。

不推荐使用或索引失效情况

• 数据量很小的表

• 有大量重复数据的字段

• 频繁更新的字段

• 如果对索引字段使用了函数或者表达式计算，索引失效

• innodb OR 条件没有对所有条件创建索引，索引失效

• 大于小于条件 <code>>，索引是否生效取决于命中的数量比例，如果命中数量很多，索引生效，命中数量很小，索引失效

• 不等于条件 != ，索引失效

• LIKE 值以 %

권장되지 않거나 색인이 잘못되었습니다

테이블 데이터 볼륨이 작은

🎜중복 데이터가 많은 필드🎜🎜🎜🎜자주 업데이트되는 필드🎜🎜🎜🎜인덱스 필드에 함수나 표현식 계산을 사용하면 인덱스가 실패합니다🎜🎜🎜🎜innodb OR 조건 인덱스 모든 조건에 대해 생성되지는 않습니다. 🎜🎜🎜🎜는 <code>> 조건보다 크거나 작습니다. 조회수가 많으면 인덱스가 적용되고 조회수가 매우 적으며 인덱스가 실패합니다. 🎜🎜🎜🎜 조건이 != &lt와 동일하지 않습니다. ;>, 인덱스가 실패합니다 🎜🎜🎜🎜LIKE 값이 로 시작합니다. %로 시작하면 인덱스가 유효하지 않습니다🎜🎜🎜🎜권장 학습: 🎜mysql 비디오 튜토리얼🎜🎜

위 내용은 MySQL 인덱스가 제어되고 있습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!