开心一刻
一天，我和隔壁小男孩一起乘电梯，中途进来一位高挑的美女，她牵着一条雪白的贵宾犬
小男孩看着这条雪白的贵宾犬，甚是喜欢，说道：阿姨，我能摸下这个狗狗吗？
美女：叫姐姐
小男孩低头看了下贵宾犬，虽说有点不乐意，但还是说道：阿姨，我能摸下这个姐姐吗？
我极力克制，奈何实在忍不住，哈哈哈...
基础回顾
正式开讲之前，我们先来回顾一下索引相关的知识，如果你们已经知道，就当温习，如果你们还不知道，那么你们有福了，又可以学到新知识了
数据结构
关于表索引概念，我相信你们都听说过，也能说上来
索引是为了加速对表中数据行的检索而创建的一种分散存储的
数据结构
所以索引是一种数据结构，那具体是什么数据结构，你们知道吗？还不知道的小伙伴赶紧小本本记录下
索引的数据结构包括
哈希表、B树、B+树等，而用的最多的就是B+树
既然用的最多的是 B+树，那么我们就来看看 B+树 结构的索引到底长什么样；以 MySQL 为例（其他数据库类似，大家不要被案例给限制住了），假设我们有表 tbl_index
CREATE TABLE tbl_index (
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1),
KEY idx_c2 (c2)
);
基于 C1 有 聚簇索引 c1 ，基于 C2 有 非聚簇索引 c2（非聚簇索引亦称二级索引）
-
InnoDB 索引
InnoDB 下的
聚簇索引和二级索引还是有区别的聚簇索引的叶子节点上存放了完整的数据行，二级索引的叶子节点上数据记录并不是完整的数据行，而是索引列+主键列的数据组合 -
MyISAM 索引
MyISAM 的
聚簇索引和二级索引结构一致，只是聚簇索引多个唯一性约束不管是
聚簇索引还是二级索引，叶子节点上存放的数据都是索引列+数据行指针
B+树 就如上图中介绍的 聚簇索引 或 二级索引 那样，是一个倒立的树结构，它有很多特性，涉及内容很多，这里就不细讲了，感兴趣的自行去查阅资料
索引列顺序
单列索引的列顺序好说，它就一列，不存在列先后顺序的问题，按这个列的值进行顺序排序，存储到 B+树 中就好，上面两图都是单列索引；但在实际应用中，更多的还是用到组合索引（在多列上建一个索引），既然有多列，那就存在列与列之间的顺序问题了；那组合索引的的结构具体是什么样的了？假设我们有表 tbl_group_index，在 c2 列和 c3 列上建一个组合索引 idx_c2_c3
CREATE TABLE tbl_group_index (
c1 INT,
c2 INT,
c3 CHAR(1),
c4 INT,
PRIMARY KEY(c1),
KEY idx_c2_c3 (c2,c3)
);
那么二级索引 idx_c2_c3 的结构是什么样的，你们能想象出来吗
先按 c1 列排序，若 c1 列相等了再按 c2 列排序，概括就是，按组合索引指定的列，从左往右逐个排序；整体上先按第一列排序，第一列相等的数据整体按第二列排序，第一列相等且第二列相等的数据整体按第三列排序，以此类推
索引失效
基于 MySQL 5.7.30 的 InnoDB 引擎，准备表并初始化数据
CREATE TABLE tbl_customer_recharge_record (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
customer_id INT(11) NOT NULL COMMENT '顾客ID',
customer_name VARCHAR(50) NOT NULL COMMENT '顾客姓名',
recharge_type TINYINT(2) NOT NULL COMMENT '充值方式 1:支付宝, 2:微信,3:QQ,4:京东,5:银联,6:信用卡,7:其他',
recharge_amount DECIMAL(15,2) NOT NULL COMMENT '充值金额, 单位元',
recharge_time DATETIME NOT NULL COMMENT '充值时间',
remark VARCHAR(500) NOT NULL DEFAULT 'remark' COMMENT '备注',
PRIMARY KEY (id),
KEY idx_c_id(customer_id),
KEY idx_name_type_time(customer_name,recharge_type,recharge_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客充值记录表';
INSERT INTO tbl_customer_recharge_record(customer_id, customer_name,recharge_type,recharge_amount,recharge_time) VALUES
(1,'李小龙',1,10000,NOW()),
(2,'李连杰',2,20000,NOW()),
(1,'李小龙',2,10000,NOW()),
(1,'李小龙',3,10000,NOW()),
(2,'李连杰',7,20000,NOW()),
(3,'成龙',3,15000,NOW()),
(1,'李小龙',1,10000,NOW());
一共三个索引
- 基于 id 列的聚簇索引
- 基于 customer_id 列的二级索引
idx_c_id - 基于 customer_name,recharge_type,recharge_time 列的组合索引（也是二级索引）
idx_name_type_time
后续会用 explain 查看 SQL 的执行计划，不了解的小伙伴可以先去学习学习
一切准备就绪后，我们来看看索引失效的场景
全表扫描更优
优化器在进行优化的时候，会从众多可选的执行计划中选择它认为最优的那一个，当优化器计算得出通过全表扫描比通过索引查询更优时，它会选择全表扫描的方式进行查询，我们来看几个案例
select * from tbl_customer_recharge_record where customer_id = 2;
执行计划如下
通过索引 idx_c_id 来查询，跟我们预想的一样，相信你们也没有什么异议；我们接着往下看
select * from tbl_customer_recharge_record where customer_id = 1;
执行计划如下
大家看仔细了，能用的索引包括 idx_c_id，但实际没用它，而是走的全表扫描，因为优化器认为走全表查询成本更低，查询更快
MySQL 5.6 新引入的一项跟踪功能：OPTIMIZER_TRACE，可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中；跟踪功能默认是关闭的，我们要用它的话，需要将其开启
set optimizer_trace='enabled=on';
查看优化器优化步骤
优化器对
select * from tbl_customer_recharge_record where customer_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":"multiple equal(1, `tbl_customer_recharge_record`.`customer_id`)"
},
{
"transformation":"constant_propagation",
"resulting_condition":"multiple equal(1, `tbl_customer_recharge_record`.`customer_id`)"
},
{
"transformation":"trivial_condition_removal",
"resulting_condition":"multiple equal(1, `tbl_customer_recharge_record`.`customer_id`)"
}
]
}
},
{
"substitute_generated_columns":{
}
},
{
"table_dependencies":[
{
"table":"`tbl_customer_recharge_record`",
"row_may_be_null":false,
"map_bit":0,
"depends_on_map_bits":[
]
}
]
},
{
"ref_optimizer_key_uses":[
{
"table":"`tbl_customer_recharge_record`",
"field":"customer_id",
"equals":"1",
"null_rejecting":false
}
]
},
{
"rows_estimation":[
{
"table":"`tbl_customer_recharge_record`",
"range_analysis":{
"table_scan":{
"rows":7,
"cost":4.5
},
"potential_range_indexes":[
{
"index":"PRIMARY",
"usable":false,
"cause":"not_applicable"
},
{
"index":"idx_c_id",
"usable":true,
"key_parts":[
"customer_id",
"id"
]
},
{
"index":"idx_name_type_time",
"usable":false,
"cause":"not_applicable"
}
],
"setup_range_conditions":[
],
"group_index_range":{
"chosen":false,
"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":100,
"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_computation":[
],
"attached_conditions_summary":[
{
"table":"`tbl_customer_recharge_record`",
"attached":"(`tbl_customer_recharge_record`.`customer_id` = 1)"
}
]
}
},
{
"refine_plan":[
{
"table":"`tbl_customer_recharge_record`"
}
]
}
]
}
},
{
"join_execution":{
"select#":1,
"steps":[
]
}
}
]
}
内容很多，但我们只需要关注
{
"considered_execution_plans":[
{
"plan_prefix":[
],
"table":"`tbl_customer_recharge_record`",
"best_access_path":{
"considered_access_paths":[
{
// 走索引 idx_c_id，花费成本 2.8
"access_type":"ref",
"index":"idx_c_id",
"rows":4,
"cost":2.8,
"chosen":true
},
{
// 走全表，花费成本 2.4
"rows_to_scan":7,
"access_type":"scan",
"resulting_rows":7,
"cost":2.4,
"chosen":true
}
]
},
// 对比下来，最终选择花费成本更低的全表扫描
"condition_filtering_pct":100,
"rows_for_plan":7,
"cost_for_plan":2.4,
"chosen":true
}
]
}
相比于使用索引，全表扫描话费成本更低，那为什么还选择索引呢？
LIKE 非前一致匹配
也就是我们平时说的
LIKE 进行后方一致或中间一致的匹配，例如 LIKE '%abc' 或 LIKE '%abc%'
如果 LIKE 进行前一致匹配，索引还是会生效的，例如
select * from tbl_customer_recharge_record where customer_name like '成%';
但如果是非前一致匹配，则不会使用索引，例如
select * from tbl_customer_recharge_record where customer_name like '%杰';
至于为什么，大家可以结合 索引列顺序 中的内容来分析，不难哦
OR 未全部使用索引
数据量太少，优化器会选择全表扫描，而不走索引了，我们再加点数据
INSERT INTO tbl_customer_recharge_record(customer_id, customer_name,recharge_type,recharge_amount,recharge_time) VALUES
(1,'李小龙',1,10000,NOW()),
(2,'李连杰',2,20000,NOW()),
(3,'成龙',3,15000,NOW()),
(4,'吴京',5,500,NOW()),
(5,'吴越',4,200,NOW()),
(6,'张晋',6,100,NOW()),
(7,'梁小龙',7,2000,NOW()),
(8,'释小龙',1,3000,NOW()),
(9,'甄子丹',2,4000,NOW()),
(10,'元彪',3,5000,NOW()),
(11,'钱嘉乐',4,5000,NOW()),
(12,'钱小豪',5,5000,NOW()),
(13,'洪金宝',6,5000,NOW()),
(14,'刘家良',6,5000,NOW()),
(15,'刘家辉',5,5000,NOW()),
(16,'邹兆龙',4,5000,NOW()),
(17,'林国斌',3,5000,NOW()),
(18,'赵文卓',2,5000,NOW()),
(19,'于荣光',1,5000,NOW()),
(20,'杨紫琼',1,5000,NOW()),
(1,'李小龙',1,5000,NOW()),
(2,'李连杰',2,5000,NOW()),
(3,'成龙',2,5000,NOW()),
(13,'洪金宝',2,5000,NOW()),
(9,'甄子丹',1,5000,NOW()),
(20,'杨紫琼',1,5000,NOW()),
(18,'赵文卓',1,5000,NOW()),
(11,'钱嘉乐',1,5000,NOW()),
(16,'邹兆龙',1,5000,NOW()),
(19,'于荣光',1,5000,NOW());
如果 OR 条件都能用到索引的话，还是会走索引查询的，例如
select * from tbl_customer_recharge_record where customer_id = 11 OR customer_name = '钱小豪';
如果 OR 条件中有部分条件不能走索引，则会进行全表扫描，例如
select * from tbl_customer_recharge_record where customer_id = 11 OR recharge_type = 2;
为什么 recharge_type = 2 使用不了索引 idx_name_type_time，因为 未遵循最左匹配原则
未遵循最左匹配原则
不管是 组合索引 还是 单列索引，都遵循从左往右匹配的规则，如果能匹配上则可以通过索引过滤，否则只能通过全表扫描来过滤
为什么要遵循从左往右匹配的规则，因为索引结构决定了只能从左往右匹配；没想明白的小伙伴往上翻一翻，去回顾下索引数据结构
单列索引 就不举例了，前面的 LIKE 已经演示过了，我们来看下 组合索引 的情况，如果遵循最左匹配原则，例如
select * from tbl_customer_recharge_record where customer_name = '成龙' and recharge_type = 3;
不能直接跨过前面的列，否则就不能用到索引了，例如
select * from tbl_customer_recharge_record where recharge_type = 3;
强烈建议：书写 SQL 时，WHERE 条件的顺序严格按组合索引的顺序，从左往右逐个书写
虽说有些数据库（例如 MySQL）里顺序颠倒后也能使用索引（优化器会优化列顺序来适配索引），但是性能还是比顺序正确时差一些
否定谓词
否定谓词包括：<>, !=, NOT IN, NOT EXIST，这些都会导致全表扫描，例如
select * from tbl_customer_recharge_record where customer_id <> 2;
列运算
说的更准确点，是在查询条件的谓词左侧对索引列进行运算，这种情况就不能用索引了，例如
select * from tbl_customer_recharge_record where customer_id - 1 = 1;
在谓词右侧进行运算，还是能用到索引的，例如
select * from tbl_customer_recharge_record where customer_id = 1 + 1;
列函数
说的更准确点，是在查询条件的谓词左侧对索引列使用函数，这种情况就不能用索引了，例如
select * from tbl_customer_recharge_record where ABS(customer_id) = 2;
这是因为：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
在谓词右侧使用函数，还是能用到索引的，例如
select * from tbl_customer_recharge_record where customer_id = ABS(2);
强烈建议：使用索引时，查询条件式谓词左侧是原始列，不要对其进行运算和使用函数
隐式类型转换
数据库的某些数据类型是可以兼容其他类型的，比如 VARCHAR 兼容 INT，如果当查询条件谓词右侧表达式的类型与与谓词左侧列类型不一致时，数据库会自动进行类型转换，将兼容性低的类型转换成兼容性高的类型，例如将 INT 类型转换成 VARCHAR 类型，这就是 隐式类型转换
我们来看案例，假设我们有表 tbl_char 及数据
CREATE TABLE tbl_char (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
type CHAR(1) NOT NULL,
name VARCHAR(50),
PRIMARY KEY (id),
KEY idx_type(type),
KEY idx_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tbl_char(type, name) VALUES
('1',null),
('2','1'),
('3','2'),
('4',null),
('5','5'),
('6','6'),
('7','7');
注意看 type 字段的类型，是 CHAR，那么 SQL
select * from tbl_char where type = 2;
能走索引吗，我们来看执行计划
CHAR 与 INT 作比较，因为 INT 兼容 CHAR，所以是将 CHAR 转换成 INT，那么上述 SQL 就相当于
select * from tbl_char where CAST(type AS signed int) = 2;
是不是回到了前面讲过的 列函数 的情况了，对查询条件谓词左侧的索引列使用函数，不会使用索引；如果 type 字段类型是 INT，而 SQL 是
select * from tbl_char where type = '2';
此时会不会走索引了？我们来分析下，同样是将 CHAR 转换成 INT，那么 SQL 相当于
select * from tbl_char where type = CAST('2' AS signed int);
相当于对查询条件谓词右侧的常量使用函数，是可以使用索引的！
隐式类型转换不仅会增加额外的性能开销，还会导致索引不可用，可以说是有百害而无一利
强烈建议：使用索引时，查询条件谓词右侧表达式类型应该与谓词左侧列类型保持一致
NULL
给大家看两个案例，我就不下结论了
-
IS NOT NULL
select * from tbl_char where name is not null; -
IS NULL
select * from tbl_char where name is null;
NULL 的水很深，详情可查看：神奇的 SQL 之温柔的陷阱 → 为什么是 IS NULL 而非 ＝ NULL ？
强烈建议：所有列都指定 NOT NULL 和默认值
总结
-
索引的数据结构包括 哈希表、B树、B+树 等，而用的最多的就是 B+树
-
索引失效的常见场景
- 全表扫描优于索引扫描
- LIKE 非前一致匹配
- OR 条件未全部使用索引
- 未遵循最左匹配原则
- 使用否定谓词
- 查询条件谓词左侧，对索引列进行运算
- 查询条件谓词左侧，对索引列使用函数
- 隐式类型转换
- NULL
如上只是列举了常见部分，并未列举全，欢迎各位小伙伴补充
-
推荐做法
- 书写 SQL 时，WHERE 条件的顺序严格遵循最左匹配原则，从左往右逐个书写
- 使用索引时，查询条件谓词左侧应该是原始列，右侧是常数且类型与左侧列一致，左右侧都不参与计算、使用函数（计算、函数运算、逻辑处理都交由专门的开发语言去实现）
- 所有列都指定 NOT NULL 和默认值，避免 NULL 的陷阱
