MySQL优化（2）：存储引擎和锁

存储引擎：

早期的时候，存在如何选择myisam和innodb？

现在，innodb不断地发展完善，成为了主流的存储引擎。

因此5.5之后的mysql，无脑选择innodb即可。

 

mysql中的数据，索引以及其他的对象，是如何存储的，是一套文件系统的实现。

mysql支持很多的存储引擎，使用

show engines

即可查到全部的引擎

 

myisam和innodb的比较：

1.存储文件的角度：myisam数据和索引分别存储，数据是.myd，索引是.myi；innodb数据和索引集中存储为.ibd，不支持文件级别的移动

2.记录存储顺序的角度：myisam顺序存储，直接在表末尾插入记录；innodb主键顺序存储，插入时需要排序操作，稍影响了效率

3.空间碎片的角度：myisam会产生；innodb不会产生；空间碎片就删除了以前的记录，myisam将会空余下来一些空间

通俗来讲，就说原来存储文件100k，我删除了一部分记录后还是100k

优化myisam的空间碎片：

optimize table xxx;

4.外键和事务的角度：myisam不支持；innodb支持，保证数据的完整性

5.全文索引的角度：myisam不支持；innodb新版本支持，但是不支持中文，所以很鸡肋

6.锁的角度：myisam表级锁定；innodb行级锁定，表级锁定，处理并发能力更强

 

总结：没有特殊需求，采用innodb，数据基本都是读写的情况下，可以考虑采用myisam

 

锁：

避免资源争用的机制功能，多个任务同时使用一个资源，对该资源产生争用 

数据库中，多个任务对数据库进行crud操作就是资源争用

 

通俗解释原理：在某个任务使用资源的时候，标识出来，其他任务就不能同时操作，需要等待或者放弃

基本流程：先尝试加锁，如果锁定成功，那么使用该资源，使用完毕后释放；如果失败，进入操作队列，等待锁释放

 

锁的类型：

共享锁（读锁），排他锁（写锁）

不同类型的锁，导致并发操作是不一样的

 

读锁：共享读操作，阻塞写操作，不能执行写操作

写锁：写操作执行的时候独占资源，自己可读可写，其他任务不能读也不能写

mysql执行任何sql时，都会自动增加锁定，常规操作的情况下，不需要手动管理锁

 

这里的锁和上文存储引擎的行锁表锁不一样，是锁具体的实现方式

表级锁：操作会锁定整张表，无论是共享锁还是独占锁

行级锁：操作会锁定操作的记录

 

锁的语法：

表锁：read是共享锁读锁，write是独占锁写锁

lock tables [table-name] read|write;
unlock tables;

行锁：第一个是共享锁，第二个是独占锁

select * from table [table-name] lock in share mode;
select * from table [table-name] for update;

行锁独占锁只锁定了表的该行，其他任务可以读，不能更新锁定的行，行锁不是精确到某一行，而是一个间隙锁

这时候插入锁定的行的条件的记录也会失败（比如锁定id小于20的行，插入id18，也是锁定的）