MySql复习

基本规范:

三大范式

第一范式

属性不可再分

第二范式

实体的属性完全依赖于主关键字。

产品的id不完全依赖于订单的id,可以拆分出去。简单点就是说这个订单表中要尽量都放订单的信息。

第三范式

即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

范式越高，表越细碎，查询时就要多表联查，性能低。实际也不会严格遵守，甚至

反范式化设计

 计数器。由于存在行锁，高并发下如果只有下面一行，多个线程间要等待。

加一个字段，计数槽的概念，从表的设计来看是冗余的，但是这样多线程可以同时操作这3行。

数据类型

整型

实数型

float   double    decimal

decimal保精度但是效率低，前两个效率高但是不准确。

引入一种思想：数据量特别大，要存实数，既要确保精度，又要确保效率，怎么办?把小数扩大成整数，用bigint。

字符型

char  varchar  前者比后者更省空间。

'hello'分别用char(5)和varchar(200)来存，二者的存储开销基本一样，但是前者性能更高。从磁盘往内存读数据的时候，前者只会分配固定内存，后者不确定。

命名规范

数据库名、表名、表别名、字段名、字段别名等都小写

SQL关键字、函数名、绑定变量等都大写

#字符串、日期时间类型的变量需要使用一对'  '表示

起别名用双引号

` `着重号，解决变量名和关键字冲突

注意：

空值null参与的运算以及比较，结果一定也为null。100+null=null   where id>null，where后面整体为null。100/0=null

mysql中聚合函数不能嵌套，oracle可以

sql中的+号只表示加法运算，没有连接的含义，100+'1'=101   100+'a'=100,100+'ab'=100， 不能转换的字符取0。 计算过程中会自动向浮点型转化。

分页LIMIT后面两个参数，第一个为偏移量，第二个是每页的数据容量。偏移量=（页数-1）*容量

排序写在分页前面。

语法

多表查询

内连接

外连接

给少的一方通过+补充数据，使两表一致。但是mysql中不支持下面写法，oracle中可以。

满外连接。mysql不支持

7种join方法

通过下面方式在sql中实现满外连接（补充7种join关系）：

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

推荐使用union  all  不用去重，效率高。 

左上图：左外连接，右上图，右外连接，中图，内连接

筛选条件

分组函数

下面这种写法是错误的。一个部门中会有多个职位，只按部门分，显示哪个职位呢？

HAVING

where是条件过滤，having也是条件过滤，二者的区别是什么：

 场景：

要求1:如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE，否则报错。

要求2:HAVING 必须声明在 GROUP BY 的后面。

要求3:开发中使用HAVING的前提是SQL中使用了GROUP BY

既然非聚合函数条件都可以放，聚合只能放在having中，where存在的意义？

执行流程

 先from看用哪些表，通过join on组织多表间的关系，拿到了需要的表后进行where筛选，对查出来的数据进行分组展示，对分组后的数组进行条件筛选，得到最终的数据后按需求select，最后排序和分页展示。

表中的数据几百万条，where后就能去掉很大的比重，然后再分类什么的。如果先having，它只能放在分组后面，所以会对几百万条数据分组，再筛选。效率差距来源。

执行流程也解释了select中起的别名为何不能用在where中。

约束

查看表约束

非空 NOT NULL

ALTER TABLE users  MODIFY age  INT  NOT NULL;  -- 将age字段修改为非空

ALTER TABLE users  MODIFY age INT NULL;  -- 允许age字段为NULL

如果现有字段中存在NULL值，直接添加NOT NULL会失败。需先更新数据，再添加约束：

非空字段建议搭配DEFAULT值，确保插入数据时无需显式指定该字段

唯一约束 UNIQUE

CREATE TABLE products (
    id INT PRIMARY KEY,
    category VARCHAR(50),
    name VARCHAR(50),
    UNIQUE (category, name)  -- 组合唯一约束：同一类别下名称不能重复
);

-- 方式1：直接添加约束（系统自动命名）
ALTER TABLE users ADD UNIQUE (phone);

-- 方式2：添加带名称的约束（推荐，方便后续管理）
ALTER TABLE users ADD CONSTRAINT uc_phone UNIQUE (phone);

-- 添加组合唯一约束
ALTER TABLE products ADD CONSTRAINT uc_category_name UNIQUE (category, name);

唯一索引（Unique Index） 和 唯一约束（Unique Constraint）的核心功能都是保证字段值的唯一性，本质上是同一概念的不同表现形式，MySQL 通过唯一索引实现唯一约束

CREATE UNIQUE INDEX idx_username ON users (username);

- 通过约束名删除（适用于命名约束）
ALTER TABLE users DROP CONSTRAINT uc_phone;

-- 通过索引名删除（适用于未命名约束或索引创建的约束）
ALTER TABLE users DROP INDEX phone;

主键约束

一个表中最多有一个主键约束，要求非空且唯一。

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- 联合主键
);

-- 方式1：使用ALTER TABLE语句
ALTER TABLE users ADD PRIMARY KEY (id);

-- 方式2：为复合主键命名（MySQL自动命名为PRIMARY）
ALTER TABLE order_items 
ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id);

ALTER TABLE users DROP PRIMARY KEY;

外键约束

父表必须存在且有主键，子表外键字段需与父表主键类型兼容

子表插入时，外键值必须存在于父表中，或为 NULL（如果外键允许 NULL）

父表删除记录时，若子表存在关联记录，需先删除子表记录或设置级联操作

-- 正确：先删除子表记录
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE customer_id = 1;

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)  -- 外键关联
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers  -- 外键约束名（可选）
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

级联操作 

ON DELETE SET NULL   父表记录删除时，子表关联字段置为 NULL（需外键允许 NULL）

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE  -- 父表删除时，子表关联记录自动删除
    ON UPDATE CASCADE  -- 父表更新时，子表关联记录自动更新
);

检查约束

5.7 及之前版本不支持，8.0后支持

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT,
    CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 65)  -- 年龄范围限制
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    CHECK (delivery_date >= order_date)  -- 交货日期不能早于订单日期
);

ALTER TABLE employees DROP CONSTRAINT chk_age;

索引

索引结构

还有个全文索引，知道就行

B+树结构

二叉树缺点:顺序插入时，会形成一个链表，查询性能大大降低，大数据量情况下，层级较深，检索速度慢。

红黑树解决了树的平衡问题，大数据量情况下，层级较深，检索速度慢。

B-Tree，阶数=子节点最大个数=指针数=key+1，减少了层级

B+树。所有的数据都会出现在叶子节点，叶子节点形成一个单向链表

 mysql进行了优化

Hash结构

二者区别：

Hash索引只能用于等值比较(=，in)，不支持范围查询(between，>，<，….) 无法利用索引完成排序操作。

查询效率高，通常只需要一次检索(没有发生碰撞)就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的，一些热点查询会自动优化成哈希索引。

为什么InnoDB存储引擎选择使用B+tree索引结构?

InnoDB 存储引擎选择 B + 树作为索引结构，是因为其结构特性与数据库的查询需求、存储机制高度适配，能够在性能、效率和稳定性上达到最优平衡。B + 树作为一种多路平衡查找树，非叶子节点仅存储索引键，叶子节点存储完整数据或数据指针且通过双向链表连接，这使得树的高度通常较低（千万级数据一般仅 3-4 层），查询时最多只需 3-4 次磁盘 IO，效率稳定；同时，叶子节点的链表结构让范围查询、排序和分组操作无需遍历整棵树，只需定位首尾节点即可连续读取，极大提升了这类常见操作的效率。从磁盘 IO 优化来看，B + 树的每个节点大小与 InnoDB 的页（默认 16KB）对齐，一次 IO 可读取整个节点，而非叶子节点存储更多索引项的特点进一步降低了树高，配合磁盘预读机制和局部性原理，连续访问时能有效利用相邻数据的物理存储特性，减少 IO 次数。相比其他结构，B + 树克服了哈希索引不支持范围查询、B 树非叶子节点存数据导致树高增加、二叉树可能退化等问题，且能完美支持 InnoDB 的聚簇索引特性 —— 主键索引叶子节点直接存储数据，辅助索引通过主键关联，既避免冗余又保证一致性，因此成为 InnoDB 的理想选择。

索引分类

聚集索引和二级索引

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

 联合索引

覆盖索引：

select 后面的字段，尽量都包含在where中使用的索引列中，减少回表查询。

前缀索引

当某个字段内容是大文本时，又恰好需要索引，可以去该列的前多少个字符建立索引。

CREATE INDEX index_name ON table_name (column_name);普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);唯一索引
CREATE INDEX index_name ON table_name (column1, column2, column3);组合索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);添加主键时会自动创建索引
SHOW INDEX FROM table name ;
DROP INDEX index_name ON table_name
#查看不同命令访问数据库的频次
SHOW GLOBAL STATUS LIKE 'Com_______';#7个下划线
#前缀索引
ALTER TABLE users ADD INDEX idx_email (email(10));

索引失效

最左前缀法

对于联合索引“ABC”，查询从索引的最左列开始，并且不跳过索引中的列,如果跳跃某一列，索引将部分失效(后面的字段索引失效)。查“AC”，C失效；查“BC”,都失效；查“AB”不失效；“BCA”，前后的顺序无所谓，只要左边的存在就行，不会失效（由于查询优化器能够对条件进行重排序，使其变为 A、B、C，所以不会违反最左前缀法则）。

联合索引中，出现范围查询（大于或小于），范围查询右侧的列索引失效。第一个后面的status失效，第二个不失效，因为是>=

在索引列上进行运算操作，索引将失效。

字符串类型字段使用时，不加引号，索引将失效.

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

MySQL自己会评估使用索引情况，如果使用索引比全表查询更慢，则不使用索引

sql提示

建议数据库用什么索引，但是不一定听你的。

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

如果索引列不能存储NULL值，请在创建表时使用NQT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

 事务

对勾代表存在的问题，叉子是解决了的问题

如何解决上述问题：

不使用锁的场景（快照读）：MVCC 解决大部分问题

当执行普通SELECT查询（不加锁的 “快照读”）时：

• MVCC 通过数据快照机制，确保事务只能看到事务启动前已提交的数据版本，因此可以：
  • 避免脏读：不会读到其他未提交事务的修改。
  • 避免不可重复读：同一事务内多次读取同一行，结果始终一致（基于同一快照）。
  • 避免普通幻读：其他事务新插入的记录不会出现在当前事务的快照中，因此两次查询的结果集行数不变。

使用锁的场景（当前读）：MVCC 与锁机制协同工作。

四大特性如何保证

原子性

• 实现机制： undo log（回滚日志）
  • 事务执行时，InnoDB 会记录每行数据的修改前状态到undo log中（如插入前记录删除日志，更新前记录旧值）。
  • 若事务执行失败（如异常中断、手动ROLLBACK），MySQL 会利用undo log反向操作，将数据恢复到事务开始前的状态。
  • 例如：执行UPDATE t SET a=1 WHERE id=1时，undo log会记录id=1的原始a值，回滚时会将a恢复为原始值。

一致性

• 实现机制：多因素协同

  • 数据库约束：主键、外键、唯一索引、CHECK 约束等，阻止非法数据写入（如主键重复会直接报错）。

  • 事务原子性：通过undo log确保事务失败时数据回滚，避免中间状态。

  • 隔离性：通过锁和 MVCC 防止并发操作导致的数据混乱。

  • 应用层保证：业务逻辑需确保事务内操作符合业务规则（如转账时 “转出金额 = 转入金额”）。

隔离性

实现机制：锁机制 + MVCC（多版本并发控制）

• 锁机制：

  • 行级锁（共享锁 S、排他锁 X）：控制对单行数据的并发访问。

  • 表级锁（意向锁 IS/IX、表锁）：协调行锁与表级操作（如全表扫描）的冲突。

  • 间隙锁 / 临键锁：在REPEATABLE READ隔离级别下防止幻读。

• MVCC：

  • 通过为每行数据维护多个版本（隐藏列存储创建版本号和删除版本号），实现 “读不加锁”。

  • 事务只能看到启动前已提交的数据版本，避免脏读、不可重复读等问题。

• 隔离级别：通过设置不同隔离级别（READ UNCOMMITTED到SERIALIZABLE），平衡隔离性与并发性能

持久性

• 实现机制：redo log（重做日志）

  • 事务执行时，InnoDB 先将数据修改写入内存缓冲池（Buffer Pool），同时记录修改操作到redo log（顺序写入，性能高）。

  • redo log会定期（或满足条件时）刷新到磁盘，确保即使内存数据丢失，也能通过redo log恢复已提交的修改。

  • 相比直接刷写数据文件（随机 IO，性能低），redo log通过 “预写日志（WAL，Write-Ahead Logging）” 机制，大幅提升持久性保证的效率。

MVCC

不同的隔离级别，生成Readview的时机不同:

READ COMMITTED:在事务中每一次执行快照读时生成ReadView。解决了脏读的问题，也就是说只能读取已经提交的事务。

REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView，后续复该ReadView。解决了不可重复读的问题

锁

全局锁

全局锁就是对整个数据库加锁，加锁后整个数据库就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都 将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

数据库中加全局锁，是一个比较重的操作，存在以下问题: 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。

flush tables with read lock
unlock tables

表级锁

读书和写锁

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。读锁也叫共享锁S，写锁是排它锁X。

只有SS共享，其它都互斥

元数据锁

元数据理解成表结构，简单理解就是当前线程操作数据库时，其它线程不能修改表结构。

意向锁

是表级锁，用于标识某个事务正在或准备对表中的行加锁（行级锁），其核心作用是协调表级锁与行级锁的兼容性，避免事务之间的冲突。当事务对表中的某一行加锁时，数据库会先在表级别添加对应的意向锁，以声明 “该表的某些行正在被锁定或即将被锁定，比如

• 事务对表中一行加行级共享锁（S） 前，会先在表级添加意向共享锁（IS）。

• 事务对表中一行加行级排他锁（X） 前，会先在表级添加意向排他锁（IX）

如果没有意向锁，在加表锁时，会逐行检查看有没有行锁，效率很低。有了意向锁，在加行锁前就对表加了共享锁，所以在加表锁时只用检查一下共享锁，就能判断是否冲突了，不用再逐行遍历。

行级锁

普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。  

//对读取的记录加共享锁
select... lock in share mode;
//对读取的记录加独占锁
select ... for update;

 行锁

(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

间隙锁

(Gap Lock):锁定索引记录间隙(不含该记录)， 确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持

临键锁

(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

默认情况下，InnoDB在 RR级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。 

日志

redo log

客户端发起请求，缓存页中没有数据时从磁盘获取，加载到缓存区。进行修改时，不会立刻将修改内容同步到磁盘中。而是将修改操作记录在日志缓存中，日志缓存会立刻写入磁盘中。（前者不立刻同步，是因为在操作数据库的过程中进行的不多是随机IO，而写日志是以追加的方式进行的，显然后者效率更高）

undo log

 优化

 性能分析方法：

慢查询日志记录了所有执行时间超过指定值(默认10秒)的所有SQL语句的日志

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

EXPLAIN 或者 DESC命令获取 MVSOL如何执行 SELECT语句的信息，包括在 SELECT语句执行过程中表如何连接和连接的顺序。

语句优化

插入优化：

尽可能批量插入数据

尽可能手动提交事务

如果一次性需要插入大批量数据，使用insert语句插入性能较低，使用load指令进行插入。

主键优化

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO INCREMENT自增主键(页分裂)。

尽量不要使用UUID做主键或者是其他自然主键，如身份证号

业务操作时，避免对主键的修改。

页分裂：

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多多就会行溢出）

乱序插入时，比如50。会把page1中的后两个数据先移动到page3,再重新建立索引

补充页合并的概念：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除，并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

 order by优化

在分组操作时，可以通过索引来提高效率

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。 尽量使用覆盖索引。

多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort buffer size(默认256k)。

limit优化：

一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

问题解析：limit 2000000,10 为什么低效？

当执行类似 SELECT * FROM 表名 WHERE 条件 ORDER BY 字段 LIMIT 2000000,10 的查询时，看似只是想获取 “第 2000001 到 2000010 条记录”，但 MySQL 的执行逻辑是：

1. 先根据 WHERE 条件筛选出符合要求的所有记录；
2. 对这些记录按照 ORDER BY 指定的字段进行全量排序（排序前 2000010 条记录）；
3. 从排序后的结果中，跳过前 2000000 条，只取后面 10 条返回；
4. 其余排序好的 2000000 条记录会被直接丢弃。

对于大偏移量的分页查询，优化逻辑是：先通过覆盖索引快速定位到目标记录的主键，再通过主键查询详细数据，避免对全量数据排序。

count优化：

InnoDB 引擎在执行 count(*)的时候，默认需要把数据一行一行地从引擎里面读出来(后面做了优化)，然后累积计数。没有什么比较好的优化策略，可以通过自己计数的方法。

count(字段)< count(主键 id) <count(1)< count(*)

update优化： 

update修改数据时会加锁，update操作的列如果有索引，加的是行锁，没有索引就会升级成表锁。

 存储引擎

逻辑存储结构

物理存储结构

分为内存结构和磁盘结构，二者通过后台线程进行交互。

内存结构

Buffer Pool:

Change Buffer:

Adaptive Hash Index：

Log Buffer:

磁盘结构

System Tablespace :

File-Per-Table Tablespaces：

每个数据库表及其索引存储的独立物理文件中。

General Tablespaces：

一种用户可自定义的共享表空间，用的不多。

Doublewrite Buffer Files:

Redo Log：

Undo Tablespaces:

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undo log日志。

后台进程