1.页
InnoDb 存储数据的时候，不可能所有数据都在一起，这个查询速度太慢，所以和操作系统一样，有页的概念。
默认每一页大小是16kb.
File Header：表示页的一些通用信息，占固定的38字节
Page Header：表索引页专有的一些信息，占固定的56个字节
Infimum & Supremum：两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节
User Records：真实存储我们插入的记录的部分，大小不固定
Free Space：页中尚未使用的部分，大小不固定
Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多
File Trailer：用于检验页是否完整的部分，占用固定的8个字节
总结：就是mysql 在分页后 会记录每一页的信息，比如上一页，下一页，最大值，最小之，索引层 等等重要的信息。
页建立好之后，由于每一页存储着最大值和最小值，所以查询的时候比较方便
那么问题来了，如果数据量够大，那么页数量肯定 也会增加，如果页的数量 很大，那么查找数据在哪一页也是个难题，所以有一个索引页的概念，就是在建一个类似于页的数据结构，它存储每一个大页中的取值范围，如果这个索引也很多的话，在往上面继续建立索引页，这就是索引以及它的数据结构。
示意图如下：
平衡二叉树(红黑树)
像红黑树这样的平衡二叉树，无论如何插入元素，他都可以通过一些旋转的方法调整树的高度，使得整棵树的查询效率维持在O(logN)，如下图所示：
这么来说他已经符合了成为索引的必备条件，但是最终没有选择他作为索引说明还有不足的地方。仔细看看可以发现平衡二叉树的每个节点只有两个孩子节点，如果一张表的数据量特别大，整棵树的高度也会随之上升。一个千万级别的表如果用平衡二叉树作为索引的话，树高将会达到二十多层。这也就意味着做一次查询需要二十多次磁盘io，这是一个不小的开销。
那么有没有能在大数据量的情况下，还能保持一个较小树高的树型结构呢？
B树和B+树
答案就是B树。上面我们说到了平衡二叉树的瓶颈在于一个节点只有两个孩子节点，而B树一个节点可以存放N个孩子节点，这就完美解决了树高的问题，我们可以把B树称为平衡多叉树，B树作为索引如下图所示：
图片来源网络
但是以B树的结构作为索引仍有可以优化的地方，我们先看看最终的B+树，再仔细分析B+树在B树的基础上作了哪些改进，为什么B+树最终能够胜任索引的工作：
图片来源网络
从图片中可以看到B+树同样是一棵多差平衡树，和B树一样很好地解决了树高的问题。
改进点一：
但仔细看可以发现，B树的节点中既存储索引，也存储表对应的数据；而B+树的非叶子节点是不存储数据的，只存储索引，数据全部存储在叶子节点上。
为什么要做这样的改进？我们做一次算术就知道了。
假设树高为2，主键ID为bigint类型，长度为8字节，节点指针为6字节，一行数据记录的大小为1k，一次io操作能获得一页16k的数据。
在索引为B+树的情况下，根节点能存储：16k / (6 + 8) = 1170 条索引指针；到了第一层，一共能指向 1170 1170 = 1368900 条索引指针；到了最底一层叶子节点，一个节点能存储16k / 1k = 16 条记录，一共能存储 1170 1170 * 16 = 21902400 条记录
在B树的情况下，由于非叶子节点使用了大量空间存储数据，存放的索引指针肯定就少，最终整棵树如果想要存储和B+树一样多的数据就必须要增加树高，这样一来就增加了磁盘io，所以说B+树作为索引的性能比B树高。
改进点二：
叶子节点之间使用指针连接，提高区间访问效率。如果我们要进行范围查询，可以轻松通过B+树叶子节点之间的指针进行遍历，减少了不必要的磁盘io。
1.索引定义
2.索引类型
索引分为聚簇索引和非聚簇索引两种，
聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引中数据和索引不在同一个地方 MyISAM；
聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快，一个表中只能有一个聚簇索引 InnoDB。
MyISAM索引文件与数据文件是分离的 索引时无序的:
MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引，Col1为主键。
索引底层实现结构 ： B+ Hash表
参考数据结构
https://www.yuque.com/docs/share/75f86cb7-545f-42bc-8462-4016edd02918?#
3.Explain字段解析
id ： SELECT识别符。这是SELECT的查询序列号，表示查询中执行select子句或操作表的顺序！
select_type ：
| SIMPLE | 简单SELECT(不使用UNION或子查询) |
|---|---|
| PRIMARY | 最外面的SELECT |
| UNION | UNION中的第二个或后面的SELECT语句 |
| DEPENDENT UNION | UNION中的第二个或后面的SELECT语句,取决于外面的查询 |
| UNION RESULT | UNION 的结果 |
| SUBQUERY | 子查询中的第一个SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个SELECT,取决于外面的查询 |
| DERIVED | 导出表的SELECT(FROM子句的子查询) |
table : 使用的表
type : 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序.
| system | 表仅有一行(=系统表)。这是const联接类型的一个特例。 |
|---|---|
| const | 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! |
| eq_ref | 对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 |
| ref | 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 |
| ref_or_null | 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 |
| index_merge | 该联接类型表示使用了索引合并优化方法 |
| unique_subquery | 该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 |
| index_subquery | 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) |
| range | 只检索给定范围的行,使用一个索引来选择行。 |
| index | 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 |
| ALL | 对于每个来自于先前的表的行组合,进行完整的表扫描。 |
possible_keys : 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key : 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
key_len : 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
ref : 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows : 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数
filtered : 显示了通过条件过滤出的行数的百分比估计值。
Extra : 该列包含MySQL解决查询的详细信息
| Distinct | MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 |
|---|---|
| Not exists | MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 |
| Using filesort | MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 |
| Using index | 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 |
| Using temporary | 为了解决查询,MySQL需要创建一个临时表来容纳结果。 |
| Using where | WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 |
| Using sort_union(…), Using union(…), Using intersect(…) | 这些函数说明如何为index_merge联接类型合并索引扫描。 |
| range checked for each record (index map: #) | MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 |
| Using index for group-by | 类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。 |
查看下面实例：
代表简单是使用select 使用了 索引为code的查询
4.InnoDB分析
新增表：
mysql 存储数据的是，当数据过多的时候 ，要建立索引来提高查询速度。
mysql 的表结构索引使用B+树的结构把数据存储起来
首先会存储一个主键对应整条数据的索引； B+树的最底层就是所有数据。数据结构如下.（如果每条数据的大小刚才是 16kb）
联合索引时，可能使用的是字符串的asc编码来进行排序，对应的值不是数据库的具体的数据，而是主键索引，所以当使用非主键索引的情况下，需要回表来再一次的查询数据。
索引是排序的一种数据结构，因为排序 索引查询才快，那联合索引就是根据多字段排序的一种数据结构，那肯定先按照字段一排序，再按照字段二等依次排序，然后数据存储是主键id,然后再回表。
添加联合索引 a,b,c
如果是select *
当使用 联合索引时， 先从联合索引中找到 主键，根据主键回表找到整个数据;
如果是select a,b,c
当使用 联合索引时，因为联合索引就存储这个查询结果a,b,c 所以不需要回表。查询列被所要的数据覆盖，这个叫覆盖索引。
从上面结构可以看到 查询2 能使用索引，查询3 不能使用索引，因为查询满足最左前缀匹配原则，因为和底层存储数据有关，因为只有知道首个数字才能根据排序查询。
当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能 这叫索引下推。
具体的sql 执行过程到底规则是 用语法分析器来决定到底使用哪些索引，以及查询方式，为了io的读取次数最小.
Mysql 索引失效场景
1：数据库索引字段，存在大量数据重复，mysql 语法分析器认为全部扫描比使用索引快。
2：不满足最左前缀匹配原则
3：使用 or
EXPLAIN SELECT * from finance_enter_item where business_type =1 OR id = 12313 //使用索引EXPLAIN SELECT * from finance_enter_item where business_type =1 OR warehouse_id = 12313 //不使用索引
若有收获，就点个赞吧
0 人点赞
