MySQL知识点

1 事务

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

1.1 事务的四个特性

事务具有ACID四个特性。

  • Atomicity原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做，不可能只执行一部分操作
  • Consistency一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态，不存在中间状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态
  • Isolation隔离性：一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间互相独立
  • Durability持久性：也称永久性，指一个事务一旦提交，它对数据库中数据的改变是持久的。即使数据库发生故障也不应该对其有任何影响

1.1.1 ACID之间的关系

这几个特性不是一种平级关系

  • 只有满足一致性，事务的执行结果才是正确的
  • 无并发的情况下，事务串行执行，隔离性一定满足。此时只要能满足原子性，就一定能满足一致性
  • 并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
  • 事务满足持久化是为了应对系统崩溃的情况

只有保证了事务的原子性、隔离性之后，一致性才能得到保障。也就是说，A、I是手段，C是目的

图片

1.1.2 Autocommit

MySQL默认采用自动提交模式。即如果不显式使用start transaction语句来开始一个事务，那么每一次SQL操作（非select操作）都会被当做一个事务并自动提交。 如果要手动开启事务需要设置set autocommit=0禁止自动提交事务。

1.2 事务并发带来的问题

  • 脏读（Dirty Reads）：事务A读取了事务B未提交的内容，而事务B后面进行了回滚
  • 不可重复读（虚读/Non-Repeatable Reads）：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据的结果不一致
  • 幻读（Phantom Reads）：事务A先读取了一些数据，接着事务B插入了一些数据，事务A再读取数据时，发现多了一些不知道从哪里来的数据，就像是幻读了一样
  • 丢失修改（Lost Update）：事务A读取一个数据时，事务B也同时读取了，事务A做出修改动作，事务B也做出修改动作，从而事务A的操作被事务B的操作覆盖掉

脏读是一个事务回滚影响另外一个事务，不可重复读侧重于修改和删除，幻读侧重于新增（删除应该不算幻读，幻读应该特指加入行。delete和update可以对记录加锁，保证事务安全。而insert，由于插入行不存在，无法加锁，只能引入间隙锁加MMVC解决，这也是幻读单独拿出来的原因）。幻读可以看作是不可重复读的一种特殊情况，单独区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

1.3 事务的隔离级别

为了解决并发事务的问题，制定了四种隔离级别

图片

  • 读取未提交（Read Uncommitted）：允许读取尚未提交的数据，可能会导致脏读、幻读或不可重复读
  • 读取已提交（Read Committed）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
  • 可重复读（Repeatable Read） ：除非数据是被本身事务自己所修改，否则对同一字段的多次读取结果都是一致的，可以阻止脏读和不可重复读，但幻读仍有可能发生
  • 可串行化（Serializable） ： 所有的事务依次逐个执行，事务之间不会产生干扰，可以防止脏读、不可重复读以及幻读

上述隔离级别，从上到下，依次提高。安全性越来越高，但是锁也越来越多，效率也越来越低。
事务隔离机制基于锁机制和并发调度机制共同实现。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

Serializable隔离级别通过锁实现。除了Serializable，Read Committed和Repeatable Read两个隔离级别都是基于MVCC实现。不过，Serializable之外的其他隔离级别可能也需要用到锁机制，比如Repeatable Read在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL默认隔离级别为Repeatable Read，一般数据库默认隔离级别为Read Committed
InnoDB通过next-key lock解决幻读，保证ACID

1.4 MVCC

根据MySQL的锁机制，写锁和读锁是冲突的，所以MySQl通过MVCC处理加锁后的读写冲突，提高数据库高并发场景下的吞吐性能。最早的数据库系统，只有读读之间可以并发，读写、写读、写写都要阻塞。引入MVCC后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

MVCC只在读取已提交和可重复读两个隔离级别下工作。其他两个隔离级别和MVCC不兼容，因为读取未提交总是读取最新的数据行，而不是符合当前事务版本的数据行，无需使用MVCC；可串行化会对所有读取的行都加锁，单纯使用MVCC无法实现。

通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作时，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别判断读取哪个版本的数据

MVCC的实现依赖于：隐藏字段、Read View、undo log

1.4.1 版本号

  • 系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就自动递增
  • 事务版本号：事务开始时的系统版本号，可以从事务ID判断事务的执行先后顺序

1.4.2 隐藏字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列db_trx_id、db_roll_pointer，如果表中没有主键和非NULL唯一键，则还会有第三个隐藏的主键列db_row_id

  • DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务id。delete操作在内部被视为更新，只不过会在记录头Record header中的deleted_flag字段将其标记为已删除
  • DB_ROLL_PTR（7字节）：回滚指针，指向该行的undo log。如果该行未被更新，则为空
  • DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引，InnoDB会使用该id生成聚簇索引

1.4.3 undo log

undo log主要有两个作用

  • 当事务回滚时用于将数据恢复到修改前的样子
  • 当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过undo log读取之前的版本数据，以此实现非锁定读

InnoDB存储引擎中分为insert undo log 和update undo log两种

  • insert undo log：insert操作中产生的undo log，只在事务回滚时需要。因为insert操作的记录只对事务本身可见，对其他事务不可见，故该undo log可以在事务提交后直接删除。不需要进行purge操作
  • update undo log：update或delete操作中产生的undo log。该 undo log可能需要提供MVCC 机制，因此不能在事务提交时就删除。提交时放入undo log链表，只有当数据库所使用的快照不涉及该日志记录，对应的日志才会被purge线程删除

1.4.4 版本链

不同事务或者相同事务对同一记录行的修改，使该记录行的undo log通过回滚指针（roll_pointer）连成一条链表，这个链表就称为版本链。链首是最新的记录，链尾是最早的旧记录。

eg.假设现在有一张core_user表，表里有一条id为1，名字为孙权的数据

图片

现在开启一个事务A： 对core_user表执行update core_user set name ="曹操" where id=1,会进行如下操作

  • 首先获得一个事务id=100
  • 把core_user表修改前的数据拷贝到undo log
  • 修改core_user表id=1的数据
  • 把修改后的数据事务id=101改成当前事务版本号，并把roll_pointer指向undo log数据地址
    图片

1.4.5 当前读与快照读

  • 快照读
    读取的是记录数据的可见版本（有旧的版本），不加锁，普通的select语句都是快照读。执行select操作时innodb默认会执行快照读，会记录下这次select后的结果，之后的select返回这次快照的数据，即使其他事务提交了也不会影响当前select的数据，这就实现了可重复读。

快照的生成在第一次执行select时，也就是说假设A开启了事务，没有执行任何操作，这时B insert了一条数据然后commit，A再执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

select * from table where ?
  • 当前读
    对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录加锁。假设要update一条记录，但是另一个事务已经delete掉这条数据并且commit了，update就会产生冲突，所以在update时需要知道最新的数据。

以下第一个语句加S锁，其它都加X锁

# 对读的记录加一个S锁
select * from table where ? lock in share mode 
# 对读的记录加一个X锁
select * from table where ? for update
# 对修改的记录加一个X锁
insert
update
delete

1.4.6 Read View

对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的。

核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的

因此，INNODB的设计者提出了Read View的概念，Read View就是事务执行SQL语句时产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。

Read View中包含以下几个参数

  • m_ids：Read View创建时其他活跃(未提交)的事务ID列表。创建Read View时，将当前未提交事务ID记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids不包括当前事务自己和已提交的事务（正在内存中）
  • m_low_limit_id/max_trx_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。由于事务id一般是递增分配的，所以max_trx_id是m_ids中最大的那个id再加上1。大于等于这个ID的数据版本均不可见
  • m_up_limit_id/min_trx_id：未提交事务列表m_ids中最小的事务ID，如果m_ids为空，则 m_up_limit_id=m_low_limit_id。小于这个ID的数据版本均可见
  • m_creator_trx_id：创建该Read View 的事务ID
    图片

1.4.7 数据可见性比较算法

  • 如果db_trx_id<min_trx_id，表明生成该版本的事务在生成Read View前，已经提交，所以该版本可以被当前事务访问
  • 如果db_trx_id>= max_trx_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问
  • 如果min_trx_id=<db_trx_id< max_trx_id，分3种情况讨论
    • 如果m_ids包含db_trx_id,则代表Read View生成时，这个事务还未提交，但是如果db_trx_id等于creator_trx_id则表明数据是自己生成的，因此是可见的
    • 如果m_ids包含db_trx_id，并且db_trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务是看不见的
    • 如果m_ids不包含db_trx_id，则说明这个事务在Read View生成之前就已经提交了，修改的结果当前事务是能看见的
      图片

如果某个版本的数据对当前事务不可见，就顺着版本链找到下一个版本的数据，继续按照上面的规则进行判断，以此类推，若是到了最后一个版本，该版本的数据仍对当前事务不可见，就表明该条记录对该事务完全不可见，查询结果不包含该条记录

1.4.8 RC和RR隔离级别下生成READVIEW时的区别

RC在每一次SELECT语句前都会生成一个ReadView，事务期间会更新，因此在其他事务提交前后所得到的m_ids列表可能发生变化，使得先前不可见的版本后续又突然可见。
而RR只在事务的第一次SELECT语句时生成一个ReadView，事务操作期间不更新。

1.4.9 RR级别下的幻读问题

RR级别下，快照读通过MVVC实现，当前读通过加next-key lock实现。快照读并没有真正避免幻读, 但是当前读避免了不可重复读和幻读。
执行当前读时，锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。

2 MySQL架构

图片

从上图可以看出，MySQL主要由下面几部分构成

  • 连接器：身份认证和权限相关(登录MySQL 时)
  • 查询缓存：执行查询语句时，会先查询缓存（MySQL8.0版本后移除，因为不太实用）
  • 分析器：没有命中缓存的话，SQL语句就会经过分析器，说白了就是先看SQL语句要干什么，再检查SQL语句语法是否正确
  • 优化器：按照MySQL认为最优的方案去执行
  • 执行器：执行语句，然后从存储引擎返回数据。 执行语句前会先判断是否有权限，如果没有权限就会报错
  • 插件式存储引擎：负责数据的存储和读取，采用的是插件式架构，支持InnoDB、MyISAM、Memory等多种存储引擎。不同的存储引擎表的数据存取方式不同，支持的功能也不同

大体来说，MySQL分为Server层和存储引擎两部分。不同的存储引擎共用一个Server层

  • Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖了MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图
  • 存储引擎层负责数据的存储和提取。MySQL 5.5.5前，MyISAM是默认存储引擎。5.5.5版本后，InnoDB是默认存储引擎。在建表时可以通过engine=xxx指定存储引擎

3 MySQL索引

索引是一种用于快速查询和检索数据的有序的数据结构。常见的索引结构有B树，B+树和Hash。索引的作用就相当于书的目录。

图片

3.1 索引的优缺点

  • 优点
    • 使用索引可以大大加快数据的检索速度（减少检索的数据量）, 这也是创建索引的最主要原因
    • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  • 缺点
    • 创建和维护索引需要耗费许多时间。当对表中的数据进行增删改时，如果数据有索引，那么索引也需动态的修改，从而降低SQL执行效率
    • 索引需要使用物理文件存储，耗费一定空间

大多数情况下索引查询比全表扫描要快。但是如果数据库的数据量不大，那么使用索引也不一定能带来很大提升

3.2 索引的分类

按照存储结构划分：BTree索引（B-Tree或B+Tree），Hash索引，full-index全文索引，R-Tree索引
按照应用层次划分：主键索引，二级索引(辅助索引)，其中二级索引包括普通索引，唯一索引，复合索引，前缀索引等索引
按照数据的物理顺序与键值的逻辑顺序划分：聚集索引，非聚集索引

  • 普通索引：一个索引只包含单个列，允许数据重复和NULL，一个表可以有多个普通索引，普通索引的唯一作用是快速查询数据
  • 唯一索引：索引列的值必须唯一，但允许有空值，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分是为了该属性列的数据的唯一性，而不是为了查询效率
  • 复合索引/联合索引/组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  • 主键索引：数据表的主键列使用的就是主键索引。一张数据表只能有一个主键，并且主键不能为null，不能重复。在MySQL的InnoDB引擎中，当没有显式的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认主键，否则将自动创建一个6Byte的自增主键
  • 前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。只适用于字符串类型的数据
  • 聚簇索引(聚集索引)：索引结构和数据一起存放的索引，主键索引属于聚集索引。具体细节取决于不同的实现，InnoDB的聚簇索引是在同一个结构中保存了B+Tree索引和数据行记录
    • 优点
      • 聚集索引的查询速度非常快，因为整个B+树本身就是一颗多叉平衡树，叶子节点都是有序的，定位到索引的节点，就相当于定位到了数据
    • 缺点
      • 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时进行排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢
      • 更新代价大：如果索引列的数据被修改，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可修改的
  • 非聚簇索引：索引结构和数据分开存放的索引。二级索引属于非聚集索引。非聚集索引的叶子节点不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查询数据
    • 优点
      • 更新代价比聚集索引小。因为非聚集索引的叶子节点是不存放数据的
    • 缺点
      • 跟聚集索引一样，非聚集索引也依赖于有序的数据
      • 可能会二次查询(回表)：当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

主索引和唯一索引的区别
主索引不能重复且不能为空，唯一索引不能重复但可以为空；
一张表只能有一个主索引，但可以有多个唯一索引；
主索引的查询性能要高于唯一索引

3.2.1 联合索引的最左前缀匹配原则

使用联合索引时，MySQL会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或在执行过程中遇到范围查询，如>、<、between和以%开头的like查询等条件，才会停止匹配。在使用联合索引时，可以将区分度高的字段放在最左边，这可以过滤更多数据
eg.联合索引(a,b,c)相当于建立了(a), (a,b),(a,b,c)三个索引，MySQL组装索引树时按照从左到右的顺序建立B+Tree的联合索引树

匹配索引情况一
假设（a,b,c）索引要搜索的值为('张三', 21, 100) ，检索数据时匹配的顺序就是a,b,c。

B+Tree会优先比较a来确定下一步的搜索方向，如果a相同再依次比较b和c，最后得到检索数据。

匹配索引情况二
假设（a,c）索引要搜索的值为('张三', 100) ，检索数据时匹配的顺序就是a,b,c。

B+Tree使用a来指定搜索方向，但下一个字段b缺失，所以只能把a等于张三的数据都找到，然后再匹配c是100的数据。

匹配索引情况三
假设（b,c）索引要搜索的值为('张三', 21) ，检索数据时无匹配顺序。

B+Tree不知道下一步该查哪个节点，因为建立搜索树时a是第一个比较因子，必须要先根据a来搜索才能知道下一步去哪里查询。此时索引失效！

3.2.2 索引下推

索引下推是MySQL 5.6版本中提供的一项索引优化功能，在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数，通俗说，减少查询主键索引树次数，减少磁盘IO
eg.建立联合索引idx_age_weight

select * from user where age = 11 and weight = 98

之前搜索过程是
在idx_age_weight索引树中匹配出所有age=11的索引，拿到主键id，回表去一条条再比对weight字段。如下图，需要进行3次回表搜索操作

图片

之后的搜索过程是
在idx_age_weight索引树中匹配出所有age=11的索引，顺便对weight字段进行判断，过滤weight=100的记录，然后再进行回表搜索。如下图，只需进行2次回表搜索操作

图片

3.2.3 覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，直接根据该索引就可以查到数据，而无需回表查询。
如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键
再如普通索引，如果一条SQL需要查询name，name字段正好有索引，那么直接根据这个索引就可以查到数据，无需回表
eg.一张用户表，id为主键，age为普通索引

CREATE TABLE user (
  id int(11) PRIMARY KEY,
  
  name varchar(255) DEFAULT NULL,
  
  age int(11) DEFAULT NULL,
  
  KEY idx_age (age) USING BTREE,
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from user where age=22不是覆盖索引

select id,age from user where age=22是覆盖索引，查询的id、age都可以直接在idx_age索引树中获取，不需要回表查询

3.3 索引的底层实现

3.3.1 Hash表

哈希表是键值对的集合，通过键(key)可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近O(1)）。其具体实现为：通过哈希算法（散列算法）快速找到key对应的index，找到了index也就找到了对应的value

hash = hashfunc(key)
index = hash % array_size

但是哈希算法有Hash冲突问题，即多个不同的key最后得到的index相同。常用的解决办法是链地址法（将哈希冲突数据放在链表中）。JDK1.8之前HashMap就是通过链地址法解决哈希冲突。JDK1.8之后HashMap为了减少链表过长造成的搜索时间过长引入了红黑树

为什么MySQL没有使用哈希表作为索引的数据结构
1.Hash冲突问题
2.Hash索引不支持排序和范围查询（这是哈希表的最大缺点）

3.3.2 B树&B+树

B树也称B-树，全称为多路平衡查找树，B+树是B树的一种变体。B树和B+树中的B是Balanced （平衡）的意思。

图片
图片

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

3.3.2.1B树&B+树的异

  • B树的所有节点既存放键(key)也存放数据(data)，而B+树只有叶子节点存放key和data，其他节点只存放key
  • B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点
  • B树的检索过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就比较稳定，任何查找都是从根节点到叶子节点，叶子节点的顺序检索很明显

3.3.2.2 MySQL中索引的实现

在MySQL中，MyISAM引擎和InnoDB引擎都是使用B+Tree作为索引结构，但是两者的实现方式不同。
假设有一张学生表，id为主键

图片

  • MyISAM引擎中，B+Tree叶节点的data域存放的是数据记录的地址。在索引检索时，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”
    图片

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

图片

3.4 索引失效

下面的情况会造成索引失效

  • like通配符，左侧开放情况下，全表扫描
    • select * from t_user where user_name like '%abc%'和select * from t_user where user_name like '%abc' 不走索引，select * from t_user where user_name like 'abc%'走索引
    • 如果要让like '%abc'也走索引，可以使用REVERSE()函数创建一个函数索引即select * from t_user where reverse(user_name) like reverse('%abc')
  • or条件筛选，可能会导致索引失效
    • select * from t_user where num=10 or num=20不走索引，select * from t_user where num=10 union select * from t_user where num=20走索引
  • where中对索引列使用mysql的内置函数，一定失效
  • where中对索引列进行运算，如+、-、*、/，一定失效
    • select * from t where f/2=100不走索引，select * from t where f=100*2走索引
  • 类型不一致，隐式的类型转换，导致索引失效
  • where语句中索引列使用了负向查询，可能会导致索引失效
    • 负向查询包括NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等。!<、!>为SQL Server语法
  • 索引字段可以为null，使用is null或is not null时，可能会导致索引失效
  • 隐式字符编码转换导致的索引失效
  • 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效
  • MySQL优化器的最终选择，不走索引

3.5 MySQL语句创建索引

  • 添加PRIMARY KEY(主键索引）
    ALTER TABLE table_name ADD PRIMARY KEY (column(length))

length可以省略不写

  • 添加UNIQUE(唯一索引)
    ALTER TABLE table_name ADD UNIQUE (column(length))

  • 添加INDEX(普通索引)
    ALTER TABLE table_name ADD INDEX index_name (column(length))
    CREATE INDEX index_name ON table_name(column(length))

  • 添加多列索引
    ALTER TABLE table_name ADD INDEX index_name (column1(length), column2(length), column3(length))
    CREATE INDEX index_name ON table_name(column1(length), column2(length), column3(length))

4 MySQL锁

按照类别可以把锁分为共享锁（读锁/S锁）和排他锁（写锁/X锁）。

按照粒度可以把锁分为行级锁（row-level locking）、表级锁（table-level locking）和页级锁（全局锁）。其中InnoDB支持表级锁和行级锁，默认为行级锁；MyISAM只支持表级锁。

4.1 共享锁和排他锁

  • 共享锁：事务在读取记录时获取的锁，允许多个事务同时获取（锁兼容）。如果其他事务对共享锁进行修改操作即加排他锁，可能会造成死锁

  • 排他锁：事务在修改记录时获取的锁，不允许多个事务同时获取（锁不兼容）。如果一个记录已经被加了排他锁，那在此事务结束之前，其他事务不能再对这条事务加任何类型的锁。排它锁是悲观锁的一种实现

    S锁 X锁
    S锁 不冲突 冲突
    X锁 冲突 冲突

排他锁与任何锁都不兼容，共享锁仅和共享锁兼容

4.2 表级锁、行级锁、页级锁

  • 表级锁：MySQL中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗较少，加锁快，不会出现死锁。其锁定粒度最大，并发度最低，触发锁冲突的概率最高
  • 行级锁：MySQL中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁，所以加锁的开销也最大，加锁慢，会出现死锁。其锁定粒度最小，并发度高，能大大减少数据库操作的冲突
    • 记录锁（Record Lock）：锁定一个记录上的索引，而不是记录本身(锁加在索引上而不是行上)
    • 间隙锁（Gap Lock）：锁定索引之间的间隙，但是不包含索引本身。其目的是防止其他事务插入数据。隔离级别比Read Committed低的不会使用间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁
    • 临键锁（Next-key Lock）：Record Lock+Gap Lock，锁定一个记录上的索引，也锁定索引之间的间隙。比如一个索引包含值10，11，13和20。那么临键锁的范围如下

      (negative infinity, 10]
      (10, 11]
      (11, 13]
      (13, 20]
      (20, positive infinity)

  • 页级锁：MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。页级进行了折中，一次锁定相邻的一组记录。BDB支持页级锁。开销和加锁时间界于表锁和行锁之间，会出现死锁。并发度一般

    InnoDB的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行UPDATE、DELETE语句时，如果WHERE条件中字段没有命中唯一索引或者索引失效，就会导致扫描全表并对表中的所有行记录进行加锁。所以，有时即使用了索引也有可能会全表扫描，这是因为MySQL优化器

4.3 意向锁

用于快速判断在不知道表内是否有行锁的情况下，是否可以对该表使用表锁即判断是否有锁冲突。意向锁由存储引擎维护，用户无法手动操作意向锁

4.3.1 意向共享锁和意向排他锁

意向锁是表级锁，共有两种：

  • 意向共享锁（IS锁）：一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁
  • 意向排他锁（IX锁）：一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁

意向锁之间互相兼容

IS锁 IX锁
IS锁 兼容 兼容
IX锁 兼容 兼容

意向锁和共享锁和排它锁互斥（这里指的是表级的共享锁和排他锁，意向锁不与行级的共享锁和排他锁互斥）

IS锁 IX锁
S锁 兼容 互斥
X锁 互斥 互斥

5 MySQL存储引擎

存储引擎是基于表的，而不是数据库

可以通过show engines命令查看MySQL支持的所有存储引擎，通过show variables like '%storage_engine%'命令直接查看MySQL当前默认的存储引擎，使用show table status from db_name where name='table_name'命令查看数据库中某个表使用的存储引擎

图片

图片

图片

所有的存储引擎中只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事务。

5.1 MyISAM和InnoDB的区别

  • 是否支持行级锁：MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁
  • 是否支持事务：MyISAM不提供事务支持。InnoDB提供事务支持，实现了SQL标准定义的四个隔离级别，具有提交和回滚事务的能力
  • 是否支持外键：MyISAM不支持，InnoDB支持
    • 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此一般不建议在数据库层面使用外键，应用层面可以解决。
  • 是否支持数据库异常崩溃后的安全恢复：MyISAM不支持，InnoDB支持。使用InnoDB的数据库在异常崩溃后，数据库重新启动时会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log
  • 是否支持MVCC：MyISAM不支持，InnoDB支持。MVCC可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能
  • 索引实现不一样：虽然MyISAM和InnoDB引擎都使用B+Tree作为索引结构，但是两者的实现方式不太一样。InnoDB引擎中，其数据文件本身就是索引文件。而MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录

6 MySQL日志

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志（redo log/重做日志）、二进制日志（binlog/归档日志）、回滚日志（undo log）几大类。

图片

redo log保证事务的持久性，undo log保证事务的原子性，binlog保证数据一致性

6.1 重做日志redo log

InnoDB存储引擎独有，它让MySQL拥有了崩溃恢复能力

图片

MySQL中数据以页为单位，查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，放入Buffer Pool中。后续的查询都是先从 Buffer Pool中找，没有命中再去硬盘加载，减少硬盘IO开销，提升性能。更新表数据时也是如此，发现Buffer Pool里存在要更新的数据，就直接在Buffer Pool里更新。

图片

然后把“在某个数据页上做了什么修改”记录到重做日志缓存里，接着刷盘到redo log文件里

6.2 归档日志binlog

binlog记录所有涉及更新数据的逻辑操作，并且是顺序写。MySQL的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

不记录SELECT、SHOW等不修改数据的SQL语句

图片

binlog与redo log的不同

  • redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎
  • binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2这一行的c字段加1”，属于MySQL Server 层。不管用什么存储引擎，只要发生了表数据更新，都会产生binlog日志

6.3 回滚日志undo log

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚。MySQL恢复机制通过回滚日志实现，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

回滚日志会先于数据持久化到磁盘上。这样保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库时，数据库还能够通过查询回滚日志来回滚之前未完成的事务。

7 常见面试题

7.1 varchar和char的区别

  • char是一个定长字段，假如申请了char(10)的空间，那么无论实际存储多少内容，该字段都占用10个字符
  • varchar是变长的，也就是说申请的只是最大长度，占用的空间为实际字段长度+1，最后一个字符用来记录该字段的长度
  • 从检索效率上讲，char>varchar，因此如果确定某个字段的长度就使用char，否则应该尽量使用varchar。例如存储用户MD5加密后的密码，就应该使用char

7.2 varchar(10)和int(10)代表什么含义

varchar的10代表了申请的空间长度，即可以存储的数据的最大长度，而int的10只是代表了展示的长度，不足10位以0填充。

int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示

7.3 varchar(100)和varchar(200)的区别

占用内存空间大小是不一致的，但是占用磁盘的大小是一致的。存储字符串"abc"，完全是一样的磁盘空间，但是对于varchar(100)来说，接收到的字符串长度太长了就会报错

7.4 drop、delete与truncate的区别

用法不同

  • drop(丢弃数据)：drop table 表名，直接将表删除，删除表时使用
  • truncate(清空数据)：truncate table 表名，只删除表中的数据，再插入数据时自增长id从1开始，清空表中数据时使用
  • delete(删除数据)：delete from 表名 where 列名=值，删除某一行的数据，如果不加where子句和truncate table 表名作用类似

truncate和不带where子句的delete、以及drop都会删除表内的数据，但truncate和delete只删除数据不删除表的结构(定义)，而drop会删除表的结构即执行drop后对应的表不复存在

属于不同的数据库语言

truncate和drop属于DDL语句，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。而delete语句是DML语句，操作会放到rollback segement中，事务提交之后才生效

  • DML是数据库操作语言（Data Manipulation Language）的缩写。是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作
  • DDL（Data Definition Language）是数据定义语言的缩写。是对数据库内部的对象进行创建、删除、修改的操作语言，它和DML语言的最大区别是DML只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL语句更多被数据库管理员使用，一般的开发人员很少使用

由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言DQL（Data Query Language）

执行速度不同

一般来说drop>truncate>delete

delete命令执行时会产生binlog日志，而日志记录是需要消耗时间的，但是也方便数据回滚恢复
truncate命令执行时不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等
drop命令会把表占用的空间全部释放

7.5 binlog日志的录入格式

binlog日志有三种格式，可以通过binlog_format参数指定

  • statement：记录的内容是SQL语句原文。由于sql的执行是有上下文的，因此有一些使用了函数之类的语句无法被记录复制

    • 比如执行一条update T set update_time=now() where id=1，记录的内容如下
      图片

  • row：记录的内容不仅是简单的SQL语句，还包含操作的具体数据，从而保证了同步数据的一致性。但是这种格式需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度
    图片

  • mixed：一种折中的方案，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是就用row格式，否则就用statement格式

7.6 MySQL执行一条查询语句的内部执行过程

  • 客户端通过TCP连接发送连接请求到mysql连接器，连接器对该请求进行权限验证及连接资源分配
  • 连接器权限验证通过后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有则进入分析器
  • 分析器对查询语句进行语法分析和词法分析（检查数据表和数据列是否存在，解析别名看是否存在歧义），判断SQL语法是否正确，如果查询语法错误则直接返回给客户端错误信息，如果语法正确则进入优化器
  • 优化器对查询语句进行优化处理，生成执行计划。例如一个表里面有多个索引，优化器会判别哪个索引性能更好
  • 执行器执行语句进行查询比对，将满足条件的数据保存到结果集中，同时逐步将数据缓存到查询缓存中，直到查询到满足条件的所有数据，最终将结果集返回给客户端
    图片

7.7 limit 3,2的含义

去除前三条数据之后查询两条信息

7.8 now()和current_date()的区别

now()返回当前时间包含日期和时分秒，current_date()只返回当前时间

图片

7.9 一张表，ID自增主键，insert了17条记录后，删除了第15,16,17条记录后重启Mysql，再insert一条记录，这条记录的ID是多少？

如果表的引擎类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL 自增主键的最大ID也不会丢失

如果表的引擎类型是InnoDB，那么是15。InnoDB表只是把自增主键的最大 ID记录到内存中，所以重启数据库或者对表进行OPTIMIZE操作，都会导致最大ID丢失

这仅仅是MySQL 8以前的版本，也就是MySQL 5.7及之前的版本
在MySQL 8.0中，InnoDB的行为已更改，使得结果与MyISAM一样
如果删除的不是最后的记录，那么自增的最大ID一样

7.10 MySQL查询是否区分大小写

不区分。

下面查询结果一样

SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;

7.11 为什么要使用not null

MySQL中not null其实占空间，空值才不占空间，但这不是禁止使用null的重点。

  • 所有使用NULL值的情况，都可以通过一个有意义的值来表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性
  • NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能
  • NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp
  • NOT IN、!=等负向条件查询在有 NULL值的情况下返回永远为空结果，查询容易出错

7.12 哪些情况需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段
  • JOIN关联查询，作为外键关系的列建立索引
  • 单键/组合索引的选择问题，高并发下倾向创建组合索引，创建时遵循最左前缀匹配原则
  • ORDER BY查询中排序的字段，排序字段通过索引访问大幅提高排序速度
  • GROUP BY需要分组字段或查询中统计字段

7.13 哪些情况不要创建索引

  • 表数据量不大
  • 经常增删改的表
  • 数据重复且分布均匀的表字段
  • 频繁更新的字段（会加重IO负担）
  • where条件里用不到的字段不创建索引

7.14 InnoDB为什么要使用B+树，而不是B树、Hash、红黑树或二叉树

  • B树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低
  • Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高
  • 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高
  • 红黑树：树的高度随着数据量增加而增加，IO 代价高

7.15 Hash索引和B+树的区别

  • hash索引无法进行范围查询。因为经过hash函数建立索引后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循左节点小于父节点，右节点大于父节点的规则，天然支持范围查询
  • hash索引不支持使用索引进行排序，原理同上
  • hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测，AAAA和AAAAB的索引没有相关性
  • hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)时可以只通过索引完成查询
  • hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复时，发生hash碰撞，此时效率可能极差，而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低

7.16 大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

  • 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如当用户在查询订单历史时，可以控制在一个月的范围内
  • 读/写分离：经典的数据库拆分方案，主库负责写，从库负责读
  • 缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存
  • 垂直分区：根据数据库里数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库

    • 简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示图片

    • 垂直拆分的优点：使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护

    • 垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂

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

    • 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响图片

    • 水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库

    • 水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨界点Join性能较差，逻辑复杂。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O

posted @ 2022-08-03 16:59  ycylikestuty  阅读(129)  评论(0)  编辑  收藏  举报