数据库总结

存储引擎

存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5. Federated 。
主要介绍InnoDB和MyIsam

InnoDB

InnoDB底层存储结构为B+树，B树的每个节点对应innodb的一个page，page大小是固定的，一般设为 16k。其中非叶子节点只有键值，叶子节点包含完成数据

适用场景

• 经常更新的表，适合处理多重并发的请求
• 支持事务
• 可以从灾难中恢复（通过big-log日志等）
• 外键约束
• 支持自动增加列属性auto_increment

MyIsam

MyIASM是 MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，
因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织
成有固定长度的记录，按顺序存储的。—ISAM 是一种静态索引结构。
缺点是它不 支持事务处理

索引

索引的原则

1. 选择唯一性索引
   唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
2. 为经常需要排序，分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
   越多的索引，会使更新表变得很浪费时间
5. 索引值太长会影响查询的速度
   尽量使用前缀来索引
6. 删除不再使用或者很少使用的索引
7. 最左前缀匹配原则
8. 尽量选取区分度高的列作为索引
9. 索引列不能参与计算
10. 尽量的扩展索引，不要新建索引

索引的分类

1. 普通索引
   最基本的索引，没有任何限制，
   直接创建索引

create index index_name on table (column(length))

修改表结构的方式添加索引

alter table table_name add index index_name on (column(length))

创建表的同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    index index_name（title（length））
}

删除索引

drop index index_name on table 
``

 2. 唯一索引
与普通索引类似，不同的是索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一，
创建唯一索引
```sql
create unique index index_name on table (column(length))

修改表结构

alter table table_name add unique index_name on (column(length))

创建表时直接指定

unique index_name (title(length))

3. 主键索引
   是一种特殊的唯一索引，一个表只能有一个主键，一般在建表的同时创建主键索引
4. 组合索引
   指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一字段，索引才会被使用，使用组合索引遵循最左前缀集合

alter table table_name add index name_city_age(name,city,age);

5. 全文索引
   主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多

注意事项

1. 索引不会包含null值的列
2. 使用短索引
   如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 索引列排序
   查询只使用一个索引，因此如果where子句中使用了索引的话，那么orderby中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序
4. like语句操作
   一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引
5. 不要在列上进行运算
   这将导致索引失效而进行全表扫描
6. 不使用not in和<>操作

数据库事务

事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，事务是不可分割的工作逻辑单元
事务具备四个属性，简称ACID属性

• 原子性
  事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
• 一致性
  当事务完成时，数据必须处于一致状态
• 隔离性
  对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
• 永久性
  事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

数据库事务的使用

1. 开启事务
   

不进行提交事务的话，其实没有改动

2. 提交事务
   

3. 回滚事务
   

事务的隔离级别

没有事务隔离级别的话，会引发的问题：

1. 脏读
   脏读指一个事务读取了另外一个事务未提交的数据
2. 不可重复读
   指在一个事务内读取表中的某一行数据，多次读取结果不同
   不可重复读和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据
3. 虚读（幻读）
   虚读(幻读)是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

MySQL数据库定义了四种隔离级别

1. 串行化：
   Serializable(串行化)：可避免脏读、不可重复读、虚读情况的发生。
2. 可重复读：
   Repeatable read(可重复读)：可避免脏读、不可重复读情况的发生。
3. 读已提交：
   Read committed(读已提交)：可避免脏读情况发生。
4. 读未提交：
   Read uncommitted(读未提交)：最低级别，以上情况均无法保证。

mysql数据库查询当前事务隔离级别：select @@tx_isolation
mysql数据库默认的事务隔离级别是：Repeatable read(可重复读)
mysql数据库设置事务隔离级别：set transaction isolation level 隔离级别名

数据库并发策略

并发控制一般采用三种方法，分别是乐观锁，悲观锁和时间戳

• 乐观锁
  乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题。
• 悲观锁
  悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。
• 时间戳
  时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量，

以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）

1. 共享锁
   又称为读锁，获得共享锁之后，可以查看但无法修改和删除数据，
   所以我们可以给数据库增加读锁，获得读锁的事务就可以读取数据了。当数据库已经被别人增加了读锁的时候，其他新来的事务也可以读数据，但是不能写。
   也就是说，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
   用法：在查询语句后面添加lock in share mode，Mysql会对查询结果中的每行加共享锁

SELECT ... LOCK IN SHARE MODE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据
2. 排它锁
又称为写锁、独占锁，获得排他锁之后，既能读数据，又能修改数据
就是我们对数据进行写操作的时候，要先获得写锁，获得写锁的事务既可以写数据也可以读数据。但是，如果数据库已经被别人增加了排他写锁，那么后面的事务是无法在获得该数据库的任何锁的。
也就是说，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的*。获准排他锁的事务既能读数据，又能修改数据。
用法：在查询语句后面加入for update

SELECT ... FOR UPDATE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞

拿MySql的InnoDB引擎来说，对于insert、update、delete等操作。会自动给涉及的数据加排他锁；
对于一般的select语句，InnoDB不会加任何锁

数据库锁

• 行级锁
  行级锁是一种排它锁，防止其他事务修改此行

1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁。

• 表级锁
  表示对当前操作的整张表加锁，实现简单，资源消耗较少，表级锁定分为表共享读锁（共享锁）与表独占写锁
  （排他锁）
• 页级锁
  页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁。