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，也是锁定的）