注：本篇文章为对数据库知识的查缺补漏，从索引，事务管理，存储过程，触发器，一些sql优化角度汇总

索引

数据库的索引包含两种数据结构，B+树索引以及Hash索引。

B+树

B+树是n叉排序树，每个节点有多个孩子，一棵B+树包含根节点，内部节点和叶子节点。只有最底层的叶子节点才保存信息，内部节点只是在搜索中用来指引到正确节点的，而且到任何一个叶子节点的最短路径长度都是相同的O(logn)。

使用B+树查找特定值的时间复杂度为O(logn)，当需要查找两个值之间的多个元素时，通过对叶子节点之间的相连，也使得范围查询更加高效。

如上图，假设查找10到100间的值：

只需要按B+树的最短路径找到10（若10不存在则找10之后最贴近的值）
然后用那些连接来收集10的后续节点，直到找到100

归纳一下，假设需要找M个后续节点，树总共有N个节点，那么这次搜索只需要消耗M+logN。

但对于在数据库中增加或删除一行，B+树需要自我整理和平衡，需要O(logN)时间复杂度，所以使用太多的B+索引，减慢了快速插入，更新，删除表中一行的操作速度

Hash索引

Hash索引对特定值的检索效率很高，索引检索一次一般即可定位，不像B+索引需要从根节点到叶子节点，需要O(logN)次的IO访问。

但以Mysql为例，查询资料如下表：

描述	innoDB	MyISAM	Memory	Archive
事务	支持事务，行级别锁定	不支持，表级别锁定	不支持，表级别锁定	不支持，表级别锁定
索引	B+Tree	B+Tree	B+Tree以及Hash	不支持

对于常用的innoDB以及MyISAM引擎，均不支持Hash索引，因其自身存在的诸多弊端

Hash索引仅仅能满足“=”，“IN”，“<=>”查询，不能使用范围查询
Hash索引无法用来进行数据的排序操作
Hash索引不能利用联合索引键查询，因为计算组合索引，Hash值是将键合并后再一起Hash，不能单独计算Hash值，所以对于联合索引前几个键进行查询时，Hash索引无法被利用
Hash索引计算的Hash值若不够松散，存在大量Hash值相等的情况下，不能避免表扫描，性能也不一定好于B+树

有关索引

聚集索引

聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key（pk 主键）来命名

非聚集索引

以mysql为例，非聚集索引分为普通索引（index）和唯一索引（unique）

非聚集索引并不决定数据在磁盘上的物理排序，索引只包含被建立索引的数据，以及一个行定位符row-locator，这个行定位符可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据

联合索引

多个字段上建立的索引，能够加速复合查询条件的检索，联合索引能够满足最左侧查询需求，例如（x,y,z）三列索引，能够使x 或 x,y 或 x,y,z使用上索引，需要注意的是最左侧查询需求，并不是指SQL语句的写法必须满足索引顺序，SQL优化会自动调整以满足联合索引的顺序的，如zyx会被自动优化为xyz以便使用联合索引加速查询

sql的where条件提取

SQL的where条件，均可归纳为三大类：Index Key（First Key & Last Key），Index Filter，Table Filter，按照从前到后的顺序获取查询的数据

例子：select * from t where a >= 3 and a < 6 and b > 5 and c!=3 and d !=9
联合索引[a,b,c]

Index Key：

用于确定SQL查询在索引中的范围，由于一个范围，至少包含一个起始与一个终止，分别用于定位索引查询的起始以及索引查询的终止条件；

MySQL利用=、>=、> 来确定下边界（first key），利用最左原则，首先判断第一个索引键值在where条件中是否存在，如果存在，则判断比较符号，如果为(=,>=)中的一种，加入下边界的界定，然后继续判断下一个索引键，如果存在且是(>)，则将该键值加入到下边界的界定，停止匹配下一个索引键；如果不存在，直接停止下边界匹配，因此上面的例子，下边界为 a>=3 and b>5

判断上边界方法与下边界相反，上面例子的上边界为 a<6,最终确定Index Key的范围是a>=3 and a < 6

Index Filter：

简单来说，就是无法用Index key确定部分的索引可以当做Index Filter，当然是需要满足最左原则的，即从第一列索引往后看，若第一列索引包括查询条件‘=’，或者有index key可以跳过看下一列索引

本例子中的Index Filter即为b> 5 and c!=3

Table Filter：

经过Index Key 和Index Filter过滤后，所有不属于索引列的查询条件，均归为Table Filter之中，通过前面两部过滤后，使得需要回表查询的数据大量减少，提高了查询的效率，本例子为 d!=9

有关between和like使用索引

Between：

where a between 10 and 100 可以转变为 where a > 10 and a <100,作为index key进行索引查询

like：

like需要注意的是%不能是在最左侧，where d like ‘%a’ 这样的查询是无法利用索引的，因为索引的匹配需要符合最左前缀原则

索引的结果排序

若联合索引为[a,b,c]，a是有序的，若a为固定的值，则b有序，依次类推

事务管理

ACID事务是一个工作单元，保证四个属性原子性（Atomicty），一致性（Consistency），隔离性（Isolation），持久性（Durability）

事务隔离级别

首选说明下脏读，不可重复读，幻读的定义：

脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据

不可重复读：在一个事务内两次读到的数据是不一样的，受到另一个事务修改后提交的影响，因此称为是不可重复读

幻读：第一个事务对表进行读取，当第二个事务对表进行增加或删除操作事务提交后，第一个事务再次读取，会出现增加或减少行数的情况

如下列举数据库事务的四种隔离级别：

1. 未提交读（Read Uncommitted）：允许脏读，可能读到其他会话中未提交事务修改的数据

2. 提交读（Read Committed）：只能读取到已经提交的数据，会产生不可重复读，**Oracle,PostgreSQL,SQL Server默认**

3. 可重复读（Repeated Read）：可重复读，在同一个事务内的查询都是事务开始时刻一致的，但存在幻读，**InnoDB默认**

4. 串行读（Serializable）：完全串行化的读，每次读都要获得表级别锁，读写相互都会阻塞 **SQLite默认**

附：InnoDB有三种行锁算法record lock（单行锁），gap lock（间隙锁，锁定一个范围，但不包含记录本身，目的是为了防止同一事务的两次当前读，出现幻读的情况）next-key lock（1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题）,并且都是针对索引进行加行锁

事务的并发控制

为了进行并发控制，多数数据库使用了悲观锁和乐观锁的方案

悲观锁

假定会发生冲突，屏蔽一切可能违反数据一致性的操作，其包括排他锁和共享锁

排他锁的原理是如果一个事务需要一条数据，就把数据锁住，如果另一个事务也需要这条数据，就必须要等第一个事务释放这条数据，但是对一个仅仅读取数据的事务使用排他锁非常昂贵，因为这会迫使其它只需要读取相同数据的事务等待

共享锁是如果一个事务只需要读取数据A，它会给数据A加上共享锁并读取，如果第二个事务也需要仅仅读取数据A，它也会给数据A加上共享锁并读取，如果第三个事务需要修改数据A，它会给数据A加上排它锁，但是必须等待另外两个事务释放它们的共享锁才能进行操作

最后，如果一块儿数据被加上排他锁，一个只需要读取该数据的事务必须等待排他锁释放才能给该数据加上共享锁

以Mysql的innoDB为例（因为MyISAM不支持事务且为表级别锁定）

insert、update、delete，InnoDB会自动给涉及的数据加排他锁；对于一般的select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁

共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

还有一个问题需要注意，就是因为innoDB为行级别锁定，显示加锁一定要确定好行，避免勿使用造成表级别的加锁，这样会极大的影响数据库的性能，举例子进行说明，goods表中有id（主键），name，status（索引列）三列

1. select * from goods where id=1 for update;明确使用主键，并且有该数据，row-lock

2. select * from goods where id=-1 for update;明确使用主键，但没有查到数据，无lock

3. select * from goods where name='道具' for update;没有主键，table-lock

4. select * from goods where id > 0 for update;  主键是确定范围，row-lock

5. select * from goods where id <> 0 for update; 主键使用不等于符号，table-lock

6. select * from goods where status=1 for update; 明确指定索引，并且有该数据，row-lock

7. select * from goods where status=-1 for update; 明确指定索引，但没有查到数据，无lock

8. select * from goods where status > 1 for update; 索引是确定范围  row-lock

9. select * from goods where status <> 1 for update; 索引使用不等于符号，table-lock

乐观锁

假设不会发生并发冲突，只在提交操作时检查是否违反数据一致性，乐观锁一般使用两种形式数据版本以及时间戳

数据版本就是数据表添加一个作为version的字段，当需要修改之前，获取修改前version的old值，当执行修改的时候，当前的version与查询出来的old version进行比对，若不同，证明有另外的数据对该行进行修改，所以不允许执行修改sql，若比对相同，则进行修改操作，也就是CAS（Compare and Set）机制

时间戳和上面类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的old时间戳进行对比，如果一致则OK，否则就是版本冲突

总结

乐观锁是在表中增加版本字段，修改时where语句中增加版本的判断，算是一种CAS（Compare And Swep）操作。悲观锁会认为本次操作会发生并发冲突，所以一开始就上锁（SELECT … FOR UPDATE，SELECT … LOCK IN SHARE MODE）

存储过程与触发器

存储过程

存储过程是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它

优点

1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译

2. 当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用

3. 复用性高，面向对象的编程思想

4. 安全性高,可设定只有某些用户才具有对指定存储过程的使用权

缺点

1. 调试麻烦

2. 移植问题，数据库端代码与数据库相关的，更换数据库存在不兼容

3. 重新编译问题，若表结构改变等，不够灵活

4. 数据量增大，会导致数据结构的变化，编译完的sql存在优化问题

5. 与数据库层耦合太高，对于数据表结构变化，多表查询，数据库集群的使用和运维，将会造成麻烦

触发器

触发器是种特殊的存储过程，它的执行不是由程序调用，也不需要手动操作，它是由事件来触发，触发器的事件，是对表进行增删改的操作。当对一个数据库或表进行增删改（ Insert，Delete，Update）的时就会激活触发器

总结

大体了解存储过程与触发器，但是在实际开发中很少使用，也查了下知乎一些人的评论，结合自己的理解，对于一些企业级别，商业级别的应用，一般情况下会使用存储过程和触发器，存储过程一旦调试完成通过后就能稳定运行，这与各个业务在一段时间内是相对稳定和确定是匹配的；存储过程大大地减少了业务系统与数据库的交互，并且服务器的负载是可控，也即系统的访问人数首先是可控的；但在互联网中，数据库压力大，数据增速不可控，DB也会存在瓶颈，尽量会把业务逻辑放到应用的语言层面，让数据库尽量少做事，也能更好的进行扩展

MySQL的一些使用技巧

1.优化查询的查询缓存

//不好的写法
select name from user where date > CURDATE()
//好的写法
select name from user where date > #{计算出来的时间}

第一个写法不好的原因是CURDATE()的使用，并且适用于所有非确定性功能NOW()和RAND()，，，，因为功能返回的结果是可变的，MySQL会禁用查询器的查询缓存

2.获取唯一行时使用LIMIT 1

数据库引擎将在找到第一个记录之后停止扫描，而不是遍历整个表或索引

3.索引并对连接使用同样的字段类型

若包含多连接查询，需要确保关联的字段在两张表上均建立索引，这会影响MySQL如何内部优化连接操作，并且被连接的两个字段，需要使用同样的类型

附：参考文章