MySQL性能优化(二)--MySQL存储引擎以及锁详细讲解篇

1.MySQL结构体系

 外界入口是通过连接管理器开始的，然后最重要的就是查询解析器，对sql语句进行解析，然后有的查询我们并没有优化，但是还是查询效率比较高，是因为查询优化器的原因，然后数据都存储在存储引擎层。

MySQL存储引擎只针对表，数据库中允许出现不同引擎

InnoDB - MySQL默认使用存储引擎

MyISAM – MySQL早期使用的存储引擎

Memory – MySQL内存引擎

还有很多并不常用的存储引擎 XtraDB,CSV,Archive,Federated….

2.InnoDB介绍与存储特性

2.1.InnoDB存储引擎

1.   MySQL5.5.8之后InnoDB是默认存储引擎，MySQL5.5.8之前是MyISAM
2.   InnoDB采用“表空间“保存文件，不同的表空间保存方式也是略有不同。
3.   InnoDB支持事务处理

2.2. InnoDB的存储特性

InnoDB表空间有两种形式:

1. 使用系统表空间(早期使用方式) ibdataN（全局存储）
2. 随着时间的发展，演变成独立的表空间：tablename.ibd(推荐)

这两种表空间是可以切换的，设置innodb_file_per_table决定表空间模式

2.3.图文示例更加了解引擎

mysql创建数据库，库名testdb，字符集不要选择utf-8 只能是3字节，像表情符号都需要4字节，选择utf8mb4支持4字节，排序规则选择一般性排序规则就可以了，创建库创建表以后，找到mysql安装目录，如下图：

找到data，发现都是自己创建的库文件，ibdata1就是系统表空间

刚才我们创建了testdb库，那么进来testdb后，innodb_test1.ibd就是独立的表空间

innodb_test1.ibd 存储的数据等方面的东西,innodb_test1.frm是存储表约束，定义的信息的地方

//查询是否是独立空间  innodb_file_per_table=ON 独立表空间，OFF是系统表空间

show VARIABLES LIKE 'innodb_file_per_table';

//设置表空间为系统表空间的语句

SET GLOBAL innodb_file_per_table=off

设置完成以后，再创建表，就不会在有ibd文件了

2.4 为什么推荐使用独立表空间？

1. 系统表空间 all in one 不利于管理

2. 系统表空间会产生io瓶颈，在文件中也会有锁也会有多线程操控，那么独立的文件读取操作也好肯定要比只有一个文件要好。

3. 系统表空间很难回收存储空间

4. 独立表空间使用optimize table 命令回收存储空间

3.InnoDB的锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 

MySQL默认就会弄表锁还是行级锁，当发现是有索引的操作时就会把操作的哪一行锁死，其余的用户操作不能操控这一行，直到上一个用户释放，提交事务。锁住的只是操作的那一行，其他用户可以选择其他行

注意：在InnoDB中只有利用索引的更新，删除操作，才可以使用行级锁，不能使用索引的写锁操作是表锁，在实际开发的时候，如果遇到写操作，一定要确保update/delete语句的条件要能够使用索引，否则就会锁表，程序就不具备并发性

3.1.锁分类

1.共享锁(读锁)

简称S Lock，读锁是共享的，互不阻塞。例如多用户可以在同时读取数据库同一部分，互不干扰。

在同一时间段内，多个用户可以读取同一个资源，读取的过程中数据不会发生任何变化。读锁之间是相互不阻塞的，多个用户可以同时读，但是不能允许有人修改，任何事务都不允许获得数据上的排它锁，直到数据上释放掉所有的共享锁

2.排它锁（写锁）

简称（X Lock），在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁和写锁操作，只能由一个用户来写其他用户既不能读也不能写

3.2.锁粒度

我们尽量只锁定我们需要修改的那部分的数据，而不是一修改就锁住所有的数据，或者更理想的形式，只精确的锁定修改的数据片，这样子我们锁住的对象带有选择性将提高我们的数据并发。

1.表锁

开销较小，一旦有用户访问这个表就会加锁，其他用户就不能对这个表操作了，应用程序遇到锁等待的可能性比较高

2.页锁

是MySQL中比较独特的一种锁定级别，锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

3.行锁

开销较大，能具体的锁定到表中的某一行数据，但是能更好的支持并发处理，会发生死锁。

4.InnoDB的适用场景

1. InnoDB存储引擎适用于绝大多数场景
2. MySQL5.7以后也支持全文索引与空间函数
3. 5.5版本以前默认是MyISAM

5.MyISAM存储引擎

5.1  MyISAM特点

1. 不支持事务
2. 支持全文检索，支持text支持前缀索引
3. 支持数据压缩
4. 数据二进制保存，内存紧密存储，减少硬盘寻址时间，顺序读性能很好
5. 表级锁，混合读写性能不佳，并发性差

5.2 MyISAM应用场景

1. 非事务应用，例如：保存日志
2. 只读类应用，报表数据，字典数据
3. 空间类应用，开发GIS系统
4. 系统临时表，sql查询，分组临时表引擎

 

示例：创建一个存储引擎是MYISAM的表，创建完以后的结构如图下：

Frm 文件是所有存储引擎都会创建的，用来记录表结构。

MYD数据存储

MYI索引存储

MYD后缀和MYI主要就是存储数据的，所有的数据都存储在这两个文件

5.3 MyISAM并发性和锁级别

MyISAM使用表级锁，当对表中数据进行修改的，会对整个表的数据进行加锁，在读取数据的时候，也需要对表中的数据加共享锁，所以对读写混合的操作并发性并不是太好。如果是只读的时候，并发性还是可以接受的，因为共享锁并不会阻塞共享锁。

MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表添加排它锁。但是在表有读取查询时，也可以往表中插入新的记录。这个共享锁还能进行写操作好像貌似是版本的问题，后期会进行优化的把

6.Memory存储引擎

6.1.Memory特点

1. 不支持事务
2. 内存读写，临时存储（重启应用内存清空）
3. 超高的读写效率，比MyISAM高一个量级
4. 表级锁，并发性差 （把一般读的多的应用设置成Memory，读取的速度非常快，一般每秒几万条都是正常的）

6.2.Memory应用场景

1. 读多写少的静态数据，例如省市县的对应表
2. 充当缓存使用，保存高频访问静态数据
3. 系统临时表（当超出临界值就会向myisam磁盘存储，如果磁盘存储效率就会低的多）

6.3.Memory关键参数

设置max_heap_table_size 控制内存表大小（字节）

设置tmp_table_size 设置内存临时表最大值（字节）注意tmp_table_size值要小于等于max_heap_table_size

set GLOBAL max_heap_table_size=1147483648

SET GLOBAL tmp_table_size=1147483648

SHOW VARIABLES LIKE '%heap%'

set GLOBAL重启后会失效，修改配置文件max_heap_table_size=2048

内存表最大长度由业务和硬件来决定，数据超过上限就会报错。

Sql排序分组所产生的中间表不超过2G使用内存表，超过2G则使用MyiSAM

7.CSV存储引擎

7.1.CSV特点

1. 纯文本保存
2. 不支持事务
3. 不支持索引

7.2.CSV应用场景

1. 数据交换/数据迁移
2. 不依赖MySQL环境

以上就是MySQL存储引擎全部内容，希望可以帮到你！