MySQL索引和锁

数据库
4407 words
Page content

索引失效的情况

create payment table:

create table payment_tab {
    `id` bigint unsigned auto_increment,
    `payment_id` bigint unsigned  not null,
    `user_id` bigint unsigned not null,
    `create_time` int unsigned  not null,
    `update_time` int unsigned  not null default 0,
    `amount` bigint  not null default 0,
    `payment_status` tinyint  not null default 0,
    `ref` varchar(64)  default NULL,
    `payment_type` tinyint not null default 0,
    primary key (`id`), -- 主键
    `idx_user_id` (`user_id`),
    `uk_payment_id` (`payment_id`), -- 唯一索引
    `idx_create_time` (`create_time`), -- 普通索引
    `idx_update_time` (`update_time`), -- 普通索引
    `idx_ref_payment_type` (`ref`, `payment_type`) -- 复合索引
} ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;;
  1. NULL值 - NULL值不参与索引
    • explain select * from payment_tab where ref is not null;
  2. OR运算符: OR运算符左右两边的条件必须都能使用索引
    • explain select * from payment_tab where ref = "a" or payment_status = 0
  3. !=、<>、NOT IN、NOT EXISTS、 between运算符
    • explain select * from payment_tab where ref != "a"
    • explain select * from payment_tab where ref <> "a"
    • explain select * from payment_tab where ref not in ("a", "b", "c")
    • explain select * from payment_tab where ref not exists (select 1 from payment where ref = "a")
    • explain select * from payment_tab where ref between "a" and "b"
  4. LIKE运算符的前缀模糊匹配
    • 当like前缀没有%，后缀有%时，索引有效;
    • explain select * from payment_tab where ref like "%xyz"
  5. 在索引列上使用mysql的内置函数，索引失效
    • explain select * from payment_tab where ref = UPPER("abc")
    • explain select * from payment_tab where SUBSTR(ref,1,3) = '100';
    • explain select DISTINCT(ref) from payement
  6. HAVING子句：HAVING子句不能使用索引
    • explain select ref, count(*) from payment_tab group by ref having count(*) > 1
  7. ORDER BY子句：在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引
  8. 复合索引使用不正确：
    • idx_col1_col2, but order by col1 desc, col2 asc
    • 复合索引未用左列字段，即不是使用第一列索引，索引失效
  9. 类型转换导致索引失效
    • explain select * from payment_tab where convert(id, char) = '666';
    • 类型隐式转换: explain select * from payment_tab where ref = 1002;
  10. 对索引列运算（如，+、-、*、/），索引失效。
    • explain select * from payment_tab where id + 1 = 666
  11. 两列做比较: 如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。
    • explain select * from payment_tab where update_time > create_time
  12. DBMS发现全表扫描比走索引效率更高，因此就放弃了走索引。 也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。 某些场景下即便强制SQL语句走索引，也同样会失效。 类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。

一般，在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过五分之一时，则使用全表扫描了。

InnoDB 的锁的类型

  • 表锁：TLOCK - 表锁，只有获取到表锁才能对表进行读写操作。
    1. lock table payment write (TLOCK)
    2. unlock tables
  • 共享锁：SLOCK - 共享锁，允许其他事务对数据进行读操作，但是不允许其他事务对数据进行写操作。
    1. select * from payment where id=1 lock in share mode (SLOCK)
  • 排他锁：XLOCK - 排他锁，不允许其他事务对数据进行读操作，也不允许其他事务对数据进行写操作。
    1. select * from payment where id=1 for update (XLOCK)
    2. update payment set amount=100 where id=1
  • 意向共享锁：ISLOCK - 意向共享锁，表示事务想要获取表中某个数据的共享锁。
  • 意向排他锁：IXLOCK - 意向排他锁，表示事务想要获取表中某个数据的排他锁。

InnoDB锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB存储引擎的锁的算法有三种：

  • Record lock：单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

  1. innodb对于行的查询使用next-key lock
  2. Next-locking keying为了解决Phantom Problem幻读问题
  3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
  4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
  5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

InnoDB行锁分为3种情形。

  • Record lock：对索引项加锁。是对索引记录本身加锁，避免其他事务修改这个记录。
  • Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。

Gap 锁主要是针对非唯一索引的范围查询操作而产生的。当一个事务对索引记录进行范围查找时，为了防止其他并发事务在该范围内插入新记录，InnoDB 会在索引间隙上设置 gap 锁。具体来说，gap 锁会锁定索引范围内的间隙（gap），同时排斥新记录插入索引范围内的间隙。

下面举一个例子来说明 gap 锁的发生情况。假设有如下表 t1，它包含一个主键 ID 和一个非唯一索引 key1：

CREATE TABLE t1 ( id INT PRIMARY KEY, key1 INT ) ENGINE=InnoDB;

插入一些数据：

INSERT INTO t1 (id, key1) VALUES (1, 10), (2, 20), (3, 30), (4, 40);

然后执行一个范围查询操作：

SELECT * FROM t1 WHERE key1 > 10 AND key1 < 30 FOR UPDATE;

这个查询操作的范围是 key1 大于 10 小于 30，它涉及到了索引范围 (10,30] 和间隙 [10,20)。此时，InnoDB 会在间隙 [10,20) 上设置 gap 锁，以排斥其他事务在该间隙内插入新记录。

  • Next-key lock：前两种的组合，对记录及其前面的间隙加锁。是对索引记录之间的空间和索引记录本身加锁，避免其他事务在这个空间插入新的记录或者修改这个记录。

MySQL InnoDB 存储引擎中的 Next-Key 锁通常发生在使用非聚簇索引进行范围查找、等值查询和唯一键查询操作时，它包括索引记录锁和间隙锁。下面以一个具体的例子来解释 Next-Key 锁的发生情况。

假设有如下表 t1，它包含一个主键 ID 和索引 KEY1：

CREATE TABLE t1 (
    id INT PRIMARY KEY,
    key1 INT UNIQUE
) ENGINE=InnoDB;

现在对该表插入一些数据：

INSERT INTO t1 (id, key1) VALUES (1, 10), (2, 20), (3, 30), (4, 40);

接下来执行如下查询操作（假设事务隔离级别为“可重复读”）：

SELECT * FROM t1 WHERE key1 > 10 AND key1 < 30;

这个查询的范围是 key1 大于 10 小于 30，查询的结果应该是 id 为 2 的记录。在 InnoDB 存储引擎中，该查询操作使用了索引 KEY1 进行范围查找，它会在 KEY1 上加锁，范围是 (10, 30]，其中 [10,20) 和 (20,30] 都是间隙。

此时，InnoDB 存储引擎会给 key1=20 的记录加一个 Next-Key 锁，因为这是间隙和记录的分界线，以防止一个新的 20 被插入到该间隙中。同时，因为在“可重复读”隔离级别下进行操作，所以这个 Next-Key 锁会持续到事务结束。如果在这个事务中有其他查询操作需要锁定该间隙或行记录，就会被阻塞，直到当前事务结束并释放锁。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！

  • 在不通过索引条件查询时，InnoDB会锁定表中的所有记录。
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  • 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

为什么 MySQL 使用 B+ 树？

是面试中经常会出现的问题，很多人对于这个问题可能都有一些自己的理解，我们在这篇文章中就会深入分析 MySQL 选择 B+ 树背后的一些原因。

InnoDB 存储引擎

MySQL 跟 B+ 树没有直接的关系，真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB，MySQL 中存储引擎的主要作用是负责数据的存储和提取，除了 InnoDB 之外，MySQL 中也支持 MyISAM 作为表的底层存储引擎。

今天最终将要分析的问题其实是: 为什么 MySQL 默认的存储引擎 InnoDB 会使用 B+ 树来存储数据？

具体的数据表中的数据（主键索引）还是辅助索引最终都会使用 B+ 树来存储数据，其中前者在表中会以 <id, row> 的方式存储，而后者会以 <index, id> 的方式进行存储，这其实也比较好理解：

  • 在主键索引中，id 是主键（pk），我们能够通过 id 找到该行的全部列；
  • 在辅助索引中，索引中的几个列构成了键，我们能够通过索引中的列找到 id，如果有需要的话，可以再通过 id 找到当前数据行的全部内容；

对于 InnoDB 来说，所有的数据都是以键值对的方式存储的，主键索引和辅助索引在存储数据时会将 id 和 index 作为键，将所有列和 id 作为键对应的值。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

B+ 树的优点

MySQL 使用 B+ 树作为其主要索引结构，主要基于以下几个优点：

优化磁盘IO操作： B+ 树的设计上能够最大化地减少磁盘IO操作。对于数据库应用，磁盘IO操作通常是最大的性能瓶颈。由于B+ 树内部节点不存储数据，每个内部节点能够存储更多的键，使得树的高度更低，磁盘读写次数相应减少。

范围查询效率高： B+树的所有叶子节点中存放了全部的关键字信息，以及指向含有这些关键字的数据记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接。只需要找到范围的最小元素，然后沿着叶子节点的链表顺序扫描即可。

查找性能稳定： B+树的每一个节点都包含了全部的键值，且每次分裂节点时都会将节点的信息一半给新的节点，保证了每个节点的密度稳定，这样在查找过程中，每一次查找路径长度的差异性都不会很大，查找性能稳定。

B+树 vs B树

为什么 MySQL 使用 B+ 树，而不是B树？

MySQL选择使用B+树作为索引结构，而非B树，主要有以下原因：

查询效率更稳定：在B树中，数据可以存储在内部节点和叶子节点上，这意味着对于某些数据，可能只需要访问到某个内部节点就能查找到，然而其他数据可能需要一直查找到叶子节点。这导致了不同数据的查询路径长度可能会有较大差异，查询效率波动较大。相反，在B+树中，所有数据都存储在叶子节点上，这保证了所有键值的查询路径长度相同，查询效率更稳定。

更适合范围查询：在B树中，数据可以存储在内部节点和叶子节点上；在B树中，范围查询可能需要在树的各层之间多次跳转，导致非连续的磁盘IO和性能下降。

磁盘I/O操作更少：由于B+树的所有键值都存储在叶子节点，并且B+树的非叶子节点相对于B树可以存储更多的键，这使得B+树的高度通常比B树更低，可以减少磁盘I/O操作。

因此，考虑到查询效率的稳定性、范围查询的效率以及磁盘I/O操作的数量，MySQL选择了B+树作为其索引结构。

B+树 vs 红黑树

为什么 MySQL 使用 B+ 树，而不是红黑树？

磁盘I/O操作更少：在 B+ 树中，数据都存储在叶子节点，并且每个节点可以包含多个键值对，因此查询一个数据时只需遍历树的少数几层。 而在红黑树中，每个节点只包含一个键值对，需要遍历的节点数量通常比 B+ 树多，从而导致更多的磁盘I/O操作。

更适合范围查询： B+ 树的叶子节点之间通过链表互相连接，这使得 B+ 树非常适合执行范围查询。只需要找到范围的最小元素，然后沿着叶子节点的链表顺序扫描即可。然而在红黑树中，执行范围查询则需要在树的各层之间多次跳转，效率较低。

更好的利用磁盘预读性能： 数据库系统普遍采用磁盘预读机制，一次性读取一个数据块（例如4KB大小），并且连续的磁盘空间读取性能更好。 B+树的结构使得范围查询等操作能够最大程度地利用这一特性，因为其子节点数据在磁盘上是连续存放的。而红黑树的数据在磁盘上是随机分布的，不能很好地利用磁盘的预读特性。

因此，基于磁盘I/O操作的考虑，以及对范围查询和磁盘预读性能的支持，MySQL没有选择红黑树作为其索引结构。