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

一.索引相关

索引是一种能帮助 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_count和low-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字段

参考资料：

• zhuanlan.zhihu.com/p/31537871---什么是悲观锁和乐观锁
• www.zhihu.com/question/27…---乐观锁和 MVCC 的区别？

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（可重复读）两种隔离级别下工作。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能