一、索引
在之前,我对索引有以下的认知:
看起来好像啥都知道,但面试让你说的时候可能就GG了:
1、聊聊索引的基础知识
首先Mysql的基本存储结构是页(记录都存在页里边):
所以说,如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句,默认会这样做:
很明显,在数据量很大的情况下这样查找会很慢!
2、索引提高检索速度
索引做了些什么可以让我们查询加快速度呢?
其实就是将无序的数据变成有序(相对):
要找到id为8的记录简要步骤:
很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过"目录"就可以很快地定位到对应的页上了!
其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。
3、索引降低增删改的速度
如果一棵普通的树在极端的情况下,是能退化成链表的(树的优点就不复存在了)
B+树是平衡树的一种,是不会退化成链表的,树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】!从上一节的图我们也可以看见,建立索引实际上就是建立一颗B+树。
4、哈希索引
除了B+树之外,还有一种常见的是哈希索引。
哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。
看起来哈希索引很牛逼啊,但其实哈希索引有好几个局限(根据他本质的原理可得):
5、InnoDB支持哈希索引吗?
主流的还是使用B+树索引比较多,对于哈希索引,InnoDB是自适应哈希索引的(hash索引的创建由InnoDB存储引擎引擎自动优化创建,我们干预不了)!
6、聚集和非聚集索引
简单概括:
区别:
非聚集索引也叫做二级索引,不用纠结那么多名词,将其等价就行了~
非聚集索引在建立的时候也未必是单列的,可以多个列来创建索引。
在创建多列索引中也涉及到了一种特殊的索引-->覆盖索引
比如说:
很明显地知道,我们上边的查询是走索引的,并且,要查询出的列在叶子节点都存在!所以,就不用回表了~
7、索引最左匹配原则
最左匹配原则:
例子:
8、=、in自动优化顺序
不需要考虑=、in等的顺序,mysql会自动优化这些条件的顺序,以匹配尽可能多的索引列。
例子:
9、索引总结
索引在数据库中是一个非常重要的知识点!上面谈的其实就是索引最基本的东西,要创建出好的索引要顾及到很多的方面:
1,最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配。
3,尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少。
4,索引列不能参与计算,尽量保持列“干净”。比如,FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 : create_time = UNIX_TIMESTAMP('2016-06-06')。
5,尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
6,单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引。
二、锁
在mysql中的锁看起来是很复杂的,因为有一大堆的东西和名词:排它锁,共享锁,表锁,页锁,间隙锁,意向排它锁,意向共享锁,行锁,读锁,写锁,乐观锁,悲观锁,死锁。这些名词有的博客又直接写锁的英文的简写--->X锁,S锁,IS锁,IX锁,MMVC...
锁的相关知识又跟存储引擎,索引,事务的隔离级别都是关联的....
这就给初学数据库锁的人带来不少的麻烦~~~于是我下面就简单整理一下数据库锁的知识点,希望大家看完会有所帮助。
1、为什么需要学习数据库锁知识
不少人在开发的时候,应该很少会注意到这些锁的问题,也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)
一般也就听过常说的乐观锁和悲观锁,了解过基本的含义之后就没了~~~
定心丸:即使我们不会这些锁知识,我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了:
MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预;
只会在某些特定的场景下才需要手动加锁,学习数据库锁知识就是为了:
2、表锁简单介绍
首先,从锁的粒度,我们可以分成两大类:
不同的存储引擎支持的锁粒度是不一样的:
InnoDB只有通过索引条件检索数据才使用行级锁,否则,InnoDB将使用表锁
表锁下又分为两种模式:
从上面已经看到了:读锁和写锁是互斥的,读写操作是串行。
值得注意的是:
3、乐观锁和悲观锁
无论是Read committed还是Repeatable read隔离级别,都是为了解决读写冲突的问题。
单纯在Repeatable read隔离级别下我们来考虑一个问题:
此时,用户李四的操作就丢失掉了:
(ps:暂时没有想到比较好的例子来说明更新丢失的问题,虽然上面的例子也是更新丢失,但一定程度上是可接受的..不知道有没有人能想到不可接受的更新丢失例子呢...)
解决的方法:
3.1、悲观锁
所以,按照上面的例子。我们使用悲观锁的话其实很简单(手动加行锁就行了):
在select 语句后边加了 for update相当于加了排它锁(写锁),加了写锁以后,其他的事务就不能对它修改了!需要等待当前事务修改完之后才可以修改.
3.2、乐观锁
乐观锁不是数据库层面上的锁,是需要自己手动去加的锁。一般我们添加一个版本字段来实现:
具体过程是这样的:
张三select * from table --->会查询出记录出来,同时会有一个version字段
李四select * from table --->会查询出记录出来,同时会有一个version字段
李四对这条记录做修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},判断之前查询到的version与现在的数据的version进行比较,同时会更新version字段
此时数据库记录如下:
张三也对这条记录修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},但失败了!因为当前数据库中的版本跟查询出来的版本不一致!
4、间隙锁GAP
当我们用范围条件检索数据而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”。InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
值得注意的是:间隙锁只会在Repeatable read隔离级别下使用~
例子:假如emp表中只有101条记录,其empid的值分别是1,2,...,100,101
Select * from emp where empid > 100 for update;
上面是一个范围查询,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。
InnoDB使用间隙锁的目的有两个:
5、死锁
并发的问题就少不了死锁,在MySQL中同样会存在死锁的问题。
但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了,但死锁是无法完全避免的,可以通过以下的经验参考,来尽可能少遇到死锁:
1)以固定的顺序访问表和行。比如对两个job批量更新的情形,简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形;将两个事务的sql顺序调整为一致,也能避免死锁。
2)大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
3)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
4)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
5)为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。
6、锁总结
上面说了一大堆关于MySQL数据库锁的东西,现在来简单总结一下。
表锁其实我们程序员是很少关心它的:
现在我们大多数使用MySQL都是使用InnoDB,InnoDB支持行锁:
在默认的情况下,select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。
InnoDB基于行锁还实现了MVCC多版本并发控制,MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC能够实现读写不阻塞!
InnoDB实现的Repeatable read隔离级别配合GAP间隙锁已经避免了幻读!
三、总结
本文主要介绍了数据库中的两个比较重要的知识点:索引和锁。他俩可以说息息相关的,锁会涉及到很多关于索引的知识~
曾几何时,项目中有碰到视频比例拉伸的需求,但是发现这个看似再普通不过的一个...
Base64是一种基于64个可打印字符来表示二进制数据的表示方法。由于2的6次方等于6...
本文实例讲述了PHP设计模式(一)工厂模式Factory。分享给大家供大家参考,具体...
ColorCombos 配色是 Web 设计的首要大事,Color Combos 帮你预备了数千种配色方...
本文讲述使用JSP实现用户登录,包括用户登录、注册和退出功能等。 1.系统用例图 ...
前言: 本示例大概功能是前台通过JQuery的Ajax调用一般处理程序(Handler),获取...
在后台添加中一般我们都设置了一个“关键字”,而且一般输入可能如下: 娃娃,毛...
如题。 问题很奇葩。元素上级标签是div,已经设置了width和height等的属性,可是...
电话网中的操作最终目标是找到一根通往被叫端的插口然后把主叫来自的电线插入被...
早在2018年的时候,有报道称微软正在打造一套新的、现代化的Windows10 PC操作系...