文章目录
1. 存储引擎
1.1 MySQL体系结构
1.2 存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的
,所以存储引擎也可被称为表类型。
存储引擎是用来控制数据库如何存,如何取,如何组织的。
不同的存储引擎,索引结构不同。
1.2.1 创建存储引擎
CREATE TABLE 表名 (
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
......
字段6 字段6类型[COMMENT 字段6注释] # 最后没逗号
) ENFINE = INNODB [COMMENT 表注释];
1.2.2 查看数据库支持的存储引擎
SHOW ENGINES;
1.3 存储引擎特点
1.3.1 InnoDB
- 介绍:
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL 5.5之后,InnoDB是默认的MySQL存储引擎。
- 特点:
DML操作遵循ACID模型,支持事务
;
行级锁
,提高并发访问性能;
支持外键
FOREIGN KEY约束,保证数据的完整性和正确性。 - 文件:
xxx.ibd
: xxx代表的是文件名,innoDB引擎的每张表都会对应一个这样的表空间文件,存储该表的结构(frm、sdi)、数据和索引。
参数:innodb_file_per_table
这个文件是二进制文件,是看不懂的,想要看懂这个文件,要执行
ibd2sdi table01.ibd
InnoDB结构特点
1.3.2 MyISAM
- 介绍:
MyISAM是MySQL早期的默认存储引擎。 - 特点:
不支持事务,不支持外键。
支持表锁
,不支持行锁。
访问速度快。 - 文件
xxx.sdi: 存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
1.3.2 MEMORY
介绍:
MEMORY引擎的表数据时存储在内存中的,由于受到硬件问题,或断电问题的影响,只能将这些表作为临时表或缓存使用。
特点:
内存存放
hash
索引(默认)
文件:
xxx.sdi: 存储表结构信息。
1.3.3 对比
特点 | InnoDB | MyISAM | MEMORY |
---|---|---|---|
存储限制 | 64TB | 有 | 有 |
事务安全 | 支持 | - | - |
锁机制 | 行锁 | 表锁 | 表锁 |
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | - | - | 支持 |
全文索引 | 支持(5.6版本后) | 支持 | - |
空间使用 | 高 | 低 | N/A |
内存使用 | 高 | 低 | 中等 |
批量插入速度 | 低 | 高 | 高 |
支持外键 | 支持 | - | - |
1.3.4 存储引擎选择
在选择存储引擎的时候,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
- InnoDB:是MySQL的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB是比较合适的选择。
- MyISAM:如果应用是以读操作和插入操作为主,只有少量的更新和删除,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。
- MEMORY: 将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
1.3.5 总结
- 体系结构
连接层、服务层、引擎层、存储层 - 存储引擎特点:
InnoDB和MyISAM: 事务、外键、行锁 - 存储引擎应用
InnoDB: 存储业务系统对于事务、数据完整性要求较高的核心数据。
MyISAM: 存储业务系统的非核心业务。
2. 索引
介绍:
索引(index)是帮助MySQL高效获取数据
的数据库结构
(有序
),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
2.1 索引优缺点
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占据空间的 |
通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时也降低了更新表的速度 ,如对表进行INSERT、UPDATE、DELETE时、效率降低 |
2.2 索引结构
索引结构 | 描述 |
---|---|
B+tree树 | 最常见的索引类型,大部门引擎都支持B+树索引 |
Hash索引 | 底层数据结构使用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree索引(空间索引) | 空间索引是MyISAM 引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES |
索引 | InnoDB | MyISAM | MEMORY |
---|---|---|---|
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后又支持 | 支持 | 不支持 |
我们平常说的索引,如果没有特殊指明,都是B+树结构组织的索引。