MySQL-索引基本学习

InnoDB索引结构

• 每个页里面有最大和最小记录标识record_type=2,3
• record_type=1是目录项 0是最终存储记录项
• 一个页16kb=16384字节 可以存很多记录了基本3层就已经很多条记录了
• 每个节点的左儿子小于父节点，父节点又小于右儿子
• 索引每个节点的数据是已经排序好了的，当然如果你是联合索引，那也是排序好了的。

索引的代价

空间问题

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成。一个表上索引建的越多，就会占用越多的存储空间 由于每个非主键索引的叶子节点上都是主键的值。如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。 **显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。**所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。当然，如果分库分表有时你不得不用上更长的有序id，如雪花id。

时间问题

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作， 索引越多时 增删改记录的时候性能就越差。

聚簇索引特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义： 页内的记录是按照主键的大小顺序排成一个单向链表。 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2. B+树的叶子节点存储的是完整的用户记录。 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。 我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。

3. 这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建 ，InnoDB存储引擎会自动的为我们创建聚簇索引，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录 都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

   简而言之：叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引

二级索引

1. 二级索引都会根据自己的数据大小进行从左到右排序建立一个二叉树
2. 叶子节点只存储索引列与主键两列的值。目录项之存储了页号与索引列

回表

如果我们的索引走的是二级索引，根据页目录定位到叶子节点，然后定位到具体记录，然后我们要根据具体记录的id去聚簇索引找到具体记录。

联合索引

每条目录项记录都由联合索引的列和页号这三个部分组成，各条记录先按照第一个列的值进行排序，如果记录的相同，则按照第二列的值进行排序。

MyISAM索引结构

InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储，意味着都是二级索引。

页分裂与索引空间的回收问题

页分裂的过程

在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证MySQL的B+Tree页顺序问题。 为了保证这个状态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。就称为页分裂。 如果移动记录时发现目标页还放不下，这时候需要申请一个新的数据页，然后挪动部分数据过去,性能更受影响

有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程

1. B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一。
2. 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间，删除普通索引影响不大，但是最好不要对主键索引重建，因为会影响表内所有索引树

索引基本使用

覆盖索引

select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

全值匹配

这个不用怎么说 全值匹配索引列完事，如果查询字段也是索引列和id，那也不用回表。

分组

如果你group by 用到了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的。可以直接就分组排序了。

左匹配原则

字符左匹配比较简单。

需要注意一点的是联合索引的顺序需要跟条件一个顺序，范围查找可能中断顺序。

如果联合索引是 name birthday 下面语句将只能用name的索引

• SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’ AND birthday > ‘1980-01-01’;

ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出 (name,birthday,phone_number)联合索引

• SELECT * FROM person_info WHERE name = ‘A’ ORDER BY birthday, phone_number LIMIT 10;
• SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10; 这个查询只能先把符合搜索条件country = ‘China’的记录提取出来后再进行排序，是使用不到索引。
• ASC DESC 不能混用：因为索引结构里面顺序都是按照一个方向排序的。

GROUP BY 跟ORDER BY规则一样

索引条件下推

select * from tuser where name like ‘张%’ and age=10; 联合索引(name, age)

在5.6之前 只能匹配张%的数据一个个回表。到主键索引上找出数据行，再对比字段age的值。

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

索引的选择

1. 考虑数据的基数离散性
2. 数据类型的选择尽量小，这个空间复杂度和时间复杂度都会更好
3. 在字符串太长的情况的索引最好切割开索引比如(name(10))
4. 不做运算
5. 主键的设计，由于结构的原因是根据大小排序，最好是自增的。
6. 冗余索引与联合索引的顺序优化。比如索引 c 与联合索引 (c,d)

基本总结

1. B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
2. B+树索引适用于下边这些情况：

• 全值匹配
• 匹配左边的列
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 用于排序
• 用于分组

3. 在使用索引时需要注意下边这些事项：

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。