一、什么是索引
- 索引是对数据库表中一列或多列的值进行排序的数据结构。
- 表面层次来讲,索引就像一本书的目录,可以快速访问数据库表中的特点信息。
- 深层次来看,索引是帮助MySQL高效获取数据的排好序的数据结构,这个数据结构可以是平衡二叉树、红黑树、Hash表、B-Tree、B+Tree
二、索引数据结构解析
演化:二叉树——红黑树——B-Tree——B+Tree
1、排序二叉树
假设有一表:t
有一sql语句:select * from t where t.col2=89
无索引时,进行全表遍历,直到符合条件,如图需要遍历6次。
以col2建立索引(假设该索引以二叉树去实现,实际大都以B树或B+树),则有:
那么根据排序二叉树“左小右大”性质,只需要两步就找到“89”的指针地址,再根据该指针到磁盘做一次IO,便可以找到想要的那一行数据。
2、红黑树
排序二叉树的弊端:当以col1这类递增列为索引时,树的结构变成链表结构
这样一来就与全文索引无区别。
优化——红黑树:当单边节点超过3个,会自动平衡,则上述结构变成:
红黑树是一种含有红黑节点并能自动平衡的二叉查找树。
性质:
- 根节点和叶子节点(NIL或NULL)都是黑色的。
- 每个红色节点的两个子节点一定都是黑色。
- 任意一节点到每个叶子节点的路径都包含数量相同的黑节点。
3、B-Tree
红黑树缺点,如上图,当持续有序递增加入节点数据时,会出现左空右满的情况,导致树的高度太高
优化: 限制高度,扩展宽度。每个节点存储多个数据。每个又可以分叉,形成多叉平衡树。eg:
特性:
- 叶节点具有相同的深度,叶节点指针为空。
- 所有索引元素不重复。
- 节点中的数据索引从左到右递增排序。
- 当查找某个节点时,将该节点放到内存RAM中快速查找。
为什么不全部放到一个节点,一起放到RAM查找?
因为内存是有限的。因此设置每个节点大小为16KB。
4、B+Tree(B-Tree的变种):MySql的最终选择
特性:
- 非叶子节点不存储数据(data),只存储索引(冗余),可以放更多的索引(每个节点16KB)
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问性能
叶子节点存放所有Data,并以链表形式呈现,体现两大特点:大数据存储、大规模查询(有序)
扩展——聚集索引和非聚集索引的区别: - 聚集索引(主键、聚簇索引):叶子节点存放整条记录(索引字段+数据),不需要回表。
- 非聚集索引(非主键、普通、二级索引):叶子节点存放索引值和磁盘地址指针,需要回表。
详情见——存储引擎。
5、B树和B+树的区别
a、B树的每个节点都存储了key和data,B+树的data存储在叶子节点上。节点不存储data,这样一个节点就可以存储更多的key。可以使得树更矮,所以IO操作次数更少。
b、树的所有叶子节点构成一个有序链表,可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连,所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。
三、MySQL常见存储引擎(形容对象:表)
1、MySQL的基本逻辑架构图
由图可知,逻辑架构包括Server层和存储引擎层。其中Server层包括连接器,分析器,优化器以及执行器;存储引擎包括多种支持的存储引擎。各个逻辑部件的作用如下:
- 连接器: 验证客户端权限,建立和断开MySQL链接。
- 分析器: 进行SQL语句的语法分析。
- 优化器: 选择索引,生成具体的SQL语句执行计划。
- 执行器: 操作存储引擎,执行SQL,返回执行结果。
- 存储引擎层: 各个不同的存储引擎都提供了一些读写接口来操作数据库。
在MySQL5.5版本之后,InnoDB已经成为了其默认的存储引擎,也是大部分公司的不二选择,毕竟谁家公司会不要求数据库支持事务呢?谁家公司又可以忍受表级锁导致的读写冲突呢?
除了InnoDB以及MyISAM存储引擎外,常见的考擦存储引擎还有Memory,使用Memory作为存储引擎的表也可以叫做内存表,将数据存储在了内存中,所以适合做临时表来使用,在索引结构上支持B+树索引和Hash索引。
2、聚簇索引和非聚簇索引
a. 聚簇索引也称为主键索引,其索引树的叶子节点中存的是整行数据,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚簇索引。因为索引(目录)只能按照一种方法进行排序。
b、非聚簇索引(普通索引)的叶子节点内容是主键的值。在InnoDB里,非主键索引也称为二级索引。这里需要注意的是:InnoDB的索引储存是聚簇索引。InnoDB里面没有非聚簇索引,更加准确的叫法是辅助索引或二级索引。非聚簇索引针对MyISAM引擎而言。
3、MyISAM存储引擎索引实现
MyISAM索引文件、数据文件是分离的(非聚簇),eg:表test_myisam的文件图如下:
结构图如下:
MyISAM存储引擎索引实现称为“非聚集索引”,B+Tree的叶子结点只存放主键的值和其内存地址指针,需要根据该地址回到内存中(test_myisam.MYD中)找到对应的一行数据,该过程也称为“回表”。
附加(草稿图):
4、InnoDB存储引擎索引实现
(1)、InnoDB索引实现(聚集)
- 表结构文件本身就是按B+Tree组织的一个索引结构文件。
- 叶子节点包含了完整的数据记录。
- 为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?(可以叶子链表快速查找数据)。
- 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)。
(2)、结构
eg:表test_innodb的文件图:
表的结构图:
- 当你的InnoDB表没有设置主键,它会自动帮你选一个能够唯一标识字段(无重复)作为默认字段,如果找不到该字段,则自动生成一列作为标识性字段来维护(建立)一棵B+Tree。
- 为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
(可以叶子链表快速查找数据)因为我们B+Tree结构每个节点从左到右是依次递增的。意味着我们存放该索引值时要比较大小,整型数据易于比较,如果是字符串“ABCD”一类的,要逐个字母去从头比较到尾。而且整型占内存小(2个字节)。另外,如果该字段已经告诉计算机为“自增”就不必再比较排序大小了。
附加(草稿图):
5、Hash索引
相当于每个索引做一次Hash结果(加密),就可以快速找到对应数据在磁盘的位置(地址)。
缺点:像select * from t where t.col = 6 单个索引值来说速度最快(所有索引数据结构中),但不支持连续查询,eg:“coll>6”。而B+Tree叶子结点是递增且都由指针链接,如找“t>20”,只需找叶子节点20再顺着指针向后找即可。非常方便千万级数据规模查询。
6、索引最左前缀原理
四、MySQL常见面试题
1、B树和B+树的区别?
- B树的每个节点都存储了key和data,B+树的data存储在叶子节点上。节点不存储data,这样一个节点就可以存储更多的key。可以使得树更矮,所以IO操作次数更少。
- 树的所有叶子节点构成一个有序链表,可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连,所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。
2、为什么底层数据结构使用B+树,而不是B树?
- B+树是B树的变种,B+树的非叶子节点只用来保存索引,不存储数据,所有的数据都保存在叶子节点;而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定,均为从根节点到叶子节点的路径。
- B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,同样空间可以读入更多的节点,所以B+树的磁盘读写代价更低。
3、MySQL常见的存储引擎有哪些?
MySQL中最常见的存储引擎有InnoDB和MyISAM,它们主要区别如下:
- MyISAM不支持事务;InnoDB是事务类型的存储引擎。
- MyISAM只支持表级锁;InnoDB支持行级锁和表级锁,默认为行级锁。
- MyISAM引擎不支持外键;InnoDB支持外键。
- 对于count(*)查询来说MyISAM更有优势,因为其保存了行数。
- InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
- MyISAM支持全文索引(FULLTEXT);InnoDB不支持。
总结:最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。InnoDB表支持事务、支持行级锁、支持外键。
4、MySQL事务有哪些特性?
事务是单个逻辑工作单元执行的一系列操作,是一个不可分割的工作单位。满足如下的四大特性:
- 原子性(Atomicity): 事务作为一个整体被执行,要么全部执行,要么全部不执行。
- 一致性(Consistency): 保证数据库状态从一个一致状态转变为另一个一致状态。
- 隔离性(Isolation): 多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
- 持久性(Durability): 一个事务一旦提交,对数据库的修改应该永久保存。
5、MySQL中的锁机制?
MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看,行级锁又可以分为独占锁和共享锁。
- a、独占锁(排它锁),也称X锁(Exclusive Lock):
独占锁锁定的资源只允许进行锁定操作的程序使用,其他任何对它的操作均不会被接受。执行数据更新命令,即INSERT、UPDATE或DELETE命令时,MySQL会自动使用独占锁。但当对象上有其它锁存在时,无法对其加独占锁。独占锁一直到事务结束才能被释放。
在select命令中使用独占锁的SQL语句为:select …for uodate。 - b、共享锁,也叫S锁(Shared Lock):
共享锁顾名思义,那就是其锁定的资源可以被其它用户读取,但其它用户不能修改。如果在select查询语句中要手动加入共享锁,那么对应的SQL语句为:select …lock in share mode。
这里需要注意:
一个事务在一行数据上加入了独占锁,那么其余事务不可以在该数据行上加入任何锁。也就是说加过排它锁的数据行在其他事务中是不能修改数据的,也不能通过for uodate和lock in share mode锁的方式查询数据,但可以直接通过select…from…查询数据,因为普通查询没有任何锁机制。
6、MySQL建表的约束条件有哪些?
约束条件是我们建表的时候对数据库表做的一个限制条件。MySQL建表时候一般有如下的五个约束条件:
- 主键约束(Primay Key Coustraint): 唯一性,非空性。
- 唯一约束(Unique Counttraint): 唯一性,可以空,但只能有一个。
- 检查约束(Check Counstraint): 对该列数据的范围、格式的限制。
- 默认约束(Default Counstraint): 该数据的默认值。
- 外键约束(Foreign Key Counstraint): 需要建立两表间的关系并引用主表的列。