Mysql索引创建原理与优化

Innodb存储引擎

Innope stores all rcords inside a fixed-size unit which i commonl called a "page though InmnoCB sometimes cals it a “block” instead.Ccurrentyal ages arethe same size,16KB.
Innope将所有RCORD存储在一个固定大小的单元中，我通常将其称为“页面”，但Innocb有时会将其称为“块”。大小为16KB

他的数据结构采取的是B+树的形式，那么什么是B+树呢，实际上也就是B树的一个变种。

我理解Mysql中的B+树只是在B树的基础上增加了叶子节点之间的指向，和传统意义上的B+树还是有所区别，如B+树叶子节点之间是单向链表，Mysql中是双向链表

为什么我插入的时候是没有顺序插入的，但是查询出来却是排好序的呢？

首先肯定不是在查询时候排的序，又没有加order by。既然不是在查询时候排的序，那肯定是插入时候排的序。
那再插入的时候排序不会影响插入性能吗？为了提高查询时候的性能

此时查询a=3（第一个字段，同时也是主键）时，因为逻辑相连，查询到4就可以不用再往下查询了。反之则需要遍历所有数据才可以确定没有这条信息。所以推荐使用自增有序id

在上图中，假设一个页最多存三条，此时我再加入一条主键id为5的将如何变化呢？

此时就体现出页目录的作用了，mysql会将数据区域分组，并像实体书一样展示最小那个所在位置（就像实体书中的文章标题所在页一样）

那如果我们有一百万个页该如何进行搜索呢？

如果我们有一百万个页肯定不能单纯查看每个页的页目录，可以和页目录一样再抽取一个页来只存放页目录信息。每个页相当于一个分组，只显示最小页码即可。此时一个B+树已经显现。

如果我们搜索a（第一个字段）>5和a<5时是如何搜索的？

当我们搜索a>5时，先根据索引找到a=5，然后根据底下的链表找到所有a>5的值。
当我们搜索a<5时，也是根据索引先找到a=5，然后找到a<5的值，但正常B+树是单项链表，如何才能反向找到小于5的值呢。所以再mysql中B+树是双向链表

聚集索引是如何存储的呢？

我们以字段b、c、d为例，就像字符串比较一样，先比较第一位，第一位相同时比较第二位。

如何以图6的方式创建聚集索引有什么问题？

每次都要复制全表，浪费性能。以图六聚集索引为例，abc索引键与列a和列e没什么关系。

单独的聚集索引下存的是主键值，拿到主键值之后再去主键哪里再拿，这样就不用每次都复制整表

优化：

Explain

id:选择标识符
select_type:表示查询的类型。（就是这条语句在完整语句的什么位置）

possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

优化器追踪

explain是没有任何上下文显示的，更加没有cost的，因此在经验不足的情况下，是无法准确定位问题，更不用说复杂查询下优化器自闭的情况了。

优化器追踪实例：

1、查看优化器状态
show variables like ‘optimizer_trace’
2、会话级别临时开启
set session optimizer_trace=“enabled=on”,end_markers_in_json=on;
3、设置优化器追踪内存大小
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
4、执行自己的sql
例：select host from user;
5、information_schema.optimizer_trace表
select trace from information_schema.OPTIMIZER_TRACE;
6、导入到.trace后缀的文件，然后用json解析器查看（如果没有权限，或直接交给运维，让他把trace文件输出给你就行，5和6二选一即可）
select trace into dumpfile “d:\test.trace” from INFOMATION_SCHMA.OPTIMIZER_TRACE;
不设置优化器最大容量的话会导致优化器返回结果不全

将结果中steps中数据取出进行分析

着重观察rows_estimation和considered_execution_plans这两个值。rows_estimation中的cost参数为io代价和cpu代价总和，数值越高性能越差（4万及4秒）