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

最新推荐文章于 2024-08-18 00:37:09 发布

碎碎念的啊木

最新推荐文章于 2024-08-18 00:37:09 发布

阅读量306

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/duan__i18n/article/details/117152094

版权

Mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

存储引擎

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、常用存储引擎的对比

在这里插入图片描述

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 的值。
在这里插入图片描述

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

原子性（atomicity）：一个事务被视为一个完整的最小工作单元，事务中的数据库操作，要么全部执行成功，要么全部执行失败回滚，不能只成功执行了其中的一部分数据库操作；
一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性（isolation）：通常来说，一个事物所做的修改在提交以前，对其他事务是不可见的。
持久性（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语句组成的整体，要么全部执行成功，要么全部执行失败。

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需要在磁盘多个地方移动磁头；
万一数据库发生崩溃，可以通过已经持久化的事务日志，来自动恢复数据。

在这里插入图片描述