性能优化-03-索引优化实践一-CSDN博客

drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('liuxin',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

1 索引原则

1.1 联合索引第一个字段是范围则不走索引

case1：

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

possible_keys：可能会走索引idx_name_age_position，但实际没走key；原因：第一个字段是范围，MySQL认为全表扫描可能会快一些（只利用name，MySQL需要回表操作）

case2：强制走索引

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

强制走索引后：rows 变小了？为什么MySQL没走索引？

rows表示MySQL大概会查询多少行，但并不意味着，rows越少，执行越快；回表比较慢

case3：明显区别的案例

-- 执行时间0.171s
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间0.234s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

1.2 覆盖索引优化

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

1.3 in和or

in和or在表数据量大的情况下会走索引，数据量小时MySQL认为全表扫描比较快

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

1.4 like

like xx%（可以当成 in or）一般会走索引

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

索引下推

MySQL5.6版本之前，没用到索引下推时：先根据name在二级索引中找到主键id，再去主键索引中拿到数据后，再比对age和position

MySQL5.6版本之后，在比较name时，同时也按索引中的字段进行判断（age、position），过滤不符合的再进行回表

为什么范围查找 > 不做索引下推？MySQL认为 > 获取的结果集很大，like xx%结果集小

2 MySQL如何选择合适的索引

trace工具

set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{
            "rows_estimation": [
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 100119,    --扫描行数
                    "cost": 10086.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 */
                    },
                    {
                      "index": "idx_hire_time",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* 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,			--是否使用覆盖索引
                        "in_memory": 1,
                        "rows": 50059,						--索引扫描行数
                        "cost": 17520.9,					--索引使用成本
                        "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": 100119,
                      "access_type": "scan",		--访问类型，scan为全表扫描
                      "resulting_rows": 100119,	
                      "cost": 10084.2,
                      "chosen": true,						--确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100119,
                "cost_for_plan": 10084.2,
                "sort_cost": 100119,
                "new_cost_for_plan": 110203,
                "chosen": true
              }
            ] /* considered_execution_plans */

MySQL根据预估成本计算，应该走哪个索引

3 常见SQL深入优化

3.1 Order by和Group by

case1：基于索引排序

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

order by和group by走索引的情况不会体现在 key_len 中，只能在Extra看出

case2：基于内存排序

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

跳过了age索引，不会走索引排序

case3：

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

按照索引树顺序，走了索引

case4：

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

没有按索引树顺序，使用内存排序

case5：

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

case6：

EXPLAIN select * from employees where name in ('Lilei','zhuge','niupi') order by age,position;

MySQL5版本不会走索引，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;

groupBy内部先通过orderBy排序，优化原则参考orderBy；尽量不使用having

3.2 using fileSort文件排序原理

using index：用到二级索引

using fileSort：用到聚簇索引

单路排序：结果全load到内存中去（有一块排序内存）

双路排序：结果集id和排序字段（相比单路排序少很多字段）；再根据id回表查询结果集

MySQL通过比较系统变量（表中字段之和）max_length_for_sort_data（默认1024字节）< 单路；> 双路

使用trace工具分析如下语句：

set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name = 'zhuge' order by position;
select * from information_schema.OPTIMIZER_TRACE;

"filesort_summary": {
    "memory_available": 262144,
    "key_size": 40,
    "row_size": 190,
    "max_rows_per_buffer": 15,
    "num_rows_estimate": 15,
    "num_rows_found": 0,
    "num_initial_chunks_spilled_to_disk": 0,
    "peak_memory_used": 0,
    "sort_algorithm": "none",
    "sort_mode": "<fixed_sort_key, packed_additional_fields>"  --排序方式：单路排序
} /* filesort_summary */

模拟双路排序：