实战（4）"/>
mysql优化实战（4）
创建示例数据
创建一个user 表并创建一个 idx_name_age_sex_phoe_root_work_id的一个联合索引和一个唯一索引idx_card
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`age` int(11) DEFAULT NULL,`id_card` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`sex` int(11) DEFAULT NULL,`phone` varchar(11) DEFAULT NULL,`create_date` datetime DEFAULT NULL,`update_date` datetime DEFAULT NULL,`root_id` int(11) DEFAULT NULL,`worker_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `idx_card` (`id_card`) USING BTREE,KEY `idx_name_age_sex_phoe_root_work_id` (`name`,`age`,`sex`,`phone`,`root_id`,`worker_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=67457 DEFAULT CHARSET=utf8;使用存储过程像数据库里添加数据30万条数据；
BEGIN#Routine body goes here...
#定义个类型
DECLARE i INT;
#给变量定义初始值SET i=1;
WHILE i<300001 DO
# 插入数据
insert into user (name,age,id_card,sex,phone,create_date,root_id,worker_id)VALUES(CONCAT("whj",i), FLOOR(RAND() * 45),UUID_SHORT(),FLOOR(RAND()*1),FLOOR(RAND() * 100000000000),now(),FLOOR(RAND() * 10),FLOOR(RAND() * 360));
set i=i+1;
END WHILE;
END
从上面这个存储过程插入的数据 一下特征name 的值在whj0 ~ whj30万之间，age 的值在0 ~ 45之间，权限ID在1 ~ 10 之间，工作ID在 0 ~ 360之间
分析优化
根据上面的索引写如下sql
explain select *from `user` where root_id=8
从这个sql 来看这个走的应该是全表不会走我们的索引的所以type 字段值为ALL；如果我们在不修改我们的索引的基础上，去对这个sql进行优化的话，只能使用覆盖索引如下
explain select `name`,age,sex,phone,root_id from `user` where root_id=8
在这种情况下也就是能优化到这种情况；但是这种情况的查询也是非常不理想的，到这里有人会讲那你为啥不去使用name 这种索引字段去查询呢？有一个问题假如你的项目查询直接是面对我们的数据库的那么就会存在很多的查询选项那么这个选项是我们控制不了的；因为索引要遵循最左前缀；所以开发项目中面对客户端的查询我建议使用我们的第三方，而我们的后台查询，就是代码中的查询我们使用sql ,毕竟我们可以控制我们的代码；
向上面的这些sql语句走的索引或者不走索引，但是呢还有写sql 是不具有稳定性的比如
explain select *from `user` where `name`<'whj300000' # 这个sql不走索引
explain select *from `user` where `name`<'whj1' # 这个又走了索引
这个会有疑惑为啥呢？怎么数据大的反而不走索引，数据小的反而走了索引。其实这个就是走的二级索引然后呢根据数据回表所以数据足够大的时候二级索引的范围查找出的数据再继续回表未必比全表扫描快，这个我前面的笔记也有提到下面我们再看看这个mysql 自己的优化部分吧！根据mysql 提供的trace 这个工具去看一下
set session optimizer_trace="enabled=on",end_markers_in_json=on; # 开启trace 这个工具不使用的时候一定关闭select *from `user` where `name`<'whj1';{"steps": [{"join_preparation": { # 第一个阶段格式化sql"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`id_card` AS `id_card`,`user`.`sex` AS `sex`,`user`.`phone` AS `phone`,`user`.`create_date` AS `create_date`,`user`.`update_date` AS `update_date`,`user`.`root_id` AS `root_id`,`user`.`worker_id` AS `worker_id` from `user` where (`user`.`name` < 'whj1')" # 这个就是我们show warnings; 得到的那个格式化的sql}] /* steps */} /* join_preparation */},{"join_optimization": { # 第二个阶段 sql的优化"select#": 1,"steps": [{"condition_processing": { # 查询的条件优化"condition": "WHERE","original_condition": "(`user`.`name` < 'whj1')","steps": [{"transformation": "equality_propagation","resulting_condition": "(`user`.`name` < 'whj1')"},{"transformation": "constant_propagation","resulting_condition": "(`user`.`name` < 'whj1')"},{"transformation": "trivial_condition_removal","resulting_condition": "(`user`.`name` < 'whj1')"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": {} /* substitute_generated_columns */},{"table_dependencies": [ # 查询的表{"table": "`user`","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": [ # 预计表的访问成本 这个啥意思就是我们有时又在 possible_keys的值在执行的预计走索引{"table": "`user`","range_analysis": {"table_scan": {"rows": 298722, # 扫描行数"cost": 30299 # 扫描成本} /* table_scan */,"potential_range_indexes": [ # 查询可能使用到的索引{"index": "PRIMARY", # 主键索引"usable": false,"cause": "not_applicable"},{"index": "idx_card", # 唯一索引"usable": false,"cause": "not_applicable"},{"index": "idx_name_age_sex_phoe_root_work_id", # 二级索引"usable": true,"key_parts": ["name","age","sex","phone","root_id","worker_id","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_sex_phoe_root_work_id","usable": false,"cause": "query_references_nonkey_column"}] /* potential_skip_scan_indexes */} /* skip_scan_range */,"analyzing_range_alternatives": { # 分析每个索引的成本"range_scan_alternatives": [{"index": "idx_name_age_sex_phoe_root_work_id","ranges": ["NULL < name < whj1"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": false, # 使用这个索引是否根据主键排序"using_mrr": false, "index_only": false, # 是否使用覆盖索引"rows": 1, # 索引的扫描行数"cost": 0.61, # 索引使用成本"chosen": true # 是否使用索引}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */,"chosen_range_access_summary": {"range_access_plan": {"type": "range_scan","index": "idx_name_age_sex_phoe_root_work_id","rows": 1,"ranges": ["NULL < name < whj1"] /* ranges */} /* range_access_plan */,"rows_for_plan": 1,"cost_for_plan": 0.61,"chosen": true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [{"plan_prefix": [] /* plan_prefix */,"table": "`user`","best_access_path": { # 最优的访问"considered_access_paths": [ # 最终确定使用的扫描 {"rows_to_scan": 1,"access_type": "range", # 选择访问类型"range_details": {"used_index": "idx_name_age_sex_phoe_root_work_id" # 使用的索引} /* range_details */,"resulting_rows": 1,"cost": 0.71, # 使用成本"chosen": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 1,"cost_for_plan": 0.71,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": "(`user`.`name` < 'whj1')","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`user`","attached": "(`user`.`name` < 'whj1')"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"finalizing_table_conditions": [{"table": "`user`","original_table_condition": "(`user`.`name` < 'whj1')","final_table_condition ": "(`user`.`name` < 'whj1')"}] /* finalizing_table_conditions */},{"refine_plan": [{"table": "`user`","pushed_index_condition": "(`user`.`name` < 'whj1')","table_condition_attached": null}] /* refine_plan */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */
}set session optimizer_trace="enabled=off" # 关闭这个tarce; 这个工具最好就是即用即开
这个上面查出来的码大家看一下，mysql 是否使用索引就是跟这个来计算cost 来决定是否使用索引；有人问可不可以使用forace index() 关键字来使用强制索引。那肯定是可以的，但是不建议，因为mysql是经过计算优化的来的往往你强制，都不是最优解，还有在mysql5.8后面强制好像也不生效，但是sql语句中也没报错；可能需要开启，这个你自己查一下
优化示例：常见一些优化
order by 与group by 优化
案例一
分析： 遵循最左前缀原则我们的name 查询走了索引，又因为我们在查询的过成功根本使用的in这个关键字查出来的name字段在 就是排好序的所以根据age 去排序需要重新排序所以我们 Extra 出现了 Using Filesort；
案例二
分析： 还是遵循了最左前缀 我们使用name 这个字段的索引使用的等于，所以在树的表示里这里列它的排序就是根据下一列继续排序；
结论： 上面这两个案例我们可以得出一个我们使用到索引的时候，如果order by 可以遵循最左前缀比如我是用name 等于其实就是固定了没什么可以对比了但是我可以使用 age 等后面的字段但是还是必须遵循最左前缀，还有就是order by 后面必须统一排序；我们为什么要遵循索引排序呢？ 如果我们不遵循那么我们需要查出数据再去排序再去回表，但是我们遵循只需要查询二级索引回表就行了；这里如果不理解去看我的第一张，告诉你什么是索引
在排序的优化中只有两种方式一种是Using index ，一种using Filesort ; 而Using index 是扫描索引本身排序，效率高；Filesort 是外排排序边扫描边排序，效率差
1. order by 遵循满足两种情况会使用using index;
1).order by 语句遵循最左前缀
2).使用 where 子句和order by 子句条件组合满足最左前缀原则
**2. 尽量在索引上完成排序；
3.order by 上的条件不在索引，就会产生 using filesort;
4.group by和order by 基本类似group by 就是加了条件的sql 然后在排序只要跟order by 一样遵循规格就行了，但是我们的having 这个关键字尽量不要使用，他的优先级小于where **
using filesort 文件排序的原理
filesort文件排序的方式
- 单路排序：一次性取出满足条件的所有字段，然后再sort buffer 中进行排序 ；用trace工具里有sort_mode信息里显示<sort_key,additional_fields> 或者 < sort_key,packed_additional_fields >
- 双路排序（又叫回表排序）：首先取出需要排序的字段，和字段对应的数据ID ,然后在sort buffer 中排序，拿着排完序的ID序列回表查询到所有字段；用trace 工具里sort_mode 信息显示<sort_key,rowid>
MySQL 中比对系统字段max_length_for_sort_data (默认1024个字节)字段的大小和查询字段的总大小来判断使用哪种排序
当我们查询的字段总大小，小于max_length_for_sort_data 使用单路排序；反之则使用双路排序
示例观察两种排序方式：
根据上面的sql 使用trace工具查看排序的方式
set SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
select *from `user` where worker_id=300 ORDER BY `name`;
select * from information_schema.OPTIMIZER_TRACE;
# trace 排序执行计划{"join_execution": {"select#": 1,"steps": [{"sorting_table_in_plan_at_position": 0,"filesort_information": [ # 根据什么字段排序是升序还是降序{"direction": "asc", # 升序"table": "`user`",# user 表"field": "name" # 根据name 字段}] /* 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": 2097152,"key_size": 41,"row_size": 231, "max_rows_per_buffer": 8774,"num_rows_estimate": 1030646,"num_rows_found": 802, "num_initial_chunks_spilled_to_disk": 0,"peak_memory_used": 163840,"sort_algorithm": "std::stable_sort","sort_mode": "<fixed_sort_key, packed_additional_fields>" # 这个字段看出这是个单路排序} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
set SESSION optimizer_trace="enabled=off";
索引设计原则
代码先上，索引设计
在你书写的功能中，先把功能完成再去书写你的索引
创建的索引尽可能的覆盖你所查询的条件(尽量不使用但字段索引)
不要在小基数的字段上建立索引（比如男女这种性别字段）
长字符串尽量使用前缀来作为索引
如果我们的order by ，group by 与where 条件冲突，优先考虑where 条件创建索引
在我们的项目中添加sql 的监控，监控一些慢的sql 来进行优化
分页查询优化
在正常开发中我感觉我们很多时候都是这样去进行的分页,一般都是根据ID 进行的分页
explain select *from `user` LIMIT 90000,5
分析： 这个分页扫描了全表，我们需要对于这样的语句进行优化，他是根据id排序筛查前面的90000行查询的结果是90001 ~ 90005 那么这样我就会的到如下的sql
explain select *from `user` where id>90000 limit 5
分析：上面这个语句一看就是使用到聚簇索引（主键索引）显示去查询大于90000的数据再去只取5个；其实这种的效率已经很高但是这种我们的开发中也不会使用，为啥呢？加入我们的ID不是连续的中间出现过断区间，那么上面一的语句查询的结果，更我们优化后查询的结果就不一样了 ；
最优解使用覆盖索引
explain select *from `user` a inner join (select id from `user` limit 90000,5) b on a.id=b.id
分析： 其实从这图就能看出来这个的性能是没有大于号高的但是考虑实际情况他是最好的，它不会出现上面的那种情况；其实这个方法我决的大多数情况都得使用，不止这一种情况，例如不适用id字段排序使用name 也可以先查询ID 如果你看不懂上面这个图，去翻翻我的其他笔记我有讲到过这个图是啥意思，之心顺序等等；
join关联查询优化
示例数据我先去根据我的user表的root_id 和worker_id 创建两张关联表root 和worker的字典如下，再给我之前那个表的root_id字段 加上索引未了方便测试
# 创建一个worker 表
DROP TABLE IF EXISTS `worker`;
CREATE TABLE `worker` (`id` int(11) NOT NULL AUTO_INCREMENT,`worker_name` varchar(25) DEFAULT NULL,`company` varchar(50) DEFAULT NULL,`create_date` datetime DEFAULT NULL,`update_date` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建一个root 表
DROP TABLE IF EXISTS `root`;
CREATE TABLE `root` (`id` int(11) NOT NULL AUTO_INCREMENT,`root_name` varchar(20) DEFAULT NULL,`create_date` datetime DEFAULT NULL,`update_date` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;创建一个存储过程,worker表中添加数据
BEGIN#Routine body goes here...
DECLARE i int;
set i=0;
WHILE i<360 DO
insert into worker (worker_name,company,create_date)VALUES(CONCAT('Java开发',i),CONCAT('帝江XXX科技',i),now());
SET i=i+1;
end WHILE;
END
创建一个向root 表中添加数据
BEGIN
DECLARE i int;
set i =0;
while i<10 DO
insert into root (root_name,create_date)VALUES(CONCAT('主管',i),NOW());
SET i =i+1;
end WHILE;
END
Mysql 关联查询的常见两种算法
为啥优化关联查询要说到算法呢？ 这个问题留在这我等会讲；
1).Nested-Loop Join 算法 （嵌套循环连接算法）
这个算法就是使用驱动表循环一次一行，当取关键字段的时候再去取被驱动表的数据，最后的合集就是这个算法查询出来的结果集
EXPLAIN select *from `root` a INNER JOIN `user` b on a.id=b.root_id
从上面的这个图分析：先查询的 root 表，在查询的user表，在连接查询中我们把先执行的表叫做驱动表，后执行的叫做被驱动表；在使用inner join 这个关键字关联如果我们关联的两个字段都是索引字段的话那么小表就是驱动表，大表就是被驱动的；但是如果一个有索引一个没有索引那么有索引的就是被驱动的表，没有索引的就是驱动表
- 使用关键字left join 查询的 左边的是驱动表右边是被驱动的；使用right join 时右边是主驱动表 左边是被驱动；jion与inner join 相似小表主驱动（一定记住走索引未必是小表不走索引未必是大表也要根据性能来考虑）
根据NLJ的算法上面一共扫描了多少次
当 a表取出一个数据的时候b表就会扫描33191次；那也就是a表扫描完10次，b表需要扫描331910次，也就是一共扫描331920次
如果驱动表关联字段不是所有字段那么就会使用第二种算法,Block-Nested-Loop join算法
2).Block-Nested-Loop join 算法
在测试之前在root 的表中加一列rootid 每一行的值等于这行的id; 在取消user 表中的root_id索引；
EXPLAIN select *from `root` a JOIN `user` b on a.rooid=b.root_id
从Extra 字段中可以知道是用来 BNLJ 这个算法；
这个算法是怎么使用的，他是把a表全部查出来放在 join buffer 中，在把b表全部查出来放在join buffer 中，a表总行数10+比较总行数30万，然后在根据被动表去对比主驱动表那么也就是30万*10；这个数据也是非常大的；
总结：当使用到了关联查询尽量使用NLJ算法，小表驱动大表，关联字段健索引，如果在知道那个是小表的情况下使用straight_join 指定驱动表；但是一般这个关键字慎用
in和exsits 的优化
使用in 这样的关键字全面的表必须大于后边表；反之使用exsits 也是小标驱动大表的原则
count(*) 优化
四个sql的执行计划一样，说明这四个sql执行效率应该差不多
字段有索引：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(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出
字段，所以理论上count(1)比count(字段)会快一点。
count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用
count(列名)或count(常量)来替代 count()。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索
性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
更多推荐
mysql优化实战（4）
发布评论