本文探讨了null值对索引性能的影响，变长列表存储技巧，B+树、页结构与索引种类，包括主键索引、唯一索引等。还深入讲解了聚簇索引、索引下推和跳跃式扫描。重点讲解了如何通过索引优化查询，如选择恰当的索引、使用联合索引和避免全表扫描。最后揭示了索引失效的几种情况和效率对比，提供了一份详细的索引优化参考指南。
1、empty和null存储结构
null为什么会影响索引性能？
变长列表和NULL值列表超出范围怎么存储？
2、索引结构
- B+树结构：
-
页结构：
-
页的row结构：
3、索引种类
- 主键索引：一种特殊的唯一索引，不允许有空值。
- 唯一索引：索引列的值必须唯一，但允许有空值。支持多列。
- 普通索引：最基本的索引，没有任何限制。
- 联合索引：多个列组成的索引，遵循”最左前缀“原则。
- 全文索引：支持大数据全文检索的倒排索引，版本5.6开始支持。
聚簇索引和非聚簇索引：
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.
每个InnoDB表都有一个特殊的索引，称为聚簇索引，用于存储行数据。通常，聚簇索引与主键同义 。
索引下推：
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
索引条件下推（ICP）是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估WHERE行的条件。启用ICP后，如果WHERE可以仅使用索引中的列来评估部分 条件，则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
索引跳跃式扫描：
4、索引优化
常用优化
-
在经常使用where、order by 或 group by条件的字段上建立索引。
-
多表联合查询时，在表关联的字段上建立索引。
-
根据情况优先使用唯一索引，单个索引区分度不大时使用联合索引。
-
设置驱动表和驱动表：当不用考虑驱动表和被驱动表不同而导致结果集不同时，如果驱动表和被驱动表条件上均没有索引时，使用数据量大的表作为驱动表。其它情况则考虑磁盘的随机读和顺序读，有索引的适合随机读。
select * from a straight join b on a.columna = b.columnb where a.id > 10 and b.id < 1000;
explain详解
explain
SELECT * FROM `fip_order` AS o
LEFT JOIN `fip_order_sales` AS s ON o.`id` = s.`order_id`
LEFT JOIN `fip_order_timeline` AS t ON o.`id` = t.`order_id`
LEFT JOIN `fip_order_loaner_info` AS li ON o.`id` = li.`order_id`
LEFT JOIN `fip_order_direct_sale` AS ds ON o.`id` = ds.`order_id`
WHERE o.id > 0 AND o.service_type_code NOT IN ('6!613!61301!002', '6!613!61301!005')
ORDER BY o.create_time;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B2ppv9Rl-1618990714633)(C:\Users\stanley.wang\AppData\Roaming\Typora\typora-user-images\image-20201225181323123.png)]
#id
- id相同：执行顺序由上至下。
- id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
#select_type
- simple: 是一个简单查询，不包含子查询或是union操作的查询。
- primary: 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMAY。
- subquery：当前表的查询是一个子查询。
- dependent subquery: 当前的子查询是依赖外部结果的子查询。
- union: Union操作的第二个或是之后的查询值为union。
- dependent union: 当UNION做为子查询时，第二个或是第二个后的查询的select_type值就标记成DEPENDENT UNION。
- union result: UNION产生的结果集，id值为null。
- derived：出现在FORM子句中的子查询。
#type
- const：当表中有且只有一行数据时，如对主键或者唯一索引查询时候，这个查询的性能也是很好的。
- system：const连接特例，当查询的表只有一行时使用，只能用于myisam和memory表，Innodb时一般是all或者index。
- ref：非唯一索引查找，返回匹配某个单独值的所有行。
- eq_ref：唯一索引或者主键查询，对于每个索引键，表中只有一条记录与之匹配。
- ref_or_null：类似于ref类型的查询，但是附加对NULL列的查询。
- index_merge：该连接类型表示使用了索引合并优化的方式。
- range: 索引范围扫描，常见between、>、<这样的查询条件。
- index: FULL index Scan 全索引扫描，同ALL的区别是，遍历的是索引树。
- all: FULL TALBLE Scan 全表扫描，这种方式效率最差。
#possible_keys
当前查询可能使用到的索引，并不一定会使用到。
#key
查询真正使用到的索引，如果没有使用到索引会显示null，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
#key_len
索引字段的最大可能长度,在一个联合索引里如果有3列，那么这3列的总字段长度是100个字节，key_len显示的可能小于100，比如只有30，这样就说明没有使用到联合索引的所有列，可能使用了一个列或两列key_len的长度由字段定义计算而来，并非数据的实际长度。
#ref
当前表在利用key列记录中的索引进行查询试用到的列或者常量。
#rows
表示mysql通过索引统计信息，估算的所需读取的行数，在关联查询时，rows列显示的是每一次嵌套查询时所需要的行数。rows值的大小是个统计抽样结果，依赖mysql的统计信息，并不十分准确。
#filtered
表示返回结果的行数占需要读取行数的百分比，值越大越好，filtered列的值依赖统计信息，并不十分准确
#extra
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
- not exists:使用not exists来优化查询。
- using filesort:使用额外操作进行排序，通常会出现在order by或者group by 查询中，出现这个值说明效率比较差。
- using index：使用了覆盖索引进行查询。
- using temporary: mysql需要使用临时表来处理查询，常见于排序，子查询，和分组查询。
- usering where：需要在mysql服务器层使用where条件来过滤数据。
- select tables optimized away: 直接通过索引来获取到数据，不用访问表，这种效率比较高。
optimizer_trace详解
show variables like '%optimizer_trace%';
set @@session.optimizer_trace='enabled=on,one_line=on';
#执行sql
select id as ids, trade_mode, trade_type, biz_type_code, order_code, customer_type_code, customer_id, customer_name,
goods_type_code, goods_id, goods_name, num, total_price, unit_price, platform_preferential_amount,
merchant_preferential_amount, merchant_type_code, merchant_id, merchant_name, project_id, city_id, order_status, status_update_time, deal_status,
create_time, create_user, create_name, update_time
from settlement_business_order
WHERE goods_type_code not in
('6!610!61003!012', '6!610!61003!012')
And id > 0
union
select group_concat(id) as ids, trade_mode, trade_type, biz_type_code, order_code, customer_type_code, customer_id, customer_name, merchant_type_code, merchant_id, merchant_name,
goods_type_code, '' as goods_id, '' as goods_name, sum(num) as num, sum(total_price) as total_price, sum(total_price)/sum(num) as unit_price,
sum(platform_preferential_amount) as platform_preferential_amount,
sum(merchant_preferential_amount) as merchant_preferential_amount, project_id, city_id, order_status, status_update_time, deal_status,
min(create_time) as create_time, min(create_user) as create_user, min(create_name) as create_name, min(update_time) as update_time
from settlement_business_order
WHERE goods_type_code in
('6!610!61003!012', '6!610!61003!012')
And id > 0
group by order_code, goods_type_code
order by create_time;
select * from information_schema.optimizer_trace;
结果分析：
官方文档参考：optimizer_trace
{
"steps": [{
"join_preparation": {
"select#": 1,
"steps": [{
"IN_uses_bisection": true
}, {
"expanded_query": "/* select#1 */ select `settlement_business_order`.`id` AS `ids`,`settlement_business_order`.`trade_mode` AS `trade_mode`,`settlement_business_order`.`trade_type` AS `trade_type`,`settlement_business_order`.`biz_type_code` AS `biz_type_code`,`settlement_business_order`.`order_code` AS `order_code`,`settlement_business_order`.`customer_type_code` AS `customer_type_code`,`settlement_business_order`.`customer_id` AS `customer_id`,`settlement_business_order`.`customer_name` AS `customer_name`,`settlement_business_order`.`goods_type_code` AS `goods_type_code`,`settlement_business_order`.`goods_id` AS `goods_id`,`settlement_business_order`.`goods_name` AS `goods_name`,`settlement_business_order`.`num` AS `num`,`settlement_business_order`.`total_price` AS `total_price`,`settlement_business_order`.`unit_price` AS `unit_price`,`settlement_business_order`.`platform_preferential_amount` AS `platform_preferential_amount`,`settlement_business_order`.`merchant_preferential_amount` AS `merchant_preferential_amount`,`settlement_business_order`.`merchant_type_code` AS `merchant_type_code`,`settlement_business_order`.`merchant_id` AS `merchant_id`,`settlement_business_order`.`merchant_name` AS `merchant_name`,`settlement_business_order`.`project_id` AS `project_id`,`settlement_business_order`.`city_id` AS `city_id`,`settlement_business_order`.`order_status` AS `order_status`,`settlement_business_order`.`status_update_time` AS `status_update_time`,`settlement_business_order`.`deal_status` AS `deal_status`,`settlement_business_order`.`create_time` AS `create_time`,`settlement_business_order`.`create_user` AS `create_user`,`settlement_business_order`.`create_name` AS `create_name`,`settlement_business_order`.`update_time` AS `update_time` from `settlement_business_order` where ((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}
}, {
"join_preparation": {
"select#": 2,
"steps": [{
"IN_uses_bisection": true
}, {
"expanded_query": "/* select#2 */ select group_concat(`settlement_business_order`.`id` separator ',') AS `ids`,`settlement_business_order`.`trade_mode` AS `trade_mode`,`settlement_business_order`.`trade_type` AS `trade_type`,`settlement_business_order`.`biz_type_code` AS `biz_type_code`,`settlement_business_order`.`order_code` AS `order_code`,`settlement_business_order`.`customer_type_code` AS `customer_type_code`,`settlement_business_order`.`customer_id` AS `customer_id`,`settlement_business_order`.`customer_name` AS `customer_name`,`settlement_business_order`.`merchant_type_code` AS `merchant_type_code`,`settlement_business_order`.`merchant_id` AS `merchant_id`,`settlement_business_order`.`merchant_name` AS `merchant_name`,`settlement_business_order`.`goods_type_code` AS `goods_type_code`,'' AS `goods_id`,'' AS `goods_name`,sum(`settlement_business_order`.`num`) AS `num`,sum(`settlement_business_order`.`total_price`) AS `total_price`,(sum(`settlement_business_order`.`total_price`) / sum(`settlement_business_order`.`num`)) AS `unit_price`,sum(`settlement_business_order`.`platform_preferential_amount`) AS `platform_preferential_amount`,sum(`settlement_business_order`.`merchant_preferential_amount`) AS `merchant_preferential_amount`,`settlement_business_order`.`project_id` AS `project_id`,`settlement_business_order`.`city_id` AS `city_id`,`settlement_business_order`.`order_status` AS `order_status`,`settlement_business_order`.`status_update_time` AS `status_update_time`,`settlement_business_order`.`deal_status` AS `deal_status`,min(`settlement_business_order`.`create_time`) AS `create_time`,min(`settlement_business_order`.`create_user`) AS `create_user`,min(`settlement_business_order`.`create_name`) AS `create_name`,min(`settlement_business_order`.`update_time`) AS `update_time` from `settlement_business_order` where ((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0)) group by `settlement_business_order`.`order_code`,`settlement_business_order`.`goods_type_code`"
}]
}
}, {
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"columns": 29,
"row_length": 4557,
"key_length": 8,
"unique_constraint": true,
"makes_grouped_rows": false,
"cannot_insert_duplicates": true,
"location": "TempTable"
}
}
}, {
"join_preparation": {
"select#": "fake",
"steps": [{
"expanded_query": "/* select#fake */ select `ids` AS `ids`,`trade_mode` AS `trade_mode`,`trade_type` AS `trade_type`,`biz_type_code` AS `biz_type_code`,`order_code` AS `order_code`,`customer_type_code` AS `customer_type_code`,`customer_id` AS `customer_id`,`customer_name` AS `customer_name`,`goods_type_code` AS `goods_type_code`,`goods_id` AS `goods_id`,`goods_name` AS `goods_name`,`num` AS `num`,`total_price` AS `total_price`,`unit_price` AS `unit_price`,`platform_preferential_amount` AS `platform_preferential_amount`,`merchant_preferential_amount` AS `merchant_preferential_amount`,`merchant_type_code` AS `merchant_type_code`,`merchant_id` AS `merchant_id`,`merchant_name` AS `merchant_name`,`project_id` AS `project_id`,`city_id` AS `city_id`,`order_status` AS `order_status`,`status_update_time` AS `status_update_time`,`deal_status` AS `deal_status`,`create_time` AS `create_time`,`create_user` AS `create_user`,`create_name` AS `create_name`,`update_time` AS `update_time` from dual order by `create_time`"
}]
}
}, {
"join_optimization": {
"select#": 1,
"steps": [{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"steps": [{
"transformation": "equality_propagation",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}, {
"transformation": "constant_propagation",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}, {
"transformation": "trivial_condition_removal",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}
}, {
"substitute_generated_columns": {}
}, {
"table_dependencies": [{
"table": "`settlement_business_order`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}]
}, {
"ref_optimizer_key_uses": []
}, {
"rows_estimation": [{
"table": "`settlement_business_order`",
"range_analysis": {
"table_scan": {
"rows": 69,
"cost": 9.75
},
"potential_range_indexes": [{
"index": "PRIMARY",
"usable": true,
"key_parts": ["id"]
}, {
"index": "idx_origin_id",
"usable": false,
"cause": "not_applicable"
}],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
}]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [{
"index": "PRIMARY",
"ranges": ["0 < id"],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 69,
"cost": 7.2509,
"chosen": true
}],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 69,
"ranges": ["0 < id"]
},
"rows_for_plan": 69,
"cost_for_plan": 7.2509,
"chosen": true
}
}
}]
}, {
"considered_execution_plans": [{
"plan_prefix": [],
"table": "`settlement_business_order`",
"best_access_path": {
"considered_access_paths": [{
"rows_to_scan": 69,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"resulting_rows": 69,
"cost": 14.151,
"chosen": true
}]
},
"condition_filtering_pct": 100,
"rows_for_plan": 69,
"cost_for_plan": 14.151,
"chosen": true
}]
}, {
"attaching_conditions_to_tables": {
"original_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"attached_conditions_computation": [],
"attached_conditions_summary": [{
"table": "`settlement_business_order`",
"attached": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}
}, {
"finalizing_table_conditions": [{
"table": "`settlement_business_order`",
"original_table_condition": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"final_table_condition ": "((`settlement_business_order`.`goods_type_code` not in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}, {
"refine_plan": [{
"table": "`settlement_business_order`"
}]
}]
}
}, {
"join_optimization": {
"select#": 2,
"steps": [{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"steps": [{
"transformation": "equality_propagation",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}, {
"transformation": "constant_propagation",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}, {
"transformation": "trivial_condition_removal",
"resulting_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}
}, {
"substitute_generated_columns": {}
}, {
"table_dependencies": [{
"table": "`settlement_business_order`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}]
}, {
"ref_optimizer_key_uses": []
}, {
"rows_estimation": [{
"table": "`settlement_business_order`",
"range_analysis": {
"table_scan": {
"rows": 69,
"cost": 9.75
},
"potential_range_indexes": [{
"index": "PRIMARY",
"usable": true,
"key_parts": ["id"]
}, {
"index": "idx_origin_id",
"usable": false,
"cause": "not_applicable"
}],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_applicable_aggregate_function"
},
"skip_scan_range": {
"chosen": false,
"cause": "has_group_by"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [{
"index": "PRIMARY",
"ranges": ["0 < id"],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 69,
"cost": 7.2509,
"chosen": true
}],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 69,
"ranges": ["0 < id"]
},
"rows_for_plan": 69,
"cost_for_plan": 7.2509,
"chosen": true
}
}
}]
}, {
"considered_execution_plans": [{
"plan_prefix": [],
"table": "`settlement_business_order`",
"best_access_path": {
"considered_access_paths": [{
"rows_to_scan": 69,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"resulting_rows": 69,
"cost": 14.151,
"chosen": true,
"use_tmp_table": true
}]
},
"condition_filtering_pct": 100,
"rows_for_plan": 69,
"cost_for_plan": 14.151,
"sort_cost": 69,
"new_cost_for_plan": 83.151,
"chosen": true
}]
}, {
"attaching_conditions_to_tables": {
"original_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"attached_conditions_computation": [],
"attached_conditions_summary": [{
"table": "`settlement_business_order`",
"attached": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}
}, {
"optimizing_distinct_group_by_order_by": {
"simplifying_group_by": {
"original_clause": "`settlement_business_order`.`order_code`,`settlement_business_order`.`goods_type_code`",
"items": [{
"item": "`settlement_business_order`.`order_code`"
}, {
"item": "`settlement_business_order`.`goods_type_code`"
}],
"resulting_clause_is_simple": true,
"resulting_clause": "`settlement_business_order`.`order_code`,`settlement_business_order`.`goods_type_code`"
}
}
}, {
"finalizing_table_conditions": [{
"table": "`settlement_business_order`",
"original_table_condition": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))",
"final_table_condition ": "((`settlement_business_order`.`goods_type_code` in ('6!610!61003!012','6!610!61003!012')) and (`settlement_business_order`.`id` > 0))"
}]
}, {
"refine_plan": [{
"table": "`settlement_business_order`"
}]
}, {
"considering_tmp_tables": [{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"columns": 1,
"row_length": 5,
"key_length": 0,
"unique_constraint": false,
"makes_grouped_rows": false,
"cannot_insert_duplicates": false,
"location": "TempTable"
}
}
}, {
"adding_sort_to_table": "settlement_business_order"
}]
}]
}
}, {
"join_optimization": {
"select#": "fake",
"steps": [{
"substitute_generated_columns": {}
}, {
"table_dependencies": [{
"table": "``",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}]
}, {
"rows_estimation": [{
"table": "``",
"table_scan": {
"rows": 0,
"cost": 2.5
}
}]
}, {
"considered_execution_plans": [{
"plan_prefix": [],
"table": "``",
"best_access_path": {
"considered_access_paths": [{
"rows_to_scan": 0,
"access_type": "scan",
"resulting_rows": 0,
"cost": 2.5,
"chosen": true,
"use_tmp_table": true
}]
},
"condition_filtering_pct": 100,
"rows_for_plan": 0,
"cost_for_plan": 2.5,
"sort_cost": 0,
"new_cost_for_plan": 2.5,
"chosen": true
}]
}, {
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [],
"attached_conditions_summary": [{
"table": "``",
"attached": null
}]
}
}, {
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`create_time`",
"items": [{
"item": "`create_time`"
}],
"resulting_clause_is_simple": true,
"resulting_clause": "`create_time`"
}
}
}, {
"finalizing_table_conditions": []
}, {
"refine_plan": [{
"table": "``"
}]
}, {
"considering_tmp_tables": [{
"adding_sort_to_table": ""
}]
}]
}
}, {
"join_execution": {
"steps": [{
"materialize": {
"select#": 1,
"steps": []
}
}, {
"materialize": {
"select#": 2,
"steps": [{
"sorting_table": "settlement_business_order",
"filesort_information": [{
"direction": "asc",
"expression": "`settlement_business_order`.`order_code`"
}, {
"direction": "asc",
"expression": "`settlement_business_order`.`goods_type_code`"
}],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [],
"filesort_summary": {
"memory_available": 262144,
"key_size": 1612,
"row_size": 5083,
"max_rows_per_buffer": 51,
"num_rows_estimate": 69,
"num_rows_found": 12,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 32872,
"sort_algorithm": "std::sort",
"sort_mode": "<varlen_sort_key, packed_additional_fields>"
}
}]
}
}, {
"sorting_table": "<union temporary>",
"filesort_information": [{
"direction": "asc",
"expression": "`create_time`"
}],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [],
"filesort_summary": {
"memory_available": 262144,
"key_size": 9,
"row_size": 70097,
"max_rows_per_buffer": 3,
"num_rows_estimate": 15,
"num_rows_found": 62,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 33272,
"sort_algorithm": "std::sort",
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
}
}]
}
}]
}
索引失效
-
右模糊匹配时。如 where name like ‘%五%’。
ps：我们框架的com.to8to.common.search.ExampleBuilder#buildSearch就是全模糊匹配，慎用。
-
联合索引使用了只使用了右边的索引。
explain select * from t8t_fi_fip.fip_order where project_id = 24290460; explain select * from t8t_fi_fip.fip_order where project_id = 24290460 and service_type_code = '6!613!61301!006'; explain select * from t8t_fi_fip.fip_order where service_type_code = '6!613!61301!006'; -
索引字段是varchar使用int查询时。
explain select * from t8t_ps_smg.smg_server_order where relate_code = 32114239874982374; -
索引参与运算或使用函数。
<select id="selectWaitingConfirmedList" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List"/> FROM eda_message_instance WHERE status = #{status} AND #{currentTime} - create_time >= #{timeOut} </select> -
对索引列使用NOT IN或<>，使用NOT EXISTS 代替NOT NULL，使用 > and < 代替 <>。
explain select * from t8t_ps_smg.smg_server_order where pay_mode not in (0,1,2); -
索引查询的结果集最好不要超过50%，一般推荐在30%以下。
select count(1) from t8t_ps_smg.smg_server_order; #347818 select count(1) from t8t_ps_smg.smg_server_order where pay_mode in (0,1); #205175 explain select * from t8t_ps_smg.smg_server_order where pay_mode in (0,1); -
在大字段上建立索引。
。。。。。。。。
-
exists 和 in 的效率对比。
explain select * from t8t_fi_fip.fip_order a where a.id in (select order_id from t8t_fi_fip.fip_order_timeline where directsaler_time > 1585126816); explain select * from t8t_fi_fip.fip_order a where exists (select 1 from t8t_fi_fip.fip_order_timeline b where b.order_id = a.id and b.directsaler_time > 1585126816);
707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
