innodb的索引和锁

概述

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会收到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

innoDB存储引擎索引概述

InnoDB存储引擎支持一下几种常见的索引：
（1）、B+树索引
（2）、全文索引
（3）、哈希索引
InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
B+树索引是传统意义上的索引，这是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值快速找到数据。
注意：B+树中的B不是代表二叉（Binary），而是代表平衡（Balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。
另外，B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读取到内存，再在内存中进行查找，最后得到要查找的数据。

数据结构与算法

二分查找法

二分查找法（Binary search）也被称为折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是：将记录按照有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。

二叉查找树和平衡二叉树

B+树是通过二叉查找树，再有平衡二叉树，B树演化而来。在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此通过中序遍历得到的键值的排序输出。
但是若想最大性能地构造一棵二叉查找树，需要这颗二叉查找树是平衡的，从而引出了新的定义平衡二叉树，或称为AVL树。
平衡二叉树的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。平衡二叉树的查询性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一颗最优的二叉树，但是最优的二叉树的建立和维护需要大量的操作，因此，用户一般只建立一颗平衡二叉树即可。
平衡二叉树的查询速度的确很快。但是维护一颗平衡二叉树的代价是非常大的。通常来说，需要一次或多次左旋和右旋来得到插入或更新后树的平衡性。

B+树

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依然排序，同时需要考虑插入到B+树的三种情况，每种情况都可能导致不同的插入算法。

B+树的删除操作

B+树使用填充因子来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子节点的记录依然排序，同插入一样，B+树的删除操作同样需要考虑一下情况

B+树索引

B+树索引的本质就是B+树在数据库中的实现。但是B+索引在数据库中的实现。但是B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般在2-4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。数据库中的B+树索引一般可以分为聚集索引和辅助索引。但是不管是聚集索引还是辅助索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

InnoDB存储引擎表是索引的组织表，即表中数据按照主键顺序存放，而聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也就是聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在大多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现一段范围的数据页需要扫描。

B+树索引的使用

联合索引

联合索引是指对表上的多个列进行索引。联合索引内部，从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB存储引擎会在行级别上对表数据上锁。

lock与latch

lock与latch都可以被称为锁，但是却有着截然不同的含义。
latch一般被称为闩锁（轻量级锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在InnoDB存储引擎中，latch又可以分为mutex（互斥量）和relock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。此外，lock是有死锁机制的。
对于InnoDB存储引擎中的latch，可以通过命令SHOW ENGINE INNODB MUTEX来进行查看。

InnoDB存储引擎中的锁类型

innoDB存储引擎实现了如下两种标准的行级锁：
（1）、共享锁（SLock），允许事务读一行数据。
（2）、排他锁（XLock），允许事务删除或更新一行数据。
如果一个事务T1已经获得了行R的共享锁，那么另外的事务T2可以立即获得行R的共享锁，因为读取并没有改变行R的数据，称这种情况为锁兼容。但若有其他食物T3想获得行R的排他锁，则其必须等待事务T1、T2释放行R上的共享锁这种情况称为锁不兼容。

锁问题

脏读

脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。而所谓脏数据是指事务对缓冲池中行记录的修改，并且还没有提交（commit）。
对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。
脏数据却截然不同，脏数据是指未提交的数据，如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。
脏读指的就是在不同的事务下，当前事务可以读取到另外事务未提交的数据。
脏读现象在生产环境中并不常发生，脏读发生的条件是需要事务的隔离级别为READ_UNCOMMITED，而目前绝大部分的数据库都至少设置成READ COMMITED。innoDB存储引擎默认的事务隔离级别为READ_REPEATABLE。

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些DML操作。因此，在第一个事务中的两次读数据指间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。
不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的确是已经提交的数据，但是其违反了数据库事务一致性的要求。
一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身不会带来很大的问题。

丢失更新

丢失更新是另一个锁导致的问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。
避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。

阻塞

因为不同锁之间兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行。
需要牢记的是，在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚。

死锁

死锁的概念

死锁是指两个或两个以上的事务在执行过程中，因为争夺资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。解决死锁的问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的发生。然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难发现并且浪费资源。
解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在innodb存储引擎中，参数innodb_lock_wait_timeout用来设置超时时间。

事务

事务时数据库区别于文件系统的重要特性之一。在文件系统中，如果正在写文件，但是操作系统突然崩溃了，这个文件就很有可能被破坏。当然，有一些机制可以把文件恢复到某个时间点。不过，如果需要保证两个文件同步，这些文件系统可能就显得无能为力了。
这正是数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存。
innodb存储引擎中的事务完全符合ACID的特性。ACID是以下4个词的缩写：
（1）、原子性（atomicity）
（2）、一致性（consistency）
（3）、隔离性（isolation）
（4）、持久性（durability）

概述

事务可由一条简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别文件系统的重要特性之一。

1. Read Uncommitted（读取未提交内容）
   在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
2. Read Committed（读取提交内容）
   这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
3. Repeatable Read（可重读）
   这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
4. Serializable（可串行化）
   这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

Mysql索引失效的情况

首先，复习一下索引的创建：

• 普通的索引的创建：
  CREATE INDEX (自定义)索引名 ON 数据表(字段);

• 复合索引的创建：
  CREATE INDEX (自定义)索引名 ON 数据表(字段，字段，。。。);

删除索引：DROP INDEX 索引名;

以下通过explain显示出mysql执行的字段内容：

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

索引查询失效的几个情况：

• 1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
• 2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 3、组合索引，不是使用第一列索引，索引失效。
• 4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
• 6、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
• 7、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
• 8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
  索引失效分析工具：

可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。

回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。