MySQL 고급 학습: 효율적이고 적절한 인덱스를 생성하는 방법에 대한 자세한 설명

이 기사는 MySQL에 대한 고급 연구로서 보다 적합한 인덱스를 만드는 방법에 대한 자세한 이해를 제공할 것입니다.

라이브러리에 데이터가 많을 때 인덱싱의 중요성을 모르고, 라이브러리에 데이터가 많을 때 적절한 인덱스의 중요성도 모릅니다. 이 글에서는 효율적이고 적절한 인덱스를 만드는 방법을 소개합니다. [관련 권장 사항: mysql 동영상 튜토리얼]

1. 인덱스 열을 사용하여 쿼리할 때는 표현식을 사용하지 말고 데이터베이스 계층 대신 비즈니스 계층에 계산을 넣으세요

아래 표시된 두 SQL의 결과는 다음과 같습니다. 동일하지만 두 SQL의 실행 계획이 다릅니다. 표현식이 실행 계획에 영향을 미치는 const의 actor_id+4보다 효율성이 훨씬 낮습니다. 자세한 설명을 해주세요

2. 기본 키 쿼리는 다른 인덱스 대신에 테이블 백 쿼리를 발생시키지 않습니다.

모든 테이블에는 기본적으로 기본 키가 있으므로 일반적인 개발에서는 인덱스를 사용할 수 있으면 인덱스를 사용하고, 사용할 수 있으면 기본 키 인덱스를 사용합니다.

3. 접두사 인덱스를 사용하세요

우리 인덱스는 실제로 문자열인 경우가 많으며 긴 문자열이 필연적으로 나타나기 때문에 인덱스가 너무 많은 공간을 차지하고 효율성이 떨어집니다. 특히 blob, text 및 varchar와 같은 긴 열의 경우. 이때 대처 방법은 해당 필드의 전체 값을 인덱스로 사용하는 것이 아니라 전반부만 취하는 것입니다(선택한 프리픽스 인덱스의 선택도는 전체 열에 가깝습니다). 이는 인덱스 공간을 크게 줄여 효율성을 향상시킬 수 있습니다. 단점은 인덱스의 선택성이 감소한다는 것입니다.

인덱스 선택성: 전체 데이터 테이블 레코드 수(#T)에 대한 고유 인덱스 값의 비율(1/#T ~ 1)입니다. 인덱스의 선택성이 높을수록 데이터의 차별화가 높고 더 많은 행을 필터링할 수 있으므로 쿼리 효율성이 높아집니다. 고유 인덱스의 선택도는 1이며 성능이 가장 좋습니다.

예를 들어 회사 직원 테이블의 이메일 필드에서 회사의 이메일 접미사는 xxxx@qq.com와 같이 모두 동일합니다. 실제로 이메일을 인덱스로 사용할 때 유일하게 유효한 부분은 xxxx 부분입니다. @qq.com도 마찬가지고 인덱스에는 의미가 없습니다. 물론 xxxx를 인덱스로 사용하는 것만으로도 전체 값과 동일한 선택성을 가지지만 xxxx를 인덱스로 사용하면 인덱스 공간이 분명히 줄어듭니다.

아래에서는 직원 테이블을 예로 들었습니다(테이블 구조 및 데이터에 대해서는 기사 끝 부분 참조)

인덱싱할 이메일 필드를 예로 사용합니다.

이 데이터의 이메일 주소는 실제로 모바일입니다. 전화번호 +@qq.com를 예로 들어보면 사실 처음 11자리와 그 다음은 모두 동일합니다. 다음 SQL을 사용하여 이러한 데이터의 선택성 계산을 확인합니다(처음 10, 11, 12를 각각 취함).

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

위 그림을 보면 상위 10위, 상위 11위, 상위 12위의 선택도가 각각 0.14, 1.0, 1.0인 것을 알 수 있는데, 11위 위치에서 지수 선택도가 가장 높은 1이므로 거기에 있습니다. all을 사용할 필요가 없습니다. 인덱스로 인덱스 공간이 늘어납니다.

-- 创建前缀索引
alter table employee add key(email(11));

count를 사용하여 통계의 빈도를 계산할 수도 있습니다(발생 횟수가 적을수록 반복률은 낮아지고 선택성은 높아집니다)

-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;

4 정렬에는 인덱스 스캔을 사용합니다

정렬을 자주 사용합니다. 필요에 따라 order by를 사용하지만 order by는 데이터를 메모리에 로드하여 정렬합니다. 데이터 양이 너무 많아 메모리에 저장할 수 없으면 여러 번만 처리할 수 있습니다. 그러나 인덱스 자체는 정렬되어 있으므로 인덱스를 통해 직접 정렬을 완료하는 것이 더 쉽습니다.

한 인덱스 레코드에서 다음 레코드로 이동하기만 하면 되기 때문에 인덱스 자체를 스캔하는 것은 빠르지만, 쿼리에 필요한 모든 열을 인덱스가 포함할 수 없는 경우 스캔할 때마다 테이블로 돌아가야 합니다. 인덱스 레코드 해당 행을 한 번 쿼리합니다. 이는 기본적으로 Random IO입니다. 따라서 인덱스 순서로 데이터를 읽는 것은 일반적으로 순차적 전체 테이블 스캔보다 느립니다.

MySQL은 동일한 인덱스를 사용하여 행 정렬과 검색을 모두 만족시킬 수 있습니다. 가능하다면 그러한 인덱스를 생성하는 것을 고려해 보십시오.

인덱스 열 순서가 order by 절의 순서와 완전히 일치하고 모든 열의 정렬 방향(역방향 또는 정방향)이 동일한 경우에만 MySQL은 인덱스를 사용하여 결과를 정렬할 수 있습니다. 쿼리가 여러 테이블과 관련되어야 하는 경우 order by 절의 필드가 모두 첫 번째 테이블에 있는 경우에만 인덱스 정렬을 사용할 수 있습니다. 쿼리별 순서도 결합된 인덱스의 가장 왼쪽 접두사를 충족해야 하며, 그렇지 않으면 인덱스 정렬을 사용할 수 없습니다.

실제로 개발 중에 주의해야 할 두 가지 주요 사항이 있습니다.

• where 조건의 필드와 order by 필드는 인덱스를 결합하여 가장 왼쪽 접두사를 만족할 수 있습니다.
• Desc와 asc 순서는 일관되어야 합니다.

5. Union all, in 또는 all은 인덱스를 사용할 수 있지만 in

을 사용하는 것이 좋습니다. 위와 같이 Union all은 두 번, in 또는 한 번만 실행됩니다. 동시에 or 와 in 의 실행 계획은 동일하다는 것을 알 수 있지만,

실행 시간을 살펴보고 있습니다. 아래와 같이 자세한 시간을 보려면 set profiling=1을 사용하고, 특정 시간을 보려면 showprofiles를 사용하면 됩니다. 아래 그림을 보면 or의 시간은 0.00612000, in의 시간은 0.00022800임을 알 수 있습니다. 여전히 격차가 매우 큽니다(테스트 테이블 데이터는 200행만 있음)set profiling=1可以看到详细时间，使用show profiles 查看具体时间。下图看出or的时间0.00612000，in的时间0.00022800，差距还是很大的(测试的表数据只有200行)

union all: 查询分为了两阶段，其实还有一个union，在平时开发中必须使用到union的时候推荐使用union all，因为union中多出了distinct去重的步骤。所以尽量用union all。

6. 范围列可以用到索引

范围的条件：>,>=,<,<=,between

范围列可以用到索引，但是范围列后面的列就无法用到索引了(索引最多用于一个范围列)

比如一个组合索引age+name 如果查询条件是where age>18 and name="纪"后面的name是用不到的索引的。

曾经面试被问到不等于是否能够走某个索引，平时没有注意过也没有回答成功，这次亲自做个实验，关于结论请看文末。

7. 强制类型转换会全表扫描

我在employee表中定义了mobile字段是varchar类型且建立索引，我分别用数字和字符串查询.

看看结果: 两者type是不一样的，而且只有字符串才用到索引。

如果条件的值的类型和表中定义的不一致，那么mysql会强制进行类型转换，但是结果是不会走索引，索引在开发中我们需要根据自己定义的类型输入对应的类型值。

8. 数据区分度不高，更新频繁的字段不宜建立索引

• 索引列更新会变更B+树的，频繁更新的会大大降低数据库性能。
• 类似于性别这类(只有男女，或者未知)，不能有效过滤数据。
• 一般区分度在80%以上就可以建立索引，区分度可以使用count(distinct(列名))/count(*)

9. 创建索引的列不允许为null，可能会得到不符合预期的结果

也就是建立索引的字段尽量不要为空，可能会有些意想不到的问题，但是实际工作中也不太可能不为空，所以根据实际业务来处理吧，尽量避免这种情况。

10. 当需要进行表连接的时候，最好不要超过三张表

表连接其实就是多张表循环嵌套匹配，是比较影响性能的， 而且需要join的字段数据类型必须一致，提高查询效率。关于表连接原理后面专门写一篇吧。

11. 能使用limit的时候尽量使用limit。

limit的作用不是仅仅用了分页，本质作用是限制输出。

limit其实是挨个遍历查询数据，如果只需要一条数据添加 limit 1的限制，那么索引指针找到符合条件的数据之后就停止了，不会继续向下判断了，直接返回。如果没有limit，就会继续判断。

但是如果分页取1万条后的5条limit 10000,10005

union all: 쿼리는 두 단계로 나누어집니다. 실제로, 또한 일상적인 개발에 반드시 사용해야 하는 유니온도 있습니다. 유니온에는 별도의 중복 제거 단계가 추가되므로 유니온 시에는 유니온 all을 사용하는 것이 좋습니다. 그러니 Union All을 사용해 보세요.

6. 범위 열을 인덱싱에 사용할 수 있습니다.

범위 조건: >,>=,<,<=,between

범위 열을 사용할 수 있습니다. 인덱스이지만 범위 열 이후의 열은 인덱스를 사용할 수 없습니다(인덱스는 최대 하나의 범위 열에 사용할 수 있습니다)

예를 들어 쿼리 조건이 where age>18이고 name="Ji"< /code> 뒤의 이름은 사용되지 않은 색인입니다. <p></p></blockquote>한번 인터뷰에서 특정 지표를 사용할 수 있다는 뜻인지 질문을 받았습니다. 저는 그것에 주의를 기울이지 않았거나 성공적으로 대답했습니다. 이번에는 제가 직접 실험을 해봤습니다. 결론. <p><strong><h3 data-id="heading-6">7. 강제 유형 변환은 테이블 전체를 스캔합니다</strong></p>직원 테이블의 <code>mobile 필드를 varchar 유형으로 정의하고 인덱스를 생성했습니다. 숫자와 문자열을 사용하여 쿼리하세요.

---

결과를 보세요. 두 가지 유형이 다르며 문자열만 인덱스를 사용합니다.

🎜조건 값의 유형이 테이블에 정의된 유형과 일치하지 않으면 mysql은 강제로 유형 변환을 수행하지만 결과는 색인화되지 않습니다. 인덱스 개발 중에 유형에 따라 해당 유형 값을 입력해야 합니다. 우리 스스로 정의했습니다. 🎜🎜🎜

8. 데이터 차이가 높지 않으며 자주 업데이트되는 필드는 색인화되어서는 안 됩니다. 인덱스 열 업데이트는 B+ 트리를 변경하고 빈번한 업데이트는 데이터베이스 성능을 크게 저하시킵니다. 🎜🎜성별(남성과 여성만 또는 알 수 없음)과 유사하게 데이터를 효과적으로 필터링할 수 없습니다. 🎜🎜일반적으로 구별도가 80% 이상일 때 인덱스를 생성할 수 있으며, 구별을 위해 count(distinct(열 이름))/count(*)를 사용할 수 있습니다🎜🎜

9. 인덱스를 생성하려면 컬럼은 null이 허용되지 않으며 예상치 못한 결과가 발생할 수 있습니다🎜🎜즉, 인덱스된 필드는 최대한 비어 있으면 안 되지만 실제 작업에서는 예상치 못한 문제가 발생할 수 있습니다. 비어 있지 않을 가능성이 높으므로 실제 상황에 따라 비즈니스를 처리하고 이러한 상황을 피하도록 노력하십시오. 🎜

10. 테이블 연결이 필요한 경우 테이블 3개를 초과하지 않는 것이 가장 좋습니다. 🎜🎜테이블 연결은 실제로 여러 테이블의 루프 중첩 매칭이므로 성능에 영향을 줍니다. 쿼리 효율성을 높이려면 조인해야 하는 필드의 데이터 유형이 일관되어야 합니다. 나중에 테이블 연결 원리에 관한 특집 기사를 작성해 보겠습니다. 🎜

11. 가능하면 제한을 사용해 보세요. 🎜🎜리미트의 기능은 단순한 페이징이 아니라, 필수 기능은 출력을 제한하는 것입니다. 🎜🎜limit는 실제로 쿼리 데이터를 하나씩 순회하며 데이터가 하나만 필요하고 limit 1의 제한을 추가하면 조건을 충족하는 데이터를 찾은 후 인덱스 포인터가 중지됩니다. 계속해서 아래쪽으로 판단하지 않습니다. 제한이 없으면 판결은 계속됩니다. 🎜🎜하지만 페이징하여 10,000개 항목 limit 10000,10005 이후 5개 항목을 가져오는 경우 10,000개 항목을 순회하고 5개 항목을 가져오므로 매우 비효율적입니다. 팁: 기본 키가 순차적인 경우 기본 키를 통해 직접 페이지가 매겨진 데이터를 얻을 수 있습니다. 🎜🎜12. 단일 테이블 인덱스 수는 5개 이내로 조절하세요🎜🎜인덱스를 생성/유지하는 데에도 비용이 들고 공간도 차지합니다. 인덱스는 많을수록 합리적으로 사용해야 합니다. 🎜🎜13. 단일 결합 인덱스의 필드 수는 5를 초과할 수 없습니다.🎜🎜필드가 많을수록 인덱스가 커지고 저장 공간도 늘어납니다. 🎜🎜🎜인덱스는 많을수록 좋으며, 테이블 구축을 시작할 때 인덱스를 설계할 필요는 없습니다. 성급한 최적화는 효율적인 인덱스가 아닙니다. 관련 비즈니스 SQL 그런 다음 관련 인덱스를 구축하면 보다 신중하게 생각하고 보다 효과적이고 효율적인 인덱스를 구축할 수 있습니다. 🎜🎜위는 인덱스 최적화에 해당하는 소소한 사항인데, 스위스 SQL 작성에 도움이 되길 바랍니다.🎜🎜🎜🎜보충🎜🎜🎜🎜인덱싱 여부에 대한 질문🎜

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

위 내용은 MySQL 고급 학습: 효율적이고 적절한 인덱스를 생성하는 방법에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!