MySQL进阶-存储引擎、索引、锁、事务

2022-10-20 20:54:14

存储引擎

1、概述

MySQL 的存储引擎是插件式的，用户可以根据实际的应用场景，选择最佳的存储引擎。MySQL默认支持多种存储引擎，以适应不同的应用需求。

MySQL 5.7 支持的存储引擎有：InnoDB、MyISAM、MEMORY、CSV、MERGE、FEDERATED 等。从 5.5.5 版本开始，InnoDB 成为 MySQL 的默认存储引擎，也是当前最常用的存储引擎，5.5.5 版本之前，默认引擎为 MyISAM。创建新表时，如果不指定存储引擎，MySQL 会使用默认存储引擎。

查看数据库当前的默认引擎：

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

查看数据库当前所支持的存储引擎：

mysql> show engines\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 6. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

每一行的含义大致如下：

  • Engine：存储引擎名称；
  • Support：不同值的含义为：
    ·DEFAULT：表示支持并启用，为默认引擎；
    ·YES：表示支持并启用；
    ·NO：表示不支持；
    ·DISABLED：表示支持，但是被数据库禁用。
  • Comment：存储引擎注释；
  • Transactions：是否支持事务；
  • XA：是否支持XA分布式事务；
  • Savepoints：是否支持保存点。

创建表时，ENGINE 关键字设置表的存储引擎

mysql> create table a (id int) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> create table b (id int) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

##  表 a 的存储引擎为 InnoDB，表 b 的存储引擎为 MyISAM。

查看表的相关信息

show table status 命令查看表的相关信息。

mysql> show table status like 'a'\G
*************************** 1. row ***************************
           Name: a
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-04-21 02:29:06
    Update_time: 2020-04-29 00:24:17
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

每一行的含义大致如下：

  • Name：表名；
  • Engine：表的存储引擎类型；
  • Version：版本号；
  • Row_format：行的格式;
  • Rows：表中的行数；
  • Avg_row_length：平均每行包含的字节数；
  • Data_length：表数据的大小（单位字节）；
  • Max_data_length：表数据的最大容量；
  • Index_length：索引的大小（单位字节）；
  • Data_free：已分配但目前没有使用的空间，可以理解为碎片空间（单位字节）；
  • Auto_increment：下一个 Auto_increment 值；
  • Create_time：表的创建时间；
  • Update_time：表数据的最后修改时间；
  • Check_time：使用check table命令，最后一次检查表的时间；
  • Collation：表的默认字符集和字符列排序规则；
  • Checksum：如果启用，保存的是整个表的实时校验和；
  • Create_options：创建表时指定的其他选项；
  • Comment：表的一些额外信息。

2、常用存储引擎的对比

MySQL进阶-存储引擎、索引、锁、事务

3、InnoDB 存储引擎

从 5.5 版本开始，InnoDB 是MySQL的默认事务性引擎，也是最重要、使用最广泛的存储引擎。InnoDB 具有提交、回滚、自动崩溃恢复的事务安全保障，拥有独立的缓存和日志，提供行级别的锁粒度和强大的并发能力。

在大多数使用场景中，包括事务型和非事务型存储需求，InnoDB 都是更好的选择，除非有非常特别的原因需要使用其他存储引擎。

自动增长列

InnoDB 表的自动增长列，插入的值可以为空，也可以人工插入，如果插入的值为空，则实际插入的值是自动增长后的值。

下面定义表 t1，字段 c1 为自动增长列，对该表进行 insert 操作，可以发现，当插入值为空时，实际插入的值是自动增长后的值。

mysql> create table t1(
    ->     c1 int not null auto_increment,
    ->     c2 varchar(10) default null,
    ->     primary key(c1)
    -> ) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1(c1,c2) values(null,'1'),(2,'2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

InnoDB中，自增长列必须是索引，同时必须是索引的第一个列 。如果不是第一个列，数据库会报出异常

mysql> create table t2(
    ->     c1 int not null auto_increment,
    ->     c2 varchar(10) default null,
    ->     key(c2,c1)
    -> ) engine = innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table t2(
    ->     c1 int not null auto_increment,
    ->     c2 varchar(10) default null,
    ->     key(c1,c2)
    -> ) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

主键和索引

InnoDB 表是基于聚簇索引建立的，聚簇索引也叫主索引，也是表的主键，InnoDB 表的每行数据都保存在主索引的叶子节点上。InnoDB 表必须包含主键，如果创建表时，没有显式指定主键，InnoDB 会自动创建一个长度为 6 个字节的 long 类型隐藏字段作为主键。所有的 InnoDB 表都应该显式指定主键。

InnoDB 表中，除主键之外的索引，叫做二级索引。二级索引必须包含主键列，如果主键列很大的话，其他的所有索引都会很大。因此，主键是否设计合理，对所有的索引都会产生影响。

主键的设计原则大致如下：

  • 满足唯一和非空约束；
  • 主键字段应尽可能地小；
  • 主键字段值基本不会被修改；
  • 优先考虑自增字段，或查询最频繁的字段。

存储方式

InnoDB 存储表和索引的方式，有以下两种：

  • 独享表空间的存储方式： 表结构保存在 .frm 文件中，每个表的数据和索引单独保存在 .ibd 文件中；
  • 共享表空间的存储方式： 表结构保存在 .frm 文件中，数据和索引保存在表空间 ibdata 文件中。

使用共享表空间时，随着数据的不断增长，表空间的维护会越来越困难，一般情况，都建议使用独享表空间。可以通过配置参数 innodb_file_per_table 来开启独享表空间。

innodb_file_per_table = 1 #1为开启独享表空间

使用独享表空间时，可以很方便对单表进行备份和恢复操作，但是直接复制 .ibd 文件是不行的，因为缺少共享表空间的数据字典信息，但是可以通过下面的命令，实现 .ibd 文件和 .frm 文件能被正确识别和恢复。

alter table xxx discard tablespace;
alter table xxx import tablespace;

4、MyISAM

4.1 特性

  • 加锁与并发

MyISAM 可以对整张表加锁，而不是针对行。读数时会对表加共享锁，写入时对表加排它锁。在表有读取查询的同时，也可以对表进行插入数据。

  • 延迟更新索引键

创建 MyISAM 表时，可以指定 DELAY_KEY_WRITE 选项，在每次更新完成时，不会马上将更新的索引数据写入磁盘，而是先写到内存中的键缓冲区，当清理键缓冲区或关闭表的时候，才将对应的索引块写入磁盘。这种方式可以极大地提升写入性能。

  • 压缩

可以使用 myisampack 工具对 MyISAM 表进行压缩。压缩表可以极大地减少磁盘空间使用，从而减少磁盘 IO，提升查询性能。压缩表时不能进行数据的修改。表中的记录是独立压缩的，读取单行时，不需要解压整个表。

一般来说，如果数据在插入之后，不再进行修改，这种表比较适合进行压缩，如日志记录表、流水记录表。

  • 修复

针对 MyISAM 表，MySQL 可以手工或自动执行检查和修复操作。执行表的修复可能会导致丢失一些数据，而且整个过程非常缓慢。

可以通过check table xxx检查表的错误，如果有错误，则通过repair table xxx进行修复。
在 MySQL 服务器关闭的情况下，也可以通过 myisamchk 命令行工具进行检查和修复操作。

mysql> create table t1(
    ->     c1 int not null,
    ->     c2 varchar(10) default null
    -> ) engine = myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> check table t1;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| tempdb.t1 | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> repair table t1;
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| tempdb.t1 | repair | status   | OK       |
+-----------+--------+----------+----------+
1 row in set (0.00 sec)

4.2 存储方式

MyISAM 在磁盘中存储成 3 个文件，文件名和表名相同

  • .frm-存储表定义 ；
  • .MYD-存储数据；
  • .MYI-存储索引。

下面为 MyISAM 表的创建语句，及相应的数据文件：

mysql> create table a (id int) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

[root@mysql-test-1 tempdb]# ls -lrt a.*
-rw-r----- 1 mysql mysql 8556 Apr 13 02:01 a.frm
-rw-r----- 1 mysql mysql 1024 Apr 13 02:01 a.MYI
-rw-r----- 1 mysql mysql    0 Apr 13 02:01 a.MYD

5、Memory

Memory 使用内存中的内容来创建表，每个 Memory 表只有一个 .frm 文件。如果需要快速访问数据，并且数据不会被修改，丢失也没有关系，使用 Memory 是非常适合的。而且 Memory 表支持 Hash 索引，查找操作非常快。

Memory 表无法取代基于磁盘的表：

  • Memory 表是表级锁，并发写的性能较差；
  • 不支持BLOB或TEXT类型的列，并且每行的长度是固定的，即使指定了varchar列，实际存储也会使用char列。

Memory 表比较适合以下场景：

  • 用于查找或映射表，如邮编、省市区等变化不频繁的表；
  • 用于缓存周期性聚合数据的表；
  • 用于统计操作的中间结果表。

索引

1、何为索引

索引在 MySQL 中也叫“键（Key）”，是存储引擎用于快速查找记录的一种数据结构，这也是索引的基本功能。

MySQL 索引的工作原理，类似一本书的目录，如果要在一本书中找到特定的知识点，先通过目录找到对应的页码。在 MySQL 中，存储引擎用类似的方法使用索引，先在索引找到对应值，再根据索引记录找到对应的数据行。简单总结，索引就是为了提高数据查询的效率，跟一本书的目录一样。

以下查询假设字段 c2 上建有索引，则存储引擎将通过索引找到 c2 等于 测试01 的行。也就是说，存储引擎先在索引按值进行查找，再返回所有包含该值的数据行。

mysql> select * from t1 where c2='测试01'\G
*************************** 1. row ***************************
c1: 1
c2: 测试01
1 row in set (0.00 sec)

从执行计划的角度，也可以看出索引 idx_c2 被使用：

mysql> create table t1(
    ->     c1 int not null auto_increment,
    ->     c2 varchar(10) default null,
    ->     primary key(c1)
    -> ) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1() values(1,'测试01');
Query OK, 1 row affected (0.00 sec)

mysql> create index idx_c2 on t1(c2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where c2='测试01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_c2
          key: idx_c2
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

常见的索引类型主要有 B-Tree 索引、哈希索引、空间数据索引（R-Tree）、全文索引。

  • InnoDB 和 MyISAM 存储引擎可以创建 B-Tree 索引，单列或多列都可以创建索引；
  • Memory 存储引擎可以创建哈希索引，同时也支持 B-Tree 索引；
  • 从 MySQL5.7 开始，InnoDB 和 MyISAM 存储引擎都可以支持空间类型索引；
  • InnoDB 和 MyISAM 存储可以支持全文索引（FULLTEXT），该索引可以用于全文搜索，仅限于CHAR、VARCHAR、TEXT 列。

2、优点

索引最大的作用是快速查找数据，除此之外，索引还有其他的附加作用。

B-Tree 是最常见的索引，按照顺序存储数据，它可以用来做 order by 和 group by 操作。因为 B-Tree 是有序的，将相关的值都存储在一起。因为索引存储了实际的列值，某些查询仅通过索引就可以完成查询，如覆盖查询。
总的来说，索引三个优点如下：

  • 索引可以大大减少 MySQL 需要扫描的数据量；
  • 索引可以帮助 MySQL 避免排序和临时表；
  • 索引可以将随机 IO 变为顺序 IO。

索引并不总是最好的优化工具：

  • 对于非常小的表，大多数情况，全表扫描会更高效；
  • 对于中大型表，索引就非常有效；
  • 对于特大型表，建索引和用索引的代价是日益增长，这时候可能需要和其他技术结合起来，如分区表。

3、B-Tree索引

3.1 存储结构

B-Tree 对索引列的值是按顺序存储的，并且每一个叶子页到根的距离相同。
B-Tree 索引可以加快数据查找的速度，因为存储引擎不需要全表扫描来获取数据，只要从索引的根节点开始搜索即可。

以表 customer 为例，我们来看看索引是如何组织数据的存储的。

mysql> create table customer(
		 id int,
         last_name varchar(30),
		 first_name varchar(30),
		 birth_date date,
		 gender char(1),
		 key idx1_customer(last_name,first_name,birth_date)
     );

表中的每行数据，索引包含了 last_name、first_name 和 birth_date 的值。
MySQL进阶-存储引擎、索引、锁、事务

3.2 适合 B-Tree 索引的查询类型

  • 全值匹配：

和索引中的所有列进行匹配，如查找姓名为 George Bush、1960-08-08 出生的客户。

mysql> explain select * from customer where first_name='George' and last_name='Bush' and birth_date='1960-08-08'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 190
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • 匹配最左前缀：

只使用索引的第一列，如查找所有姓氏为 Bush 的客户：

mysql> explain select * from customer where last_name='Bush'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  • 匹配列前缀：

只匹配某一列的值的开头部分，如查找所有以 B 开头的姓氏的客户，这里使用了索引的第一列：

mysql> explain select * from customer where last_name like 'B%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: range
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 93
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
  • 匹配范围值：

查找所有姓氏在 Allen 和 Bush 之间的客户，这里使用了索引的第一列：

mysql> explain select * from customer where last_name between 'Allen' and 'Bush'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: range
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 93
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
  • 精确匹配某一列，并范围匹配另一列：

第一列全匹配，第二列范围匹配，如查找姓氏为 Bush，名字以 G 开头的客户：

mysql> explain select * from customer where last_name='Bush' and first_name like 'G'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: range
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 186
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
  • 只访问索引的查询：

只需要访问索引即可获取数据，不需要回表访问数据行，这种查询也叫覆盖索引：

mysql> explain select last_name from customer where last_name='Bush'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
  • 索引还可以用于 order by 排序操作，因为索引中的节点是有序的。如果 B-Tree 可以按照某种方式查找到数据，那么也可以按照这种方式进行排序。

3.3 B-Tree 索引的限制

  • 不是按照索引的最左列开始查找数据，则无法使用索引。
  • 不能跳过索引的列。
  • 查询中有某个列的范围查询，在其右边的列都无法使用索引进行查找数据。

4、哈希索引

哈希索引基于哈希表实现，仅支持精确匹配索引所有列的查询。
对于每行数据，存储引擎都会对所有的索引列计算出一个哈希码。
哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。

4.1 存储结构

常见的存储引擎中，MEMORY 存储引擎显式支持哈希索引。
如果多个列的哈希值相同，哈希索引会以链表的方式存放多个记录指针到同一个哈希条目中。

以 customer 表为例，索引是如何组织数据的存储的：

mysql> create table customer(
		 id int,
         last_name varchar(30),
		 first_name varchar(30),
		 birth_date date,
		 key idx1_customer(first_name) using hash
     ) ENGINE=MEMORY;
     
mysql> select * from customer;
+------+-----------+------------+------------+
| id   | last_name | first_name | birth_date |
+------+-----------+------------+------------+
|    1 | Allen     | Cuba       | 1960-01-01 |
|    2 | Barrymore | Julia      | 2000-05-06 |
|    3 | Basinger  | Viven      | 1979-01-24 |
+------+-----------+------------+------------+
3 rows in set (0.00 sec)

假设哈希索引使用哈希函数f()，返回的值如下：

f('Cuba')=1212

f('Julia')=5656

f('Viven')=2323

哈希索引的数据结构如下：
+-----------+-----------------------+
| 槽(Slot)  | 值(Value)              |
+-----------+-----------------------+
|      1212 | 指向第1行的指针          |
|      2323 | 指向第3行的指针          |
|      5656 | 指向第2行的指针          |
+-----------+-----------------------+

InnoDB 存储引擎也能支持哈希索引，但它所支持的哈希索引是自适应的。InnoDB 存储引擎会根据表的使用情况，在内存中基于 B-Tree 索引之上再创建一个哈希索引，这种行为是自动的、内部的行为，不能人为去干预是否在一张表中生成哈希索引。

4.2 适合哈希索引的查询类型

  • 精确匹配所有列

和索引中的所有列进行精确匹配，如查找名字为Julia的客户。

数据库先会计算first_name='Julia’的哈希值5656，然后在索引中查找5656，找到对应的指针为：指向第2行的指针，最后根据指针从原表拿到具体值，并进行比较是否为Julia

mysql> explain select * from customer where first_name='Julia'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx1_customer
          key: idx1_customer
      key_len: 93
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

4.3 哈希索引的限制

  • 哈希索引只支持等值查询，包括=、IN、<=>；
  • 哈希索引不存储字段值，只包含哈希值和行指针，不能使用索引中的值来避免读取行；
  • 哈希索引不是按照索引值顺序存储的，不能用于排序；
  • 哈希索引不支持部分索引列匹配查找，如在字段(last_name,first_name)创建哈希索引，此时需要查找last_name='Allen’的数据行，这种查询无法使用该哈希索引；
  • 哈希索引不支持范围查询，如查找所有姓氏在Allen和Bush之间的客户，这种查询无法使用哈希索引；
  • 如果出现很多哈希冲突（不同的索引列值有相同的哈希值），索引的维护成本是很高的，应尽量避免在选择性很低的字段上创建哈希索引。

5、空间数据索引 R-Tree

常见的存储引擎中，MyISAM 存储引擎支持空间索引，主要用作地理数据存储。空间索引会从所有维度来索引数据，查询时，可以使用任意维度来组合查询。这点和 B-Tree 索引不同，空间索引不需要前缀查询。MySQL 的 GIS 支持其实并不完善，一般情况并不建议在 MySQL 中使用空间索引。

6、全文索引

全文索引查找的是文本中的关键词，并不是直接比较索引中的值，它是一种特殊类型的索引。全文索引和其他索引的匹配方式完全不一样，更类似于搜索引擎，并不是简单的 where 条件匹配。

在相同的列上可以同时创建全文索引和 B-Tree 索引，全文索引适用于 match against 操作，不是简单的where 条件操作。

7、如何高效高性能的选择使用 MySQL 索引？

7.1 独立的列

独立的列，是指索引列不能是表达式的一部分，也不能是函数的参数。如果 SQL 查询中的列不是独立的，MySQL 不能使用该索引。

下面两个查询，MySQL 无法使用 id 列和 birth_date 列的索引。开发人员应该养成编写 SQL 的好习惯，始终要将索引列单独放在比较符号的左侧。

mysql> select * from customer where id + 1 = 2;
mysql> select * from customer where to_days(birth_date) - to_days('2020-06-07') <= 10;

7.2 前缀索引

有时候需要对很长的字符列创建索引，这会使得索引变得很占空间，效率也很低下。碰到这种情况，一般可以索引开始的部分字符，这样可以节省索引产生的空间，但同时也会降低索引的选择性。

就要选择足够长的前缀来保证较高的选择性，但是为了节省空间，前缀又不能太长，只要前缀的基数，接近于完整列的基数即可。

Tips：索引的选择性指，不重复的索引值（也叫基数，cardinality）和数据表的记录总数的比值，索引的选择性越高表示查询效率越高。

完整列的选择性：

mysql> select count(distinct left(last_name,3))/count(*) left_3, count(distinct left(last_name,4))/count(*) left_4, count(distinct left(last_name,5))/count(*) left_5, count(distinct left(last_name,6))/count(*) left_6 from customer;
+--------+--------+--------+--------+
| left_3 | left_4 | left_5 | left_6 |
+--------+--------+--------+--------+
|   0.043|   0.046|   0.050|   0.051|
+--------+--------+--------+--------+

上面的查询，当前缀长度为 6 时，前缀的选择性接近于完整列的选择性 0.053，再增加前缀长度，能够提升选择性的幅度也很小了。

创建前缀长度为6的索引：

mysql> alter table customer add index idx_last_name(last_name(6));

前缀索引可以使索引更小更快，但同时也有缺点：无法使用前缀索引做 order by 和 group by，也无法使用前缀索引做覆盖扫描。

7.3 合适的索引列顺序

在一个多列 B-Tree 索引中，索引列的顺序表示索引首先要按照最左列进行排序，然后是第二列、第三列等。索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 order by、group by 和 distinct 等的查询需求。

索引的列顺序非常重要，在不考虑排序和分组的情况下，通常会将选择性最高的列放到索引最前面。

以下查询，是应该创建一个 (last_name,first_name) 的索引，还是应该创建一个(first_name,last_name) 的索引？

mysql> select * from customer where last_name = 'Allen' and first_name = 'Cuba'

首先来计算下这两个列的选择性，看哪个列更高。

mysql> select count(distinct last_name)/count(*) last_name_selectivity, count(distinct first_name)/count(*) first_name_selectivity from customer;
+-----------------------+------------------------+
| last_name_selectivity | first_name_selectivity |
+-----------------------+------------------------+
|                 0.053 |                  0.372 |
+-----------------------+------------------------+

很明显，列 first_name 的选择性更高，所以选择 first_name 作为索引列的第一列：

mysql> alter table customer add index idx1_customer(first_name,last_name);

7.4 覆盖索引

如果一个索引包含所有需要查询的字段，称之为覆盖索引。
由于覆盖索引无须回表，通过扫描索引即可拿到所有的值，它能极大地提高查询效率：索引条目一般比数据行小的多，只通过扫描索引即可满足查询需求，MySQL 可以极大地减少数据的访问量。

表 customer 有一个多列索引 (first_name,last_name)，以下查询只需要访问 first_name 和last_name，这时就可以通过这个索引来实现覆盖索引。

mysql> explain select last_name, first_name from customer\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx1_customer
      key_len: 186
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

当查询为覆盖索引查询时，在 explain 的 extra 列可以看到 Using index。

7.5 使用索引实现排序

MySQL 可以通过排序操作，或者按照索引顺序扫描来生成有序的结果。如果 explain 的 type 列的值为index，说明该查询使用了索引扫描来做排序。

order by 和查询的限制是一样的，需要满足索引的最左前缀要求，否则无法使用索引进行排序。只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（正序或倒序)都一致，MySQL才能使用索引来做排序。如果查询是多表关联，只有当 order by 子句引用的字段全部为第一个表时，才能使用索引来做排序。

以表 customer 为例，哪些查询可以通过索引进行排序：

mysql> create table customer(
		 id int,
         last_name varchar(30),
		 first_name varchar(30),
		 birth_date date,
		 gender char(1),
		 key idx_customer(last_name,first_name,birth_date)
     );
  • 可以通过索引进行排序的查询

索引的列顺序和 order by 子句的顺序完全一致：

mysql> explain select last_name,first_name from customer order by last_name, first_name, birth_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_customer
      key_len: 190
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

索引的第一列指定为常量：
从 explain 可以看到没有出现排序操作（filesort）：

mysql> explain select * from customer where last_name = 'Allen' order by first_name, birth_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

索引的第一列指定为常量，使用第二列排序：

mysql> explain select * from customer where last_name = 'Allen' order by first_name desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

索引的第一列为范围查询，order by 使用的两列为索引的最左前缀：

mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by last_name,first_name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: range
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
  • 不能通过索引进行排序的查询

使用两种不同的排序方向：

mysql> explain select * from customer where last_name = 'Allen' order by first_name desc, birth_date asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

order by 子句引用了一个不在索引的列：

mysql> explain select * from customer where last_name = 'Allen' order by first_name, gender\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_customer
          key: idx_customer
      key_len: 93
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

where 条件和 order by 的列无法组成索引的最左前缀：

mysql> explain select * from customer where last_name = 'Allen' order by birth_date\G

第一列是范围查询，where 条件和 order by 的列无法组成索引的最左前缀：

mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by first_name\G

第一列是常量，第二列是范围查询（多个等于也是范围查询）：

mysql> explain select * from customer where last_name = 'Allen' and first_name in ('Cuba','Kim') order by birth_date\G

锁

MySQL 有两个级别的锁等待，服务器级别和存储引擎级别

1、表锁

1.1 显式锁

通过 lock tables和unlock tables 可以控制显式锁。

在 MySQL 会话中执行 lock tables 命令，在表customer上会获得一个显式锁。

mysql> lock tables customer read;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一个会话中，对表 customer 执行 lock tables 命令，查询会挂起。

mysql> lock tables customer write;

在第一个会话中执行 show processlist 查看线程状态，可以看到线程 13239868 的状态为 Waiting for table metadata lock。在 MySQL 中，当一个线程持有该锁后，其他线程只能不断尝试获取。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13239801
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 13239868
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 12
  State: Waiting for table metadata lock
   Info: lock tables customer write
2 rows in set (0.00 sec)

1.2 隐式锁

除了显式锁会阻塞这样的操作，MySQL 在查询过程中也会隐式地锁住表。

通过 sleep() 函数可以实现长时间的查询，然后 MySQL 会产生一个隐式锁。

在 MySQL 会话中执行 sleep(30)，在表 customer上 会获得一个隐式锁。

mysql> select sleep(30) from customer;

在 MySQL 另一个会话中，对表 customer 执行 lock tables 命令，查询会挂起。

mysql> lock tables customer write;

在第三个会话中执行 show processlist 查看线程状态，可以看到线程 13244135 的状态为 Waiting for table metadata lock。select 查询的隐式锁阻塞了 lock tables 中所请求的显式写锁。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13244112
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 6
  State: User sleep
   Info: select sleep(30) from customer
*************************** 2. row ***************************
     Id: 13244135
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 2
  State: Waiting for table metadata lock
   Info: lock tables customer write

2、全局锁

全局读锁，通过 flush tables with read lock 或设置 read_only=1 来实现，全局锁与任何表锁都冲突。

在 MySQL会 话中执行 flush tables 命令，获得全局读锁。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一个会话中，对表 customer 执行 lock tables 命令，查询会挂起。

mysql> lock tables customer write;

在第一个会话中执行 show processlist 查看线程状态，可以看到线程 13283816 的状态为 Waiting for global read lock。这是一个全局读锁，而不是表级别锁。

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 13283789
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 13283816
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 10
  State: Waiting for global read lock
   Info: lock tables customer write
2 rows in set (0.00 sec)

3、命名锁

命名锁是一种表级别锁，它是 MySQL 服务器在重命名或删除表时创建。
命名锁与普通的表锁冲突，无论是显式的还是隐式的表锁。

lock table s命令，在表 customer上 获得一个显式锁。

mysql> lock tables customer read;
Query OK, 0 rows affected (0.00 sec)

在 MySQL 另一个会话中，对表 customer 执行 rename table 命令，此时会话会挂起，会话状态为Waiting for table metadata lock：

mysql> rename table customer to customer_1;

mysql> show processlist\G
...
*************************** 2. row ***************************
     Id: 51
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 128
  State: Waiting for table metadata lock
   Info: rename table customer to customer_1

4、用户锁

用户锁，需指定名称字符串，以及等待超时时间（单位秒）。

执行 get_lock 命令，成功执行并持有一把锁。

mysql> select get_lock('user_1',20);
+------------------------+
| get_lock('user_1',20) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

在 MySQL 另一个会话中，也执行 get_lock 命令，尝试锁相同的字符串，此时会话会挂起，会话状态为User lock。

mysql> select get_lock('user_1',20);
+------------------------+
| get_lock('user_1',20) |
+------------------------+
|                      1 |
+------------------------+

mysql> show processlist\G
...
*************************** 2. row ***************************
     Id: 51
   User: root
   Host: localhost
     db: tempdb
Command: Query
   Time: 3
  State: User lock
   Info: select get_lock('user_1',20)

5、InnoDB 存储引擎中的锁等待

存储引擎级别的锁，比服务器级别的锁更难以调试，而且各种存储引擎的锁互不相同，有些存储引擎甚至都不提供任何方法来查看锁。

show engine innodb status

show engine innodb status 命令包含了 InnoDB 存储引擎的部分锁信息，但很难确定哪个事务导致这个锁的问题，因为该 命令不会告诉你谁拥有锁。

如果事务正在等待某个锁，相关锁信息会体现在 show engine innodb status 输出的 TRANSACTION 部分中。在 MySQL 会话中执行如下命令，拿到表 customer 中第一行的写锁。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from customer limit 1 for update;
+------+-----------+------------+------------+--------+
| id   | last_name | first_name | birth_date | gender |
+------+-----------+------------+------------+--------+
| NULL | 111       | 222        | NULL       | 1      |
+------+-----------+------------+------------+--------+
1 row in set (0.00 sec)

在 MySQL 另一个会话中，对表 customer 执行相同的 select 命令，查询会被阻塞。

mysql> select * from customer limit 1 for update;

这时执行 show engine innodb status 命令能够看到相关的锁信息。

1 ---TRANSACTION 124178, ACTIVE 6 sec starting index read
2 mysql tables in use 1, locked 1
3 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
4 MySQL thread id 12570, OS thread handle 139642200024832, query id 48195 localhost root Sending data
5 select * from customer limit 1 for update
6 ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
7 RECORD LOCKS space id 829 page no 3 n bits 72 index GEN_CLUST_INDEX of table `tempdb`.`customer` trx id 124178 lock_mode X locks rec but not gap waiting

第 7 行表示 thread id 12570 这个查询，在等待表 customer 中的 GEN_CLUST_INDEX 索引的第 3 页上有一个排它锁（lock_mode X）。最后，锁等待超时，查询返回错误信息。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

imformation_schema

在 MySQL5.5 开始，一般通过 imformation_schema 的表来查询相关的事务和锁信息，通过imformation_schema 要比 show engine innodb status 命令要高效和全面。

在 MySQL 会话中执行如下命令，能看到谁阻塞和谁在等待，以及等待多久的查询。

mysql> SELECT
     IFNULL(wt.trx_mysql_thread_id, 1) BLOCKING_THREAD_ID,t.trx_mysql_thread_id WAITING_THREAD_ID, CONCAT(p. USER, '@', p. HOST) USER,
     p.info SQL_TEXT, l.lock_table LOCK_TABLE, l.lock_index LOCKED_INDEX, l.lock_type LOCK_TYPE, l.lock_mode LOCK_MODE,
     CONCAT(FLOOR(HOUR (TIMEDIFF(now(), t.trx_wait_started)) / 24),'day ',MOD (HOUR (TIMEDIFF(now(), t.trx_wait_started)),24),':',
     MINUTE (TIMEDIFF(now(), t.trx_wait_started)),':',SECOND (TIMEDIFF(now(), t.trx_wait_started))) AS WAIT_TIME,
     t.trx_started TRX_STARTED, t.trx_isolation_level TRX_ISOLATION_LEVEL, t.trx_rows_locked TRX_ROWS_LOCKED, t.trx_rows_modified TRX_ROWS_MODIFIED
     FROM INFORMATION_SCHEMA.INNODB_TRX t
     LEFT JOIN information_schema.innodb_lock_waits w ON t.trx_id = w.requesting_trx_id
     LEFT JOIN information_schema.innodb_trx wt ON wt.trx_id = w.blocking_trx_id
     INNER JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id
     INNER JOIN information_schema. PROCESSLIST p ON t.trx_mysql_thread_id = p.id
     ORDER BY 1\G
*************************** 1. row ***************************
 BLOCKING_THREAD_ID: 1
  WAITING_THREAD_ID: 62751
               USER: root@localhost
           SQL_TEXT: NULL
         LOCK_TABLE: `tempdb`.`customer`
       LOCKED_INDEX: GEN_CLUST_INDEX
          LOCK_TYPE: RECORD
          LOCK_MODE: X
          WAIT_TIME: NULL
        TRX_STARTED: 2020-06-22 06:52:14
TRX_ISOLATION_LEVEL: READ COMMITTED
    TRX_ROWS_LOCKED: 1
  TRX_ROWS_MODIFIED: 0
*************************** 2. row ***************************
 BLOCKING_THREAD_ID: 62751
  WAITING_THREAD_ID: 62483
               USER: root@localhost
           SQL_TEXT: select * from customer limit 1 for update
         LOCK_TABLE: `tempdb`.`customer`
       LOCKED_INDEX: GEN_CLUST_INDEX
          LOCK_TYPE: RECORD
          LOCK_MODE: X
          WAIT_TIME: 0day 0:0:5
        TRX_STARTED: 2020-06-22 07:01:49
TRX_ISOLATION_LEVEL: READ COMMITTED
    TRX_ROWS_LOCKED: 1
  TRX_ROWS_MODIFIED: 0
2 rows in set, 2 warnings (0.00 sec)

从结果显示线程 62483 等待表 customer 中的锁已经 5s，它被线程 62751 所阻塞。
下面这个查询可以告诉你有多少查询被哪些线程锁阻塞。

mysql> select concat('thread ', b.trx_mysql_thread_id, ' from ', p.host) as who_blocks,
       if(p.command = "Sleep", p.time, 0) as idle_in_trx, 
       max(timestampdiff(second, r.trx_wait_started, now())) as max_wait_time, 
       count(*) as num_waiters
     from information_schema.innodb_lock_waits as w
     inner join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id
     inner join information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id
     left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id
     group by who_blocks order by num_waiters desc\G
*************************** 1. row ***************************
   who_blocks: thread 62751 from localhost
  idle_in_trx: 1206
max_wait_time: 20
  num_waiters: 5
1 row in set, 1 warning (0.00 sec)

从结果显示线程 62751 已经空闲了一段时间，有 5 个线程在等待线程 62751 完成提交并释放锁，有一个线程已经等待线程 62751 释放锁长达 20s。

事务

事务是在存储引擎层实现的。
MySQL 是支持多种存储引擎的数据库，但并不是所有的存储引擎都支持事务，比如 MyISAM 就不支持事务

1. ACID

  1. 原子性（atomicity）：一个事务被视为一个完整的最小工作单元，事务中的数据库操作，要么全部执行成功，要么全部执行失败回滚，不能只成功执行了其中的一部分数据库操作；
  2. 一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
  3. 隔离性（isolation）：通常来说，一个事物所做的修改在提交以前，对其他事务是不可见的。
  4. 持久性（durability）：事务提交成功，所做的修改就会永久保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

2. 隔离级别

  • 读取未提交数据（read uncommitted）：一个事务还未提交，它所做的变更能被别的事务看到。事务可以读取未提交的数据，被称为脏读（dirty read），这种隔离级别在实际应用中一般很少使用；
  • 读取已提交数据（read committed）：一个事务提交之后，它所做的变更才能被别的事务看到。大多数数据库的默认隔离级别是提交读(read committed)，比如Oracle。
  • 重复读取（repeatable read）：一个事务在执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。该级别保证了在同一个事务中，多次读取同样记录的结果是一致的。MySQL 的默认事务隔离级别是可重复读(repeatable read);
  • 序列化（serializable）：serializable 是最高的隔离级别。对同一行数据，读写都会进行加锁。当出现锁冲突时，后面访问的事务必须等前一个事务完成，才能继续执行。实际应用场景很少用到这种隔离级别，只有在非常需要确保数据一致性，而且可以接受没有并发的情况，才会使用这种隔离级别。
隔离级别 脏读可能性 不可重复度可能性 幻读可能性 加锁读
未提交读（read uncommitted） yes yes yes no
提交读（read committed） no yes yes no
可重复读（repeatable read） no no yes no
可串行化（serializable） no no no yes

3. 事务控制机制（Transaction）

  • RDBMS = SQL语句 + 事务(ACID)
  • 事务是一个或者多个SQL语句组成的整体，要么全部执行成功，要么全部执行失败。
    MySQL进阶-存储引擎、索引、锁、事务

3.1 手动管理事务

  • 默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务
  • 为了让多条SQL语句纳入到一个事务下，可以手动管理事务
SET 
START TRANSACTION;//开启事务
SQL语句
[COMMIT｜ROLLBACK];//提交｜回滚

3.2 隔离级别调整

默认情况下，MySQL 的隔离级别是可重复读（repeatable read）。

MySQL 可以通过 set transaction_isolation 命令来调整隔离级别，新的隔离级别会在下一个事务开始时生效。

调整隔离级别的方法有两种：

  • 临时：在 MySQL 中直接用命令行执行：
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> SET transaction_isolation = 'REPEATABLE-READ';
## SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE_READ;
Query OK, 0 rows affected (0.00 sec)

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE_READ;
SET transaction_isolation = ‘REPEATABLE-READ’;

  • 永久：将以下两个参数添加至配置文件 my.cnf，并重启 MySQL：
transaction_isolation = 'REPEATABLE-READ'

3.3 自动提交

默认情况下，MySQL 是自动提交（autocommit）的。也就意味着：如果不是显式地开始一个事务，每个查询都会被当做一个事务执行 commit。这是和 Oracle 的事务管理明显不同的地方，如果应用是从Oracle 数据库迁移至 MySQL 数据库，则需要确保应用中是否对事务进行了明确的管理。

在当前连接中，可以通过设置 autocommit 来修改自动提交模式：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)
-- 1或ON表示启用自动提交模式，0或OFF表示禁用自动提交模式

如果设置了autocommit=0，当前连接所有事务都需要通过明确的命令来提交或回滚。

对于 MyISAM 这种非事务型的表，修改 autocommit 不会有任何影响，因为非事务型的表，没有 commit或 rollback 的概念，它会一直处于 autocommit 启用的状态。

有些命令，在执行之前会强制执行 commit 提交当前连接的事务。比如 DDL 中的 alter table，以及lock tables 等语句。

3.4 事务中使用不同的存储引擎

MySQL 的服务层并不负责事务的处理，事务都是由存储引擎层实现。

在同一事务中，使用多种存储引擎是不可靠的，尤其在事务中混合使用了事务型和非事务型的表。如同一事务中，使用了 InnoDB 和 MyISAM 表：

  • 如果事务正常提交，不会有什么问题；
  • 如果事务遇到异常需要回滚，非事务型的表就无法撤销表更，这就会直接导致数据处于不一致的状态。

4. 死锁

死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，
若无外力作用，它们都将无法推进下去。
当多个事务尝试以不同的顺序锁定资源，或者多个事务同时锁定同一个资源，都有可能产生死锁。

示例：
两个事务同时处理 customer 表

两个事务同时执行了第一条 update 语句，更新并锁定了该行数据，紧接着又都执行第二条 update 语句，此时发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，陷入死循环，需要外力介入才能解除死锁。

mysql> CREATE TABLE `customer` (
  `id` int(11) NOT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `balance` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

事务1：
start transaction;
update customer set balance = 100 where id = 1;
update customer set balance = 200 where id = 2;
commit;

事务2：
start transaction;
update customer set balance = 300 where id = 2;
update customer set balance = 400 where id = 1;
commit;

为了解决死锁问题，数据库实现了各种死锁检测和死锁超时机制。
越复杂的存储引擎，越能检测到死锁的循环由来，并返回错误。
还有一种解决死锁的办法是：当锁等待超时后，放弃锁请求。

InnoDB 存储引擎可以自动检测事务的死锁，并回滚一个或几个事务来防止死锁。但是有些场景 InnoDB是无法检测到死锁的，比如在同一事务中使用 InnoDB 之外的存储引擎、lock tables 设定表锁定的语句，此时要通过设置 innodb_lock_wait_timeout 这个系统参数来解决。通过锁等待超时来解决死锁问题，通常不是好的办法，因为很有可能导致大量事务的锁等待。当发生锁等待超时，数据库会抛出如下报错信息：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

调整 innodb_lock_wait_timeout 的方法有两种：

  • 临时：在MySQL中直接用命令行执行：
-- innodb_lock_wait_timeout的默认值为50秒
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> set innodb_lock_wait_timeout=51;
Query OK, 0 rows affected (0.00 sec)
  • 永久：将以下两个参数添加至配置文件 my.cnf，并重启 MySQL：
innodb_lock_wait_timeout=50

在程序设计时，也要尽可能的减小死锁发生的概率。以下是针对 InnoDB 存储引擎减小死锁发生概率的一些建议：

  • 类似业务模块，尽可能按照相同的访问顺序来访问，防止产生死锁；
  • 同一个事务中，尽可能做到一次锁定需要的所有资源，减少死锁发生概率；
  • 同一个事务中，不要使用不同存储引擎的表，比如 MyISAM 和 InnoDB 表出现在同一事务中；
  • 尽可能控制事务的大小，减少锁定的资源量和锁定时间长度；
  • 对于容易产生死锁的业务模块，尝试升级锁颗粒度，通过表级锁减少死锁发生概率。

5. 事务日志

使用事务日志可以提高事务的安全性和效率：

  • 修改表数据时，只需要在内存中进行修改，再持久化到磁盘上的事务日志，而不用每次都将修改的数据持久化到磁盘。事务日志持久化后，内存中所修改的数据可以慢慢再刷到磁盘，这种方式称为预写式日志，修改数据需要写两次磁盘；
  • 效率快很多，因为事务日志采用追加方式，写日志的操作只是磁盘上一小块区域的顺序IO，不像随机IO需要在磁盘多个地方移动磁头；
  • 万一数据库发生崩溃，可以通过已经持久化的事务日志，来自动恢复数据。

MySQL进阶-存储引擎、索引、锁、事务

上一篇：IntelliJ IDEA 老司机，还没用过 Stream Trace 功能？


下一篇：隐性连接 与 内连接 的关系