Loading

MySQL数据库引擎

一、数据库引擎概述

1、为什么要合理选择数据库引擎

​ MySQL中的数据用各种不同的技术存储在文件或者内存中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，可以获得额外的性能和功能，从而改善应用整体性能。

​ 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

2、数据库引擎定义

​ 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

3、存储引擎作用

  1. 设计并创建数据库以保存系统所需的关系或XML文档。
  2. 实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。
  3. 为单位或客户部署实现的系统。
  4. 提供日常管理支持以优化数据库的性能。

4、如何修改数据库引擎

  1. 修改配置文件my.ini：将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB

  2. 建表时指定数据库引擎：

    create table mytbl(   
    id int primary key,   
    name varchar(50)   
)type=MyISAM;

  3. 建表后更改：

    alter table table_name type = InnoDB;

5、查看修改后的数据库引擎

  1. show table status from database_name; 

  2. show create table table_name;

  3. 使用数据库管理工具查看

二、MySQL各大存储引擎

存储引擎主要有：1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

1、InnoDB

​ MySQL官方对InnoDB的讲解：

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。

  2. InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。

  3. InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

  4. InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

  5. InnoDB被用来在众多需要高性能的大型数据库站点上产生。

    ​ 官方语言太官方

  6. 定义：（默认的存储引擎）InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

  7. Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

  8. 适用场景

    1）经常更新的表，适合处理多重并发的更新请求。

    2）支持事务。

    3）可以从灾难中恢复（通过bin-log日志等）。

    4）外键约束。只有他支持外键。

    5）支持自动增加列属性auto_increment。

  9. 补充：事务，简称ACID是什么：

    1. A 事务的原子性(Atomicity)：指一个事务要么全部执行，要么不执行。也就是说一个事务不可能只执行了一半就停止了。比如从取款机取钱，这个事务可以分成两个步骤：1划卡，2出钱。这两步要么同时完成，要么都不完成。
    2. C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性。例如：完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
    3. I 独立性(Isolation）：事务的独立性也有称作隔离性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致。
    4. D 持久性(Durability）:事务的持久性是指事务执行成功以后，该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

2、MyIsam

  1. 定义：MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

    MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

  2. 适用场景：

    1. 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
    2. 不支持外键的表设计。
    3. 查询速度很快，如果数据库insert和update的操作比较多的话比较适用。
    4. 整天 对表进行加锁的场景。
    5. MyISAM极度强调快速读取操作。
    6. MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

三、InnoDB和MyIsam使用及其原理对比

  1. 事务：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。
  2. 性能主题：MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。
  3. 行数保存：InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。
  4. 索引存储：对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持
  5. 服务器数据备份：InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
  6. 锁的支持：MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

使用建议：

InnoDB：

1）可靠性高或者要求事务处理，则使用InnoDB。

2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

MyISAM：

1）做很多count的计算的。如一些日志，调查的业务表。

2）插入修改不频繁，查询非常频繁的。

posted @ 2021-04-16 23:53  Zzxij  阅读(444)  评论(0)    收藏  举报