MySQL进阶

TagBug

发布于 2023-03-17 11:42:06

7220

发布于 2023-03-17 11:42:06

文章被收录于专栏：TagBug

存储引擎、索引、视图

# MySQL 进阶

# 存储引擎

体系结构：连接层、服务层、引擎层、存储层

存储引擎选择语法

SHOW ENGINES;
CREATE TABLE XXX(...) ENGINE = INNODB;

存储引擎特点

INNODB 与 MyISAM：事务、外键、行级锁

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

存储引擎应用

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)	将数据于索引分开村塾，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

# 索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

# SQL 优化

[待补充…]

# 视图

# 语法

创建

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，则不检查对应的视图限制