MySql如何选择合适的索引

EXPLAIN SELECT * from t_pay_invoice where supplier_code >= ‘GYS00001’;

如果用idx_supplier_code索引需要遍历supplier_code字段索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历supplier_code字段的索引树就能拿到所有结果，如下：
EXPLAIN SELECT supplier_code from t_pay_invoice where supplier_code >= ‘GYS00001’;

EXPLAIN SELECT * from t_pay_invoice where supplier_code >= ‘GZS00001’;

对于上面这两种supplier_code>‘GYS00001’ 和supplier_code>‘GZS00001’ 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭
trace工具用法：
set session optimizer_trace=“enabled=on”,end_markers_in_json=on;‐‐开启trace

SELECT * from t_pay_invoice where supplier_code >= ‘GZS00001’;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{
“steps”: [{
“join_preparation”: { – 第一阶段：SQL准备阶段
“select#”: 1,
“steps”: [{
“expanded_query”: "/* select#1 / select t_pay_invoice.id AS id,t_pay_invoice.source_type AS source_type,t_pay_invoice.mechanism_code AS mechanism_code,t_pay_invoice.settlement_sheet_id AS settlement_sheet_id,t_pay_invoice.invoice_collect_time AS invoice_collect_time,t_pay_invoice.pc_cycle AS pc_cycle,t_pay_invoice.plan_payment_date AS plan_payment_date,t_pay_invoice.status AS status,t_pay_invoice.audit_status AS audit_status,t_pay_invoice.settlement_currency AS settlement_currency,t_pay_invoice.exchange_rate AS exchange_rate,t_pay_invoice.convert_exchange_rate AS convert_exchange_rate,t_pay_invoice.settlement_total_price AS settlement_total_price,t_pay_invoice.supplier_code AS supplier_code,t_pay_invoice.supplier_type AS supplier_type,t_pay_invoice.supplier_manage_code AS supplier_manage_code,t_pay_invoice.financial_flag AS financial_flag,t_pay_invoice.payee_name AS payee_name,t_pay_invoice.bank_account AS bank_account,t_pay_invoice.open_bank AS open_bank,t_pay_invoice.bank_unionpay AS bank_unionpay,t_pay_invoice.account_platform_no AS account_platform_no,t_pay_invoice.after_verification_amount AS after_verification_amount,t_pay_invoice.not_verification_amount AS not_verification_amount,t_pay_invoice.in_pay_amount AS in_pay_amount,t_pay_invoice.remark AS remark,t_pay_invoice.is_lock AS is_lock,t_pay_invoice.version AS version,t_pay_invoice.supplier_manage_code_bill AS supplier_manage_code_bill,t_pay_invoice.is_transfer AS is_transfer,t_pay_invoice.create_user_id AS create_user_id,t_pay_invoice.create_user_name AS create_user_name,t_pay_invoice.pay_currency AS pay_currency,t_pay_invoice.bill_amount AS bill_amount,t_pay_invoice.pay_money AS pay_money,t_pay_invoice.create_time AS create_time,t_pay_invoice.modify_time AS modify_time from t_pay_invoice where (t_pay_invoice.supplier_code >= ‘GZS00001’)"
}] / steps /
} / join_preparation /
},
{
“join_optimization”: { – 第二阶段：SQL优化阶段
“select#”: 1,
“steps”: [{
“condition_processing”: { – 条件处理
“condition”: “WHERE”,
“original_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”,
“steps”: [{
“transformation”: “equality_propagation”,
“resulting_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”
}
] / steps /
} / condition_processing /
},
{
“table_dependencies”: [{ --表依赖详情
“table”: “t_pay_invoice”,
“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”: “t_pay_invoice”,
“range_analysis”: {
“table_scan”: { – 全表扫描情况
“rows”: 157, --扫描行数
“cost”: 38.5 --查询时间成本
} / table_scan / ,
“potential_range_indices”: [{ – 查询可能使用的索引
“index”: “PRIMARY”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “idx_settlement_sheet_id”,
“usable”: false,
“cause”: “not_applicable”
},
{
“index”: “idx_supplier_code”,
“usable”: true,
“key_parts”: [
“supplier_code”,
“id”
] / key_parts /
},
{
“index”: “idx_account_platform_no”,
“usable”: false,
“cause”: “not_applicable”
}
] / potential_range_indices / ,
“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_supplier_code”,
“ranges”: [
“GZS00001 <= supplier_code”
] / 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_supplier_code”,
“rows”: 1,
“ranges”: [
“GZS00001 <= supplier_code”
] / 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”: “t_pay_invoice”,
“best_access_path”: { --最优访问路径
“considered_access_paths”: [{
“access_type”: “range”,
“rows”: 1,
“cost”: 2.41,
“chosen”: true
}] / considered_access_paths /
} / best_access_path / ,
“cost_for_plan”: 2.41,
“rows_for_plan”: 1,
“chosen”: true
}] / considered_execution_plans /
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”,
“attached_conditions_computation”: [] / attached_conditions_computation / ,
“attached_conditions_summary”: [{
“table”: “t_pay_invoice”,
“attached”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”
}] / attached_conditions_summary /
} / attaching_conditions_to_tables /
},
{
“refine_plan”: [{
“table”: “t_pay_invoice”,
“pushed_index_condition”: “(t_pay_invoice.supplier_code >= ‘GZS00001’)”,
“table_condition_attached”: null,
“access_type”: “range”
}] / refine_plan /
}
] / steps /
} / join_optimization /
},
{
“join_execution”: { --第三阶段：SQL执行阶段
“select#”: 1,
“steps”: [] / steps /
} / join_execution /
}
] / steps */
}
结论：索引扫描的时间成本低于全表扫描，所以mysql最终选择索引扫描
set session optimizer_trace=“enabled=off”;‐‐关闭trace

常见SQL深入优化
Order By 和Group by优化

case1：

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

case 2:

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

case 3:

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

case4：

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

case 5:

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

case 6:

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

case 7：

分析：对于排序来说，多个相等条件也是范围查询

case8：

可以使用覆盖索引优化

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

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 比查询字段的总长度小，那么使用 双路排序模式。

我们先看单路排序的详细过程：

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
4. 重复步骤 2、3 直到不满足 name = ‘zhuge’
5. 对 sort_buffer 中的数据按照字段 position 进行排序
6. 返回结果给客户端

我们再看下双路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
4. 重复 3、4 直到不满足 name = ‘zhuge’
5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 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很多参数设置都是做过优化的，不要轻易调整。