MySQL 인덱스 오류를 해결하는 방법

1. 서문

SQL 문에서 인덱스 쿼리를 수행할 때 인덱스 오류가 발생하며 이는 문의 실행 가능성과 성능 효율성에 결정적인 영향을 미칩니다. 이 문서에서는 인덱스가 실패하는 이유를 분석합니다. code>, <code>색인 오류로 이어지는 상황 및 가장 왼쪽 접두사 일치 원칙에 초점을 맞춘 색인 오류에 대한 최적화 솔루션, MySQL 논리 아키텍처 및 최적화 도구, 인덱스 실패 시나리오 및 실패 이유. 索引为何失效，有哪些情况会导致索引失效以及对于索引失效时的优化解决方案，其中着重介绍最左前缀匹配原则、MySQL逻辑架构和优化器、索引失效场景以及为何会失效。

二、最左前缀匹配原则

之前有写了一篇关于MySQL添加索引特点及优化问题方面的文章，下面将介绍索引失效的相关内容。

首先引入在之后的索引失效原因中会使用到的一个原则：最左前缀匹配原则。

最左前缀底层原理：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

什么是最左前缀匹配原则呢？要想理解联合索引的最左匹配原则，先来理解下索引的底层原理：索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。数据库需要依赖联合索引中最左边的字段来构建，因为B+树只能根据一个值来确定索引关系。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

a的值有序，出现的顺序为1，1，2，2，3，3。b的值无序，出现的数字为1，2，1，4，1，2。在a的值相等的情况下，我们可以观察到b的值按照一定顺序排列，但要注意这个顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

由于整个过程是基于explain结果分析的，那接下来在了解下explain中的type字段和key_lef字段。

1.type：联接类型。

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

• const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。将主键放在WHERE条件中，MySQL会将该查询转换为一个const查询。

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。注意：ALL全表扫描的表记录最少的表如t1表ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。

• index

  2. 가장 왼쪽 접두사 일치 원칙
• 이전에 MySQL에 인덱스를 추가할 때의 특성 및 최적화 문제에 대한 기사를 작성한 적이 있습니다. 다음은 인덱스 실패와 관련된 내용을 소개합니다.
  먼저 후속 색인 실패 이유에 사용될 원칙을 소개합니다: 가장 왼쪽 접두사 일치 원칙.

가장 왼쪽 접두사의 기본 원칙: MySQL은 결합 인덱스를 구축할 때 가장 왼쪽 접두사 일치 원칙, 즉 데이터를 검색할 때 결합 인덱스의 가장 왼쪽부터 일치를 시작합니다.

가장 왼쪽 접두사 일치 원칙은 무엇인가요? 결합 인덱스의 가장 왼쪽 일치 원리를 이해하려면 먼저 인덱스의 기본 원리를 이해해야 합니다. 인덱스의 맨 아래 레이어는 B+ 트리이고, 결합 인덱스의 맨 아래 레이어도 B+ 트리이지만 B+ 트리에서는 조인트 인덱스의 노드 키 값이 저장됩니다. B+ 트리는 하나의 값으로만 ​​인덱스 관계를 판단할 수 있기 때문에 조인트 인덱스의 가장 왼쪽 필드를 기반으로 데이터베이스를 구축해야 합니다.

예: (a, b)의 공동 인덱스를 생성하면 해당 인덱스 트리는 아래 그림과 같습니다.

• a 값은 순서대로 나타나는 순서는 1, 1, 2, 2, 3, 3입니다. b의 값은 순서가 없으며 나타나는 숫자는 1, 2, 1, 4, 1, 2입니다. a의 값이 동일할 때 b의 값이 일정한 순서로 배열되어 있음을 관찰할 수 있지만, 이 순서는 상대적이라는 점에 유의해야 합니다. 이는 결합 인덱스를 생성하는 MySQL의 규칙이 첫 번째 필드의 정렬을 기반으로 결합 인덱스의 가장 왼쪽 첫 번째 필드를 먼저 정렬한 다음 두 번째 필드를 정렬하는 것이기 때문입니다. 따라서 b=2와 같은 쿼리 조건에는 인덱스를 사용할 수 있는 방법이 없습니다.
  전체 과정은 explain 결과 분석을 기반으로 하므로 explain의 type 필드와 key_lef 필드에 대해 알아보겠습니다.
• 1.

  유형: 연결 유형

  .

• system: 테이블에는 레코드 행이 하나만 있습니다(시스템 테이블과 동일). 이는 일반적으로 나타나지 않으며 무시할 수 있습니다.

const: 인덱스를 통해 한 번 발견되면 const는 기본 키 또는 고유 인덱스를 비교하는 데 사용됩니다. 한 행의 데이터만 일치시키면 되므로 매우 빠릅니다. WHERE 조건에 기본 키를 넣으면 MySQL은 쿼리를 const 쿼리로 변환합니다.

• eq_ref: 고유 인덱스 스캔, 각 인덱스 키에 대해 테이블의 단 하나의 레코드만 일치합니다. 기본 키 또는 고유 인덱스 스캔에서 흔히 볼 수 있습니다. 참고: t1 테이블 ref: 고유하지 않은 인덱스 스캔과 같이 가장 적은 레코드가 있는 테이블의 전체 전체 테이블 스캔은 단일 값과 일치하는 모든 행을 반환합니다. 기본적으로 이는 단일 값과 일치하는 모든 행을 반환하는 인덱스 액세스입니다. 그러나 일치하는 여러 행을 찾을 수 있으므로 검색과 스캔을 혼합해야 합니다.

range: 인덱스를 사용하여 행을 선택하여 지정된 범위의 행만 검색합니다. 키 열에는 사용된 인덱스가 표시됩니다. 일반적으로 where 문에는between, , in 등과 같은 쿼리가 나타납니다. 인덱스 열에 대한 이 범위 스캔은 전체 인덱스 스캔보다 낫습니다. 전체 인덱스를 스캔하지 않고 특정 지점에서 시작하고 다른 지점에서 끝나기만 하면 됩니다. 🎜🎜🎜🎜index: 전체 인덱스 스캔, 인덱스와 ALL의 차이점은 인덱스 유형이 인덱스 트리만 순회한다는 것입니다. 인덱스 파일은 일반적으로 데이터 파일보다 작기 때문에 이는 일반적으로 모든 블록입니다. (Index와 ALL은 모두 전체 테이블을 읽지만, index는 인덱스에서 읽고, ALL은 하드 디스크에서 읽습니다.)🎜🎜🎜🎜ALL: 전체 테이블 스캔, 전체 테이블을 탐색하여 일치하는 행을 찾습니다.🎜🎜🎜🎜 2.🎜 key_len🎜: MySQL이 실제로 사용하기로 결정한 인덱스의 길이를 표시합니다. 인덱스가 NULL이면 길이도 NULL입니다. NULL이 아닌 경우 사용된 인덱스의 길이입니다. 따라서 이 필드를 사용하여 어떤 인덱스가 사용되는지 추론할 수 있습니다. 🎜🎜🎜계산 규칙: 🎜🎜🎜🎜🎜1. 고정 길이 필드, int는 4바이트, date는 3바이트, char(n)은 n자를 차지합니다. 🎜🎜🎜🎜2. 가변 길이 필드 varchar(n)은 n 문자 + 2바이트를 차지합니다. 🎜🎜🎜🎜3. 문자 세트가 다르면 문자가 차지하는 바이트 수가 다릅니다. Latin1 인코딩에서는 한 문자가 1바이트를 차지하고, gdk 인코딩에서는 한 문자가 2바이트를 차지하고, UTF-8 인코딩에서는 한 문자가 3바이트를 차지합니다. 🎜🎜🎜🎜(내 데이터베이스는 Latin1 인코딩 형식을 사용하므로 후속 계산에서 한 문자가 1바이트로 계산됩니다.) 🎜🎜🎜🎜4 모든 인덱스 필드에 대해 NULL로 설정된 경우 1바이트가 필요합니다. 🎜🎜🎜🎜가장 왼쪽 접두사 일치 원리를 이해한 후, 인덱스 실패 시나리오를 살펴보고 실패 이유를 분석해 보겠습니다. 🎜

3. MySQL 논리 아키텍처 및 최적화

MySQL 논리 아키텍처: MySQL逻辑架构：

mysql架构可分为大概的4层，分别是：

1. 1.客户端：各种语言都提供了连接mysql数据库的方法，比如jdbc、php、go等，可根据选择 的后端开发语言选择相应的方法或框架连接mysql

2. 2.server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖mysql的大多数核心服务功能，以及所有的内置函数（例如日期、世家、数 学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

3. 3.存储引擎层：负责数据的存储和提取，是真正与底层物理文件打交道的组件。 数据本质是存储在磁盘上的，通过特定的存储引擎对数据进行有组织的存放并根据业务需要对数据进行提取。存储引擎的架构模式是插件式的，支持Innodb，MyIASM、Memory等多个存储引擎。现在最常用的存储引擎是Innodb，它从mysql5.5.5版本开始成为了默认存储引擎。

4. 4.物理文件层：存储数据库真正的表数据、日志等。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。

server层重要组件介绍：

1.连接器

连接器负责来自客户端的连接、获取用户权限、维持和管理连接。

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建连接才会使用新的权限设置。

2.查询缓存

mysql拿到一个查询请求后，会先到查询缓存查看之前是否执行过这条语句。之前运行的语句及其输出结果可能直接存储在内存中，以键值对的形式缓存。key是查询的语句，value是查询的结果。当SQL查询的关键字(key)能够直接在查询缓存中匹配时，查询结果(value)就会被直接返回给客户端。

其实大多数情况下建议不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。只要涉及到一个表的更新操作，所有和该表相关的查询缓存都很容易失效并被清空。因此很有可能经过费力将结果存储之后，还未来得及使用就被新的更新操作全部清空了。对于更新操作多的数据库来说，查询缓存的命中率会非常低。除非业务需要的是一张静态表，很长时间才会更新一次。比如，一个系统配置表，那么这张表的查询才适合使用查询缓存。

3.分析器

词法分析（识别关键字，操作，表名，列名）
语法分析 (判断是否符合语法）

4.优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5.执行器

开始执行的时候，要先判断一下用户对这个表 T 有没有执行查询的权限。如果没有，就会返回没有权限的错误。如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去调用这个引擎提供的接口。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

MySQL优化器

mysql 아키텍처는 대략 4개의 계층으로 나눌 수 있습니다.

1. 1.

   클라이언트

   : 다양한 언어가 jdbc, php, go 등 mysql 데이터베이스에 연결하는 방법을 제공합니다. 선택한 백엔드 개발 언어에 따라 mysql에 연결하는 데 해당 방법이나 프레임워크를 선택할 수 있습니다 li>

2. 2.
   서버 레이어🎜: 커넥터, 쿼리 캐시, 분석기, 최적화 프로그램, 실행기 등을 포함하며 mysql의 핵심 서비스 기능 대부분과 모든 내장 기능(예: 날짜, 가족)을 포함합니다. , 수학 및 암호화 기능 등) 저장 프로시저, 트리거, 뷰 등과 같은 모든 교차 스토리지 엔진 기능이 이 계층에서 구현됩니다. 🎜
3. 🎜3.🎜스토리지 엔진 레이어🎜: 데이터의 저장 및 검색을 담당하며 기본 물리적 파일을 실제로 처리하는 구성 요소입니다. 데이터의 본질은 디스크에 저장되며, 특정 스토리지 엔진을 통해 데이터가 체계적으로 저장되고 비즈니스 요구에 따라 추출됩니다. 스토리지 엔진의 아키텍처 모델은 플러그인이며 Innodb, MyIASM 및 Memory와 같은 여러 스토리지 엔진을 지원합니다. 현재 가장 일반적으로 사용되는 스토리지 엔진은 Innodb이며, 이는 mysql5.5.5부터 기본 스토리지 엔진이 되었습니다. 🎜
4. 🎜4.🎜물리적 파일 레이어🎜: 데이터베이스의 실제 테이블 데이터, 로그 등을 저장합니다. 실제 파일에는 redolog, undolog, binlog, errorlog, querylog, Slowlog, 데이터, 인덱스 등이 포함됩니다. 🎜

🎜🎜서버 계층의 중요한 구성 요소 소개: 🎜🎜🎜🎜1. 커넥터 🎜🎜🎜 커넥터는 클라이언트의 연결, 사용자 권한 획득, 연결 유지 및 관리를 담당합니다. 🎜🎜사용자가 성공적으로 연결을 설정한 후에는 관리자 계정을 사용하여 사용자의 권한을 수정하더라도 기존 연결의 권한에는 영향을 미치지 않습니다. 수정이 완료된 후에는 새 연결만 새 권한 설정을 사용합니다. 🎜🎜🎜2. 쿼리 캐시🎜🎜🎜mysql은 쿼리 요청을 받은 후 먼저 쿼리 캐시로 이동하여 이전에 이 명령문이 실행되었는지 확인합니다. 이전에 실행된 명령문과 그 출력은 키-값 쌍으로 캐시되어 메모리에 직접 저장될 수 있습니다. 키는 쿼리문이고, 값은 쿼리 결과입니다. SQL 쿼리의 키워드가 쿼리 캐시에서 직접 일치할 수 있는 경우 쿼리 결과(값)가 클라이언트에 직접 반환됩니다. 🎜🎜사실 대부분의 경우 쿼리 캐시를 사용하지 않는 것이 좋습니다. 이유는 무엇입니까? 쿼리 캐싱은 종종 득보다 실이 더 많기 때문입니다. 테이블 업데이트 작업이 포함되어 있는 한 테이블과 관련된 모든 쿼리 캐시는 쉽게 무효화되고 지워질 수 있습니다. 따라서 결과를 공들여 저장한 후 사용하기 전에 새로운 업데이트 작업으로 결과가 지워질 가능성이 매우 높습니다. 업데이트 작업이 많은 데이터베이스의 경우 쿼리 캐시 적중률이 매우 낮습니다. 비즈니스에 정적 테이블이 필요한 경우가 아니면 오랫동안 한 번만 업데이트됩니다. 예를 들어 시스템 구성 테이블인 경우 이 테이블의 쿼리는 쿼리 캐시를 사용하는 데 적합합니다. 🎜🎜🎜3. 분석기🎜🎜🎜어휘 분석(키워드, 연산, 테이블 이름, 열 이름 식별)
구문 분석(문법 준수 여부 확인)🎜🎜🎜4 최적화 프로그램은 다음과 같습니다. 테이블에 여러 인덱스가 있는 경우 사용할 인덱스를 결정하거나 문에 여러 테이블 연결(조인)이 있는 경우 테이블이 조인되는 순서를 결정합니다. Optimizer 단계가 완료된 후 이 문의 실행 계획을 결정한 후 Executor 단계로 들어갑니다. 🎜🎜🎜5. 실행자 🎜🎜🎜가 실행을 시작하면 먼저 사용자에게 이 테이블 T에 대한 쿼리를 실행할 권한이 있는지 확인해야 합니다. 그렇지 않은 경우 권한 없음 오류가 반환됩니다. 쿼리 캐시에 적중되면 쿼리 캐시가 결과를 반환할 때 권한 확인이 수행됩니다. 쿼리는 또한 최적화 프로그램 이전에 권한을 확인하기 위해 사전 확인을 호출합니다. 권한이 있으면 테이블을 열고 계속 실행하세요. 테이블이 열리면 실행기는 테이블의 엔진 정의를 기반으로 엔진이 제공하는 인터페이스를 호출합니다. 일부 시나리오에서는 실행기가 한 번 호출되고 엔진 내부에서 여러 행이 스캔되므로 엔진이 스캔하는 행 수가 Row_examined와 정확히 동일하지 않습니다. 🎜🎜MySQL 최적화 프로그램: 🎜🎜MySQL 최적화 프로그램은 비용 기반 최적화(Cost-based Optimization)를 사용하고 SQL 문을 입력으로 사용하며 내장된 비용 모델 및 데이터 사전 정보와 스토리지 엔진을 사용합니다. 통계 정보는 쿼리 문, 즉 쿼리 계획을 구현하는 데 어떤 단계를 사용하는지 결정합니다. 🎜🎜🎜🎜🎜높은 수준에서 MySQL 서버는 서버 계층과 스토리지 엔진 계층이라는 두 가지 구성 요소로 나뉩니다. 그 중 옵티마이저는 스토리지 엔진 API 위에 위치한 서버 계층에서 작동합니다. 🎜🎜🎜옵티마이저의 작업 프로세스는 의미상 4단계로 나눌 수 있습니다. 🎜🎜

1.논리 변환, 부정 제거, 등가 전달 및 상수 전달, 상수 표현식 평가, 외부 조인을 내부 조인으로 변환, 하위 쿼리 변환, 뷰 병합 등
2.인덱싱과 같은 최적화 준비 ref 및 범위 액세스 방법 분석, 쿼리 조건 팬아웃 값(팬아웃, 필터링 후 레코드 수) 분석, 상수 테이블 감지
3.비용 최적화 기반, 액세스 방법 및 연결 순서 선택 등
4. 테이블 조건 푸시다운, 액세스 방법 조정, 정렬 회피, 인덱스 조건 푸시다운 등 실행 계획 개선.

4. 인덱스 실패 시나리오 및 실패 이유

1.like는 와일드카드 문자 %로 시작하고 인덱스가 실패합니다. 위에서는 가장 왼쪽 접두사 일치의 기본 원리를 소개합니다. 우리는 일반적으로 사용되는 인덱스 데이터 구조가 B+ 트리이고 인덱스가 정렬되어 있음을 알고 있습니다. 색인 키워드 유형이 Int 유형인 경우 like以通配符%开头索引失效。上面介绍了最左前缀匹配底层原理，我们知道了通常用的索引数据结构是B+树，而索引是有序排列的。如果索引关键字的类型是Int 类型，索引的排列顺序如下：

数据只存放在叶子节点，而且是有序的排放。

如果索引关键字的类型是String类型，排列顺序如下：

可以看出，索引的排列顺序是根据比较字符串的首字母排序的。
我们在进行模糊查询的时候，如果把 % 放在了前面，最左的 n 个字母便是模糊不定的，无法根据索引的有序性准确的定位到某一个索引，只能进行全表扫描，找出符合条件的数据。(最左前缀底层原理)

在使用联合索引时也是如此，如果违背了索引有序排列的规则，同样会造成索引失效，进行全表扫描。
例子:表example中有个组合索引为：（A,B,C）
SELECT * FROM example WHERE A=1 and B =1 and C=1; 可以走索引；
SELECT A FROM example WHERE C =1 and B=1 ORDER BY A; 可以走索引（使用了覆盖索引）
SELECT * FROM example WHERE C =1 and B=1 ORDER BY A; 不可以走索引

覆盖索引:索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)

可以有两种方式优化：
一种是使用覆盖索引，第二种是把%放后面。

2.字段类型是字符串，where时没有用引号括起来。表中的字段为字符串类型，是B+树的普通索引，如果查询条件传了一个数字过去，它是不走索引的。
例子:表example中有个字段为pid是varchar类型。

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE pid = 1

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = '1'

为什么第一条语句未加单引号就不走索引了呢？ 这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

3.OR 前后只要存在非索引的列，都会导致索引失效。查询条件包含or，就有可能导致索引失效。
例子:表example中有字段为pid是int类型,score是int类型。

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = 1

//把or条件加没有索引的score，并不会走索引，为ALL全表查询
explain SELECT * FROM example WHERE pid = 1 OR score = 10

这里对于OR后面加上没有索引的score这种情况，假设它走了p_id的索引，但是走到score查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并。
mysql是有优化器的，处于效率与成本，遇到OR条件，索引可能会失效也是合理的。

注意: 如果or条件的列都加了索引，索引可能会走的。

4.联合索引(组合索引)，查询时的条件列不是联合索引中的第一个列，索引失效。在联合索引中，查询条件满足最左匹配原则时，索引是正常生效的。
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
例子:有一个联合索引idx_pid_score，pid在前，score在后。

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1 OR score = 10

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score = 10

联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关。

5.计算、函数、类型转换(自动或手动)导致索引失效，索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。색인 순서는 다음과 같습니다.

데이터는 리프 노드에만 저장되며 순서대로</code로 배출됩니다. >. </p>🎜인덱스 키워드의 유형이 <code>문자열 유형인 경우 🎜정렬 순서는 다음과 같습니다. 🎜🎜🎜🎜🎜index의 첫 글자에 따라 인덱스의 순서가 정렬되어 있는 것을 볼 수 있습니다. 비교 문자열. 🎜퍼지 쿼리를 수행할 때 %를 앞에 넣으면 가장 왼쪽 n 글자가 퍼지되고 불확실해집니다. 인덱스의 순서에 따라 특정 인덱스를 정확하게 찾을 수는 없습니다. 조건을 만족하는 데이터입니다. (가장 왼쪽 접두어의 기본 원칙)🎜🎜 조인트 인덱스를 사용할 때도 마찬가지입니다. 인덱스 순서 규칙을 위반하면 인덱스도 유효하지 않습니다. 전체 테이블 스캔. 🎜예: 테이블 예에 결합된 인덱스가 있습니다: (A, B, C) 🎜SELECT * FROM 예 WHERE A=1 and B =1 and C=1; Can 인덱스를 사용할 수 있습니다. 🎜SELECT A FROM 예제 WHERE C =1 및 B=1 ORDER BY A; Can 인덱스(포함 인덱스 사용) 🎜SELECT * FROM 예제 WHERE C =1 및 B=1 ORDER BY A; >아니요색인 사용🎜

🎜🎜Covered index:🎜색인에는 쿼리 요구 사항을 충족하는 모든 데이터가 포함되어 있으며 Covering Index(Covering Index)라고 합니다🎜

🎜두 가지 방법이 있습니다< code>최적화: 🎜첫 번째는 커버링 인덱스를 사용하는 것이고, 두 번째는 %를 맨 뒤에 넣는 것입니다. 🎜🎜2. 필드 유형은 문자열이며 where는 따옴표로 묶지 않습니다. 테이블의 필드는 문자열 유형이며 B+ 트리의 일반 인덱스입니다. 쿼리 조건이 숫자를 전달하면 인덱스가 생성되지 않습니다. 🎜예: 테이블 예제에는 varchar 유형의 pid라는 필드가 있습니다. 🎜

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE Date_ADD(birthtime，INTERVAL 1 DAY) = 6

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score-1=5

🎜첫 번째 문은 작은따옴표 없이 색인이 생성되지 않는 이유는 무엇인가요? 이는 작은따옴표를 추가하지 않으면 문자열과 숫자 사이의 비교가 이루어지기 때문입니다. MySQL은 비교하기 전에 암시적 유형 변환을 수행하고 이를 부동 소수점 숫자로 변환합니다. 🎜🎜3.OR 전후에 인덱스가 아닌 열이 있으면 인덱스가 실패합니다. 쿼리 조건에 또는가 포함되어 있으면 인덱스가 실패할 수 있습니다. 🎜예: 테이블 예에는 pid가 int 유형이고 Score가 int 유형인 필드가 있습니다. 🎜

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score != 2

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score <> 3

🎜OR 뒤에 인덱스가 없는 점수가 나오는 경우에는 p_id의 인덱스를 취한다고 가정하지만, 점수 쿼리 조건의 경우 여전히 테이블 전체를 스캔해야 하므로 전체 테이블 스캔 + 인덱스 스캔 + 병합의 3단계 프로세스가 필요합니다. 🎜MySQL에는 효율성과 비용 측면에서 OR 조건이 발생하면 인덱스가 실패할 수 있는 최적화 기능이 있습니다. 🎜🎜참고: OR 조건의 열이 색인화되면 색인이 손실될 수 있습니다. 🎜🎜4.조인트 인덱스(결합 인덱스), 쿼리 중 조건 열이 조인 인덱스의 첫 번째 열이 아니어서 인덱스가 무효화됩니다. 조인트 인덱스에서는 쿼리 조건이 가장 왼쪽 일치 원칙을 충족하면 인덱스가 정상적으로 적용됩니다. 🎜(k1,k2,k3)과 같은 결합 인덱스를 생성하는 것은 (k1), (k1,k2), (k1,k2,k3) 세 개의 인덱스를 생성하는 것과 동일합니다. 🎜예: pid가 먼저이고 점수가 두 번째인 공동 인덱스 idx_pid_score가 있습니다. 🎜

//此时执行语句type为range索引查询
explain SELECT * FROM example WHERE name is not null

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE name is not null OR  card is not null

//此时执行语句example表会走type为index类型索引，example_two则为ALL全表搜索不走索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

🎜공동 인덱스는 가장 왼쪽의 원칙을 만족하지 않아 일반적으로 인덱스가 실패하게 되는데, 이 역시 Mysql 옵티마이저와 관련이 있습니다. 🎜🎜5.계산, 함수, 유형 변환(자동 또는 수동)으로 인해 인덱스 오류가 발생합니다. 인덱스 필드에 (!= 또는 < >, not in)을 사용하면 인덱스 오류가 발생할 수 있습니다. 🎜Birthtime은 인덱싱되지만 mysql 내장 함수 Date_ADD()를 사용하기 때문에 인덱싱되지 않습니다. 🎜예: 테이블 예에서 idx_birth_time 인덱스는 datetime 유형의 birthdaytime 필드입니다🎜

//此时执行语句example表会走type为index类型索引，example_two会走type为ref类型索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

🎜 인덱스 열에도 연산(예: +, -, *, /)이 있어 인덱스가 무효화됩니다. 🎜예: 테이블 예에 int 유형 점수 필드 인덱스 idx_score가 있습니다🎜

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score-1=5

还有不等于(!= 或者<>)导致索引失效。
例子：在表example中有int类型的score字段索引idx_score

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score != 2

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score <> 3

虽然score 加了索引，但是使用了！= 或者 < >，not in这些时，索引如同虚设。
6. is null可以使用索引，is not null无法使用索引。
例子:在表example中有varchar类型的name字段索引idx_name，varchar类型的card字段索引idx_card。

//此时执行语句type为range索引查询
explain SELECT * FROM example WHERE name is not null

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE name is not null OR  card is not null

7.左连接查询或者右连接查询查询关联的字段编码格式不一样。两张表相同字段外连接查询时字段编码格式不同则会不走索引查询。
例子:在表example中有varchar类型的name字段编码是utf8mb4,索引为idx_name
在表example_two中有varchar类型的name字段编码为utf8,索引为idx_name。

//此时执行语句example表会走type为index类型索引，example_two则为ALL全表搜索不走索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

当把两表的字段类型改为一致时:

//此时执行语句example表会走type为index类型索引，example_two会走type为ref类型索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

所以字段类型也会导致索引失效
8.mysql估计使用全表扫描要比使用索引快,则不使用索引。当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。
建议:不要给’性别’等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。
Mysql出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快，这跟它的优化器有关。

위 내용은 MySQL 인덱스 오류를 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!