MySQL⑦SQL优化、锁

1、SQL 优化

1.1、插入数据

两个优化思路：INSERT 和 LOAD。

1.1.1、INSERT

向数据库表插入多条记录：INSERT

  1. 批量插入

  2. 手动提交事务

  3. 主键顺序插入：主键顺序插入的效率高于乱序插入。

    # 批量插入
INSERT INTO tb_user(id, name)
VALUES(1,'Alice'),
	(2,'Bob'),
	(3,'Cindy');
# 手动提交事务
START TRANSACTION;

INSERT INTO tb_user(id, name) VALUE(1,'Alice');
INSERT INTO tb_user(id, name) VALUE(2,'Bob');
INSERT INTO tb_user(id, name) VALUE(3,'Cindy');

COMMIT;

1.1.2、LOAD

向数据库表插入大量数据：LOAD

此时使用 INSERT 效率低，应使用 LOAD 指令。

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

# 开启从本地加载文件导入数据
SET GLOBAL local_infile = 1;

# 加载数据到表结构中
LOAD DATA LOCAL INFILE '数据文件路径'
INTO TABLE 表名 FIELDS
TERMINATED BY '字段分隔符' LINES TERMINATED BY '行分隔符（\n）' ;

1.2、主键

刚才讲到，主键顺序插入的效率高于乱序插入。

1.2.1、说明

InnoDB 是索引组织表（Index Organized Table, IOT）

表的数据按主键顺序组织存放

结构特点

  1. 行数据存储在聚集索引的叶子节点上。
  2. 数据行记录在逻辑结构 page 中，每个 page 大小固定，默认 16K。
  3. 规定每个页包含 2-N 行数据，根据主键排列（如果某行数据过大，会行溢出）。

操作特点

  1. 若插入的记录在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。
  2. 删除的记录并不会被物理删除。而是被标记为删除，且空间允许被其它记录声明使用。

页分裂

主键乱序插入时，如果页的大小不足则会页分裂

步骤

  1. 将页的后半部分记录移动到一张新表中，并将新记录插入。

  2. 修改页之间的指针关系。

    image-20220316084621705

页合并

  • 页中删除的记录达到阈值时，InnoDB 会寻找相邻页，查看是否可以合并。
  • MERGE_THRESHOLD：合并页的阈值（默认页的 50%），可以在创建表或者创建索引时指定。

步骤

  1. 删除记录达到阈值，查看相邻页是否可以合并

  2. 若可以合并，将标记为删除的记录实际删除

  3. 移动记录进行页合并

    image-20220316090422084

1.2.1、优化

主键索引设计原则

  1. 满足业务需求的情况下，尽量降低主键长度（尽量不使用 UUID 和身份证等）
  2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。
  3. 业务操作时，避免修改主键。

说明：在实际开发中，往往使用两种主键，即逻辑主键和业务主键。

  • 逻辑主键：AUTO_INCREMENT 自增主键，用于区分每一条数据库记录。
  • 业务主键：能唯一标识业务实体的键，如用户 ID，员工 ID，学号等。

1.3、ORDER BY

MySQL 排序的两种方式

  • Using index：通过有序索引顺序扫描，直接返回有序数据。
  • Using filesort：通过索引或全表扫描，读取满足条件的数据行，在排序缓冲区中完成排序操作。

1.3.1、说明

以 tb_user 表为例

  1. 如果查询列没有索引，则以 Using filesort 方式排序。

  2. 创建索引

    # 没有指定排序方式，默认为升序
CREATE INDEX idx_user_age_phone ON tb_user(age, phone);

排序查询：此时 age 和 phone 有联合索引

以下是不同 SQL 语句对应使用的排序方式

  1. Using index：遵守最左前缀法则。

    SELECT id, age, phone FROM tb_user ORDER BY age;

SELECT id, age, phone FROM tb_user ORDER BY age, phone;

  2. Using index, Backward index scan：遵守最左前缀法则，且指定排序方式与定义的索引排序方式都相反。

    SELECT id, age, phone FROM tb_user ORDER BY age DESC, phone DESC;

  3. Using filesort：不遵守最左前缀法则。

    SELECT id, age, phone FROM tb_user ORDER BY phone;

SELECT id, age, phone FROM tb_user ORDER BY phone, age;

  4. Using index, Using filesort：遵守最左前缀法则，但指定排序方式与定义的索引排序方式部分相反。

    SELECT id, age, phone FROM tb_user ORDER BY age, phone DESC;

# 解决方案：创建相应排序顺序的索引以满足业务
CREATE INDEX idx_user_age_phone_ad ON tb_user(age ASC, phone DESC);

1.3.2、优化

在优化排序操作时，要尽量优化为 Using index。

  1. 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则。
  2. 尽量使用覆盖索引。
  3. 多字段排序时，注意创建联合索引的排序规则（ASC / DESC）。
  4. 如果无法避免 filesort，适当增加排序缓冲区大小（sort_buffer_size，默认 256K）。

1.4、GROUP BY

MySQL 分组的方式

  1. Using index：索引
  2. Using temporary：临时表

1.4.1、说明

以 tb_user 表为例

  1. 如果查询列没有索引，则以 Using temporary 方式排序。

  2. 创建索引

    CREATE INDEX idx_user_prof_age ON tb_user(profession, age);

分组查询：此时有联合索引

以下是不同 SQL 语句对应使用的分组方式

  • Using index：遵守最左前缀法则

    SELECT profession, COUNT(*) FROM tb_user GROUP BY profession;

SELECT profession, COUNT(*) FROM tb_user GROUP BY profession, age;
SELECT profession, COUNT(*) FROM tb_user WHERE profession = '计算机科学' GROUP BY age;

  • Using temporary：不遵守最左前缀法则

    SELECT profession, COUNT(*) FROM tb_user GROUP BY age;

1.4.2、优化

  1. 为分组条件列建立索引，尽量覆盖索引。
  2. 索引的使用满足最左前缀法则。

1.5、LIMIT

分页查询：大数据量的数据库表，越往后查询效率越低。

  • 原因：MySQL 会对指定分页之前的所有记录进行排序，仅返回指定分页的记录。
  • 优化：覆盖索引 + 子查询。

示例：查询第 1,000,000-1,000,010 记录

MySQL 会对前 1,000,000 条记录进行排序，仅返回 1,000,000-1,000,010 记录。

# 查询第1,000,000条记录开始的10条记录
SELECT * FROM tb_user LIMIT 1000000,10;

改进

# 使用SELECT子查询
SELECT s.*
FROM tb_user u, (SELECT id FROM tb_user ORDER BY id LIMIT 1000000,10) uid
WHERE u.id = uid.id;

# 错误写法：IN不支持子查询
SELECT *
FROM tb_user
WHERE id IN (SELECT id FROM tb_user ORDER BY id LIMIT 1000000,10);

1.6、COUNT

COUNT(*) 说明

  • MyISAM：将表的总行数存储在磁盘中，执行 count(*) 时直接返回，效率高（前提是没有 WHERE 条件，否则也慢）
  • InnoDB：执行 count(*) 时，从引擎中逐行读取并累计。

效率：COUNT(字段) < COUNT(主键 id) < COUNT(1) ≈ COUNT(*)

优化：尽量使用 COUNT(*)

取值 服务层计数方式
COUNT(字段) InnoDB 引擎遍历整张表，取出每行的字段值 无 NOT NULL 约束：对非 null 行数进行累加。
有 NOT NULL 约束：按行累加。
COUNT(主键) InnoDB 引擎遍历整张表，取出每行的主键值 按行进行累加（主键不可能为null）
COUNT(数字) InnoDB 引擎遍历整张表，不取值 对于返回的每一行，放一个数字进去，按行累加。
COUNT(*) InnoDB引擎遍历整张表，不取值 按行累加

1.7、UPDATE

在前面讲到，InnoDB 支持行级锁。

  • 行锁是对索引项加锁，而不是对记录加锁。
  • 关于行级锁、表锁等概念，稍后会具体讲解。

在一个事务中执行 UPDATE 操作时，系统会自动加锁。

  • 若条件列有索引，加行级锁，否则升级为表锁。

  • 事务提交后，自动释放锁。

    # id 有主键索引，可以加行级锁
START TRANSACTION;
UPDATE tb_user SET name = 'Jaywee' WHERE id = 1;
COMMIT;

# name没有索引，升级为表锁
START TRANSACTION;
UPDATE tb_user SET name = 'Jaywee' WHERE name = 'demo';
COMMIT;

优化：执行 UPDATE 语句时，对 WHERE 条件列添加有效索引。

2、锁

锁：计算机协调多个进程或线程并发访问某一资源的机制。

MySQL 按照锁的粒度分，分为以下三类：

  • 全局锁：锁定当前数据库中的所有表
  • 表级锁：锁住当前的整张表。
  • 行级锁：锁住当前的行记录。

2.1、全局锁

全局锁：对整个数据库实例加锁。

  • 加锁后整个实例处于只读状态。
  • 加锁期间 DML、DDL 语句都会被阻塞。
  • 典型场景：全库的逻辑备份。锁定所有的表，获取一致性视图，保证数据的完整性。

2.1.1、语法

  1. 加锁

    flush tables with read lock ;

  2. 数据备份

    mysqldump -uroot –p1234 itcast > itcast.sql

  3. 释放

    unlock tables ;

2.1.2、备份

加全局锁方式

特点如下

  • 主库备份：备份期间都不能执行更新，基本上业务无法进行。
  • 从库备份：备份期间从库不能执行主库同步过来的二进制日志（binlog），导致主从延迟。

不加锁方式（参数--single-transaction）

# 语法
mysqldump --single-transaction -u用户名 –p密码 数据库 > SQL文件

# 示例：将student数据库备份到student.sql文件中
mysqldump --single-transaction -uroot –p123456 student > student.sql

2.2、表级锁

表级锁：每次操作锁住整张表。

  • 锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 应用：MyISAM、InnoDB、BDB 等存储引擎都支持。
  • 类型：表锁、元数据锁、意向锁

2.2.1、表锁

表锁有以下两类

  • 表共享读锁（read lock）

  • 表独占写锁（write lock）

    # 加锁
LOCK TABLES 表名... READ;
LOCK TABLES 表名... WRITE;
# 释放锁
UNLOCK TABLES;
# 客户端断开连接时，锁也会释放

读锁

事务 A 对一个表加读锁，则任意事务对该表只读。

image-20220316153042036

写锁

事务 A 对一个表加写锁，则阻塞其它事务的读写。

image-20220316153219627

2.2.2、元数据锁

meta data lock（MDL）

MySQL 5.5 引入

  • 元数据：可理解为表的结构。

  • 作用：维护表元数据的数据一致性，避免 DML 和 DDL 冲突。

    • 当表上有活动事务时，不能对元数据进行写操作。
    • 即：当表涉及到未提交的事务时，不能修改表结构。

  • 使用：在访问一张表的时候，系统自动控制 MDL 加锁。

    • 增删改查：MDL 读锁（共享）
    • 更改表结构：MDL 写锁（排他）

理解

  1. 事务 A 执行 UPDATE 语句：对记录添加行锁，并且自动为该表添加一个 MDL 读锁

    SELECT * FROM tb_user WHERE id = 7;

  2. 事务 B 对该表添加表锁时，会阻塞

    LOCK TABLES tb_user READ;

  3. 事务 A 提交事务，释放行锁后，MDL 读锁也随之释放。

  4. 事务 B 才能获得 MDL 写锁。

常见元数据锁

在访问数据库表时，系统会根据操作自动加上相应的元数据锁。

对应元数据锁 互斥性
加表锁 SHARED_READ_ONLY
SHARED_NO_READ_WRITE
SELECT
SELECT IN SHARE MODE		 SHARED_READ 只与 EXCLUSIVE 互斥
增删改
SELECT FOR UPDATE		 SHARED_WRITE 只与 EXCLUSIVE 互斥
修改表结构 EXCLUSIVE 与其它 MDL 互斥

查看元数据锁情况

SELECT object_type,object_schema,object_name,lock_type,lock_duration
FROM performance_schema.metadata_locks;

2.2.3、意向锁

Intent lock

  • 作用：避免 DML 在执行时，加的行锁与表锁冲突。
  • 使用
    • DML 执行时添加行锁，系统自动添加对应的意向锁。
    • 事务提交后，意向锁会自动释放。

理解

假设场景：事务 A 对表中记录加了行锁，事务 B 要对该表加一个表锁。

  1. 事务 A 执行 UPDATE 语句，添加行锁。
  2. 事务 B 要加表锁时，要确定每行纪律都没有行锁才能加表锁。
  3. 也就是说，加表锁之前需要遍历整张数据库表，效率低。

意向锁：

  1. 事务 A 执行 UPDATE 语句，添加行锁的同时对该表添加一个意向锁。
  2. 事务 B 要加表锁时，判断该表是否有意向锁，没有则添加表锁。

分类

意向锁之间相互兼容，不会互斥

对应意向锁 互斥性
SELECT LOCK IN SHARE MODE 意向共享锁（IS） 与表独占写锁互斥
增删改、SELECT FOR UPDATE 意向排它锁（IX） 与表锁互斥

查看意向锁、行锁的加锁情况

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
FROM performance_schema.data_locks;

2.3、行级锁

行级锁：每次操作锁住对应的行数据。

  • 锁定粒度最小，发生锁冲突的概率最低，并发度最高
  • 应用：InnoDB 存储引擎（基于索引组织数据）。
  • 针对索引项加锁，而不是对记录本身加锁。
  • 如果对应列没有索引或失效，则行级锁会升级为表锁。

2.3.1、行锁

Record Lock：锁定单个行记录，阻塞其它事务的删改。

隔离级别支持：RC、RR

InnoDB 实现两种类型的行锁

  • 共享锁（S）：允许获取共享锁的事务执行读操作，阻止其它事务获得相同数据集的排它锁。

  • 排它锁（X）：允许获取排它锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排它锁。

    image-20220315235132991

常见 SQL 语句的行锁

类型 说明
增删改 排它锁 自动加锁
SELECT 不加锁
SELECT ... LOCK IN SHARE MODE 共享锁 手动添加 LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排它锁 手动添加 FOR UPDATE

说明

默认情况下，InnoDB 在 RR 隔离级别运行，且使用临键锁进行搜索和索引扫描，以防止幻读。

2.3.2、间隙锁、临键锁

二者都在 RR 隔离级别下支持。

  • Gap Lock：锁定索引记录的间隙（不含该记录），阻塞其它事务的 INSERT，防止幻读。
  • Next-Key Lock：行锁与间隙锁组合，锁定记录及间隙。

2.4、锁的使用情况

InnoDB 默认在 RR 隔离级别运行，且使用临键锁进行搜索和索引扫描，以防止幻读。

2.4.1、没有索引

当记录没有索引时，无法添加行锁，行锁升级为表锁。

2.4.2、唯一索引

等值查询

  1. 对于存在的记录，添加临键锁
  2. 对于不存在的记录，优化为间隙锁。
    • 假设表中有 id 为 1，3，7，8 的记录，执行 UPDATE tb_user WHERE id = 5
    • 此时在 3 和 7 之间添加间隙锁。
  • 等值查询：
  • 范围查询：添加临键锁（即给当前记录添加行锁，当前记录之后的所有间隙添加间隙锁）。

2.4.3、普通索引

等值查询：向右遍历，直至最后不满足查询条件的值，退化为间隙锁。

  • InnoDB 的 B+tree 索引，叶子节点是有序的双向链表。
  • 由于普通索引非唯一，可能存在多个相同值的记录。
  • 因此加锁的时候，会向右遍历直到不满足查询条件的值，加临键锁，并在记录之后加间隙锁。

示例

假设等值查询条件为 age = 16

  1. MySQL 匹配到第一个 16，向右遍历直到不满足查询条件的值 38

  2. 对 B 位置的 16 加临键锁，在 B 和 C 之间加间隙锁。

    image-20220317114722651
mysql

相关

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

MySQL left join、right join和join的区别

标签