月份：2017年9月

1、UNIX时间戳转换为日期用函数： FROM_UNIXTIME()

select FROM_UNIXTIME(1156219870);

输出：2006-08-22 12:11:10

2、日期转换为UNIX时间戳用函数： UNIX_TIMESTAMP()

Select UNIX_TIMESTAMP(‘2006-11-04 12:23:00’);

输出：1162614180

Select UNIX_TIMESTAMP(NOW());

输出当前时间戳

例：mysql查询当天的记录数：

$sql=”select * from message Where DATE_FORMAT(FROM_UNIXTIME(chattime),’%Y-%m-%d’) = DATE_FORMAT(NOW(),’%Y-%m-%d’) order by id desc”;

PHP方式转换：

UNIX时间戳转换为日期用函数： date()

date(‘Y-m-d H:i:s’, 1156219870);

日期转换为UNIX时间戳用函数：strtotime()

strtotime(‘2010-03-24 08:15:42’);

【摘要】如何找到执行效率比较低的 SQL 语句是对于开发人员来说能够及时的将该条 SQL 进行修改，能够有效的对 MySQL 数据库进行优化。

可以通过以下方式来定位执行效率低的 SQL 语句：

可以通过慢查询日志定位那些执行效率较低的 SQL，用mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 日志文件。依次我们可以了解哪些 SQL 语句查询效率低。

windows 下具体的设置方法如下：

1、打开 MySQL 的配置文件 my.ini 文件，然后找到 [mysqld] 标签；

2、在 [mysqld] 标签下面加上以下内容：

# 开启慢查询日志
slow_query_log = true
# 慢查询日志保存路径及文件
slow_query_log_file = "H:/wamp/bin/mysql/mysql5.6.17/slow.log"
# 超过 3s 的查询
long_query_time = 3

3、保存并关闭，然后重启 MySQL 即可。接下来我们可以使用 select sleep(10); 来模拟慢查询状态，然后查看记录：

select sleep(10);

然后我们可以打开该慢查询日志 slow.log：

这样就将查询时间超过 3s 的 SQL 语句记录到日志文件中了。

什么是索引选择性？

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

什么是Cardinality（基数）？

索引可以用来提升数据的查询速度，但是并不是在所有的查询条中出现的列都需要添加索引，对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分数据时使用B+数索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性。如：

select * from student where sex=’M’

按性别进行查询时，可取值的范围一般只有’M’,’F’。因此上述SQL语句得到的结果可能是该表50%的数据，这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最合适的。例如，对于姓名字段，基本上在一个应用中不允许重名的出现。

索引的选择性是指索引列唯一值的数目与表中记录数的比例，如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性，当然除非是唯一性索引（唯一性索引属于高选择性），并且不得不手工优化查询以避免使用非选择性的索引。

怎么查看索引是否属于高选择性呢？可以通过SHOW INDEX结果中的列Cardinality来观察。Cardinality值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality是一个预估值。同时需要注意的是，Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引是非常有必要的。使用执行计划（explain）可以用来查看这个SQL语句需要查询的行数。

Innodb存储引擎是如何统计Cardinality（基数）？

我们知道了Cardinality表示选择性，建立索引的前提是列中的数据是高选择性的，这对数据库来说才具有实际意义。然而数据库是怎样来统计Cardinality信息的呢？因为MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树索引的实现又各不相同，所以对Cardinality的统计是放在存储引擎层进行的。

此外需要考虑到的是，在生产环境中，索引的更新操作可能是非常频繁的。如果每次索引在发生操作时就对其进行Cardinality的统计，那么将会给数据库带来很大的负担。另外需要考虑的是，如果一张表的数据非常大，如一张表有50G的数据，那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境下，也是不能接受的。因此，数据库对于Cardinality的统计都是通过采样的方法来完成的。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时就去更新Cardinality信息，这样会增加数据库系统的负荷，同时对于大表的统计，时间上也不允许数据库这样去操作。因此，InnoDB存储引擎内部对更新Cardinality信息的策略为：

• 表中1/16的数据已发送过变化。
• stat_modified_counter>2000000000。

第一种策略为自上次统计Cardinality信息后，表中1/16的数据已经发生过变化，这时需要更新Cardinality信息。第二种情况考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数。当stat_modified_counter，用来表示发生变化的次数，当stat_modified大于2000000000时，同样需要更新Cardinality信息。

接着考虑InnoDB存储引擎内部是怎样来进行Cardinality信息的统计和更新操作的？同样是通过采样的方法，默认InnoDB存储引擎对8个叶子节点（Leaf Page）进行采样，采样的过程如下：

• 取得B+树索引中叶子节点的数量，记为A。
• 随机取得B+树索引中8个叶子节点，统计每个页不同记录的个数，即为P1，P2，…，P8。
• 根据采样信息给出Cardinality的预估值：Cardinality = (P1+P2+…+P8) * A/8。

通过上述的说明可以发现，在InnoDB存储引擎中，Cardinality值是通过对8个叶子节点预估而得到的，不是一个世纪精确的值。再者，每次对Cardinality值的统计，都是通过随机取8个叶子节点得到的，这同时又暗示了另一个Cardinality对象，即每次得到的Cardinality值可能是不同的。如：SHOW INDEX FROM OrderDetails语句会触发MySQL数据库对于Cardinality值的统计，所以可能会出现此表没有任何数据变化，但是你多次执行SHOW INDEX FROM OrderDetails看到的Cardinality值不同。因为每次执行都会触发MySQL数据对于Cardinality值的统计，随机选取8个叶子节点进行分析。所以如果遇到这个并不是InnoDB存储因为的Bug，只是随机采样而导致的。

当然，有一种情况使得用户每次观察到的索引Cardinality值都是一样的，那就是表足够小，表的叶子节点数小于或者等于8个。这时即使随机采样，也总是会采取到这些页，因此每次得到的Cardinality值是相同的。

当执行SQL语句ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时会导致InnoDB存储引擎去重新计算索引的Cardinality值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新Cardinality值。

InnoDB 1.2版本提供了以下参数对Cardinality统计进行设置，这些参数如下：

mysql> show global variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_include_delete_marked   | OFF         |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
+--------------------------------------+-------------+
8 rows in set (0.02 sec)

innodb_stats_auto_recalc

开启与否只会影响persistent类型的统计。

innodb_stats_method

用来判断如何对待索引中出现的NULL值记录，该参数默认值为nulls_equal，表示将NULL值记录视为相同的记录。其有效值还有nulls_unequal，nulls_ignored，分别表示将NULL值记录视为不同的记录和忽略NULL值记录。例如某页中索引记录为NULL、NULL、1、2、2、3、3、3，在参数innodb_stats_method的默认设置下，该页的Cardinality为4；若值为nulls_unequal，则该页的Cardinality为5；若值为nulls_ignored，则Cardinality为3。

innodb_stats_sample_pages

在InnoDB 1.2版本之前，用来设置统计Cardinality时每次采样的数量，默认值：8，在InnoDB 1.2版本时被innodb_stats_transient_sample_pages参数取代。

innodb_stats_on_metadata

当通过命令SHOW TABLE STATUS、SHOW INDEX及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时，是否需要重新计算索引的Cardinality值。默认值：OFF，InnoDB 1.2版本提供。

innodb_stats_persistent

是否将命令ANALYZE TABLE计算得到的Cardinality值存放到磁盘上，存放mysql.innodb_index_stats表中。若是，则这样的好处是可以减少重新计算每个索引的Cardinality值，例如当MySQL数据库重启时。此外，用户也可以通过命令CREATE TABLE和ALTER TABLE的选项STATS_PERSISTENT来对每张表进行控制，默认值：ON，InnoDB 1.2版本提供。

innodb_stats_persistent_sample_pages

若参数innodb_stats_persistent设置为ON，该参数表示ANALYZE TABLE更新Cardinality值时每次采样页的数量。默认值：20，InnoDB 1.2版本提供。

innodb_stats_transient_sample_pages

该参数用来取代之前版本的参数innodb_stats_sample_pages，表示每次采样页的数量。默认值：8，InnoDB 1.2版本提供。

索引选择性实践

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

MySQL官方文档中关于此数据库的页面为https://dev.mysql.com/doc/employee/en。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。

显然选择性的取值范围为(0, 1]，其公式Index Selectivity = Cardinality / #T， 选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，如测试数据库中用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：

mysql> SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+
1 row in set (0.29 sec)

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

从上图可以看到employees表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了：

mysql> EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300363 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建或<first_name, last_name>，看下两个索引的选择性：

mysql> SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
1 row in set (0.30 sec)

mysql> SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+
1 row in set (0.87 sec)

显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法（对于VARCHAR索引最长可以有768字节）？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

mysql> SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+
1 row in set (0.69 sec)

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

mysql> SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+
1 row in set (0.75 sec)

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，这时我们把这个前缀索引建上。

先开启profile再执行一遍按名字查询，比较分析一下与建索引前的结果：

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  18454 | 1955-02-28 | Eric       | Anido     | M      | 1988-07-18 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.17 sec)

mysql> ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));
Query OK, 0 rows affected (1.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  18454 | 1955-02-28 | Eric       | Anido     | M      | 1988-07-18 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.17215550 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'              |
|        2 | 1.20597425 | ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name, last_name(4)) |
|        3 | 0.00076825 | SELECT SQL_NO_CACHE * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+----------------------------------------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

性能的提升是显著的，查询速度提高了200多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

MySQL变量很多，其中有一些MySQL变量非常值得我们注意，下面就为您介绍一些值得我们重点学习的MySQL变量，供您参考。

1、Threads_connected

首先需要注意的,想得到这个变量的值不能show variables like ‘Threads_connected’;而是
show status like ‘Threads_connected'(下面的变值也是这样的);
意思:变量的值是表示当前有多少个客户连接该mysql服务器
引申:连接数是否过多,网络时候存在问题!特别是在pconnect的情况下:)

2、Created_tmp_disk_tables

意思:在硬盘上建立的临时表数目
引申:如果这个值比较大的话,那么查询时需要建立临时表(CREATE TEMPORARY TABLE)的操作 就要消耗更多的时间

3、Handler_read_first

意思:读表索引的第一行
引申:如果这个值变化比较大的话,可以认为表索引建立的有问题,全索引的扫描操作比较多

4、Innodb_buffer_pool_wait_free

意思:This variable indicates the number of times MySQL has to wait for memory pages to be flus
引申:If this variable is high, it suggests that MySQL’s memory buffer is incorrectly configured for the amount of writes the server is currently performing.
不了解这个:)

5、Key_reads

意思:读文件系统上面的索引的次数
引申:如果这个值太大的话,就需要考虑key cache设置是否正常了

6、Max_used_connections

意思:重起后到现在最大连接数
引申:服务器负载和可能需要调节的连接数

7、Open_tables

意思:当前打开的表的数目
引申:如果这个值很低,table cache很大,则减小table cache的设置是没有问题的,如果这个值很大,并接近了table cache的值,我们就需要加大talbe cache的设置

8、Select_full_join

意思:全连接的查询数目
引申:数值过大,需要建立更多的索引来避免

9、Slow_queries

意思:慢查询的数目
引申:过大的话就要察看慢查询的日志,并且检查sql语句书写是否恰当

10、Uptime

意思:运行时间,单位秒

一. 前言

在MySQL的使用过程中，对SQL加锁的类型经常感到疑惑，这让死锁分析也变得举步维艰。因此需要将MySQL的各种SQL在各个隔离级别下加的锁进行分析，以免再次分析的时候还感到疑惑，也方便用于查询。

本次分析对SQL的删除语句进行分析，主要从以下几种情况进行分析：

1. 非唯一索引删除一条存在的记录
2. 唯一索引删除一条存在的记录
3. 主键删除一条存在的记录
4. 非唯一索引删除一条不存在记录
5. 唯一索引删除一条不存在的记录
6. 主键删除一条不存在的记录
7. 不同的SQL根据主键删除2条记录
8. 非唯一索引删除一条已经标记删除的记录
9. 唯一索引删除一条已经标记删除的记录

在使用之前需要打开innodb lock monitor，这样在查看 engine innodb status 的时候可以更加清晰的查到到锁的情况

set GLOBAL innodb_status_output_locks=ON;

二. SQL的加锁分析

相关表结构

• 普通索引表结构

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

• 唯一索引表结构

CREATE TABLE `tu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4

• 表的记录，唯一索引和普通索引的表结构均一样
• 测试的事务隔离级别为RR。

+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  2 |  3 |  2 |
|  3 |  5 |  3 |
|  4 |  8 |  4 |
|  5 | 11 |  5 |
|  9 |  9 | 20 |
| 10 |  7 | 10 |
| 11 | 20 | 15 |
| 12 | 30 | 17 |
| 13 | 25 | 16 |
| 14 | 27 | 10 |
+----+----+----+

2.1 删除SQL加锁分析

根据非唯一索引删除一条存在记录

delete from t where c1=5;
Query OK, 1 rows affected (0.00 sec)


---TRANSACTION 146749, ACTIVE 9 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 104 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146749 lock mode IX
RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X
RECORD LOCKS space id 53 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 146749 lock_mode X locks rec but not gap
RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X locks gap before rec

根据非唯一索引进行删除的时候，锁情况为：

4 lock struct(s)：4种锁结构，分别为IX，idx_c1和主键的行锁，还有idx_c1的gap锁
3 row lock(s)：有3个行锁，除去IX的都是算在row lock里面

根据唯一索引删除一条存在记录

delete from tu where c1=5;
Query OK, 1 rows affected (0.00 sec)


---TRANSACTION 146751, ACTIVE 2 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 134 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146751 lock mode IX
RECORD LOCKS space id 45 page no 5 n bits 72 index `uniq_c1` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap

根据唯一索引进行删除的时候，锁情况为：

3 lock struct(s)：3种锁结构，分别为IX，idx_c1和主键的行锁，没有gap锁
2 row lock(s)：有2个行锁，除去IX的都是算在row lock里面，没有gap，因此为2个

根据主键删除一条存在记录

delete from tu where id=2;
Query OK, 1 rows affected (0.00 sec)


---TRANSACTION 146753, ACTIVE 2 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 147 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146753 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146753 lock_mode X locks rec but not gap

根据主键进行删除的时候，锁情况为：

2 lock struct(s)：2种锁结构，分别为IX和主键的行锁，没有gap锁
1 row lock(s)：有1个行锁，就主键记录上的行锁，没有gap，因此为1个

根据非唯一索引删除一条不存在 记录

delete from t where c1 = 4;
Query OK, 0 rows affected (0.00 sec)


---TRANSACTION 146786, ACTIVE 1 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 671 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146786 lock mode IX
RECORD LOCKS space id 53 page no 5 n bits 80 index `idx_c1` of table `test`.`t` trx id 146786 lock_mode X locks gap before rec

根据非唯一索引删除一条 不存在 记录，锁情况为：

2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁
1 row lock(s)：有1个行锁，为非唯一索引的gap锁

根据唯一索引删除一条不存在 记录

delete from tu where c1 = 4;
Query OK, 0 rows affected (0.00 sec)


---TRANSACTION 146787, ACTIVE 2 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 711 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146787 lock mode IX
RECORD LOCKS space id 45 page no 5 n bits 72 index `uniq_c1` of table `test`.`tu` trx id 146787 lock_mode X locks gap before rec

根据唯一索引删除一条 不存在 记录,发现和非唯一索引一样，锁情况为：

2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁
1 row lock(s)：有1个行锁，为唯一索引的gap锁

根据主键删除一条不存在 记录

delete from tu where id = 6;
Query OK, 0 rows affected (0.00 sec)


---TRANSACTION 146831, ACTIVE 24 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 881 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146831 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146831 lock_mode X locks gap before rec

根据主键删除一条 不存在 记录,发现和非唯一索引一样，锁情况为：

2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁
1 row lock(s)：有1个行锁，为主键上的gap锁

根据主键删除两条存在的记录

有 5 ， 10 这两条记录

delete from tu where id>=5 and id<10;
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146900, ACTIVE 35 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 995 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146900 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146900 lock_mode X locks rec but not gap
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146900 lock_mode X
有 5 ， 9 这两条记录

delete from tu where id>=5 and id<=9;
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146912, ACTIVE 12 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1022 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146912 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146912 lock_mode X locks rec but not gap
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146912 lock_mode X

有 4 ，10 这两条记录

delete from tu where id>4 and id<10;
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146906, ACTIVE 13 sec
2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1011 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146906 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146906 lock_mode X

有 10 没 7 

delete from tu where id>=7 and id<=10;
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146966, ACTIVE 2 sec
2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1172 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146966 lock mode IX
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146966 lock_mode X

有 4没 8 

delete from tu where id>=4 and id<=8;
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146972, ACTIVE 20 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1201 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146972 lock mode IX
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146972 lock_mode X locks rec but not gap
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146972 lock_mode X

有3，4两条记录

delete from tu where id in (3,4);
Query OK, 2 rows affected (0.00 sec)

---TRANSACTION 146880, ACTIVE 1 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 928 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146880 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146880 lock_mode X locks rec but not gap

根据主键删除两条的时候，使用in的锁情况为：

2 lock struct(s)：2种锁结构，分别为IX和i主键的行锁，没有gap锁
2 row lock(s)：有2个行锁，就主键记录上的行锁，没有gap，因此为2个

根据主键删除两条的时候，使用>,<,>=,<=,比较符号的锁情况为：

1. 无论如何，匹配到2条记录，因此必须会有2 row lock(s)
2. 如果只有>,<，那么毫无疑问，是不会锁定两个边界的记录，因此他只会锁定边界到边界内的整个范围，锁的类型为X，此时为2 lock struct(s) ，3 row lock(s)
3. 碰到 >= 的时候，判断 >= 的值是否存在，如果存在，则锁定该记录。所以除了IX,X锁，还有行锁，因此存在的时候为3 lock struct(s), 3 row lock(s)。如果不存在，和第二种是一样的，为2 lock struct(s) ，3 row lock(s) 。

非唯一索引删除一条已经标记删除的记录

Sess1                    Sess2                    Sess3
begin;
delete from t where c1=8;       
                         begin; 
                         delete from t where c1=8;  
                                                  @1 show engine innodb status
commit;     
                                                  @2 show engine innodb status

@1 show engine innodb status


---TRANSACTION 146981, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox updating
delete from t where c1=8
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
------------------
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
---TRANSACTION 146980, ACTIVE 16 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 355, OS thread handle 0x7f61ab145700, query id 2802 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146980 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X
RECORD LOCKS space id 54 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 146980 lock_mode X locks rec but not gap
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X locks gap before rec

@2 show engine innodb status

---TRANSACTION 146981, ACTIVE 50 sec
3 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X locks gap before rec

非唯一索引删除一条已经标记删除的记录的锁情况为：

• 加锁等待时: 2 lock struct(s) ，持有IX锁，等待记录上的X锁
• 加锁成功时：3 lock struct(s)，持有IX,行锁，和gap锁，这个和非唯一索引删除一条不存在的记录是基本一样的，多了个因Sess1 提交成功后多获得的行锁。

唯一索引删除一条已经标记删除的记录

Sess1                     Sess2                     Sess3
begin;      
delete from tu where c1=8;      
                          begin;    
                          delete from tu where c1=8;    
                                                    @1 show engine innodb status
commit;     
                                                    @2 show engine innodb status

@1 show engine innodb status

---TRANSACTION 146984, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2842 localhost msandbox updating
delete from tu where c1=8
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X waiting
------------------
TABLE LOCK table `test`.`tu` trx id 146984 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X waiting
---TRANSACTION 146983, ACTIVE 9 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 355, OS thread handle 0x7f61ab145700, query id 2839 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146983 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146983 lock_mode X locks rec but not gap
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146983 lock_mode X locks rec but not gap

@2 show engine innodb status

---TRANSACTION 146984, ACTIVE 23 sec
3 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2842 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146984 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X locks gap before rec

唯一索引删除一条已经标记删除的记录的锁情况为：

• 加锁等待时: 2 lock struct(s) ，持有IX锁，等待记录上的X锁

• 加锁成功时：3 lock struct(s)，持有IX,行锁，和gap锁，和非唯一索引删除一条标记为已删除的记录的情况一模一样。

三. 总结

1. 在非唯一索引的情况下，删除一条存在的记录是有gap锁，锁住记录本身和记录之前的gap
2. 在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有gap存在
3. 非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加gap锁
4. 通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的gap
5. RC 情况下是没有gap锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。