1、在索引之前，我对索引有如下认识：索引可以加快数据库的检索速度；如果表经常进行INSERT/UPDATE/DELETE操作，就不要创建索引，换句话说：索引会降低插入、删除、修改等维护任务的速度；索引需要占用物理和数据空间；知道索引的最左匹配原则；知道索引的分类：聚簇索引和非聚簇索引；Mysql支持Hash索引和B+树索引；看起来你什么都懂，但是当面试让你说的时候，你可能会GG：为什么使用索引可以加快数据库的检索速度？为什么说索引会降低插入、删除、修改等维护任务的速度；索引的最左匹配原则指的是什么？Hash索引和B+树索引有什么区别？哪一个更常用？InnoDB存储支持吗？聚簇索引和非聚簇索引有什么区别？......1.让我们谈谈索引的基础知识。首先，Mysql的基本存储结构是页（页中存放记录）：每个数据页可以组成一个双向链表；每个数据页中的记录可以形成一个单向链表；每个数据页都会为其中存储的记录生成一个页目录，通过主键查找记录时，可以在页目录中使用二分法快速定位到对应的槽位，然后遍历组中的记录对应槽位快速查找指定记录；使用其他列（非主键）作为查找条件：只从最小的记录开始依次遍历单向链表中的每条记录。所以，如果我们写select*fromuserwhereusername='Java3y'没有任何优化的sql语句，它默认会这样：定位到记录所在的页面，需要遍历双向链表找到页面从所在页面开始由于不是基于主键查询，所以只能遍历所在页面的单向链表。很明显，这种搜索在数据量很大的情况下会很慢！2、索引提高了检索速度。索引对加快我们的查询有什么作用呢？其实就是把无序的数据（相对）改成有序的：找到id为8的记录，简单步骤：显然：不使用索引，我们需要遍历双向链表定位到对应的页面，现在通过“目录”可以快速导航到相应的页面！其实底层结构就是B+树。B+树作为树的一种实现，可以让我们快速的找到对应的记录。3、索引降低了增删改查的速度。如果普通树在极端情况下可以退化为链表（树的优点将不复存在）B+树是一种平衡树，不会退化为链表。对于链表来说，树的高度比较低（基本符合矮胖（平衡）结构）【这样我们检索的时间复杂度是O(logn)】！从上一节的图中我们也可以看出，建索引其实就是建B+树。B+树是一棵平衡树。如果我们对这棵树进行增删改查，必然会破坏其原有的结构；为了保持一棵平衡的树，必须做额外的工作。因为这些额外的工作开销，索引会降低增删改查的速度；4.除了B+树，还有一种常见的哈希索引。哈希索引使用一定的哈希算法将键值转换为新的哈希值。查找的时候不需要像B+树那样从根节点到叶子节点一步步查找。只需要一种哈希算法就可以立即定位到对应的位置，速度非常快。本质上就是把key值转换成一个新的hash值，根据这个hash值定位。看起来哈希索引很强大，但实际上哈希索引有几个局限性（根据其本质原理）：哈希索引不能使用索引完成排序；不支持最左匹配原则；当有大量重复键值的情况下，哈希索引的效率也极低---->哈希冲突问题；不支持范围查询；5、InnoDB是否支持哈希索引？主流还是使用B+树索引。对于哈希索引，InnoDB采用自适应哈希索引（哈希索引的创建由InnoDB存储引擎自动优化，我们无法干预）！6、聚簇索引和非聚簇索引的简单总结：聚簇索引是用主键创建的索引；非聚集索引是使用非主键创建的索引；区别：聚集索引将数据存储在叶节点的表中；非聚集索引在叶子节点存储主键和索引列；使用非聚集索引查询数据时，获取叶子上的主键，然后找到要查找的数据。（获取主键再进行查找的过程称为回表）非聚集索引也称为二级索引。这么多名词不用管，等价就好~非聚簇索引创建的时候不一定是单列的，可以是多列创建索引。此时，就涉及到对哪些列进行索引，哪些列不进行索引的问题（最左匹配原则-->后文介绍）在创建多个单列（非聚簇）索引时，会创建多个索引树generated(所以创建太多索引会占用磁盘空间)创建多列索引还涉及到一个特殊的索引-->覆盖索引我们之前知道如果不是聚簇索引，叶子节点存放的是主键+列值，finally到“回表”，即通过主键再次查找。这会更慢。覆盖索引就是把要查询的列和索引匹配起来，不回表！例如：现在我创建了一个索引（用户名，年龄），在查询数据的时候：selectusername,agefromuserwhereusername='Java3y'andage=20。很明显上面的查询是有索引的，列到被查询存在于叶节点中！所以，不需要回表~所以，尽量使用覆盖索引~7.索引的最左匹配原则最左匹配原则：一个索引可以简单到一列（a）或者复杂到多列（a,b,c,d），即联合索引。如果是联合索引，那么key也是由多列组成的。同时，索引只能用来查找key是否存在（相等），遇到范围查询时不能进一步匹配（>、<、between、like左匹配）等，随后退化为线性搜索。因此，列的排列顺序决定了可以最佳索引的列数。例子：如果有一个索引（a,b,c,d），查询条件a=1andb=2andc>3andd=4，它会在每个节点依次检查a，b，c，并且不能***d。（很简单：索引***只能相等，不能范围匹配）8、=，在自动优化顺序中不需要考虑=，in等的顺序，mysql会自动优化这些条件的顺序尽可能匹配可能多的索引列。例子：如果有索引（a,b,c,d），查询条件c>3andb=2anda=1andd<4anda=1andc>3andb=2andd<4，etc.一切皆有可能，MySQL会自动优化为a=1andb=2andc>3andd<4，依次安装a、b、c。9.索引总结索引是数据库中非常重要的一个知识点！以上其实就是索引最基本的东西。要创建一个好的索引，必须考虑到很多方面：1.最左前缀匹配原则。这是一个非常重要非常重要非常重要的原则（重要的事情说三遍）。MySQL会一直向右匹配，直到遇到范围查询（>,<,BETWEEN,LIKE）后停止匹配。3、尽量选择鉴别度高的列作为索引。区分度的公式是COUNT(DISTINCTcol)/COUNT(*)。表示字段唯一性的比例，比例越大，我们扫描的记录越少。4、索引列不能参与计算，尽量保持列“干净”。例如，如果FROM_UNIXTIME(create_time)='2016-06-06'，则无法使用该索引。原因很简单。B+树存储的所有字段都是数据表中的字段值，但是在查找的时候，需要对所有元素应用函数。相比之下，这样的价格显然是太高了。所以语句应该写成：create_time=UNIX_TIMESTAMP('2016-06-06')。5、尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要增加(a,b)的索引，那么只需要修改原来的索引即可。6、单个多列复合索引和多个单列索引的搜索查询效果是不同的，因为MySQL在执行SQL时只能使用一个索引，会从多个单列索引中选择限制性最大的索引。2、mysql中的锁看起来很复杂，因为有很多东西和名词：排他锁、共享锁、表锁、页锁、间隙锁、意向排它锁、意向共享锁、行锁、读锁、写锁，乐观锁，悲观锁，死锁。这些名词的一些博客直接写了锁的英文缩写--->X锁、S锁、IS锁、IX锁、MMVC...锁相关的知识涉及到存储引擎、索引、事务隔离级别。相关....这给刚接触数据库锁的小伙伴们带来了很大的困扰~~~所以下面我就简单梳理一下数据库锁的知识点，希望大家看完后觉得有帮助。1、为什么要学习数据库锁？很多人在开发的时候应该很少会注意到这些锁的问题，也很少会在程序中加锁（库存除外，对数量精度要求极高）一般都听说过常说的乐观锁和悲观锁。了解了基本的意思就没了~~~放心：即使我们不知道这些锁知识，我们的程序在正常情况下还是可以正常运行的。因为这些锁是数据库为我们隐式加的：对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及到的数据集加上排他锁（X）；MyISAM会在执行查询语句前自动对所有涉及的表加读SELECT锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动对涉及的表加写锁。这个过程不需要用户干预；手动加锁只是在某些特定的场景下才需要，学习数据库锁知识的目的是让我们在特定的场景下使用它，更好地控制我们编写的程序。在和别人谈论数据库技术的时候，我们三言两语就可以建立起自己的知识库体系！2、表锁简介首先，从锁的粒度上，我们可以分为两类：表锁成本小，加锁速度快；不会发生死锁；锁性强，锁冲突概率高，并发性最好；行锁很昂贵，加锁很慢；会有死锁；锁粒度小，锁冲突概率低，并发度高；不同存储引擎支持的锁粒度不同：InnoDB行锁和表锁都支持！MyISAM只支持表锁！InnoDB只有使用行级锁通过索引条件检索数据，否则，InnoDB将使用表锁也就是说，InnoDB的行锁是基于索引的！表锁分为两种模式：TableReadLockTableWriteLock从下图可以清楚的看出，在表读锁和表写锁的环境下：读写不阻塞，读写阻塞，写和写作阻塞！读读不阻塞：当前用户正在读数据，其他用户也在读数据，不会被锁定。读写阻塞：当前用户正在读取数据，其他用户无法修改当前用户读取的数据，会被锁定！写写阻塞：当前用户正在修改数据，其他用户无法修改当前用户正在修改的数据，会被锁定！由上可见：读锁和写锁是互斥的，读写操作是串行的。如果一个进程想要获取一个读锁，另一个进程想要获取一个写锁。在mysql中，写锁优先于读锁！写锁和读锁优先级的问题可以通过参数调整：max_write_lock_count和low-priority-updates值得注意的是：MyISAM可以支持并发查询和插入操作。哪种模式可以通过系统变量concurrent_insert来指定。在MyISAM中，默认为：如果MyISAM表中没有空洞（即表中间没有被删除的行），MyISAM允许一个进程读表，而另一个进程从表中读。在表的末尾插入记录。但是InnoDB存储引擎不支持！3.乐观锁和悲观锁，无论是Readcommitted还是Repeatableread隔离级别，都是为了解决读写冲突的问题。我们来考虑一个Repeatableread隔离级别下的问题：此时用户李四的操作丢失了：Lostupdate：一个事务的更新覆盖了其他事务的更新结果。（ps：我还没想到更好的例子来说明丢失更新的问题，虽然上面的例子也丢失了，但是一定程度上是可以接受的，不知道有没有人能想到一个丢失的例子不可接受的更新。。。）解决方案：使用Serializable隔离级别，事务是串行执行的！乐观锁悲观锁乐观锁是一种思想。具体实现是表中有一个version字段，这个字段在第一次读取的时候获取。处理完业务逻辑开始更新后，需要再次检查该字段的值是否与第一次相同。如果相同则更新，否则拒??绝。之所以叫乐观，是因为这种模式不从数据库加锁，等到更新了再判断是否可以更新。悲观锁是数据库级别的锁，会阻塞等待锁。3.1.悲观锁所以，按照上面的例子。如果我们使用悲观锁，其实很简单（手动加行锁即可）：select*fromxxxxforupdate在select语句后加forupdate，相当于加了排他锁（写锁）。加写锁后，其他事务不能修改！需要等待当前事务被修改后才能修改。也就是说如果张三使用select...forupdate，李四无法修改这条记录~3.2乐观锁乐观锁不是数据库级别的锁。它是一个需要手动添加的锁。一般我们加一个版本字段来实现：具体过程如下：张三select*fromtable--->会查询记录，会有一个版本字段李四select*fromtable--->会query记录记录的时候，会同时有一个version字段。李四会修改这条记录：updateAsetName=lisi,version=version+1whereID=#{id}andversion=#{version}，用来判断之前查询的版本与当前数据的版本对比,version字段会同时更新。此时数据库记录如下：张三也修改了这条记录：updateAsetName=lisi,version=version+1whereID=#{id}andversion=#{version}，但是失败了！因为当前数据库中的版本与查询的版本不一致！4.间隙锁GAP当我们以范围条件而不是相等条件检索数据，并请求共享锁或排它锁时，InnoDB会锁定现有数据记录中满足范围条件的索引项；对于键值在条件范围内但不存在的记录，称为“间隙（GAP）”。InnoDB也会锁住这个“缺口”。这种锁定机制就是所谓的间隙锁。值得注意的是，间隙锁只会在Repeatableread隔离级别下使用~例子：如果emp表只有101条记录，则empid值为1,2,...,100,101Select*fromemp其中empid>100用于更新；以上是范围查询，InnoDB不仅会锁定empid值为101的符合条件的记录，还会锁定empid大于101的“gap”（这些记录不存在）。InnoDB使用间隙锁有两个目的：防止幻读（如前所述，在Repeatableread隔离级别下，GAP锁可以避免幻读）满足恢复和复制的需要MySQL恢复机制要求：在一个事务被执行之前已提交，其他并发事务不能插入任何满足其锁定条件的记录，即不允许幻读。但是总的来说，MySQL已经通过回滚的方式帮助我们解决了很多死锁问题，但是并不能完全避免死锁。以下经验参考可用于最大限度地减少死锁：1)以固定顺序访问表和行。比如批量更新两个job的情况，简单的做法是先对id列表进行排序，然后再执行，这样就避免了交叉等待锁的情况；调整两个事务的SQL顺序一致也可以避免死锁。2)大事化小。大事务更容易出现死锁。如果业务允许，大交易应该拆分成小交易。3）在同一个事务中，尽量一次性锁定所有需要的资源，减少死锁的概率。4）降低隔离级别。如果业务允许，降低隔离级别也是更好的选择。比如将隔离级别从RR调整为RC，可以避免很多由间隙锁引起的死锁。5)为表添加合理的索引。可以看出，如果不使用索引，就会对表的每一行加锁，死锁的概率会大大增加。6、锁总结上面说了很多关于MySQL数据库锁的事情，现在简单总结一下。其实我们程序员很少关心表锁：在MyISAM存储引擎中，它是在执行SQL语句时自动加上的。在InnoDB存储引擎中，如果没有使用索引，会自动加表锁。现在我们大多数使用MySQL都是使用InnoDB，而InnoDB支持行锁：共享锁--读锁--S锁排他锁--写锁--X锁默认情况下select不加任何行锁~transactions可以通过以下语句向记录集添加共享锁或排他锁来显示。共享锁：SELECT*FROMtable_nameWHERE...LOCKINSHAREMODE。独占锁(X)：SELECT*FROMtable_nameWHERE...FORUPDATE。InnoDB也实现了基于行锁的MVCC多版本并发控制。MVCC在隔离级别下工作在Readcommitted和Repeatableread下。MVCC可以实现非阻塞读写！InnoDB实现的Repeatableread隔离级别配合GAP间隙锁避免幻读！乐观锁其实是一种思想，就像它的名字一样：认为数据不会被锁就更新，发现不对就不更新（回滚）。通常会在数据库中添加一个版本字段来实现这一点。悲观锁使用数据库的行锁。认为数据库会出现并发冲突，直接锁住数据，在当前事务提交之前不能修改其他事务。知识点：索引和锁。它们可以说是息息相关的，锁会涉及到很多关于索引的知识~
