数据库的索引与锁（）

一、索引

索引是帮助MYSQL高效获取数据的数据结构，可以得到索引的本质，索引是数据结构，有一列或多列字段。

1.1索引的基础知识

首先知道索引可以加快数据库的检索速度，表经常进行INSERT/UPDATE/DELETE操作就不要建立索引，索引会降低插入删除修改等维护任务的速度。 2、索引需要占物理和数据空间 3、索引具有最左匹配原则  4、索引的聚集索引和非聚集索引 5、Mysql支持Hash索引和B+树索引两种。

首先了解Mysql的基本存储结构是页（所有的记录都存在页里面），各个数据页可以组成一个双向链表，而每个数据页中的记录又可以组成单向链表，每个数据页中的记录又可以组成单向链表。每个数据页都会存储在里面的记录生成一个页目录，通过主键查找某条记录的时候可以再页目录中使用二分快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可以快速找到指定的记录。   从最小记录开始依次遍历单链表中的每条记录。

1.2索引提高检索的速度

索引做了些什么可以让我们查询加快速度呢？实际上就是将无序的数据变为有序的数据。

如果没有索引我们就需要遍历双向链表来定位对应的页，现在通过“”目录”就可以很快定位到对应的页上，其实底层就是B+树，B+树作为树的一种实现，能够让我们很快查找出对应的记录。

1.3索引降低增删改的速度

B+树是平衡树的一种，所谓平衡树就是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。如果一棵普通的树在极端的情况下，是退化成链表的，树的优点就不存在了。

B+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的（类似又矮又胖的那种）这样一来我们检索的时间复杂度是O（logn）。B+树是一颗平衡树，如果我们对这颗树删改的话，那肯定破坏他的原有结构。要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，呆滞索引会降低增删改的速度。（Innodb数据文件是一颗B+树，非单调的书剑会造成插入新纪录时的数据文件为了维持B+树的特性而频繁撕裂，这样非常低效）

1.4哈希索引

除了B+树之外，还有一种常见的哈希索引。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点的逐级寻找，只需要一次hash算法like定位到响应位置，速度非常快。

本质上就是把键值对换算成新的hash值，来根据这个hash值来定位。

hash的缺点1、hash索引也没办法利用索引完成排序，2、不支持最左匹配原则 3、在大量重复键值的情况下，hash索引的效率也是最低的，存在hash碰撞的问题。 4、不支持范围查询

1.5聚集与非聚集索引

聚集索引就是以主键创建的索引，非聚集索引就是以非主键创建的索引。

区别：聚集索引在叶子节点存储都是表中数据，非聚集索引在叶子节点存储的是主键与索引列，

使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。（拿到主键再查找这个过程回表）

非聚集索引也叫作二级索引，不用纠结那么多名词，将其等价即可~

非聚集索在建立的时候未必是单列的，可以多个列来创建索引。

创建多个单列（非聚集）索引的时候，会生成多个索引树（所以过多的创建索引会占用磁盘空间）。

在创建多列索引中也涉及到了一种特殊的索引——》覆盖索引

1.6索引的最左匹配原则

最有匹配原则：索引可以简单如一个列a，也可以复杂如（a,b,c,d），即联合索引。  如果是联合索引，那么key也由多个列组成，同时，索引只能用查找key是否存在（相等），遇到范围查询（<>between like左匹配）等就不进一步匹配了，后续退化成线性查找。（因此，列的排列顺序决定了可命中索引列数）

如索引（a,b,c,d）查找条件a=1 and b=2 and c=3 and d<4，则会在每个节点依次命中a,b,c,无法命中d，因为索引只能命中相等，而不能是范围匹配。

1.7 = in 自动优化顺序

不需要考虑=，in等顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

索引总结：

1、最左匹配原则，非常中亚哦，Mysql会 一直向右匹配直到遇到范围查询(> < Between like)就停止匹配。

2、尽量选择区分度高的列作为索引。别用性别这种

3、索引列 不能参与计算，尽量保持干净。比如FROM UNIXTIME(create_time) = '2016-06-06'就不能使用索引，原因很简单，B+树中存储的都是数据表中字段值，但是进行索引时，需要把所有的元素都应用函数才能比较，所以时间写成UNIX_TIMESTAMP（‘2016-06-26’）

5、尽可能的去扩展索引而不是建立索引，比如表中有了a索引，现在要加上变成a b索引，那么只需要修改原来的索引即可。

6、单个多列组合索引和多个单索引影的查询效果不同，因为执行时只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

 

锁

在mysql中有一大堆锁名词，排它锁，共享锁，表锁，页锁，间隙所，行锁，读锁，写锁，乐观锁、悲观锁、死锁。等等。。。。

2.1锁的知识

首先很多时候这些锁并不是我们去规定的，在一般情况下还是可以跑的好好的。

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给设计数据集加上排他锁。

对于MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE DELETE INSERT）前，会自动给涉及的表加写锁，这个过程并需要用户干预。

2.2表锁简单介绍

首先从锁的粒度，可以分为两大类 表锁与行锁

表锁：开销小、加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低。

行锁：开销大、加锁慢、会出现死锁、锁定粒度小，发生锁冲突的概率低，并发度高。

不同的存储引擎支持的锁粒度是不一样的。InnoDB行锁与表锁都支持，MyISAM只支持表锁。

InnoDB只有通过索引条件检索数据才能使用行锁，否则，InnoDB将使用表锁，也就是说InnoDB的行锁是基于索引的。

表锁又分为两种模式：表读锁与表写锁，从下图中可以清晰看到，在表读锁和表写锁的环境下，读读不会阻塞，读写阻塞，写写阻塞。

读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁。

读写阻塞：当前用户在读数据，其他用户不能修改当前用户的数据，会加锁！

写写阻塞，当前用户在修改数据，其他用户不能修改当前用户修改的数据，会加锁。（这个是对表锁的两种模式）

备注如果有一个线程想要获取读锁，同时另一个线程想要获取写锁，在mysql里面，写锁是优先与读锁的。

这里注意个区别：MyISAM可以查询与插入操作一起并发执行，通过指定一种模式，MyISAM允许一个进程读表的同时，一个进程从表尾插入记录。但是InnoDB存储引擎是不支持的。

2.3乐观锁与悲观锁

无论是Read committed 还是Repeatable read隔离级别，都是为了解决读写冲突的问题。 例如在可重复读的条件下，用户李四的操作丢失掉了。丢失更新：一个事务的更新覆盖了其他事务的更新结果。

解决办法就是 使用Serializable隔离级别，事务是串行执行的。

1、乐观锁是一种思想，具体就是表中有一个版本字段，第一次读的时候，获取到这个字段，处理完业务逻辑开始更新的时候，需要再次查看该字段是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。

2、悲观锁是从数据库层加锁，都会阻塞去等待锁。

举例说明：

悲观锁就是  select * from for update   在select语句后面加了for update相当于加了排它锁，加了写锁以后，其他的事务就不能对它修改了，需要等待当前事务修改完之后才可以修改。 例如张三执行了这个操作，李四就无法对这个记录修改。

乐观锁：不是数据库层面上的锁，是需要自己手动去加的锁，一般我们添加一个版本字段来实现，name sex  string  +version，用来判断。版本不一致就回滚

2.4间隙锁

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙“GAP”，InnoDB对这个间隙加锁的机制就是间隙锁。（间隙锁只在Repeatable read隔离级别下使用）

例如emp上只有101条记录，其empid的值分别是1，2，，，，100,101 

Select * from emp where empid >100 for update

上面一个范围查询，InnoDB不仅会对符合条件的empid为101的加锁，同时对大于101的间隙都会加锁。

2.5死锁

并发的问题少不了死锁，在Mysql中同样会存在死锁的问题，但一般来说MYSQL通过回滚帮我们解决了很多死锁的问题，但死锁是无法完全避免的。

我们应该注意

1、以固定的顺序访问表和行，比如对两个job批量更新的情形，简单方法是对id列表先排序后执行，这样就避免了交叉等待锁的情形，将两个事务sql顺序调整一致，也能避免死锁 。

2、大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3、在同一事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4、降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为GAP锁造成死锁。

5、为表添加合理的索引，可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

2.6锁总结

在MyISAM存储引擎中，当执行SQL语句的时候是自动加的。

InnoDB存储引擎，如果没有使用索引，表锁也是自动加的。现在我们大多是使用MYSQL的InnoDB，InnoDB支持行锁

 

乐观锁其实是一种思想，正如其名，认为不会锁定的情况下更新数据，如果发现不对劲，才不会更新。在数据库中往往添加一个version字段来实现。

悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来吧数据锁住，其他事务不能修改，直至提交当前事务。