MySQL

事务

• 在MySQL，事务是一组原子性的SQL操作，要么全部成功，要么全不成功

• 基本语法

  • 开启事务：start transcation
  • 事务回滚：rollback
  • 提交事务：commit

• 特点：一致性，原子性，隔离性，持久性

• 并发造成的影响：

  • 读脏数据：在一个事务中读取到另一个尚未提交的事务中的数据（即数据发生变化，但可能提交事务也可能回滚）。如果另一个事务回滚了，那么读取到的数据实际上是无效的，这就导致了读取到脏数据
  • 不可重复读：在一个事务内，多次读取同一数据时，由于其他事务的并发修改，导致读取到的数据不一致。
  • 幻读：多次执行同一个查询，却得到了不同的结果集，特别是在第二次及之后的查询中出现了第一次查询没有返回的行，好像这些行是“幻”影一样突然出现。

• 事务隔离级别

  • 读未提交（Read Uncommitted） ：

    • 允许读取尚未提交的数据变更。
    • 这个隔离级别会导致脏读（Dirty Reads），即一个事务可以读取另一个事务中尚未提交的数据，如果那个事务最终回滚，那么第一个事务读取到的数据就是无效的。

  • 读已提交（Read Committed） ：

    • 只允许读取已经提交的数据变更。
    • 这个隔离级别避免了脏读，但是仍然可能出现不可重复读（Non-Repeatable Reads），即一个事务在读取某些数据后，另一个事务修改了这些数据并提交，导致第一个事务再次读取时数据发生了变化。

  • 可重复读（Repeatable Read） ：

    • 确保在事务执行期间，多次读取同一数据的结果是一致的。
    • 这个隔离级别避免了不可重复读，但是可能出现幻读（Phantom Reads），即一个事务在执行范围查询时，另一个事务插入或删除了符合查询条件的行，导致第一个事务在查询结束时得到不同的结果集。

  • 串行化（Serializable） ：

    • 确保事务可以从数据库中检索到的数据，就好像其他事务不存在一样，完全隔离。
    • 这个隔离级别是最严格的，可以避免脏读、不可重复读和幻读，但是性能开销最大，因为它通常通过锁定事务涉及的所有数据来实现。

索引

• 什么是索引？

  • 索引是帮助MySQL高效获取数据的数据结构（有序）
  • 在无索引的情况下，一条SQL语句执行时默认会进行全表扫描，效率极低，但如果有索引，就会根据索引去查找。

• 优缺点

  • 能够提高检索的效率，降低数据库的IO成本，通过索引列对插入进行排序，降低数据排序的成本，降低CPU的消耗。
  • 但建立索引也需要消耗时间，索引本身也要占用空间。索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert，update，delete

• 索引结构

  • B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
  • Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引的查询才有效，不支持范围查询
  • R-Tree(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少
  • Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES

• 索引分类

  • 主键索引：针对主键创建的索引，只能有一个，默认自动创建
  • 唯一索引：避免同一表中某一列的值重复，可以有多个
  • 常规索引：快速定位特定的数据，可以有多个
  • 全文索引：全文索引查找的是文本中的关键字，而不是比较索引中的值，可以有多个

• 在InnoDB中，根据索引的存储形式，又可以分为以下两种：

  • 聚集索引：索引结构的叶子节点保存了行数据，必须有，而且只有一个
  • 二级索引：索引结构的叶子节点保存了主键，可以存在多个二级索引

• 聚集索引选取规则

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，表中存在UNIQUE约束的索引，且该索引的所有列都被定义为NOT NULL，那么这个索引会成为聚集索引
  • 如果表中既没有主键也没有合适的UNIQUE索引，InnoDB会隐式地为表生成一个聚集索引，这个索引基于一个隐藏的row_id列