MySql存储引擎

面屏思过 2022-09-28 我要评论

一、MySql的逻辑结构

MySQL体系结构分为四层：分别是连接层、服务层、存储引擎层、系统文件层。

连接层又称为客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
服务层是MySQL Server的核心：主要包含系统管理和控制工具、SQL接口、解析器、查询优化器、缓存。
存储引擎层：负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，不同的存储引擎具备不同的功能，可以根据自己的需要进行选择，最常见的是MyISAM和InnoDB。插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
系统文件层：将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件（redolog,undo.log），数据文件，索引文件，二进制日志，错误日志，配置文件，pid 文件，socket 文件等。

二、什么是存储引擎

MySql数据库使用不同的机制存取表文件，有不同的存储方式，索引技巧，锁定水平且广泛的功能和能力。将这些不同的技术及配套的功能称为存储引擎。

MySQL支持的存储引擎

MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等，其中较为常用的有三种：InnoDB、MyISAM、MEMORY。

三、操作

查询数据库支持的存储引擎：

SHOW ENGINES;

查询某个数据库中所有数据表的存储引擎：

// 标准语法： SHOW TABLE STATUS FROM 数据库名称;

// 查询db4数据库所有表的存储引擎
SHOW TABLE STATUS FROM db4;

查询某个数据库中某个表的存储引擎：

//标准语法： SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';

// 查看db4数据库中user表的存储引擎
SHOW TABLE STATUS FROM db2 WHERE NAME='user';

创建数据表指定存储引擎：

// 标准语法：CREATE TABLE 表名( 列名,数据类型, ... )ENGINE = 引擎名称;

CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE=MYISAM;

修改数据表的存储引擎：

// 标准语法： ALTER TABLE 表名 ENGINE = 引擎名称;

// 修改engine_test表的存储引擎为InnoDB
ALTER TABLE engine_test ENGINE=INNODB;

四、数据库的索引

MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。

在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的分类

普通索引： 最基本的索引，它没有任何限制。
唯一索引：索引列的值必须是唯一，允许有控制，如果是组合索引，则列值组合必须唯一。
主键索引：一种特殊的唯一索引，不允许有空值。一般在建表是同时创建主键索引。
组合索引：将单列索引进行组合
外键索引： 只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现联级操作。
全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

五、索引操作

创建索引：

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
	[USING 索引类型]  -- 默认是BTREE
	ON 表名(列名...);
       
// 为student表中的name列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

//为student表中的age列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

查询索引：

// 标准语法： SHOW INDEX FROM 表名;

// 查询student表中的索引  (主键列自带主键索引)
SHOW INDEX FROM student;

添加索引：

ALTER添加索引
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

删除索引：

// 标准语法：DROP INDEX 索引名称 ON 表名;

// 删除idx_score索引
DROP INDEX idx_score ON student;