MySql

索引

索引，是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较少且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增加时，性能则会急剧下降。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级。索引的实现通常使用B树及其变种B+树。

索引存储结构

B-Tree索引

B-Tree 即Balance Tree，是一个平衡树（多路搜索树），又称BTree。

在B树中，每个结点最多含有4个子结点。除了根结点和叶子结点其它每个结点至少有2个结点。若根结点不是叶子结点，则至少有两个子结点。最重要的一点就是所有叶子结点都出现在同一层，叶子结点不包含任何关键字的信息。

你可以将键和值存放在内部节点；B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

B+Tree索引

B+Tree是BTree的变体。在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。

B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。

区别

数据库为什么使用B+树而不是B树？

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。
• B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
• B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
• B+树增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

聚簇索引

聚簇索引（也叫聚集索引），不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。

InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有找打这样的索引，会隐式定义一个逐渐来作为聚簇索引。一个表只能有一个聚簇索引。

聚集的数据优点：

• 可以把相关数据保存在一起。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree结构中存储，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。

缺点：

• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价非常高，因为会强制InnoDB将每个被更新新的行移动到新的位置。
• 二级索引（非聚簇索引）存储空间较大，因为在二级索引的叶子节点包含了引用行的主键列（列值）。
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引叶子节点保存的不是只想行的指针，而是行的主键值。意味着二级索引查找时，先查找到二级索引的叶子节点获取到主键值，然后再根据主键值查找聚簇索引对应的位置。所以这里是两次B+Tree查找，而不是一次。

聚簇索引与非聚簇索引主要的区别

聚簇索引：

InnoDB存储引擎的主键是基于聚簇索引。聚簇索引就是整个表，每个叶子节点存储都包含了主键值、事务ID、回滚指针以及所有的剩余列。

还有一点不同就是，InnoDB的二级索引和聚簇索引很不同，InnoDB二级索引的叶子节点中存储不是行（主键）指针，而是主键值，并以此作为指向的指针。（这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作，但是会有2次查询的缺陷，上面有提到）

非聚簇索引：

非聚簇索引（普通索引）在叶子结点并不包含所有行的数据记录，只是会在叶子结点存有 自己本身的键值和主键的值。在检索数据时，通过普通索引叶子结点上的主键来获取想要查询的行数据记录。

另外，MyISAM存储引擎就是非聚簇索引。非聚簇索引相对聚簇索引数据存储分布上比较简单，索引中每个叶子节点都存储了行号与列值。它的主键列存储与二级索引存储格式上完全相同，都是在叶子节点存储行号与列值。实际上主键索引就是一个名称为Primary的唯一非空索引。

聚簇索引字段使用建议

尽量不要使用UUID做主键索引，一是因为UUID的长度比较大，会导致索引占用的空间也大；二是由于UUID完全是随机的字符串，所以在新行插入时，会增加很多额外工作：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除了，或者还没被加载到缓存中，InnoDB在插入之前不得不先从磁盘上读取目标页到内存中。者将导致大量的随机I/O。
• 因为写入是乱序的，InnoDB不得不频繁地做页分裂处理，以便新的行分配空间。
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

尽量使用顺序值来作为主键存储，例如在单库单表的情况下，可以采用自增列作为主键。在分库分表情况下，可以采用一些中间件（如Zookeeper）来处理，而最好不要图意省事，使用UUID。

索引类型

• 主键索引: 数据列不允许重复，不允许为NULL.一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

空间方面：索引需要占物理空间。

创建索引注意事项

1. 单表索引：单表索引建议控制在5个以内，单索引字段数不允许超过5个。字段超过5个时，实际已经起不到有效过滤数据的作用了。

2. 更新频繁：不要在更新频繁的字段建立索引，因为更新字段的同时，索引对应的存储位置也需要维护，频繁的更新会导致许多额外的系统开销。

3. 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

4. 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

5. 最左前缀匹配原则：非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

   =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

作者： Zealon
崇尚简单，一切简单自然的事物都是美好的。