(一)MySql索引优化数据结构与性能优化

索引是帮助Mysql高效获取数据的排好序的数据结构

索引是什么？

1. 官方介绍索引是帮助Mysql高效获取数据的数据结构，更通俗的说，数据库索引好比是一本书前面的目录能加快数据库的查询速度 2. 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中） 3. 我们通常所说的索引，包括聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）索引 索引的优势和劣势 优势： 1. 可以提高数据检索的效率，降低数据库的IO成本类似于书的目录 2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗 被索引的列会自动进行排序，包括【单列索引】和【组合索引】只是组合索引的排序要复杂一些 如果按照索引列的顺序进行排序，对应order by 语句来说，效率就会提高很多 劣势： 1. 索引会占用磁盘空间 2.索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作Mysql不仅要保存数据，还有保存 或者更新对应的索引文件

1. 数据结构及索引实现

- 二叉树（右边的元素大于去父元素,左边的元素小于其父元素） - 红黑树 (是一种自平衡二叉查找树) - Hash表 (Hash表再等值查询时,效率很高,时间复杂度为O(1) 但是不支持范围快速查找,范围查找时还是只能通过扫描全表来实现) - B-Tree (叶节点具有相同的深度,叶节点的指针为空 所有索引元素不重复 节点中的数据索引从左到右递增排列) - B+Trees （多叉平衡树 非椰子节点不存储Data,只存储索引(冗余),可以放更多的索引,叶子节点包含所有索引字段 叶子节点用指针连接,提高区间访问的性能）

树的高度为3的 B+Trees 大约可存储 21,902,400索引

1.聚集索引- [聚簇索引]:索引和数据放在一起的(叶节点包含了完整的数据)2.非聚集索引- [稀疏索引]:索引和数据不存放在一起(如 MyISAM myz/myd 索引和数据分离) 聚集索引查询比非聚集索引查询要快 B+Tree 相比 B-Tree 能存放更多的数据介意使用主键整形自增

1.1 MyISAM 索引

1.2 InnoDB 索引

创建InnoDB数据表

CREATE TABLE `abc_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `d` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_abc` (`a`, `b`, `c`) ) ENGINE = InnoDB; INSERT INTO abc_innodb(a,b,c) VALUES (1,4,2); INSERT INTO abc_innodb(a,b,c) VALUES (8,5,6); INSERT INTO abc_innodb(a,b,c) VALUES (6,7,5); INSERT INTO abc_innodb(a,b,c) VALUES (4,7,3); INSERT INTO abc_innodb(a,b,c) VALUES (4,7,2); INSERT INTO abc_innodb(a,b,c) VALUES (9,2,1); INSERT INTO abc_innodb(a,b,c) VALUES (19,6,2); INSERT INTO abc_innodb(a,b,c) VALUES (10,1,9);

1.3 最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。 在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。 就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。 可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、 组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配

2.索引类型

2.1主键索引

每个InnodDB表都有一个聚簇索引，聚簇索引使用B+Tree构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时InnoDB会自动创一个RowID字段来构建聚簇索引。自动创建索引的具体规则如下：

1. 在表上定义主键 PRIMARY KEY,InnoDB将主键索引引用聚簇索引 2.如果没有定义主键，InooDB会选择第一个不为NULL的唯一索引列用作聚簇索引 3.如果以上两个都没有，InnoDB会使用一个6 byte长整型的随机字段ROWID字段构建聚簇 索引。该ROWID字段会在插入新行时自动递增

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

2.2 普通索引

MySql 中基本索引类型,没有什么限制,允许在定义索引列中插入重复值和空值

2.3 唯一索引

索引列中的值 必须是唯一的但是允许有空值

2.4 全文索引

只能在文本类型CHAR,VARCHAR,TEXT 类型字段上创建全文索引.最短长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引MyISAM和InnoDB中都可以使用全文索引

2.5 空间索引

Mysql在5.7之后的版本支持了空间索引,而且支持OpenGIS 几何数据模型.MySql在空间索引这方面遵循OpenGIS几何数据模型规则

2.6前缀索引

在文本类型如CHAR,VARCHAR,TEXT类型上创建索引时,可以指定索引列的长度,但是数值类型不能确定

2.7 单列索引

由单个字段创建的索引(一个表最多可以有16个索引,最大字节长度为 256)

2.8 组合索引(联合索引)

由多个字段组成的索引叫做组合索引(组合索引的使用,需要遵循最左前缀匹配原则,一般情况下在条件允许的情况下 使用组合索引替代多个单列索引来使用)

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。

试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

联合索引的创建原则,在创建联合索引的时候应该把频繁使用的列，区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那介意使用联合索引

联合索引的使用

考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引当前所有存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否也可以加入到当前已经存在的索引上，使其查询语句可以使用覆盖索引

select * from abc_innodb where a = 13 and b = 16 and c = 4;

CREATE TABLE `abc_innodb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, `d` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_abc` (`a`, `b`, `c`) ) ENGINE = InnoDB;

2.9覆盖索引（并非索引结构）

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

3.索引的数据结构

3.1 hash表

Hash表,在Java中的HashMap,TreeMap就是Hash表结构,以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列,Value可以存储行记录或者行磁盘地址。Hash表再等值查询时效率很高，时间复杂度O(1);但是不支持范围快速查找，范围查找时还是只通过扫描全表的方式 (显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。)

3.2 二叉树查找

下图为二叉树示例图 二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。 这个特点是为了保证每次查找都可以折半而减少IO次数,但是二叉树很考验第一个根节点的取值因为很容易在这个特点下出现我们并不想发生的情况“树不分叉了”这就很难受

如下图

3.3 平衡二叉树

平衡二叉树是采用二分法思维，平衡二叉树查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1.在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高，右子树很矮的情况

使用平衡二叉树查询的性能接近于二分查找法，时间复杂度是O(log2n)查询id=6,只需要进行两次IO操作 平衡二叉树存在的问题:

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘IO操作。叔的高度就等于每次查询数据时磁盘IO操作的次数。磁盘每次寻道书剑为10ms，在表数据量大时，查询性能就会很差。（100万的数据量，log2n约等于20次磁盘IO 时间20*10=0.2ms）平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高

3.4 B树（改造二叉树）

mysql的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时 ，所以我们优化的重点就是减少磁盘的IO操作。访问二叉树的每个节点就会发生一次IO如果想要减少磁盘IO操作就要 降低树的高度。 假如key为bigint=8byte 每个节点有两个指针，每个指针为4个byte 一个节点占用的空间16个byte(8+4*2=16) 因为每次在Mysqk的InnoDB存储引擎一次IO会读取的一页默认(16kb)的数据量，而二叉树一次IO有效数据量只有16byte ，空间利用率极低为了最大化利用一次IO空间一个简单的想法是在每个节点存储多个元素 在每个节点尽可能多的存储数 据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树 从高变成了矮胖。构建一百万条数据，树的高度只需要2层就可以了(1000*1000=100万) 也就说只需要进行两次IO 操作就能拿到数据 磁盘IO次数减少查询数据的效率也就提高了 B树是一种多叉平衡查找树 1. B树的节点中存储着多个元素，每个内节点有多个分叉 2. 节点中的元素包含键值和数据，节点中的键值从大到小进行排列也就是说在所有节点都能存储数据 3. 父节点当中的元素不会出现在子节点 4. 所有叶子节点都位于同一层，叶子节点具有相同的的深度,叶节点之间没有指针连接

举个例子，在b树中查询数据的情况：

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

过程如图：

问题来了就是B树还可以进行改造

1. B树同样不支持范围查询的快速查找 就意味着当你进行范围查找时还是会从根节点去挨个遍历 可想而知其 查询效率还是很低 2. 如果data存储的是行记录，行的大小随着列数的增多，所占用的空间就会变大，这时一个页中可存储的数据量 就会变少,树相应就会变高，磁盘IO次数就会变多

3.5 B+树 (B+Tree、改造B树)

B+Tree作为B树的升级版,在B树的基础上,Mysql对其继续改造，使用B+Tree构建索引。B+TreeB树最主要的区别在于非叶子节点是否存储数据的问题

1. B树：非叶子节点和叶子节点都会存储数据 2. B+Tree ：只有叶子节点会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针来连接，最底层的 叶子节点形成了一个双向有序的列表

B+Tree的最底层叶子节点包含了所有的索引项。从图上可以看出，B+Tree在查找数据的时候由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据 所以我们在需要查询数据的情况下每次的磁盘IO跟树的高度有直接关系 但是从另一方面来说，由于数据都被放到了子节点，放索引的磁盘块锁存放的索引数量是会跟着增加的 相对于B树来说,B+Tree的树高理论上来说是要比B树树高矮的 也存在索引覆盖的情况，在索引中数据满足了当前的查询语句所需要的全部数据此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点

3.5.1 eg:等值查询

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6

第一次磁盘IO：将磁盘块1加载到内存中,在内存中从头遍历比较,9<15走左路,到磁盘寻址磁盘块2

第二次磁盘IO：将磁盘块2加载到内存中,在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6

第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头比那里比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址） 如下图

3.5.2 范围查询

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO操作

查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合条件的数据

第四次磁盘IO:根据磁盘块6后继指针中到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26 ，9<26<=26将data缓存到结果集

因为主键具备唯一性（后面不会有<=26的数据），不需要继续查找查询终止，将结果返回