本文摘自
声明:如果没有说明具体的数据库和存储引擎,默认指的是mysql中的innodb存储引擎
在之前,我对索引有以下的认知:
- 索引可以加快数据库的检索速度
- 表经常进行insert/update/delete操作就不要建立索引了,换言之:索引会降低插入、删除、修改等维护任务的速度。
- 索引需要占物理和数据空间。
- 了解过索引的最左匹配原则
- 知道索引的分类:聚集索引和非聚集索引
- mysql支持hash索引和b+树索引两种
看起来好像啥都知道,but,但面试让你说的时候可能就gg了:
- 使用索引为什么可以加快数据库的检索速度啊?
- 为什么说索引会降低插入、删除、修改等维护任务的速度?
- 索引的最左匹配原则指的是什么?
- hash索引和b+树索引有什么区别?主流的使用哪一个比较多?innodb存储都支持吗?
- 聚集索引和非聚集索引有什么区别?
- ........
首先mysql的基本存储结构是页(记录都存在页里边)
可以得出以下结论:
1. 各个数据页可以组成一个双向链表
2. 而每个数据页中的记录又可以组成一个单向链表
3. 每个数据页都会为记录生成一个页目录,通过主键查找某条记录时可以在页目录中使用二分法快速定位到对应的槽,然后遍历该槽对应分组中的记录找到指定的记录
4. 以其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录。
所以说,如果我们写select * from user where username = 'java3y' 这样没有进行任何优化的sql语句,默认会这样做:
很明显,在数据量很大的情况下这样查找会很慢!
通过上面可知,加快查找速度势在必行,那么,索引做了些什么可以让我们查询加快速度呢?其实就是将无序的数据变成有序(相对)
例如:要找到id为8的记录简要步骤如下
很明显的是:
【参考资料】:mysql索引
平衡树:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树。
b+树是平衡树的一种,是不会退化成链表的,树的高度都是相对比较低的(基本符合均衡的结构)【这样一来我们检索的时间复杂度就是o(logn)】!从上一节的图我们也可以
看见,建立索引实际上就是建立一颗b+树。
【参考资料】:b+树删除和修改
除了b+树之外,还有一种常见的是哈希索引。哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似b+树那样从根节点到叶子节点逐级查找,只
需一次哈希算法即可立刻定位到相应的位置,速度非常快。但是hash索引有好几个局限:
【参考资料】:
主流的还是使用b+树索引比较多,对于哈希索引,innodb是自适应哈希索引的(hash索引的创建由innodb存储引擎引擎自动优化创建,用户干预不了)!
聚集索引有2个特点:
1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义
2. b+树的叶子节点存储的是完整的用户记录,就是指这个记录中存储了所有列的值。
我们把具有这两种特性的b+
树称为聚集索引,所有完整的用户记录都存放在这个聚集索引的叶子节点处。这种聚集索引并不需要我们在mysql语句中显式的去创建,
innodb存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是,在innodb存储引擎中,聚集索引就是数据的存储方式(所有的用户记录都存储在了叶子节点),也
就是所谓的索引即数据。
非聚集索引也叫做二级索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用,因为b+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?难道
只能从头到尾沿着链表依次遍历记录么?我们可以多建几棵b+树,不同的b+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则,
再建一棵b+树
过程如下:首先根据c2列的值,定位到非聚集索引中的某一个叶子节点,然后根据该叶子节点中的主键去聚簇索引中再查找一遍完整的用户记录,称之为回表
在执行sql时,mysql只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引,根据b+树的性质,很容易理解各种常见的mysql索引优化思路
1. 优先使用自增key作为主键
假设用4b的自增key作为索引,则m可达到512,层高仅有3。参考这里,使用自增的key有两个好处:
1. 自增key一般为int等整数型,key比较紧凑,这样m可以非常大,而且索引占用空间小。最极端的例子,如果使用50b的varchar(包括长度),那么m = 4 * 1024 /
54m = 75.85 ~= 76,深度最大log(76/2)(10^7) = 4.43 ~= 5 ,再加上cache缺失、字符串比较的成本,时间成本增加较大。同时,key由4b增长到50b,整棵索引树
的空间占用增长也是极为恐怖的(如果二级索引使用主键定位数据行,则空间增长更加严重)
2. 自增的性质使得新数据行的插入请求必然落到索引树的最右侧,发生节点分裂的频率较低,理想情况下,索引树可以达到“满”的状态。索引树满,一方面层高更低,一
方面删除节点时发生节点合并的频率也较低
优化案例:
曾使用varchar(100)的列做过主键,存储containerid,过了3、4天100g的数据库就满了,之后增加了自增列作为主键,containerid作为unique的二级索引,时间、空间优化效果相当显著。
2. 最左前缀匹配
3. 索引列不能参与计算
有索引列参与计算的查询条件对索引不友好(甚至无法使用索引)。原因很简单,如何在节点中查找到对应key?如果线性扫描,则每次都需要重新计算,成本太高;如果二分
查找,则需要确定大小关系
4. 能扩展就不要新建索引
如果已有索引(a),想建立索引(a, b),尽量选择修改索引(a)为索引(a, b)。mysql可以直接在索引a的b+树上,经过分裂、合并等修改为索引(a, b)
5. 不需要建立前缀有包含关系的索引
如果已有索引(a, b),则不需要再建立索引(a),但是如果有必要,则仍然需考虑建立索引(b)。
6. 选择区分度高的列作索引
这很容易理解。如,用性别作索引,那么索引仅能将1000w行数据划分为两部分(如500w男,500w女),索引几乎无效。区分度的公式是count(distinct <col>) /
count(*),表示字段不重复的比例,比例越大区分度越好。唯一键的区分度是1,而一些状态、性别字段可能在大数据面前的区分度趋近于0
锁的相关知识又跟存储引擎,索引,事务的隔离级别都是关联的
不少人在开发的时候,应该很少会注意到这些锁的问题,也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下),即使我们不会这些锁知识,我们的程序在一
般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了,只会在某些特定的场景下才需要手动加锁。
首先要明确的是,用户很少手动加表锁
首先,从锁的粒度,我们可以分成两大类:
不同的存储引擎支持的锁粒度是不一样的:innodb行锁和表锁都支持、myisam只支持表锁!innodb只有通过索引条件检索数据才使用行级锁,否则,innodb使用表
锁也就是说,innodb的行锁是基于索引的!
表锁下又分为两种模式:
从上面已经看到了:读锁和写锁是互斥的,读写操作是串行。
innodb和myisam有两个本质的区别:innodb支持行锁、innodb支持事务
innodb实现了以下两种类型的行锁:
另外,为了允许行锁和表锁共存,实现多粒度锁机制,innodb还有两种内部使用的意向锁(intention locks),这两种意向锁都是表锁:
mvcc(multi-version concurrency control)多版本并发控制,可以简单地认为:mvcc就是行级锁的一个变种(升级版)。在表锁中我们读写是阻塞的,基于提升并发性能
的考虑,mvcc一般读写是不阻塞的(很多情况下避免了加锁的操作)。可以简单的理解为:对数据库的任何修改的提交都不会直接覆盖之前的数据,而是产生一个新的版本与
老版本共存,使得读取时可以完全不加锁。
事务的隔离级别就是通过锁的机制来实现,锁的应用最终导致不同事务的隔离级别,只不过隐藏了加锁细节,事务的隔离级别有4种:
read uncommitted:出现的现象--->脏读:一个事务读取到另外一个事务未提交的数据,例子:a向b转账,a执行了转账语句,但a还没有提交事务,b读取数据,发现
自己账户钱变多了!b跟a说,我已经收到钱了。a回滚事务【rollback】,等b再查看账户的钱时,发现钱并没有多...
出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁,导致读的数据就变成了无用的或者是错误的数据
read committed:出现的现象--->不可重复读:一个事务读取到另外一个事务已经提交的数据,也就是说一个事务可以看到其他事务所做的修改,例如:a查询数据库得到
数据,b去修改数据库的数据,导致a多次查询数据库的结果都不一样【危害:a每次查询的结果都是受b的影响的,那么a查询出来的信息就没有意思了】
repeatable read:避免不可重复读是事务级别的快照!每次读取的都是当前事务的版本,即使被修改了,也只会读取当前事务版本的数据
如果还是不太清楚,我们来看看innodb的mvcc是怎么样的吧《高性能mysql》
至于虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。和不可重复读类似,但虚读(幻读)会读到其他事务的插入的数据,导致前后读取不
一致,幻读的重点在于新增或者删除 (数据条数变化),不可重复读的重点是修改,?
无论是read committed还是repeatable read隔离级别,都是为了解决读写冲突的问题,现在考虑一个问题:有一张数据库表user,只有id、name字段,现在有2个请求
同时候操作表a,过程如下:(模拟更新丢失,虽然不是很恰当)
1. 操作1查询出name="zhangsan"
2. 操作2也查询出name="zhangsan"
3. 操作1把name字段数据修改成lisi并提交
4. 操作2把name字段数据修改为wangwu并提交
那么操作1的更新丢失啦,即一个事务的更新覆盖了其它事务的更新结果,解决上述更新丢失的方式有如下3种:
我们使用悲观锁的话其实很简单(手动加行锁就行了):select * from xxxx for update,在select 语句后边加了for update 相当于加了排它锁(写锁),加了写锁以后,其
他事务就不能对它修改了!需要等待当前事务修改完之后才可以修改.也就是说,如果操作1使用select ... for update,操作2就无法对该条记录修改了,即可避免更新丢失。
乐观锁不是数据库层面上的锁,需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现,例如操作1和操作2在更新user表的时,执行语句如下:
update a set name=lisi,version=version+1 where id=#{id} and version=#{version},此时即可避免更新丢失。
【参考资料】:
间隙锁只会在repeatable read隔离级别下使用
当我们用范围条件检索数据而不是相等条件检索数据,并请求共享或排他锁时,innodb会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在
的记录,叫做“间隙(gap)”。innodb也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。例子:假如emp表中只有101条记录,其empid的值分别是1,2,...,100,101
select * from emp where empid > 100 for update;
上面是一个范围查询,innodb不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁
innodb使用间隙锁的目的有2个:
并发的问题就少不了死锁,在mysql中同样会存在死锁的问题
【参考资料】:mysql死锁问题分析
【参考资料】:
表锁其实我们程序员是很少关心它的:
现在我们大多数使用mysql都是使用innodb,innodb支持行锁:
在默认的情况下,select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论