目录
存储引擎
MySQL的体系结构
连接层:最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SOL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。
引擎层:存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
存储层:主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引警是基于表的,而不是基于库的,所以存储引警也可被称为表类型。
在创建表的时候我们可以指定存储引擎,如果不指定默认为INNODB引擎
create table 表明{
字段1, 字段类型,
……
}ENGINE = 引擎名;
如果想查看当前数据库支持哪些存储引擎,我们可以使用语句
show engines;
存储引擎特点
InnoDB
一种兼顾高可靠性与高性能的通用引擎,在Mysql5.5之后,InnoDB是MySQL的默认存储引擎。
特点是:
- DML操作遵循ACID模型,支持事务;
- 支持行级锁,提高并发访问性能;
- 支持外键约束,保证数据的完整性和正确性;
存储文件:
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引。该文件可以在Windows目录下C:\ProgramData\MySQL\MySQL Server 8.0\Data下找到,每个文件夹对应的是一个数据库。
如果需要通过idb文件查看表结构,可以通过命令
ibd2sdi 表名.ibd
逻辑存储结构
磁盘操作的最小单元为page,每个page是16K。而每个Extent大小为1M。也就是说,一个Extent包含64个page
MyISAM
MyISAM是MySQL早期的默认存储引擎
特点:
- 不支持事务,不支持外键
- 支持表锁,不支持行锁
- 访问速度快
存储文件:
- xxx.sdi:存储表结构信息
- xxx.MYD:存储数据
- xxx.MYI:存储索引
Memory
Memory引擎的表数据是存储在内存当中的,只能将使用Memory引擎的表作为临时表或缓存使用
特点:
- 内存存放,访问速度快
- 采用hash索引
存储文件:
- xxx.sdi:存储表结构信息
对比
特点 | InnoDB | MyISAM | Memory |
存储限制 | 64TB | 有 | 有 |
事务安全 | 支持 | - | - |
锁机制 | 行锁 | 表锁 | 表锁 |
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | - | - | 支持 |
全文索引 | 5.6版本后支持 | 支持 | - |
空间使用 | 高 | 低 | N/A |
内存使用 | 高 | 低 | 中等 |
批量插入速度 | 低 | 高 | 高 |
支持外键 | 支持 | - | - |
InnoDB与MyISAM最大的区别在于,InnoDB支持事务,支持行锁,支持外键
存储引擎选择
InnoDB:是Mysql的默认存储擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
通常来讲,我们使用InnoDB就足够了,MyISAM引擎用来记录日志等丢失几条消息也无所谓的信息,MEMORY因为无法存储太多数据,用来做缓存就可以了。但是MyISAM与MEMORY都可以被其他非关系型数据库来替代,比如说Redis。
索引
介绍
索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
比如说存在这样一张表,接下来我们要找到age为45的用户,在没有对age字段添加索引的清空下,是通过id进行全表扫描,一行一行对比age=45的数据。
如果对age字段建立索引,在插入时,数据库会维护索引信息,查找时从根节点开始查找,查询速度快。
优点:
- 提高数据库查询效率,减少数据库IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗
缺点:
- 索引会占用存储空间
- 索引大大提高了查询效率,同时也降低了Insert、Update、Delete效率
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
索引结构 | 描述 |
B+Tree索引 | 最常见的索引,大部分引擎都支持 B+ 树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效不支持范围查询 |
R-Tree(空间索引) | 空间索引是MvISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(空间索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
引擎对索引结构的支持情况
索引 | InnoDB | MyISAM | Memory |
B+Tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-Tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6之后支持 | 支持 | 不支持 |
B+Tree索引
默认都是使用B+Tree索引,但是MySQL又对经典的B+Tree做了一个优化,增加了一个指向相邻叶子节点的链表指针,形成了带有顺序指针的B+Tree,提高区间访问的性能。
为什么选择B+Tree索引结构?
相对于二叉树,层级更少,搜索效率高。
对于B-Tree,无论是叶子节点还是非叶子节点都存储数据,这样会导致一页中的存储的键值减少,指针跟着减少,如果需要保存大量数据,B-Tree的高度会比B+Tree高导致性能降低。其次,对B+Tree优化后,形成一个双线链表,对于范围查询更具有优势。
Hash索引
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中
hash索引的特点
- 只能用于对等比较(=、in),不支持范围查询(between,>,<等)
- 无法利用索引完成排序操作
- 查询效率高,通常只需要一次检索。
索引分类
分类 | 含义 | 特点 | 关键字 |
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB中根据索引的存储形式又可以分为以下两种
分类 | 含义 | 特点 |
聚集索引 | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引 | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
当需要对name字段进行查询时,先通过二级索引查找到对应的name键值,然后获取对应的id值,然后拿着id值取聚集索引中查找行信息,该操作叫做回表查询
InnoDB的主键索引B+Tree高度为多少?
B+Tree的高度,取决于主键数据类型,首先我们知道,B+Tree的非叶子节点是存储的只有键值与InnoDB的指针,而一个区块只能存放16K的内容,转化为字节为16*1024个字节。
首先InnoDB的指针占用6个字节,假设主键数据类型是bigint,需要占用8个字节的大小,在高度为2的情况下,计算公式为:
n*8+(n+1)*6 = 16*1024
计算出n约等于1170。
如果一行数据大小为1k。那么在高度为2的情况下可以存储 1171*16 =18736 数据。
在高度为三的情况,可以存储1171*1171*16 = 21939856 条数据
索引语法
创建索引
CREATE [ UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,…);
字段可以同时选取多个,这被称为联合索引或组合索引(需要注意的是,创建索引时,字段名的顺序会影响查找的效率,这个我们后面)
查看索引
SHOW INDEX FROM table_name;
删除索引
DROP INDEX index_name ON table_name;