MYSQL索引
何为索引？有什么作用？
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
B 树， B+树
数据库系统普遍采用B-/+Tree作为索引结构（例如mysql的InnoDB引擎使用的B+树，理解不透彻B树，则无法理解数据库的索引机制。
B-树，即为B树。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，B-tree就是指的B树
图片演示过程网站https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
先看一下B树和B+树区别（都为3阶）
https://blog.csdn.net/yueguangmuyu/article/details/118732261
MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。B-Tree结构：
- 索引值和data数据分布在整棵树结构中
- 每个节点可以存放多个索引值及对应的data数据
- 树节点中的多个索引值从左到右升序排列
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
B+Tree结构：
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接，提高区间的访问性能
相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。
B树和平衡二叉树-采用B树原因
注意B树和平衡二叉树，树的出现是为了弥合不同的存储级别之间的访问速度上的巨大差异，实现高效的 I/O。平衡二叉树的查找效率是非常高的，并可以通过降低树的深度来提高查找的效率。但是数据量越来越大，导致深度过大，效率仍然很低，并且树的存储的元素数量是有限的，数据量过大会导致内存空间不足以容纳所有节点，相比之下，B树非常适合读取和写入相对较大的数据块（如光盘）的存储系统。它通常用于数据库和文件系统。B树就是二叉搜索树的一般化。
B 树和 B+树-采用B+树原因
- B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
前面是 B-Tree，后面是 B+Tree，两者的区别在于：
- B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
- B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
- B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。
B+树优势
- B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
- B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。
B++高度总是3-4层
show variables like 'innodb_page_size';
行的个数：假设一行数据大小为1K字节（最大不超过8K），默认节点大小16K，那么一行存放16K/1K=16条数据
一个页内指针个数：索引为bigint类型，长度为8字节，指针6字节，存放个数16384/14=1170
高度为2的B+树存放1170*16=18720条数据记录
那么，Mysql高度为3的b+树能存储多少数据呢
前提须知：首先说一下，mysql为了避免每条数据都从磁盘读取，所以设计了页结构，用于磁盘与内存之间的交互单元。索引页和数据页来存放表中数据记录，数据页就是实际数据，索引页是索引键值和指针。InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎。InnoDB使用B+树作为默认的索引数据结构的一个主要原因是，减少磁盘的IO次数。而B+树里的每个节点都是一个页，默认的页大小为16KB
回答：接着，一个页通常是16KB磁盘存储数据最小为扇区，一个扇区512字节，因此InnoDB的数据文件都是16384B(16k)的整数倍。
当高度为二，能存储的数据量=根结点指针书*单个叶子节点记录行数。
假如一个表，主键id是bigint（长度为8字节），MySQL中指针大小为6字节，那么一个索引页的索引个数=页的大小/单个索引的大小，为16*1024=16384，16384/14=1170，一个页就是1170个索引，假设一行数据为1kb，一页就是16行数据。我们用1170*16就是18720行数据。
同理高度为三时，1170*1170*16=21902400。三次IO即可找到所需数据。
索引的优缺点
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
但是，使用索引一定能提高查询性能吗?
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。
而且
- 索引占用物理空间，
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。索引可以提升查询速度，会影响where查询，以及order by排序。
MySQL索引类型如下：
按物理存储分类
从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。
这两个区别在前面也提到了：
- 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
- 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表
在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：
- 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
- 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
- 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。
按字段特性分类
从字段特性的角度来看，索引分为
- 主键索引(创表时创建，一表一个，不为null)、
- 唯一索引(多个唯一，可以null)、
- 普通索引(就很普通)、
- 前缀索引(常用于char、 varchar、binary、varbinary 的列上，前n个作为索引)。-减少索引占用的存储空间，提升查询效率------一般用在邮箱匹配，将前缀提取作为索引
普通索引和唯一索引在查询效率上有什么不同？
基本没有差别，唯一索引限制了该索引只有一个，而普通索引会多几次判断，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页全部加载到内存中进行读取。这些操作所消耗的时间是可以忽略不计的。
# 建表时
# 主键
CREATE TABLE table_name (
....
PRIMARY KEY (index_column_1) USING BTREE
);
# 唯一
CREATE TABLE table_name (
....
UNIQUE KEY(index_column_1,index_column_2,...)
);
# 普通
CREATE TABLE table_name (
....
INDEX(index_column_1,index_column_2,...)
);
# 前缀
CREATE TABLE table_name(
column_list,
INDEX(column_name(length))
);
# 建表后
# 唯一
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
# 普通
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
# 前缀
CREATE INDEX index_name
ON table_name(column_name(length));
二级索引(辅助索引)
唯一索引，普通索引，前缀索引等索引属于二级索引。
其中唯一索引和普通索引在mybatis的mysql总结讲过
- 唯一索引(Unique Key)：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
- 普通索引(Index)：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
- 前缀索引(Prefix)：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
- 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
按字段个数分类
从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。
- 建立在单列上的索引称为单列索引，比如主键索引；
- 建立在多列上的索引称为联合索引；
需要注意用联合索引时，存在最左匹配原则，索引最左侧的如果不在查询条件内，则无法使用索引------利用索引的前提是索引里的 key 是有序的。
例如，索引为(a,b,c)，其中a是全局有序的，而b和c是局部相对有序。
如果where b=1；a不在条件内，则无法使用该索引
联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引。
如果出现a是范围，那么当a找到第一个主键值后该进行什么操作
- 在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
- 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
索引小技巧
一、
联合索引优先选择区分度高的放前面，尽可能的过滤不符合的条件
因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。
二、
select * from order where status = 1 order by create_time asc
最佳：给 status 和 create_time 列建立一个联合索引，可以避免mysql文件排序
查询时如果只有status，最好还得对create_time排序，联合排序筛选后就是排好序的time，避免在文件排序，提高了查询效率。
三、
适合索引的：
where经常查询的
唯一的
GROUP BY和ORDER BY的，建立索引后在B+tree是排序好的
回表
辅助索引搜索到主键值然后搜索主键索引的B+树
select * from user where id=100
直接搜索主键索引的B+树找到数据
select * from user where username='javaboy'
先搜搜username对应的主键值，然后搜索主键索引对应的数据，这就是回表
使用二级索引会出现回表，但不是一定出现的
例如：select age,username,address from user where username='javaboy'
优化索引
- 前缀索引优化；---邮箱
- 覆盖索引优化；--将查询字段设为联合索引
- 主键索引最好是自增的；--否则容易产生页分裂
- NOT NULL
- 防止索引失效；
索引失效：
- 最左匹配原则
- 使用左或者左右模糊匹配，like %xx 或者 like %xx%
- 查询条件使用计算，函数，类型转换等
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：
- 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
- 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表，如下图的黄色部分：
执行语句的过程--MYSQL大局观
先看图------小林图解---------
MySQL 的架构共分为两层：
Server 层和存储引擎层
执行流程
- 连接服务
- 连接器
- TCP 连接
- 查询缓存
- 注意：命中率低，一个更新操作就会清空缓存，MYSQL8.0删掉该流程
- MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer poll。
- 解析SQL
- 解析器----词法分析和语法分析----构造语法树和检查语法
- 执行SQL
- 预处理器----检查表和字段是否存在，将*扩展成所有列
- 优化器---将 SQL 查询语句的执行方案确定下来---索引选择，扫描选择
- 注意优化器会选择查询代价小的索引，基于成本
- 执行器--执行查询和扫描
全索引扫描并不一定就比全表扫描好，取决于数据存储位置。
如果数据在内存，那么这两种没有太大区别。
如果数据在磁盘，全表扫描比全索引扫描要好，这是因为，全表扫描是顺序读数据，sequential read，是顺序IO
而全索引扫描，可能会产生随机读(reandom read),随机IO,显然，顺序读要比随机读快很多。
文件排序和索引排序
EXPLAIN--执行计划
对于执行计划，参数有：
- possible_keys 字段表示可能用到的索引；
- key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
- key_len 表示索引的长度；
- rows 表示扫描的数据行数。
- type 表示数据扫描类型，我们需要重点看这个。
type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：
- All（全表扫描）；
- index（全索引扫描）；
- range（索引范围扫描）；
- ref（非唯一索引扫描）；
- eq_ref（唯一索引扫描）；
- const（结果只有一条的主键或唯一索引扫描）。
在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。
需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。
除了关注 type，我们也要关注 extra 显示的结果。
这里说几个重要的参考指标：
- Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
- Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
- Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。
