数据库知识

一、乐观锁

程序自己实现，一般的实现乐观锁的方式就是记录数据版本。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

二、悲观锁

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）,形如 select…for update。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

1. 排他锁【for update】

排它锁（Exclusive），简写为 X 锁，又称写锁。

2. 共享锁【lock in share mode】

共享锁（Shared），简写为 S 锁，又称读锁。

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

三、事务的隔离级别

1. 并发引起的问题：

丢失修改： 就是 两个 事务都 修改同一数据 就必有一个被覆盖。

读脏数据： 就是 一个事务中修改的数据 被宁一个事务读到了。属于 读未提交。

不可重复读： 第一次读 与 第二次读不一样，由于其他事务的修改。

幻读：事务T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时 读取的结果和和第一次读取的结果不同。

2. 隔离级别：

1. 未提交读（READ UNCOMMITTED）

    事务中的修改，即使没有提交，对其它事务也是可见的。
   

2. 提交读（READ COMMITTED）

    一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
   

3. 可重复读（REPEATABLE READ）

    保证在同一个事务中多次读取同样数据的结果是一样的。
   

4. 可串行化（SERIALIXABLE）

    强制事务串行执行。
   

3. 一级封锁协议 [only X锁]

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么一个事务的修改就不会被覆盖。

4. 二级封锁协议 [X锁 + S锁]

在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

5. 三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

6. 隔离级别和解决问题

    隔离级别    脏读  不可重复读   幻影读
    未提交读    YES     YES     YES
    提交读     NO      YES     YES
    可重复读    NO       NO     YES
    可串行化    NO       NO      NO

四、索引

索引选择性

不重复的索引值和记录总数的比值（数值分散程度）。
选择性越高，查询效率也越高。最大值为 1，此时每个记录都有唯一的索引与其对应。

索引类型

B+tree索引---- 聚集索引（主键）| 辅助索引非联合（主键+单索引）| 辅助索引联合（组合索引）
哈希索引
空间数据索引（R-Tree）
全文索引【myisam】

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

索引选择

1. 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。减少索引的空间占用。
   形如

    idx_pid2(`parent_id`,`word`(20)); 在后面写个数字就好了。表示只用前20个字符创建索引
   

2. 组合索引的时候 让选择性最强的索引列放在前面。

3. 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。[简单说 ，就是where条件 带表达式不可以]。

   但是 select count(索引列) 是可以走索引的。

        SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; 不能走
        SELECT count(actor_id) FROM sakila.actor ; 可以走
   

4. 以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

<img src="https://pic3.zhimg.com/50/8c45fe417afbe97127e8c55fe1cd9395_hd.jpg" data-rawwidth="149" data-rawheight="205" class="content_image" width="149">

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

所以出现最左前缀匹配原则：

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

查询优化

1. Explain
2. 减少返回的列 最好不要使用 SELECT * 语句，要根据需要选择查询的列。
3. 减少返回的行 最好使用 LIMIT 语句来取出想要的那些行。
4. 拆分大的 DELETE 或 INSERT 语句
   如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重 要的查询。

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

五、切分

垂直切分

将表按功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立商品数据库 payDB、用户数据库 userDB 等，分别用来存储项目与商品有关的表和与用户有关的表。