이 기사에서는 mysql에 대한 관련 지식을 제공하며, 논리적 기능에 따른 인덱스 분할, 물리적 구현에 따른 분할, 필드 수에 따른 분할 등 인덱스 유형 문제를 포함하여 mysql 인덱스와 관련된 문제를 주로 소개합니다. , 모두에게 도움이 되기를 바랍니다.
추천 학습: mysql 튜토리얼
SQL 최적화에서 인덱스는 쿼리 효율성을 질적으로 향상시킬 수 있는 중요한 부분이지만 인덱스는 전능하지 않고 불합리한 인덱스 설계로 인해 쿼리 효율성이 느려질 수도 있습니다. .
인덱스는 SQL이 데이터를 효율적으로 얻을 수 있도록 특별히 설계된 데이터 구조입니다. 일반적인 예는 인덱스가 특정 값을 빠르게 찾고 찾을 수 있는 책의 목차와 유사하다는 것입니다. 데이터 쿼리 효율성을 가속화합니다. 실제로 인덱스도 테이블입니다. 이 테이블은 기본 키와 인덱스 필드를 저장하고 엔터티 테이블의 레코드를 가리킵니다(포인터와 유사).
따라서 가장 자주 쿼리되고 가장 자주 정렬되는 데이터 열만 인덱싱되어야 합니다. 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');설명을 사용하여 확인하면 전체 텍스트 인덱스가 효과적인 것을 확인할 수 있습니다. 
물리적 구현으로 구분하면 일반적으로 클러스터형 인덱스와 비클러스터형 인덱스로 나눌 수 있습니다.
存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。
聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。
需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。
非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。
非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。
按字段个数可以把索引分为单一索引和联合索引。
索引字段只有一列时为单一索引，上述所有索引都是单一索引。
将多个字段组合在一起创建的索引叫联合索引。如下：
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 时，针对索引的选择大致有如下步骤：
因此，虽然有多个单列索引，但 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 值以 %
<code>> 조건보다 크거나 작습니다. 조회수가 많으면 인덱스가 적용되고 조회수가 매우 적으며 인덱스가 실패합니다. 🎜🎜🎜🎜 조건이 != <와 동일하지 않습니다. ;>, 인덱스가 실패합니다 🎜🎜🎜🎜LIKE 값이 로 시작합니다. %로 시작하면 인덱스가 유효하지 않습니다🎜🎜🎜🎜권장 학습: 🎜mysql 비디오 튜토리얼🎜🎜
위 내용은 MySQL 인덱스가 제어되고 있습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
