网站排名带照片怎么做,网站的功能定位,搜索网站的方法,商城网站建设公司目录 前言一、场景举例1.联合索引第一个字段用范围查询不走索引(分情况#xff09;2.强制走指定索引3.覆盖索引优化4.in和or在表数据量比较大的情况会走索引#xff0c;在表记录不多的情况下会选择全表扫描5.like 后% 一般情况都会走索引(索引下推) 二、Mysql如何选择合适的索… 目录 前言一、场景举例1.联合索引第一个字段用范围查询不走索引(分情况2.强制走指定索引3.覆盖索引优化4.in和or在表数据量比较大的情况会走索引在表记录不多的情况下会选择全表扫描5.like 后% 一般情况都会走索引(索引下推) 二、Mysql如何选择合适的索引1.trace工具分析 三、常见sql深入优化1.Order by与Group by优化2.order by和group by优化总结3.Using filesort文件排序原理详解 四、索引设计原则1、代码先行索引后上2、联合索引尽量覆盖条件3、不要在小基数字段上建立索引4、长字符串我们可以采用前缀索引5、where与order by冲突时优先where6、基于慢sql查询做优化 前言 为employees表添加10w条数据需要等待一会(嫌时间长的话可以自己手动写一个java脚本)。下边例子都是基于10w数据演示。本章与之前的第二章有很多关联场景建议先熟悉一下之前的博客。 第二章博客跳转地址 表结构
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
) ENGINEInnoDB AUTO_INCREMENT100002 DEFAULT CHARSETutf8 COMMENT员工记录表;插入数据语句执行较慢建议着急的自己写个批插脚本
DROP PROCEDURE IF EXISTS insert_emp;
DELIMITER ;;
CREATE PROCEDURE insert_emp()
BEGIN
DECLARE i INT;
SET i1;
WHILE(i100000)DO
INSERT INTO employees(NAME,age,POSITION) VALUES(CONCAT(july,i),i,dev);
SET ii1;
END WHILE;
END;;
DELIMITER ;
CALL insert_emp();一、场景举例
表结构及相关索引
1.联合索引第一个字段用范围查询不走索引(分情况
EXPLAIN SELECT * FROM employees WHERE NAME july AND age 22 AND POSITION manager;EXPLAIN SELECT * FROM employees WHERE NAME july AND age 22 AND POSITION manager;EXPLAIN SELECT * FROM employees WHERE NAME july AND age 22 AND POSITION manager;从上边三条explain结果可以看出当联合索引中的第一个字段name为范围查询时mysql选择了全表扫描而不是走我们的idx_name_age_position索引为什么会这样呢 其实mysql底层有一定的判断规则mysql认为走全表扫描比走索引更好一些。这么说你可能不相信请继续向下看。
2.强制走指定索引
使用FORCE INDEX关键字指定强制走哪一个索引。
EXPLAIN SELECT * FROM employees FORCE INDEX(idx_name_age_position)
WHERE NAME july AND age 22 AND POSITION manager;结合上边不走索引的执行结果我们可以看出mysql在全表扫描时大概扫描10w多行的数据强制走索引之后只扫描了大概5w多行数据。看到这里那我岂不是走索引香啊。但是不能单单只看扫描行数还要看查询耗时。
先关闭查询缓存
SET GLOBAL query_cache_size0;
SET GLOBAL QUERY_CACHE_TYPE0;查询两个sql语句执行耗时
SELECT * FROM employees WHERE NAME july
SELECT * FROM employees FORCE INDEX(idx_name_age_position) WHERE NAME july 第一条执行耗时0.199s 第二条执行耗时0.240s 可以看出走索引查询时时间更长全表扫描反而时间比较短。当然这里有人可能觉得执行一次不代表每次都是走索引耗时慢呢这种情况万一是偶然呢这里大家可以自己动手多执行几次你会发现这两天sql语句耗时有所变化不是差很多但是最终结果都是第一条耗时比第二条短。
3.覆盖索引优化
如何优化这种全表扫描呢其实之前的博客中也有讲到那就是尽量让查询语句走覆盖索引。
EXPLAIN SELECT name,age,position FROM employees WHERE NAME july 4.in和or在表数据量比较大的情况会走索引在表记录不多的情况下会选择全表扫描
创建一张与employees一模一样的表employees_copy
CREATE TABLE employees_copy (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
) ENGINEInnoDB AUTO_INCREMENT100002 DEFAULT CHARSETutf8 COMMENT员工记录表;copy表中只添加几条记录而employees表中有10w条记录。 两张表执行相同语句
employees表
EXPLAIN SELECT * FROM employees WHERE name in (LiHua,July,Jack) AND age 22 AND position
manager;employees_copy 表
EXPLAIN SELECT * FROM employees_copy WHERE name in (LiHua,July,Jack) AND age 22 AND position
manager;其实这里也可以看出为什么在表数据少时mysql选择不走索引因为在copy表中先去索引查询完后还需要回表再去查一遍。一共就三条记录还有查询两遍不如直接一下全表扫描来的快。
or 查询也是一样的道理
EXPLAIN SELECT * FROM employees WHERE (name LiHua or name July) AND age 22 AND position
manager;EXPLAIN SELECT * FROM employees_copy WHERE (name LiHua or name July) AND age 22 AND position
manager;5.like 后% 一般情况都会走索引(索引下推)
EXPLAIN SELECT * FROM employees WHERE name like LiHua% AND age 22 AND position manager;mysql5.6之前版本mysql会根据条件LiHua%把所有的主键id查询出来然后去主键索引去查询然后根据查询出的数据再根据其它条件进行过滤。
索引下推 5.6及之后的版本首先根据条件LiHua%把对应的主键id查询出来然后再查出来的基础上再根据age字段和position字段的条件进行过滤如果符合条件则把整个id拿到否则过滤掉整个id。最后拿着得到的id集合去主键索引里查询。减少回表次数 索引下推会减少回表次数对于innodb引擎的表索引下推只能用于二级索引innodb的主键索引聚簇索引树叶子节点上保存的是全行数据所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找Mysql没有用索引下推优化 估计应该是Mysql认为范围查找过滤的结果集过大这个就得看底层源码了解才知道了like KK% 在绝大多数情况来看过滤后的结果集比较小所以这里Mysql选择给 like KK% 用了索引下推优化这里like后%不是一定每次都会走索引下推有时like KK% 也不一定就会走索引下推。 二、Mysql如何选择合适的索引
1.trace工具分析
EXPLAIN select * from employees where name a;EXPLAIN select * from employees where name z;对于上面这两种 name‘a’ 和 name‘z’ 的执行结果mysql最终是否选择走索引或者一张表涉及多个索引mysql最终如何选择索引我们可以用trace工具来一查究竟开启trace工具会影响mysql性能所以只能临时分析sql使用用完之后立即关闭。 前置条件开启trace
set session optimizer_traceenabledon,end_markers_in_jsonon; SELECT * FROM employees where name a order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;第一张表记录太多就不全部展示了。 information_schema.OPTIMIZER_TRACE这个库下的这个表是固定也是mysql默认就有的数据库。
展开trace列原始内容
{steps: [{join_preparation: {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: {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: 97657,cost: 19886} /* 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: 48828,cost: 58595,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: 97657,access_type: scan,resulting_rows: 97657,cost: 19884,chosen: true,use_tmp_table: true}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 97657,cost_for_plan: 19884,sort_cost: 97657,new_cost_for_plan: 117541,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: {select#: 1,steps: [{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: {rows: 100001,examined_rows: 100001,number_of_tmp_files: 29,sort_buffer_size: 262056,sort_mode: sort_key, packed_additional_fields} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}相关trace中一些关键字的含义
{steps: [{join_preparation: { ‐第一阶段SQL准备阶段格式化sql},{join_optimization: { ‐第二阶段SQL优化阶段比如查询条件中一些无意义的查询where 11或者优化一下查询条件使之符合最左前缀匹配等。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: 97657, ‐-扫描行数cost: 19886 -‐查询成本是一个相对值没有单位值越大成本越高} /* table_scan */,potential_range_indexes: [ ‐-查询可能使用的索引{index: PRIMARY, ‐-主键索引usable: false, ‐-不会走主键所以结果为falsecause: 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: 48828, ‐‐索引扫描行数cost: 58595, ‐‐索引使用成本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: 97657,access_type: scan, ‐‐访问类型为scan全表扫描resulting_rows: 97657,cost: 19884,chosen: true, ‐‐确定选择use_tmp_table: true}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 97657,cost_for_plan: 19884,sort_cost: 97657,new_cost_for_plan: 117541,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: [{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: {rows: 100001,examined_rows: 100001,number_of_tmp_files: 29,sort_buffer_size: 262056,sort_mode: sort_key, packed_additional_fields} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}结论全表扫描的成本低于索引扫描所以mysql最终选择全表扫描
关闭trace
set session optimizer_traceenabledoff; 三、常见sql深入优化
1.Order by与Group by优化
employees 表相关索引信息如下
举例1
EXPLAIN SELECT * FROM employees
WHERE NAME LiLei AND POSITION dev ORDER BY age;分析 利用最左前缀法则中间字段不能断因此查询用到了name索引从key_len74也能看出age索引列用在排序过程中因为Extra字段里没有using filesort所以age字段也走索引了。
举例2
EXPLAIN SELECT * FROM employees WHERE NAME LiLei ORDER BY POSITION;分析 从explain的执行结果来看key_len74查询使用了name索引由于用了position进行排序跳过了 age出现了Using filesort。所以POSITION字段未走索引。
举例3
EXPLAIN SELECT * FROM employees WHERE NAME LiLei ORDER BY age,POSITION;分析 查找只用到索引nameage和position用于排序也用到了索引无Using filesort。
举例4
EXPLAIN SELECT * FROM employees WHERE NAME LiLei ORDER BY POSITION,age;分析 和举例3中explain的执行结果一样但是出现了Using filesort因为索引的创建顺序为name,age,position但是排序的时候age和position颠倒位置了导致后边排序无法用到索引。
举例5
EXPLAIN SELECT * FROM employees WHERE NAME LiLei AND age 18 ORDER BY POSITION,age;分析 与举例4对比在Extra中并未出现Using filesort因为age为常量在排序中被优化相当于在name和age都确定的情况下按照position去排序所以索引未颠倒不会出现Using filesort。
举例6
EXPLAIN SELECT * FROM employees WHERE NAME zhuge ORDER BY age ASC,POSITION DESC;分析 虽然排序的字段列与索引顺序一样且order by默认升序这里position desc变成了降序导致与索引的排序方式不同从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式我了解是建立索引时指定字段是按照升序建立还是降序建立。
举例7
EXPLAIN SELECT * FROM employees WHERE NAME IN (LiLei,zhuge)
ORDER BY age,POSITION;分析 对于排序来说多个相等条件也是范围查询即在name的in查询中这一批范围里的age和position不一定是相对有序的所以order by时走的Using filesort。
举例8
EXPLAIN SELECT * FROM employees WHERE NAME a ORDER BY NAME;分析 按照道理该sql是可以走索引的即在namea’的这个范围中name顺序是有序的可以走索引但是mysql确实全表扫描可能原因就是mysql认为全表扫描比走索引要好。
EXPLAIN SELECT NAME,age,POSITION FROM employees WHERE NAME a ORDER BY NAME;分析 尝试用覆盖索引进行优化果然走索引了。
2.order by和group by优化总结
1.MySQL支持两种方式的排序filesort和indexUsing index是指MySQL扫描索引本身完成排序。index效率高filesort效率低。 2.order by满足两种情况会使用Using index。①order by语句使用索引最左前列。②使用where子句与order by子句条件列组合满足索引最左前列。 3.尽量在索引列上完成排序遵循索引建立索引创建的顺序时的最左前缀法则。 4.如果order by的条件不在索引列上就会产生Using filesort。 5.能用覆盖索引尽量用覆盖索引 6.group by与order by很类似其实质是先排序后分组遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意where高于having能写在where中的限定条件就不要去having限定了。
3.Using filesort文件排序原理详解
filesort文件排序方式
单路排序是一次性取出满足条件行的所有字段然后在sort buffer中进行排序用trace工具可以看到sort_mode信息里显示 sort_key, additional_fields 或者 sort_key,packed_additional_fields 双路排序回表排序模式是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID然后在 sort buffer 中进行排序排序完后需要再次取回其它需要的字段用trace工具可以看到sort_mode信息里显示 sort_key, rowid
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。 如果 字段的总长度小于max_length_for_sort_data 那么使用 单路排序模式。如果 字段的总长度大于max_length_for_sort_data 那么使用 双路排序模式。
验证单路排序和双路排序 首先开启trace
set session optimizer_traceenabledon,end_markers_in_jsonon; 查询语句
SELECT * FROM employees WHERE NAME july ORDER BY POSITION;
SELECT * FROM information_schema.OPTIMIZER_TRACE;单路排序举例
{steps: [{join_preparation: {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 july) order by employees.position}] /* steps */} /* join_preparation */},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: (employees.name july),steps: [{transformation: equality_propagation,resulting_condition: (employees.name july)},{transformation: constant_propagation,resulting_condition: (employees.name july)},{transformation: trivial_condition_removal,resulting_condition: (employees.name july)}] /* 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: [{table: employees,field: name,equals: july,null_rejecting: false}] /* ref_optimizer_key_uses */},{rows_estimation: [{table: employees,range_analysis: {table_scan: {rows: 97656,cost: 19886} /* 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: [july name july] /* ranges */,index_dives_for_eq_ranges: true,rowid_ordered: false,using_mrr: false,index_only: false,rows: 1,cost: 2.21,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_position,rows: 1,ranges: [july name july] /* ranges */} /* range_access_plan */,rows_for_plan: 1,cost_for_plan: 2.21,chosen: true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{considered_execution_plans: [{plan_prefix: [] /* plan_prefix */,table: employees,best_access_path: {considered_access_paths: [{access_type: ref,index: idx_name_age_position,rows: 1,cost: 1.2,chosen: true},{access_type: range,range_details: {used_index: idx_name_age_position} /* range_details */,chosen: false,cause: heuristic_index_cheaper}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 1,cost_for_plan: 1.2,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: {original_condition: (employees.name july),attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: employees,attached: null}] /* 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 */},{added_back_ref_condition: ((employees.name july))},{reconsidering_access_paths_for_index_ordering: {clause: ORDER BY,steps: [] /* steps */,index_order_summary: {table: employees,index_provides_order: false,order_direction: undefined,index: idx_name_age_position,plan_changed: false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{refine_plan: [{table: employees,pushed_index_condition: (employees.name july),table_condition_attached: null}] /* refine_plan */}] /* steps */} /* join_optimization */},{join_execution: {select#: 1,steps: [{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: { ‐‐文件排序信息rows: 0, -‐预计扫描行数examined_rows: 0, -‐参与排序的行number_of_tmp_files: 0, ‐‐使用临时文件的个数这个值如果为0代表全部使用的sort_buffer内存排序否则使用的
磁盘文件排序sort_buffer_size: 262056, ‐‐排序缓存的大小单位Bytesort_mode: sort_key, packed_additional_fields ‐‐排序方式这里用的单路排序} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}多路排序举例 employees表所有字段长度总和肯定大于10字节所以设置max_length_for_sort_data为10。
set max_length_for_sort_data 10再次执行刚才的查询
SELECT * FROM employees WHERE NAME july ORDER BY POSITION;
SELECT * FROM information_schema.OPTIMIZER_TRACE;{steps: [{join_preparation: {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 july) order by employees.position}] /* steps */} /* join_preparation */},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: (employees.name july),steps: [{transformation: equality_propagation,resulting_condition: (employees.name july)},{transformation: constant_propagation,resulting_condition: (employees.name july)},{transformation: trivial_condition_removal,resulting_condition: (employees.name july)}] /* 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: [{table: employees,field: name,equals: july,null_rejecting: false}] /* ref_optimizer_key_uses */},{rows_estimation: [{table: employees,range_analysis: {table_scan: {rows: 97656,cost: 19886} /* 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: [july name july] /* ranges */,index_dives_for_eq_ranges: true,rowid_ordered: false,using_mrr: false,index_only: false,rows: 1,cost: 2.21,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_position,rows: 1,ranges: [july name july] /* ranges */} /* range_access_plan */,rows_for_plan: 1,cost_for_plan: 2.21,chosen: true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{considered_execution_plans: [{plan_prefix: [] /* plan_prefix */,table: employees,best_access_path: {considered_access_paths: [{access_type: ref,index: idx_name_age_position,rows: 1,cost: 1.2,chosen: true},{access_type: range,range_details: {used_index: idx_name_age_position} /* range_details */,chosen: false,cause: heuristic_index_cheaper}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 1,cost_for_plan: 1.2,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: {original_condition: (employees.name july),attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: employees,attached: null}] /* 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 */},{added_back_ref_condition: ((employees.name july))},{reconsidering_access_paths_for_index_ordering: {clause: ORDER BY,steps: [] /* steps */,index_order_summary: {table: employees,index_provides_order: false,order_direction: undefined,index: idx_name_age_position,plan_changed: false} /* index_order_summary */} /* reconsidering_access_paths_for_index_ordering */},{refine_plan: [{table: employees,pushed_index_condition: (employees.name july),table_condition_attached: null}] /* refine_plan */}] /* steps */} /* join_optimization */},{join_execution: {select#: 1,steps: [{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: {rows: 0,examined_rows: 0,number_of_tmp_files: 0,sort_buffer_size: 262136,sort_mode: sort_key, rowid ‐‐‐排序方式这里用的双路排序} /* filesort_summary */}] /* steps */} /* join_execution */}] /* steps */
}单路排序的详细过程
从索引name找到第一个满足 name ‘july’ 条件的主键 id根据主键 id 取出整行取出所有字段的值存入 sort_buffer 中从索引name找到下一个满足 name ‘july’ 条件的主键 id重复步骤 2、3 直到不满足 name ‘july’对 sort_buffer 中的数据按照字段 position 进行排序返回结果给客户端
双路排序的详细过程
从索引 name 找到第一个满足 name ‘july’ 的主键id根据主键 id 取出整行把排序字段 position 和主键 id 这两个字段放到 sort buffer 中从索引 name 取下一个满足 name ‘july’ 记录的主键 id重复 3、4 直到不满足 name ‘july’对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序遍历排序好的 id 和字段 position按照 id 的值回到原表中取出 所有字段的值返回给客户端
对比两种排序模式 正常在内存里排序肯定是要比在磁盘中转一下进行排序要快的。在sort buffer内存大小够用的情况下是不会去磁盘排序的只有超过了sort buffer大小之后才会涉及磁盘排序。 单路排序会把所有需要查询的字段都放到 sort buffer 中而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序然后再通过主键回到原表查询需要的字段。这里我们可以看出来如果是单路排序那么sort buffer里边查询的所有字段都会放进去而双路只会放进去主键和排序的字段这样的话sort buffer一定的情况下双路排序放的要排序的行就会比单路多但是双路还是需要根据主键回表查询剩余要查询的字段。有利有弊需要自己衡量。 如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了可以适当把max_length_for_sort_data 配置小点让优化器选择使用双路排序算法可以在sort_buffer 中一次排序更多的行只是需要再根据主键回到原表取数据。 如果 MySQL 排序内存有条件可以配置比较大可以适当增大 max_length_for_sort_data 的值让优化器优先选择全字段排序(单路排序)把需要的字段放到 sort_buffer 中这样排序后就会直接从内存里返回查询结果了。 所以MySQL通过 max_length_for_sort_data 这个参数来控制排序在不同场景使用不同的排序模式从而提升排序效率。 如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序但不能因为这个就随便增大sort_buffer(默认1M)mysql很多参数设置都是做过优化的不要轻易调整。 四、索引设计原则 1、代码先行索引后上 一般应该等到主体业务功能开发完毕把涉及到该表相关sql都要拿出来分析之后再建立索引。当然这样比较标准但是正常情况下如果业务不复杂在设计的同时那些增删改查的sql基本都能确定个差不多在设计阶段也可以把索引设计上。前提是了解需要使用那些sql语句。 2、联合索引尽量覆盖条件 比如可以设计一个或者两三个联合索引(尽量少建单值索引)让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。 3、不要在小基数字段上建立索引 索引基数是指这个字段在表里总共有多少个不同的值比如一张表总共100万行记录其中有个性别字段其值不是男就是女那么该字段的基数就是2。 如果对这种小基数字段建立索引的话还不如全表扫描了因为你的索引树里就包含男和女两种值根本没法进行快速的二分查找那用索引就没有太大的意义了。 一般建立索引尽量使用那些基数比较大的字段就是值比较多的字段那么才能发挥出B树快速二分查找的优势来。 4、长字符串我们可以采用前缀索引 尽量对字段类型较小的列设计索引比如说什么tinyint之类的因为字段类型较小的话占用磁盘空间也会比较小此时你在搜索的时候性能也会比较好一点。 当然这个所谓的字段类型小一点的列也不是绝对的很多时候你就是要针对varchar(255)这种字段建立索引哪怕多占用一些磁盘空间也是有必要的。 对于这种varchar(255)的大字段可能会比较占用磁盘空间可以稍微优化下比如针对这个字段的前20个字符建立索引就是说对这个字段里的每个值的前20个字符放在索引树里类似于 KEYindex(name(20),age,position)。 此时你在where条件里搜索的时候如果是根据name字段来搜索那么此时就会先到索引树里根据name字段的前20个字符去搜索定位到之后前20个字符的前缀匹配的部分数据之后再回到聚簇索引提取出来完整的name字段值进行比对。 但是假如你要是order by name那么此时你的name因为在索引树里仅仅包含了前20个字符所以这个排序是没法用上索引的 group by也是同理。所以这里大家要对前缀索引有一个了解。 5、where与order by冲突时优先where 一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据然后做排序的成本可能会小很多。 6、基于慢sql查询做优化 可以根据监控后台的一些慢sql针对这些慢sql查询做特定的索引优化。慢sql的监控方式有很多这里大家可以去了解了解目的主要是获取那些慢sql然后进行分析和优化。
