MySQL 存储引擎 可以理解为，MySQL的“文件系统”，（插件形式存在）只不过功能更加强大。mysql提供的存储引擎有InnoDB、MyISAM等 TokuDB 是第三方的存储引擎moardb，数据压缩比打，写入数据快，如果你的应用是读多写少的情况强烈建议使用此存储引擎 innodb存储引擎的功能 一、查看存储引擎 1、查看默认存储引擎 SELECT @@default_storage_engine; 2、查看所有支持的存储引擎 show engines; 3、 查看某一个表的存储引擎 show create table city\G 或 show table status like 'city'\G; 4、查看world数据库下边所有表的存储引擎 select table_schema,table_name,engine from information_schema.tables where table_schema='world'; 5、查看所有使用myisam存储引擎的表 select table_schema,table_name,engine from information_schema.tables where engine='myisam'; 二、设置存储引擎 查看存储引擎默认配置 show variables like '%engine%'; 1、在启动配置文件中设置服务器存储引擎： [mysqld] default-storage-engine= 2、使用 SET 命令为当前客户机会话设置： SET @@storage_engine=; 3、在建表CREATE TABLE 语句指定： CREATE TABLE test (id int) engine = innodb 三、Innodb体系结构——物理存储结构（表空间） 支持事务：为了保证数据的完整性，将多个操作变成原子性操作 支持行级锁：两个人不能同时修改一行数据，修改数据频繁时候使用 支持表级锁：两个人不能同时修改同一张表，对于大量的数据同时修改使用 支持外键，约束两张表中的关联字段不能同时添加、删除 表空间实际上就在系统分区之上加了一层逻辑概念，方便扩展存储空间 共享表空间：主要存放系统元数据等（不用于生产） 默认情况下，InnoDB 元数据、撤消日志和缓冲区存储在系统“表空间”中。 这是单个逻辑存储区域，可以包含一个或多个文件。 每个文件可以是常规文件或原始分区。 最后的文件可以自动扩展。 show variables like '%data%'; # 查看共享表空间配置 innodb_data_file_path ibdata1:76M; ibdata2:50M: autoextend 第一个文件使用的大 第二个文件起始大小 自动扩展 4864x16/1024 独立表空间：主要存放用户数据 查看独立表空间是否开启（5.6之后默认开启） mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) 单独删除表空间的ibd文件 alter table t1 discard tablespace; 四、Innodb存储引擎——事务ACID Atomic（原子性） 所有语句作为一个单元全部成功执行或全部取消。 Consistent（一致性） 如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。 Isolated（隔离性） 事务之间不相互影响。 Durable（持久性） 事务成功完成后，所做的所有更改都会准确地记录在 数据库中。所做的更改不会丢失。 事务的操作语句 一个事务的操作语句是标准的DML语句（update，delete,insert），在执行dml语句的时候会默认在前边加上一个begin 1、 START TRANSACTION（或 BEGIN）：显式开始一个新事务 1 2 3 COMMIT：永久记录当前事务所做的更改(提交) 2、 START TRANSACTION（或 BEGIN）：显式开始一个新事务 1 2 3 ROLLBACK：取消当前事务所做的更改 SAVEPOINT：分配事务过程中的一个位置，以供将来引用 ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改 以上操作可以帮你回滚到某一个操做，而非commit提交，因此用的不多 查看是否每次执行dml语句都提交，off关闭提交on打开会提交，如果要永久关闭可以在配置文件中设置autocommit=OFF mysql> show global variables like 'autocommit'; 全局 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) 在有些业务繁忙企业场景下，这种配置可能会对性能产生很大影响，但对于安全性上有很大提高。 将来，我们需要去权衡我们的业务需求去调整是否自动提交。或者配置批量提交如1秒钟提交一次 隐式提交的语句 1、导致提交的非事务语句 DDL语句： （ALTER、CREATE 和 DROP） DCL语句： （GRANT、REVOKE 和 SET PASSWORD） 锁定语句：（LOCK TABLES 和 UNLOCK TABLES 2、开启AUTOCOMMIT = 1或START TRANSACTION会自动提交 3、 TRUNCATE TABLE、LOAD DATA INFILE、SELECT FOR UPDATE都会导致自动提交 五、 redo log在事务ACID过程中，实现的是“D”持久化的作用 redo log 事务中所有操作会先写到redo log中，然后再同步到数据库文件中。所以数据库文件进行事务操作修改时，redo log肯定已经记录了所有事务操作，此时即使数据库挂掉，事务操作也都已经持久化到redo log中了，数据库恢复后可以继续执行剩下操作。 redo log有两部分组成，redo log buffer与redo log file。如果每个事务的redo log都实时写到file中，再写到数据文件中，那么性能会比较差，所以可以先把一定时间间隔中的事务操作记录到buffer中，然后统一刷新到file中（此时数据库文件的刷新不一定晚于重做日志文件的刷新）。 redo log使用buffer缓存，丢失了数据持久性，数据库宕机时，没有持久化到redo log file中的事务操作也会丢失。此时数据库数据需要回滚到这些丢失事务之前的状态，undo log正好记录了事务之前的状态。 redo log是物理日志，记录里的是对数据库页的操作，不是sql语句，具有幂等性。 log group包含多个redo log，redo log循环覆盖log group中的文件。 innodb_flush_log_at_trx_commit 这个参数值设置为1的时候，表示每次事务的 redo log 都直接持久化到磁盘，建议设置为1，保证MySQL异常重启后数据不丢失。 6、Innodb存储引擎——事务日志undo 在事务ACID过程中，实现的是“A、C”原子性和一致性的作用。 undo log undo log记录了事务提交之前的数据状态。所以当事务操作同步到数据文件仅仅执行了一半就失败了，恢复后无法找到剩余事务操作，那就只好回滚到事务执行前了。这是就可以使用undo log了。 不同于redo log存放在单独文件中，undo log存放在数据库内部特殊的段中（undo segment），这个段位于共享表空间中。可以知道，undo log必然发生在事务执行之前，所以事务操作执行开始了，undo log必然已经存在了。。 另外一个场景是如果数据库意外宕机，在重新开启数据库的时候会优先读取redo日志把数据恢复到最后一次commit的状态，那如果在断电的时候没有commit呢，也就是没有commit标记，这时候就需要借助undo日志把回滚到宕机之前的状态，确保数据的一致性 7、Innodb存储引擎——事务中的锁(1) 在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。 如果一个事物1正在数据A那么他就拥有了这个数据的锁 锁的粒度： MyIasm：低并发锁——表级锁 Innodb：高并发锁——行级锁 四种隔离级别 READ UNCOMMITTED 允许事务查看其他事务所进行的未提交更改，事务中修改，未提交，其他事务也能看到 READ COMMITTED 允许事务查看其他事务所进行的已提交更改，有的企业会用，会出现两次事务读取数据不一致，幻读 REPEATABLE READ****** 确保每个事务的 SELECT 输出一致,InnoDB 的默认级别常用，会出现幻读问题，**需要注意的是InnoDB存储引擎通过多版本并发控制解决了幻读问题 SERIALIZABLE 将一个事务的结果与其他事务完全隔离，解决所有问题，但是慢，导致大量的超时与锁争用。 开启事务和锁 begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值，for update添加行锁； update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务
#yyds干货盘点#--MySQL 的存储引擎
