MySQL之索引
索引
一、索引是什么
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
二、索引原理
1、B+Tree模拟器
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
2、原理
数据库除了存储数据本身之外，还维护着一个满足特定查找算法的数据结构，这些结构以某种方式指向数据，这样就可以基于这些数据结构实现高效查找算法。这种结构就是索引,MySQL中索引是B+树实现的,每个索引都对应一棵B+树
图1
图1所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能。
图2
添加25时，最左边11-22变成11-22-25进行分裂，22提到父节点，31-32改变成22-31-32，22-31-32空间进行二次分裂，31提上去22 32分开。
3、B+Tree树结构
根节点
最上面一层叫根节点（可以是非叶子节点，也可以是叶子节点，保存键值、指针）
非叶子节点
不是叶子节点的都是非叶子节点（仅保存索引列和指针）
叶子节点
最下边一层叫叶子节点（具体行记录，保存键值、指针）
4、计算存储数量
在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k
InnoDB存储引擎最小存储单元----Page页，一个page页默认16K，page可以储存指针（主键键值），也可以储存行记录，其中指针指向下一个page的地址
两层高度的B+Tree存储数量=根节点指针数*每个指针对应第二层的行记录数
根节点指针数=page的大小/(主键类型大小+指针大小)
每个指针对应第二层的行记录数=page的大小/一行记录大小
例：
如果主键是int类型 ：占用4字节 指针：6字节
根节点指针数=16K/10=1638
行记录数=16K/1K=16
如果B+Tree两层的话就是1638 * 16=26208
三层就是1638 * 1638 * 16=42928704
5、为什么使用B+Tree
1、充分利用空间局部性原理，适合磁盘存储。
2、树的高度很低，能够在存储大量数据情况下，进行较少的磁盘IO。
3、能够很好支持单值，范围查询，有序性查询。
4、索引和数据分开存储，让更多的索引存储在内存中
因为索引是储存在文件中，所以读取索引要通过磁盘io，而磁盘io相对内存io来说是一个相当缓慢的过程，可以理解为查找的时候并不是把整个索引树都载入内存当中（数据量一大，索引也非常庞大，甚至到几个G），而是取树的第一个节点，然后在进行比较之后再选择下一个节点。
三、索引优劣势
优势
提高数据检索效率，降低数据库IO成本
通过索引列对数据进行排序，降低数据排序成本，降低了CPU消耗
劣势
一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。(空间的代价)
索引是对数据的排序，当对表中的数据进行增、删、改操作时，都要维护修改内容涉及到的B+树索引。所以在进行这些操作时需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护索引(时间上的代价)
四、explain详解
±—±----------------±--------±-----±--------------------±------±-----------±-----±-----±---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±----------------±--------±-----±--------------------±------±-----------±-----±-----±---------+
id:
id相同代表：执行顺序由上到下
id不同代表：如果是子查询，id越大的越先被执行，理解转化为数学运算：1+（1*(1+1)）
id有相同有不同代表：先执行id值大的，然后顺序执行id相同的
select_type:
.simple
它表示简单的select,没有union和（子查询：暂定 简单的子查询）
eg:
sql如下（示例）：
EXPLAIN SELECT * FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00';
sql如下（示例）：
EXPLAIN SELECT * FROM (SELECT * FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00')a;
.primary
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
eg:
sql如下（示例）：
EXPLAIN SELECT * FROM (SELECT count(*) FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00')a;
.union/union result
union语句的第二个或者说是后面那一个.
union result：UNION的结果
eg:
sql如下（示例）：
EXPLAIN SELECT * FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00'
union SELECT * FROM `sms_record_2021_02` where create_time BETWEEN '2021-02-01 00:00:00' and '2021-02-01 00:01:00';
.dependent union
UNION中的第二个或后面的SELECT语句，取决于外面的查询。
UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
sql如下（示例）：
EXPLAIN SELECT * FROM `sms_record_2021_01`
union SELECT * from sms_record_2021_01 s1 where create_time>=(SELECT MAX(create_time) FROM `sms_record_2021_02` s2 where s1.id=s2.id)
.derived
在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里(子查询位于FROM子句)。
eg:
sql如下（示例）：
EXPLAIN SELECT * FROM (SELECT count(*) FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00')a;
SUBQUERY/DEPENDENT SUBQUERY
1、SUBQUERY：子查询中首个SELECT（如果有多个子查询存在）：
eg:
sql如下（示例）：
EXPLAIN SELECT * FROM sms_record_2021_01 where create_time=(SELECT MAX(create_time) FROM `sms_record_2021_01` where create_time BETWEEN '2021-01-01 00:00:00' and '2021-01-01 00:01:00');
2、DEPENDENT SUBQUERY：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
重点解释 子查询的查询方式依赖于外面的查询结果.用这个例子就是,先进行子查询外部的查询,得到一个结果集,.然后这个结果的每一行在跟select子查询的结果集进行匹配,也就是说,外部结果集的每一行都要关联内部结果集一次
sql如下（示例）：
EXPLAIN SELECT * FROM sms_record_2021_01 s1 where create_time=(SELECT MAX(create_time) FROM `sms_record_2021_02` s2 where s1.id=s2.id);
table:输出行所用的表
type:显示连接使用了何种类型。
从最好到最差的连接类型为const、eq_reg、ref、range、index和all
const：表最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快,记住一定是用到primary key 或者unique
eq_ref:唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
ref:非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。
range:只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
index :该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
ALL: 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
possible_keys:
显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。
key:
显示查询语句实际使用的索引。若为null，则表示没有使用索引
key_len:
显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。
ref:
显示索引的哪一列或常量被用于查找索引列上的值。
rows:
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好。
Extra:
Using filesort(order by)： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary(group by)： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
select tables optimized away：在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
五、场景
1.创建场景
适合：
主键自动建立唯一索引
频繁作为查询的条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
单间/组合索引的选择问题（在高并发下倾向创建组合索引）
查询中排序的字段，若通过索引去访问将大大提高排序的速度
不适合：
表记录太少
Where条件里用不到的字段不创建索引
经常增删改的表、频繁更新的字段不适合创建索引：因为每次更新不单单是更新了记录还会更新索引，加重IO负担
数据重复且分布平均的表字段，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
2.失效场景
1、字段是字符串
1.1、like 模糊查询 %在字符串前边 ‘%字符串%’ ‘%字符串’
EXPLAIN SELECT * FROM `sms_record_2021_01` where mobiles like '%152%';
EXPLAIN SELECT * FROM `sms_record_2021_01` where mobiles like '%152';
失效原理：
152%代表找以152开头的所有，152叫前缀
%152%代表任意包含152的值，152叫中缀
%152代表找以152结尾的所有，152叫后缀
字符串在B+树里面存储的时候，也是按照字母的大小去排序。首先按照第一个字母去比较，如果第一个字母相同则按照第二个字母去比较和最佳左前缀法则相似。如果左边用了%，那后面的字符是无序的，此时就不能使用二分查找来定位元素还是退化为了全表扫描。
1.2、not like失效
EXPLAIN SELECT * FROM `sms_record_2021_01` where mobiles not LIKE '188%'
1.3、精确查询（隐式转换） = 若没有引号包着会失效
查询条件中使用了整型值去做查询，则无法命中索引。（在这种情况下，两个参数都被转化为浮点数再进行比较，但由于 ‘1’ ，‘ 1’ ，‘1a’ …）这样的数字都会被转化成1，故MySQL 无法使用索引只能进行全表扫描
如果索引列是数值则可以，因为索引是建立在int 的基础上，而将纯数字的字符串可以百分之百的转化成数字，故可以用到索引，虽然也会进行一定的转换，消耗一定的资源，但是最终任然会使用到索引。
EXPLAIN SELECT * FROM `sms_record_2021_01` where mobiles = 18811649409;
1.4、or 只要or两边的条件有一个没有加索引 有索引的那一个条件就会失效 除非两边的列都加索引才能生效（仅限=）
EXPLAIN SELECT * FROM `sms_record_2021_01` where mobiles = '18811649409' or app_id=11;
2、字段是数值
2.1、>= 、 >失效 如果>=或者>的条件是最大值索引有效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id>=2220090215175711001;
2.2、<= 、 <失效 如果<=或者<的条件是最小值索引有效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id<2220090511204411150;
例外：
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id<2220090215175711001;
2.3、！=失效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id!=2220090215175711001;
2.4、运算符+ - * /失效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id +1 =2220091113323911011;
2.5、BETWEEN …and…失效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id BETWEEN 2220090511204411150 and 2220091113323911010;
2.6、not in失效 in有效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id not in( 2220090215175711001,2220090511204411150);
2.7、is not null失效 is null有效
EXPLAIN SELECT * FROM `t_mall_bargain_0` where f_activity_order_id is not null;
3、联合索引（a,b）
3.1、违背最左原则，索引失效
失效原理：
联合索引的B+树也相差不多，因为联合所有有多个字段，下面的图以两个字段为例子，比如两个字段为(a,b),其实和单值索引的不同至少他的键值对不是一个，而是多个
当我们只分析a时,会发现a是有序的，1，1，2，2，3，3
当我们只分析b时,会发现b是无序的，1，2，1，4，1，2
但是如果我们先根据a排序，再来看b，就会发现在a确定的情况b其实也是有序的。
这个就是我们联合索引命中的原理。即a本身有序，在a确定的情况下，b又是有序的，所以就相当于都是有序的
3.2、对最左边的列进行范围查询，索引失效
select * from test where a>1 and b=2;
失效原理：
要找到一个b有序的前提是确定a的值，范围查找中大于a的值可能是一个，也可能是一百万个。所以，a不确定，b就是无序的，哪怕b用=
3、有效场景（优化）
1、 减少 select * 的使用
select *会查询很多不必要的字段,造成不必要的网络传输和IO消耗
2、 order by 优化
当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。
filesort 出现的情况举例
order by 字段不是索引字段
order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc;
order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc;
解决：可以使用主键id进行排序
3、 group by优化
其原理也是先排序后分组，其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。
4、不要对索引做以下处理
计算，如：+、-、*、/、!=、<>、is null、is not null、or
函数，如：sum()、round()等等
手动/自动类型转换，如：id = “1”，本来是数字，给写成字符串了
5、 最左前缀法则
适合场景：数据量大，并且多个查询条件同时出现的时候
1015
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
