mysql之索引

每一条数据在内存中都有一个地址值，主键索引 记录了当前地址值。
下面主要了解的是InnoDB 索引：
类型：，普通索引、唯一索引、全文索引（文本类型）

InnoDB 逻辑存储结构

MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。

优化查询，mysql在数据存储结构上优化了B Tree
Balanced Tree 这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）
有大量的索引的结构的调整，就是节点的分裂和合并，其实就是 InnoDB 页的分裂和合并。
所以 解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键
B+树（加强版多路平衡查找树）
B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索 到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一 层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶 子节点

非叶子节点可以存储：102416 的大小 假设一个索引类型占16个字节
那么第一页可以存储1024个单元也就是1024条数据 深度1
第二次查找的范围则是10241024 也就是 1,048,576‬条数据 深度2
第三次为叶子节点为数据存储节点， 深度3
当查询上千万数据时 IO三次磁盘可以找到想要的数据
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数 据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
它是根据左闭右开的区间 [ )来检索数据。
InnoDB 内部使用哈希索引来实现自适应哈希索引特性。
体现在：buffer pool 里面有一块区域是 Adaptive Hash Index 自适应哈希 索引

数据格式

MyISAM 有 MYD,.MYI
一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我 们的 user_myisam 表的所有的表数据。
一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在 id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。
MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索 引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。
InnoDB 有.ibd
它是以主键为索引来组织数据的存储的，所以索引文件和数据文 件是同一个文件，都在.ibd 文件里面。
在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。
InnoDB 中，主键索引和辅助索引是有一个主次之分的。 辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主 键索引中查询，最终取得数据。

如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐 藏的聚集索引，它会随着行记录的写入而主键递增。

索引结构

主键索引

主键索引的叶子节点存储数据是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引

非主键索引的叶子节点是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

非主键索引与主键索引：如果根据非主键索引查询的字段不止是ID，就需要回表一次，通过非主键索引查询得到ID，在查询主键索引得到行数据。

索引维护

因B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
如果新增的数据是有序的那么就在最后一行数据后插入即可。
如果新增数据的索引在索引中间，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

当删除过多时，也会造成页合并。

重建索引

重建普通索引
alter table T drop index k;
alter table T add index(k);
重建主键索引—删除主键还是创建主键，都会将整个表重建
alter table T drop primary key;
alter table T add primary key(id);
注意：主键索引和普通索引都需要重建的话，直接使用语句：alter table T engine=InnoDB。

索引使用原则 （离散度高的字段进行索引）

count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。 数据行数相同的情况下，分子越大，列的离散度就越高。
如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

联合索引最左匹配

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的
建立（左字段，右字段） 索引， 左字段就不用建立索引（因为按照最左匹配原则）

覆盖索引–减少回表

当用非主键索引去查询时，查询的字段在辅助索引下面有时，则为覆盖索引。不用进行会表再次查询，非主键索引都会存储主键。
例如：
表信息：
table: t
字段： id , name , age
主键索引： id
非主键索引：name
用例：
不回表：select id,name from T where name=xxx
回表：select age，id from T where name=xxx

索引条件下推 （例如：组合查询情况下，或者再次范围查询ID）

MySQL 5.6 引入的索引下推优化（index condition pushdown)
当查询条件为组合字段（某个字段模糊查询）时， ICP功能未开启，则会返回多条模糊匹配到主表回写查询；ICP功能开启 则筛选出完全符合的主键去回表查数据。
索引下推为：当where查询条件的字段都可以在同一个索引上找到，避免回表在去主键索引判断。
减少回表次数。

前缀索引

-- 前缀索引创建
alter table table_name add key (column_name(12));
--前缀索引的验证
select count(distinct 字段 ) as L from 表;  -- 统计列上有多少不同的值

-- 截取不同长度的区分度如下， 长度越短越节省空间，区分度越高扫描行越少
-- 根据能接受的比例衡量取值
-- 前缀索引 会使覆盖索引失效
select 
count(distinct left(字段,4)）as L4, 
count(distinct left(字段,5)）as L5,
count(distinct left(字段,6)）as L6, 
count(distinct left(字段,7)）as L7,
 from 表;
--使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，
--在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。


--- 当碰到前缀索引区分度不够高时，
-- 1 可以选择倒叙存储， 例如一个系统只存储了一个县或者一个市的身份证信息，同一个地区身份证号码前缀都一样
select field_list from 表 where id_card = reverse('input_id_card_string');
-- 2 可以使用hash字段，在表中在创建一个整数字段来保存身份证的效验码，同时对这个字段创建索引。
--创建索引
alter table 表 add id_card_crc int unsigned, add index(id_card_crc);
--插入新记录时，用crc32()函数得到效验码放入此字段，由于效验码可能存在冲突，在查询时在加上id_card的条件判断
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

索引的创建与使用

在用于 where 判断 order 排序和 join 的（on）字段上创建索引
索引的个数不要过多-浪费空间，更新变慢。
区分度低的字段，例如性别，不要建索引——离散度太低，导致扫描行数过多
频繁更新的值，不要作为主键或者索引——页分裂
组合索引把散列性高（区分度高）的值放在前面
创建复合索引，而不是修改单列索引。

什么时候用不到索引？

重点：什么算用不到索引？
一：完全没有走索引，全表扫描
二：走了索引，但是扫描了当前索引下的全部数据。例如：name字段有索引，select ‘主键 ’ from t where name like%xx%

索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、 计算（+ - * /）
字符串不加引号，出现隐式转换*
like 条件中前面带%
NOT LIKE 不能
!= （<>）和 NOT IN 在某些情况下可以：

深度分析索引

唯一索引和普通索引取舍

针对于K字段设置普通索引和唯一索引后：

查询方式的区别：

select id from T where k=xxx
当k为唯一索引时：找到一个满足条件K后直接返回。
当K为普通索引时：找到满足条件的第一个记录后，继续向下寻找，直到找到第一个不满足条件的K。

更新方式的区别：

更新会涉及到change buffer。—对change buffer不懂的看下mysql之初识
更新的数据在内存页中：
唯一索引比普通索引多了一次判断。-性能差别不大
更新的数据不在内存页中：
唯一索引:需要将数据页读取到内存中，然后判断，然后插入。
普通索引：则是将更新记录在 change buffer，语句执行就结束了。
区别：将数据页读取到内存中，涉及随机IO访问，是数据库的成本最高的操作之一，change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

不断删除历史数据和新增数据场景

查询索引的采样基数： show index from t

优化器——因扫描行数放弃使用索引：

长事务—》不断发生删除数据—》查询数据 （索引可能失效）
可能发生的原因是：优化器的索引取错了。
优化器的目的：找到并执行最优的方案，最优方案影响因素（扫描行数是之一），最小的扫描行数意味着代价也越小。 其他因素：是否临时表，是否排序等。
扫描行数的判断：会使用随机采样，InnoDB 默认会选择 N 个数据页，统计每个页上不同的值得到一个平均值然后乘以页数就是这个索引的基数。 更新频率：当变更的数据行超过1/M时，重新执行下上面步骤。
索引采样后存储方式：innodb_stats_persistent控制，是否存储在内存中。

• 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
  重新统计索引采样信息：analyze table t

优化器——因排序选择错索引：

--数据库记录 id,a,b 三个字段都是int,且值一样，且从1开始到10W. 总共10万条数据。
-- 前提条件：符合这两个and条件的数据没有一条

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

看这条语句可以分析出来使用A索引的话执行更快。但是优化器确实使用了B索引，扫描行数为5W。
我理解为：因使用了排序 order by b limit 1， 影响优化器选择索引的关键因素也有排序，优化器认为直接使用B索引可以不用排序，减少排序的执行的代价。

处理方案：

删除索引，使用force index，修改语句让优化器意识到，analyze table 重新统计索引采样。