【Mysql调优】2、Mysql体系和存储引擎

1、Mysql体系

使用sql的执行过程就很好大致理解这个体系图：

• 支持接口就是进行链接的客户端，发起sql之后，首先会到连接器当中进行连接的管理和权限的校验；

• 之后sql语句会被查询解析器进行解析，看sql语法是否正确，分析正确时候向缓存当中查询该sql是否有缓存，有则直接返回结果集（查询缓存是需要手动开启，使用并不多所以mysql8废弃了查询缓存）；

• sql语法正确则通过查询优化器在进行对sql执行进行优化，索引选择等之后；

• 通过执行器去调用相应的存储引擎返回结果集。

  需要注意的是上述中除了存储引擎，其他部分都是发生在Server层当中，mysql的server层和存储引擎是分离的，存储引擎是以插件的形式存在于mysql当中；并且存储引擎是针对表的，数据库中允许出现不同的存储引擎，但是表只能有一种存储引擎。
  Mysql的存储引擎有很多，InnoDB、MyISAM、XtraDB、CSV、Memory、Atchive、Federated等等，但是常见的存储引擎只有三种，InnoDB、MyISAM、Memory。

2、Mysql当中的锁（基本概念）

按职责分类

共享锁-读锁：只要持有一条数据的共享锁的线程都可以访问到这条数据。共享锁在查询时提供共享的区域，所有线程
均可持有一条数据的共享锁。

独占锁（排他锁）-写锁：当一个线程获取到一条数据的独占锁，那么其他线程想获取这条数据只能进行等待。同一时
间数据只能让一个线程进行操作，也就是一条数据的独占锁同一时间只能让一个线程占用。

按粒度分类

按粒度分类，其实就是通过锁锁定的范围进行分类。
行级锁：顾名思义是锁的是一行记录，如果多个线程同时分别操作不同行的记录，那么他们是并行进行进行的，互相不
会影响；但是如果多个线程同时操作同一行数据记录，只要有一个线程获取到数据的行级锁，那么其他线程就会出现排
队的情况。

表级锁：锁定的是整张表，一个线程只要获取到某张表的表级锁，那么其他线程在表级锁没被释放前，均需要进行
排队等待。

注意：
其中使用到了id主键，带有唯一索引。

3、存储引擎

1）InnoDB存储引擎

Mysql5.5.8之后InnoDB是默认的存储引擎
采用“表空间”保存文件
支持事务处理

存储特性

InnoDB表空间有两种形式：
早期使用的是系统表空间ibDataN(N为一个数字)，它是一个全局的表空间，时间长之后所有数据放在一个文件当中
会存在一定的IO问题，所以Mysql5.7之后出了独立表空间：tableName.ibd(推荐使用)，每张表会生独立的表空
间。

设置表空间模式

-- 查看innodb表空间模式是否开启(ON 开启，OFF关闭)
show variables like '%innodb_file_per_table';

-- 关闭innodb表空间模式，使用全局系统表（关闭innodb_file_per_table）
set global innodb_file_per_table=off;

注意1：在关闭innodb_file_per_table后，新建表tableA的数据信息就不会在data目录下生成对应的
tableA.ibd的数据文件，只存在tableA.frm表结构文件，数据信息会被保存到全局系统表ibdataN当中。

注意2：Mysql版本在5.6之前要使用独立表空间要手动开启

为什么推荐独立表空间

• 系统表空间相当于所有表结构、表数据都存放在一个文件当中，不利于管理
• 系统表空间会产生IO瓶颈，因为文件系统也有锁的概念，多个独立表空间相较于系统表空间是不同的文件会有所缓解
• 系统表空间很难被回收存储空间，假设有数据库当中使用系统表空间，要进行存储空间回收，对某些表进行删除，Mysql并没有命令针对于系统表空间进行存储回收，如果需要这样做只能将所有数据导出然后删除某些表的数据然后重算文件，这针对于线上数据库是不可能的
• 独立表空间使用optimize table命令回收表空间

InnoDB事务特性

• 支持事务
• 默认使用行级锁
• 具备良好的高并发特性
• InnoDB适用于绝大多数场景，Mysql5.7之后InnoDB支持全文索引和空间函数

2） MyISAM存储引擎

• 不支持事务（rollback命令不会回滚事务当中MyISAM表的修改）
• 支持全文检索，支持text文本类型的前缀索引
• 支持数据压缩
• 在硬盘上存储方式为紧密存储，采取顺序读的方式性能很好
• 只支持表级锁，不支持并发操作

应用场景：

（1）非事务性应用：保存日志（基本上都是追加，很少有覆盖删除等操作，相当于记录流水账，Myisam支
持压缩，可节省空间）
（2）只读类应用：报表数据、字典数据等写少读多的场景（MyIsam在写表时会锁表）
（3）空间类应用：GIS地理空间系统（5.7之前只有MyIsam引擎能够调用空间函数供GIS系统进行空间地
理位置的运算，而5.7之后InnoDB引擎也提供了对空间函数的支持）
（4）系统临时表：SQL子查询、分组查询等操作也都会生成临时的MyIsam引擎的表

存储特性

数据库表生成之后有三个文件，都是以表名命名不同后缀的文件，frm文件保存表的结构，MYD保存表的数据，MYI保存表的索引文件，MYD和MYI与MyISAM引擎有很深的关联。

3）Memory存储引擎

不支持事务
内存读写，临时存储（Mysql服务重启数据清空）
超高的读写频率，比MyISAM高一个量级（高版本的Mysql的InnoDB存储引擎有查询缓存，需要手动开启）

应用场景

（1）读多写少的静态数据，例如省市县的静态表；
（2）充当缓存使用，保存高频访问的静态数据；
（3）系统临时表，并不是业务意义上的临时表，而是mysql底层在进行排序、分组等操作时自动创建的临时表（sql优化尽量避免临时表）；

关键参数

• max_heap_table_size：内存表最大容量（单位：字节），限制Memory引擎的表最大存储大小

• tmp_table_size：内存临时表最大值（单位：字节），该值设置时要小于等于max_heap_table_size

  两种设置参数的方式：
  （1）修改my.cnf配置文件，在[mysqld]下增加一行配置，max_heap_table_size=2048M，或tmp_table_size=2048M（该方式设置后需要重启Mysql服务）
  （2）在msyql连接中设置全局参数（单位：字节），命令 set GLOBAL max_heap_table_size = 2147483648或set GLOBAL tmp_table_size = 2147483648（该方式设置之后要在新的连接中查看才能生效）

存储特性

创建之后只存在frm文件，数据在Mysql服务重启后清空，一般数据来源于其他持久化的表。

4）CSV存储引擎

纯文本保存（建表字段均需not null标明）
不支持事务
不支持索引

应用场景