数据库

mysql

Posted by fjw on September 6, 2021
mysql

sql分类

image-20240926103444930

DDL

数据库操作

  1. show databases;
  2. create database dbname;
  3. use dbname;
  4. select database();
  5. drop database dbname;

表操作

  1. show tables;
  2. create table tb_name(c1 type1, c2 type2);
  3. desc tb_name
  4. show create table tb_name
  5. alter table tb_name add(添加字段)/modify(修改字段类型)/change(修改名称及类型)/drop(删除字段)/rename to(修改表名) …;
  6. drop table tb_name;

DML

添加数据

insert into tb_name(c1, c2,…) valuesval1, val2,…;

修改数据

update tb_name set c1=val1, c2=val2[where condition];

删除数据

delete from tb_name [where cond]

DQL

语句

select

​ 字段列表 -> 字段名 [as] 别名

from

​ 表名

where

​ 条件列表 : >, >=, <，<=, =, <>, like, between… and, in, and, or （分组前过滤）

group by

​ 分组字段列表

having

​ 分组后条件列表（分组后过滤）

order by

​ 排序字段列表 （升序： asc，降序： dec）

limit

​ 分页参数 （起始索引， 展示记录数）

顺序

from -> where -> select ->group by -> having -> order by -> limit

DCL

用户管理

create user ‘用户名’@’主机名’ indentified by ‘密码’

alter user ‘用户名’@’主机名’ indentified with mysql_native_password by ‘密码’

drop user ‘用户名’@’主机名’

权限控制

授权：

grant 权限列表 on 数据库名.表名 to ‘用户名’@’主机名’；

撤销权限：

revoke 权限列表 on 数据库名.表名 from ‘用户名’@’主机名’；

约束

概念

约束是作用于表中字段的规则，用户限制存储在表中的数据

目的

保证数据库中数据的正确，有效性，完整性

分类

  1. 非空约束 not null
  2. 唯一约束 unique
  3. 主键约束 primary key
  4. 默认约束 default (没有指定值，使用默认值)
  5. 检查约束check
  6. 外键约束foreign key

多表查询

多表关系

一对多： 在多的一方设置外键。关联一的一方的主键

多对多： 建立中间表，中间表包含两个外键，关联两张表的主键

一对一： 用于表结构拆分，在其中任何一方设置外键，关联另一方的主键

多表查询

内连接

隐式： select … from 表A, 表B where 条件

显示： select … from 表A inner join 表B on 条件…

外连接

左外： select … from 表A left join 表B on 条件

右外： select… from 表A right join 表B on 条件

自连接

select … from表A 别名1,表A 别名2 where 条件

子查询

标量子查询

列子查询

行子查询

表子查询

事务

简介

一组操作的集合，要么全部执行成功，要么全部失败

操作

开启事务：

start transaction;

提交/回滚：

commit;/rollback;

事务的四大特性

image-20240926103952521

并发事务问题

脏读，不可重复读，幻读

事务的隔离级别

image-20240926104231157

mysql体系结构

image-20241007193053290

存储引擎控制数据存储方式

image-20241007193538025

innodb的特点

事务、外键、行级锁

B+树

  1. 相对于二叉树，层级更少，搜索效率更高
  2. 相对于B树，无论叶子节点还是非叶子节点，都会保存数据，导致存储的键变少，指针跟着减少，需要增加树的高度，导致性能降低，B+树叶子节点形成了链表，可以快速进行范围查询
  3. 相对于hash索引，B+树支持范围匹配和排序操作

索引

语法

MySQL 索引是一种用于加速数据库查询的结构，通过在表中的某些列上创建索引，MySQL 可以更快地定位所需的数据，避免全表扫描。索引对于提高查询性能至关重要，尤其是在处理大数据集时。以下是 MySQL 中创建和管理索引的常见语法和详细介绍：

创建索引

MySQL 提供了多种方式来创建索引，通常在 CREATE TABLE 或 ALTER TABLE 语句中定义，也可以使用 CREATE INDEX 来单独创建索引。

  1. CREATE INDEX 用于在表的现有列上创建索引，语法如下：
1
2

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (column_name [(length)] [ASC | DESC], ...);
  • index_name：索引的名称。
  • table_name：要创建索引的表名。
  • column_name：要索引的列名。可以指定多个列，创建复合索引。
  • length：对于 VARCHAR 或 BLOB 类型的列，可以指定索引的前缀长度。
  • ASC / DESC：指定索引的排序方式（默认为 ASC）。

示例：

1
2

CREATE INDEX idx_name ON employees (name);
CREATE INDEX idx_name_age ON employees (name, age);
  1. 在 CREATE TABLE 时创建索引

在创建表的同时可以定义索引，语法与 CREATE INDEX 类似：

1
2
3
4
5
6

CREATE TABLE table_name (
    column_name1 datatype,
    column_name2 datatype,
    ...
    [UNIQUE | FULLTEXT | SPATIAL] INDEX [index_name] (column_name [(length)] [ASC | DESC], ...)
);

示例：

1
2
3
4
5
6

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    age INT,
    INDEX idx_name (name)
);

索引类型

MySQL 支持几种不同类型的索引，根据不同的应用场景可以选择合适的索引类型：

普通索引（Index）

普通索引用于加速查询，允许重复值和 NULL。这是最常见的索引类型。

1

CREATE INDEX idx_name ON employees (name);
唯一索引（UNIQUE Index）

唯一索引确保列中的值是唯一的（即不会有重复值），对于查询性能有优化作用，但同时强制了唯一性约束。

1

CREATE UNIQUE INDEX idx_email ON users (email);
  • 如果试图在 UNIQUE 索引的列中插入重复值，MySQL 会报错。
  • 可以通过在多个列上创建唯一索引，实现组合唯一性。
1

CREATE UNIQUE INDEX idx_name_age ON employees (name, age);
全文索引（FULLTEXT Index）

FULLTEXT 索引用于加速文本字段的全文搜索，通常用于 CHAR, VARCHAR, TEXT 类型的列。MySQL 在 InnoDB 引擎中也支持 FULLTEXT 索引，但主要用于 MyISAM 表。

1

CREATE FULLTEXT INDEX idx_bio ON employees (bio);

可以通过 MATCH ... AGAINST 语法进行全文搜索：

1

SELECT * FROM employees WHERE MATCH(bio) AGAINST('developer');
空间索引（SPATIAL Index）

SPATIAL 索引用于存储和查询地理空间数据，通常用于 GEOMETRY 类型的列，并且只能在 MyISAM 或 InnoDB 表上使用。

1

CREATE SPATIAL INDEX idx_location ON places (location);
复合索引（Composite Index）

复合索引是指在多个列上创建的索引，用于加速涉及多个列的查询。使用复合索引时，索引会首先按第一个列排序，再按第二个列排序，依次类推。

1

CREATE INDEX idx_name_age ON employees (name, age);

在查询时，如果查询条件包含复合索引中的前几个列，MySQL 会利用这个索引加速查询。例如：

1

SELECT * FROM employees WHERE name = 'John' AND age = 30;

复合索引也能用于部分匹配，但必须是从索引的左边开始，例如，以下查询也会使用 idx_name_age 索引：

1

SELECT * FROM employees WHERE name = 'John';

但如果只按 age 查询，索引将不会生效：

1

SELECT * FROM employees WHERE age = 30;

删除索引

要删除一个已经存在的索引，可以使用 DROP INDEX 语法。注意，删除索引不会影响表中的数据。

1

DROP INDEX index_name ON table_name;

示例：

1

DROP INDEX idx_name ON employees;

查看索引

要查看表中已存在的索引，可以使用 SHOW INDEX 或 DESCRIBE 命令。

1

SHOW INDEX FROM table_name;

示例：

1

SHOW INDEX FROM employees;

索引的使用场景

  • 主键索引：主键列默认建立唯一索引，并且强制唯一性。
  • 查询优化：对于查询中经常出现在 WHERE 子句、ORDER BY、GROUP BY、JOIN 的列，创建索引可以显著提高查询效率。
  • 唯一性要求：如果列需要唯一值，可以使用唯一索引来确保数据完整性。
  • 全文检索：对于大文本字段，使用 FULLTEXT 索引进行全文搜索优化。

索引的注意事项

  • 过多的索引可能降低写入性能：每当有插入、更新或删除操作时，MySQL 都需要更新索引，因此过多的索引会降低写入性能。
  • 适当选择索引列：应当选择那些用于查询条件或排序的列作为索引，而不是所有列都添加索引。
  • 索引大小问题：对于 BLOB 和 TEXT 类型的列，通常需要指定索引的前缀长度（例如 VARCHAR(255)），否则索引的大小可能过大。
1

CREATE INDEX idx_name_prefix ON employees (name(10));

聚集索引和二级索引

在 MySQL 中，聚集索引（Clustered Index）和二级索引（Secondary Index，也称非聚集索引）是两种重要的索引类型，它们在数据的存储和查询效率上有显著差异。下面详细介绍它们的概念、区别以及它们在 MySQL 中的作用。

聚集索引（Clustered Index）

概念

聚集索引决定了表中数据的物理存储顺序。在 MySQL 的 InnoDB 存储引擎中，表的数据行是按照聚集索引的顺序存储的，因此一个表只能有一个聚集索引。通常情况下，主键会自动作为聚集索引。

工作原理

  • 当你对表的某列创建聚集索引时，MySQL 会根据该列的值对数据行进行物理排序。
  • 在 InnoDB 中，聚集索引的叶子节点不仅存储索引键值，还存储完整的行数据。这意味着通过聚集索引可以直接找到行数据。
  • 如果表有主键，MySQL 会默认使用主键作为聚集索引。如果表没有主键，InnoDB 会选择一个唯一的非空索引作为聚集索引；如果也没有这样的索引，InnoDB 会创建一个隐藏的聚集索引。

聚集索引的特性

  • 数据存储顺序：数据行按照聚集索引列的顺序存储，查询时可以直接找到数据行。
  • 只允许一个：每个表只能有一个聚集索引，因为表的行只能按一种方式排序。
  • 高效的范围查询：因为数据物理上是连续存储的，使用聚集索引进行范围查询时效率较高。

假设你有一张 employees 表，并且 id 是主键列，MySQL 会自动将 id 列设置为聚集索引：

1
2
3
4
5

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

这里，id 是聚集索引，表的数据按照 id 的顺序存储，查询 id 列时可以直接访问数据行。

二级索引（Secondary Index，或非聚集索引）

概念

二级索引是指除了聚集索引之外的其他索引。在 InnoDB 中，二级索引的叶子节点存储的是索引列的值和对应的主键值，而不是完整的行数据。通过二级索引查找数据时，MySQL 首先通过二级索引找到对应的主键值，然后通过主键在聚集索引中查找实际的数据行。

工作原理

  • 二级索引独立于聚集索引，叶子节点中存储的是索引列值和对应的主键值，而不是行数据。
  • 查询时，MySQL 先通过二级索引找到对应的主键，然后通过主键回表（回到聚集索引）查找完整的行数据。
  • 由于需要回表查询，所以二级索引的查询效率通常低于聚集索引，尤其是在需要访问很多行数据时，回表操作会增加 I/O 开销。

二级索引的特性

  • 多个二级索引：一个表可以有多个二级索引，可以为不同的列创建多个索引。
  • 回表查询：二级索引查询时会涉及“回表”操作，即通过二级索引查找到主键后，还需要再去聚集索引查找完整的行数据。
  • 不影响数据存储顺序：二级索引不会改变表的物理存储顺序。

示例

假设在 employees 表上为 name 列创建了一个二级索引：

1

CREATE INDEX idx_name ON employees(name);

在查询 name 时，MySQL 会先通过 idx_name 索引找到对应的主键 id，然后通过主键在聚集索引中找到实际的行数据。

聚集索引 vs 二级索引的区别

特性 聚集索引（Clustered Index） 二级索引（Secondary Index）
数据存储方式 按照索引列的顺序存储实际数据行 叶子节点存储索引列和对应的主键值
查找数据方式 直接从索引定位到数据行 先通过二级索引找到主键，再通过主键查找行数据（回表操作）
数量限制 每个表只能有一个聚集索引 每个表可以有多个二级索引
查询效率 查询效率更高，特别是范围查询 需要回表查找数据，查询效率相对较低
使用场景 主键或其他具有唯一性的列 非主键列或需要加速查询的其他列

聚集索引和二级索引的优化建议

  • 选择合适的聚集索引：由于聚集索引决定了数据的物理存储顺序，应该选择一个常用于范围查询、排序查询的列作为聚集索引。通常，主键是理想的选择。

  • 二级索引的使用：二级索引适合用于那些频繁用于查询条件或 JOIN 操作的列，但要注意避免创建过多的索引，因为每次数据插入、更新或删除时，索引都会增加开销。

  • 覆盖索引：对于某些查询，可以通过构建“覆盖索引”来避免回表操作。如果查询的所有列都包含在二级索引中，MySQL 可以直接从索引中获取数据，而无需回表。

    示例：

    假设你经常运行如下查询：

    1

    SELECT name, age FROM employees WHERE name = 'John';

    可以创建一个覆盖索引：

    1

    CREATE INDEX idx_name_age ON employees(name, age);

    这样查询时可以直接从二级索引中获取 name 和 age，无需回表。

索引的设计原则

选择合适的列创建索引

经常用于 WHERE、JOIN 和 GROUP BY 子句的列

  • 索引应该主要用于查询频繁涉及的列，如 WHERE 条件、JOIN 操作中的连接字段以及用于分组查询的列。

  • 示例：对于如下查询，通常会为 age 和 department_id 列创建索引。

    1

    SELECT * FROM employees WHERE age > 30 AND department_id = 5;

经常用于排序的列

  • 如果查询经常使用 ORDER BY 或 GROUP BY 进行排序，应该为相关列创建索引，以避免排序时进行全表扫描。

  • 示例：对于如下查询，创建 age 列的索引可以加速排序操作。

    1

    SELECT * FROM employees ORDER BY age DESC;

索引不宜过多

  • 索引有助于加速查询，但过多的索引会带来维护开销，特别是在执行 INSERT、UPDATE 和 DELETE 操作时，每次对数据的修改都需要更新相关的索引。
  • 索引过多也会增加索引存储空间的开销。因此，应根据实际查询需求，合理选择需要创建索引的列。

选择性高的列创建索引

  • 选择性 是指列中不同值的数量与总行数的比率。高选择性的列可以显著提升查询效率，因为索引会帮助快速定位行数据，避免扫描大量的无关数据。
  • 示例：如果 id 是自增主键，它的选择性非常高，适合作为索引。而 gender 列（假设只有两个值：male 和 female）的选择性较低，不适合单独创建索引。

尽量使用复合索引（Composite Index）

  • 如果查询中经常使用多个列进行过滤，可以为这些列创建复合索引。复合索引不仅能加速多列查询，还可以减少需要维护的索引数量，避免过多的单列索引。

  • 索引最左前缀原则：复合索引遵循“最左前缀”原则，即 MySQL 会首先根据索引中的第一列进行查找，然后再根据第二列、第三列进行匹配。因此，查询条件中必须包含索引的最左列才能有效利用索引。

    示例：

    对于如下查询，可以为 age 和 department_id 创建复合索引：

    1

    SELECT * FROM employees WHERE age > 30 AND department_id = 5;

    索引顺序为 (age, department_id)，那么以下查询都可以有效利用该索引：

    1
2

    sql复制代码SELECT * FROM employees WHERE age > 30;
SELECT * FROM employees WHERE age > 30 AND department_id = 5;

    但如果查询仅涉及 department_id，则该复合索引不会被使用。

    1

    SELECT * FROM employees WHERE department_id = 5;  -- 无法使用复合索引

避免在低选择性的列上创建单列索引

  • 如果某个列只有少量不同值（如性别、布尔值等），不建议为这些列创建单独的索引。因为低选择性的索引很难显著提高查询效率，MySQL 在这种情况下仍可能选择进行全表扫描。

    示例：

    在 gender 列（仅有 male 和 female 两个值）上创建索引，几乎无法提高查询效率，因为每次查询都会匹配到大量数据。

使用覆盖索引

  • 覆盖索引 是指查询的所有数据都能从索引中直接获取，而不需要再访问实际的表数据（避免“回表”操作）。通过设计覆盖索引，可以显著提高查询的效率。

  • 示例：

    如果有如下查询：

    1

    SELECT age, department_id FROM employees WHERE name = 'John';

    可以为 (name, age, department_id) 创建复合索引，这样查询时只需要从索引中获取所需数据，而无需回表查找具体行数据。

索引的长度

  • 对于 VARCHAR 或 TEXT 类型的列，可以使用前缀索引，即仅对列的前几个字符进行索引，避免索引过长。

  • 示例：

    假设 email 列较长且不需要对整个字段进行索引，可以只索引前 10 个字符：

    1

    CREATE INDEX idx_email ON employees (email(10));

    这可以减少索引大小，但在前缀字符相同的情况下，可能会降低索引选择性。

避免在频繁更新的列上创建索引

  • 索引的维护需要付出一定代价。如果某个列经常进行更新操作，尽量避免为该列创建索引，否则每次更新该列时都会导致索引的重建，影响性能。

    示例：

    如果有个列记录员工的工作年限（years_of_service），而且该列每年都会更新，则不适合为该列创建索引，因为更新频率太高。

考虑排序和范围查询

  • 索引可以加速排序查询。如果查询中有 ORDER BY 操作，并且该排序列上有索引，MySQL 可以通过索引来优化排序而避免全表扫描。

  • 示例：

    对于如下查询：

    1

    SELECT * FROM employees WHERE department_id = 5 ORDER BY age DESC;

    可以为 department_id 和 age 创建复合索引 (department_id, age) 来加速查询。

  • 范围查询问题：在复合索引中，如果索引列涉及范围查询（如 > 或 <），索引中范围后的列将无法被有效使用。例如，以下查询使用复合索引 (age, salary)，但由于 age > 30 是范围查询，salary 列将无法利用索引：

    1

    SELECT * FROM employees WHERE age > 30 AND salary = 50000;

考虑业务场景设计索引

  • 索引设计需要结合实际业务查询场景，不是为每个可能的查询都设计索引。需要分析应用中最常见的查询模式，优先为这些高频查询优化索引设计。

    示例：

    如果大多数查询都是根据员工的 name 和 department_id 查询，应该为这些列设计索引，而非为不常用的列（如 address）创建索引。

使用合适的存储引擎

  • 不同的存储引擎（如 InnoDB、MyISAM）在索引的实现上有所不同。InnoDB 支持聚集索引，且主键索引是聚集索引，因此需要选择合适的存储引擎并根据存储引擎的特点设计索引。

    InnoDB：主键索引作为聚集索引，可以加速按主键查询的操作。因此，通常建议为 InnoDB 表创建合适的主键索引。

    MyISAM：不支持聚集索引，因此 MyISAM 中的主键索引不会影响数据的物理存储顺序。

总结

MySQL 索引设计是一个需要深思熟虑的过程，既要考虑查询性能，也要平衡插入和更新时的开销。以下是索引设计时应遵循的主要原则：

  • 为频繁出现在查询条件中的列创建索引。
  • 使用复合索引，遵循“最左前缀”原则。
  • 高选择性的列更适合作为索引。
  • 避免在低选择性或频繁更新的列上创建索引。
  • 利用覆盖索引加速查询。
  • 尽量避免过多的索引，保持合理的索引数量。

索引失效的情况

MySQL 索引可以极大提升查询性能，但在某些情况下索引可能会失效，导致查询无法利用索引进行高效的查找，而是进行全表扫描（FULL TABLE SCAN）。索引失效会影响数据库的性能优化，因此了解索引失效的常见原因非常重要。

以下是 MySQL 索引失效的常见情况：

1. 查询条件中的数据类型不匹配

当查询条件中的数据类型与索引列的数据类型不匹配时，索引可能会失效。MySQL 在处理时会进行隐式类型转换，这会导致无法利用索引。

示例

1
2

-- age 是一个 INT 类型的列
EXPLAIN SELECT * FROM employees WHERE age = '30';

由于 age 列是 INT 类型，但查询中使用了字符串 '30'，MySQL 会进行类型转换，从而导致索引失效，改为全表扫描。应避免隐式类型转换，保证查询条件中的数据类型与列的类型一致。

解决方法

1

EXPLAIN SELECT * FROM employees WHERE age = 30;

2. 使用 LIKE 时通配符在前

当使用 LIKE 关键字时，如果通配符（%）出现在字符串的开头，索引将无法使用，因为这会导致无法进行前缀匹配。

示例

1
2

-- name 列上有索引
EXPLAIN SELECT * FROM employees WHERE name LIKE '%John';

由于 % 出现在开头，MySQL 无法通过索引进行前缀扫描，只能执行全表扫描。

解决方法

1

EXPLAIN SELECT * FROM employees WHERE name LIKE 'John%';

此时，% 只在后面，MySQL 可以通过索引快速查找符合前缀 John 的数据。

3. 查询条件使用函数或表达式

如果查询条件中的索引列使用了函数或表达式，索引会失效。因为 MySQL 在使用索引时，直接查找的是索引列的原始值，而不是计算后的值。

示例

1
2

sql复制代码-- 对 birth_date 列进行了函数操作
EXPLAIN SELECT * FROM employees WHERE YEAR(birth_date) = 1990;

在这个查询中，YEAR(birth_date) 使用了函数，MySQL 无法利用索引，因为函数使得 MySQL 无法使用原始列值进行索引查找。

解决方法

将函数的运算移到查询条件的另一边，或者在表设计时创建一个冗余列来存储计算后的值。

1
2

-- 将计算放在查询外部
EXPLAIN SELECT * FROM employees WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';

4. 使用 OR 关键字且部分条件无索引

如果查询使用了 OR 关键字，并且 OR 的条件部分没有索引，索引可能会失效。MySQL 在处理 OR 时，需要所有的条件都能够利用索引，否则会进行全表扫描。

示例

1

EXPLAIN SELECT * FROM employees WHERE age = 30 OR department_id = 5;

如果 age 列有索引，但 department_id 没有索引，那么 MySQL 会对整个查询进行全表扫描。

解决方法

确保 OR 两侧的列都建立了索引，或者将 OR 分解成多个查询进行联合：

1
2

-- 为 department_id 列添加索引
ALTER TABLE employees ADD INDEX idx_department_id (department_id);

或者使用 UNION 来优化：

1
2
3
4

EXPLAIN
(SELECT * FROM employees WHERE age = 30)
UNION
(SELECT * FROM employees WHERE department_id = 5);

5. 不满足最左前缀原则（复合索引）

对于复合索引（多列索引），MySQL 使用的是“最左前缀匹配”规则。如果查询没有按照复合索引的最左列开始，索引将无法使用。

示例

假设为 employees 表的 (name, age, department_id) 列创建了复合索引：

1

EXPLAIN SELECT * FROM employees WHERE age = 30 AND department_id = 5;

由于查询中没有包含复合索引的第一列 name，所以索引无法使用。

解决方法

查询时必须从复合索引的最左列开始，才能有效使用索引：

1

EXPLAIN SELECT * FROM employees WHERE name = 'John' AND age = 30 AND department_id = 5;

6. 范围查询导致索引失效

在使用复合索引时，如果索引的某一列是范围查询（如 <, >, BETWEEN, LIKE），那么该列后面的索引列将无法继续使用。

示例

假设为 employees 表的 (name, age, department_id) 列创建了复合索引：

1

EXPLAIN SELECT * FROM employees WHERE name = 'John' AND age > 30 AND department_id = 5;

由于 age > 30 是范围查询，MySQL 只能使用复合索引的 name 和 age 列，department_id 列将无法被使用。

解决方法

如果可能，避免使用范围查询。或者，将常用的查询条件放在索引的前面。

7. 使用 IS NULL 或 IS NOT NULL

MySQL 在某些情况下无法使用索引处理 IS NULL 或 IS NOT NULL 的查询条件，导致索引失效。

示例

1

EXPLAIN SELECT * FROM employees WHERE department_id IS NOT NULL;

有些版本的 MySQL 对 IS NULL 和 IS NOT NULL 查询无法有效使用索引。

解决方法

尽量避免使用 IS NULL 或 IS NOT NULL 作为查询条件。如果查询条件中不可避免，需要测试查询计划，查看是否使用了索引。

8. 使用不等于（!= 或 <>）

不等于运算符（!= 或 <>）可能导致索引失效，因为 MySQL 需要遍历所有数据以找到不等的结果。

示例

1

EXPLAIN SELECT * FROM employees WHERE department_id != 5;

不等于的查询通常会导致 MySQL 进行全表扫描。

解决方法

如果可以，尽量避免使用 != 或 <>，可以通过范围查询来替代。

9. 使用函数索引

如果在查询中对索引列使用了函数或运算，MySQL 无法使用该索引进行查询。例如，使用 YEAR(), LENGTH(), UPPER() 等函数处理索引列会导致索引失效。

示例

1

EXPLAIN SELECT * FROM employees WHERE UPPER(name) = 'JOHN';

解决方法

可以通过增加冗余列或提前处理数据来避免索引失效：

1
2

-- 在应用层处理大小写转换
EXPLAIN SELECT * FROM employees WHERE name = 'John';

10. 查询的返回结果占比较大

当查询的返回结果占总数据的很大比例时，MySQL 可能会放弃使用索引，选择进行全表扫描，因为全表扫描的开销可能会比使用索引更小。

示例

1

EXPLAIN SELECT * FROM employees WHERE department_id IN (1, 2, 3);

如果 department_id 列上的索引返回了大量行，MySQL 可能会认为全表扫描效率更高。

解决方法

可以优化查询条件，减少返回的行数，或使用适当的索引策略。

总结

索引失效通常是由于查询条件中使用了函数、数据类型不匹配、不满足最左前缀原则、或使用了 LIKE、OR 等情况导致的。为了避免索引失效并保持查询性能的提升，建议：

  1. 保证查询条件中的数据类型与索引列的数据类型一致。
  2. 使用复合索引时，遵守最左前缀原则。
  3. 避免在查询条件中使用函数、OR、!=、IS NULL 等容易导致索引失效的情况。
  4. 合理设计和使用索引，避免不必要的索引失效，从而提升 MySQL 的查询性能。

性能分析

慢查询日志

MySQL 的慢查询日志（Slow Query Log）是一个强大的工具，用于记录执行时间超过特定阈值的 SQL 查询。这对于识别和优化性能瓶颈、提高数据库整体性能至关重要。以下是关于如何启用、配置、使用和分析 MySQL 慢查询日志的详细指南。

什么是慢查询日志

慢查询日志 是 MySQL 用于记录那些执行时间超过预设阈值的查询的日志。通过分析这些日志，可以识别出哪些查询对数据库性能影响最大，从而进行优化。

慢查询日志的用途

  • 性能优化：找出执行缓慢的查询，优化其结构或添加索引。
  • 资源监控：了解哪些查询消耗了大量的资源（如 CPU、内存、I/O）。
  • 查询调优：通过调整查询逻辑，减少不必要的全表扫描等高消耗操作。

启用慢查询日志

慢查询日志默认在 MySQL 中可能是禁用的。以下是启用慢查询日志的步骤：

1. 检查当前慢查询日志设置

首先，查看当前慢查询日志的状态：

1
2
3
4

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

2. 启用慢查询日志

你可以通过修改 MySQL 配置文件，/etc目录下（通常是 my.cnf 或 my.ini）来永久启用慢查询日志，或者通过运行时设置来临时启用。

a. 修改配置文件（永久启用）

编辑 MySQL 配置文件，添加或修改以下参数：

1
2
3
4
5

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON
  • slow_query_log：开启慢查询日志（ON 或 OFF）。
  • slow_query_log_file：指定慢查询日志文件的路径。
  • long_query_time：定义查询被认为是“慢”的时间阈值（以秒为单位）。例如，2 表示记录执行时间超过 2 秒的查询。
  • log_queries_not_using_indexes：记录未使用索引的查询，帮助识别缺少索引的查询。

保存配置文件后，重启 MySQL 服务以应用更改：

1
2
3
4
5

# 对于基于 systemd 的系统
sudo systemctl restart mysql

# 或者
sudo service mysql restart
b. 运行时设置（临时启用）

如果你不想重启 MySQL，可以通过运行时命令启用慢查询日志：

1
2
3
4

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

注意：这些设置在 MySQL 重启后会失效，除非在配置文件中进行永久设置。

3. 确保 MySQL 有写入权限

确保 MySQL 进程对指定的慢查询日志文件路径具有写入权限。例如：

1
2
3

sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log

配置慢查询日志

除了基本的启用设置，MySQL 提供了多种配置选项来细化慢查询日志的行为：

1. long_query_time

定义一个查询被视为“慢”的执行时间阈值（以秒为单位）。默认值通常是 10 秒，但建议根据实际情况调整。

1

long_query_time = 2

2. log_queries_not_using_indexes

记录那些未使用索引的查询，即使它们的执行时间低于 long_query_time。

1

log_queries_not_using_indexes = ON

3. min_examined_row_limit

仅记录检查行数超过此阈值的查询。默认是 0，表示不限制。

1

min_examined_row_limit = 1000

4. slow_query_log_use_global_control

在 MySQL 8.0.22 及更高版本中，可以使用此参数来统一控制慢查询日志的行为。

查看和分析慢查询日志

启用慢查询日志后，你可以使用多种方法查看和分析日志内容。

1. 使用 mysqldumpslow 工具

MySQL 提供了 mysqldumpslow 工具，用于汇总和分析慢查询日志。

示例命令
1

mysqldumpslow -s t /var/log/mysql/slow-query.log
  • -s t：按照查询时间排序（t 表示总时间）。
  • -t 10：仅显示前 10 条最慢的查询。
常用选项
  • -s：排序方式（t：总时间，l：锁定时间，r：返回行数，c：查询次数）。
  • -t：显示前 N 条结果。
  • -a：包括完整的查询文本，而不是摘要。
示例
1
2

# 按总执行时间排序，显示前 5 条
mysqldumpslow -s t -t 5 /var/log/mysql/slow-query.log

2. 使用 pt-query-digest 工具

Percona Toolkit 提供的 pt-query-digest 是一个功能强大的工具，用于详细分析慢查询日志。

安装 Percona Toolkit
1
2
3
4
5

# 对于基于 Debian/Ubuntu 的系统
sudo apt-get install percona-toolkit

# 对于基于 RHEL/CentOS 的系统
sudo yum install percona-toolkit
使用 pt-query-digest
1

pt-query-digest /var/log/mysql/slow-query.log
示例输出

pt-query-digest 会生成一个详细的报告，包含以下内容：

  • 总体摘要：慢查询的总数、总执行时间、平均时间等。
  • 查询分组：将相似的查询分组，并提供每组的统计信息。
  • 详细查询：每个查询的执行次数、总时间、锁定时间、返回行数等。
生成 HTML 报告
1

pt-query-digest /var/log/mysql/slow-query.log > report.txt

你也可以生成 HTML 格式的报告，便于浏览和分享：

1

pt-query-digest --output report /var/log/mysql/slow-query.log > report.html

然后，使用浏览器打开 report.html 查看详细报告。

3. 手动查看日志文件

如果日志文件不大，你可以使用常规文本编辑工具（如 less、grep、awk）进行查看和过滤。

示例
1
2
3
4
5

# 查看最近的 100 行
tail -n 100 /var/log/mysql/slow-query.log

# 查找特定表的查询
grep 'FROM your_table_name' /var/log/mysql/slow-query.log

注意：对于大型日志文件，手动分析可能不切实际，建议使用专用工具如 pt-query-digest。

使用工具分析慢查询日志

为了更高效地分析慢查询日志，可以借助一些第三方工具和平台，这些工具通常提供更友好的用户界面和高级分析功能。

1. Percona Toolkit

如前所述，pt-query-digest 是 Percona Toolkit 中的一个重要工具，适用于详细分析慢查询日志。

2. MySQL Enterprise Monitor

如果你使用的是 MySQL Enterprise Edition，可以使用 MySQL Enterprise Monitor 来监控和分析慢查询。

3. 开源工具

  • phpMyAdmin：对于小型日志文件，可以通过 phpMyAdmin 的接口查看和分析慢查询。
  • MySQL Workbench：提供查询分析和性能优化工具，支持慢查询日志的可视化分析。
  • Open Source Solutions：如 MonYog、Monyog 等，提供图形化的慢查询分析和监控。

4. 可视化工具

  • Grafana：结合 Prometheus 或其他数据源，可以可视化慢查询统计数据。
  • Kibana (ELK Stack)：通过 Logstash 收集和处理慢查询日志，使用 Kibana 进行可视化分析。

最佳实践

为了充分利用慢查询日志，以下是一些最佳实践：

1. 设置合理的 long_query_time

根据你的应用需求和数据库性能，设置一个合理的阈值。例如，对于大多数应用，long_query_time = 1 秒是一个常见的起点。

2. 记录未使用索引的查询

启用 log_queries_not_using_indexes，帮助识别缺少索引的查询，从而优化查询性能。

1

log_queries_not_using_indexes = ON

3. 定期分析慢查询日志

定期使用工具（如 pt-query-digest）分析慢查询日志，及时发现和优化新的性能瓶颈。

4. 清理和轮转日志文件

为了防止日志文件过大，配置日志轮转（log rotation）。可以使用工具如 logrotate 进行自动轮转。

示例 logrotate 配置

创建一个文件 /etc/logrotate.d/mysql-slow，内容如下：

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

/var/log/mysql/slow-query.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

5. 优化查询和索引

根据分析结果，优化高频率和高耗时的查询：

  • 重写查询：简化复杂的查询，减少子查询和联接。
  • 添加索引：为频繁查询的字段添加适当的索引，避免全表扫描。
  • 避免不必要的列：仅选择需要的列，减少数据传输量。
  • 使用缓存：对于高频访问的数据，考虑使用缓存机制（如 Redis）减少数据库负载。

6. 使用连接池

如果发现 Threads_connected 数量较高，可能需要使用连接池机制，减少频繁的连接和断开操作对性能的影响。

注意事项

1. 性能影响

启用慢查询日志会对数据库性能产生一定的影响，尤其是在高负载环境下。因此：

  • 最小化记录内容：仅记录必要的信息，避免记录过多不必要的数据。
  • 选择合适的存储位置：将慢查询日志存储在性能较高的存储介质上，避免成为 I/O 瓶颈。
  • 使用异步日志：如果可能，使用异步方式记录日志，减少对查询性能的影响。

2. 日志文件管理

确保有足够的磁盘空间存储慢查询日志，并定期轮转和清理，防止日志文件占满磁盘空间。

3. 安全和权限

保护慢查询日志文件的访问权限，防止敏感查询信息泄露。确保只有授权用户可以访问和查看日志文件。

4. 持续监控

将慢查询日志与其他监控工具结合使用，全面了解数据库性能。例如，结合 Performance Schema、MySQL Enterprise Monitor 或第三方监控工具，实现实时性能监控和预警。

结论

MySQL 的慢查询日志是优化数据库性能的重要工具。通过合理启用和配置慢查询日志，结合强大的分析工具，可以深入了解数据库的查询行为，识别和优化性能瓶颈，从而提升整体应用性能和响应速度。遵循上述最佳实践和注意事项，可以确保在使用慢查询日志的过程中，最大程度地发挥其价值，同时最小化对系统性能的影响。

Performance Schema

MySQL Performance Schema 是一个功能强大的工具，能够替代过时的 PROFILE 功能，用于收集和分析 MySQL 实例的性能数据。Performance Schema 能够提供全局、细粒度的数据，帮助开发者、DBA 更加全面地了解数据库性能问题。

以下是 Performance Schema 的详细介绍及使用方式，帮助你更好地替代 PROFILE 功能来进行性能调优。

Performance Schema 简介

Performance Schema 是 MySQL 5.5 版本引入的一个监控工具，主要用于收集数据库的各种运行时信息，如：

  • 等待事件（锁、文件 I/O、网络 I/O 等）
  • 查询执行时的 CPU 使用情况
  • 存储引擎的性能信息
  • 数据库事务的详细性能数据
  • 内存和资源的使用情况
  • 并发控制（锁、信号等）

Performance Schema 的数据是通过一系列预定义的表存储在名为 performance_schema 的数据库中，用户可以通过查询这些表来获取系统的性能信息。

启用 Performance Schema

大多数现代 MySQL 版本默认启用了 Performance Schema，可以通过以下命令来确认是否启用：

1

SHOW VARIABLES LIKE 'performance_schema';

如果返回的结果为 ON，则 Performance Schema 已启用；否则，可以在 MySQL 配置文件中（如 my.cnf）进行配置：

1
2

[mysqld]
performance_schema = ON

启用后，重启 MySQL 服务使配置生效。

Performance Schema 表结构

Performance Schema 中的所有信息都存储在 performance_schema 数据库中的表里。常用的表主要包括以下几类：

  • 事件表（Events Tables）：记录各种数据库事件（如等待、阶段、语句、事务等），包括 events_waits_current、events_stages_current 等。
  • 计数器表（Counters Tables）：记录不同类型事件的计数和时间数据，包括 events_waits_summary_by_event_name、events_stages_summary_by_account_by_event_name 等。
  • 监控表（Monitoring Tables）：监控线程、文件 I/O、表锁、用户和账户相关的性能数据，包括 table_io_waits_summary_by_table、file_summary_by_instance 等。

要查看 Performance Schema 的所有表，可以执行以下查询：

1

SHOW TABLES FROM performance_schema;

常见的 Performance Schema 表及其用途

下面介绍一些常用的表，帮助你理解查询 MySQL 性能的关键点：

events_statements_history 表

  • 用途：跟踪最近执行的 SQL 语句的历史记录，类似于 PROFILE 中跟踪查询执行的功能。它可以记录每条语句的执行时间、内存使用情况、错误状态等。
1
2

SELECT event_id, sql_text, timer_wait, lock_time, errors 
FROM performance_schema.events_statements_history;
  • event_id：事件的唯一标识。
  • sql_text：SQL 语句的内容。
  • timer_wait：执行该 SQL 语句所用的时间，单位为 picoseconds。
  • lock_time：执行语句时花费在锁上的时间。
  • errors：是否发生错误。

events_waits_current 表

  • 用途：用于监控当前正在等待的资源（如锁、I/O、网络等）。对于检测系统瓶颈很有用。
1

SELECT * FROM performance_schema.events_waits_current;

这个表记录了 MySQL 内部的等待事件信息，可以帮助你查找性能瓶颈，例如查询正在等待的锁定情况。

events_stages_current 表

  • 用途：显示当前执行的 SQL 语句的阶段信息。展示 SQL 各阶段的执行时间。
1

SELECT * FROM performance_schema.events_stages_current;

可以查看某个查询当前处于哪个执行阶段（如“Sending data”、“Sorting result”等），并且可以查看每个阶段的耗时情况。

threads 表

  • 用途：提供有关正在运行的线程的信息，类似于 SHOW PROCESSLIST，但比后者更加详细。
1

SELECT * FROM performance_schema.threads;

可以通过该表查看当前 MySQL 实例中所有活动线程的状态和执行情况。

table_io_waits_summary_by_table 表

  • 用途：跟踪表的 I/O 等待情况，帮助你识别 I/O 性能瓶颈。它统计每个表的读写操作等待时间，适用于优化表的读写性能。
1

SELECT * FROM performance_schema.table_io_waits_summary_by_table;

通过该表可以查看哪些表的 I/O 操作消耗时间最多，从而找到可能影响性能的表。

典型的 Performance Schema 使用场景

查询执行耗时分析

使用 events_statements_history 或 events_stages_history 表，查看最近的查询执行及其各个阶段的耗时。

1
2
3
4

SELECT sql_text, timer_wait 
FROM performance_schema.events_statements_history
ORDER BY timer_wait DESC
LIMIT 10;

该查询可以列出最近执行的 SQL 语句，并按执行时间排序，帮助你发现哪些查询耗时较长。

锁等待分析

锁等待会导致数据库性能下降，特别是在高并发的场景下。你可以通过 events_waits_summary_by_instance 表来分析锁等待情况。

1
2
3
4

SELECT event_name, SUM(timer_wait) AS total_wait_time
FROM performance_schema.events_waits_summary_by_instance
WHERE event_name LIKE 'wait/lock%'
GROUP BY event_name;

该查询可以帮助你找到哪些锁类型导致了性能瓶颈。

I/O 性能问题分析

使用 file_summary_by_instance 表，可以监控文件 I/O 的性能情况。

1
2
3
4

SELECT file_name, count_read, sum_timer_read, count_write, sum_timer_write 
FROM performance_schema.file_summary_by_instance
ORDER BY sum_timer_read DESC
LIMIT 10;

通过该查询可以找到文件读写操作的瓶颈，比如哪些文件的 I/O 操作占用了过多的时间。

内存和 CPU 使用情况分析

Performance Schema 还可以结合系统状态变量（如 performance_schema.memory_summary_global_by_event_name 和 performance_schema.threads）来分析 MySQL 内存和 CPU 的使用情况。

1
2
3
4

SELECT thread_id, processlist_user, processlist_host, processlist_db, 
       processlist_time, processlist_state 
FROM performance_schema.threads
WHERE processlist_time > 100;  -- 运行时间超过100秒的线程

可以用来识别哪些线程消耗了大量时间，有助于诊断高负载下的性能问题。

优化 Performance Schema 的使用

由于 Performance Schema 收集大量信息，可能对性能产生影响。因此可以通过配置调优它的开销：

  • 限制收集数据的范围：通过 setup_instruments 和 setup_consumers 表，可以选择性地启用或禁用某些性能数据的收集。
  • 调整数据采样频率：根据需要调整采样的详细程度，减少对系统性能的影响。
1
2

UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO' WHERE NAME = 'wait/sync/mutex/sql/LOCK_plugin';

这个命令禁用了特定的性能事件收集，从而减少开销。

总结

MySQL Performance Schema 是强大和灵活的工具，适合用于全面监控和分析 MySQL 的性能。通过对 Performance Schema 中各个表的查询，你可以更深入了解 SQL 查询的执行情况、锁等待、I/O 操作、内存和 CPU 的使用情况，从而优化数据库性能。

主要步骤：

  1. 确保 Performance Schema 启用。
  2. 使用 performance_schema 数据库中的表获取性能数据。
  3. 分析查询执行耗时、锁等待、I/O 瓶颈等信息。
  4. 在生产环境中，合理调整 Performance Schema 的开销，确保性能监控的平衡。

Performance Schema 是 MySQL 现代性能调优的核心工具，提供了全面的性能数据。

Explain 执行计划

MySQL 的 EXPLAIN 命令用于分析 SQL 查询的执行计划。它可以帮助你了解查询的执行方式、所使用的索引、扫描的行数以及是否存在性能瓶颈。通过分析执行计划，你可以优化查询性能，减少查询时间。EXPLAIN 可以用于 SELECT、DELETE、INSERT 和 UPDATE 语句，但最常用的是在 SELECT 查询上。

下面将详细介绍 EXPLAIN 的输出和如何分析这些结果。

如何使用 EXPLAIN

最简单的方式是将 EXPLAIN 加在查询前面：

1

EXPLAIN SELECT * FROM employees WHERE age > 30;

EXPLAIN 会返回一张表，包含多个字段，每个字段显示查询执行计划中的某些信息。

EXPLAIN 输出字段的详细解释

id

  • 描述：id 标识查询中的每个查询块或子查询。id 值相同表示这些操作是并行的，值不同则表示有执行顺序。
  • 应用：子查询或联合查询中会出现多个 id 值。id 值越大，优先级越低。

select_type

  • 描述

    ：表示查询的类型。常见类型有：

    • SIMPLE：简单查询（无子查询或联合查询）。
    • PRIMARY：主查询，查询中的最外层查询。
    • SUBQUERY：子查询。
    • DERIVED：派生表（子查询作为临时表）。
    • UNION：UNION 中的第二个及后续的查询。

table

  • 描述：显示当前操作正在访问的表名或别名。如果是子查询或派生表，会显示 <derivedN>，其中 N 是子查询的 id。

partitions

  • 描述：如果表是分区表，此字段会显示被访问的分区。没有分区表时，这一列为空。

type

  • 描述

    ：显示连接的类型，表示 MySQL 如何查找表中的行。类型越好，查询性能越高。常见类型有：

    • system：表只有一行（系统表）。
    • const：表最多有一行匹配，通常是主键或唯一索引查询。
    • eq_ref：对于每个来自前一张表的行，MySQL 只需访问一次匹配的行，通常用于主键或唯一索引上的连接。
    • ref：非唯一索引或主键查找，返回匹配的所有行。
    • range：使用索引进行范围扫描。
    • index：对整个索引进行扫描，类似于全表扫描。
    • ALL：全表扫描，性能最差。

示例：

1

EXPLAIN SELECT * FROM employees WHERE id = 1;

此查询会返回 const 类型，因为 id 是主键，并且查询会返回一行。

possible_keys

  • 描述：表示 MySQL 认为可能用于查询的索引。MySQL 根据表的结构和查询条件推测哪些索引可能对查询有用。

key

  • 描述：MySQL 实际使用的索引。如果该列为空，表示查询没有使用索引。

示例：

1

EXPLAIN SELECT * FROM employees WHERE name = 'John';

如果 name 列有索引，key 列会显示该索引的名称。

key_len

  • 描述：表示 MySQL 使用的索引的字节数。key_len 可以帮助你了解索引的使用情况。如果索引覆盖了多列，key_len 可能会显示部分列的长度。
  • 注意：key_len 是索引字段的最大可能长度，实际上可能不会使用这么多字节。

ref

  • 描述：显示使用哪个列或常量与 key 索引列进行比较，通常用于多表连接查询。

rows

  • 描述：MySQL 估算的需要读取的行数。该值是 MySQL 的估算值，不是实际行数。它帮助你评估查询的效率。
  • 示例：rows = 1000 表示 MySQL 认为该操作需要扫描 1000 行。

filtered

  • 描述：表示通过 WHERE 条件过滤后的行数百分比。通常结合 rows 来判断查询经过过滤后保留多少数据。

Extra

  • 描述

    ：提供查询的其他信息，常见值包括：

    • Using index：表示查询的覆盖索引，数据直接从索引中读取，避免了回表操作。
    • Using where：表示 MySQL 使用 WHERE 条件过滤数据。
    • Using temporary：表示查询使用了临时表，通常用于复杂的排序和分组查询。
    • Using filesort：表示查询使用了文件排序，MySQL 无法利用索引进行排序，性能较差。

EXPLAIN 使用示例

  • 示例 1：简单查询

    1

    EXPLAIN SELECT * FROM employees WHERE id = 1;

    输出结果：

    id select_type table type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE employees const PRIMARY PRIMARY 4 const 1 100.00 NULL

    分析：

    • type = const：表示这是主键查询，只返回一行，查询的效率最高。
    • rows = 1：表示 MySQL 预计只需扫描一行（因为 id 是主键）。
    • filtered = 100.00：表示没有经过 WHERE 子句的额外过滤，因为这是一个主键查询。
    • Extra = NULL：表示没有额外操作。

    示例 2：多表 JOIN 查询

    1
2
3
4
5

    EXPLAIN SELECT e.name, d.name 
FROM employees e 
JOIN departments d 
ON e.department_id = d.id 
WHERE e.age > 30;

    输出结果：

    id select_type table type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE d ALL PRIMARY NULL NULL NULL 5 100.00 Using where
    1 SIMPLE e ref department_id department_id 4 d.id 20 50.00 Using where

    分析：

    • 第一个表 departments 的 type 是 ALL，表示全表扫描，因为 departments 表没有合适的索引。filtered = 100.00 表示没有过滤，扫描了所有行。
    • 第二个表 employees 的 type 是 ref，表示 employees 表使用了 department_id 索引，进行了基于索引的连接。filtered = 50.00 表示只有 50% 的行通过了 WHERE 子句（age > 30）的过滤条件。

    示例 3：带索引的范围查询

    1

    EXPLAIN SELECT * FROM employees WHERE age > 30;

    输出结果：

    id select_type table type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE employees range age_index age_index 4 NULL 1000 20.00 Using where

    分析：

    • type = range：MySQL 使用了范围查询，表明 age_index 索引被用于扫描 age > 30 的范围。
    • rows = 1000：MySQL 预计会扫描 1000 行数据。
    • filtered = 20.00：表示经过 WHERE age > 30 过滤后，只有 20% 的数据符合条件，预计会返回约 200 行数据。
    • Extra = Using where：查询中使用了 WHERE 子句来过滤数据。

    示例 4：EXPLAIN 分析 ORDER BY

    1

    EXPLAIN SELECT * FROM employees ORDER BY age DESC;

    输出结果：

    id select_type table type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE employees index NULL age_index 4 NULL 1000 100.00 Using index

    分析：

    • type = index：表明 MySQL 使用了索引进行查询，并且直接从索引中获取数据，避免了回表操作。
    • rows = 1000：MySQL 预计需要扫描 1000 行数据。
    • filtered = 100.00：表示没有额外的 WHERE 条件过滤，所有行都经过索引扫描。
    • Extra = Using index：表明索引覆盖了查询，数据直接从索引中读取，无需访问表中的行数据。

总结

  • EXPLAIN 是 MySQL 查询优化的核心工具，通过分析执行计划，你可以深入了解查询的工作方式、索引使用情况以及可能的性能瓶颈。
  • 关键字段：type 字段表示查询的效率，ALL 是最差的，const 是最优的。key 和 possible_keys 可以告诉你索引的使用情况。Extra 则提供了额外的重要信息，如 Using filesort 和 Using temporary，这些通常表示性能问题。
  • 优化建议：通过 EXPLAIN 发现问题后，可以通过创建合适的索引、减少全表扫描、优化 JOIN 查询来提高查询性能。

SQL优化

插入优化

插入（INSERT）操作是数据库中常见的写操作之一，尤其是在处理大量数据批量插入时，优化插入操作可以显著提高数据库的写入性能。在 MySQL 中，插入操作的性能优化涉及多个方面，包括事务管理、索引策略、批量插入等。

1. 使用批量插入

如果你有大量数据要插入，最好使用批量插入而不是逐行插入。每次发送单个 INSERT 语句，MySQL 会为每一条记录执行独立的插入操作，这样会产生较高的开销（网络延迟、事务处理、日志写入等）。

示例

逐行插入的方式：

1
2

INSERT INTO employees (id, name, age) VALUES (1, 'John', 30);
INSERT INTO employees (id, name, age) VALUES (2, 'Jane', 28);

可以优化为批量插入：

1
2
3
4

INSERT INTO employees (id, name, age) VALUES 
(1, 'John', 30),
(2, 'Jane', 28),
(3, 'Mark', 35);

通过批量插入，MySQL 只需要处理一次事务和网络操作，能极大提高插入性能。

2. 关闭自动提交（autocommit）

MySQL 默认会在每次 INSERT 语句后自动提交事务（autocommit），这会导致每个 INSERT 都产生一次磁盘写操作。为了提高插入速度，可以通过手动控制事务，插入多条记录后再一次性提交。

优化步骤

  • 在插入大量数据时，先关闭自动提交。
1

SET autocommit = 0;
  • 然后插入数据。
1
2
3

INSERT INTO employees (id, name, age) VALUES (1, 'John', 30);
INSERT INTO employees (id, name, age) VALUES (2, 'Jane', 28);
...
  • 最后手动提交事务。
1

COMMIT;

通过这种方式，多个 INSERT 操作可以合并为一次磁盘写入，大幅度提升插入性能。

3. 延迟索引的创建

插入带有索引的数据时，MySQL 需要为每条记录更新相关索引，特别是当插入大量数据时，索引更新的开销会很大。为了优化插入性能，可以在批量插入数据之前禁用索引，插入完成后再重建索引。

优化步骤

  1. 删除索引： 在插入数据前，删除与插入列相关的索引。

    1

    ALTER TABLE employees DROP INDEX idx_name;

  2. 批量插入数据： 插入数据时避免索引更新的开销。

  3. 重建索引： 插入完成后，重新创建索引。

    1

    ALTER TABLE employees ADD INDEX idx_name (name);

这种方式适合一次性插入大量数据的场景，因为索引的重建只在插入完成后进行一次，避免了在插入过程中反复更新索引。

4. 使用 INSERT LOW_PRIORITY

MySQL 提供 INSERT LOW_PRIORITY，可以用于优化插入操作与读取操作的竞争。

  • INSERT LOW_PRIORITY：LOW_PRIORITY 则是推迟插入操作，直到没有其他查询在执行。它允许读取操作优先执行，适合读写混合的场景。

示例

1

INSERT LOW_PRIORITY INTO employees (id, name, age) VALUES (1, 'John', 30);

使用 LOW_PRIORITY 可以减少写操作对读操作的影响，特别是在高并发的读写操作中有明显优势。

5. 使用 LOAD DATA INFILE

如果需要导入大批量数据，LOAD DATA INFILE 比 INSERT 语句具有显著的性能优势。LOAD DATA INFILE 可以直接从文件中批量加载数据，大幅减少 SQL 解析和执行时间。

优化步骤

  1. 将数据存储到一个 CSV 文件中。
  2. 使用 LOAD DATA INFILE 导入数据：
1
2
3
4
5

LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
(id, name, age);

LOAD DATA INFILE 是 MySQL 中最快的批量插入方法，尤其是在数据量非常大的情况下。

6. 优化日志操作

MySQL 会将每次写操作记录在二进制日志和 InnoDB 的事务日志中，这对数据恢复非常重要，但会增加写入的开销。针对某些场景，可以暂时禁用某些日志功能来提高插入性能。

禁用二进制日志

  • 如果你不需要记录这些插入操作以进行复制或恢复，插入大量数据时可以临时禁用二进制日志：
1

SET SQL_LOG_BIN = 0;
  • 完成插入后，重新启用二进制日志：
1

SET SQL_LOG_BIN = 1;

优化 innodb_flush_log_at_trx_commit 参数

  • 默认情况下，innodb_flush_log_at_trx_commit = 1，表示每次事务提交后，MySQL 会将日志同步到磁盘，这提供了最高的数据安全性，但性能较低。为了优化插入性能，可以将其设置为 2，即每秒钟刷新日志而不是每次事务提交时刷新。
1

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

这种方式可以显著提高插入性能，但会有少量的数据安全风险（崩溃后丢失最近一秒内的事务）。

7. 使用合适的存储引擎

不同的存储引擎在处理插入操作时性能各不相同：

  • InnoDB：InnoDB 是 MySQL 默认的存储引擎，提供事务支持和行级锁，适合大多数应用场景。如果使用 InnoDB，需要确保正确配置，如调整 innodb_buffer_pool_size 以确保较好的性能。
  • MyISAM：MyISAM 插入速度相对较快，但它不支持事务，且锁粒度较大（表级锁）。如果对数据完整性要求不高且插入性能要求非常高，可以考虑 MyISAM。

选择合适的存储引擎可以有效提升插入性能，尤其是在数据一致性和写入速度之间权衡时。

8. 调整批量插入的大小

当执行批量插入时，批次的大小也会影响性能。如果一次插入的行数太少，不能充分利用批量插入的优势；但如果一次插入的行数太多，则可能会导致内存占用过高或网络传输瓶颈。

优化策略

可以通过实验找出适合你环境的批量插入大小。通常，批量插入几百行数据是比较理想的选择。

1
2
3
4
5
6

INSERT INTO employees (id, name, age) VALUES 
(1, 'John', 30),
(2, 'Jane', 28),
(3, 'Mark', 35),
...
(500, 'Alice', 29);

在不同数据库配置下，你可以调整批量插入的大小来找到最佳性能点。

9.顺序插入

顺序插入主要优化了以下方面的性能：

2.1 减少页分裂

InnoDB 的表数据按主键顺序存储在一个 B-Tree 中。如果插入的数据是顺序的，那么新行的数据总是插入到 B-Tree 的叶子节点的末尾。这样插入时：

  • 不需要移动现有的数据页。
  • 不需要对 B-Tree 进行页分裂（Page Split），页分裂是指当一页已满时，系统需要分裂该页，并重新平衡 B-Tree，这是一项较为耗时的操作。
  • 降低了磁盘的 I/O 操作和内存的重新分配。

相反，随机插入会导致 B-Tree 的频繁分裂，影响插入性能，因为新插入的数据可能需要被插入到 B-Tree 的中间位置，导致整个数据结构需要重新平衡。

2.2 减少磁盘碎片

顺序插入可以减少数据在磁盘上产生的碎片。当数据是顺序插入时，MySQL 会将数据按顺序放入同一个页中，避免了随机写入数据导致的页碎片问题。

  • 随机插入时，InnoDB 可能需要将数据写入不同的磁盘位置，增加了磁盘寻道时间。
  • 顺序插入有助于保持数据的局部性，减少磁盘 I/O 的消耗。

总结

通过优化 MySQL 插入操作，特别是当面对大量数据插入时，可以显著提高数据库的写入性能。以下是一些关键的优化策略：

  1. 批量插入：避免逐行插入，使用批量插入。
  2. 控制事务：关闭自动提交，手动控制事务的提交。
  3. 延迟索引更新：批量插入时，可以考虑暂时禁用索引。
  4. 使用 LOAD DATA INFILE：在大量数据导入时，LOAD DATA INFILE 是最快的方式。
  5. 优化日志配置：在需要时调整日志刷新策略或禁用二进制日志。
  6. 调整批量大小：根据环境配置合适的批量插入大小，避免内存和网络瓶颈。

这些优化方法适用于不同的场景，可以根据具体的业务需求进行组合使用，从而提升 MySQL 的插入性能。

主键优化

  1. 尽量降低主键的长度
  2. 插入时，顺序插入，选择自增主键
  3. 业务操作避免对主键修改

order by优化

在 MySQL 中，ORDER BY 操作可以通过两种方式进行：index 排序 和 filesort 排序。index 排序使用索引来完成排序，而 filesort 是 MySQL 用于处理无法通过索引完成排序的机制。理解这两者的差异，对于优化排序性能至关重要。

index 排序

当 MySQL 能够利用现有的索引顺序直接返回已排序的结果时，就使用了 index 排序。这是排序的最优方式，因为不需要额外的排序操作，直接从索引中提取有序数据即可。

何时使用 index 排序？

MySQL 在以下条件下会使用 index 进行排序：

  • 排序列是索引的一部分：如果 ORDER BY 的列已经有索引，MySQL 可以直接使用索引顺序返回数据，而不需要进行额外的排序计算。
  • 复合索引的最左前缀规则：如果查询中的 WHERE 子句和 ORDER BY 子句中的列匹配复合索引的最左列，MySQL 可以使用索引进行排序。
  • 单一排序方向：所有排序列的顺序（升序或降序）必须一致。如果查询中需要对多个列按不同方向排序，MySQL 将无法使用索引进行排序。

示例 1

假设我们有一张 employees 表，并且在 age 列上创建了索引：

1

CREATE INDEX idx_age ON employees(age);

执行以下查询时，MySQL 可以直接使用索引完成排序：

1

EXPLAIN SELECT * FROM employees ORDER BY age;
  • EXPLAIN 输出：在 Extra 列中会显示 Using index，表明 MySQL 使用了索引进行排序，效率很高。

示例 2

使用复合索引：

1

CREATE INDEX idx_dept_age ON employees(department_id, age);

执行以下查询时，MySQL 可以利用复合索引同时完成过滤和排序操作：

1

EXPLAIN SELECT * FROM employees WHERE department_id = 5 ORDER BY age;

因为 department_id 和 age 符合复合索引的最左前缀原则，MySQL 可以直接使用索引进行排序。

filesort 排序

当 MySQL 无法通过索引完成排序时，会使用 filesort 进行排序。尽管名称是 filesort，但它并不总是涉及磁盘操作。MySQL 会先尝试在内存中进行排序，如果数据量过大，才会将数据写入磁盘进行排序。

filesort 是一种额外的排序操作，性能相比 index 较低，尤其是对于大数据集来说，可能会对性能产生较大的影响。因此，尽量避免查询中使用 filesort，而是优化查询和索引设计，促使 MySQL 使用 index 排序。

何时使用 filesort 排序？

  • 没有适合的索引：当 ORDER BY 中的列没有适合的索引时，MySQL 无法直接利用索引排序，会使用 filesort。
  • 不符合最左前缀原则：对于复合索引，如果 ORDER BY 的列不符合最左前缀规则，MySQL 无法使用索引进行排序。
  • 排序方向不一致：如果多个列的排序方向不一致，MySQL 也无法使用索引排序，而是使用 filesort。

示例

如果没有在 age 列上创建索引，以下查询将使用 filesort 进行排序：

1

EXPLAIN SELECT * FROM employees ORDER BY age;
  • EXPLAIN 输出：Extra 列会显示 Using filesort，表示 MySQL 在进行额外的排序操作，这通常是性能的瓶颈。

示例 2

如果我们有一个复合索引 (department_id, age)，但查询中不符合最左前缀规则，比如以下查询：

1

EXPLAIN SELECT * FROM employees ORDER BY age;

因为 age 不是复合索引的最左列，MySQL 无法利用该索引进行排序，因此会使用 filesort。

filesort vs. index 的性能差异

  • 索引排序（index）的优势在于，它直接利用已排序的索引返回数据，因此没有额外的排序开销。
  • filesort 排序需要在查询执行的过程中额外进行排序操作，通常会消耗更多的 CPU、内存和磁盘资源，尤其是在数据量大的情况下。

总的来说，filesort 通常会比 index 慢，尤其当数据量较大时。使用 EXPLAIN 查看查询计划时，如果看到 Using filesort，说明 MySQL 执行了额外的排序操作，应该考虑通过索引优化来避免。

如何优化 filesort

既然 filesort 通常会带来性能瓶颈，以下是一些优化 filesort 的常见方法：

创建合适的索引

确保 ORDER BY 中的列有合适的索引，这样 MySQL 可以直接使用索引进行排序，避免 filesort。特别是当查询包含 WHERE 和 ORDER BY 时，考虑创建复合索引。

示例

1

CREATE INDEX idx_department_age ON employees(department_id, age);

这样，下面的查询将直接使用索引排序：

1

SELECT * FROM employees WHERE department_id = 5 ORDER BY age;

EXPLAIN 中的 filesort 与 index

通过 EXPLAIN 语句，可以查看 MySQL 在执行查询时是否使用了索引进行排序，还是使用了 filesort。

  • Using index：表示 MySQL 使用索引直接返回排序结果。
  • Using filesort：表示 MySQL 无法通过索引完成排序，使用了额外的排序操作。

示例

1

EXPLAIN SELECT * FROM employees ORDER BY age;

查看 EXPLAIN 输出中的 Extra 列，确保尽量避免 Using filesort，而是通过索引排序来提升性能。

总结

  • index 排序是最优的排序方式，MySQL 直接利用索引中的顺序返回排序结果，避免了额外的排序开销。
  • filesort 排序是 MySQL 在无法使用索引进行排序时采用的策略，它会带来额外的计算和 I/O 开销，尤其是在数据量较大时。
  • 优化 ORDER BY 的关键在于为排序列创建合适的索引，并确保查询和索引设计能够充分利用 MySQL 的排序优化能力。通过 EXPLAIN 语句分析查询计划，确保尽量避免 Using filesort，以提升查询性能。

group by优化

索引，多字段满足最左前缀法则

Limit优化

覆盖索引 + 子查询

update优化

  1. update后面的过滤条件 使用索引，没用索引的情况需要全表扫描，行锁退化成表锁
  2. 避免大量数据更新，即使有索引，inndb也会使用表锁

总结

大的方向是覆盖索引，降低回表，优化排序

锁

在 MySQL 中，锁机制是实现并发控制的重要手段，用于确保多个事务同时操作数据库时能够保持数据的一致性和完整性。MySQL 支持多种锁类型，锁的粒度越小，并发控制的性能就越好。在 MySQL 中，常见的锁包括表锁、行锁以及全局锁等。InnoDB 和 MyISAM 存储引擎对锁的支持有所不同，其中 InnoDB 支持行级锁，而 MyISAM 只支持表级锁。

接下来，我们将详细介绍锁的分类、使用场景及其优化方法，重点讲解表锁和行锁。

锁的分类

按锁的粒度分类

  • 表级锁（Table Lock）：锁定整个表，锁的粒度大，并发控制差。
  • 行级锁（Row Lock）：锁定表中的某一行，锁粒度小，可以提高并发性。行级锁主要由 InnoDB 存储引擎支持。

按锁的模式分类

  • 共享锁（Shared Lock，S 锁/读锁）：允许多个事务同时读取同一行或表，但不允许写操作。
  • 排他锁（Exclusive Lock，X 锁/写锁）：一个事务持有排他锁后，其他事务无法读取或写入被锁定的行或表。

元数据锁

元数据锁（Metadata Lock, MDL） 是 MySQL 中的一种重要的锁机制，用于确保在对数据库对象（如表、视图、存储过程等）进行DDL 操作（如 CREATE、DROP、ALTER 等）时，能够保证元数据的一致性和安全性。MySQL 的元数据锁在 MySQL 5.5 引入，旨在防止 DDL 操作与正在进行的查询、更新等操作发生冲突。

元数据锁的一个关键点是，它会在执行 DDL 操作时对表的元数据加锁，以确保对表结构修改的安全性。接下来我们将详细解释元数据锁的作用、使用场景以及可能带来的锁等待问题。

什么是元数据锁（MDL）

元数据锁是 MySQL 自动管理的一种锁，主要用于保护数据库对象的结构（表结构、字段、索引等），防止多个并发操作对相同的元数据对象进行冲突性操作。MDL 锁确保当某个线程对表结构进行修改时，其他线程不会同时操作该表的结构，避免数据不一致。

主要作用

  • 保证元数据的一致性：当一个事务正在访问某个表时，其他事务不能对该表进行结构上的修改（如 ALTER TABLE 或 DROP TABLE 等），保证数据的正确性。
  • 防止并发冲突：当有读写操作进行时，防止其他事务同时对表结构进行变更，避免表的结构在查询过程中被修改。

MDL 锁的特点

  • MDL 锁不需要显式加锁和解锁，它是在每次访问表、视图、存储过程等对象时，MySQL 自动加锁和释放。
  • MDL 锁会在事务开始时自动获取，并在事务提交或回滚后自动释放。
  • DML 操作（如 SELECT、INSERT、UPDATE、DELETE）也会触发 MDL 锁，用于保护表的元数据。

元数据锁的类型

MySQL 的元数据锁包括多种不同的模式，用于适应不同类型的操作。这些模式可以大致分为以下几类：

MDL 读锁（Shared Lock, S 锁）

读锁允许多个事务并发读取表的元数据，但不允许修改表的结构。对于 DML 操作（如 SELECT、INSERT、UPDATE、DELETE），MySQL 会对表的元数据加上读锁，以确保在事务执行期间，表的结构不会被修改。

  • 多个事务可以并发获取元数据的读锁。
  • 不允许获取写锁，即不能在获取了读锁后执行 ALTER TABLE 等修改表结构的操作。

MDL 写锁（Exclusive Lock, X 锁）

写锁是排他锁，允许一个事务修改表的元数据（例如 ALTER TABLE、DROP TABLE、RENAME 等），同时阻止其他事务读取或修改表的结构。在写锁持有期间，其他事务无法对该表进行 DDL 操作，也无法进行 DML 操作。

  • 只有一个事务能持有写锁，其他事务必须等待该锁释放。
  • 阻塞读锁和写锁的获取，即当有写锁时，其他事务不能访问表的元数据。

元数据锁的工作流程

当你在 MySQL 中执行对表的操作时，MySQL 会根据操作类型自动获取相应的元数据锁。典型的场景如下：

DML 操作触发 MDL 读锁

当一个事务执行 SELECT、INSERT、UPDATE 或 DELETE 等 DML 语句时，MySQL 会对表的元数据加读锁，确保该表的结构在查询期间不会被改变。

示例

1
2
3

BEGIN;
SELECT * FROM employees WHERE employee_id = 1;
-- 此时表 employees 的元数据被加上 MDL 读锁

此时，employees 表的元数据锁是一个读锁，其他事务可以继续读取表的数据，但无法执行修改表结构的操作（如 ALTER TABLE）。

DDL 操作触发 MDL 写锁

当执行 ALTER TABLE、DROP TABLE、RENAME TABLE 等 DDL 语句时，MySQL 会对表的元数据加上写锁。写锁是排他的，确保只有一个事务能够对表的元数据进行修改。

示例

1
2

ALTER TABLE employees ADD COLUMN age INT;
-- 此时表 employees 被加上 MDL 写锁

在写锁持有期间，其他任何事务都无法对该表进行查询、修改或结构变更，必须等待锁释放。

元数据锁的使用场景

读操作中的 MDL

当一个事务开始执行 SELECT 时，MySQL 会自动为表加上 MDL 读锁，确保该表的结构在查询期间不会发生变动。

  • 当一个事务长时间持有 MDL 读锁，其他试图执行 DDL 操作的事务将被阻塞，直到该事务完成。

DDL 操作中的 MDL

当你执行诸如 ALTER TABLE 的 DDL 操作时，MySQL 会为该表加上 MDL 写锁。MDL 写锁会阻止所有对该表的读写操作，直到结构修改完成。

  • 如果在持有 MDL 写锁期间有其他事务正在执行长时间查询或写入操作，DDL 操作将被阻塞，导致锁等待。

元数据锁引发的常见问题

锁等待和死锁

由于 MDL 锁是自动获取的，如果某个事务长时间不提交或回滚，那么其他事务的 DDL 操作将会陷入锁等待，导致性能问题。例如，长时间运行的查询会持有 MDL 读锁，导致对表进行 ALTER TABLE 等 DDL 操作的事务被阻塞。

示例问题

  • 一个事务执行长时间查询而不提交：
1
2
3

BEGIN;
SELECT * FROM employees WHERE employee_id = 1;
-- 未提交的事务持有 MDL 读锁
  • 另一个事务试图对表执行 ALTER TABLE 操作：
1
2

ALTER TABLE employees ADD COLUMN age INT;
-- 此 DDL 操作将被阻塞，直到上一个事务提交

DDL 操作的锁等待

当你执行 DDL 语句时，如果表正在被其他事务使用（例如进行查询），那么 DDL 语句会一直等待，直到持有读锁的事务完成。此时，DDL 操作将陷入锁等待，可能会导致其他请求的积压。

长时间未提交的事务

如果有事务长时间持有 MDL 锁且未提交或回滚，可能会引发死锁问题。虽然 MySQL 会自动检测死锁并中止其中一个事务，但这通常会导致应用性能下降或事务失败。

元数据锁问题的解决和优化方法

尽量减少长事务

为避免长时间持有 MDL 读锁，应该尽量减少事务的执行时间，及时提交事务。尤其是对于大查询、大更新操作，应该尽量使用分页查询或分批处理，避免事务长时间占用锁。

使用 pt-online-schema-change 工具

对于需要修改表结构的场景，使用 ALTER TABLE 可能导致锁等待或锁冲突。为避免这个问题，可以使用 Percona 提供的 pt-online-schema-change 工具，该工具通过创建临时表和增量数据同步来实现在线无锁表结构变更。

查看当前锁状态

MySQL 提供了一些系统表，用来查看当前的锁状态，包括元数据锁的状态。通过这些表可以诊断锁等待问题。

查看当前的锁信息
1

SELECT * FROM performance_schema.metadata_locks;

这条语句可以查看当前 MySQL 实例中所有持有的元数据锁，帮助诊断哪些事务导致了锁等待。

合理设置超时时间

可以通过设置事务超时时间，避免事务长时间等待元数据锁，提升数据库的并发性能。

设置锁等待超时时间
1

SET innodb_lock_wait_timeout = 30;  -- 设置锁等待超时时间为 30 秒

这个参数可以避免事务长时间陷入锁等待，超过设定时间后会自动回滚等待的事务。

总结

  • 元数据锁（MDL） 是 MySQL 自动管理的一种锁，用于保证表结构的一致性，防止多个事务同时对表的元数据（结构）进行并发操作

表锁（Table Lock）

表锁 是 MySQL 中的一种最基本的锁机制，锁定整个表，而不仅仅是某几行。表锁在 MyISAM 存储引擎中常见，而 InnoDB 存储引擎主要使用行锁。表锁会限制表上所有的操作，直到锁被释放。

2.1 表锁的特性

  • 锁定粒度大：表锁会锁住整个表，无论是对一行数据进行操作，还是对多行数据操作，都需要锁住整个表，导致并发性降低。
  • 兼容性差：如果有事务对表加了表锁，那么其他事务无法对该表进行读或写操作，必须等待锁释放。

2.2 表锁的两种类型

  • 读锁（READ LOCK）：当一个事务对表加读锁时，其他事务可以继续读该表，但不能写。读锁是共享锁，允许多个事务同时获取。
  • 写锁（WRITE LOCK）：当一个事务对表加写锁时，其他事务既不能读也不能写该表，直到写锁被释放。写锁是排他锁，只允许一个事务持有。

2.3 表锁的使用

表锁的主要使用场景是一些对性能要求较低的场景，比如备份数据或修改表结构时，可以使用表锁来避免数据的不一致。

读锁和写锁的示例
1
2
3
4
5
6
7
8

-- 对 employees 表加读锁
LOCK TABLES employees READ;

-- 对 employees 表加写锁
LOCK TABLES employees WRITE;

-- 解锁
UNLOCK TABLES;

注意：在 LOCK TABLES 之后必须手动执行 UNLOCK TABLES 来释放锁，否则表将一直被锁住，导致其他操作无法执行。

2.4 表锁的应用场景

  • 备份：在逻辑备份时，使用表锁确保在备份期间没有其他事务对数据进行修改。
  • 表结构修改：修改表结构（如 ALTER TABLE）时，通常会加表锁，防止其他事务读写该表。

行锁（Row Lock）

行锁 是 InnoDB 存储引擎的特性之一，与表锁相比，行锁的粒度更小，允许多个事务并发地操作不同的行，大幅提升并发性能。行锁是通过索引实现的，这意味着只有在使用索引查询时，才能精确锁定所需的行；如果没有索引，行锁会退化为表锁。

行锁的特性

  • 锁定粒度小：只锁定特定的行，未被锁定的行可以被其他事务并发操作。
  • 依赖索引：行锁是基于索引的，如果查询没有使用索引，InnoDB 无法锁定特定的行，将会锁住整个表。

行锁的两种类型

  • 共享锁（S 锁）：多个事务可以同时对同一行加共享锁，用于读操作，允许多个事务同时读取同一行，但不允许修改。
  • 排他锁（X 锁）：一个事务加了排他锁后，其他事务不能读取或修改该行。通常用于修改操作。

行锁的使用

行锁通常用于并发场景下的数据库操作，特别是在高并发系统中，行锁可以显著提高并发性能。

共享锁和排他锁的示例
1
2
3
4
5

-- 对某行加共享锁（读锁）
SELECT * FROM employees WHERE employee_id = 1 LOCK IN SHARE MODE;

-- 对某行加排他锁（写锁）
SELECT * FROM employees WHERE employee_id = 1 FOR UPDATE;
  • LOCK IN SHARE MODE：允许多个事务读取同一行。
  • FOR UPDATE：用于更新操作，锁定所选择的行，防止其他事务修改或读取这些行。

行锁的应用场景

  • 并发写操作：在高并发的系统中，使用行锁可以保证不同事务在操作相同表时能够互不干扰。
  • 事务中的数据一致性：行锁能够确保在一个事务完成之前，其他事务无法修改该事务正在操作的行，从而保证数据的一致性。

行锁的特殊机制

InnoDB 提供了一些特殊的行锁机制来防止并发事务之间的冲突和数据不一致问题，特别是在高并发或事务隔离级别较高的场景下：

间隙锁（Gap Lock）

间隙锁是一种用于防止幻读的行锁机制。间隙锁不仅锁定已经存在的行，还会锁定特定范围内的“间隙”，即使间隙中没有记录，也会被锁定，从而防止其他事务在这个间隙中插入新的行。

示例
1
2

-- 锁定 employee_id 在 1 和 10 之间的所有行
SELECT * FROM employees WHERE employee_id BETWEEN 1 AND 10 FOR UPDATE;

在该查询中，InnoDB 不仅会锁定 employee_id 为 1 到 10 的行，还会锁定它们之间的间隙，防止其他事务插入 employee_id 在 1 和 10 之间的新记录。

临键锁（Next-Key）

Next-Key 锁是 InnoDB 的默认锁机制，它是行锁和间隙锁的组合。Next-Key 锁锁定一个索引记录及其相邻的间隙，防止其他事务插入新记录，从而保证一致性。

意向锁（Intention Lock）

意向锁是 InnoDB 的一种表锁，用来表明某个事务打算在某些行上加行锁。意向锁分为意向共享锁（IS 锁）和意向排他锁（IX 锁）。意向锁不会阻塞其他的行锁操作，但它会阻止其他事务获取表级锁。

行锁的退化：行锁退化为表锁的情况

在某些情况下，行锁可能会退化为表锁，这会导致并发性能下降，常见的场景包括：

  • 查询未使用索引：如果查询没有使用索引，InnoDB 无法精确锁定特定的行，将会锁定整个表。
  • 锁定行数过多：当事务锁定的行数过多时，InnoDB 可能会将行锁升级为表锁以减少锁管理的开销。
  • 未命中索引：即使存在索引，如果查询中的条件未命中索引，InnoDB 也可能会退化为表锁。

全局锁（Global Lock）

全局锁是 MySQL 中一种特殊的锁机制，它会锁定整个 MySQL 实例，使所有数据库的写操作都无法执行。全局锁通常用于备份或维护操作，例如使用 FLUSH TABLES WITH READ LOCK。

使用场景

  • 备份操作：通过全局锁，可以确保在备份期间数据不会发生变化，保证备份的一致性。

示例

1
2
3
4
5

-- 获取全局锁
FLUSH TABLES WITH READ LOCK;

-- 释放全局锁
UNLOCK TABLES;

锁的监控与优化

MySQL 提供了一些工具来帮助监控锁的状态，主要包括 INFORMATION_SCHEMA 下的相关表，例如：

1

SELECT * FROM information_schema.innodb_locks;

通过这些工具，可以查看当前有哪些事务正在等待锁、持有锁，帮助诊断锁相关的性能问题。

锁的优化建议

  • 合理设计索引：确保查询条件使用了索引，避免行锁退化为表锁。
  • 减少事务锁的持有时间：尽量缩短事务的执行时间，避免长时间持有锁。
  • 分批处理大批量操作：对于大规模更新或删除操作，建议分批次进行，减少一次性锁定的行数。

总结

  • 表锁：锁定整个表，适用于 MyISAM 存储引擎。表锁粒度大，并发性能较差，通常用于备份或修改表结构的场景。
  • 行锁：InnoDB 存储引擎支持的锁类型，锁定粒度小，可以实现高并发。行锁通过索引实现，如果没有索引，行锁可能退化为表锁。
  • 间隙锁 和 Next-Key 锁：用于防止幻读问题，确保事务之间的隔离性。
  • 全局锁：用于维护操作，锁定整个 MySQL 实例，通常用于确保数据一致性的场景，如备份操作。

锁机制是 MySQL 并发控制的重要组成部分，合理设计数据库架构和索引，可以最大限度地减少锁冲突，提升系统的并发性能。

InnoDB引擎

逻辑存储结构

InnoDB 引擎的逻辑存储结构主要由 表空间（Tablespace）、段（Segment）、区（Extent） 和 页（Page） 组成。它通过这些层级结构管理数据的存储和检索。

表空间（Tablespace）

表空间是数据库中存储数据的最高层次单位，所有的表、索引等数据都存储在表空间中。InnoDB 可以有单独的表空间，也可以使用共享表空间（系统表空间）。

段（Segment）

段是表空间中的一个组成部分，InnoDB 将每种对象（表、索引等）对应的数据分配给不同的段。段的主要类型包括：

  • 数据段（Data Segment）：用于存储表的数据。
  • 索引段（Index Segment）：用于存储索引数据。
  • 回滚段（Rollback Segment）：用于存储事务回滚信息。

区（Extent）

区是段的组成部分，每个区默认大小为 1 MB，一个区由多个连续的页组成。InnoDB 按照区来分配空间。

页（Page）

页是 InnoDB 中最小的存储单位，每个页默认大小为 16 KB。页用于存储表的数据、索引数据等。常见的页类型有：

  • 数据页：用于存储行记录。
  • Undo 页：用于存储事务的回滚日志。
  • 系统页：用于存储系统信息。

逻辑存储结构的示意图

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

+-------------------------------+
|           表空间               |
|                               |
|  +-------------------------+  |
|  |           段             |  |
|  |                         |  |
|  |  +-------------------+  |  |
|  |  |       区           |  |  |
|  |  |                   |  |  |
|  |  |  +-------------+  |  |  |
|  |  |  |    页        |  |  |  |
|  |  |  |   (16KB)     |  |  |  |
|  |  |  +-------------+  |  |  |
|  |  |  |    页        |  |  |  |
|  |  |  |   (16KB)     |  |  |  |
|  |  |  +-------------+  |  |  |
|  |  +-------------------+  |  |
|  |                         |  |
|  +-------------------------+  |
|                               |
+-------------------------------+

行（Row）

行是存储在页中的最小单位，也是表中一条完整的记录。每个数据页可以存储多条行记录，行的存储结构较为复杂，尤其在涉及到行溢出、行锁和事务控制时。

每一行的存储内容包括：

  • 行记录数据：表的实际字段值。

  • 行格式：InnoDB 支持多种行格式，常见的包括 Compact、Redundant、Dynamic 和 Compressed。其中 Compact 和 Dynamic 是常用格式。

  • 隐藏列

    ：InnoDB 会为每一行记录添加隐藏列，用于行的元数据管理。包括：

    • DB_ROW_ID：用于唯一标识每一行。
    • DB_TRX_ID：记录修改该行的最后一个事务的 ID。
    • DB_ROLL_PTR：指向 Undo Log，用于事务回滚。

行在页中的存储示意图

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

+-------------------------------+
|           页 (16KB)            |
|                               |
|  +-------------------------+  |
|  | 行1: [数据, 隐藏列]       |  |
|  +-------------------------+  |
|  | 行2: [数据, 隐藏列]       |  |
|  +-------------------------+  |
|  | 行3: [数据, 隐藏列]       |  |
|  +-------------------------+  |
|  |       ...               |  |
|  +-------------------------+  |
|  | 行N: [数据, 隐藏列]       |  |
|  +-------------------------+  |
|                               |
+-------------------------------+

行的格式

  • Compact 格式：这是 InnoDB 的默认行格式，优化了存储空间，在字段较短时可以更高效利用页空间。
  • Dynamic 格式：允许将较大的数据（如 BLOB 或 TEXT 类型）存储在外部页，减少数据页的压力。
  • Redundant 格式：这是早期版本 InnoDB 的行格式，现已不常用。
  • Compressed 格式：用于压缩表或索引页，以节省存储空间。

行溢出

当一行的数据量过大，无法完整地存储在一个数据页中时，InnoDB 会将部分数据（例如 BLOB、TEXT）存储在专门的溢出页中。这种情况下，行记录中只保留部分指向溢出页的指针。

行在事务控制中的作用

InnoDB 是基于行级锁的存储引擎，这意味着它能够在多事务并发操作时对单独的行进行锁定，而不会锁定整个表。行锁是 InnoDB 实现高并发和事务隔离的重要机制。

结构总结

InnoDB 的存储结构可以由上到下这样理解：

  • 表空间 → 段 → 区 → 页 → 行

以下是完整的逻辑存储结构示意图：

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
26

+-----------------------------------------+
|               表空间                    |
|                                         |
|  +-----------------------------------+  |
|  |              段                   |  |
|  |                                   |  |
|  |  +-----------------------------+  |  |
|  |  |             区               |  |  |
|  |  |                             |  |  |
|  |  |  +-----------------------+  |  |  |
|  |  |  |         页             |  |  |  |
|  |  |  |                       |  |  |  |
|  |  |  |  +-----------------+  |  |  |  |
|  |  |  |  | 行1: [数据, 隐藏列] |  |  |  |
|  |  |  |  +-----------------+  |  |  |  |
|  |  |  |  | 行2: [数据, 隐藏列] |  |  |  |
|  |  |  |  +-----------------+  |  |  |  |
|  |  |  |         ...           |  |  |  |
|  |  |  |  +-----------------+  |  |  |  |
|  |  |  |  | 行N: [数据, 隐藏列] |  |  |  |
|  |  |  |  +-----------------+  |  |  |  |
|  |  |  +-----------------------+  |  |
|  |  +-----------------------------+  |
|  +-----------------------------------+  |
|                                         |
+-----------------------------------------+

通过这种分层次的结构，InnoDB 实现了数据的高效存储与管理，同时支持事务处理和行级别的并发控制。

说明

  1. 表空间 包含一个或多个 段。
  2. 段 包含多个 区，每个区占用 1 MB 的空间。
  3. 每个 区 由多个 页 组成，每个页的大小为 16 KB。
  4. 数据最终以 页 的形式进行存储。
CATALOG
    FEATURED TAGS
    网络 性能优化 内核 工具 耗时分析 调试 数据库 k8s 内存分析 协程 协议 工程 异步 文件系统 环境 编译选项 虚拟机 http rust学习 vpn wsl