MySQL 인덱스 최적화 사례 분석
- PHPz앞으로
- 2023-05-28 12:25:58773검색
데이터 준비
CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW()); ‐‐ 插入一些示例数据 drop procedure if exists insert_emp; delimiter ;; create procedure insert_emp() begin declare i int; set i=1; while(i<=100000)do insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev'); set i=i+1; end while; end;; delimiter ; call insert_emp();
위와 같이 기본 키 인덱스와 (이름, 나이, 직위) 조인트 인덱스가 있는 employees 테이블이 있습니다. employees 表，有主键索引和 (name, age, position ) 联合索引, 看下面的查询示例:
联合索引的首字段用范围查询
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描
强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高， 一般不会使用这个手段，除非有证据能证明强制走索引后效率大幅度提高
覆盖索引优化
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
将 select * 修改为 select name, age, posiion ， 只选择索引中已经存在的列，可以不用回表，所以会利用索引
in和or什么时候会走索引
在表数据量比较大的情况会走索引，数据量不多的情况下会选择全表扫描,示例如下:
in 查询
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
用到全部索引 or 查询
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
用到全部索引
下面新建一张 employees_copy 表，结构和 employee 一样，但数据只有三条, 再执行上面两个查询
in 查询
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
全表扫描
or查询
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
全表扫描
like xx% 一般都会走索引，和数据量无关
大表
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
小表
EXPLAIN SELECT * FROM employees_copy WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
可以看到，无论表的数据量大小，都会利用索引，为什么呢？
其实 like 用到了索引下推的优化
索引下推
对于辅助联合索引，正常情况下按照最左前缀原则， SELECT * from employees where name like 'LiLei%' and age = 22 and position = 'dev' 这种情况下只会走name字段的索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找没有用索引下推优化？
估计应该是Mysql认为范围查找过滤的结果集过大，like KK%보세요. 다음 쿼리 예:
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
결론: 결합 인덱스의 첫 번째 필드에 범위 검색을 사용하고 인덱스를 거치지 마세요. MySQL은 내부적으로 첫 번째 필드에 범위를 사용
한다고 생각할 수 있습니다. 🎜🎜강제 인덱싱🎜{
"steps": [
{
"join_preparation": { //第一阶段：SQL准备阶段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { //第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { //条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ //表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ //预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { //全表扫描情况
"rows": 10123, //扫描行数
"cost": 2054.7 //查询成本
} /* table_scan */,
"potential_range_indexes": [ //查询可能使用的索引
{
"index": "PRIMARY", //主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", //辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { //分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" //索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, //使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, //是否使用覆盖索引
"rows": 5061, //索引扫描行数
"cost": 6074.2, //索引使用成本
"chosen": false, //是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { //最优访问路径
"considered_access_paths": [ //最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", //访问类型：为scan，全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, //确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { //第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
// 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描🎜mysql> select * from employees where name > 'zzz' order by position; mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; # 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描 mysql> set session optimizer_trace="enabled=off"; //关闭trace🎜
🎜🎜 수정 *를 선택하여 이름, 나이, 위치를 선택하고 인덱스만 선택합니다. 테이블에 이미 존재하는 열은 테이블로 반환할 필요가 없으므로 인덱스가 사용됩니다🎜🎜 in 및 or은 인덱스를 사용할 때 사용됩니다🎜🎜🎜테이블의 데이터 양이 상대적으로 클 경우 인덱스가 사용되며, 데이터 양이 적을 경우 전체 인덱스가 선택됩니다. 예는 다음과 같습니다: 🎜🎜🎜🎜in query🎜🎜select * from employees limit 10000,10;🎜
🎜🎜모든 인덱스 사용 🎜또는 쿼리🎜🎜select * from employees where id > 90000 limit 5;🎜
🎜🎜모든 인덱스 사용🎜🎜아래에 새로운 Employees_copy 테이블을 생성합니다. 구조는 직원과 동일하지만 데이터 항목이 3개뿐입니다. 그런 다음 쿼리에서 위의 두 쿼리🎜🎜🎜를 실행합니다🎜🎜 EXPLAIN select * from employees ORDER BY name limit 90000,5;🎜🎜🎜전체 테이블 스캔🎜🎜🎜 또는 쿼리🎜 🎜
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;🎜
🎜🎜전체 테이블 scan🎜🎜 xx%가 일반적으로 인덱싱을 사용하는 것처럼 데이터 양과는 아무런 관련이 없습니다🎜🎜큰 테이블🎜CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
-- 插入一些示例数据
-- 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
-- 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();🎜
🎜🎜작은 테이블🎜EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;🎜
🎜🎜테이블의 데이터 크기에 상관없이 인덱스가 사용되는 것을 알 수 있는데 왜일까요? 🎜🎜실제로 인덱스 푸시다운 최적화를 사용하는 것과 같습니다🎜🎜인덱스 푸시다운🎜🎜보조 관절 인덱스의 경우 일반적인 상황에서는 가장 왼쪽 접두사 원칙에 따라 SELECT * 이름이 'LiLei%'와 같은 직원 중에서 age = 22 and position = 'dev' 이 경우 이름 필드에 따라 필터링한 후 얻은 인덱스 행의 나이와 위치가 어긋나기 때문에 이름 필드의 인덱스만 사용됩니다. , 인덱스를 잘 사용할 수 없습니다. 🎜🎜MySQL5.6 이전 버전에서는 이 쿼리는 조인트 인덱스에서 'LiLei'로 시작하는 인덱스만 일치시키고, 이 인덱스에 해당하는 기본 키를 하나씩 테이블에 반환할 수 있었습니다. 기본 키 인덱스 해당 레코드를 찾은 다음 age와 position 두 필드의 값을 비교하여 일치하는지 확인합니다. 🎜🎜MySQL 5.6에서는 인덱스 순회 과정에서 🎜인덱스에 포함된 모든 필드를 먼저 판단하고 조건에 맞지 않는 레코드를 필터링한 다음 테이블로 반환🎜하여 효과적으로 숫자를 줄일 수 있습니다. 테이블 반환. 인덱스 푸시다운 최적화를 사용한 후 위 쿼리는 공동 인덱스에서 이름이 'LiLei'로 시작하는 인덱스를 일치시키고 인덱스에서 두 필드 age 및 position을 필터링한 다음 해당 기본 키 ID를 필터링합니다. 전체 데이터 행을 확인하기 위해 테이블로 돌아왔습니다. 🎜🎜인덱스 푸시다운은 테이블 반환 횟수를 줄입니다. innodb 엔진의 테이블 인덱스 푸시다운은 보조 인덱스에만 사용할 수 있습니다. innodb의 기본 키 인덱스(클러스터형 인덱스)는 리프 노드에 전체 행 데이터를 저장합니다. 시간 인덱스 푸시다운은 전체 데이터 행을 쿼리할 필요성을 줄이지 않습니다. 🎜🎜색인 푸시다운으로 범위 검색이 최적화되지 않는 이유는 무엇입니까? 🎜🎜Mysql은 범위 검색 및 필터링의 결과 집합이 너무 크다고 생각하는 것으로 추정됩니다. like KK% 대부분의 경우 필터링된 결과 집합이 상대적으로 작으므로 여기서 Mysql은 다음과 같은 결과를 제공하기로 선택합니다. 물론 KK% 지수 푸시다운 최적화가 사용됩니다. 물론 이는 절대적인 것은 아닙니다. 때로는 KK%가 지수 푸시다운으로 이어지지 않을 수도 있습니다. 🎜🎜색인 선택 방법🎜🎜🎜먼저 다음 두 쿼리를 살펴보세요.🎜🎜
同样的表，同样的字段，因为条件的不同，选择的索引也不同，MySQL 是如何选择的呢？
Trace 工具
MySQl 提供了一个工具，可以看到选择索引的计算过程， 用法如下:
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace mysql> select * from employees where name > 'a' order by position; mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
下面是对 trace 字段的解析
{
"steps": [
{
"join_preparation": { //第一阶段：SQL准备阶段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { //第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { //条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ //表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ //预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { //全表扫描情况
"rows": 10123, //扫描行数
"cost": 2054.7 //查询成本
} /* table_scan */,
"potential_range_indexes": [ //查询可能使用的索引
{
"index": "PRIMARY", //主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", //辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { //分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" //索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, //使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, //是否使用覆盖索引
"rows": 5061, //索引扫描行数
"cost": 6074.2, //索引使用成本
"chosen": false, //是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { //最优访问路径
"considered_access_paths": [ //最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", //访问类型：为scan，全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, //确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { //第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
// 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描mysql> select * from employees where name > 'zzz' order by position; mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; # 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描 mysql> set session optimizer_trace="enabled=off"; //关闭trace
深入优化
order by 和 group by
order by 和 group by 也会遵循左前缀法则, 如下例子 ：
根据左前缀法则，用到了 name 字段的索引，同时使用 age 字段用来排序， 因为 extra 种没有 filesort
order by 或者 group by 用到的索引不会参与到 key_len 的计算，索引 key_len 仍然只是 74， 即 name字段的长度
再看下面一个例子:
where 条件是name 排序字段是 position 跳过了age字段，所以只能用 name 索引，无法利用 position 索引进行索引排序，用到是文件排序
再看第三个例子:
使用name条件查询， 同时使用 age position 双字段排序，没有跳过联合索引的字段. 所以可以用索引排序
然后颠倒一下排序顺序，先position 再 age：
发现此时只能文件排序了
再看下面的例子
虽然排序字段与索引字段不一样，但仍然是索引排序， 因为查询条件中 用到是 （name， age）索引，排序中用到是 position 索引，并没有颠倒顺序。所以还是索引排序
如果一个正序一个倒序呢？
虽然排序字段与索引字段顺序相同， 但是 age 是正序， position 是倒叙，导致与索引的排序方式不同，无法利用索引。Mysql8及以上版本可以使用降序索引来支持文件排序。
先 in 查询:
对于排序来说，多个相等条件也是范围查询, 无法利用索引排序
先范围查询:
这里发生了全表扫描，没有任何索引，排序自然也无法利用索引了，可以使用覆盖索引优化:
优化总结
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
Using filesort文件排序原理详解
单路排序模式；
是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示或者
双路排序（又叫回表排序模式）
是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模·式。
分页优化
常规的limit分页
有如下查询语句
select * from employees limit 10000,10;
该sql并不是只查询了10条，而是查找了10010条，然后把前10000条结果给舍弃掉, 因此要查询一个大表靠后的内容，执行效率是非常低的
优化
根据主键排序
上面的下面的sql语句没有指定排序方式，默认使用ID排序。当使用ID排序时，我们可以使用下面的优化。
select * from employees where id > 90000 limit 5;
如果id是连续自增的，和limit 90000,5 结果没有差别，是 90001 ~ 90005 的数据。
但是如果在90000之前删除了一条数据，结果就不一样了，id > 90000 limit 5 的结果是 90001 ~ 90005， 但是limit 90000, 5 的结果是 90002 ~ 90006， 很明显 90002 ~ 90006 才是符合我们直觉的。所以这个优化只能限制与排序条件是连续的。如果id不是自增的呢？会出现什么情况，假如 90000 这条数据有两个，limit 90000, 5 的结果是 90000 ~ 90004，而 id > 90000 limit 5 的结果仍是 90001 ~ 90005， 会把 id= 90000 的数据漏掉一条。
所以这个优化只能用于排序的字段是连续自增的，并且不能重复
非主键排序的优化
有如下查询语句
EXPLAIN select * from employees ORDER BY name limit 90000,5;
发现并没有用上name的索引，因为 select * ,扫描联合索引时，无法的到全部数据，需要回表，成本比全表扫描更高，所以优化器放弃使用索引。
可以使用索引覆盖的方法，使用分页查询仅仅找到少量的主键，然后在使用主键查找整行数据， 如下:
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
看下执行计划：
原 SQL 使用文件排序，优化后的使用索引排序
表关联优化
先造一些数据:
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
-- 插入一些示例数据
-- 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
-- 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();新建 t1 t2 表，结构一样， 都在a字段上有索引，b字段没有索引，t1表有 10000 行记录，t2表只有100条记录。
常见的表关联算法
内嵌循环连接算法 Nested-Loop Join
基于块的嵌套循环连接算法 Block Nested-Loop Join
内嵌循环连接算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
一般关联字段有索引的时候使用这种算法, 示例:
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
从执行计划中可以看到这些信息：
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表；优化器一般会优先选择小表做驱动表，用where条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表
当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
上面sql的大致流程如下：
从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。
整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行
基于块的嵌套循环算法
当关联字段没有没有索引的时候会使用这种算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
如下:
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下：
把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。
这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？·
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。
为什么要使用 BNLJ 算法呢?
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
通常情况下，MySQL会采用 BNL 算法进行关联查询，当被驱动表的关联字段没有索引时。当存在索引时，使用NLJ算法可以获得比BNL算法更好的性能
对于关联sql的优化
关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用
straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
straight_join只适用于inner join，并不适用于left join，right join。由于使用left join和right join已经明确了表的执行顺序
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
小表的定义
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。不单单是表的总数据量
in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
#等价于：
for(select id from B){
select * from A where A.id = B.id
}exists：当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引关于Exists
EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
count 查询优化
有下面四条查询语句：
EXPLAIN select count(1) from employees; EXPLAIN select count(id) from employees; EXPLAIN select count(name) from employees; EXPLAIN select count(*) from employees;
只有 count(字段名) 不会把该字段为null 计入总数
其实上面四条的查询计划都一样，效率上没有太大的差别
当字段有索引
count(*)≈count(1)>count(字段)>count(主键 id)
字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
当字段没有索引
count(*)≈count(1)>count(主键 id)>count(字段)
字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(1)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。
count(*)
count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
常见优化方法
自己维护的总行数
show table status 如果只需要知道表总行数的估计值可以用如下sql查询，性能很高 show table status like 'employee'
将总数维护到Redis里 插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性
索引设计原则
索引设计原则：
1、代码先行，索引后上
等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)
where 조건으로 검색할 때 이름 필드를 기준으로 검색하는 경우 먼저 이름 필드의 처음 20자를 기준으로 인덱스 트리에서 검색하고 일치 후 처음 20자의 접두사를 찾습니다. 부분 데이터인 경우 클러스터형 인덱스로 돌아가 비교를 위해 전체 이름 필드 값을 추출합니다.
그러나 이름별로 주문하면 이름이 색인 트리의 처음 20자만 포함하므로 이 정렬은 색인을 사용하는 것이 불가능합니다. 그룹별로
5 , 있는 경우에도 마찬가지입니다. where와 order by가 충돌하면 where에 우선순위가 부여됩니다.
where와 order by가 인덱스 디자인 충돌이 있는 경우 인덱스는 where를 위해 설계해야 할까요? or order by를 위해 설계해야 할까요? where를 인덱스로 사용해야 할까요, 아니면 인덱스로 정렬해야 할까요?
일반적으로 이때 지정된 데이터의 일부를 빠르게 필터링한 후 정렬하기 위해 인덱스를 사용하는 경우가 많습니다.
대부분의 경우 인덱스를 기반으로 필터링하면 원하는 데이터의 작은 부분을 가능한 한 빨리 필터링할 수 있기 때문에 정렬 비용이 훨씬 줄어들 수 있습니다.
예를 들어
이름, 나이, 성별, 직위 열과 공동 인덱스(이름, 나이, 성별, 직위)가 포함된 직원 테이블이 있습니다.
sex: Gender, 값은 0 또는 1
다음과 같이 쿼리: 이름 = 'zhangsan', 나이 = 18, 직위 = 'dev'인 직원에서 ID를 선택하세요. 성별 필드를 건너뛰었기 때문에 직위는 인덱스를 사용할 수 없습니다select id from employees where name = 'zhangsan' and age = 18 and position = 'dev' 因为跳过了 sex 字段，position 无法利用索引
因为 sex 只有两个取值，我们在查询语句上把 sex 的值全部枚举出来， 如下:
select id from employees where name = 'zhangsan' and age = 18 and sex in (0, 1) and position = 'dev'
这样一来就可以利用全部索引了。
再举个例子
加入我们要查询最近一周登录的用户，首先想到的是 last_login_time > {一周之前的时间}
왜냐하면 성별은 두 개의 값만 사용하면 다음과 같이 쿼리 문에서 성별의 모든 값을 열거합니다.
이름 = 'zhangsan', 나이 = 18, 성별은 (0, 1)인 직원에서 ID를 선택합니다. 그리고 position = 'dev'🎜🎜 이렇게 하면 모든 인덱스를 활용할 수 있습니다. 🎜🎜또 다른 예🎜🎜지난 주에 로그인한 사용자를 쿼리하려면 가장 먼저 떠오르는 것은 last_login_time > {time one week ago}입니다. 범위 쿼리, 다음 모두 해당 필드는 인덱스를 사용할 수 없습니다. 사용자가 최근에 로그인했는지 여부를 식별하기 위해 Recent_login_flag(tinyint)라는 다른 필드를 설계할 수 있습니다. 예약된 작업을 사용하여 이 필드의 값을 정기적으로 업데이트합니다. 이런 방식으로 범위 쿼리는 동등한 쿼리가 되며, 비즈니스의 허용 여부에 따라 데이터가 시간에 따라 변경되지 않을 수도 있습니다. 🎜🎜간단히 말하면, 인덱스를 최대한 활용할 수 있는 방법을 찾으면 됩니다. 🎜위 내용은 MySQL 인덱스 최적화 사례 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!