Mysql体系知识总结_mysql 知识体系

MySQL 是一种关系型数据库，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL（实际上他内部做了很多的修改），因此它的稳定性是有保障的。
MySQL的默认端口号是3306。

一、存储引擎

MySQL当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。当然，mysql是支持好多种存储引擎的

1、InnoDB（Mysql 5.5以后默认的存储引擎）

• InnoDB支持行级锁和表级锁，默认是行级锁
• 支持事务，外键等功能
• 具有 事务、回滚、崩溃后安全恢复能力
• 支持MVCC（多版本并发控制，只有写写的时候阻塞，读读、读写、写读都可以并发执行） ；应对高并发事务, MVCC比单纯的加锁更高效。

2、MyISAM（5.5以前默认）

• 只有表级锁
• 性能极佳，每次查询具有原子性，但不提供事务支持
• 崩溃后无法安全恢复
• 不支持外键
• 不支持MVCC

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果不考虑 MyISAM 崩溃恢复问题的话）。

二、索引

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

1、为什么使用索引

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以明显提高数据检索的速度（前提是索引生效） （主要原因）
• 帮助服务器避免排序和临时表
• 可以加速表和表之间的连接

2、哈希索引

• 底层的数据结构是哈希表（数组+链表），可以一次定位，查询比较快
• 适用于单条数据查询，查询较快
• 不能使用范围查找，只能用于等值比较查询
• 无法用于排序运算，因为哈希索引数据并不是按照索引值顺序存储的

3、BTree索引（B+树）

• B+树是有序的，在这种范围查询中，优势非常大
• 我们可以看到B+树只有叶子结点是存有值的，别的节点存的索引，而且叶子节点之间是有指向的，方便我们做范围查询。
• ⭐ （常问）：我们为什么使用B+树而不使用B树呢？
  • 首先呢，B树除了叶子结点，其余的节点也是存有值的，这就导致这些个节点要比B+树（只有叶子结点存有值）的大，我们知道，数据库的数据是存在磁盘上的，我们需要读到内存中去，当数据量非常大的情况下，我们拿到这些数据，如果采用B树的话，可能我们需要取两次甚至更多次才能取到，而且我们在检索的时候不一定第一大块就有我们需要的那个数据，也就是我们需要做好多次查询。但是B+树不一样，我们也许只需要一次就能把所有的数据取出（一次性的加载到内存，减少了磁盘IO的操作），毕竟他只有叶子结点存有值，对吧。所以说我们只需要遍历一次就可以将我们需要的值遍历出来。
  • 另外还有就是B+树的叶子节点是存在指向的，对于我们的范围查询是非常友好的，B树却没有。

4、聚簇索引与非聚簇索引

• 聚簇索引和非聚簇索引都是采用的B+树，信息都是在叶子结点

• 聚簇索引

  • 索引结构和数据一起存放的索引（也就是说这个叶子节点带有这一行的全部数据，不需要再根据地址去定位）
  • 优点
    • 得益于存储的数据，他的数据访问速递非常快，因为我找到这个索引之后就可以拿到他的数据，不用再根据这个去定位拿数据（节省了一次磁盘IO）
  • 缺点
    • 插入时依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
    • 更新主键的代价是很大的。如果对索引列的数据被修改时，那么对应的索引也将会被修改而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的所以对于主键索引来说，主键一般都是不可被修改的。
    • 空间消耗比较大，因为他除了存储索引，还包括了所有的数据，必然消耗更多的空间。

• 非聚簇索引（辅助索引）

  • 二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
  • 优点
    • 更新的代价比聚簇索引要低（叶子结点不存放数据）
  • 缺点
    • 插入也依赖于有序的数据
    • 二次查询（回表，最大的缺点） ：当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。（多了一次磁盘IO）

5、注意点

• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
• 在经常用在连接的列上，主要是一些外键，可以加快连接的速度
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 避免 where 子句中对字段施加函数，这会造成无法命中索引
• 进行加减乘除操作，导致无法命中索引（例：select c.id from t_stu where c.id + 1 = 2;）
• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗

6、最左前缀原则

顾名思义就是：最左优先。MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

• 如User表的name和city加联合索引就是(name,city)
  select * from user where name=xx and city=xx ; ／／可以命中索引
  select * from user where name=xx ; // 可以命中索引
  select * from user where city=xx ; // 无法命中索引

• 联合索引为（a，b，c）
  select * from table where a = ‘1’ // 走索引
  select * from table where a = ‘1’ and b = ‘2’ // 走索引
  select * from table where a = ‘1’ and c = ‘3’ // 走索引
  select * from table where a = ‘1’ and b = ‘2’ and c=‘3’ // 走索引
  select * from table where b = ‘1’ and c = ‘3 // 不走索引

三、事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。经典案例：转账。事务就是保证这两个关键操作（A转出去，B接收到）要么都成功，要么都要失败。

1、四大特性（ACID）

• 原子性（Atomicity）
  • 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）
  • 执行事务后，数据库从一个正确的状态变化到另一个正确的状态；
• 隔离性（Isolation）
  • 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）
  • 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

2、并发事务带来的问题

• 脏读

  • 就是说一个事务A正在访问数据，并对数据进行了修改操作，此时这个事务还没有及时更新数据库；此时另外一个事务B也访问了这条数据，然后使用了这条数据，因为这条数据是A正在修改还没有提交的数据，所以B读到的这个数据就是"脏数据"。

• 丢失修改

  • 就是说事务A读取了数据，同时事务B也读取了数据，A对数据进行了修改，B也对这个数据进行了修改，那么A修改的结果会丢失，因为称为丢失修改

• 不可重复读

  • 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读

  • 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

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

3、事务的隔离级别

• READ-UNCOMMITTED(读取未提交)

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

• READ-COMMITTED(读取已提交)

  • 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)

  • 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)

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

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生。

Next-key lock：record+gap 锁定一个范围，包含记录本身

四、Mysql中的乐观锁和悲观锁

1、悲观锁

当我一个事务要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他事务修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制(悲观锁)。

2、乐观锁

总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

五、数据库连接池

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。
————————————————————————来源于javaguide的GitHub

六、一条sql语句在Mysql中的执行过程

采用大神一张图（javaguide）：
需要说明一下就是：查询缓存(MySQL 8.0 版本后移除)

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器: 执行语句，然后从存储引擎返回数据。
• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。