中间件-Mysql

FLOAT 和 DOUBLE区别？

  • float：8位精度，占用4个字节
  • double：18位精度，占用8字节

MyISAM 与InnoDB 区别

  • InnoDB 支持事务、外键、行锁，MyISAM只支持表锁
  • InnoDB 不缓存行数，MyISAM 用变量缓存总行数
  • InnoDB 必须有主键，MyISAM 可以没有
  • InnoDB 是聚集索引B+Tree，MyISAM是非聚集索引B-Tree。InnoDB的辅助索引存主键，MyISAM存物理地址
    • 存主键可以让辅助索引与数据的关联是主键的逻辑关系，按页存的主键聚簇索引怎么变换辅助索引都不用管

推荐使用自增 id 作为主键?

  • 普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会「导致普通索引的存储空间较大」

  • 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接「按照顺序插入」，不用刻意维护

  • 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，「导致页分裂维护成本较高」

InnoDB 支持的种事务隔离级别，以及逐级之间的区别？

  • read uncommited: 读未提交。 读写都不加锁，出现脏读
  • read commited： 读提交。 写加排他锁，读不加锁，出现不可重复读。（行锁）
  • repeatable read：可重复读。 写加排他锁，读加共享锁，出现幻读（mvcc，间隙锁）
  • serializable： 串行。 读写都加排他锁

在 Mysql 中 ENUM 的用法是什么？

  • 是枚举字符类型，在建表的时候指定

innodb：

  • 索引组织表：innodb中表是根据主键顺序组织存放。没有主键的表：使用建表时定义的第一个非空索引作为主键；自动创建6字节的指针_rowid作为主键。
  • 逻辑存储结构：表空间、段segment、区extent、页page（块block）；
  • 表空间：innodb所有数据都存储在同一表空间中，可以通过设置参数可以让每张表的部分内容（数据、索引、缓冲）独立存放到表空间；
  • 段：数据段、索引段、回滚段
  • 区：

sql执行计划

  • 使用explain进行查看
  • 主要看几个关键的字段，table、type、keys、ref、extra

mysql有哪些索引

  • 主键索引、单列索引、联合索引、唯一索引、前缀索引、字符串倒序索引、hash值索引

Sql优化

  • 使用explain查询计划可以知道是否使用了索引，是否进行来全表扫描
  • 优先使用合理的索引进行优化，再考虑其他优化方式。
    • **写频繁**的列慎重加索引，索引越多占用磁盘空间越大
    • 字段辨识度越高，索引的使用效果越好
    • 对短小的值加索引，占用空间小，io次数越少。某些情况下也可以使用字符串前缀加索引(前缀索索引有导致读取行数变多；无法使用索引覆盖)
    • 需要加锁的字段，就需要加索引，否则会锁表
  • 在WHERE、JOIN ON和ORDER BY使用到字段上加上索引
  • 避免查询时判断NULL会导致全表扫描
  • 避免使用OR会导致全表扫描,可以改用UNION或UNION ALL
  • 不使用SELECT *
  • 深度分页优化、回表(Using where)、索引覆盖(Using index)、索引下推(Using index condition)、最左匹配原则（遇到范围查询(>、<、between、like)就会停止匹配）
    • 深度分页优化：
      • last_id, 添加where条件的id范围查询，没有页码的应用，如：滑动加载下一页、只有上下页按钮等
      • 子查询：通过子查询查询最小主键或者分页数据的主键，根据最小主键范围查询分页或者join主键查询分页
        • 主要通过子查询，减少回表次数和缩小排序字段数据的大小(提高filesort的效率)
  • 针对索引区分度建⽴联合索 引，尽量保持区分度⾼的在最左
  • 根据执行计划优化
    • Using where：表示优化器需要通过索引回表查询数据
      • 注意rows的数据量，数据量少可以不处理
    • Using index：即覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将待查询字段建立联合索引实现。
      • 覆盖索引高性能的原因是：
        • 索引通常比记录要**小**，覆盖索引查询只需要读索引，而不需要回表
        • 索引都按照值的大小进行**顺序存储，相比与随机访问记录，需要更少的I/0**
        • 大多数数据引擎能更好的缓存索引，例如MyISAM只缓存索引
    • Using index condition：索引下推，是MySQL关于减少回表次数的重大优化。
      • Mysql5.6开始提供的非主键索引查询优化，当根据最左匹配原则查询只有部分字段进行时，Mysql执行器会将停止匹配后的字段也传递给存储引擎，存储引擎在索引上进行查找时也是把停止后的字段进行匹配，从而减少回表次数
      • 如where name like 成% and age = 20, 无优化时是name的范围查询然后server依次回表获取age进行二次过滤，优化后是name范围查询同时匹配age，避免了server层的回表
    • Using filesort：文件排序，这个一般在ORDER BY时候，数据量过大，比较消耗资源
      • 原理：
        • 当**order by 非索引字段**时就会出现filesort
        • 首先查询**所有数据，然后将数据放到每个线程的sort_buffer中，然后使用快排**进行排序
        • 如果数据量过大sort_buffer不够了，这时会使用**磁盘的临时文件，将数据分成多个文件，通过sort_buffer进行每个文件快速排序，然后通过归并排序**合并读个文件
      • 优化：
        • 减少临时文件数量，sort_buffer可存下的行数越多，分文件的数量就越小。通过子查询select id，xx字段减少每行的数据量，然后通过id关联进行回表，比如：select * from (select id, name from user order by name) t right join user u on u.id = t.id
        • 通过**索引覆盖**解决，通过建立联合索引，把order by的字段加入到联合索引中进行索引覆盖，实现排序

mysql的执行流程

mysql—update语句的执行过程

  1. 客户端发送到mysql，首先抵达mysql的server层
  2. 解析器SQL解析，进行词法分析（解析单词）、语法分析（sql语义正确性，生成AST语法树）
  3. 预处理器处理，检查列名表名以及权限判断
  4. 优化器，针对语法树进行优化，并生成执行计划。常见优化：子查询转换为半连接、索引判断
  5. 执行器，根据执行计划调用存储引擎接口执行sql
  6. 执行update1：读数据，从内存页或者磁盘读取数据，执行完目标行在内存中
  7. 执行update2：修改数据，并将修改后的数据行调用引擎接口写入数据
  8. 执行update3：引擎将数据写入内存数据页，然后二阶段提交写入redo log，此时log处于prepare状态
  9. 执行update4：执行器生成binlog，并写入磁盘
  10. 执行update5：执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

mysql的mvcc

  • 多版本的并发控制，能有效的提高数据库的并发访问能力，并在一定程度上解决幻读问题。
  • innodeDB的通过两种读取方式和两个隐藏列实现mvcc，读取方式是快照读和当前读，隐藏列是创建事务ID，删除事务id；
  • 快照读会读取创建事务id比当前事务ID小的并且删除事务id比当前大的数据，即保证当前事务开始前存在且还没删除的数据，一般就是select
  • 当前读则是读表的数据，update，delete，insert，for update，for share mode
  • 能解决幻读是在快照读的时候，因为快照读始终是一样的；但是如果事务中在select之后出现了当前读insert/update，则还是会出现幻读
  • 原因是前面的UPDATE语句执行之后，会将当前记录上存储的事务信息更新为当前的事务，而当前事务所做的任何更新，对本事务所有SELECT查询都变的可见，因此最后输出的结果是UPDATE执行后更新的所有记录。
  • 间隙锁可以解决幻读
  • 表级锁，记录锁，意向锁，间隙锁，net-key锁

ACID原理

原子性

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

实现原理：undo log

持久性

InnoDB提供**内存缓冲（Buffer Pool）**用于加快IO效率，但是也带了新的问题：某些场景下修改的数据还没有刷新到磁盘就会导致数据的丢失，事务的持久性无法保证。通过redo log解决这个问题。

实现原理：redo log

隔离性

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间**的相互影响。**隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

隔离性追求的是并发情形下事务之间互不干扰，主要可以分为：

  • (一个事务)**写操作对(另一个事务)写操作的影响：锁机制**保证隔离性
  • (一个事务)**写操作对(另一个事务)读操作的影响：MVCC**保证隔离性

实现原理：mvcc、锁

隔离级别

脏读：读到了其他事务未提交的数据

不可重复读：同一事务内不同的时刻读到的同一行数据可能是不一样的，即第二次读读到了其他事务提交的最新数据

幻读：同一个事务里面不同的时刻执行两次同样的sql语句，可能导致不同结果的问题，第二次sql语句可能会返回之前不存在的行

  • read uncommited(读未提交)：
    • 实现原理：使用**一级封锁协议，修改先加X锁**直到事务结束时释放
    • 并发问题：脏读、不可重复读、幻读
  • read commited(读已提交)：
    • 实现原理：
      • 当前读：使用**二级封锁协议，在一级锁基础上，读取数据前先加S锁**，读完后立即释放
      • 快照读：MVCC
    • 并发问题：不可重复读、幻读
  • repeatalbe read(可重复读)：
    • 实现原理：
      • 当前读：使用**三级封锁协议，在一级锁基础上，读取数据前先加S锁**，直到事务结束时释放
      • 快照读：MVCC
    • 并发问题：幻读，MySQL 的可重复读隔离级别其实解决了幻读问题
    • Mysql如何解决幻读：
      • 当前读：添加**Next-Key 锁**
      • 快照读：MVCC没有幻读问题
  • serializable(串行事务):
    • 并发问题：效率低性能差

一致性

在其他三个特性的基础上实现一致性

MVCC

MVCC解决的问题是什么？

数据库并发场景有三种，分别为：

  1. 读读：不存在安全问题，也不需要并发控制
  2. 读写：有线程安全问题，可能会造成串务隔离性问题，可能遇到脏读、却读、不可重复读
  3. 写写：有线程安全问题，可能存在更新丢失问题

MVCC是一种用来解决读写冲突的无锁并空制，也就是为事务分配单项噌长的时间，为每个修改保存一个版本，版本与串务时间戥关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：

  • 在并发读写数据库时，可以做到在读操作时不用迴塞写操作，写操作也不用塞读慢作，提高了数据库并发读写的性能
  • 解决读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

MVCC的实现

mvcc的实现原理主要依赖于记录中的**三个隐藏字段，undo log**, **readview(快照)**来实现的。

隐藏字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

  • DB_TRX_ID：6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id
  • DB_ROLL_PTR：7字节，回滚指针，指向undo log中上一个旧版本的数据
  • DB_ROW_ID：6字节，隐藏的主键，如果数据表没有主键，那么innoDB会自动生成一个6字节的row_id

记录如：

nameagegenderDB_ROW_IDDB_TRX_IDDB_ROLL_PTR
zhangsan23男110x12312313

DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID，DB_ROLL_PTR是一个指针，配合undo log指向上一个旧版本

undo log

undog被称之为回滚日志，表示在进行insert,delete,update操作的时候产生的方便回滚的日志。

当进行insert操作的时候，产生的undo log只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃。

当进行update和delete操作的时候，产生的undo log不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit，并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_bit为true, 并且DB_TRX_ID对于purge线程的readview可见，那么这条记录一定时可以被清除的）

快照（read view）

ReadView是事务进行快照**读操作的时候生成的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。最大作用是用作数据的可见性判断。 ReadView遵循的可见性规则主要是将要被修改的数据的最新DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的id去对比，如果不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的当前事务能看到的最新老版本数据**。

快照全局属性：
  • trx_list：快照**生成时刻系统正活跃的事务**ID列表
  • up_limit_id：快照**生成时刻最小个活跃事务ID，即记录trx_list列表中事务ID最小的ID，可以理解为trx_list的下界**
  • low_limit_id: 快照生成时刻系统尚未分配的下一个事务ID，可以粗略理解为**trx_list的上界**
数据读取规则：
  1. 如果DB_TRX_ID < up_limit_id，则当前事务能看到DB_TRX_ID所在的记录，否则下一步
    • 解读：当前数据最后修改的事务，是在当前快照所有活跃事务之前处理的(事务id比trx_list的下界都小)，所以能看得到；
  2. 如果DB_TRX_ID => low_limit_id，则代表DB_TRX_ID所在的记录在快照生成后才出现的，那么对于当前事务肯定不可见，否则进入下一步判断
  • 解读：当前数据最后修改的事务，是在当前快照所有活跃事务之后处理的(事务id比trx_list的上界都大)，所以不能看到
  1. 判断DB_TRX_ID 是否在活跃事务**trx_list中，如果在则代表在快照生成时这个事务活跃还没有commit，则当前事务也是看不到；如果不在，则说明这个事务在快照生成之前就已经commit**，那么修改的结果是能够看见的。
  • 解读：第1、2步已经把上界、下界判断了，这里判断在**trx_list的区间内的；当前数据最后修改的事务不在trx_list中，说明DB_TRX_ID的事务不活跃已经提交，可以看得到；当前数据最后修改的事务在trx_list**中，说明DB_TRX_ID的事务还没提交，不可以看到
  1. 规则总结：其实规则等同于找到**快照生成时数据的最后修改事务提交的数据，如果快照只读取一行数据，则可以通过记录快照生成时最后的事务id进行快照读取；但往往一个快照会读取很多表很多行数据，各行的DB_TRX_ID 差异很大无法统一记录；通过记录快照生成时的活跃事务id，就能知道快照生成时事务的执行情况，在读取时通过计算的方式找到每行数据的快照生成时最后的事务id**，进行快照读；

ReadCommited、RepeatableRead快照读

因为**快照**生成时机的不同，从而造成RC、RR级别下快照读的结果的不同

  1. 在**RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照，将当前系统活跃的其他事务记录起来，在之后的快照读使用的都是同一个快照**，通过这种方式实现可重复读；
  2. 在RC级别下，事务**每次快照读都会新生成一个快照**，这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。

总结：在RC隔离级别下，是每个快照读都会生成并获取最新的快照；而在RR隔离级别下是事务的第一个快照读创建快照，之后的快照读获取的都是同一个快照.