建表语句，同时插入10万条数据

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()        
begindeclare 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();

综合例子

1. 联合索引第一个字段用范围不会走索引

   EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

   

   结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描 

2. 强制走索引                     

   EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

                                                                                                 

    

   结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

   我们来查询看看：强制走索引反而效率低了

3. 覆盖索引优化

   EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

   

4. 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';

   

mysql如何选择合适的索引

EXPLAIN select * from employees where name > 'a';
EXPLAIN select * from employees where name > 'zzz' ;

 

 

 同样的一张表，对同样的字段有的走索引，有的不走索引，这涉及到了mysql内部有个cost成本计算。我们可以通过trace工具一探究竟：

trace工具使用
set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启traceselect * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{"steps": [{"join_preparation": {"select#": 1,			--第一阶段：sql准备阶段，sql优化"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": 94170,	--扫描行数"cost": 9707.3	--查询成本} /* 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": 47085,		--索引扫描行数"cost": 51666,		--索引使用成本"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": 94170,"access_type": "scan",		--访问类型：scan 全表扫描"resulting_rows": 94170,"cost": 9705.2,"chosen": true,		--确定选择"use_tmp_table": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 94170,"cost_for_plan": 9705.2,"sort_cost": 94170,"new_cost_for_plan": 103875,"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_in_plan_at_position": 0} /* filesort */] /* considering_tmp_tables */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [{"sorting_table_in_plan_at_position": 0,"filesort_information": [{"direction": "asc","table": "`employees`","field": "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": 188,"max_rows_per_buffer": 1394,"num_rows_estimate": 281600,"num_rows_found": 93919,"num_initial_chunks_spilled_to_disk": 28,"peak_memory_used": 268928,"sort_algorithm": "std::stable_sort","sort_mode": "<fixed_sort_key, packed_additional_fields>"} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}

常见sql深入优化

1.Order by与Group by优化

case1:

EXPLAIN select * from employees where name='LiLei' and position='dev' order by age

分析：

利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

case2:

EXPLAIN select * from employees where name='LiLei' order by position

分析：

从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

Case 3：

EXPLAIN select * from employees where name='LiLei' order by age,position

 分析：

查找只用到索引name，age和position用于排序，无Using filesort。

case4:

EXPLAIN select * from employees where name='LiLei' order by position,age

 分析：

和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

case5:

EXPLAIN select * from employees where name='LiLei' and age=18 order by position,age

 分析：

与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

case6:

EXPLAIN select * from employees where name='LiLei' order by age asc,position desc

 分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

case7

EXPLAIN select * from employees where name >'LiLei' order by name 

 可以用覆盖索引来优化

EXPLAIN select name,age,position from employees where name >'LiLei' order by name 

     

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

2.分页查询优化

        很多时候我们业务系统实现分页功能可能会用如下sql实现

        select * from employees limit 10000,10;

        表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

        case 1：根据自增且连续的主键排序的分页查询

                select * from employees limit 90000,5;

                select * from employees where id > 90000 limit 5;

        但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。

        因此，如果主键不连续，不能使用上面描述的优化方法。另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的

        case 2:根据非主键字段排序的分页查询

                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;

 

        

3.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表插入1万行记录
drop procedure if exists insert_t1; 
delimiter ;;
create procedure insert_t1()        
begindeclare 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()        
begindeclare 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表关联有两种算法

        1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

         一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

        上面sql的大致流程如下：

1. 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。

        整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。

     

  2.基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b

 

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下：

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。

对于关联sql的优化

• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

 

3.in和exsits优化

 原则：小表驱动大表，即小的数据集驱动大的数据集

 in：当B表的数据集小于A表的数据集时，in优于exists

exists：当A表的数据集小于B表的数据集时，exists优于in

        

EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

4.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(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。