在日常生活之中，MySQL的索引原理还是非常重要的，也是一个程序很多时候的瓶颈所在。本文会梳理包括MySQL 索引和其他的一些MySQL 知识，下面就开始讲。
下面是参考的博客地址：
http://huyan.couplecoders.tech/mysql/读书笔记/高性能mysql/2019/06/01/Mysql索引原理及其优化/
什么是索引
先上定义：
索引是存储引擎用于快速找到记录的一种数据结构。
啥意思呢？
说白了，空间换时间。
索引通过额外的空间占用，类似于目录一样，对某些数据进行了重新排序和整理，这样才能够在查找的时候进行比较快速的寻找。
其关键就是对于某些数据的重新排序。我们都知道，在一个数据表之中，数据是有天然的ID的。如果我们按照这个默认的ID，也就是插入顺序来查找某些数据，除了遍历之外别无他法。但是如果我们对某些经常查询的数据进行重新排序，那么在再次查找的时候，就可以通过二分法复杂度 O(logn)来快速得到这个值的具体位置，然后通过随机存储直接得到这个数据的完整条目。
而MySQL使用的是B+树，在效率上面比单纯的线性链表式索引效率要更高。
所谓「随机访问」，指的是当存储器中的讯息被读取或写入时，所需要的时间与这段信息所在的位置无关。相对的，存取顺序访问（Sequential Access）存储设备中的信息时，其所需要的时间与位置就会有关系（如磁带）。
索引的优缺点？
索引的优点
首先，按照上面我们所说的，索引可以帮我们快速的找到数据。其次，因为索引之中的数据是顺序储存，所以可以帮我们进行orderby 的操作。第三，索引之中虽然只是部分，但是其中也是存储了真正的值的，部分查询可以直接在索引之中完成，不需要去查大表。
总结了一下，索引的优点就是：
- 减少查询所需要扫描的数据量（加快了查询速度）
- 减少服务器的排序操作和创建临时表的操作（加快了groupby和orderby的操作）
- 将服务器的随机IO变为顺序IO（加快了查询速度）
索引的缺点
之前我们讲过，索引是用空间换时间，那么节省了时间肯定是要从空间上面找补回来的。
索引的主要缺点也就是在空间方面。
索引，按照我们之前讲的，也就是一张额外的表格。其也需要存储，因此会带来额外的空间占用。其次，在插入，更新和删除操作的同时，对这个索引表格也要进行进一步的维护，要有额外的时间开销。
总结如下：
- 索引占用磁盘或者内存空间
- 减慢了插入更新操作的速度
实际生产环境之中，在一定数据范围之内，建立索引的开销是远远小于其带来的好处的。
索引类型
对MySQL而言，其索引是在存储引擎部分实现的，因此不同的存储引擎所实现的索引也不太一样。InnoDB目前是使用最广泛的引擎，使用的索引是B+树，因此大部分时间的索引也都是他。
MySQL目前有以下几种索引：
- B-树索引 / B+ 树索引
- 哈希索引
- 空间数据索引
- 全文索引
本文之中只探讨 B- 树索引和 B+ 树索引之间的不同与其带来的好处：
B+树在B树上面加上了以下的限制：
- 每个中间节点不保存数据，这意味着所有非叶子节点的数据都被保存了一份放在叶子节点之中
- 叶子结点在B-树的基础之上按照自身的顺序进行了连接。
这样的好处有什么呢？
- 中间节点不保存数据了，那么相对于B树而言，每一个非叶子节点其可以存储的数据更多，那么树的层级就更少，其查询数据更快
- B+树的查询速度更稳定：因为所有关键字的数据地址都存储在叶子节点上面，所以其每次查找的速度都相同，相比而言更加稳定
- 所有叶子节点按照顺序链组成了链表，因此可以更方便的进行范围查询
其中第三点是最重要的！ 这也是为什么B+树在查找某一节点的时候速度其实是比B树慢，但是仍然被InnoDB所使用的原因！
怎么创建高性能的索引？
因为优化索引和优化查询一般是分不开的，因此这里会包含部分的查询优化。
前缀索引和索引选择性
如果是给一个很长的字符串上面加上索引，可以考虑使用前缀索引。
数据库在使用索引进行查找的时候，主要是分以下两步：
- 在索引的B+树上面找到对应的查找的值，比如找到名字是”abcdefghijk”的一条记录，并且拿到这条数据在磁盘上面的地址
- 根据地址去磁盘上面查找该记录，并且将该记录取出。
这就涉及到我们所说的前缀索引是否可以优化该记录了：
如果我们发现，其实”abcde”在这个数据表之中就已经是唯一的数据，那么我们使用“abcde“来作为该数据的索引，是不是可以起到和”abcdefghijk“一样的效果？
那么此时我们就完成了一次”前缀索引“。而且我们发现，其长度远远小于完整数据的长度。这就是前缀索引的作用。
索引的选择性： （不重复的值）/（所有的值）， 那么可以看出来，索引的选择性的值可选范围为0-1，0的情况为所有的值都有一条或更多的重复值，而1的情况为所有的值都互不重复，都是独立的值。
前缀索引：
在对一个比较长的字符串做索引的时候，可以只选择其中的一部分字符，从而提高索引效率。但是这样也会降低我们刚才所说的，”索引的选择性“。
我觉得这个概念还是比较容易理解，毕竟短的字符串肯定比长的字符串更容易重复，那么我们截取其中的一部分，当然其选择性会降低。
我们使用下面的方法来算出来一些前缀的选择性，比如：
select
count(distinct left(school_name,3))/count(*) as sch3,
count(distinct left(school_name,4))/count(*) as sch4,
count(distinct left(school_name,5))/count(*) as sch5,
count(distinct school_name)/count(*) as original
from
user;
前三个语句分别是取其前3，4，5个字符作为索引的时候其选择性。如果我们慢慢增大这个数值，在其和第四个（原本的选择性）差别不大的时候，就是一个比较合适的前缀索引的长度了。
选择好长度之后，就可以创建一个前缀索引了：
alter table user add index sch_pre3(`school(3)`)
联合索引
在一张表之中，我们经常会遇到对多个列进行索引的需求，因为查询的需求多种多样，这个时候我们可以选择建立多个独立的索引，或者建立一个联合索引。大多数的时候都是联合索引更适合一些。
下面假设一个查询语句：
select * from user where school_name = 'abcdefg' and age > 30
我们要在school_name 和 age 上面分别建立两个独立的索引，那么预期是这条查询语句会命中两条索引。但是用explain 命令查看，会发现其不一定。
理论上讲，MySQL之中支持合并索引，也就是同时使用两个索引。但是 MySQL 之中的优化器不一定这样认为，其可能会认为， 查询两次 B+树 的代价高于查询一次索引之后再去大表之中进行数据过滤，因此其选择只用一个索引。
创建联合索引的语法：
alter table user add index school_age(`school`,`age`)
使用联合索引的时候，一个比较重要的因素就是：
所有的索引列只可以进行最左前缀匹配， 例如上面的 school_age 联合索引，如果只有 age 作为查询条件，是不可以使用的。例如使用
select * from user where age =20
是无法命中上面的联合索引的。
那么选择索引的判断标准是什么呢？
如果不考虑查询的情况之下，应该将选择性高的列放在联合索引的前面。但是实际上，我们都是考虑查询情况的。也就是我们需要通过查询来反推索引，使得某个固定的查询尽可能的命中索引，”根据业务设计索引“，这样才能加快查询速度。
因此联合索引的优化更多的是根据某个或者某些语句优化的。
最左前缀索引的原理
当数据库有序的时候，mysql可以使用索引。那么假设我们建立一个联合索引，示例数据如下：
| school | age |
|---|---|
| a | 12 |
| b | 12 |
| b | 14 |
| b | 15 |
| c | 1 |
我们可以看到，首先在这个索引之中，school是完全有序的，索引school可以使用索引。
我们之前讲到过，索引是对某一列进行的有序排列，而在这个联合索引之中，只有school是有序排列的，age部分没有有序排列。因此对于age字段，我们没有办法直接应用索引。
那么什么时候我们可以使用age作为索引呢？可以看到，当左边一列，也就是school固定的时候，比如值为”b” 的时候，age就是有序的了。所以当使用school 做定值匹配 的时候，例如当school=b 的时候，对于这三条数据而言，age字段就是有序的了。因此可以使用age作为索引。这就是最左前缀 的原理。
另外，最左前缀只可以使用一个范围查询，例如:
select * from user where school>a;
select * from user where school=b and age>12;
以上这两句都会用到最左前缀索引。但是如果多余一个范围查询，例如：
select * from user where school>a and age>12
就不可以。原因是当school作为范围匹配的时候，mysql无法确认age字段是否严格有序，比如school的范围命中了b,c的四条数据，那么age就不是有序的，无法使用后续的索引了。
聚簇索引——存储数据的方式之一
聚簇索引不是一种索引类型，而是一种存储数据的方式。InnoDB的聚簇索引是在同一个数据结构之中放入了索引和数据。
不管一个表有多少索引，在磁盘上面其肯定有且只有一种排序方式，所以一个表上面只可以有一种聚簇索引。
InnoDB使用主键来进行聚簇索引，如果没有主键的话，就会选择一个唯一的非空索引，如果还没有，InnoDB就会生成一个隐式的主键来进行聚簇索引。之所以必须有一个聚簇索引的列，是因为数据必须在磁盘上面有一种排序方式来存储。
这也是InnoDB推荐自增主键的原因。因为自增主键自增且连续，那么在InnoDB插入数据的时候，就可以不断的在后面追加数据即可。如果使用UUID作为主键，那么每一次的插入操作，其都需要先找到当前主键在已经排序的主键当中的位置，然后再插入。并且其要移动主键之后的数据，以保持情况之后的数据和主键的顺序相同，这样子的代价非常高。
那么我们可以对”在索引的叶子节点之中，其存储的数据不是该数据的真实物理地址，而是该数据的主键“这种方式做一些合理的猜测和解释了：
由于在聚簇索引之中，主键如果不使用自增主键，那么在插入数据的时候其有可能会修改主键和物理地址之间的关系，因为主键是要建立索引的，而索引必须有序。在这种情况之下，如果索引之中存储的数据是真正的物理地址，那么就意味着每次插入数据其都需要对索引进行进一步的整理和修改，那么就非常耗费资源了。
聚簇索引和非聚簇索引的区别可以用一个简单的例子来说明:
当我们拿到一本书的时候,目录就是主键,是一个聚簇索引,因为在目录中连续的内容,在正文中也是连续的,当我们想要查看迎着阳光盛大逃亡章节,只需要在目录中找到它对应的页面,比如459,然后去对应的页码查看正文即可.
而非聚簇索引呢,则类似于书后面的附录专有名词索引一样(二级普通索引),当你查找邦达列夫的时候,附录会告诉你,这个名词出现在了迎着阳光盛大逃亡一节,然后你需要去目录(主键索引)中再次查找到对应的页码.
覆盖索引
当一个索引包含，或者说覆盖需要查询的字段的值时，我们就称之为覆盖索引。
设想有如下的查询语句:
select
school_name,age
from
user
where
school_name = 'a'
这个语句之中所需要的信息，已经被我们刚才所说的联合索引全部覆盖了，那么就不必要找到之后再进行查找主键等等的操作了。而且索引的大小，一般是小于完整数据的大小的，覆盖索引可以极大的减少从磁盘之中加载数据的数量。
为什么前缀索引和覆盖索引无法一起使用？
从二者定义上面的区别就可以看出来。前缀索引，是选取某个字段的部分值作为其索引，而覆盖索引是指一个索引包含所有需要查询的值。二者从定义上面就相互矛盾。而且，假设二者一起使用了，由于前缀索引之中的内容是部分的，实际上还是要从数据库之中使用主键来进一步获得其完整信息，那么就起不到覆盖索引的作用。
删除冗余和重复的索引
有一些索引是在查询之中从未使用，但是却白白增加数据开销的。这种索引要及时删除。
比如：
- 在主键上面再建立一个普通索引。毋庸置疑，这个是毫无作用的。
- 在有联合索引
school_age的情况之上，再次建立一个school的独立索引。由于索引的最左前缀匹配规则，school_age是可以完全命中school的单独查询的，因此后者可以删掉。
如何查看索引的一些相关信息
索引信息
在mysql之中，可以使用show index from table_name 来查看某个表上面的索引，其将会有下面的输出。
也可以使用show create table table_name 来查看建表语句，其中包括创建索引的语句。
索引大小
在mysql 5.0 之后的版本之中，可以通过查看information_schema.TABLES 之中的数据来获取更加详细的数据。
该表各字段的含义如下表:
| 字段 | 含义 | ||
|---|---|---|---|
| Table_catalog | 数据表登记目录 | ||
| Table_schema | 数据表所属的数据库名 | ||
| Table_name | 表名称 | ||
| Table_type | 表类型[system view | base table] | |
| Engine | 使用的数据库引擎[MyISAM | CSV | InnoDB] |
| Version | 版本，默认值10 | ||
| Row_format | 行格式[Compact | Dynamic | Fixed] |
| Table_rows | 表里所存多少行数据 | ||
| Avg_row_length | 平均行长度 | ||
| Data_length | 数据长度 | ||
| Max_data_length | 最大数据长度 | ||
| Index_length | 索引长度 | ||
| Data_free | 空间碎片 | ||
| Auto_increment | 做自增主键的自动增量当前值 | ||
| Create_time | 表的创建时间 | ||
| Update_time | 表的更新时间 | ||
| Check_time | 表的检查时间 | ||
| Table_collation | 表的字符校验编码集 | ||
| Checksum | 校验和 | ||
| Create_options | 创建选项 | ||
| Table_comment | 表的注释、备注 |
索引碎片
在索引的创建删除过程中,不可避免的会产品索引碎片,当然还有数据碎片,我们可以通过执行optimize table xxx来重新整理索引及数据,对于不支持此命令的存储引擎来说,可以通过一条无意义的alter语句来触发整理,比如:将表的存储引擎更换为当前的引擎,alter table xxxx engine=innodb.
