一、索引

使用索引是数据库性能优化的必备技能之一。

1、MySQL常见的四种索引类型：

InnoDB和MyISAM是B-Tree索引，只有Memory/Heap引擎支持Hash索引。

2、四种索引的特点比较：

1）哈希索引

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

优点：速度会很快，只需要往后追加。

缺点：

（1）Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询，无法被用来避免数据的排序操作。

（2）由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样，所以数据库无法利用索引的数据来避免任何排序运算；也就是因为不是有序的，所以哈希索引做区间查询的速度是很慢的，比如 Memcached 及其他一些 NoSQL 引擎。

（3）Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能变慢。
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

2）B-Tree索引

二叉搜索树：这个时间复杂度是 O(log(N))。
平衡二叉树-：它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树（AVL树）。

如下图，两个平衡二叉树:

深度越大，从磁盘读数据块需要的寻址时间就越长。为了让一个查询尽量少地读磁盘，使用“N 叉”树=》平衡多路查找树（B树）（mangodb用的b树索引）。

B 树定义：
• 根节点至少有两个子节点
• 每个节点有M-1个key，并且以升序排列
• 位于M-1和M key的子节点的值位于M-1 和M key对应的Value之间
• 其它节点至少有M/2个子节点

如下图，是一个M=4 阶的B树:

B+ 树:
• B+树是对B树的一种变形树，它与B树的差异在于：
• 有k个子结点的结点必然有k个关键码；
• 非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
• 树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。

如下图，是一个B+树:

B和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

3）RTREE索引

RTREE在mysql很少使用，仅支持geometry数据类型。

4）全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。必须使用特有的语法才能使用全文索引进行查询。
例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：SELECT * FROM article WHERE MATCH(title, content) AGAINST(‘查询字符串’)
MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

3、为什么MySQL选择B+树做索引

对于BTREE这种Mysql默认的索引类型，具有普遍的适用性。

1）B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2）B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3）B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4）B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

小结：MySQL 默认的存储引擎选择 B+ 树而不是哈希或者 B 树的原因：
哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；
B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；

使用建议：

以下两点必须使用 InnoDB：
1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。
2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

对比之下，MyISAM的使用场景：
1）做很多count计算的。如一些日志，调查的业务表。
2）插入修改不频繁，查询非常频繁的。

4、索引失效

1）对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
2）隐式类型转换。

Eg: select * from tradelog where tradeid=110717;
tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
数据类型转换的规则: 这里有一个简单的方法，看 select “10” > 9 的结果：
如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

就知道对于优化器来说，这个语句相当于：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

3）隐式字符编码转换

Eg：两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。
字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。这个设定很好理解，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

可以通过 explain 检查 SQL 的执行计划，以确认是否真正使用了索引。

5、InnoDB和MyISAM是B+树，有什么区别？

根据叶子节点的内容，索引类型分为主键索引和非主键索引。
主键索引也被称为聚簇索引（clustered index）。
非主键索引也被称为二级索引（secondary index），即非聚集索引。

1）MyISAM索引结构（非聚集索引）

B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

2）InnoDB聚簇索引：

B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。

InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，,InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID,并以此作为隐藏的主键。
然后对其建立聚簇索引（主键索引）。

二、索引及索引优化

例：mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

如果语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。（eg: 在一个市民信息表上,将身份证号和名字建立联合索引, 高频请求通过身份证查回姓名就不需要回表了，直接覆盖索引就可以）

如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符，遇到范围查询(>、<、between、like)就会停止匹配。Eg：“where name like ‘张 %’”。这时，你也能够用上姓名这个索引。利用索引的“最左前缀”，建立一个复合索引（a，b，c），相当于建立了多个索引(a),(a,b),(a,b,c); 在建立联合索引的时候，如何安排索引内的字段顺序变得重要。

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。Eg：有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：mysql> select * from tuser where name like ‘张%’ and age=10 and ismale=1;

无索引下推执行流程:

索引下推执行流程:

总结：B+ 树为了维护索引有序性，在插入新值或者删除数据的时候需要做必要的维护，进行页分裂或者页合并，直接影响数据库性能。用自增主键，有利于维护索引，插入数据快，是追加操作减少页分裂；如果用业务中唯一索引列做主键，索引长度可能会大，但是避免了搜索两颗树，避免了回表。根据业务场景选择怎么建索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。