likes
comments
collection
share

深入浅出MySQL核心名词

作者站长头像
站长
· 阅读数 4

在MySQL中，有很多专业名词，比如：覆盖索引，索引下推，共享锁，排他锁，间隙锁，聚簇索引等等。今天我们就来一起看看，这些名词到底蕴含着什么知识。

覆盖索引

覆盖索引是一种索引，它包含了查询所需的所有数据，而无需回表到实际的数据行。当一个查询可以完全通过索引满足，而无需访问实际的数据行时，就称为覆盖索引。覆盖索引的使用可以提高查询性能，因为它减少了对实际数据的访问次数，减少了IO操作，提高了查询的速度。

以下就是覆盖索引的典型例子

CREATE TABLE users (
   id INT PRIMARY KEY,
   username VARCHAR(255),
   phone VARCHAR(20),
   address VARCHAR(255)
);

我建了一个username+phone的联合索引，当我执行以下SQL语句的时候，则会覆盖索引。

select id,username,phone from users where username = 'zhangsan';

因为id，username,phone在索引上都已存在，而不需要去回表查询。这则是覆盖索引。

回表

当我们需要查询的字段，在索引上没有，或者说索引上并没有我们想要查询的字段时，MySQL会拿着索引上的主键值去主键索引上得到相关的记录，这则是回表。

例如:

select * from users where username = 'zhangsan';

username与phone的联合索引上并没有address的属性值。那么从联合索引上得到 id、username、phone属性，拿着id再去聚簇索引上得到address，这则是回表。

索引下推

MySQL5.6版本出来的特性，可以在对联合索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，能有效的减少回表次数。

还是已上面的例子距离

假设表中有这两条记录

usernamephoneaddress
张三13062900000a
张四15090901111b

SQL语句如下:

select * from users where username like username '张%' and phone '130%'

如果没有索引下推执行是这样的，存储引擎根据联合索引username查到2条记录，张三与张四，拿着2个主键主键ID去进行回表得到所有需要的字段，返回server层。server层在根据phone进行筛选过滤，得到一条记录。这其中回表了2次。

如果有索引下推执行是这样的，存储引擎根据联合索引username查到2条记录，恰巧phone也在联合索引上，此时就根据phone进行筛选过滤，得到1条记录，再去回表得到其他的字段记录。这其中只回表的1次。大大提高了性能，线上数据，如果以张开头的名字有1w呢，10w呢，100w呢，这要回表多少次？所以索引下推提高性能毋庸置疑。

聚簇索引（聚集索引）

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使我们没有创建主键，MySQL也会帮我们创建一个隐式的主键。 这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。 这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引（聚簇索引）。 InnoDB的主键索引就是聚集索引，MySAM则不是。

非聚簇索引（非聚集索引）

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键及该列的值，想要查找其他数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

排他锁 (X锁/写锁)

排他锁是一种独占锁，它阻止其他事务同时获取相同资源的锁。一旦一个事务获取了排他锁，其他事务将无法同时获取相同资源的锁，直到拥有排他锁的事务释放锁。

假设我们有一个简单的银行账户表 accounts：

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

INSERT INTO accounts (account_id, balance) VALUES (1, 1000), (2, 2000);

现在，假设用户A要执行一笔转账操作，需要锁定源账户，执行转账，然后释放锁。这里使用 FOR UPDATE 语句来获取排他锁：

-- 用户A开始转账操作，锁定源账户
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 执行转账操作，更新源账户余额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 完成转账操作，释放锁
COMMIT;

在这个例子中，SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE; 语句获取了对账户ID为1的排他锁。在整个事务期间，其他事务无法获取账户ID为1的排他锁，防止并发的修改。

共享锁（S锁/读锁）

共享锁是一种允许多个事务同时获取的锁，但是它们是与排他锁互斥的。多个事务可以同时持有共享锁，表示它们只是读取数据而不修改，互不干扰

假设一个图书馆系统的书籍表 books：

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    available_copies INT
);

INSERT INTO books (book_id, title, available_copies) VALUES (1, 'Introduction to SQL', 5), (2, 'Data Science Basics', 3);

多个用户可以同时读取同一本书的信息。在这里，我们使用 LOCK IN SHARE MODE 语句获取共享锁：

-- 用户B开始借阅操作，锁定书籍
START TRANSACTION;
SELECT * FROM books WHERE book_id = 1 LOCK IN SHARE MODE;

-- 读取书籍信息，不影响其他用户的读取
-- ...

-- 完成借阅操作，释放锁
COMMIT;

在上面这个例子中，SELECT * FROM books WHERE book_id = 1 LOCK IN SHARE MODE; 语句获取了对书籍ID为1的共享锁。其他事务可以同时获取相同书籍的共享锁，以便并发地读取书籍信息。