MySQL学习笔记【待更新】

反卷但卷
50 阅读9分钟

MySQL

字段类型

  1. VARCHAR(100)和CHAR(10)存储相同字符时，占用磁盘空间相同，但是当加载到内存时，VARCHAR(100)会占用100空间，CHAR(10)只会占用10的空间。
  2. BLOB和TEXT字段的缺点
    • 没有默认值
    • 使用临时表会磁盘上创建临时表而不是内存中
    • 检索效率低
    • 不能直接创建索引
  3. NULL和''的区别
    1. NULL是空又不是空，某种含义上表示一个不确定的值，NULL与其他任何值运算的结果都是NULL【SELECT NULL or TRUE的结果为1】，DISTINCT、GROUP BY、ORDER BY将NULL视为同一个值并不是认为NULL= NULL。COUNT(*)统计包括NULL，COUNT(列名)统计不包括NULL。
    2. ''是一个已知确定的值，与'xx','yyyy'相同，但是在SUM聚合函数中，会将''视为0。

存储引擎

  1. MySQL支持的存储引擎有很多，MyISAM、INNODB、Memory等等。
  2. MySQL的存储引擎是插件式的，表级的
  3. MyISAM和INNODB的区别
    1. 锁
      • MyISAM只支持表级锁
      • INNODB支持行级锁和表级锁，粒度更细，性能也就更高
    2. 事务
      • MyISAM不支持事务
      • INNODB支持事务，且有四种隔离级别：读未提交、读已提交、可重复读、串行化
    3. 外键
      • MyISAM不支持
      • INNODB支持，可以维护事务一致性，但有性能损失，不建议使用
    4. MVCC
      • MyISAM不支持
      • INNODB支持，多版本控制机制减少了锁，提高了性能
    5. 索引
      • MyISAM是B+树结构存储索引，但是数据与索引结构分离，非聚簇索引，B+树叶节点存储的数据的地址
      • INNODB是B+树结构存储索引，数据与索引不分离，聚簇索引，B+树叶节点中存储的是完整的数据
    6. 异常崩溃后的安全恢复
      • MyISAM不支持
      • INNODB支持，基于redo log + bin log实现，主要是redo log

索引

  1. 优缺点

    • 优点：提高检索速度，减少IO次数（索引只存储部分字段或一个字段的数据，所以一个页能存储更多的记录）
    • 缺点：创建和维护需要耗费性能，当对索引相关字段进行增删改时，需要同时维护聚簇索引和其他涉及的索引

  2. 多种数据结构作为索引底层数据结构的区别

    1. 哈希表、二叉查找树、二叉平衡树、红黑树、B树、B+树
      1. 哈希表虽然查单个记录非常快，但是在需要进行范围查询的场景下就不行了
      2. 二叉查找树在极端情况下会退化为链表，性能及其不稳定
      3. 二叉平衡树会频繁的旋转节点，需要进行多次磁盘IO，性能较低
      4. 红黑树只是追求一个大致的平衡，虽然一次最多旋转一次，但是树的高度并不稳定，且可能会较高
      5. B树，每个节点中都存储数据，一页中可以存储的节点数较少。查询所需访问的节点路径深度不稳定，即查询效率不稳定。范围查询需要中序遍历。
      6. B+树只有叶节点存储数据，每次查询都会到叶节点读取数据，效率稳定，相邻叶子节点之间链接成链表，范围查询效率高。

  3. 索引类型：主键索引、普通索引、唯一索引、覆盖索引、联合索引、全文索引、前缀索引

  4. 降序索引：InnoDB从MySQL 8.0开始原生支持降序索引，早期版本可能通过反向扫描升序索引实现，效率较低。

  5. 聚簇索引与非聚簇索引

    • 聚簇索引查询不需要回表，查到数据就可以返回，非聚簇索引如果不是覆盖索引的话需要回表查完整数据
    • 聚簇索引与非聚簇索引都依赖有序数据，如果索引列无序，比如是UUID这种，在插入时可能会导致页分裂这种耗时IO
    • 聚簇索引更新代价大，非聚簇索引只有部分列和主键列，更新代价相对较小

  6. 联合索引

    • 最左前缀匹配原则，当遇到<,>这种范围查询时，会停止匹配，当遇到<=,>=,Between以及like前缀匹配时会继续匹配，因为他们都有等于的条件在内。

  7. 索引下推

    • 在联合索引中，根据能匹配上的最左前缀进行筛选，并对匹配不上，比如因使用函数计算而失效的字段进行比较筛选
    • 减少回表次数

  8. 使用索引

    • 字段不为NULL
    • 应该在被频繁查询的字段上创建索引，尤其是被作为条件查询的字段
    • 频繁需要排序的字段
    • 频繁用于表连接的字段
    • 被频繁更新的字段需要慎重创建索引，除非该字段被查询的频率更高且需要提高查询效率
    • 每张表上的索引数不宜太多，毕竟索引的维护需要成本的，并且会增加优化器执行时间，一定程度上影响查询效率
    • 尽可能建立联合索引，比如当有查询设计到a作为条件,a,b作为条件，a,b,c作为条件时，应该建立a,b,c联合索引
    • 避免冗余索引，比如当有联合索引a,b,c和a,b及单列索引a时，a,b和a就是冗余索引，因为命中后两个必定能命中a,b,c

  9. 避免索引失效

    • 未按照最左前缀匹配原则使用联合索引

    • 在索引列上进行计算、函数运算、类型转换等操作

    • 以%开头的LIKE查询

    • OR条件前后字段只要一个字段无索引就都无法使用索引

    • IN的取值范围较大时会导致索引失效

    • 隐式转换

      • 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp;使用SQL测试了一下，似乎并没有因为隐式转换而无法使用索引，推测是因为转换的结果是确定的？隐式转换导致索引失效的本质是因为转换后的结果不确定了吗，相当于进行了运算。

        • -- 测试查询 1：使用字符串格式日期（隐式转换）
EXPLAIN SELECT
	* 
FROM
	test_table 
WHERE
	create_time = '2025-04-11 15:30:00';
	
-- 测试查询 2：使用正确格式时间戳（无转换）
EXPLAIN SELECT
	* 
FROM
	test_table 
WHERE
	create_time = TIMESTAMP ( '2025-04-11 15:30:00' );

      • 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较

      • 两个参数转换成浮点数后运算，当表字段列从字符串转换为浮点数时，会由于转换结果不确定，导致无法使用索引

事务

  1. AIDC：AID保证C
    • A(Atomicity，原子性)：一个事务中的操作要么都起作用，要么都不起作用，通过Undo Log实现回滚
    • I(Isolation，隔离性)：多事务并发时不同事务之间互不影响，通过锁和MVCC实现
    • D(Durability，持久性)：一个事务完成后，对数据的影响是持久的，即数据库异常也不应该对其有影响，如丢失。通过Redo Log和Bin Log保证事务提交后数据持久化
    • C(Consistency，一致性)：数据在事务执行前和后保持一致性，比如在事务中完成了两个用户的交易，那么在事务完成后或异常后，两个用户的账户余额总和不变。
  2. 并发事务可能存在的问题
    • 脏读
      • 现象：事务A读取事务B未提交的修改，随后事务B回滚，导致事务A读取到无效数据。
      • 解决方案：需将隔离级别设置为读已提交（Read Committed）及以上。InnoDB在读已提交级别下通过多版本并发控制（MVCC）避免脏读。
    • 第一类丢失更新（回滚覆盖）
      • 现象：事务A修改数据后回滚，覆盖事务B已提交的更新。
      • 解决方案：通过行级锁或读已提交及以上隔离级别解决。
    • 第二类丢失更新（提交覆盖）
      • 现象：事务A和事务B同时读取并修改同一数据，后提交的事务覆盖先提交的事务结果。
      • 解决方案：需使用显式锁（如SELECT FOR UPDATE）或可重复读（Repeatable Read）及以上隔离级别，结合乐观锁机制（如版本号）。
    • 不可重复读
      • 现象：事务A多次读取同一数据，期间事务B提交了更新操作，导致事务A两次读取结果不一致。
      • 解决方案：需将隔离级别提升至可重复读（Repeatable Read）及以上。InnoDB在该级别下通过MVCC保证事务内读取数据的一致性。
    • 幻读
      • 现象：事务A基于相同条件多次查询，期间事务B插入或删除了符合条件的数据，导致事务A两次统计的数据量不一致。
      • 解决方案：在可重复读（Repeatable Read）隔离级别下通过**Next-Key Lock（Record Lock 记录锁,Gap Lock 间隙锁）**机制防止幻读，无需强制使用串行化（Serializable）隔离级别。
  3. 事务隔离级别与锁机制的关系
    • 读未提交（Read Uncommitted）：无锁，存在脏读、不可重复读、幻读。
    • 读已提交（Read Committed）：通过MVCC避免脏读，但可能出现不可重复读和幻读。
    • 可重复读（Repeatable Read）：默认级别，通过MVCC和Next-Key锁避免脏读、不可重复读及大部分幻读。
    • 串行化（Serializable）：通过表级锁彻底解决所有并发问题，但牺牲并发性能。
avatar
文章
阅读
粉丝