「Mysql索引原理（七）」覆盖索引

       通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回到表中查询呢？ 如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能：

       在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

       不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B+Tree索引所覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

       当发起一个呗索引覆盖的查询是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

       索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段，但不是整个查询涉及的字段。mysql5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

这里索引无法覆盖该查询，有两个原因：

这条语句只检索1行，而之前的 like '%Kim%'要检索3行。

也有办法解决上面所说的两个问题，需要重写查询并巧妙设计索引。

       这种方式叫做延迟关联，因为延迟了对列的访问。在查询第一个阶段MySQL可以使用覆盖索引，因为索引包含了主键id的值，不需要做二次查找。

       在FROM子句的子查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好吧。

数据量大了怎么办？

       这样优化的效果取决于WHERE条件匹配返回的行数。假设这个people表有100万行，我们看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行。

实例1中 ，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。

实例2中 ，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据及上性能提高了很多，优化后的查询效率主要得益于只需读取40行完整数据行，而不是原查询中需要的30000行。

实例3中 ，子查询效率反而下降。因为索引过滤时符合第一个条件的结果集已经很小了，所以子查询带来的成本反而比从表中直接提取完整行更高。

       在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询。

       例如，people表中last_name字段有一个二级索引，虽然该索引的列不包括主键id，但也能够用于对id做覆盖查询：

select id,last_name from people where last_name='hua'

二叉搜索树、N叉树

页分裂：B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一。

页分裂逆过程：页合并，当删除数据后，相邻的两个数据页利用率很低的时候会做数据页合并

主键索引：key：主键，value：数据页，存储每行数据

非主键索引：key：非主键索引，value：主键key，导致回表

最左匹配：优先将区分度高的列放到前面，这样可以高效索引，

最左匹配原则遇到范围查询就停止匹配，范围查询(>、<、between、like)为什么？因为出现范围匹配后，后面的索引字段无法保证有序，局部有序失去，顺序失去则无法提高查询效率

SELECT * FROM table WHERE a IN (1,2,3) and b >1

如何建立索引？

还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

索引组织表

索引用页存储：key【10】-point【6】，通过调整key大小，当页大小固定的情况下，通过调整key大小，使得N叉树变化；

如key 10, point 6则单个索引16字节，页大小为16k，则页面总共可以存储1024个索引，即N大小

覆盖索引: 二级索引的信息已经存在想要的列，例如主键

如果现在有一个高频请求，要根据市民的身份z号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

索引下推优化：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

整理索引碎片，重建表：alter table T engine=InnoDB

  首先是看key的大小，另外是数据页的大小，如果需要改变N，则需要从这两个方面做改动；

一个innoDB引擎的表，数据量非常大，根据二级索引搜索会比主键搜索快，文章阐述的原因是主键索引和数据行在一起，非常大搜索慢，我的疑惑是：通过普通索引找到主键ID后，同样要跑一边主键索引，对于使用覆盖索引的情况下，使用覆盖索引可以直接解决问题

https://blog.csdn.net/itworld123/article/details/115144202

https://time.geekbang.org/column/article/69236

https://zhuanlan.zhihu.com/p/334684710

https://www.cxyzjd.com/article/pyzhizhuren/88431380

https://www.jianshu.com/p/4277d9dd0a9f

https://www.cnblogs.com/rjzheng/p/12557314.html

https://mengkang.net/1302.html

https://note.cser.club/database/bi-xu-le-jie-de-mysql-san-da-ri-zhi-binlogredo-log-he-undo-log

https://cloud.tencent.com/developer/news/44861

---