数据库索引和锁

数据库索引

什么是索引？

所谓的索引就是用于标识数据或者或者对象的符号，比如身份证可以唯一地标识一个人，学号可以唯一地标识一个学生，经度和纬度合在一起可以确定一个地点等。

索引是本来就存在与数据中的，每条数据都至少有一个索引信息，只是用不用这个索引信息是由我们来决定的，如果我们想要使用这些索引信息，那么就需要去组织管理这些信息(索引的存储结构)

为什么要使用索引

在进行数据查询时一定会根据其中的一个或者某几个字段的值来选择，首先假如不使用索引信息，也就是没有对数据的索引信息进行组织和管理，那么当我们想要从数据库中找到学号为xxxxxx的学生，需要与数据库表中的各条数据逐行对比，才能找到匹配的数据。这样的查询效率比较低，时间复杂度为O(n)。

因此，如果考虑对数据中的索引采取一定的组织方式的话，比如将数据按照学号的大小顺序排列，这样在查找学号为xxxxxx的学生时，就可以使用二分查找，查找的时间复杂度就变为O(logn)，不过采用二分查找数据需要放在连续空间不利于数据的插入和删除操作。所以在数据库中的索引通常按照B+树的结构存放，查询任意一条数据的时间复杂度都为O(logn)。

使用索引就是为了在查询数据时避免全表扫描，提高查询效率

什么样的信息能称为索引

如前面所说的，只要是能够标识数据的信息都可以成为索引，如主键、唯一键。能够作为索引的字段不唯一，每个索引都有自己的数据结构。

聚集索引、非聚集索引、稠密索引、稀疏索引

关于这几个名词的概念在很多地方看都觉得讲的不是很清楚，于是就自己去看了一下《数据库系统概念》，以下是自己的理解。

要明确一张数据表可以有多个索引，索引是根据搜索码的取值创建的（搜索码是指能够用于查找记录的字段或者字段集合）。另外，数据记录的存储最终是要存储到物理磁盘上的，那么就需要决定这些数据按照什么原则去存储，例如完全可以按照记录插入的时间顺序来存，后插入的记录直接放到前一个记录的后面即可。这样存储虽然方便，但是对后面的查询没有任何帮助。因此我们便考虑能不能让记录按照某个搜索码的大小顺序来存储，这样的话只需要利用搜索码建立一张索引表，然后根据索引表就可以使用二分查找之类的方法快速定位数据记录的位置。该索引表中的记录顺序与实际物理存储的记录顺序是完全一致的。下面是一些具体定义。

聚集索引，它又被称为主索引，称它为主索引倒不是因为它是建立在主键上的索引，实际上它可以建立在任何搜索码上。而是因为数据记录的物理存放顺序是按照该索引顺序存放的。那么就需要注意一旦数据记录选择该搜索码作为聚集索引，那么数据记录的物理存放规则也就确定了，这样的话数据的物理存储顺序也就不可能与其它的搜索码的顺序保持一致，这也就是为什么一张表的聚集索引只能有一个的原因。

非聚集索引，也被称为辅助索引，它与聚集索引相反，数据记录的存放顺序与当前的索引顺序是不一致的。由前面所述，数据记录的存储顺序只能由一个搜索码决定，那么以该搜索码建立的索引就是聚集索引，以其它搜索码建立的索引就是非聚集索引。

稠密索引，这个定义是从索引表的角度来说的，就是每一个记录都需要创建的一个索引项，索引项包括搜索码的值以及指向具有该值的第一条数据记录的指针。稠密索引又可以分为以下两种情况：
(1) 对于聚集索引来说，如果存在具有相同搜索码值的记录，会顺序存储在第一条数据记录之后。那么在稠密索引中只需要记录相同搜索码值的第一条记录的指针即可，其余的可以依据物理位置找到。
(2) 对于非聚集索引，由于其索引的顺序与记录的物理存储顺序不一致，无法根据存储位置找到具有相同搜索码值的其它记录，因此即使多个记录的搜索码相同也要分别为它们建立索引项。

稀疏索引，当然也是从索引表的角度来看，在稀疏索引中不会为每一个搜索码值都建立索引项，而是采用一种区间的方式，每个索引项对应的指针指向的是大于等于当前搜索码值的记录的位置，然后根据该位置再进行下一步搜索。这也就要求数据记录的物理存储必须按照当前索引的顺序存储，那么，也就是说只有聚集索引才能使用稀疏索引。

总结

• 是不是聚集索引要根据数据记录的存储顺序是否与该索引顺序一致
• 数据记录存储是固定的，那么聚集索引也就是唯一的，非聚集索引可以有多个
• 稠密索引和稀疏索引是从索引表的创建的角度来说的
• 稀疏索引只能记录聚集索引
• 非聚集索引必须使用稠密索引

索引的数据结构

B+-Tree

B+树是数据库中的主流数据结构，查询数据的时间复杂度比较稳定

Hash索引

基于MyISAM和InnoDB引擎的MySQL不显示支持Hash索引

缺点：

• 只能满足“=”，“IN”， 无法使用范围查询
• 无法被用来比卖你数据的排序操作
• 不能利用部分索引键查询
• 不能避免表扫描（获取对应的哈希值后需要再扫描数据链中的数据）
• 遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

BitMap索引

MySQL不支持，Oracle支持

如何定位并优化慢查询SQL

• 根据慢日志定位慢查询SQL
  • 使用show variables like '%quer%'; 查看慢日志功能是否打开
  • show status like '%slow_queries%'; 可以获得慢查询的数量
  • set global slow_query_log = on; 打开记录慢查询功能
  • set global long_query_time; 设置慢查询的时间阈值
• 使用explain等工具分析SQL
• 修改SQL或者尽量让SQL走索引

联合索引的最左匹配原则

如前所述，不仅单个字段可以成为索引，多个字段的组合也可以成为索引。但是在使用多个字段组成的索引时，需要满足最左匹配原则。

1、最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 3 and b = 4 and c > 6 and d = 6，如果建立的索引为(a, b, c, d)，那本次查询是不使用索引的，如果建立的索引是(a, b, d, c)则会使用该索引，a，b，d的顺序可以任意调整。

2、=和in可以乱序，比如a = 3 and b = 4 and d = 6 ，索引(a, b, d)是可以使用的。

索引是建立的越多越好吗？

• 数据量小的表不需要建立索引，建立会增加额外的索引开销
• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间

数据库锁模块

MyISAM与InnoDB关于锁方面的区别是什么？

• MyISAM默认用的是表级锁，不支持行级锁(在对数据库进行操作时只会锁住被操作的行)
• InnoDB默认用的是行级锁，也支持表级锁(在对数据库进行操作时会锁住整张表)
• MyISAM不支持事务
• InnoDB支持事务

数据库操作不走索引时使用的是表级锁

MyISAM

在该引擎下会默认添加表锁，当对数据进行select操作时会为表添加一个表级别的读锁；当进行update、insert和delete操作时会自动加上表级别的写锁。

适用场景

• 频繁执行全表count语句，MyISAM中有一个变量保存了整个表的行数
• 对数据进行增删改的频率不高(因为增删改会涉及到锁表操作)，查询非常频繁
• 没有事务的场景

InnoDB

在该引擎下会默认使用的是行锁，行级锁与表级锁的共享性和排它性是一样的，只不过作用的范围不同，行级锁只作用于被上锁的行。

适用场景

• 数据增删改查都相当频繁
• 可靠性要求比较高，要求支持事务

数据库锁的分类

读锁

读锁又被称为共享锁，因为一个会话对数据库进行读操作不会阻塞其它会话的读操作，但是会阻塞其它会话的写操作。

在进行select操作的后面加上for update将会添加排它锁，加lock in share mode会添加共享锁

写锁

写锁又被称为排它锁，当一个会话给数据库加了写锁后会阻塞其它会话中所有的读和写操作。

显示添加读/写锁

lock tables TABLE_NAME read/write；

unlock tables 删除锁

页级锁

介于表级和行级锁之间，锁定位于被操作数据相邻的数据

意向锁

当事务A锁住了表中的某一行时，事务B如果想要申请整张表的写锁，这是因为事务A对数据行加了行锁，如果让B申请成功那么就会与A发生冲突。因此在给事务B分配表锁时需要判断当前数据库表有没有被其它事务加过行或者表锁，那么就需要进行如下判断：

1：判断表是否已被其他事务用表锁锁表

2：判断表中的是否有任意一行已被行锁锁住。

这样的话在第2步中需要对整张表中记录进行判断，效率比较低。于是就有了意向锁，在意向锁存在的情况下，任何事务对表或者行进行上锁前都必须先获取意向锁，有了意向锁，上面的检查过程就变为：

1：不变

2：获取表的意向锁

申请意向锁的动作是数据库完成的，事务进行上锁时数据库会自动先申请表的意向锁，不需要我们程序员使用代码来申请

悲观锁

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定，再修改的方式被称之为悲观并发控制。

乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

事务的ACID

原子性(Atomicity)：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

一致性(Consistency)：事务前后数据的完整性必须保持一致，可类比能量守恒定律

隔离性(Isolation)：指多个用户并发访问数据库时，数据库为每个用户开启事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

持久性(Durability)：指一个事务一旦被提交，他对数据库中数据的改变是永久性的，接下来即使数据库发生故障也不应该对其有影响

事务隔离级别以及各级别下的并发访问问题

查看会话的事务隔离级别select @@tx_isolation

设置会话的隔离级别set session transaction isolation level read uncommitted

事务隔离级别越高，安全性越高，串行化执行越严重，降低数据库的并发度。Oracle默认为READ-COMMITTED， MySQL默认为REPEATABLE-READ

事务并发访问引起的问题以及如何避免

1. 更新丢失—MySQL所有事务隔离级别再数据库层面上均可避免

一个事务的更新覆盖了另一个事务的更新，目前主流数据库都会主动加锁来避免更新丢失的情况。

2. 脏读—READ-COMMITTED事务隔离级别以上可避免

一个事务读取另一个事务未提交的更新数据

3. 不可重复读—REPEATABLE-READ事务隔离级别以上可避免

多次读取操作返回的结果不一致现象

4. 幻读—SERIALIZALE事务隔离级别可避免

事务A读取与搜索条件相匹配的若干行，事务B通过删除或插入改变了事务A的结果集

InnoDB可重复读隔离级别下如何避免幻读

表象：快照读（非阻塞读）—伪MVCC

当前读

加了锁的增删改查语句：select ... lock in share mode，select ... for update，update，delete，insert

快照读（隔离级别在SERIALIZALE一下才能成立）

不加锁的非阻塞读，select，提升并发执行的能力，开销低，读取数据可能不是最新版本

内在：next-key锁（行锁+gap锁）

对主键索引或者唯一索引会用Gap锁吗

• 如果where条件全部命中，则不会用Gap锁，只会加记录锁
• 如果where条件部分命中或者全不命中，则会加Gap锁