04：mysql索引总结

1.为什么需要索引：类似书本的目录，如果没有索引，需要从上到下一个一个遍历。

2.常见的索引模型：hash 有序数组 b+树
（1）hash是k v 的存储结构 用一个hash函数把key换算成一个确定的位置，然后吧value放入到数组中，key可能会重复，重复的拉成链表。hash的值不是递增的，所以对于增加只需要往后追加即可，对于区间查询的时候效率很低，hash表这种结构只适合等值查询的场景，nosql形式。
（2）有序数组对于范围长训性能就非常优秀，他是依次递增的，所以查询的时候根据二分可以快速查询到数据，但是对于增加和删除，就必须挪动后面的所有数据，所以有序数组索引适合静态存储引擎，比如保存20年某个城市的所有人口信息
（3）为什么使用b+，不使用二叉树等，因为对于数据量稍大，几百万的时候，二叉树的层高会有几十层，这时候去硬盘查找，一次io大概10ms，一层 层高就需要一次io，时间成本太高，对于b+树来说，mysql每次io读取一个页是16k，对于所以的根节点存取，一般是主键id，long类型的是8个字节，然后每个索引还会有6个字节只想子树的指针，所以一个页大概可能存取1200个索引，对于四层的树高，前面三层是1200的3次方，大概存取17亿的数据，然后最底层存取叶子节点的数据，读写的性能非常高，目前mysql默认的引擎innodb就是b+树

3.InnoDB 的索引模型：使用了b+数的索引结构，根据叶子节点的内容，分为主键索引和非主键索引（也叫二级索引），主键索引的叶子节点存取了整行数据，又被称为聚簇索引，在mylsam存储引擎中，叶子节点数据是存放在另一片的文件中，和主键拆分存储，所以又叫非聚簇索引。

4.索引怎么去维护：索引是有序的，所以新插入一个数据，需要挪动后面的数据，有时候数据页已经满了，需要去插入新的数据，就会造成页的分裂，性能受到了影响，并且，原本一个页的数据切分为两个，数据的利用率下降。对于相邻的两个页，如果是有删除了数据，那么数据页也会去做合并。对于页分裂，一般来说主键id是自增状态，有序既不会分裂，对于页合并，可以采用逻辑删除的方法。

5.索引优化
（1）回表：在innodb引擎的二级索引中，叶子节点存储的是主键的id。通过索引去找到主键，然后通过主键id去找到整行数据再返回。这种就叫做回表
（2）索引覆盖：为了避免回表，一般根据二级索引去做一次查询的时候，会将主键id带上，这样的话就是索引覆盖。同时在联合索引中，比如根据身份证查询姓名，这种两个联合索引可以高频使用索引覆盖，不要回表，提升效率，但是对于建立冗余索引提升查询效率，需要去做一个权衡，是否值得。
（3）最左匹配：对于联合索引 a b c，查询的时候，where条件之后a ab abc 都可以直接使用索引，但是bc c是不可使用的，对于a c前面的也是可以使用前面索引查询筛选。
（4）索引下推，对于查询语句 select * from tuser where name like ‘张%’ and age=10 and ismale=1; 如果没有索引下推，会给所有姓张的查询出来，然后对于每个姓张的每行数据进行回表查询，但是索引下推之后，会将 姓张和年龄10岁的进行内部判断，不等于10的直接跳过，省去年龄的回表查询

6.普通索引和唯一索引
一般来说，普通索引和唯一索引区别不大，对于查询，唯一索引会查到既返回，普通索引则会继续向下查询，知道不符合条件才会返回。
对于插入更新来说，需要知道change buffer，做一次更新的时候，如果内存中有这个数据，会将更新操作缓存在change buffer中，这样就不需要再从磁盘中读入数据进行修改。change buffer在内存中有拷贝，他在空余的时间，或者数据库shutdown的时候会merge存入硬盘。他减少了从磁盘中的读取操作。
唯一索引是不能使用change buffer的，因为内存中有数据直接修改，没有数据的时候，会从硬盘读取数据，这时候直接更新内存，就没必要使用change buffer。
对于普通索引来说，如果数据在change buffer，可以直接修改，如果不在的话，这时候会从磁盘读取数据，放入内存之中，因为使用change buffer，是普通索引，所以的话是有序的读取，查询速度很快。
对于和redo log的区别，就是在change buffer中，插入更新等操作，会同样的记录到redo log之中，并且redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

7.MySQL为什么有时候会选错了索引
选择索引是优化器的工作。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
扫描行数是根据解释计划的cardinality基数来判断的，对于创建索引的字段，采样统计，默认选择N页然后得到平均值再乘以总的页数，估算出一个大概的基数。当然，索引上面，数据不同的越多，重复的值越少，那么统计的越加准确。并且，当索引的页数增加的时候，超过一定的比例，就会重新触发做一次索引统计。
对于一些回表等操作也会影响到索引的使用，可能查询的数据过多，并且考虑到回表，不使用索引查询也是有可能的。
对于一些可能优化器使用错误的索引，可以使用force index（）强制进行走索引，或者修改sql，以达到我们期望他所经过的索引。
还有一些索引可能使用错误的情况，可以删掉无用的索引也可以解决这个问题

8.怎么给字符串加上索引
（1）对于字符串，可以直接给加上索引，但是可能会占用空间较多
（2）这种较长的字符串，可以使用前缀索引，对于区分度较高的字符串，可以根据测试，选择合适的长度，截取前面的一部门作为索引存储，这样的话，可能会导致会多查一部分数据，并且不能使用联合索引，所以索引覆盖不能使用。
（3）对于字符串前一部分重复太高类似身份证这样的，可以使用函数翻转进行截取存储，但是这样的话不支持范围查询

9.表空间大小问题
现在基本所有的机制都是，对于删除的操作，只是将状态置于无效，其他的数据是在上面覆盖的，所以删除都是逻辑上的删除，并不是物理上的。对于删除的数据，没有数据覆盖的情况下，大多都是可以找回来的。
对于MySQL来说，可能一张表10G，删除了大量的数据之后，空间并没有变化，只是当前的记录可以被覆盖了而已。
在索引之中，一般来说是一个个的页，对于删除大量的连续的数据，会空出页被复用，但是对于不连续的删除，记录可能就会出现漏洞，占用了空间却没有去使用，随机的插入数据也会这样。
这些数据空洞，一般怎么处理呢，可以去重建表，使用alter table A engine=InnoDB 命令来重建表，在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。MySQL 5.6 版本开始引入的 Online DDL
1.建立一个临时文件，扫描表 A 主键的所有数据页；
2.用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3.生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5.用临时文件替换表 A 的数据文件。

10.使用索引需要注意的
select count(*) from tradelog where month(t_modified)=7; t_modified加上了索引
对于这种，使用函数的，MySQL会找不到具体从哪开始，会放弃走b+ 树的搜索功能，会全表扫描这个索引，全表扫描

mysql> select * from tradelog where tradeid=110717; tradeid是varchar类型
这时候会发生隐式转换，所以会走函数
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
这时候和前面一样，优化器放弃走树搜索的功能

备注：参考极客时间MySQL45讲做的笔记