《互联网数据库MySql高级》

索引，事务，锁

一.索引
1.什么是索引？
一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境
中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。
2.为什么要有索引？
索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
3.索引的优劣势

索引的优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。

索引的劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的1.5 倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。

CREATE TABLE mytable( 
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX myindex (username(16))
-- 建表时添加一个索引
);

-- 查看是否索引成功 show是查看命令 mytable是表明
SHOW INDEX FROM mytable
-- 删除索引 语法：drop index 索引名字 on 表明
DROP INDEX myindex ON mytable;

-- 语法：CREATE index 索引名 on 表名(字段)
CREATE index myindex  on mytable(username(16));

二.事务
1.什么是事务？
事务（Transaction）：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。
事务只和 DML语句有关，或者说 DML语句才有事务。
2.转账操作理解事务

actno	balance
1	500
2	100

start transaction; -- 开启事务
begin transaction;

commit; -- 提交事务

rollback -- 回滚事务

actno	balance
1	500
2	100
3	300

3.事务的特征
事务四大特征(ACID)：

Ø 原子性(A)：事务是最小单位，不可再分；

Ø 一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败；

Ø 隔离性(I)：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；

Ø 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)。
4.事务的隔离级别
事务的隔离级别有4个：
Ø 读未提交（readuncommitted）：事务A和事务B，事务A未提交的数据，事务B可以读取到，这里读取到的数据叫做“脏数据”。这种隔离级别最低，一般是在理论上存在，数据库隔离级别一般都高于该级别；

Ø 读已提交（readcommitted）：事务A和事务B，事务A提交的数据，事务B才能读取到。这种隔离级别高于读未提交，可以避免“脏数据”。但如果事务 A 多次读取同一数据，而事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致，这种现象称为“不可重复读”；

Ø 可重复读（repeatable read）：事务A和事务B，事务A提交之后的数据，事务B读不到事务A提交

的数据。这种隔离级别高于读已提交，但如果事务A在修改数据的过程中（比如将所有记录状态设为1），如果事务B向同一张表中插入一条新记录（状态为0），事务A提交后再次查询表，会发现有一条记录状态没有改成1，好像发生了幻觉，这种现象称为“幻读”。可重复读是MySQL默认隔离级别；

Ø 串行化（serializable）：事务A和事务B，事务A在操作数据库时，事务B只能排队等待。这种级别可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。
三.锁
1、乐观锁
乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加

1。也就是先查询出那条记录，获取出 version 字段，如果要对那条记录进行操作(更新)，则先判断此刻 version
的值是否与刚刚查询出来时的 version 的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，并将
version 字段的值加 1；如果更新时发现此刻的 version
值与刚刚获取出来的 version 的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。除了自己手动实现乐观锁之外，现在许多框架已经封装好了乐观锁的实现，如hibernate 或 JPA，需要时，可自行百度搜索“hibernate乐观锁”试试看。

2、悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。