什么是索引
索引是一种高效获取数据的存储结构，例：hash、 二叉、 红黑，索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
Mysql为什么不用上面三种数据结构而采用B+Tree ？
若仅仅是select * from table where id=1, 上面三种算法可以轻易实现，但若是select * from table where id<6, 就不好使了，它们的查找方式就类似于”全表扫描”，因为他们的高度是不可控的。B+Tree的高度是可控的，mysql通常是3到5层。注意：B+Tree只在最末端叶子节点存数据，叶子节点是以链表的形势互相指向的
索引类型
FULLTEXT
全文索引，目前只有MyISAM引擎支持，只有CHAR、VARCHAR，TEXT列上可以创建全文索引，它的出现是为了解决WHERE name LIKE "%word%"这类针对文本的模糊查询效率较低的问题
HASH
哈希索引，由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高
BTREE
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf，实质上是B+Tree，这是MySQL里默认和最常用的索引类型
建议
通常我们对查询次数比较频繁，值比较多的列才建索引
存储引擎
MySQL支持两种存储引擎，MyiSAM引擎和InnoDB引擎，均采用B+Tree作为索引结构
MyiSAM引擎：非聚集索引
若以这个引擎创建数据库表create table user ...，它实际是生成三个文件
user.myi 索引文件
user.myd 数据文件
user.frm 数据结构类型
如下图：当我们执行select * from user where id = 1的时候，它的执行流程。
1、查看该表的myi文件有没有以id为索引的索引树
2、根据这个id索引找到叶子节点的id值，从而得到它里面的**数据地址**(叶子节点存的是索引和数据地址)
3、根据数据地址去myd文件里面找到对应的数据返回出来
InnoDB引擎：聚集索引
若以这个引擎创建数据库表create table user...，它实际是生成两个文件：
user.ibd 索引数据文件
user.frm 数据结构类型
InnoDB与MyiSAM最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息，因此InnoDB表必须要有主键)，若此时，你在其他列创建索引例如name，它就会在idb中创建一个以name为索引的索引树，(叶子节点存的是索引和主键索引)
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录
如下图：当我们执行select * from user where name = ‘test’的时候，它的执行流程。
1、找到name索引树
2、根据name的值找到该树下叶子的name索引和主键值
3、用主键值去主键索引树去叶子节点到该条数据信息
MyiSAM引擎和InnoDB引擎的区别
| 引擎类型 | 全文索引 | 事务 | 保存表的具体行数 | 行锁 | 表锁 |
|---|---|---|---|---|---|
| MyiSAM | 支持 | X | 支持 | X | 支持 |
| InnoDB | 支持（5.6之后） | 支持 | X | 支持 | 支持 |
建议
不用事务的时候，count计算多的时候适合MyiSAM引擎。对可靠性要求高就是用InnoDB引擎。推荐用InnoDB引擎
B-Tree与B+Tree
考虑到磁盘I/O是非常高昂代价的操作，计算机系统做了一些优化，当一次I/O时，不光会把当前磁盘地址的数据读取到内存中，而且会把相邻的数据也读取到内存缓冲区中，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快访问到。
因此索引的数据结构，要把磁盘I/O次数限制在一个很小的数量级，最好是一个常量数量级，B+Tree应运而生
B-Tree（平衡多路查找树）
为磁盘等外存储设备设计的一种平衡查找树，系统从磁盘读取数据到内存时是以磁盘块位基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取。B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，因此力求达到树的深度不超过3，也就是I/O不需要超过三次；
由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率；而三次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。
B+Tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，B-Tree中每个节点中有key，也有data，而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
在B+Tree中所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度；
B+Tree在B-Tree的基础上有两点变化
1、数据是存在叶子节点中的
2、数据节点之间是有指针指向的
由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。
因此可以对B+Tree进行两种查找运算，一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找
注意
InnoDB引擎采用的是B+Tree的方式建立索引的
特性
- 单节点能存储更多数据，使得磁盘IO次数更少。
- 叶子节点形成有序链表，便于执行范围操作。
- 聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。
其他辅助索引的B+Tree的表现形式
这里辅助索引，也称为二级索引，叶节点储存的信息是主键的信息。如图：
InnoDB辅助索引不存储数据的信息（或者索引）就是为了避免数据地址发生迁移的时候不会跟着修改辅助索引的叶节点信息
B+Tree关键性质
I/O的次数取决于B+Tree的高度H，假设当前数据表的数据为N，每个磁盘块的数据项的数量是M，则有：H=log(M+1)N，当数据量N一定的情况下，M越大，H越小；而M=磁盘块大小/数据项大小，磁盘块大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度也就越低。这也就是为什么每个数据项，即索引字段要尽量的小，比如int占4个字节，要比bigint的8个字节小一半。这也是为什么B+Tree要求把真实数据放在叶子节点内而不是内层节点内，一旦放到内层节点内，磁盘块的数据项会大幅度的下降，导致树层级的增高。当数据项为1时，B+Tree会退化成线性表B+Tree的数据项是复合性数据结构，比如复合索引（name，age，gender）的时候，B+Tree是按照从左到右的顺序来建立搜索树的，比如当（小张，22，女）这样的数据来检索的时候，B+Tree会优先比较name来确定下一步的搜索方向，如果name相同再依次比较age和gender，最后得到检索的数据。但是，当（22，女）这样没有name的数据来的时候，B+Tree就不知道下一步该查哪个节点，因为建立搜索树的时候，name就是第一个比较因子，必须根据name来搜索才知道下一步去哪里查询。比如，当（小张，男）这样的数据来检索时，B+Tree就可以根据name来指定搜索方向，但下一字段age缺失，所以只能把名字是“小张”的所有数据都找到，然后再匹配性别是“男”的数据了。这个是非常重要的一条性质，即索引的最左匹配特性
建立索引的原则
- 最左前缀匹配原则【重要】
最左前缀匹配原则：当建立了复合索引(A、B、C)（相当于创建了索引A、AB、ABC），在sql中where A = x会用到索引A，where B = x,C = x不会用到索引，where A = x,C = x会用到索引A
MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配，比如：a = 1 AND b = 2 AND c > 3 AND d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引，则都可以用到，a,b,d的顺序可以任意调整
参考文献：最左前缀原理与相关优化
等于
=和in可以乱序。比如a = 1 AND b = 2 AND c = 3建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式尽量选择区分度高的列作为索引【重要】
索引列不能参与计算
单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引
索引的优化方法
- 索引不会包含有NULL值的列
只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL
- 使用短索引
假设，如果有一个数据类型为CHAR(255)的列，在前10个或20个字符内，绝大部分数据的值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作
- 索引列排序
MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引
- LIKE语句操作
一般情况下，不建议使用LIKE操作；如果非使用不可，如何使用也是一个研究的课题。LIKE “%aaaaa%”不会使用索引，但是LIKE “aaa%”却可以使用索引
聚集索引和非聚集索引
- 聚集索引
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引）
- 非聚集索引
非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。记录的物理顺序与逻辑顺序没有必然的联系
对于B+Tree的数据结构来说，聚集索引：叶节点是数据节点。非聚簇索引：叶节点是指向数据库的地址
分析【重要】
每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。聚集索引一张表只能创建一个，非聚集索引一张表可以创建多个，在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键（Primary Key）进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集
非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量
锁
什么是锁
锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中，是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说，锁对于数据库而言就显得尤为重要
MySQL锁的比较
相对于其他的数据库而言，MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：
| 引擎类型 | 表锁 | 行锁 | 页锁 |
|---|---|---|---|
| MyiSAM | 支持 | X | X |
| BDB | 支持 | X | 支持 |
| InnoDB | 支持 | 支持 | X |
开销、加锁速度、死锁、粒度、并发性能比较
| 锁类型 | 开销 | 加锁速度 | 死锁 | 粒度 | 并发性能 |
|---|---|---|---|---|---|
| 表锁 | 小 | 快 | 不会 | 锁定力度大，发生锁冲突概率高 | 低 |
| 行锁 | 大 | 慢 | 会 | 锁定粒度小，发生锁冲突的概率低 | 高 |
| 页锁 | 两者之间 | 两者之间 | 会 | 两者之间 | 两者之间 |
表锁更适用于以查询为主，只有少量按索引条件更新数据的应用；行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。（PS：由于BDB已经被InnoDB所取代，我们只讨论MyISAM表锁和InnoDB行锁的问题）
MyiSAM：表锁
MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。
锁兼容【重点】
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性如下表所示：
| 锁模式\请求锁模式是否兼容当前锁 | 无锁 | 读锁 | 写锁 |
|---|---|---|---|
| 表共享读锁 | 兼容 | 兼容 | 冲突 |
| 表独占写锁 | 兼容 | 冲突 | 冲突 |
可见，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如下表所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止
写阻塞读demo
| session_1 | session_2 |
|---|---|
获取写锁：lock table t_test write |
|
| 执行查询/添加/更新/删除语句：select/insert/update/delete | 执行查询【等待】 |
释放写锁：unlock tables |
【等待】 |
| 获得锁，查询返回 |
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本示例中，显式加锁基本上都是为了方便而已，并非必须如此。
读阻塞写demo
| session_1 | session_2 |
|---|---|
获取读锁：lock table t_test read |
|
| 执行查询该表 | 其他session也可以查询该表的记录 |
| 执行查询其他表【Error：需要获取其他表的锁】 | 其他session可以查询或者更新未锁定的表 |
| 执行添加/更新/删除【Error：需要获取写锁】 | 其他session更新锁定的表会等待【等待】 |
释放读锁：unlock tables |
【等待】 |
| 该session获得锁，更新操作完成 |
并发插入（Concurrent Inserts）
上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
- 当
concurrent_insert设置为0时，不允许并发插入。 - 当
concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 - 当
concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
写优先调度机制
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。
- 通过指定启动参数
low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。 - 通过执行命令
SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。 - 通过指定
INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。
InnoDB：行锁
InnoDB实现了以下两种类型的行锁及其意向锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
| 当前锁模式\请求锁模式是否兼容 | X | IX | S | IS | 无锁 |
|---|---|---|---|---|---|
| X | 冲突 | 冲突 | 冲突 | 冲突 | 兼容 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 | 兼容 |
| IS | 冲突 | 兼容 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁：
- 共享锁（S）：SELECT * FROM table_name WHERE …
LOCK IN SHARE MODE - 排他锁（X）：SELECT * FROM table_name WHERE …
FOR UPDATE
用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁
共享锁demo
| session_1 | session_2 |
|---|---|
获取共享锁：select * from t_test where id = 1 lock in share mode |
|
| 其他session仍然可以查询记录：select * from t_test where id = 1 | |
可以获取共享锁：select * from t_test where id = 1 lock in share mode |
|
| 对锁定的记录执行更新操作，等待锁：update t_test xx where id = 1【等待】 | |
| 对锁定的记录执行更新操作，出现死锁异常退出：update t_test xx where id = 1【死锁】 | |
| 获得锁，更新成功 |
死锁是如何发生的
假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁，当 T1 的 select 执行完，准备执行 update 时，根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update.在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是产生死锁。
排他锁demo
| session_1 | session_2 |
|---|---|
获取排他锁：select * from t_test where id = 1 for update |
|
| 其他session仍然可以查询记录：select * from t_test where id = 1 | |
对该记录获取共享锁：select * from t_test where id = 1 lock in share mode【等待】 |
|
| 对锁定的记录执行更新操作，更新后释放锁：update t_test xx where id = 1 | |
| 获得锁，得到其他session提交的记录 |
InnoDB行锁实现方式：只给索引项加锁
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！，在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能
在不通过索引条件查询的时候加行锁，InnoDB使用的是表锁，而不是行锁
设置表t_test（id,name），id为主键索引，name是普通字段
| session_1 | session_2 |
|---|---|
获取排他锁（实际获取的是表锁）：select * from t_test where name = ‘1’ for update |
|
获取排他锁，等待表锁释放：select * from t_test where name = ‘2’ for update【等待】 |
在通过索引条件查询的时候加行锁，InnoDB使用的是行锁
设置表t_test（id,name），id为主键索引，name是普通字段
| session_1 | session_2 |
|---|---|
获取排他锁：select * from t_test where id = 1 for update |
|
获取排他锁：select * from t_test where id = 2 for update |
使用相同索引键的阻塞demo
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的
设置表t_test（id,name），id为主键索引，name是普通字段，包含数据(1,1)(1,2)
| session_1 | session_2 |
|---|---|
获取排他锁：select * from t_test where id = 1 and name = ‘1’ for update |
|
获取排他锁，由于索引键被锁，等待行锁释放：select * from t_test where id = 1 and name = ‘2’ for update【等待】 |
使用不同索引的阻塞例子
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁
设置表t_test（id,name），id为主键索引，name是普通索引，包含数据(1,1)(1,4)(2,2)
| session_1 | session_2 |
|---|---|
获取排他锁：select * from t_test where id = 1 for update |
|
获取排他锁：select * from t_test name = ‘2’ for update |
|
获取排他锁，由于索引键被锁导致记录被锁，等待行锁释放：select * from t_test name = ‘4’ for update【等待】 |
在分析锁冲突时，先检查SQL的执行计划
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引
InnoDB：间隙锁（Next-Key锁）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）
举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，下面的SQL：1
select * from emp where empid > 100 for update
这是一个范围条件的检索，InnoDB不仅会对符合条件的empid=101的记录加锁，也会对empid>101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
设置表t_test（id），id为主键索引，包含数据1,2,......,100,101共101条数据
| session_1 | session_2 |
|---|---|
获取排他锁（对不存在的数据加锁，即为间隙锁）：select * from t_test where id = 102 for update |
|
| 插入新数据（由于间隙锁，阻塞等待）：insert into t_test(id) values(102)【等待】 | |
回滚：rollback（释放间隙锁） |
|
| 插入成功 |
什么时候使用表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁：
第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。在InnoDB下，使用表锁要注意以下两点：
使用
LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁在用
LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表
死锁、活锁与饿死
什么是饿死
饿死（starvation）是一个线程长时间得不到需要的资源而不能执行的现象。有人饿死并不代表着出现了死锁。
什么是死锁
两个线程相互竞争资源，线程A等线程B释放，线程B等线程A释放，产生死锁
什么是活锁
两个线程相互礼让资源，线程A让线程B先用，线程B让线程A先用，产生活锁
关于死锁
MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生