sql执行计划计算
mysql底层会对sql进行查问优化，根据各个计划所产生的cost成本计算最优执行计划，sql的最终执行计划是否走了索引，抑或为什么没有走索引的起因能够用trace工具来剖析。
开启trace：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启tracemysql> select * from user where name > 'a' order by age;mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;查看trace字段：
6 {7 "steps": [8 {9 "join_preparation": { ‐‐第一阶段：SQL筹备阶段，格式化sql10 "select#": 1,11 "steps": [12 {13 "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`"14 }15 ] /* steps */16 } /* join_preparation */17 },18 {19 "join_optimization": { ‐‐第二阶段：SQL优化阶段20 "select#": 1,21 "steps": [22 {23 "condition_processing": { ‐‐条件解决24 "condition": "WHERE",25 "original_condition": "(`employees`.`name` > 'a')",26 "steps": [27 {28 "transformation": "equality_propagation",29 "resulting_condition": "(`employees`.`name` > 'a')"30 },31 {32 "transformation": "constant_propagation",33 "resulting_condition": "(`employees`.`name` > 'a')"34 },35 {36 "transformation": "trivial_condition_removal",37 "resulting_condition": "(`employees`.`name` > 'a')"38 }39 ] /* steps */40 } /* condition_processing */41 },42 {43 "substitute_generated_columns": {44 } /* substitute_generated_columns */45 },46 {47 "table_dependencies": [ ‐‐表依赖详情48 {49 "table": "`employees`",50 "row_may_be_null": false,51 "map_bit": 0,52 "depends_on_map_bits": [53 ] /* depends_on_map_bits */54 }55 ] /* table_dependencies */56 },57 {58 "ref_optimizer_key_uses": [59 ] /* ref_optimizer_key_uses */60 },61 {62 "rows_estimation": [ ‐‐预估表的拜访老本63 {64 "table": "`employees`",65 "range_analysis": {66 "table_scan": { ‐‐全表扫描状况67 "rows": 10123, ‐‐扫描行数68 "cost": 2054.7 ‐‐查问老本69 } /* table_scan */,70 "potential_range_indexes": [ ‐‐查问可能应用的索引71 {72 "index": "PRIMARY", ‐‐主键索引73 "usable": false,74 "cause": "not_applicable"75 },76 {77 "index": "idx_name_age_position", ‐‐辅助索引78 "usable": true,79 "key_parts": [80 "name",81 "age",82 "position",83 "id"84 ] /* key_parts */85 }86 ] /* potential_range_indexes */,87 "setup_range_conditions": [88 ] /* setup_range_conditions */,89 "group_index_range": {90 "chosen": false,91 "cause": "not_group_by_or_distinct"92 } /* group_index_range */,93 "analyzing_range_alternatives": { ‐‐剖析各个索引应用老本94 "range_scan_alternatives": [95 {96 "index": "idx_name_age_position",97 "ranges": [98 "a < name" ‐‐索引应用范畴99 ] /* ranges */,100 "index_dives_for_eq_ranges": true,101 "rowid_ordered": false, ‐‐应用该索引获取的记录是否依照主键排序102 "using_mrr": false,103 "index_only": false, ‐‐是否应用笼罩索引104 "rows": 5061, ‐‐索引扫描行数105 "cost": 6074.2, ‐‐索引应用老本106 "chosen": false, ‐‐是否抉择该索引107 "cause": "cost"108 }109 ] /* range_scan_alternatives */,110 "analyzing_roworder_intersect": {111 "usable": false,112 "cause": "too_few_roworder_scans"113 } /* analyzing_roworder_intersect */114 } /* analyzing_range_alternatives */115 } /* range_analysis */116 }117 ] /* rows_estimation */118 },119 {120 "considered_execution_plans": [121 {122 "plan_prefix": [123 ] /* plan_prefix */,124 "table": "`employees`",125 "best_access_path": { ‐‐最优拜访门路126 "considered_access_paths": [ ‐‐最终抉择的拜访门路127 {128 "rows_to_scan": 10123,129 "access_type": "scan", ‐‐拜访类型：为scan，全表扫描130 "resulting_rows": 10123,131 "cost": 2052.6,132 "chosen": true, ‐‐确定抉择133 "use_tmp_table": true134 }135 ] /* considered_access_paths */136 } /* best_access_path */,137 "condition_filtering_pct": 100,138 "rows_for_plan": 10123,139 "cost_for_plan": 2052.6,140 "sort_cost": 10123,141 "new_cost_for_plan": 12176,142 "chosen": true143 }144 ] /* considered_execution_plans */145 },146 {147 "attaching_conditions_to_tables": {148 "original_condition": "(`employees`.`name` > 'a')",149 "attached_conditions_computation": [150 ] /* attached_conditions_computation */,151 "attached_conditions_summary": [152 {153 "table": "`employees`",154 "attached": "(`employees`.`name` > 'a')"155 }156 ] /* attached_conditions_summary */157 } /* attaching_conditions_to_tables */158 },159 {160 "clause_processing": {161 "clause": "ORDER BY",162 "original_clause": "`employees`.`position`",163 "items": [164 {165 "item": "`employees`.`position`"166 }167 ] /* items */,168 "resulting_clause_is_simple": true,169 "resulting_clause": "`employees`.`position`"170 } /* clause_processing */171 },172 {173 "reconsidering_access_paths_for_index_ordering": {174 "clause": "ORDER BY",175 "steps": [176 ] /* steps */,177 "index_order_summary": {178 "table": "`employees`",179 "index_provides_order": false,180 "order_direction": "undefined",181 "index": "unknown",182 "plan_changed": false183 } /* index_order_summary */184 } /* reconsidering_access_paths_for_index_ordering */185 },186 {187 "refine_plan": [188 {189 "table": "`employees`"190 }191 ] /* refine_plan */192 }193 ] /* steps */194 } /* join_optimization */195 },196 {197 "join_execution": { ‐‐第三阶段：SQL执行阶段198 "select#": 1,199 "steps": [200 ] /* steps */201 } /* join_execution */202 }203 ] /* steps */204 }205206 论断：全表扫描的老本低于索引扫描，所以mysql最终抉择全表扫描207208 mysql> select * from employees where name > 'zzz' order by position;209 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;210211 查看trace字段可知索引扫描的老本低于全表扫描，所以mysql最终抉择索引扫描212（↑↑↑援用↑↑↑）
mysql> set session optimizer_trace="enabled=off"; ‐‐敞开trace索引优化
外围1：索引排序分为Using index和Using filesort，其中Using filesort较慢，如果呈现则须要思考应用Using index进行优化。
Using filesort的呈现起因是因为内存大小有余，无奈寄存须要排序的值，所以采纳了内部文件排序，如果能放得下则会采取Using index。
filesort的单路排序和双路排序单路排序：单路排序是一次性取出所有的数据在内存中排序双路排序：双路排序是只取出须要排序的字段和索引ID，排序后再依据id回表取出所有的数据有点相似于汇集索引和辅助索引的查问形式区别MySQL 通过比拟零碎变量 max_length_for_sort_data(默认1024字节) 的大小和须要查问的字段总大小来判断应用哪种排序模式。如果 字段的总长度小于max_length_for_sort_data ，那么应用 单路排序模式；如果 字段的总长度大于max_length_for_sort_data ，那么应用 双路排序模式。简略说就是看放不放得下，放得下就间接都取出来，放不下就只取一部分外围2：所有的优化准则都是基于B+tree构建的索引树，辅助索引的检索优先于汇集索引，组合索引的最左前缀准则意味着组合起因里每一个索引字段都是后面那个索引字段的细分，必须依序执行，否则无奈胜利索引。
外围3：范畴查问应该放在查问的最初一位，一般来说范畴查问后的查问条件无奈匹配到对应的索引，如果有多个范畴查问能够思考应用冗余的常量字段来代替范畴查问。
外围4：如果非必要，在查问时尽量应用笼罩索引代替select * from 的形式来查找，能够大大晋升查问效率。
索引设计准则
1、代码后行，索引后上
2、联结索引尽量笼罩条件
3、不要在小基数字段上建设索引
4、长字符串咱们能够采纳前缀索引
5、where与order by抵触时优先where
6、基于慢sql查问做优化
Order by与Group by优化
应用级联索引能够使order by和group by的执行速度大幅度放慢，但不能违反最左前缀法令，条件和orderby或者groupby必须以索引建设的程序进行查问。
惟一的例外是
select * from user where name='a' and age=12 order by height，age;user表中存在一个name，height，age的索引，以上查问是能够走索引的，因为age的值12是常量，在执行的时候会被优化，所以最终走的索引仍旧是name，height，age。
分页查问优化
limit分页的执行原理：
select * from user limit 100,10以上查问mysql会先查问出表中的前100条数据，而后舍弃掉，再查问10条，所以一共是查问了110条数据，在数据量过大时效率会比较慢，能够采纳以下优化形式。
select * from user u inner join (select id from user order by uid limit 90000,5) udon u.id = ud.id;这种查问形式只查ID，而后应用ID的主键索引进行回表检索，会大幅度缩小查问的数据量，在大数据查问时候无效的防止了filesort形式的排序。
Join关联查问优化
join的查问形式分两种
1.嵌套循环链接（Nested-Loop Join(NLJ)）
1. 从驱动表中读取一行数据（如果驱动表表有查问过滤条件的，会从过滤后果里取出一行数据）；2. 从第 1 步的数据中，取出关联字段，到被驱动表中查找；3. 取出被驱动表中满足条件的行，跟驱动表中获取到的后果合并，作为后果返回给客户端；4. 反复下面 3 步。2.基于块的嵌套循环链接（Block Nested-Loop Join(BNL)）
1. 把驱动表的所有数据放入到 join_buffer 中2. 把被驱动表中每一行取出来，跟 join_buffer 中的数据做比照3. 返回满足 join 条件的数据ps：如果join_buffer中放不下则分批把驱动表中的数据放入进去mysql优化时inner join会主动选取较小的表作为驱动表，但并非100%抉择正确
left join 左侧为驱动表
right join 右侧为驱动表
优化重点：
关联字段加索引，让mysql做join操作时尽量抉择NLJ算法
小表驱动大表，写多表连贯sql时如果明确晓得哪张表是小表能够用straight_join写法固定连贯驱动形式，省去
mysql优化器本人判断的工夫
in和exsits优化
优化重点：小表驱动大表
count(*)查问优化
count（*）是最快的查问总数的函数，执行效率如下：
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还能够走主键索引，所以count(主键 id)>count(字段)