MySql高级索引、事务和锁

一、索引

1. 什么是索引？
   一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境
   中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。
   说起加速查询，就不得不提到索引了。
2. 为什么要有索引？
   索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关
   键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
   索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
   索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
3. 索引的优劣势：
   索引的 优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。
   索引的 劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的 1.5 倍；索
   引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，
   添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。

MySQL 中索引的使用
创建索引
1.索引可以在创建表时创建，也可以在创建表之后添加

CREATE TABLE mytable(
ID INT not NULL,
username VARCHAR(16) NOT NULL,
INDEX myindex (username(16)) --  index 表示索引，myindex 表示定义的索引名称
);

注意：如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须
指定 length。
2. 在创建表之后创建索引

CREATE INDEX myindex ON mytable(username(16));
或者
ALTER table mytable ADD INDEX myindex(username)

3.查看索引
创建索引后可以使用下面语句查看索引。

show INDEX from mytable;
-- 查看索引是否创建成功show是查看，mytable是表名

4.删除索引
删除索引可以使用 drop 或者 alter 命令来完成。

DROP INDEX myindex ON mytable;
或者
ALTER TABLE mytable DROP INDEX myindex;
-- 代码中的 myindex 是创建索引时定义的索引名称。

4.通过 EXPLAIN 分析 SQL 是否使用到索引

explain select * from mytable where username = 'jack'

注意：如果 key 值为索引名称，则该查询使用了索引，如果 key 值为 null，则表示没有使用索引。

二、事务

1. 什么是事务？
   事务（Transaction）：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转
   账业务，该业务就是一个最小的工作单元)。
   事务只和 DML 语句有关，或者说 DML 语句才有事务。
   2）转账操作理解事务
   以下是银行账户表 t_act(账号、余额)，进行转账操作。
   
   3）使用 update 语句实现转账操作，实现账号 1 向账号 2 转账 100 元

update t_act set balance=balance-100 where actno=1;
update t_act set balance=balance+100 where actno=2;

在一个转账业务中，以上两条 DML 语句必须同时成功或者同时失败。最小单元不可再分，当第一条 DML
6
语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存
中完成的；当第二条 DML 语句执行成功后，和底层数据库文件中的数据完成同步。若第二条 DML 语句执行失败，
则清空所有的历史操作记录，要完成以上的功能必须借助事务。

L MySQL 中事务的使用
1.开启事务

start transaction;
或者
begin transaction;

2.提交事务

commit；

3.回滚事务

rollback;

4.手动开启事务向 t_act 表中插入一行记录，提交事务。

start transaction;-- 手动开启事务
insert into t_act values(3,300); -- 更新操作
commit;-- 提交事务，commit 之后即改变底层数据库数据
select * from t_act;

结果如下

看到这个结果，似乎和直接执行插入一条记录没什么不同。确实，在 MySQL 中，默认情况下，事务是自动提
交的，也就是说，只要执行一条 DML 语句就开启了事务，并且提交了事务。
5.手动开启事务，向 t_act 表中插入一行记录，回滚事务。

start transaction;-- 手动开启事务
insert into t_act values(4,200); -- 更新操作
rollback;-- 回滚事务
select * from t_act;

运行结果如下

从运行结果中可以看到，回滚事务会撤销更新操作，代码执行前后数据表中的数据没有发生任何变化。

三、锁

1. 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库显得尤其重要，也更加复杂。本节我们着重讨论 MySQL 锁机制的特点，常见的锁问题，以及解决 MySQL 锁问题
   的一些方法或建议
   2.乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
   通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加 1。也就是先查询出那条记录，获取出 version 字段，如果要对那条记录进行操作(更新)，则先判断此刻 version 的值是否与刚刚查询出来时的 version 的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，并将 version 字段的值加 1；如果更新时发现此刻的 version 值与刚刚获取出来的 version 的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
   除了自己手动实现乐观锁之外，现在许多框架已经封装好了乐观锁的实现，如 hibernate 或 JPA，需要时，可
   自行百度搜索“hibernate 乐观锁”试试看。
   3.悲观锁
   与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次
   操作时都要通过获取锁才能进行对相同数据的操作，这点跟 java 中的 synchronized 很相似，所以悲观锁需要耗费
   较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相
   关语句就可以了。

MySQL 中锁的实现主要有两种：共享锁（S）与排它锁（X），它们分别对应数据的读和写操作。

1. 共享锁与排它锁
   共享锁（S）：也称为读锁，允许事务读取一行数据。例如事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A
   但不能修改 A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这保证了其他事务可以读
   A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。
   排它锁（X）：也称写锁，允许事务删除或更新一行数据。例如事务 T 对数据对象 A 加上 X 锁，事务 T 可以读
   A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。
2. 锁的粒度
   锁的粒度就是你要锁定的范围是多大。使用锁时，锁定的东西越少，对其他竞争者影响越小，这个东西的大
   小就是锁粒度。
   例如卫生间里的洗漱台，马桶，浴缸，如果分别隔开（三个区域可以分别锁定，粒度小），就可以同时给三
   个人使用。你关上浴室的门，别人还是可以洗手的；但如果卫生间的区域不隔开，一个人进卫生间锁上门，其他
   人就进不去了。可见，锁的粒度越小，锁定的资源越少，对其他用户的影响就越小，当然，对系统的开销也越大。
   锁的粒度分类：
    表级锁：开销小，加锁快，不会出现死锁。锁定粒度最大，适合查询为主的，只有少量按索引条件更新
   数据的操作。缺点是资源争用概率高；
    行级锁：开销大，加锁慢，会出现死锁。锁定粒度最小，适合大量按索引条件并发更新少量不同数据，
   同时又有并发查询的操作。使用行级锁定的主要是 InnoDB 存储引擎；
    页面锁：开销介于表锁与行锁之间，会出现死锁。