MySql中的存储引擎和索引

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《MySql中的存储引擎和索引》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

一、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;

今天关于《MySql中的存储引擎和索引》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！