常见的情况讨论是否走索引
in和or在查询是否会选择走索引
表数据比较大
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
由上图可以知道是选择了走我们建的那条联合索引。
表数据比较小
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
不过在mysql8应该做有优化以选择了走索引
由上图所知在mysql8版本之前小表的数据是选择不走索引的，mysql8版本之后可以看出是选择了，走索引的。但是当数据库的量达到什么级别的时候算小表，什么级别的时候算大表需要查证。
总结，一般情况在实际的业务开发中，例如字典表等相关的。其他的表随着业务的增长应该都会到达大表的级别。但是当我们在实际的开发当用到了in和or的时候，需要谨慎去判别当前的数据量。是否会走索引。
like 'L%'的时候一般会选择走索引
EXPLAIN SELECT * FROM employees WHERE name like 'L%' AND age = 22 AND position ='manager';
这儿使用了一个索引下推的技术
联合索引第一个字段就是要范围查询
EXPLAIN select * from employees where name > 'a';
EXPLAIN select * from employees where name > 'zzz';
同样都是在索引的第一字段就是要范围查询，MySql最终是否选择走索引或者一张表设计多个索引，Mysql最终是如何选择索引的。我们可以通过trace工具去查看。
trance工具的用法
set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- trance里面的信息
{
"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": 996772, -- 扫描行数
"cost": 100457 -- 查询成本
} /* 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 */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_name_age_position",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_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": 498386, -- 索引扫描行数
"cost": 174435, -- 索引使用成本
"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": 996772,
"access_type": "scan", -- 访问类型 为scan 全表扫描
"resulting_rows": 996772,
"cost": 100454,
"chosen": true, -- 确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 996772,
"cost_for_plan": 100454,
"sort_cost": 996772,
"new_cost_for_plan": 1.1e6,
"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 */
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* simplifying_order_by */
} /* optimizing_distinct_group_by_order_by */
},
{
"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 */
},
{
"finalizing_table_conditions": [
{
"table": "`employees`",
"original_table_condition": "(`employees`.`name` > 'a')",
"final_table_condition ": "(`employees`.`name` > 'a')"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
},
{
"considering_tmp_tables": [
{
"adding_sort_to_table": "employees"
} /* filesort */
] /* considering_tmp_tables */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { -- 第三阶段 sql执行阶段
"select#": 1,
"steps": [
{
"sorting_table": "employees",
"filesort_information": [
{
"direction": "asc",
"expression": "`employees`.`position`"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"memory_available": 262144,
"key_size": 40,
"row_size": 190,
"max_rows_per_buffer": 1379,
"num_rows_estimate": 3178496,
"num_rows_found": 1000003,
"num_initial_chunks_spilled_to_disk": 327,
"peak_memory_used": 262144,
"sort_algorithm": "std::stable_sort",
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
-- 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
select * from employees where name > 'zzz' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
set session optimizer_trace="enabled=off"; -- 关闭trace
通过trance工具可以看一出，当时 name > 'a'的时候最后选择了，走全表扫描。
常见的sql优化
order By 和 group By的优化
索引：idx_name_age_position
情况一：三个字段都参与查询 中间字段排序
EXPLAIN select * from employees WHERE `name` = 'LiLei' and position = 'manager' order by age;
利用最左前缀法则可以看出，本次查询中使用到了name索引，从key_len=74，使用到了age进行排序，所以extra中出现了Using index condition（不完全被索引覆盖）
情况二：跳过中间字段最后一个字段进行排序
EXPLAIN select * from employees WHERE `name` = 'LiLei' order by position;
;利用最左前缀法则可以看出，本次查询中使用到了name索引，从key_len=74，但是使用到了position进行排序，然而在索引中，不能直接查询到对position排好序的，数据较小的时候，查询到内存中进行处理，数据较大的时候在磁盘上完成排序。所以在extra中出现了Using filesort（外部排序，需要优化）
情况三：使用到name对后两个字段排序
EXPLAIN select * from employees WHERE `name` = 'LiLei' order by age, position;
;利用最左前缀法则可以看出，本次查询中使用到了name索引，同时使用到了age和position进行排序，由extra中是null我们分析出，本次查询使用到了索引，但是未被索引覆盖。
情况四：使用到了name对后两个字段颠倒后排序
EXPLAIN select * from employees WHERE `name` = 'LiLei' order by position, age;
利用最左前缀法则可以看出，本次查询中使用到了name索引，同时使用到了position和age进行排序，但是这与我们建索引的顺序不一样，从extra中可以看出Using filesort（外部排序，需要优化）
情况五：使用到了name对后两个字段颠倒后排序，中间字段有常量
EXPLAIN select * from employees WHERE `name` = 'LiLei' and age = 10 order by position, age;
与情况四相比，我们在查询的时候age使用到了常量，同时使用到了name、age，从而可以看出使用到了索引，但是未被索引覆盖，没有出现了外部排序的情况。
情况六：使用到name后面两个字段顺序但是排序方式不一样
EXPLAIN select * from employees WHERE `name` = 'test' order by age asc, position desc;
可以看出使用到了联合索引的name，但是后面两个字段的排序方式不一样，从extra中看出Using filesort（外部排序）的情况。据了解MySql8对这种情况有做内部优化。
###情况七： 排序进行范围查询
EXPLAIN select * from employees WHERE `name` in ('LiLei','test') order by age, position;
对应排序来说，多条件相等也是范围查询
情况八：对name进行范围查询并排序
EXPLAIN select * from employees where `name` > 'a' order by `name`;
可以看出进行了全表扫描，需要优化
分页优化
很多时候可能会面临深分页的情况，需要从前面读取一万、十万条然后抛弃前面大量的数据，我只需要最后几十条数据就可以了，执行效率会非常低。
根据自增其连续主键排序的分页查询
select * from employees limit 190000,5;
该sql查询从190001开始的五行数据，没有添加order by，表示通过主键排序。
EXPLAIN select * from employees where id > 190000 limit 5;
但是这样必须是连续的，如果中间有断开的就会有问题。
可以是有该方式进行排序必须满足下面两个条件
- 主键自增且连续
- 结果是按照主键排序的
根据非自增字段排序的分页查询
在实际的业务中往往大量的都是根据具体的业务字段进行排序且分页的，这些字段不是自增且连续的
select * from employees ORDER BY name limit 190000,5;
EXPLAIN select * from employees ORDER BY name limit 190000,5;
发现并没有使用到联合索引的name字段，而是进行了全表扫描。
可以给sql改写为这样的
select * from employees e inner join (select id from employees order by name limit 190000,5) ed on e.id = ed.id;
EXPLAIN select * from employees e inner join (select id from employees order by name limit 190000,5) ed on e.id = ed.id;
通过explain执行计划分析，优化后的sql大大的减少了时间，使用到了索引。但是实际的开发我们可能还是不会依赖到这个主键？是否可以给这个name建一个索引，然后进行优化呢。然后找其他方法进行优化呢？
Join关联查询优化
示例表
**-- 示例表：
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表插入10万行记录
DROP procedure if exists insert_t1;
delimiter;;
create procedure insert_t1()
begin
declare i int;
set i =1;
while(i <= 100000) 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();
MySql的表关联常见的两种算法
嵌套循环连接Nested-Loop Join(NLJ)算法
一次一行循环从第一张表（称为驱动表）中读取行，在这一行数据中取到关联字段，根据这个关联字段在另一张表（被驱动表）里取出满足条件的行，然后两个表结果合集。
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
sql执行流程如下：
- 从t2表中读取一行数据(如果t2表有过滤条件先过滤，在读取一行数据出来)
- 从t2表中拿到关联字段a，到t1表中去查找
- 取出t1表中满足条件的行，跟t2表中获取到的结果合并并返回给客户端
- 重复上面三步
由上面步骤我们可以分析出t2遍一共只有100行数据，进行扫描了100次，而t1每一次从t2拿到的关联字段a，去t1表关联会进行1次扫描，对t1表总共也是100次，加起来这次关联查询一共扫描了200次。
基于块的嵌套循环连接Block Nested-Loop Join(BNJ)算法
把驱动表的数据读入到join_buffer中，然后扫描被驱动表，把被驱动表每一行读出来跟join_buffer中的数据进行比对。
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
sql执行流程如下：
- 把t1表的所有数据放入到join_buffer中
- 把t2表中读取到的每一行，跟join_buffer中的数据进行比较
- 返回满足join条件的数据
由上面步骤我们可以分析出整个过程t1和t2都进行了全表扫描，因此扫描的行数为100100(t1:100000 + t2:100)。并且join_buffer里的数据是无序的，因此没读取一行数据都会去做100000次比较，所有在内存中做了100 * 100000 = 1000万次。
对应关联sql的优化建议
- 关联字段加索引，让mysql做join操作是尽量选择NLJ算法，驱动表因为需要全部查询，所以过滤的条件尽量做索引，避免全表扫描。
- 小表驱动大表，写多表连接sql时如果明确知道那张表是小表可以用straight_join写固定连接驱动方式，省掉MySql优化器自己去判断的时间
- straight_join只适用于inner join查询
- 尽可能的让优化器去判断，因为大部分情况下MySql优化器是比人的判断是更准确的。适用straight_join一定要慎重
in和exists的优化
优化原则：小表驱动大表
in： 当B表的数据集小于A表的数据集时候，in优于exists
exists： 当B表的数据集大于A表的数据集时候，exists优于in
select * from A where id in (select id from B)
#等价于：
for(select id from B){
select * from A where A.id = B.id
}
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
}
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(*) ≈ count(1) > count(字段) > count(主键 ID)
count(字段) 走的是二级索引，主键ID走的是主键索引，索引字段更快
查询字段没有索引情况
count(*) ≈ count(1) > count(主键 ID) > count(字段)
count(1)和count(字段)执行过程类似，不过count()不需要取出字段进行统计，就用常量1作为统计，count(字段)需要取出来去Server层进行统计。理论上count(1)比count(字段)快。
count()是一个特例，MySql并不会把全部字段取出来，而是专门做了优化，不取值按行进行累加，效率非常高，所以不需要用count(列名)或count(常量)来2替代count(*)。
count(主键id)MySql选择走了辅助索引而不是主键索引，MySql5.7以后做了优化。
统计查询常见的优化方法
- 查询MySql自己维护总行数
- show table status (估计值)
- 将总数维护到Redis中
- 增加数据库计数表
优化总结
- MySql支持两种排序方式filesort和index，index效率高，filesort效率低。
- order by满足两种情况会使用到using index
- order by语句使用最左前列
- 使用where字句用于order by字句条件列组合满足最左前列
- 尽量在索引列上完成排序，遵循索引建立时的最左前缀法则
- 如果order by的条件不在索引列上，就会产生using filesort
- 能用索引覆盖尽量用索引覆盖
