存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

可在创建表的时候，来指定选择的存储引擎，若未指定将自动选择默认的存储引擎。

-- 查询当前数据库支持的存储引擎
show engines;
-- 建表时指定存储引擎
CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。

特点：

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

文件：

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。

可通过show variables like 'innodb_file_per_table';查询，若该参数开启（ON），代表InnoDB引擎的表，每一张表都对应一个ibd文件。

可以使用mysql提供的一个指令ibd2sdi xxx.ibd文件，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

逻辑存储结构：

表空间：InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，表空间中可包含多个Segment段。

段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

区：区是表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

页：页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

行：InnoDB 存储引擎是面向行的，即数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段。

MyISAM

MyISAM是MySQL早期的默认存储引擎。

特点：

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件：

xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

内存存放
hash索引（默认）

文件：

xxx.sdi：存储表结构信息

区别及特点

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持(5.6版本之后)	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

存储引擎选择

在选择存储引擎时，应根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可根据实际情况选择多种存储引擎进行组合。

InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，称之为全表扫描，性能很低。

优点：

提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

索引列也要占用空间。
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene、Solr、ES

不同的存储引擎对于索引结构的支持

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree（空间索引）	不支持	支持	不支持
Full-text（全文索引）	5.6版本之后支持	支持	不支持

视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

创建

1
2
3

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]
-- 演示
create or replace view stu_v_1 as select id,name from student where id <= 10;

查询

# 查看创建视图语句
SHOW CREATE VIEW 视图名称;
# 查看视图数据
SELECT * FROM 视图名称 ...... ;
-- 演示
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;

修改

# 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
# 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 演示
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;

删除

1
2
3

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
-- 演示
drop view if exists stu_v_1;