数据库两大神器----------索引和锁

一.索引相关

索引是一种能帮助 MySQL 提高查询效率的数据结构。

在之前,我对索引有以下的认知:

1.索引可以提高查询效率

2.索引支持最左匹配特性

3.索引需要占用物理空间

4.索引的分类有:聚集索引和非聚集索引

但是面试官如果具体问的时候就说不出啥了:

1.为什么可以提高查询效率?

2.为什么索引会降低插入、删除、修改等维护任务的速度?

3.最左匹配特性指什么?

4.聚集索引和非聚集索引有什么区别?

...........

先从 MySQL 的基本存储结构说起

MySQL的基本存储结构是页(记录都存在页里边):

  • 各个数据页可以组成一个双向链表
  • 各个页中的数据组成单向数据

每个页中的记录 都会有一个目录,根据主键使用二分法去进行查找

根据页中其他的列作为查询条件:从最小记录数依次遍历单链表中的每条记录,所以以其他列查询会很慢

那么索引做了些什么可以让我们查询加快速度呢?

就是将无序的数据变成有序.

要找到id为8的记录简要步骤:

很明显的是:没有用索引我们是需要遍历双向链表来定位对应的页,现在通过 “目录” 就可以很快地定位到对应的页上了!(二分查找,时间复杂度近似为O(logn))

其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。

为什么索引能降低插入  删除  修改等维护任务的速度?

  • B+树是一颗平衡树,如果我们对这颗树增删改的话,那肯定会破坏它的原有结构。
  • 要维持平衡树,就必须做额外的工作。正因为这些额外的工作开销,导致索引会降低增删改的速度.

平衡树:它是一棵空树或它的左右两个子树的高度差的绝对值不超过1,并且左右两个子树都是一棵平衡二叉树。

B+树删除和修改的参考:https://www.cnblogs.com/wade-luffy/p/6292784.html

最左匹配原则指什么?

最左匹配原则

  • 索引可以简单如一个列(a),也可以复杂如多个列(a, b, c, d),即联合索引
  • 最左匹配原则也叫最左前缀原则,是 MySQL 中的一个重要原则,说的是索引以最左边的为起点任何连续的索引都能匹配上,当遇到范围查询(>、<、between、like)就会停止匹配。

只能判断相等,不能是范围匹配,所以列的排列顺序决定了可命中索引的列数.

聚集索引和非聚集索引有什么区别?

  • 聚集索引:一般是表中的主键索引,如果表中没有显示指定主键,则会选择表中的第一个不允许为 NULL 的唯一索引,如果还是没有的话,就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引,因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现(相比于普通索引和全表扫描),聚集索引就显得弥足珍贵,聚集索引选择还是要慎重的(一般不会让没有语义的自增 id 充当聚集索引);
  • 非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同(非主键的那一列),一个表中可以拥有多个非聚集索引。

区别:

  • 聚集索引在叶子节点存储的是表中的数据
  • 非聚集索引在叶子节点存储的是主键和索引列
  • 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

如何查询一张表有多少个索引?

SHOW INDEX FROM T 查询表 T 所有索引。

列值为NULL时,会使用到索引吗?

在 MySQL 5.6 以上的 InnoDB 存储引擎会正常触发索引。但为了兼容低版本的 MySQL 和兼容其他数据库存储引擎,不建议使用 NULL 值来存储和查询数据,建议设置列为 NOT NULL,并设置一个默认值,比如 0 和空字符串等,如果是 datetime 类型,可以设置成 1970-01-01 00:00:00 这样的特殊值。

MySQL会选错索引吗?

MySQL 会错选索引,比如 k 索引的速度更快,但是 MySQL 并没有使用而是采用了 v 索引,这种就叫错选索引,因为索引选择是 MySQL 的服务层的优化器来自动选择的,但它在复杂情况下也和人写程序一样出现缺陷。

怎么解决MySQL选错索引?

  • 删除错选的索引,只留下对的索引;
  • 使用 force index 指定索引;
  • 修改 SQL 查询语句引导 MySQL 使用我们期望的索引,比如把 order by b limit 1 改为 order by b,a limit 1 语义是相同的,但 MySQL 查询的时候会考虑使用 a 键上的索引。

使用索引注意事项

  • 使用短索引,短索引不仅可以提高查询速度,更能节省磁盘空间和 I/O 操作;
  • 索引列排序,MySQL 查询只使用一个索引,因此如果 where 子句中已经使用了索引的话,那么 order by 中的列是不会使用索引的,因此数据库默认排序可以符合要求的情况下,不要进行排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引;
  • like 语句操作,一般情况下不鼓励使用 like 操作,如果非使用不可, 注意 like "%aaa%" 不会使用索引,而like "aaa%"可以使用索引;
  • 不要在列上进行运算;
  • 不适用 NOT IN 和 <> 操作。

二.锁相关

锁的相关知识又跟存储引擎,索引,事务的隔离级别都是关联的....

这就给初学数据库锁的人带来不少的麻烦~~~于是我下面就简单整理一下数据库锁的知识点,希望大家看完会有所帮助。

1.什么是锁?MySQL中提供了几类锁?

锁是实现数据库并发控制的重要手段,可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁,MyISAM 只支持表级锁。

2.表锁下又分为两种模式

  • 表读锁(Table Read Lock)
  • 表写锁(Table Write Lock)
  • 在表读锁和表写锁的环境下:读读不阻塞,读写阻塞,写写阻塞
    • 读读不阻塞:当前用户在读数据,其他的用户也在读数据,不会加锁
    • 读写阻塞:当前用户在读数据,其他的用户不能修改当前用户读的数据,会加锁!
    • 写写阻塞:当前用户在修改数据,其他的用户不能修改当前用户正在修改的数据,会加锁!

          读锁和写锁是互斥的,读写操作是串行

  • 如果某个进程想要获取读锁,同时另外一个进程想要获取写锁。在mysql里边,写锁是优先于读锁的
  • 写锁和读锁优先级的问题是可以通过参数调节的:max_write_lock_countlow-priority-updates
  • MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式,在MyISAM中它默认是:如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。
  • 但是InnoDB存储引擎是不支持的

3.乐观锁和悲观锁

无论是Read committed还是Repeatable read隔离级别,都是为了解决读写冲突的问题。

单纯在Repeatable read隔离级别下我们来考虑一个问题:

此时,用户李四的操作就丢失掉了:

  • 丢失更新:一个事务的更新覆盖了其它事务的更新结果

解决的方法:

  • 使用Serializable隔离级别,事务是串行执行的!
  • 乐观锁
  • 悲观锁

所以,按照上面的例子。我们使用悲观锁的话其实很简单(手动加行锁就行了):

  • select * from xxxx for update

在select 语句后边加了 for update相当于加了排它锁(写锁),加了写锁以后,其他的事务就不能对它修改了!需要等待当前事务修改完之后才可以修改.

  • 也就是说,如果张三使用select ... for update,李四就无法对该条记录修改了~

乐观锁不是数据库层面上的锁,是需要自己手动去加的锁。一般我们添加一个版本字段来实现:

李四对这条记录做修改:update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version},判断之前查询到的version与现在的数据的version进行比较,同时会更新version字段

参考资料:

4.死锁问题

并发的问题就少不了死锁,在MySQL中同样会存在死锁的问题。

但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了,但死锁是无法完全避免的,可以通过以下的经验参考,来尽可能少遇到死锁:

  • 1)以固定的顺序访问表和行。比如对两个job批量更新的情形,简单方法是对id列表先排序,后执行,这样就避免了交叉等待锁的情形;将两个事务的sql顺序调整为一致,也能避免死锁。
  • 2)大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。
  • 3)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
  • 4)降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
  • 5)为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。

参考资料:www.cnblogs.com/LBSer/p/518…

优化锁方面你有什么建议?

  • 尽量使用较低的隔离级别。
  • 精心设计索引, 并尽量使用索引访问数据, 使加锁更精确, 从而减少锁冲突的机会。
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小。
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别。
  • 除非必须,查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁,优化事务性能;MVCC 只在 COMMITTED READ(读提交)和 REPEATABLE READ(可重复读)两种隔离级别下工作。
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值