MySQL的存储引擎、索引与事务

常见的端口号

• MySQL–3306
• http–80
• https–443
• tcp–23
• fcp–21
• tomcat–8080
• ssh–22
• oracle–1521
• rockermq–9876

存储引擎

使用指令查看所有引擎：

show engines;

从图中可以看出MySQL默认的存储引擎是InnoDB；并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

⭐MyISAM和InnoDB区别

在5.5版本之前的MySQL的默认的存储引擎是MyISAM。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。在5.5版本之后就引入了InnoDB（事务性数据库引擎）。
两者的对比：

1. 是否支持行级锁：MyISAM只有表级锁；而InnoDB支持行级锁和表级锁，默认是行级锁。
2. 是否支持事务和崩溃后的安全恢复：MyISAM强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否支持外键：MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用乐观(optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制

乐观锁与悲观锁：

• 场景
  一件商品，成本价是80元，售价是100元。老板先是通知小李，说你去把商品价格增加50元。小 李正在玩游戏，耽搁了一个小时。正好一个小时后，老板觉得商品价格增加到150元，价格太
  高，可能会影响销量。又通知小王，你把商品价格降低30元。
  此时，小李和小王同时操作商品后台系统。小李操作的时候，系统先取出商品价格100元；小王 也在操作，取出的商品价格也是100元。小李将价格加了50元，并将100+50=150元存入了数据 库；小王将商品减了30元，并将100-30=70元存入了数据库。是的，如果没有锁，小李的操作就 完全被小王的覆盖了。
  现在商品价格是70元，比成本价低10元。几分钟后，这个商品很快出售了1千多件商品，老板亏1 万多。
• 乐观锁与悲观锁
  上面的故事，如果是乐观锁，小王保存价格前，会检查下价格是否被人修改过了。如果被修改过 了，则重新取出的被修改后的价格， 150元，这样他会将120元存入数据库。
  如果是悲观锁，小李取出数据后，小王只能等小李操作完之后，才能对价格进行操作，也会保证 最终的价格是120元。

⭐索引

MySQL索引使用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

索引的优缺点

优点：

1. 可以加快数据的检索速度，这也是索引最主要的作用；
2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
3. 将随机IO变为顺序IO

缺点：

1. 创建索引和维护索引需要消耗许多时间。
2. 索引使用物理文件存储，会消耗一定空间。

MyISAM和InnoDB实现BTree索引方式的区别

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB

其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

事务

什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。
最经典的例子就是转账问题。

⭐事务的ACID特性

1. 原子性（Atomicity）：事务是执行的最小单位，不允许分割。原子性保证操作要么全做、要么全不做。
2. 一致性（Consistency）：执行事务之后，数据库从一个正确的状态转化为另一个正确的状态。
3. 隔离性（Isolation）：并发访问数据库时，一个事务不被其他事务影响，各并发事务之间数据库是独立的；
4. 持久性（Durability）：一个事务提交之后，他对数据库的改变时永久的，即使数据库发生故障也不会对其有影响。

⭐并发事务带来哪些问题?

• 脏读：一个事务的对数据库中的数据进行了修改，并且还没有提交到数据库中。另一个事务也读取并使用了这个数据，因为这个数据是还没有提交的，因此就是一个脏数据。
• 丢失修改：两个事务读取同一个数据，第一个事务修改了这个数据，另一个数据也修改了这个数据。这样就会导致第一个数据的修改内容丢失，因此成为丢失数据。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读：指一个事务内多次读取同一个数据，其在这个事务没有结束时另一个事务也访问该数据并修改。那么就会出现在第一个数据访问该数据多次之间，由于第二个事务修改而导致读取的可能不太一样。
• 幻读：幻读和不可重复度类似，它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

⭐事务隔离级别有哪些?MySQL的默认隔离级别是?

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×