mysql如何进行引擎优化_MYSQL性能优化之Mysql体系结构，存储引擎

Oracle： 数据文件包括：控制文件、数据文件、重做日志文件、参数文件、归档文件、密码文件。这是根据文件功能行进行划分，并且所有文件都是二进制编码后的文件，对数据库算法效率有极大的提高。由于Oracle文件管理的统一性，就可以对SQL执行过程中的解析和优化，指定统一的标准：

RBO(基于规则的优化器)、CBO(基于成本的优化器)

通过优化器的选择，以及无敌的HINT规则，给与了SQL优化极大的自由，对CPU、内存、IO资源进行方方面面的优化。

MySQL：最大的一个特色，就是自由选择存储引擎。每个表都是一个文件，都可以选择合适的存储引擎。常见的引擎有 InnoDB、 MyISAM、 NDBCluster等。但由于这种开放插件式的存储引擎，比如要求数据库与引擎之间的松耦合关系。从而导致文件的一致性大大降低。在SQL执行优化方面，也就有着一些不可避免的瓶颈。在多表关联、子查询优化、统计函数等方面是软肋，而且只支持极简单的HINT。

SQL Server ：数据架构基本是纵向划分，分为：Protocol Layer(协议层)， Relational Engine(关系引擎)， Storage Engine(存储引擎)， SQLOS。SQL执行过程就是逐层解析的过程，其中Relational Engine中的优化器，是基于成本的(CBO)，其工作过程跟Oracle是非常相似的。在成本之上也是支持很丰富的HINT，包括：连接提示、查询提示、表提示。

插件式的意思是，你可以随时改变你的表所使用的存储引擎，比如你建表的时候用的innodb，用了一段时间，用得不爽，你可以随时改存储引擎，engine=xx，改为别的存储引擎，这个就是插件式，随时可以更换存储引擎，随时插拔

存储引擎之MyISAM

frm不是特有的，mysql任何存储引擎都有，用于记录表的结构的。MYD存储数据信息，MYI存储索引信息

MyISAM使用表级锁，所以对表中数据修改时需要对整个表进行加锁。而在对表中数据读取时，也需要对整表加共享锁。可以看出，读取和写入是互斥的，并发性不是太好。如果是只读的操作，就并发性来讲，还是可以接受的，因为共享锁并不会阻塞共享锁。

对表进行修复可能会造成数据丢失。

除了使用repair table myIsam来修复，还可以使用这个工具myisamchk –help(修复时需要将mysql服务停止，要不表就糟了，糟了，糟了)

全文索引，还支持CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀(前500个字符)索引。

不好意思啊，文件太小啦。只是掩饰啊；

对于已经压缩的表，是不能进行写操作的。只能进行读操作

下面讲一下Innodb

MYSQL5.58之后版本默认存储引擎

可以看到没有IBD的文件

嘿嘿，我躲在这里

当数据过多时，删除无用数据(日志)并不能使表空间减少

方法：导出所有Innodb表，删除Innodb相关的表空间文件后，重启mysql，进行表空间的重建，再导入进去

所以，对Innodb使用独立表空间

系统表空间和独立表空间要如何选择？

Innodb数据字典：数据库对象结构的元数据信息，存放与数据库相关的信息，如表列，索引，外检……. (frm文件是mysql服务器层产生的文件，只是一个简单的二进制文件；而字典是存储引擎内部产生的，可以保证事务的安全性，存储数据与引擎相关)

Undo回滚段(5.6支持)

http://raugher.blog.51cto.com/3472678/1187780

redo log(已提交事务)缓存区大小，1秒刷新，顺序写入

undo log未提交事务，随机读写，可以独立于系统表空间存在(SSD，硬盘，获得更好的性能)

独占锁，启动事务

没有被阻塞

锁的粒度实际上是锁的策略，就是指被加锁的最小单位。锁的粒度也就是锁的范围，分为行锁和表锁，页锁。锁的范围和多个因素有关，包括事务隔离级别、是否使用索引。锁的粒度越小，并发程度越高。

开销、加锁速度、死锁、粒度、并发性能

l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

l 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索 引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理 (OLTP)系统。

添加表级独占锁，在另一个终端查询，被阻塞；

解锁后，阻塞才被终止。

行级锁只在存储引擎实现；

阻塞：不同锁之间兼容性的关系在有些时刻，一个事务中的锁需要等待另一个事务释放资源,确保事务的并发和进行(如果出现了大量的阻塞系统就可以有问题，可能是在一个频繁的表上出现了慢查询，或者其他的管理操作加上了排它锁)

死锁：两个或者两个以上的事务在执行的过程中相互占用对方等待的资源引发的异常(会被系统自动发现并处理，并选择出一个占用资源最少的事务进行回滚操作)(解决：在多个事务按相同的顺序访问所需的资源或者添加新的索引)

**Innodb状态检查：show engine innodb status(平均值统计信息，确保两次操作的间隔时间在30

s以上)**

1. 多长时间的统计

2. 主进程，循环次数，sleep次数，log刷新次数

3. 信号信息：操作系统等待信息

4. 事务信息

5. 文件IO信息

6. 缓存信息：Insert Buffer,哈辛索引，插入缓存，合并操作

7. log信息，log号，检查点

8. 缓存池，内存的信息：总的内存分配以及附加缓存池的信息，行的操作的信息

存储引擎之CSV

将csv文件做为mysql的表处理。MyISAM，Innodb的数据文件是不能查看的(二进制 文件)

mysql> create table mycsv(id int not null,c1 varchar(20) not null,c2 varchar(20) not null) engine=csv;Query OK, 0 rows affectedmysql> show create table mycsv;+-------+----------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                           |+-------+----------------------------------------------------------------------------------------------------------------------------------------+| mycsv | CREATE TABLE `mycsv` (`id` int(11) NOT NULL,`c1` varchar(20) NOT NULL,`c2` varchar(20) NOT NULL) ENGINE=CSV DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------------------------+1 row in setmysql> insert into mycsv values(1,'aaa','bbb'),(2,'ddd','cccc');Query OK, 2rows affectedRecords: 2  Duplicates: 0  Warnings: 0

可以直接在文件编辑操作，添加，删除；

不支持索引

存储引擎之Archive

更加节约存储空间

支持行级锁和缓冲区

模仿了事务缓冲区的某些特点

生成文件：

不支持删除和更新操作：

不支持OLTP处理

使用场景：日志和数据采集类应用ps: 数据处理大致可以分成两大类：联机事务处理OLTP(on-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

存储引擎之Memory

存储特点：也称Heap存储引擎，所以数据保存在内存中

mysql服务器重启,数据丢失，表结构保留(磁盘文件)

IO效率比MyISAM高很多，因为MyISAM只有数据索引保存在内存中

1.HASH索引做等值查询会很快，BTree索引做范围查询比较快。

2. InnoDB热操作时，基本相当于在内存中读取数据，再加上使用行级锁，所以在一个繁忙的系统中，其性能也不会比InnoDB好很多

3. max_heap_table_size最大是16mb

只有一个frm文件：

showtable status like'mymemory'\G

主从复制(主DB重启，从DB还有一份表)无效：因为主DB重启时会重建外面存储引擎的表，所以从DB的表也会重建的

存储引擎之Federated

不提供复制技术的情况下，提供远程访问MySQL.

性能不是很好

show engines查看支持状态

修改配置文件：

创建远端服务器：

给用户添加权限和在表创建相同的表结构

适用环境：不适用于生产环境，适合偶尔的统计分析及手工查询

如何选择正确的存储引擎

事务：InnoDB

不需要事务，主要是select和insert操作，MyISAM也可以使用

如果大部分是insert,也可以使用Archive(日志应用)

备份：只有InnoDB才有免费的在线热备方案

mysql dump只是一种逻辑备份，而且为了数据的一致性，必须加锁，所以它不是一种在线热备的方案

崩溃恢复：MyISAM崩溃的概率比InnoDB的概率高很多

存储引擎特性：如果需要聚簇索引来进行优化：Innodb;

mysql(5.7) Innodb支持空间的搜索和相关函数的应用

最后，尽量不要混合使用多种数据引擎

相关文章