君玉自牧
目录

MySQL 事务 & 视图 & 索引

# 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据

# 说明

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 INSERT、UPDATE、DELETE 语句

# 条件

# 原子性

Atomicity：每个事务（Transaction）都看作最小逻辑操作，不可进行分割；执行错误，会被回滚（Rollback）到事务开始前

# 一致性

Consistency：在事务开始之前和事务结束以后，数据库的完整性没有被破坏

# 隔离性

Isolation：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

  • 读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失；通过“排他写锁”实现
  • 读提交（Read Committed）：不可重复读取，但不允许脏读取；通过“瞬间共享读锁”和“排他写锁”实现
  • 可重复读（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据；通过“共享读锁”和“排他写锁”实现
  • 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行

# 持久性

Durability：当事务执行完毕（事务提交），该操作将永久的改变了数据库中的数据

# 语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
1
2
3
4
5
6
7

# 方法

# 手动提交

用 BEGIN、ROLLBACK、COMMIT 来实现

#开始一个事务
BEGIN
#事务回滚
ROLLBACK
#事务确认
COMMIT
1
2
3
4
5
6

# 自动提交

直接用 SET 来改变 MySQL 的自动提交模式

禁止自动提交
SET AUTOCOMMIT=0
开启自动提交
SET AUTOCOMMIT=1
1
2
3
4

# 视图

表中存放的是实际的数据，而视图中存放的是 SQL 查询语句

当我们使用视图时，会运行视图里的 SQL 查询语句创建出一张临时表，当客户端与数据库之间的连接断开后，临时表会被自动删除

# 优势

# 简单

使用视图的用户完全不需要关心后面对应的表的结构、关联条件、和筛选条件

# 安全

使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，而通过视图可以轻松实现

# 独立

一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，可通过修改视图来解决，不会对访问者造成影响

# 操作

视图的操作跟 MySQL 一样，包括增、删、改、查

# 创建

创建视图需要有 CREATE VIEW 的权限，并且对于查询涉及到的列有 SELECT 权限；如果使用 CREATE OR REPLACE，那么哈需要有该视图的 DROP 权限

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
1

# 修改

ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED|LOCAL] CHEDK OPTION];
1

# 删除

DROP VIEW [IF EXISTS] view_name [,view_name...] [RESTRICT|CASCADE];
1

# 查看

MySQL v5.1 开始，使用SHOW TABLES命令显示表名的同时也会显示视图的名字，不存在单独显示视图的SHOW VIEW命令

# 索引

索引是一种能提高数据库查询效率的数据结构，是数据库中用来提高性能的常用工具；类似于一本字典的目录，可以帮你快速找到对应的记录。

# 优缺点

# 优点

  • 索引可以加快数据查询速度，减少查询时间
  • 唯一索引可以保证数据库表中每一行的数据的唯一性

# 缺点

  • 创建索引和维护索引要耗费时间
  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
  • 以表中的数据进行增、删、改的时候，索引也要动态的维护

# 分类

所有的 MySQL 列都可以被设为索引，对相关列使用索引是提高 SELECT 性能的最佳途径。根据存储引擎可以定义每个表最大的索引数和最大索引长度，每种存储引擎对每张表支持最少 16 个索引，总索引长度至少为 265 个字节；大多数存储引擎有更高的限制。

# 数据结构

  • B+树索引：所有数据存储在叶子节点，复杂度为 O(logn)，适合范围查询
  • 哈希索引: 适合等值查询，检索效率高，一次到位
  • 全文索引：MyISAM 和 InnoDB 中都支持使用全文索引，一般在文本类型 char、text、varchar 类型上创建
  • R-Tree 索引: 用来对 GIS 数据类型创建 SPATIAL 索引

# 物理存储

  • 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据（Innodb 存储引擎）
  • 非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列（Innodb 存储引擎）

在 InnoDB 存储引擎中， 聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。

聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询

  • 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个
  • 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表

而在 MyISM 存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据

# 逻辑维度

  • 主键索引：一种特殊的唯一索引，不允许有空值
  • 普通索引：MySQL 中基本索引类型，允许空值和重复值
  • 联合索引：多个字段创建的索引，使用时遵循最左前缀原则
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值
  • 空间索引：MySQL5.7 之后支持空间索引，在空间索引这方面遵循 OpenGIS 几何数据模型规则

# B+TREE 索引

# 特点

  • 以 B+TREE 结构存储数据，大大加快了数据的查询速度
  • 在范围查找的 SQL 语句中更加适合（顺序存储）

# 场景

  • 全值匹配的查询 SQL
  • 联合索引汇中匹配到最左前缀查询
  • 匹配模糊查询的前匹配
  • 匹配范围值的 SQL 查询（not in和<>无法使用索引）
  • 覆盖索引的 SQL 查询

# HASH 索引

# 特点

  • 基于 Hash 表实现，只有查询条件精确匹配 Hash 索引中的所有列才会用到
  • 为 Hash 索引中的每一列都计算 hash 码并存储，所以每次读取都会进行两次查询
  • Hash 索引无法用于排序
  • Hash 不适用于区分度小的列上，如性别字段

# 使用

# 创建索引

索引在创建表的时候可以同时创建，也可以随时增加新的索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option]...
    
    index_col_name:
        col_name [(length)] [ASC | DESC]
1
2
3
4
5
6
7
8

也可以是使用 ALTER TABLE 的语法来增加索引，语法与 CREATE INDEX 类似

# 删除索引

DROP INDEX index_name ON tbl_name
1

# 原则

  • 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如“学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。 如果使用姓名的话，可能存在同名现象，从而降低查询速度。

  • 为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间；为其建立索引，可以有效地避免排序操作。

  • 要在条件列上创建索引，而不是查询列

最适合索引的列是出现在 WHERE 语法中的列，或连接子句中指定的列，而不是出现在 SELECT 关键词后的列；如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度；为这样的字段建立索引，可以提高整个表的查询速度。

  • 限制索引的数目

索引的数目并不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦；越多的索引，会使更新表变得很浪费时间。

  • 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。

例如：对一个 CHAR（100）类型的字段进行全文检索需要的时间肯定要比对 CHAR（10）类型的字段需要的时间要多。

  • 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

例如：TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间；如果只检索字段的前面的若干个字符，这样可以提高检索速度。

  • 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

  • 小表不应建立索引

包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

# 小结

当对索引字段进行范围查询时，只有 BTREE 索引可以通过索引访问，而 HASH 索引则会进行全表扫描；如果一定要使用范围查询，那么创建索引时就应该选择 BTREE 索引。

  • B+TREE 索引可以进行范围查询，Hash 索引不能。
  • B+TREE 索引支持联合索引的最左侧原则，Hash 索引不支持
  • B+TREE 索引支持 ORDER BY 排序，Hash 索引不支持
  • B+TREE 索引使用 LIKE 进行模糊查询的时候，后面的语句（如 %）可以起到优化的作用，Hash 索引根本无法进行模糊查询
  • Hash 索引在等值查询上比 B+TREE 索引效率更高，但是索引列的重复值很多的话，Hash 冲突，效率降低
网页编辑 (opens new window)
最近提交: 2023/03/22, 11:52:35
MySQL v5.7.9 安装部署
MySQL 慢查询 & 可视化分析

← MySQL 慢查询 & 可视化分析→

Theme by Vdoing | Copyright © 2011-2023 | 君玉自牧
粤ICP备15057965号