MySql索引问题(上)

MySQL索引常见问题(上)

1.什么是索引？

索引就相当于书的目录，当我们想要查看书的某一内容时，第一反应就是先查阅目录，根据目录的信息找到对应的内容。在MySQL中为了提高数据检索的效率，就使用索引来组织数据的排列方式，也可以说索引就是一种数据结构，是数据的目录。

存储引擎本质上来讲就是索引的建立，数据的存储和查询等技术的实现方法，不同的存储引擎例如InnoDB，MyISAM，Memory就是实现这些技术的方法不同而区分开来的。

2.索引的分类

从不同的角度来看，索引的分类是不同的

1. 按数据结构分类

   • B+树索引

   • 哈希表索引

   • 全文(Full-Text)索引

2. 按物理存储分类

   • 聚簇索引(主键索引)

   • 二级索引

3. 按字段类型分类

   • 主键索引

   • 唯一索引

   • 普通索引

   • 前缀索引

4. 按字段个数分类

   • 单列索引

   • 联合索引

MySql不同的存储引擎对索引的建立有不同的方式，这里拿Mysql默认的存储引擎InnoDB举例

在InnoDB中，创建表时，表中的数据都是通过建立一个聚簇索引来存储的

• 如果表中设置了主键(primary key)，默认会使用主键的值作为索引键
• 如果表中没有设置主键，则根据第一个不包含NULL的字段作为索引键
• 如果以上条件都不满足，InnoDB则会自动生成一个隐式自增id列作为索引键

下面我来介绍一下不同类型的索引各自的特点

按数据结构分类

InnoDB创建的主键索引和二级索引 默认都是使用B+树这种数据结构

B+树是一种多叉树，它的特点是所有非叶子结点存放的是主键值，所有叶子结点存放的才是真实的数据，父节点的索引值一定会出现在子结点中，所以保证了叶子结点包含了所有索引值对应的真实数据，并且每一个叶子结点都有两个指针分别指向前一个叶子结点和后一个叶子结点，形成了一个双向链表。

这里我举一个简单的例子来介绍B+树索引的特点

首先创建一张商品表，id为主键

CREATE TABLE `product`  (
  `id` int(11) NOT NULL,
  `product_no` varchar(20)  DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

在创建这张表之后，InnoDB就会以B+树的方式来存储所有的数据

主键索引的B+树如图所示

那么MySql为什么使用B+树这种数据结构来存储数据？

1. B+树 VS 二叉树

B+树是多叉树，每个结点可以有多个子结点，而二叉树每个结点只能有两个子结点，在数据量很大的时候，二叉树的高度会变得非常高。但B+树子结点的个数可能有上百个，即使数据量达到千万级别时，仍能保持树的高度在3-4层。

而树的查询效率是与树的高度相关的，二叉树查询数据的时间复杂度为O(log2n)以2为底n的对数。如果B+树最多可以有m个子结点，则时间复杂度是O(logmn)

由于数据都是持久化到磁盘上的，每次查询操作都需要把数据读取到内存中，也就是每加载一个结点都需要一次I/O操作，所以B+树每次最多只需进行3-4次I/O操作

所以B+树的查询效率要远远高于二叉树

2. B+树 VS B树

这两种树都是多叉树，都会把树变得矮胖，但区别在于B树的所有结点存放的都是真实数据，而B+树的非叶子结点只存放索引，只在叶子结点存放真实数据。这样就使得在数据量相同的情况下，对于树的每一层，B+树会存储更多的索引，使树变得更加矮胖。

由于B树结点存放的都是真实数据，如果使用B树作为索引，在我们想要根据某个索引值查询底层的某个数据的过程中，会将其他无用的数据也加载到内存中，严重降低了查询的效率

而且B+树底层的叶子结点是通过双向链表链接起来的，使得在范围查询的过程中更占优势

3. B+树 VS 哈希表

哈希表在等值查询中速度很快，时间复杂度为O(1)

但是范围查询却没有B+树这样的优势

按物理存储分类

索引可以分为主键索引和二级索引

• 主键索引树中非叶子结点存放的是主键值，叶子结点存放真实数据

• 而二索引树中的叶子结点存放的是主键值，而不是真实数据

在使用二级索引树查询数据时，如果想要查询一条完整的数据，则先要通过二级索引值查询到其对应的主键值，再根据主键值使用主键索引树，定位到完整的数据，这个过程就叫做回表

而有一种情况可以不需要回表，那就是当我们使用二级索引查询数据时，想要查询的字段在二级索引中存在，就不需要进行回表，可以直接返回结果，这一过程就叫做覆盖索引

按字段特性分类

• 主键索引

就是建立在主键字段(primary key)上的索引，索引树按照主键值的顺序进行排列

• 唯一索引

建立在唯一字段(unique key)上的索引，在创建表时指定唯一键后自动创建

• 普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE，需要在创建表时指定哪些字段作为普通索引

• 前缀索引

对字符类型字段前几个字符建立的索引，而不是在整个字段上建立索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。需要在创建表时指定

按字段个数分类

• 单列索引

建立在单列字段的索引

• 联合索引

建立在多列字段上的索引，联合索引树中非叶子结点存放的是多个字段的值作为索引值，也就是要求某个字段的值可以重复，但多个字段联合起来的值是不可以重复的

在使用联合索引时，存在最左匹配原则(最左前缀原则)，以最左为起点的任何连续索引都能匹配上。

在建立联合索引时，会根据我们定义索引时的顺序来组织B+树，如果存在(a, b, c)三个字段的联合索引，在建立索引树时，会首先根据字段a的值排序，如果字段a相等再根据b的值排序，以此类推。也就是字段a在整个联合索引树中是全局有序的，而字段b和字段c只是局部有序。

• where a = 1 and b = 2
• where b = 2 and c = 3
• where b = 2

以上三种情况只有第一种使用到了联合索引，而后面两种情况只能进行全表扫描，原因还是由于a是全局有序的，b，c是局部有序的，在查询中只有索引值全局有序才可以利用索引树，否则无法查询

所以如果在查询数据时不遵循最左匹配原则，该索引树就没有作用，也就是索引失效，只能根据主键索引树进行全表扫描

联合索引中的范围查询

由于有多个字段的存在，但是使用联合索引并不代表所有索引字段都被利用到了，这种情况就发生在范围查询的过程中。最左匹配原则在遇到范围查询后就会停止匹配，也就是范围查询后的字段不会利用到联合索引了

这里我们举几个例子

• Q1:下面的查询语句哪些字段利用到了联合索引？

select * from t_table where a > 1 and b = 2

由于联合索引是按照字段定义的顺序进行排序的，所以a是全局有序的，也就是在叶子结点中，a>1的数据是顺序存放的，所以我们通过二级索引找到符合 a > 1这一叶子结点，之后沿着链表向下进行查找，直到定位到不符合该条件的叶子结点。但是由于我们在前面介绍了，b字段是局部有序的，所以对于a不同的值 b字段的值是无序的，在利用二级索引检索过程中，b字段无法利用到联合索引进行排除数据

所以只有a字段利用到了联合索引

• Q2:下面的查询语句哪些字段利用到了联合索引（a， b）？

select * from t_table where a >= 1 and b = 2

该语句和Q1唯一的区别是 大于变成了大于等于。还记得我们之前说过，在联合索引树建立的过程中，a是全局有序，而b是局部有序的，在这条语句中虽然 a > 1范围内的索引b是无序的，但是 a = 1时b却是有序的，所以当索引值中 a = 1时我们可以利用 b = 2这一条件排除一些数据，在这个过程中b字段也就利用到了联合索引

所以a和b都利用到了联合索引

• Q3:下面的查询语句哪些字段利用到了联合索引（a，b）？

SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

和Q3很类似 between and 本质上来说还是 >= 和 <=，所以当在检索二级索引树时，索引值的 a = 2 或 a = 8时，可以b字段也可以利用到索引树，通过 b = 2这一条件排除数据

所以a和b都利用到了联合索引

• Q4:下面的查询语句哪些字段利用到了联合索引（name，age）？

SELECT * FROM t_user WHERE name like 'j%' and age = 22

和前面几个问题大同小异，虽然符合 name = j%的数据中 age是无序的，但是当 name = j 时age是局部有序的，此时age也可以利用联合索引减少数据的范围

所以name和age都利用到了联合索引

综上所述，联合索引的最左匹配原则，在遇到范围查询(> < )时，会停止匹配只有范围查询字段可以利用到联合索引(该字段必须全局有序)，而范围查询后的字段无法利用到联合索引，但是在对于 >= <= between,like 这样的范围查询时，在字段的值为边界值时并不会停止匹配

补充：

对于select * from t_table where a > 1 and b = 2这条语句，当我们利用二级索引定位到 符合 a > 1的第一个叶子结点时，我们是直接读取b的值判断是否符合 b = 2 还是回表到主键索引进行判断？

答：Mysql5.6之前是需要回表到主键索引判断的，但是在Mysql5.6之后加入了索引下推优化，在联合索引检索的过程中，直接对联合索引包含的字段进行判断，过滤掉不满足条件的记录，减少了回表次数