MySQL 笔记

一.逻辑架构

MySQL 架构中的三层服务

第一层是服务器层，主要提供连接处理、授权认证、安全等功能。

第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等内置函数。

第三层是存储引擎层，负责数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应服务器请求。

Mysql的执行的流程

MySQL 中的引擎

InnoDB、MyISAM 、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、Archive、CSV、Blackhole、MaxDB

MyISAM和InnoDB的区别

特性	MyISAM	InnoDB
事务支持	不支持	支持（ACID 事务）
外键支持	不支持	支持
行级锁	不支持（仅表级锁）	支持
崩溃恢复	不支持（数据易丢失）	支持（通过日志恢复）
全文索引	支持	支持（MySQL 5.6+）
存储结构	表分为 .MYD（数据）和 .MYI（索引）文件	表数据和索引存储在 .ibd 文件中
并发性能	较差（表级锁）	较好（行级锁）
压缩表	支持	不支持
热备份	不支持	支持（通过工具如 mysqldump 或 Percona XtraBackup）
适用场景	读密集型应用（如日志、报表）	写密集型应用（如电商、金融）
默认引擎	MySQL 5.5 之前默认	MySQL 5.5 之后默认
数据一致性	较弱	强（支持事务和外键）
存储空间占用	较小	较大（支持事务和行级锁）
插入性能	较快（无事务开销）	较慢（事务和行级锁开销）

---

Innodb引擎的4大特性

• 插入缓冲（insert buffer)
• 二次写 (double write)
• 自适应哈希索引 (ahi)
• 预读 (read ahead)

Innodb使用的是哪种隔离级别?

InnoDB默认使用的是可重复读隔离级别.

二. 事务

事务的意思是一条或者是一组语句组成一个单元，这个单元要么全部执行，要么全不执行。

事务的特性（ACID）

原子性： 事务是最小的执行单位，不允许分割。事务内的语句要么全部执行成功，要么全部执行失败。
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

---

脏读、幻读、不可重复读

在多个事务并发操作时，数据库中会出现下面三种问题：脏读，幻读，不可重复读。

脏读（Dirty Read）

事务A读到了事务B还未提交的数据：

幻读（Phantom Read）

事务A进行范围查询时，事务B中新增了满足该范围条件的记录，当事务A再次按该条件进行范围查询，会查到在事务B中提交的新的满足条件的记录（幻行 Phantom Row）。

不可重复读（Unrepeatable Read）

事务A在读取某些数据后，再次读取该数据，发现读出的该数据已经在事务B中发生了变更或删除。

幻读和不可重复度的区别：

• 幻读：在同一事务中，相同条件下，两次查询出来的 记录数 不一样；
• 不可重复读：在同一事务中，相同条件下，两次查询出来的 数据 不一样；

---

事务隔离级别

• 未提交读(READ UNCOMMITTED)

  这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

  这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

• 已提交读(READ COMMITTED)

  其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

• 可重复读(REPEATABLE READ)

  可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读.

  当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时另外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥

  那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

• 可串行化(SERIALIZABLE)

  这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

---

MVCC 原理

MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

基本特征

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时Copy出当前版本，随意修改，各个事务之间无干扰。
• 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：
​ 1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
​ 2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

---

MVCC 解决了哪些问题?

MVCC（Multi-Version Concurrency Control，多版本并发控制）是MySQL中用于实现并发控制的一种技术，主要解决了以下几个方面的问题：

• 读写冲突问题

  • 在传统的数据库并发控制中，读操作和写操作可能会相互阻塞。例如，当一个事务正在对某条数据进行写操作时，其他事务对该数据的读操作可能需要等待写操作完成，反之亦然。MVCC通过为数据的每个版本都保存一个时间戳或版本号，使得读操作可以直接读取数据的某个旧版本，而不需要等待写操作完成，从而避免了读写之间的阻塞，提高了并发性能。
  • 举例来说，在一个电商系统中，当一个用户在查看商品信息（读操作）的同时，另一个用户可能正在对该商品的库存进行修改（写操作）。使用MVCC，查看商品信息的用户可以直接读取商品信息的旧版本，而不会被库存修改操作阻塞，提高了系统的响应速度和用户体验。

• 脏读问题

  • 脏读是指一个事务读取到了另一个未提交事务修改的数据。MVCC通过在每个数据版本上记录事务的提交状态，使得读取操作只能读取到已经提交的数据版本，从而避免了脏读的发生。
  • 例如，在一个银行转账系统中，用户A向用户B转账100元，在转账事务未提交之前，用户B查询自己的账户余额，如果没有MVCC机制，用户B可能会读取到一个临时的、未提交的余额变化，即脏数据。而有了MVCC，用户B只能读取到转账事务提交之前的账户余额，避免了脏读问题。

• 不可重复读问题

  • 不可重复读是指在一个事务内，多次读取同一数据时，得到的结果不一致。这是因为在事务执行过程中，其他事务可能对该数据进行了修改并提交。MVCC通过为每个事务提供一个一致性的视图，使得在同一个事务内，无论何时读取数据，都能得到相同的结果，从而解决了不可重复读问题。
  • 例如，在一个订单处理系统中，一个事务需要多次读取某个订单的状态。在没有MVCC的情况下，如果在事务执行过程中，其他事务修改了订单状态并提交，那么该事务两次读取到的订单状态可能不同。而使用MVCC，该事务在整个执行过程中都能看到订单状态的同一个版本，保证了数据的一致性。

• 幻读问题

  • 幻读是指在一个事务内，按照某个条件查询数据时，第一次查询和第二次查询得到的结果集数量不同，因为在两次查询之间，其他事务插入或删除了符合查询条件的数据。MVCC通过在读取数据时，不仅记录数据的版本信息，还记录数据的范围信息，来解决幻读问题。
  • 例如，在一个学生管理系统中，一个事务要查询某个班级的学生列表。如果在事务执行过程中，其他事务向该班级插入了新学生，那么在没有MVCC的情况下，第二次查询可能会得到比第一次更多的学生记录，产生幻读现象。MVCC通过维护数据的版本和范围信息，确保在同一个事务内，查询结果的一致性，避免了幻读问题。

---

如何解决幻读？

MVCC 加上 间隙锁 的方式
（1）在快照读读情况下，mysql 通过 mvcc 来避免幻读。
（2）在当前读读情况下，mysql 通过 next-key 来避免幻读。锁住某个条件下的数据不能更改。

快照读：简单的 select 操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

三. 索引

索引分类

索引类型	适用场景	示例
普通索引	常规查询优化	INDEX(idx_name)
唯一索引	需要保证唯一性的字段	UNIQUE INDEX(uniq_email)
主键索引	表的主键	PRIMARY KEY(id)
复合索引	多条件组合查询	INDEX(idx_age_gender, age, gender)
全文索引	文本内容搜索	FULLTEXT INDEX(ft_content)
空间索引	地理空间数据	SPATIAL INDEX(gis_data)

---

索引使用原则

索引创建的核心依据

1. 查询条件字段
   • WHERE 子句：频繁作为查询条件的字段
   • JOIN 条件：经常用于表连接的字段
   • 排序字段：ORDER BY 使用的字段
   • 分组字段：GROUP BY 使用的字段

2. 数据分布特征
   • 高选择性字段：字段值唯一或接近唯一（如用户ID、手机号）
   • 区分度高：不同值数量多/重复值少的字段

3. 表数据量
   • 数据量大的表（通常超过10万行）更需要索引
   • 小表可能不需要索引（全表扫描更快）

控制数量

索引数量不是越多越好，索引越多，维护索引的代价自然也就越高。对于 DML 操作比较频繁的表，索引过多会导致很高的维护代价。

使用短索引

索引使用硬盘存储，假如构成索引的字段长度比较短，那么在储块内就可以存储更多的索引，提升访问索引的 IO 效率。

建立索引

对查询频次较高且数据量比较大的表建立索引。索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

使用前缀索引

对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。前缀索引是一种能使索引更小更快的有效方法，缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY 以及覆盖扫描。

选择合适的索引顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是 1，因此也可以使用唯一索引提升查询效率。

删除重复索引

MySQL 允许在相同列上创建多个索引，重复索引需要单独维护，重复索引是指在相同的列上按照相同顺序创建的同类型的索引，应该避免创建。如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引，对于 B-Tree 索引来说是冗余的。解决重复索引和冗余索引的方法就是删除这些索引。

---

不应创建索引的情况

1. 频繁更新的字段：索引会降低写入性能
2. 低区分度字段：如性别、状态等只有少量枚举值的字段
3. 很少使用的查询条件
4. 大文本字段：应使用前缀索引或全文索引
5. 表数据量很小（如配置表）

---

索引失效

1. like '%xx'
    select * from tb1 where name like '%cn';
2.使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
3. or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
4. 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
5. 不等于号 !=  
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
6. 大于号 > 
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
7. order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
8. 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
    当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

---

B 树与 B+ 树简明扼要的区别

定义一条数据记录为一个二元组[key,data]：

​ key为记录的键值，key唯一

​ data为数据记录除 key 外的数据

B树

	**每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。**

B+树

	**只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。**

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

主要原因：一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

InnoDB

data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

MyISAM

data存的是索引(数据的地址)。索引是索引，数据是数据。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

补充：

（1）在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

（2）MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

---

Hash 索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。

限制：

• 数据不是按照索引值顺序存储的，无法排序。
• 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
• 只支持等值比较查询，不支持范围查询。

自适应哈希索引

自适应哈希索引是 InnoDB 的一个特殊功能，当它注意到某些索引被使用得很频繁时，会在内存中创键哈希索引，让 B-Tree 索引也具有哈希索引的一些优点。

---

覆盖索引

指一个索引包含所有需要查询字段的值，不再需要根据索引回表查询。

优点：

① 索引条目通常远小于数据行大小，如果只需要读取索引可以减少数据访问量

② 索引按照列值顺序存储，对于 IO 密集型的范围查询会比随机从磁盘读取每行数据的 IO 少得多。

③ 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 很有帮助。InnoDB 的二级索引在叶子节点保存了行的主键值，如果二级主键能覆盖查询那么可以避免对主键索引的二次查询。

---

Like 查询能否用到索引

使用 like x% 查询是可以用得到索引的，而使用 like %x% 和 like %x 查询是用不到索引的。

那么使用 like %x% 和 like %x 查询为什么用不到索引？

因为索引是一种有序的 B+ Tree 数据结构，叶子节点都是按照顺序从左向右排的，如果使用 like %x% 和 like %x 查询的话，不知道开头是哪个，就会去进行全表扫描。

---

联合索引（abc）命中规则

AND

只要用到了最左侧a列，和顺序无关,都会使用索引

a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引

c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

OR 不使用索引

a = 1 AND b = 2 OR c = 3 ; 未使用索引
a = 1 OR b = 2 AND c = 3 ; 未使用索引
a = 1 OR b = 2 OR c = 3 ; 未使用索引

最左侧的‘a’列 被大于，小于，不等于比较的 ，使用 range 索引

a > 1 AND b = 2 AND c = 3 ; 使用range索引
a < 1 AND b =