mysql学习整理

MySQL

mysql是一种关系型数据库，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。MySQL的默认端口号是3306。

存储引擎

查看mysql提供的所有引擎，当前的默认存储引擎是InnoDB，并且在5.7版本中所有的引擎中只有InnonDB支持事务

查看MySQL当前默认的存储引擎

show variables like '%storage_engine%';

MyISAM和InnoDB区别

在5.5以前，mysql的默认存储引擎是MyIsam，性能比较好，而且提供了大量的的特性，但是MyIsam不支持事务和行级锁，并且最大的缺陷就是崩溃之后无法安全恢复，所以5.5之后Mysql引入了InnonDB（事务级引擎）。

两者的区别：

（1）是否支持行级锁：InnonDB支持

（2）是否支持崩溃后安全恢复：InnonDB支持

（3）是否支持MVCC：InnonDB支持

（4）是否支持外键：InnonDB支持

MVCC：多版本并发控制

一种提高并发的技术，在老版本中，只有读读之间可以实现并发，读写、写读 、写写都会阻塞，引入多版本之后只有写写会阻塞，其他三种可以并行。

特点：

（1）：每行数据都存在一个版本，每次数据更新时都更新该版本

（2）：修改时copy出当前版本，然后随意修改，各个事务之间无干扰

（3）：保存时比较版本号，如果成功则commit，失败则rollback

InnonDB实现MVCC案列：

当事务A需要修改某行的值时会有如下步骤

（1）：用排他锁锁定该行，记录 redo_log

（2）：把该行修改前的值记录到undo_log

（3）：修改当前值，记录事务编号，将回滚指针指向undo_log的修改前的行

（4）：修改成功，啥也不做，失败则回滚指向undo_log的修改前的行

索引

Mysql索引使用的数据结构主要有BTree索引和哈希索引，对于哈希索引来说，底层的数据就是哈希索引，因此在绝大多数需求为查询单条记录的时候，使用哈希索引，其余大部分场景使用BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

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

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行 

四大特性：

（1）：原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

（2）：一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

（3）：持久性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

（4）：隔离性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务可能会带来的问题

（1）：脏读，当事务A查到了一个数据，对其进行修改，而该数据还没提交，事务B查询到了该数据也对其进行修改，因为事务A的数据还未提交，所以事务B获取到的数据就是脏数据。

（2）：丢失修改，事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

（3）：幻读 ，幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。比如说老版给你发了一笔工资，你看到了，随即老板发现给你发多了，又撤回了。

（4）： 不可重复读，事务A查询了两次某数据，但在两次查询之间事务B查询了该数据并对其做了修改，导致事务A中两次查询的数据不一致。

不可重复读和幻读区别：

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

事务的隔离级别

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。 

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

MySQL InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

（1）：innodb对于行的查询使用next-key lock
（2）：Next-locking keying为了解决Phantom Problem幻读问题
（3）：当查询的索引含有唯一属性时，将next-key lock降级为record key
（4）：Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
（5）：有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为  RC、 B. 将参数innodb_locks_unsafe_for_binlog设置为1

 大表优化

（1）：限定查询数据范围。eg：近一个月内的数据

（2）：读写分离。

（3）：垂直分区。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

（4）：水平分区。保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

数据库分片的两种常见方案：

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

分库分表之后id主键如何处理：

雪花算法（snowflake）：snowflake是Twitter开源的分布式ID生成算法，结果是一个long型的ID，其核心思想是：使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号，最后还有一个符号位，永远是0