存储引擎、索引、视图
体系结构:连接层、服务层、引擎层、存储层
存储引擎选择语法
SHOW ENGINES;
CREATE TABLE XXX(...) ENGINE = INNODB;
存储引擎特点
INNODB 与 MyISAM:事务、外键、行级锁
存储引擎应用
INNODB:存储业务系统中对于事务、数据完整性要求较高的核心数据。
MyISAM:存储业务系统的非核心事务。
索引优缺点
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的 IO 成本 | 索引列也是要占用空间的。 |
通过索引列对数据进行排序,降低数据排序的成本,降低 CPU 的消耗。 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、UPDATE、DELETE 时,效率降低。 |
索引结构
索引结构 | 描述 |
---|---|
B+Tree 索引 | 最常见的索引类型,大部分引擎都支持 B+树索引 |
Hash 索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是 MyISAM 引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES |
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能由一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的时文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引 (Clustered Index) | 将数据存储和索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引 (Secondary Index) | 将数据于索引分开村塾,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
[待补充…]
创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
查询
查看创建视图的语句:
SHOW CREATE VIEW 视图名称;
查看视图数据(同查表):
SELECT * FROM 视图名称...;
修改
方式一(同创建视图语法):
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
方式二:
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
删除
DROP VIEW [IF EXISTS] 视图名称[, 视图名称]...;
...[WITH [CASCADED | LOCAL] CHECK OPTION]
当使用 WITH CHECK OPTION 子句创建视图时,MySQL 会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL 允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql 提供了两个选项:CASCADED 和 LOCAL,默认值为 CASCADED。
CASCADED:当一个视图是基于另一个视图创建时,CASCADED 选项会检查所有向下关联的视图的限制,即使所依赖的视图没有定义 CHECK OPTION
LOCAL:当一个视图是基于另一个视图创建时,LOCAL 选项会检查所有向下关联的视图的限制,如果所依赖的视图没有定义 CHECK OPTION,则不检查对应的视图限制
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项,则该视图不可更新: