MySQL进阶

2023-03-17 11:42:06 浏览数 (2)

存储引擎、索引、视图

# MySQL 进阶

# 存储引擎

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

存储引擎选择语法

代码语言：javascript复制
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)

将数据于索引分开村塾，索引结构的叶子节点关联的是对应的主键

可以存在多个

聚集索引选取规则：

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

# 索引设计原则

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

# SQL 优化

[待补充…]

# 视图

# 语法

创建

代码语言：javascript复制
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

查询

代码语言：javascript复制
查看创建视图的语句：
SHOW CREATE VIEW 视图名称;
查看视图数据（同查表）：
SELECT * FROM 视图名称...;

修改

代码语言：javascript复制
方式一（同创建视图语法）：
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
方式二：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

删除

代码语言：javascript复制
DROP VIEW [IF EXISTS] 视图名称[, 视图名称]...;

# 检查选项

代码语言：javascript复制
...[WITH [CASCADED | LOCAL] CHECK OPTION]

当使用 WITH CHECK OPTION 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql 提供了两个选项：CASCADED 和 LOCAL，默认值为 CASCADED。

CASCADED：当一个视图是基于另一个视图创建时，CASCADED 选项会检查所有向下关联的视图的限制，即使所依赖的视图没有定义 CHECK OPTION

LOCAL：当一个视图是基于另一个视图创建时，LOCAL 选项会检查所有向下关联的视图的限制，如果所依赖的视图没有定义 CHECK OPTION，则不检查对应的视图限制

# 更新条件

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项，则该视图不可更新：

  1. 聚合函数或窗口函数（SUM ()、 MIN ()、 MAX ()、 COUNT () 等）
  2. DISTINCT
  3. GROUP BY
  4. HAVING
  5. UNION 或者 UNION ALL

# 视图的作用

  1. 简单 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
  2. 安全 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
  3. 数据独立 视图可帮助用户屏蔽真实表结构变化带来的影响。

MySQL 数据库

innodb myisam mysql null 索引

0 人点赞