MySQL的存储引擎、事务补充、MySQL的锁机制、MySQL的日志

数据运维 2023-11-09 共饮一杯 手机阅读

MySQL的存储引擎

概述

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

用户可以根据不同的需求为数据表选择不同的存储引擎

可以使用 SHOW ENGINES 命令查看Mysql的所有执行引擎，默认的执行引擎是InnoDB，支持事务，行级锁定和外键。

介绍

MyISAM：Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务

InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎

Memory： 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。

Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差

Federated ：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用

CSV ：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。

BlackHole： 黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继

ERFORMANCE_SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。

Mrg_Myisam Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

MyISAM、Memory、InnoDB三种存储引擎的比较

操作

  • 查询当前数据库支持的存储引擎 

SHOW ENGINES;
  • 查看当前的默认存储引擎 

SHOW VARIABLES LIKE '%storage_engine%';

查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 

SHOW CREATE TABLE student;

  • 创建新表时指定存储引擎

语法 

CREATE TABLE(...) ENGINE=MYISAM;

CREATE TABLE student(
    id INT,
    NAME VARCHAR(20)
)ENGINE=MYISAM;
  • 修改数据库引擎

语法 

ALTER TABLE 表名 ENGINE = 存储引擎;

ALTER TABLE student ENGINE = INNODB;
ALTER TABLE student ENGINE = MYISAM;

修改MySQL默认存储引擎方法

  • 关闭mysql服务
  • 找到mysql安装目录下的my.ini文件：
  • 找到default-storage-engine=INNODB 改为目标引擎， 如：default-storage-engine=MYISAM
  • 启动mysql服务

事务

MySQL8.x版本查询隔离级别（在Mysql8中，tx_isolation变量已修改为transaction_isolation）

语法 

select @@transaction_isolation;

同时，修改事务隔离级别的操作可以修改为（当前会话立即生效，不需要重启或再开）

语法 

SET transaction_isolation = '隔离级别';
或
SET SESSION transaction_isolation = '隔离级别';

SET transaction_isolation = 'READ-UNCOMMITTED';
或
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

查看隔离级别

语法

SHOW VARIABLES LIKE '%isolation%'; 
或
SELECT @@transaction_isolation;

MySQL的锁机制