mysql中的索引、事务、锁

索引、事务、锁

一、索引
什么是索引？为什么要有索引？
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
在创建表时创建索引，代码如下所示：

CREATE TABLE mytable(
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX myindex (username(16))
);

在创建表之后创建索引，代码如下所示：

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

查看索引

SHOW INDEX FROM mytable

删除索引

DROP INDEX myindex ON mytable;
或者
ALTER TABLE mytable DROP INDEX myindex;

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

explain select * from mytable where username = 'jack'

索引分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。

二、事务
在 mysql 中，事务的常规操作包括：开启事务、提交事务与回滚事务
开启事务的语法如下所示：

start transaction;
或者
begin transaction;

提交事务的语法如下所示：

commit;

回滚事务的语法如下所示：

rollback;

案例：

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

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

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

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

事务四大特征(ACID)

原子性(A)：事务是最小单位，不可再分；
一致性©：事务要求所有的 DML 语句操作的时候，必须保证同时成功或者同时失败；
隔离性(I)：同一时间只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)。

事务的隔离级别有 4 个

乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

悲观锁

MySQL 中锁的实现主要有两种：共享锁（S）与排它锁（X），它们分别对应数据的读和写操作。共享锁（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 上的锁。