存储引擎
概述
- 数据库存储引擎是数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。
- 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
- 用户可以根据不同的需求为数据表选择不同的存储引擎
- 可以使用SHOW ENGINES命令可以查看Mysq|的所有执行引擎我们可以到默认的执行引擎是innoDB支持事务，行级锁定和外键。
分类
- MyISAM：Niysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务
- InnoDB：事务型速记的首选引擎，支持ACID事务， 支持行级锁定，MySQL5.5成为默认数据库引擎
- Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。
- Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差
- Federated：将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
- CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.csv文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储弓|擎不支持索引。
- BlackHole：黑洞引擎,写入的任何数据都会消失，一般用于记录binlog做复制的中继
- ERFORMANCE_ SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。
- Mrg_.Myisam Merge存储引擎，是一组Mylsam的组合, 也就是说，他将Mylsam弓 |擎的多个表聚合起来,但是他的内部没有数据，真正的数据依然是MyIsam引|擎的表中，但是可以直接进行查询、删除更新等操作。
| 功能 | MyISAM | MEMORY | InnoDB |
| 存储限制 | 256TB | RAM | 64TB |
| 支持事务 | No | No | Yes |
| 支持全文索引 | Yes | No | No |
| 支持B树索引 | Yes | Yes | Yes |
| 支持哈希索引 | No | Yes | No |
| 支持集群索引 | No | No | Yes |
| 支持数据索引 | No | Yes | Yes |
| 支持数据压缩 | Yes | No | No |
| 空间使用率 | 低 | N/A | 高 |
| 支持外键 | No | No | Yes |
操作
查询当前数据库支持的存储引擎:
show engines;
查看当前的默认存储引擎:
show variables like '%storage_engine%' ;
查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table 表名 ;
创建新表时指定存储引擎:
create table(. ..) engine=My ISAM;
修改数据库引擎
alter table 数据库名 engine = 引擎名;
修改MySQL默认存储引擎方法
- 关闭mysq|服务
- 找到mysql安装目录下的my.ini文件:
- 找到default-storage-engine=INNODB改为目标引擎，如: default-storage-engine=MYISAM
- 启动mysq|服务
事务
在MySQL中的事务(Transaction) 是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。.
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务用来管理DDL、DML、DCL操作，比如insert,update,delete语句，默认是自动提交的。
操作
之前的所有SQl操作其实也有事务，只是MySQL自动帮我们完成的，每执行-条sQL时MySQL就帮我们自动提交事务,因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。
在MySQL中直接用SET来改变MySQL的自动提交模式
set autocommit = 0； -- 禁止自动提交
set autocommit = 1； -- 开启自动提交MySQL的事务操作主要有以下三种
1、开启事务：Start Transaction
任何一条DML语句(insert、 update、 delete)执行， 标志事务的开启
命令：BEGIN或START TRANSACTION
2、提交事务：Commit Transaction
成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
命令：COMMIT
3、回滚事务：Rollback Transaction
失败的结束，将所有的DML语句操作历史记录全部清空
命令：ROLLBACK
事务特性
- 原子性：事务是一个不可分割的整体，事务开始后的所有操作,要么全部完成，要么全部不做
- 一致性：系统从一个正确的状态，迁移到另一个正确的状态
- 隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不可见
- 持久性：事务一旦提交，则其结果是永久性的
事务隔离级别
分类
| 名称 | 状态 | 相关性 | 脏读 | 不可重复读 | 幻读 |
| READ UNCOMMITTED | 读未提交 | 大 | 是 | 是 | 是 |
| READ COMMITTED | 读提交 | 较大 | 否 | 是 | 是 |
| REPEATEABLE READ | 可重复读 | 较小 | 否 | 否 | 是 |
| SERIALIZABLE | 序列化 | 小 | 否 | 否 | 否 |
脏读：一个事务会读到另一个事务没有提交的数据。
不可重复读：一个事务在没有提交期间，读取到的另一个事务操作的数据会不同。
幻读：一个事务提交前与提交后看到的数据会不一样。
读未提交(Read uncommitted)：一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读。
读已提交(Read committed)：一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
可重复读( Repeatable read )：就是在开始读取数据(事务开启)时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
串行(Serializable)：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
操作
查看隔离级别
show variables like ' %isolation%' ;
设置隔离级别
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、 read committed、 repeatable read、 serial izable
-- 设置read uncommitted
set session transaction isolation leve1 read uncommitted;
-- 设置read committed
set session transaction isolation level read committed;
-- 设置repeatable read
set session transaction isolation level repeatable read;
-- 设置serializable
set session transaction isolation level serializable;锁
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中，除传统的计算资源(如CPU、RAM、l/O 等)的争用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度看，锁对数据库而言显得尤其重要，也更加复杂。
分类
从对数据操作的粒度分
- 表锁：操作时，会锁定整个表。偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行锁：操作时，会锁定当前操作行。偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
| 存储引擎 | 表级锁 | 行级锁 |
| MyISAM | 支持 | 不支持 |
| InnoDB | 支持 | 支持 |
| MEMORY | 支持 | 不支持 |
| BDB | 支持 | 不支持 |
很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理(OLTP)系统。.
从对数据操作的类型分
- 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁(排它锁)：当前操作没有完成之前，它会阻断其他写锁和读锁。
操作
MyISAM表锁
MyISAM存储引擎只支持表锁。
MyISAM在执行查询语句(SELECT) 前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT 等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
加读锁
lock table 表名 read；
加锁者可读加锁的表不可以读其它表，不可以修改加锁的表，其它人可以读但不可以修改加锁的表。
加写锁
lock table 表名 write；
自己可读可写，别人不可读也不可写，且同一份数据只可以有一个写锁。
InnoDB行锁
行锁特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务，二是采用了行级锁。
InnoDB实现了两种类型的行锁：共享锁(S)又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一 把锁，都能访问到数据，但是只能读不能修改；排他锁(X)又称为写锁，简称x锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句， InnoDB会自动给涉及数据集加排他锁(X)；
对于普通SELECT语句，InnoDB不会加任何锁；
可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁(s)：SELECT * FROM 表名 WHERE ... LOCK IN SHARE MODE;
排他锁(X)：SELECT * LFROM 表名 WHERE ... FOR UPDATE;
日志
在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件，MySQL 也不例外。
日志分类：错误日志、二进制日志、查询日志、慢查询日志。
错误日志
错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的，默认存放目录为mysq|的数据目录，默认的日志文件名为hostname.err (hostname是 主机名)
查看日志位置指令
show variables like 'log_ error%';
二进制日志
二进制日志(BIN|OG) 即binlong日志，记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。
操作
二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。
Windows系统：my.ini Linux系统：my.cnf
需要将下边两行代码添加到上列文件中的[mysqld]下，并重启MySQL。
配置开启binlog日志，日志的文件前缀为 mysqlbin ---->生成的文件名如：myslbin.000001
log_bin = mysqlbin；
配置二进制日志的格式
binlog_format = STATEMENT；
查看MySQL是否开启了binlog日志
show variables like '1og_bin';查看binlog日志的格式
show variables like 'binlog_format';查看所有日志
show binlog events;查看最新的日志
show master status;查询指定的binlog日志
show binlog events in '日志名'；
show binlog events in 'binlog.000008';从指定的位置开始，查看指定的Binlog日志
show binlog events in '日志名' from 位置;
show binlog events in 'binlog.000008' from 666;从指定的位置开始，查看指定的Binlog日志，限制查询的条数
show binlog events in '日志名' from 位置 limit 条数;
show binlog events in 'binlog.000008' from 666 limit 2;从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000008' from 666 limit 1，2;清空所有的binlog 日志文件
reset master；日志格式
STATEMENT
该日志格式在日志文件中记录的都是SQL语句(statement) ，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库(slave)会将日志解析为原文本，并在从库重新执行一次。
ROW
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句：update tb_book set status = '1' ，如果是STATEMENT日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更。
MIXED
混合了STATEMENT和ROW两种格式。
查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。
默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置
set general_log = 1； -- 该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启
set general_log_file = file_name； -- 设置日志的文件名，如果没有指定，默认的文件名为host_name.log上列方法是临时开启查询日志，若要永久开启需要在相应文件下加入一些代码。
查看MySQL是否开启了查询日志
show variables like 'general_ 1og';慢查询日志
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志。long_query_time默认为10秒，最小为0，精度可以到微秒。默认不开启
set global slow_query_log = 1; -- 该参数用来控制慢查询日志是否开启，可取值：1和0，1代表开启，0代表关闭
set slow_query_log_file = slow_query.log; -- 该参数用来指定慢查询日志的文件名。
set long_query_time = 10; -- #该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s。查看慢查询日志是否开启
show variables like 'slow_query_log%';查看慢查询的超时时间
show variables like 'long_query_time%';
1083
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
