25、InnoDB存储引擎索引和算法
InnoDB存储引擎支持两种常见的索引，一种是B+树索引，另一种是哈希索引。
而且InnoDB存储引擎支持的哈希索引是自适应的。
B+树索引是目前关系型数据库系统中最常用，最有效的索引。
B+树中的B不是代表二叉（binary），而是代表平衡（balance）。
B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入内存，再在内存中进行查找，最后得到查找的数据。


索引的创建和删除可以通过两种方法，一种是alter table，另一种是create/drop index。
索引可以索引整个列的数据，也可以只索引一个列的开头部分数据。
比如表t，b列为varchar(8000)，可以只索引前100个字段，
mysql> alter table t add key idx_b (b(100));


目前MySQL数据库存在的一个普遍问题是，所有对于索引的添加或者删除操作，MySQL数据库是先创建一张新的临时表，然后把数据导入临时表，删除原表，再把临时表重名为原来的表名。
InnoDB存储引擎只支持B+树索引，为BTREE。


B+树索引的使用
并不是所有的查询条件下出现的列都需要添加索引。建议访问表中很少一部分行时，使用B+树索引才有意义。对于性别字段，地区字段，类型字段，他们可取值的范围很小，即低选择性。
如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的。
因此，当访问高选择性字段，并从表中取出很少一部分行时，对这个字段添加B+树索引是非常有必要的。但是如果出现了访问字段是高选择性的，但是取出的数据占表中的大部分的数据时，这个MySQL数据库就不会使用B+树索引了。


26、InnoDB存储引擎中的锁
26.1 InnoDB存储引擎实现了如下两种标准的行级锁：
（1）共享锁：允许事务读一行数据
（2）排他锁：允许事务删除或者更新一行数据
在新版本的InnoDB Plugin中，在information_schema架构下添加了Innodb_trx、Innodb_locks、Innodb_lock_waits。通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁的问题。


查看事务隔离级别：
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
注：对于REPEATABLE READ的事务隔离级别，总是读取事务开始时的行数据。
举例：
会话A：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 4 | jiang |
| 1 | kanggaroo |
+---+-----------+
4 rows in set (0.00 sec)
会话B：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> update t set b = 'nihao' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 1 | kanggaroo |
| 4 | nihao |
+---+-----------+
4 rows in set (0.00 sec)
如果会话B没有提交，则无论是READ COMMITTED还是REPEATABLE READ的事务隔离级别，显示的数据应该是（会话A）：
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 4 | jiang |
| 1 | kanggaroo |
+---+-----------+
再回到会话B，提交当前的事务：
mysql> commit;
再回到会话A：
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 4 | jiang |
| 1 | kanggaroo |
+---+-----------+
可以看到数据已经被修改了。


mysql> show variables like 'tx%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
举例：
会话A：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 4 | jiang |
| 1 | kanggaroo |
+---+-----------+
4 rows in set (0.00 sec)
会话B：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> update t set b = 'nihao' where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 1 | kanggaroo |
| 4 | nihao |
+---+-----------+
4 rows in set (0.00 sec)
如果会话B没有提交，则无论是READ COMMITTED还是REPEATABLE READ的事务隔离级别，显示的数据应该是（会话A）：
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 4 | jiang |
| 1 | kanggaroo |
+---+-----------+
再回到会话B，提交当前的事务：
mysql> commit;
再回到会话A：
mysql> select * from t;
+---+-----------+
| a | b |
+---+-----------+
| 2 | dolphin |
| 3 | dragon |
| 1 | kanggaroo |
| 4 | nihao |
+---+-----------+
4 rows in set (0.00 sec)
可以看到数据已经被修改了。


对于READ-COMMITTED的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。


26.2 自增长和锁
执行如下语句来得到计数器的值：
select max(auto_inc_col) from t for update;


对于自增长另外需要注意的是，InnoDB存储引擎中的实现和MyISAM不同，MyISAM是表锁，自增长不会考虑并发插入的问题，因此在Master用InnoDB存储引擎，Slave用MyISAM存储引擎的Replicate架构下，你必须考虑这种问题。
另外，InnoDB存储引擎下，自增长的列必须是索引，并且是索引的第一个列，如果是第二个则会报错；而MyISAM存储引擎则没有这这个问题：
mysql> create table hello (
->a int auto_increment,
->b int ,
->key (b,a)
-> ) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key




mysql> create table hello ( a int auto_increment, b int , key (b,a) ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)


26.3外键和锁
外键主要用于引用完整性的约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁。


26.4锁的算法
InnoDB存储引擎有3种行锁的算法设计：
Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。
Next-Key Lock：Gap Lock + Record Lock：锁定一个范围，并且锁定记录本身。
InnoDB对于行的查询都是采用这种锁定算法。
举例演示Next-Key Lock的算法：
mysql> create table t ( a int,primary key(a)) engine=innodb;
插入1,2,3,4,7,8数据。
接着开启两个会话：
SessionA SessionB
begin;
select * from t where a <6
lock in share mode; begin;
insert into t select 5(或者6);
(blocking)
在这种情况下，不论插入记录是5还是6，都会被锁定。但是插入9不会被锁定，因为此值不在范围内。
SessionA SessionB
begin;
select * from t where a =7
lock in share mode; begin;
insert into t select 5(或者6);
(success)
这时就可以插入记录5或6。
上面演示的例子都市在InnoDB的默认配置下，即事务的隔离级别为REPEATABLE READ的模式下。因为在此情况下，Next-Key Lock是默认的行记录的锁定算法。


26.4 锁问题
锁可以使得事务可以并发地工作。却也会带来问题。如果避免下面的三种问题，那么将不会产生并发异常。
1、丢失更新
1）事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1.
2）事务T2也查询该行数据，并将取到的数据显示给终端用户User2.
3）User1修改这行记录，更新数据库并提交
4）User2也修改这行记录，更新数据库并提交。
显然，这个过程中用户User1的修改更新操作“丢失”了。恐怖的结果对银行来说（转账）。


其实要避免丢失更新发生，其实需要让这种情况下的事务变成串行操作，而不是并发的操作。
即在上述四种的第（1）种情况下，对用户读取的记录加上一个排他锁，同样，发生第（2）种情况下的操作时，用户也需要加一个排他锁。这种情况下，第（2）步就必须等待第（1），（3）步完成，最后完成第（4）步，如下所示：
SessionA SessionB
begin；
select account into @cash from begin；
t where id = 1 for update;select account into @cash fromt where id = 1 for update;


update t set account = @cash - 9000
where id = 1;
commit;update t set account = @cash - 1
where id = 1;
commit;


2、脏读
脏数据和脏页不同：
脏页：是缓冲池中已经被修改的页，但是还没有刷新到磁盘，即数据库实例内存中的页和磁盘的页中的数据是不一样的，当然在刷新到磁盘之前，日志都已经被写入了重做日志文件中。
而所谓脏数据，是指在缓冲池中被修改的数据，并且还没有被提交。
对于脏页的读取，是非常正确的。脏页是因为数据库实例内存和磁盘的异步同步造成的，这并不影响数据的一致性。并且因为是异步的，因此可以带来性能的提高。
而脏数据却不同，脏数据是指未提交的数据，如果读取了脏数据，即一个事务可以读取到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。
脏数据就是在不同的事务下，可以读到另外事务未提交的数据。
如下：
Session A： Session B：
set @@tx_isolation=’read-uncommitted’; set @@tx_isolation=’read-uncommitted’;
begin; begin;
mysql> select * from t;?
+----+---------+
| id | account |
+----+---------+
| 1 | 1000 |
+----+---------+


mysql> insert into t select Null,2000;mysql> select * from t;
+----+---------+
| id | account |
+----+---------+
| 1 | 1000 |
| 2 | 2000 |
+----+---------+
上面的隔离级别进行了更换，由默认REPEATABLE READ换成了READ UNCOMMITTED，因此在会话A中事务并没有在提交的前提下，会话B中两次select操作取得了不同的结果，并且这两个记录是在会话A中并未提交的数据，即产生了脏读，违反了事务的隔离性。
脏读数据在生产环境中并不常发生。脏读数据发生的条件是需要事务的隔离级别为READ UNCOMMITTED。而目前绝大部分的数据库都至少设置成READ COMMITTED。InnoDB存储引擎的事务隔离级别为READ REPEATABLE。


3、不可重复读
不可重复读是指一个事务内多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。
不可重复读和脏读的区别是：脏读是读到未提交的数据；而不可重复读读到的确实时已经提交的数据，但是其违反了数据库事务一致性的要求。Oracle（READ COMMITTED）等数据库其实是允许不可重复读的现象的。
而InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，就避免了不可重复读的现象。