mysql--索引

转载自https://www.cnblogs.com/zslli/p/8946145.html

InnoDb页存储结构

我们可能有很多熟悉的数据库储存引擎，比如说Inoodb，MyISAM，Memory。每一种储存引擎对于数据的持久化可能是不同的，比如说我们的Memory储存引擎的数据都是不会写进磁盘的，所有的数据是保存在内存中的，也就意味着如果我们的服务器进行重启以后，数据是不会被进行保存的。当然，因为MySQL数据库默认的储存引擎是使用的Inoodb，所以我们在这里是需要重点介绍这个储存引擎的。
Inoodb储存引擎是把数据储存在磁盘里面的储存引擎，它在内存和磁盘的交互中使用的是页这个数据单位。我们都知道一个事情就是我们在对磁盘进行访问的时候速度是非常慢的，所以我们肯定是不能接受一条数据一条数据的进行取用。所有数据划分为若干页，一个数据页是可以保存16kb的数据，也就是说我们每次在进行数据访问的时候是一次性的16kb数据。

InnoDb页中记录存储结构

数据页双向链表和页内记录单向链表

聚簇索引

索引的查找就是通过一层一层的定位来实现的，最上层的页我们称之为根节点，中间的我们称之为内节点，最底层的我们称之为叶子节点。我们就是通过页中的槽二分法快速的定位数据所在页或者组中，我们在进行遍历查找。

二级索引

二级索引和聚簇索引的区别就是叶子节点不包括完整的数据。

二级索引储存的只是我们需要使用到的列和主键，如果要其它列的数据怎么办？回表：就是通过二级索引获取到的主键然后到聚簇索引里面去进行查找。

联合索引

联合指的就是多列进行组合：我们用c2和c3创建。

联合索引就是用多个字段进行创建索引，然后根据对应列顺序进行排序。比如上图我们使用的就是c2和c3两个列，所以我们就是现根据c2进行排序，如果c2相同的情况下我们再根据c3进行排序。

索引的创建和删除语法

1：在建表的时候创建索引：index和key二选其一即可

create tabel 表名(列信息) index|key 索引名(创建索引使用的列)

2：在修改表结构的时候我们创建索引：

alter table 表名 add key|index 索引名(索引使用的列)

3：修改表结构删除索引：

alter table 表名 drop key|index 索引名；

索引原理总结

1.一个索引对应一颗B+树，所有的真实记录都是存在叶子节点里面的，所有的项目录都存在内节点或者说根节点上。

2.innodb会为我们的表格主键添加一个聚簇索引，如果没有主键的话数据库是会为我们自动添加row_id这一列的。聚簇索引的叶子节点包含完整的用户记录。

3.我们是可以为自己感兴趣的列添加二级索引的，二级索引的叶子节点没有用户完整的信息，只是拥有对应列和主键的信息，如果想要拥有完整的信息是需要进行回表操作用二级索引找到的主键去聚簇索引寻找完整信息。

4.B+树的每一层节点都是按照索引列的大小信息进行排序而组成的双向链表，每个页里里面的记录也是按照索引列大小信息组成的单向链表。如果是联合索引的话，先按照前面的列进行排序，如果是相同的情况下再根据其他的列进行排序。

5.每个索引的搜索都是从根节点进行的，由于每个页面都按照索引列的值建立了Page Directory，所以在确定了具体页面信息的情况下是可以根据二分法进行快速的定位的。

索引的代价

1.空间上的代价：每一个索引对应的都是一颗B+树，B+树的每一个节点都对应的是一个16kb大小的数据页，如果是一个很大的数据库的话那么占用的内存还是很大的。

2.时间上的代价：我们在上面讲过，每层节点都是按照数据的大小顺序进行排列的单向链表，每个页也是按照大小排列的双向链表。那么我们在对数据进行操作的时候必然避免不了的就是数据的迁移，数据页的删除，回收，分裂等等，如果我们创建的索引过多的话那么对应的问题就是频繁的需要对这些东西进行操作。那就是浪费时间，给性能拖后腿。

索引用处

比如创建name，birthday，phon_num的联合索引

用于查询

在使用联合索引的时候，务必需要记住的就是一定要使用到第一个列，因为我们的索引就是按照第一个列最先开始排序的，如果不按照这个规则进行，那么我们是不能使用到索引的。而且，就如我们最后一条查询而言，我们在进行完成name的索引以后，在相同情况下进行phon_num的查询的时候是不能使用索引的，因为name完成以后是根据birthday进行的索引排序，所以一定要严格按照索引定义的顺序进行查找。

用于排序

我们在使用排序比如说Order by的时候也是可以使用到索引的，如下图所示，具体的规则和我们进行查询的时候是一样的，因为我们索引就是按照顺序已经进行好排序的，所以如果我们的排序的顺序和索引的顺序是一致的，那么完全没问题可以直接取用数据，但是就是一直强调的问题，如果我们列的顺序改变了们就不能在使用B+树了

用于分组

我们在使用group by的时候需要进行分组，这个过程包含了三个部分，第一个是先对name一致的进行分组，第二个在着基础上在对birthday一致的进行分组，然后最后在基础上对phon_num一致的进行分组。这就正好和我们的索引是一致的，所以是可以使用到B+树的，和上面一样，我们的顺序问题是坚决的不能乱的

索引的挑选

1>必须条件：只为我们使用到的查询条件，分组，排序列创建索引。查询列表里面的列我们没有必要建立索引。

2>基数考虑：如果一个列的差异数据不是很多，我们称之为基数小的列。也就是说所有数据的这个列的数据大部分都相同，那么就是基数小，这种列没必要创建索引。

3>数据类型：我们知道的是索引列可以有很多的数据类型，比如说整形数据我们就有TINYINT、MEDIUMINT、INT、BIGINT，它们所占用的空间内存肯定是不一样的，所以我们挑选数据类型小的类型作为索引列的数据类型，可以有效的节约空间，储存更多的数据，那么我们在进行数据取用的时候一次可以加载更多的数据进入内存，减小IO损耗，同时在CPU层次来说，数据类型越小，查询处理的速度是越快的。

4>索引字符串的前缀：这个问题我们在上面其实提到过，我们在使用索引的时候是可以的，那么在创建索引的时候当然也是可以的，这样可以减少很多的内存空间，而且我们在做字符串比较的时候如果我们使用的是前缀那么比较的时间也是可以大大进行缩短的。

5>尽量使用联合索引：因为我们的每一个索引对应的都是一颗B+树，需要使用时间和空间进行维护的，我们文章开始就说了索引需要付出的代价。我们使用联合索引，是可以满足很多字段的索引条件的。

6>主键插入的顺序：记不记得我们在上边说的，索引的一个目录项对应的是一个页，我们的数据都是有序的进行单向链表的维护，那么如果我们的主键在后期插入中间的话就涉及到了位置的移动，目录项的修改，页面分裂，数据迁移等等问题。所以我们建议的是让数据库给主键进行自增生成。

7>避免冗余重复的索引：不要为一个列重复的添加多个索引，这样是不好的，他对效率的提升没有半点的帮助，但是对空间的消耗确实实打实的。

8>覆盖索引：比如我们开始创建的索引是没有包含country这个列的，如果我们如下图所示进行查询，我们本来是可以在索引直接得到三个列的数据，但是差一个列，这时候就必须用主键去聚簇索引进行回表操作了。所以我们查询的列最好都是我们索引的列，也就是说我们是鼓励把需要查询的列明确进行书写的。