MySQL 인덱스의 가장 왼쪽 접두사 원칙은 무엇입니까?

머리말

가장 왼쪽에 접두사 인덱스가 있는 이유

최종적으로 분석해 보면 mysql의 데이터베이스 구조 B+트리

실제 문제에서는 예를 들어

index(a, b, c) 세 개의 필드가 있습니다.

쿼리 문 select * from table where c = '1'을 사용하면 sql 문은 인덱스 인덱스를 사용하지 않습니다. select * from table where c = '1' ，sql语句不会走index索引的

select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

1. 定义

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

为了更好辨别这种情况，通过建立表格以及索引的情况进行分析

2. 全索引顺序

建立一张表，建立一个联合索引，如果顺序颠倒，其实还是可以识别的，但是一定要有它的全部部分

建立表

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

建立索引ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

索引的顺序位name-age-pos

显示其索引有没有show index from staffs;

通过颠倒其左右顺序，其执行都是一样的

主要的语句是这三句

1. explain select *from staffs where name='z3'and age=22 and pos='manager';

2. explain select *from staffs where pos='manager' and name='z3'and age=22;

3. explain select *from staffs where age=22 and pos='manager' and name='z3';

以上三者的顺序颠倒，都使用到了联合索引

最主要是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划

不论以何种顺序都可使用到联合索引

3. 部分索引顺序

3.1 正序

如果是按照顺序（缺胳膊断腿的），都是一样的

1. explain select *from staffs where name=‘z3’;

2. explain select *from staffs where name='z3’and age=22;

3. explain select *from staffs where name='z3’and age=22;

其type都是ref类型，但是其字段长度会有微小变化，也就是它定义的字长长度变化而已

3.2 乱序

如果部分索引的顺序打乱

1. 只查第一个索引explain select *from staffs where name='z3';

2. 跳过中间的索引 explain select *from staffs where name='z3' and pos='manager';

3. 只查最后的索引 explain select *from staffs where pos='manager';

可以发现正序的时候

如果缺胳膊少腿，也是按照正常的索引

即使跳过了中间的索引，也是可以使用到索引去查询

但是如果只查最后的索引

type就是all类型，直接整个表的查询了（这是因为没有从name一开始匹配，直接匹配pos的话，会显示无序，）

有些时候type就是index类型，这是因为还是可以通过索引进行查询

index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描

4. 模糊索引

类似模糊索引就会使用到like的语句

所以下面的三条语句

如果复合最左前缀的话，会使用到range或者是index的类型进行索引

1. explain select *from staffs where name like '3%'; 最左前缀索引，类型为index或者range

2. explain select *from staffs where name like '%3%'; 类型为all，全表查询

3. explain select *from staffs where name like '%3%';

   select * from table where b =‘1’ and c ='2' 이 문은 인덱스 인덱스로 이동하지 않습니다

1. 정의

가장 왼쪽 접두사 일치 원칙: MySQL은 결합 인덱스를 만들 때 가장 왼쪽 접두사 일치 원칙을 따릅니다. 는 공동 인덱스에서 데이터를 검색할 때 가장 왼쪽부터 매칭이 시작됩니다

이 상황을 더 잘 파악하려면 테이블 및 인덱스 생성 상황을 분석하세요

2. 전체 인덱스 순서

테이블을 생성하고 생성합니다. 순서가 반대인 경우에도 식별 가능하지만 모든 부분이 있어야 합니다

🎜테이블 생성🎜

CREATE TABLE IF NOT EXISTS article(
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) UNSIGNED NOT NULL,
	category_id INT(10) UNSIGNED NOT NULL,
	views INT(10) UNSIGNED NOT NULL,
	comments INT(10) UNSIGNED NOT NULL,
	title VARCHAR(255) NOT NULL,
	content TEXT NOT NULL
);

INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

🎜인덱스 생성ALTER TABLE Staffs ADD INDEX index_staffs_nameAgePos(name,age,pos); 🎜🎜색인 순서 name-age-pos🎜🎜색인 표시 show index from Staffs;🎜🎜🎜🎜왼쪽과 오른쪽 순서를 반대로 하여 실행은 동일🎜🎜주요 문장은 다음 세 문장입니다🎜

1. 🎜설명 select *from Staffs where name='z3'and age=22 and pos='manager ';🎜🎜
2. 🎜설명 *pos='manager' 및 name='z3'and age=22인 직원 중에서 선택;🎜🎜
3. 🎜 *나이=22, 위치='관리자', 이름='z3'인 직원 중에서 선택하세요.🎜🎜🎜🎜🎜🎜위 3가지의 순서를 반대로 하여 조인트 인덱스를 사용합니다. 주된 이유는 MySQL에는 쿼리 최적화 프로그램 설명이 있으므로 SQL 문의 필드 순서는 통합과 결합될 필요가 없습니다. 인덱스에 의해 정의된 필드는 동일한 순서로 되어 있습니다. 이 SQL 문을 수정하는 것이 가장 효율적이며 최종적으로 실제 실행 계획을 생성합니다🎜🎜조인트 인덱스는 순서에 관계없이 사용할 수 있습니다🎜🎜3. 부분 인덱스 순서🎜

   3.1 양수

   🎜에 있는 경우 주문(팔다리 없음) 동일합니다🎜
   1. 🎜설명 *이름=‘z3&rsquo인 직원 중에서 선택;;🎜 🎜
   2. 🎜설명 *이름='z3’나이=22인 직원 중에서 선택🎜🎜🎜설명 *이름='z3’나이인 직원 중에서 선택 =22;🎜🎜🎜🎜🎜🎜유형은 모두 ref 유형이지만 필드 길이는 약간 변경됩니다. 즉, 정의하는 단어 길이만 변경됩니다🎜

      3.2 순서가 잘못되었습니다

      🎜 일부 색인이 잘못되었습니다🎜
      1. 🎜첫 번째 색인만 확인하세요설명 select * from Staffs where name='z3';🎜🎜🎜중간 색인 건너뛰기explain select *from name='z3' and pos='manager';🎜 🎜
      2. 🎜마지막 색인만 확인explain select *from pos='manager'; 🎜🎜🎜🎜🎜🎜양수열을 찾을 수 있습니다🎜🎜팔, 다리가 없어도 일반 인덱스를 따라갈 수 있습니다🎜🎜 중간 인덱스를 생략하더라도 계속 사용할 수 있습니다 index to query🎜🎜근데 마지막 index만 조회하면🎜🎜type이 all type이면 테이블 전체를 직접 조회하게 된다. 순서대로 표시됨,)🎜🎜때때로 유형이 인덱스 유형인 경우가 있는데, 이는 여전히 인덱스를 통해 쿼리할 수 있기 때문입니다.🎜🎜index는 모든 인덱스 트리를 스캔하는 반면 all은 전체 테이블에서 전체 디스크의 데이터를 스캔합니다. 🎜🎜4. index🎜🎜유사한 퍼지 인덱스는 like 문을 사용합니다🎜🎜그래서 다음 세 문은🎜🎜가장 왼쪽 접두사와 결합하면 범위 또는 인덱스 유형이 인덱싱에 사용됩니다🎜
         1. 🎜설명 *from 이름이 '3%'인 직원에서 선택; 가장 왼쪽 접두사 색인, 유형은 색인 또는 범위입니다🎜🎜
         2. 🎜설명 *from 직원에서 이름 선택 like '%3%';유형은 모두, 전체 테이블 쿼리🎜🎜
         3. 🎜설명 *이름이 '%3%';인 직원 중에서 선택, 유형은 모두, 전체 테이블 쿼리🎜🎜🎜🎜🎜🎜🎜5. 범위 인덱스🎜🎜여러 필드를 쿼리할 때 중간에 범위가 있는 경우 인덱스를 삭제하고 중간 인덱스를 제거하는 것이 좋습니다🎜 🎜구체적인 아이디어는 다음과 같습니다. 🎜

            建立一张单表

            CREATE TABLE IF NOT EXISTS article(
            	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
            	author_id INT(10) UNSIGNED NOT NULL,
            	category_id INT(10) UNSIGNED NOT NULL,
            	views INT(10) UNSIGNED NOT NULL,
            	comments INT(10) UNSIGNED NOT NULL,
            	title VARCHAR(255) NOT NULL,
            	content TEXT NOT NULL
            );
            
            INSERT INTO article(author_id,category_id,views,comments,title,content)
            VALUES
            (1,1,1,1,'1','1'),
            (2,2,2,2,'2','2'),
            (1,1,3,3,'3','3');

            经过如下查询：

            explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

            

            发现其上面的单表查询，不是索引的话，他是进行了全表查询，而且在extra还出现了Using filesort等问题

            所以思路可以有建立其复合索引

            具体建立复合索引有两种方式：

            1. create index idx_article_ccv on article(category_id,comments,views);

            2. ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

            

            但这只是去除了它的范围，如果要去除Using filesort问题的话，还要将其中间的条件范围改为等于号才可满足

            发现其思路不行，所以删除其索引 DROP INDEX idx_article_ccv ON article;

            

            主要的原因是：

            这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

            当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

            所以建立复合索引是对的

            但是其思路要避开中间那个范围的索引进去

            只加入另外两个索引即可create index idx_article_cv on article(category_id, views);

            

위 내용은 MySQL 인덱스의 가장 왼쪽 접두사 원칙은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!