当前位置: 首页 > 科技观察

数据库必备的两大神器:索引和锁的底层原理是什么?

时间:2023-03-20 11:44:32 科技观察

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间隙锁避免幻读!乐观锁其实是一种思想,就像它的名字一样:认为数据不会被锁就更新,发现不对就不更新(回滚)。通常会在数据库中添加一个版本字段来实现这一点。悲观锁使用数据库的行锁。认为数据库会出现并发冲突,直接锁住数据,在当前事务提交之前不能修改其他事务。知识点:索引和锁。它们可以说是息息相关的,锁会涉及到很多关于索引的知识~