Post

Mysql

Posted
By jelech
9 min read
Mysql

数据库查询执行流程

COMACT如何存储数据

行、页、区、段

行存储结构

索引

  • 普通索引

  • 唯一索引：一个字段或多个字段或字段的组合值唯一

  • 主键索引：主键上的

  • 联合索引

  • 分类：聚簇索引：索引上叶子节点是数据

  • 分类：非聚簇索引：索引上叶子节点是索引，但有指向数据的块的指针

  • 类型：B+数索引 全文索引 哈希索引

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。二级索引如果查询的不是主键（或者联合索引的值），需要进行**回表**

联合索引

索引建立契机

  • 适合：

  • 不适合：

索引优化

  • 前缀索引优化：长字符串空间优化

  • 覆盖索引优化：避免回表

  • 主键索引最好是自增的：避免页分裂

  • 尽量设置为NOT_NULL：方便优化器、Null值列表占用空间

  • 防止索引失效

索引失效

  • 使用!=或<>操作符：索引对期待扫描全部数据的查询通常没有帮助，尤其是不等式查询。

  • 对索引列进行计算或函数操作

  • 使用LIKE操作符以%开头的模糊查询

  • 联合索引中没有使用最左前缀原则

  • 数据类型不一致

  • 如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列

B+树索引

  • 相比于B树： 两者都是二叉查找树，B+索引没有数据，数据都在叶子节点上，叶子节点相连，B+树支持范围查找。时间复杂度为固定的log(n)

  • 相比与Hash索引： Hash只在等值查找上比B+树好，其他都没B+好

  • 推荐单表不超过2000w，是因为超过这个值，索引需要4层才能存储，需要消耗更多磁盘io

覆盖索引

用索引去覆盖要查找的数据范围，避免回表

count(*)与count(1)

效率：count(*)=count(1)>count(id)>count(field)

  • 优化count(*)

数据库事务与ACID

作为一个逻辑工作单元进行执行的一系列操作，他可以满足四大条件

  • A原子性：要么全部完成，要么全部都不执行

  • C一致性：事务必须让数据库从一个一致性状态变为另一个一致性状态

  • I隔离性：提供一定的隔离级别，可以尽量减少外部并发操作的干扰

  • D持久性：事务提交后，对数据库的修改应该是永久的

隔离级别

并发可能带来的问题：数据丢失、脏读、不可重复读、幻读

  • 未提交读

  • 已提交读：每次读取数据时成一个新的 Read View

  • 可重复读：启动事务时生成一个 Read View

  • 可序列化

MVCC

需要先了解两个结构

ReadView四字段

聚簇索引中的的隐藏列

roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

MVCC流程（多版本并发控制）

事务在请求数据时，除了自己更新的记录可见以外，还有以下情况

  • 如果记录的trx_id小于min_trx_id，代表之前已提交的事务的数据，所以可见

  • 如果trx_id>max_trx_id，代表ReadView后启动的事务，所以不可见

  • 如果在两者之间，则去判断是否在m_ids中

MySQL存储引擎

  • InnoDB：支持事务处理、行级锁定和外键约束，适合需要高并发写入和复杂数据关系的场景。它使用聚簇索引来提高数据访问速度。

  • MyISAM：不支持事务处理，但查询性能较高，适合只读或大量查询的应用。MyISAM使用非聚簇索引，且不支持外键。

性能优化

  • SQL语句优化，索引优化

  • 分表、分库、分读写库

  • 加入缓存

锁

分类

  • 全局锁：数据库锁

  • 表级锁

  • 行级锁

  • 行级锁的SX分类

如何加行级锁

加锁的对象是索引，加锁的基本单位是 next-key lock，其在一些场景下会退化成记录锁或间隙锁：在能使用记录锁或者间隙锁就能避免幻读现象的场景下会退化

  • 唯一索引等值查询

  • 唯一索引范围查询

  • 非唯一索引等值查询

  • 非唯一索引范围查询

  • 没有索引的查询

死锁了怎么解决

  • 设置事务等待锁的超时时间

  • 开启主动死锁检测

other

  • 间隙锁的意义只在于阻止区间被插入，因此是可以共存的

  • 遇到唯一键冲突后会给记录加上S锁：id-记录锁 x-临键锁

日志

undoLog

回滚日志实现了事务中的原子性，主要用于事务回滚和 MVCC。

redoLog

重做日志实现了事务中的持久性，主要用于掉电等故障恢复；

binLog

归档日志Server 层生成的日志，主要用于数据备份和主从复制；

外键

  • LEFT JOIN

  • INNER JOIN

  • RIGHT JOIN

  • FULL OUTER JOIN

Coding

创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 创建数据库
CREATE DATABASE example_db;

-- 使用数据库
USE example_db;

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表（添加列）
ALTER TABLE users ADD COLUMN status VARCHAR(10) DEFAULT 'active';

-- 删除列
ALTER TABLE users DROP COLUMN status;

-- 删除表
DROP TABLE IF EXISTS users;

-- 删除数据库
DROP DATABASE IF EXISTS example_db;

CURD

1
2
3
4
5
6
7
8
9
10
11
12

-- 插入数据
INSERT INTO users (username, email) VALUES ('johndoe', 'john@example.com');

-- 查询数据
SELECT * FROM users;
SELECT username, email FROM users WHERE id > 5 ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

高级查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 聚合查询
SELECT COUNT(*) AS total_users FROM users;
SELECT MAX(created_at) AS last_signup FROM users;

-- 分组与聚合
SELECT status, COUNT(*) AS users_count FROM users GROUP BY status HAVING users_count > 1;

-- 连接查询
SELECT u.username, o.order_date FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

-- 子查询
SELECT username FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

索引和性能优化

1
2
3
4
5
6
7
8

-- 创建索引
CREATE INDEX idx_username ON users (username);

-- 删除索引
DROP INDEX idx_username ON users;

-- EXPLAIN 来分析查询性能
EXPLAIN SELECT * FROM users WHERE username = 'johndoe';
计算机基础
数据库 高频
This post is licensed under CC BY 4.0 by the author.