1. 存储引擎
1.1 MySQL体系结构
连接层
最上层是一些客户端和链接服务,包含本地 sock 通信和大多数基于客户端 / 服务端工具实现的类似于 TCP / IP 的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。 在 该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构 主要完成大多数的核心服务功能,如 SQL 接口,并完成缓存的查询,SQL 的分析和优化,部分内置函数的执行。 所有跨存储引擎的功能也在这一层实现,如 过程、函数 等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化,如确定表的查询顺序,是否利用索引等,最后生成相应的执行操作。如果是 select 语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
存储引擎层, 负责 MySQL 中数据的存储和提取,服务器通过 API 和存储引擎进行通信。不同的存储引擎具有不同的功能,可以根据自己的需要,来选取合适的存储引擎。 数据库中的索引是在存储引擎层实现的。
存储层
数据存储层, 主要将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等) 存储在文件系统之上,并完成与存储引擎的交互。
和其他数据库相比,MySQL的不同之处在于:它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上,插件式的存储引擎架构,将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.2 存储引擎介绍
存储引擎就是存储数据、建立索引、更新 / 查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。我们可以在创建表的时候,来指定选择的存储引擎,若没指定将自动选择默认的存储引擎。
- 建表时指定存储引擎
create table 表名( 字段1 字段1类型 [ comment 字段1注释 ] , ...... 字段n 字段n类型 [ comment 字段n注释 ] ) engine = INNODB [ comment 表注释 ] ;
- 查询当前数据库支持的存储引擎
show engines;
1.3 存储引擎特点
1.3.1 InnoDB(默认引擎)
1、特点
- DML 操作遵循 ACID 模型,支持事务;
- 行级锁,提高并发访问性能;
- 支持外键 FOREIGN KEY 约束,保证数据的完整性和正确性;
2、文件
xxx.ibd:xxx 代表的是表名,innoDB 引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm-早期的 、sdi-新版的)、数据和索引。
参数:innodb_file_per_table
show variables like 'innodb_file_per_table';
如果该参数开启,代表对于 InnoDB 引擎的表,每一张表都对应一个 ibd 文件。每一个 ibd 文件就对应一张表,而在这个 ibd 文件中不仅存放表结构、数据,还会存放该表对应的索引信息。 而该文件是基于二进制存储的,不能直接基于记事本打开,我们可以使用 mysql 提供的一个指令 ibd2sdi ,通过该指令就可以从 ibd 文件中提取 sdi 信息,而 sdi 数据字典信息中就包含该表的表结构。
3、逻辑存储结构
MyISAM
-
特点
- 不支持事务,不支持外键
- 支持表锁,不支持行锁
- 访问速度快
-
文件
xxx.sdi:存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
-
1.3.3 Memory
Memory 引擎的表数据是存储在内存中的,由于受到硬件问题或断电问题的影响,只能将这些表作为临时表或缓存使用。特点: 内存存放、 hash 索引(默认)
1.4 存储引擎选择
InnoDB: 是 Mysql 的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么 InnoDB 存储引擎是比较合适的选择。
MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY 的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
2. 索引
2.1 索引概述
索引(index)是帮助 MySQL 高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的 IO 成本 | 索引列也是要占用空间的 |
通过索引列对数据进行排序,降低数据排序的成本,降低 CPU 的消耗 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、UPDATE、DELETE时,效率降低 |
2.2 索引结构
2.2.1 概述
MySQL 的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
不同的存储引擎对于索引结构的支持:、
注意: 我们平常所说的索引,如果没有特别指明,都是指 B+ 树结构组织的索引。(大数据量情况下,二叉树 / 红黑树 层级较深,检索速度慢)
2.2.2 B+Tree
B+Tree 与 B-Tree 相比,主要有以下三点区别:
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
思考题:InnoDB主键索引的B+tree高度为多高呢?
假设: 一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间,主键即使为 bigint,占用字节数为 8。
高度为2:n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170
1171* 16 = 18736
也就是说,如果树的高度为2,则可以存储 18000 多条记录。
高度为3:1171 * 1171 * 16 = 21939856
也就是说,如果树的高度为3,则可以存储 2200w 左右的记录。
数据结构可视化 - B+ 树
2.2.3 Hash
哈希索引就是采用一定的 hash 算法,将键值换算成新的 hash 值,映射到对应的槽位上,然后存储在 hash 表中。
特点
A. Hash 索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)
B. 无法利用索引完成排序操作
C. 查询效率高,通常(不存在 hash 冲突的情况)只需要一次检索就可以了,效率通常要高于 B+ tree 索引
存储引擎支持
在 MySQL 中,支持 hash 索引的是 Memory 存储引擎。 而 InnoDB 中具有自适应hash 功能,hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。
为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?
A. 相对于二叉树,层级更少,搜索效率高;
B. 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
C. 相对 Hash 索引,B+tree 支持范围匹配及排序操作;
2.3 索引分类
2.3.1 索引分类
2.3.2 聚集索引&二级索引
在 InnoDB 存储引擎中,根据索引的存储形式,可分为以下两种:
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
-
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
2.4 索引语法
创建索引
create [ unique | fulltext ] index index_name on table name (index_col_name, ...);
1
查看索引
show index from table_name;
1
删除索引
drop index index_name on table_name;
案例演示
数据准备:创建一张表 tb_user
1 create table tb_user( 2 id int primary key auto_increment comment '主键', 3 name varchar(50) not null comment '用户名', 4 phone varchar(11) not null comment '手机号', 5 email varchar(100) comment '邮箱', 6 profession varchar(11) comment '专业', 7 age tinyint unsigned comment '年龄', 8 gender char(1) comment '性别 , 1: 男, 2: 女', 9 status char(1) comment '状态', 10 createtime datetime comment '创建时间' 11 ) comment '系统用户表'; 12 13 INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 14 createtime) VALUES ('吕布', '17799990000', '[email protected]', '软件工程', 23, '1', 15 '6', '2001-02-02 00:00:00'); 16 INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 17 createtime) VALUES ('曹操', '17799990001', '[email protected]', '通讯工程', 33, 18 '1', '0', '2001-03-05 00:00:00'); 19 INSERT INTO tb_user (name, phone, email, profession, age, gender, status, 20 createtime) VALUES ('赵云', '17799990002', '[email protected]', '英语', 34, '1', 21 '2', '2002-03-02 00:00:00');
标签:comment,存储,索引,进阶篇,引擎,InnoDB,MySQL,数据 From: https://www.cnblogs.com/liuwd/p/16853208.html