一、索引应用综合案例
注：表中有12万数据，并创建name，age，position联合索引
1.1、案例分析
- 联合索引第一个字段用范围不会走索引（mysql内部评估第一个字段走索引范围比较大，回表多，效率不高，不如全表扫描快）
- 强制走索引，虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表 扫描高，因为回表效率不高
//针对上述问题，做个试验，首先关闭缓存，生产环境可自己试验set global query_cache_size=0;set global query_cache_type=0;SELECT * FROM employees WHERE name > 'zhuge11';SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'zhuge11';-- 注意：针对上述问题，可使用覆盖索引优化，只查询联合索引覆盖的字段EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22AND position ='manager';
- in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
注意：如果表中只有几条数据的话，in和or不会走索引，会进行全表扫描。
1.2、索引下推
- like ‘LiLei%’，会走索引，而且走的是全索引
- 索引下推
针对like ‘LiLei%’走全索引是因为mysql使用了索引下推技术，是mysql5.6做的优化，按常规情况来说，like ‘LiLie%’只会走name索引，而不会走age和position索引，因为LiLei后面不确定，索引age和position无序，无法走索引；
a、在mysql5.6之前，会先查询name符合’LiLie%’的所有数据，然后根据主键回表查询找到相应记录，之后再根据age、position字段过滤。
b、在mysql5.6及以后，是根据name查询符合’LiLie%’的所有数据，并根据age、position字段过滤，之后再回表查询，这样就减少了回表的数据量
1.索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点
上保存的是全 行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
2.范围查找并未使用索引下推技术，可能是因为范围查找过大，回表率高
1.3、trace工具分析
针对上述两条sql，mysql一条选择走索引，一条选择不走索引，mysql是如何分析判断的呢，我们用trace工具分析如下：
-- 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name>'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
{
"steps": [
{
"join_preparation": { -- 第一阶段:sql准备阶段，格式化sql
"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": { -- 第二阶段，sql优化阶段
"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": 212032, -- 扫描行数
"cost": 43082 -- 查询成本
} /* 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" //翻译下面的句子
"0x0100610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 < name"
] /* ranges */, -- 使用索引范围
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, -- 使用该索引获取的记录是够按照主键排序
"using_mrr": false,
"index_only": false, -- 是否使用覆盖索引
"rows": 106016, -- 索引扫描行数
"cost": 127220, -- 索引使用成本
"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": 212032,
"access_type": "scan", // 访问类型为scan，表示全表扫描
"resulting_rows": 212032,
"cost": 43079,
"chosen": true, //确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 212032,
"cost_for_plan": 43079,
"sort_cost": 212032,
"new_cost_for_plan": 255111,
"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",
"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": 212365,
"examined_rows": 212368,
"number_of_tmp_files": 65,
"sort_buffer_size": 261960,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
1.4、order by与group by优化
1.4.1、mysql的两种排序规则
mysql支持两种排序方式，分别为filesort和index，
- using index是指mysql扫描索引本身完成排序，index效率高，filesort效率低
- using filesort文件排序原理，using filesort排序有两种方式，分别为单路排序和双路排序，取出满足条件的所有行字段，然后在sort buffer中进行排序。
单路排序：是一次性取出满足条件行得所有字段，然后再sort buffer中进行排序；用trace工具可以看到 sort_mode信息里显示
双路排序(又叫回表排序模式)：是首先根据相应条件取出排序字段和可以直接定位行数据得ID,然后再 sort buffer中进行排序，排序完后需要再次回表查询取出其他需要得字段，用trace工具可以看到 sort_mode 信息里显示
mysql通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用那种排序模式。
- 如果字段的总长度小于max_length_for_sort_data，则使用单路排序模式
如果字段的总长度大于max_length_for_sort_data，则使用双路排序模式
1.4.1.1、示例验证单路排序双路排序
查看trace结果
单路排序过程从索引name找到第一个满足name = ‘zhuge’条件的主键id
- 根据主键id取出整行，取出所有字段的值，存入 sort_buffer中
- 从索引name找到下一个name=’zhuge’条件的主键id
- 重复以上步骤，找到所有满足name=’zhuge’的数据
- 对sort_buffer中的数据按照字段position进行排序
- 将结果返回给客户端
双路排序过程
- 从索引name找到第一个满足name = ‘zhuge’条件的主键id
- 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
- 重复 3、4 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
- 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出所有字段的值返回给客户端
区别
- 单路排序会把所有字段都放到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 这个参数来控制排序，在不同场景使用不同的排序模式，
从而提升排序效率。<br />注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增<br />大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。1.4.2、案例分析
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用 在排序过程中，因为Extra字段里没有using filesort
- 从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了 age，出现了Using filesort
- 查找只用到索引name，age和position用于排序，无Using filesort
- 因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了
- 虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的 排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
- 对于排序来说，多个相等条件也是范围查询
- 上述情况可以用覆盖索引优化
1.4.3、优化总结
- order by满足两种情况会使用using index
order by语句使用索引最左前列
where条件与order by条件组合符合最左前列原则
- 尽量在索引列上完成排序，遵循索引建立时的顺序
- 如果order by不在索引列上，则会产生using filesort
- 能使用覆盖索引尽量使用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。
二、索引设计原则
代码先行，索引后上，索引的创建应该等主体业务开发完毕后，把涉及到的sql全部拿出来然后分析，之后再创建索引
- 比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原
则。联合索引最多不建议超过3个，索引越多，插入、删除、修改慢，读多写少的也可以建四五个，写多读 少的一个两个
- 不要再区分度低的字段上建立索引
- 长字符串我们可以采用前缀索引，但是不要影响业务，对于varchar(255)的大字段会比较占磁盘，所以可以采用前缀索 index(name(20),age,position)，此时只会匹配前20个字符，但是对order by和group by会有影响。
- where和order by再使用索引冲突时优先where，优先让where使用索引。
- 基于慢sql查询进行优化
https://blog.csdn.net/qq_40884473/article/details/89455740
三、索引设计实战
场景：社交app，用户可能会根据省份、性别、年龄、身高、爱好、评分来进行筛选
索引 - (province,city,sex,age)
where province=xx and city=xx and age>=xx and age<=xx
-- 对于sex字段，可以通过如下sql让其走索引
where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx
最近七天登陆的用户，可通过加一个字段来解决 - is_login_in_latest_7_days
辅助索引(sex,score)
*** 并不能解决心中的疑问
- 核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询
- 尽量建立联合索引，不要建单值索引（存储空间大），唯一索引除外，mysql一般只会走一个索引
- 范围查找的字段尽量放在联合索引的最后
- 一张表尽量不要超过千万
若有收获，就点个赞吧
0 人点赞
