神奇的SQL之擦肩而过真的用到索引了吗 [复制链接]

索引的数据结构

什么是数据库索引，相信大家都能答上来，索引就是为了加速对表中数据行的检索而创建的一种分散存储的数据结构（索引是一种数据结构）

但具体是什么样的数据结构，很多小伙伴可能就不知道了

索引的数据结构包括哈希表、B树、B+树等，而用的最多的就是B+树

我们以MySQL为例，来看看B+树结构的索引到底是什么样的

表：tbl_index

c1上有聚簇索引，c2上有二级索引（即非聚簇索引）

InnoDB的索引

InnoDB下的聚簇索引和二级索引还是有区别的

MyISAM的索引

MyISAM聚簇索引和二级索引结构基本一致，只是聚簇索引有个唯一性约束

B+树就是如上图中的那样一个倒立的树结构

B+树有很多特性，这里就不细讲了，有兴趣的可以去查阅相关资料

组合索引的列顺序

单列索引的列顺序好说，它就一列，不存在列先后顺序的问题，按这个列的值进行顺序排序，存储到B+树中就好，上面两图都是单列索引

但在实际应用中，更多的还是用到组合索引（在多列上建一个索引），既然有多列，那就存在列与列之间的顺序问题了

那组合索引的的结构具体是什么样的了？

我们有表：tbl_group_index，在c2列和c3列上建一个组合索引idx_c2_c3

那么，索引idx_c2_c3的结构如下

先按c1列排序，若c1列相等了再按c2列排序

抽象化就是，按组合索引指定的列，从左往右逐个排序；整体上先按第一列排序，第一列相等的数据整体按第二列排序，第一列相等且第二列相等的数据整体按第三列排序，以此类推

索引的擦肩而过

有的小伙伴可能急了：“楼主，前戏太多了，我要看主角！！！”

楼主：“你怕是个杠精吧，前戏不写长点，怎么凑够篇幅？你去看看现在的动漫，哪个不是正戏不够前戏来扣？（更可恶的是还有一大截尾戏拼凑）”

好了，不多扯了（再扯楼主怕是有生命危险了），我们一起来看看今天的主角们！

环境准备

MySQL版本：5.7.30-log，存储引擎：InnoDB

准备表：tbl_customer_recharge_record，并初始化7条数据

一共有3个索引：

id列上的聚簇索引

customer_id列上的二级索引：idx_c_id

以及customer_name,recharge_type,recharge_time列上的组合索引：idx_name_type_time

后面我们会用EXPLAIN来查看执行计划，查看索引使用情况，对它还不熟的小伙伴，赶紧点进去先看看

全表扫描更优

这是什么意思了，就是说优化器在进行优化的时候，会从众多可选的执行计划中选择它认为最优的那一个

当优化器计算得出通过全表查询比通过索引查询更优时，它会选择全表扫描的方式进行查询

SQL：explainselect*fromtbl_customer_recharge_recordwherecustomer_id=2;

相信大家对这个没什么异议，通过idx_c_id来完成查询，跟我们预想的一样

对于explainselect*fromtbl_customer_recharge_recordwherecustomer_id=1;大家睁大眼睛看清楚了啊！

能用的索引包括：idx_c_id，但实际没用它，而是走的全表查询；因为优化器认为走全表查询成本更低，查询更快

MySQL5.6新引入的一项跟踪功能：OPTIMIZER_TRACE，可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等）

并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE中

跟踪功能默认是关闭的，我们要用它的话，需要将其开启：setoptimizer_trace=enabled=on;

查看优化器优化步骤：select*frominformation_schema.OPTIMIZER_TRACE;

优化器对select*fromtbl_customer_recharge_recordwherecustomer_id=1；优化步骤如下

{steps:[{join_preparation:{select#:1,steps:[{expanded_query:/*select#1*/select`tbl_customer_recharge_record`.`id`AS`id`,`tbl_customer_recharge_record`.`customer_id`AS`customer_id`,`tbl_customer_recharge_record`.`customer_name`AS`customer_name`,`tbl_customer_recharge_record`.`recharge_type`AS`recharge_type`,`tbl_customer_recharge_record`.`recharge_amount`AS`recharge_amount`,`tbl_customer_recharge_record`.`recharge_time`AS`recharge_time`,`tbl_customer_recharge_record`.`remark`AS`remark`from`tbl_customer_recharge_record`where(`tbl_customer_recharge_record`.`customer_id`=1)}]}},{join_optimization:{select#:1,steps:[{condition_processing:{condition:WHERE,original_condition`tbl_customer_recharge_record`.`customer_id`=1),steps:[{transformation:equality_propagation,resulting_condition:multipleequal(1,`tbl_customer_recharge_record`.`customer_id`)},{transformation:constant_propagation,resulting_condition:multipleequal(1,`tbl_customer_recharge_record`.`customer_id`)},{transformation:trivial_condition_removal,resulting_condition:multipleequal(1,`tbl_customer_recharge_record`.`customer_id`)}]}},{substitute_generated_columns:{}},{table_dependencies:[{table:`tbl_customer_recharge_record`,row_may_be_nullalse,map_bit:0,depends_on_map_bits:[]}]},{ref_optimizer_key_uses:[{table:`tbl_customer_recharge_record`,field:customer_id,equals:1,null_rejectingalse}]},{rows_estimation:[{table:`tbl_customer_recharge_record`,range_analysis:{table_scan:{rows:7,cost:4.5},potential_range_indexes:[{indexRIMARY,usablealse,cause:not_applicable},{index:idx_c_id,usable:true,key_parts:[customer_id,id]},{index:idx_name_type_time,usablealse,cause:not_applicable}],setup_range_conditions:[],group_index_range:{chosenalse,cause:not_group_by_or_distinct},analyzing_range_alternatives:{range_scan_alternatives:[{index:idx_c_id,ranges:[1=customer_id=1],index_dives_for_eq_ranges:true,rowid_ordered:true,using_mrr:false,index_only:false,rows:4,cost:5.81,chosen:false,cause:cost}],analyzing_roworder_intersect:{usable:false,cause:too_few_roworder_scans}}}}]},{considered_execution_plans:[{plan_prefix:[],table:`tbl_customer_recharge_record`,best_access_path:{considered_access_paths:[{access_type:ref,index:idx_c_id,rows:4,cost:2.8,chosen:true},{rows_to_scan:7,access_type:scan,resulting_rows:7,cost:2.4,chosen:true}]},condition_filtering_pct:,rows_for_plan:7,cost_for_plan:2.4,chosen:true}]},{attaching_conditions_to_tables:{original_condition`tbl_customer_recharge_record`.`customer_id`=1),attached_conditions_