[面试直通版]数据库核心之索引,性能与安全(上)

点击->SQL复习的文章集<-点击

目录

MySQL索引

典型问题：

数据库的功能

二叉搜索树

B树、B+树

聚簇索引与非聚簇索引

典型问题：

索引

存储层次结构

聚簇索引

非聚簇索引

InnoDB中的聚簇索引与非聚簇索引

聚簇索引的优点

聚簇索引的注意事项

联合索引

典型问题：

联合索引定义

作用时机

---

• MySQL索引

• 典型问题：

• 为什么使用B+树做索引？
• B树和B+树有什么区别？

• 数据库的功能

• 查询：
• 对于一个数据库而言，最普遍的查询就是两种：精确查询和范围查询
• 精确查询是指通过一个具体的key找到对应的一条或者多条数据
• 范围查询是指查询key在某个范围内的所有数据
• 插入
• 对于一个数据库而言，插入需要考虑两个问题：插入性能和查询性能
• 插入性能是指插入数据的过程不能消耗太多的时间
• 查询性能是指插入的数据在被查询的时候，需要快速返回

• 二叉搜索树

• 首先想一下使用线性的数据结构会有什么问题：
• 平均操作复杂度为O(n)，这还不够优化
• 二叉搜索树：
• 左子树上所有结点的值均小于它的根结点的值
• 右子树上所有结点的值均大于它的根结点的值
• 这样能保证有序，操作复杂度为O(logn)
• 适合精确查询，但是对于范围查询来说，不友好(需要不断从根节点遍历)
• 优化：
• 1.根节点不保存数据，只在叶子节点保存数据
• 2.叶子节点使用双向链表连接起来，即可快速范围查询
• 它的根节点只是为数据的查询提供路径的作用
• 在精确查询时，依然可以根据根节点的路径找到具体的数值
• 在范围查询时，因为叶子节点是使用双向链表连接起来的，并且通过索引它是有序的
• 所以只要找到范围查询的第一条，然后就可以根据叶子节点的双向链表找到符合范围的条件的范围查询

• B树、B+树

• 树高影响查询性能，因此降低树高，提升性能
• 将二叉搜索树改造成多叉搜索树，降低树高
• B树在叶子节点、根节点都保存数据，不利于范围查询
• B+树根节点不存储数据，叶子使用双向链表连接，适合范围查询
• 在插入数据、精确查询时，两者性能相近

• 聚簇索引与非聚簇索引

• 典型问题：

• 什么是聚簇索引，什么是非聚簇索引
• 聚簇索引有什么优点

• 索引

• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构
• 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
• 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容

• 存储层次结构

• 有缓存-主存层次，主存-辅存层次
• 存储可以直接影响性能
• 索引也是直接影响数据库的查询性能
• 聚簇索引：将数据存储与索引放在一块，找到索引就找到了数据
• 非聚簇索引：将数据存储与索引分开存储，索引指向数据内存空间
• 非聚簇索引通常也被称为二级索引

• 聚簇索引

• 一个数据表只有一个聚簇索引
• 默认情况下聚簇索引是主键
• 聚簇索引性能最好且具有唯一性，需要慎重选择字段作为聚簇索引
• InnoDB使用的是聚簇索引
• 将主键组织到一棵B+树中，而行数据就存储在叶子节点
• 最终目的：相同结果集的情况下，减少逻辑IO

• 非聚簇索引

• 一个数据表可以有多个非聚簇索引
• MyISAM使用的是非聚簇索引
• 非聚簇索引和聚簇索引的B+树看上去并无不同
• 节点完全一致，只是存储的内容不一致
• 非聚簇索引保存的是指向数据的指针，而聚簇索引保存的是行数据

• InnoDB中的聚簇索引与非聚簇索引

• 一个数据表只有一个聚簇索引
• 一个数据表可以有多个非聚簇索引
• 为什么非聚簇索引被称为二级索引?
• 使用非聚簇索引检索数据时：
• 第一步，检索非聚簇索引对应的B+树
• 第二步，检索到叶子节点得到指向数据的指针，还需要去聚簇索引的B+树来检索得到对应的记录
• 即：
• 先在非聚簇辅助索引检索到对应的记录，获得相应主键
• 根据主键在聚簇索引再进行一次检索操作
• 对非聚簇索引的数据列检索需要两次检索索引

• 聚簇索引的优点

• 行数据与叶子节点一起存储，结合内存页结构，检索速度快
• 聚簇索引适合排序场合、范围查询场合
• 维护聚簇索引不需要对行数据进行额外的管理操作

• 聚簇索引的注意事项

• 不建议使用长字符串(UUID)作为主键索引
• 稀疏数据的列不适合建立聚簇索引
• 频繁更新的列不适合建立索引

• 联合索引

• 典型问题：

• 请简述对MySQL联合索引的理解

• 联合索引定义

• 联合索引，也称为复合索引，即是由多个字段组成的一个索引
• 联合索引每个节点包含多个字段的值
• 创建如下的表，并创建一个联合索引(a,b)
• CREATE TABLE t(
  • a INT,
  • b INT,
  • PRIMARY KEY (a),
  • KEY idx_a_b (a,b)
• )ENGINE=INNODB
• 联合索引也是一棵B+树，不同的是B+树在对索引a排序的基础上，对索引b排序

• 作用时机

• 能够使用联合索引的情况
• 全匹配
• select * from t where a=xxx and b=xxx
• 最左前缀匹配
• 对于单个的a列，也可以用到(a,b)联合索引
• select * from t where a=xxx
• selete * from t where b=XX and a=XX,也是可以使用到联合索引的，这条语句并不符合最左匹配原则
• 这是由于查询优化器的存在，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划
• 所以，尽量的利用到索引时的查询顺序使效率最高，因为mysql查询优化器会最终以这种顺序进行查询执行
• 优化：在联合索引中将选择性最高的列放在索引最前面
• 不能使用联合索引
• select * from t where b=xxx
• 同理，如果建立(a,b,c)索引，则下面的查询都能用到索引(where条件换顺序也一样)
• select * from t where a=xxx and b=xxx and c=xxx
• select * from t where a=xxx and b=xxx
• select * from t where a=xxx
• 理：
• 联合索引使用遵循最左匹配原则
• where条件必须使用联合索引的第一个字段
• 联合索引与where条件的顺序无关，只和字段有关