MySQL之锁、存储引擎、调优、事务与事务回滚

锁

目的：解决客户端并发访问的冲突问题
锁有两种：锁类型和锁粒度
锁类型
读锁：也叫共享锁，用于查询表。在你查询时会对表加锁，此时别人是不能对表记录进行更改的，只能查询，当你查询结束，锁就会解开，别人才可以进行更改。
写锁：也叫互斥锁、排他锁，用于更新表，当你在更新表记录会对表加锁，此时别人既不能查也不能更改。

锁粒度
行级锁 InnoDB
查询和更新都锁一行
表级锁 MyISAM
查询和更新都会锁整张表

存储引擎

存储引擎：engine，就是处理表的处理器，默认是InnoDB，常用的还有MyISAM
查看表的存储引擎：

show create table 表名;

在创建表时指定存储引擎：

create table 表名
(...)engine=myisam

那么常用的存储引擎右移什么特点：
InnoDB：

• 支持事务、外键、行级锁
• 共享表的空间

生成的表的名字是：
表名.frm：表结构和表索引
表名.idb：表记录

MyISAM：

• 支持表级锁
• 独享表空间

生成的表的名字：
表名.frm：表结构
表名.MYD：表记录
表名.MYI：索引信息

那么如何决定使用哪种存储引擎呢？
1. 同时查询操作多的表使用myisam（使用innodb比较浪费资源）
2. 写操作多的表使用innodb

MySQL调优

这一部分是重点记得内容

• 创建索引
  
  • 在select、where、order by 等常设计到的字段上建立索引
• 选择合适的存储引擎
  
  • 读操作多的表设置myisam
  • 写操作多的表设置innodb
• sql语句优化（避免全表扫描，全表扫描比较浪费）
  
  • where子句尽量不使用“！=”，否则就会放弃索引进行全表扫描
  • 尽量避免NULL控制判断，否则进行全表扫描
  • 尽量避免使用or连接操作，否则全表扫描
  • 模糊查询时尽量避免使用前置的 ’ % ’ ,否则全表扫描
  • 尽量避免使用in和not in，否则全表扫描
  • 查询时查询的字段名不能使用 ‘ * ’

说几个调优的案例

第一个   null
# 优化前
select score from stuinfo where sex is null;
# 优化后
创建表时sex字段设置默认值（如：男，女，man，women。。。）

第二个   or
# 优化前：
select name from stuinfo where id=10 or id=20;
# 优化后：
select name from stuinfo where id=10
union all
select name from stuinfo where id=20;

第三个   模糊查询
# 优化前：
select country from sanguo where name like '%惇';
# 优化后：
select country from sanguo where name like '夏%惇'; --最好不要第一个用%

第四个   in / not in
# 优化前：
select name from stuinfo where id in (1, 2, 3, 4);
# 优化后：
select name from stuinfo where id between 1 and 4; --当然必须是连续的，不是的话另做考虑

事务和事务回滚

什么是事务：一件事从开始到结束的整个过程就是一件事务
作用：确保数据的一致性
sql命令会自动提交到数据库执行，如果执行成功会commit，否则rollback（回滚）

回滚就是这条命令执行失败，报废，魂飞魄散（回到执行前的状态）

举个案例：
Tom：有一张建行卡
Lucy：有一张工行卡
Tom在建行自动取款机上geiLucy转5000元钱

表1：CCB
create table CCB(
name varchar(20),
money decimal(20,2)
);

insert into CCB values('Tom', 2000000);

表2：ICBC
create table ICBC(
name varchar(20),
money decimal(20,2)
);

insert into ICBC values('Lucy', 0);

Tom开始转钱：

mysql> begin;
mysql> update CCB set money=money-5000 where name='Tom';

mysql> update ICBC set money=money+5000 where name='Lucy';
mysql> commit;
----以上是一个执行成功的栗子，因为没有碰到任何执行不成功的条件----

mysql> begin;
mysql> update CCB set money=money-5000 where name='Tom';

mysql> update ICBC set 服务器故障。。。;
mysql> rollback;
-----执行成功才怪了吧-----

The end！