为了管理方便人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后只需按照生成的执行计划调用底层存储引擎提供的API获取数据后返回给客户端就好了。MySQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型其实存储引擎以前叫表处理器后来改名为存储引擎它的功能就是接收上层传下来的指令然后对表中的数据进行提取或写入操作。1查看存储引擎查看MySQL提供什么存储引擎show engines; show engines \G;我们也可以查看MySQL中默认的存储引擎show variables like %storage_engine%; SELECT default_storage_engine;通过上面我可以看到MySQL默认的存储引擎是InnoDB。我们也可以创建表来验证一下效果create table emp1(id int); show create table emp1;当然我们也可以去修改存储引擎如果在创建表的语句中没有显式指定表的存储引擎的话那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话可以这样写启动服务器的命令行SET DEFAULT_STORAGE_ENGINEMyISAM;或者修改my.cnf文件default-storage-engineMyISAM修改完成之后一定要重启MySQL服务。之后在查看可执行上面的指令2设置表的存储引擎存储引擎是负责对表中的数据进行提取和写入工作的我们可以为不同的表设置不同的存储引擎 也就是说不同的表可以有不同的物理存储结构不同的提取和写入方式。创建表时指定存储引擎CREATE TABLE 表名( 建表语句; ) ENGINE 存储引擎名称;修改表的存储引擎ALTER TABLE 表名 ENGINE 存储引擎名称;3存储引擎的特点3.1 InnoDB引擎的特点具备外键支持功能的事务存储引擎MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后默认采用InnoDB引擎。InnoDB是MySQL的默认事务型引擎它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。InnoDB支持外键。除了增加和查询外还需要更新、删除操作那么应优先选择InnoDB存储引擎。除非有非常特别的原因需要使用其他的存储引擎否则应该优先考虑InnoDB引擎。数据文件结构表名.frm存储表结构MySQL8.0时合并在表名.ibd中表名.ibd存储数据和索引在以前的版本中字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如.frm、.par、.trn、.isl、.db.opt等都在 MySQL 8.0 中不存在了。对比 MyISAM 的存储引擎InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保存数据和索引。MyISAM 只缓存索引不缓存真实数据InnoDB 不仅缓存索引还要缓存真实数据对内存要求较高而且内存大小对性能有决定性的影响。InnoDB 支持事务的特性演示首先创建数据表create table goods_innodb( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINEinnodb DEFAULT CHARSETutf8;在窗口1开启事务并插入数据start transaction; -- 开启事务 insert into goods_innodb(id,name) values(null,Meta20); -- 插入数据不提交窗口1的事务。查看窗口2发现没有数据新增。但当 commit 窗口1的事务后窗口2就能查询到窗口1提交的数据。InnoDB 支持外键的演示MySQL支持外键的存储引擎只有InnoDB在创建外键的时候要求父表必须有对应的索引子表在创建外键的时候也会自动的创建对应的索引。父表country_innodbcountry_id为主键索引子表city_innodb表country_id字段为外键对应于country_innodb表的主键country_id。create table country_innodb( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINEInnoDB DEFAULT CHARSETutf8; create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT fk.city.country FOREIGN KEY (country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINEInnoDB DEFAULT CHARSETutf8; insert into country_innodb values(null,China),(null,America), (null,Japan); insert into city_innodb values(null,Xian,1),(null,New York,2), (null,Beijing,1);当我们删除主表的数据发现无法删除这就是外键起作用了。3.2 MyISAM 引擎主要的非事务处理存储引擎MyISAM提供了大量的特性包括全文索引、压缩、空间函数(GIS)等但MyISAM不支持事务、行级锁、外键有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎。优势是访问的速度快对事务完整性没有要求或者以SELECT、INSERT为主的应用。针对数据统计有额外的常数存储故而count(*)的查询效率很高。数据文件结构表名.frm存储表结构表名.MYD存储数据 (MYData)表名.MYI存储索引 (MYIndex)应用场景只读应用或者以读为主的业务。演示 MyISAM 存储引擎不支持事务创建一张数据表create table goods_myisam( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINEmyisam DEFAULT CHARSETutf8;打开窗口1、窗口2并登录到数据库。在窗口1开启事务插入数据先不提交查看窗口2的数据。通过测试发现在MyISAM存储引擎中是没有事务控制的。3.3 InnoDB 和 MyISAM 的对比对比项MYISAMINNODB外键不支持支持事务不支持支持行级锁表锁即使操作一条记录也会锁住整个表不适合高并发的操作行锁操作时只锁某一行不对其它行有影响适合高并发的操作缓存只缓存索引不缓存真实数据不仅缓存索引还要缓存真实数据对内存要求较高而且内存大小对性能有决定性的影响自带系统表使用YN关注点性能节省资源、消耗少、简单业务事务并发写、事务、更大资源默认安装YY默认使用NY4 MySQL 其他的存储引擎介绍4.1 Archive 引擎Archive 是归档的意思仅支持插入和查询两种功能。在MySQL5.5以后支持索引功能。它的特点是拥有很好的压缩机制使用zlib压缩库在记录请求的时候实时压缩经常被用来当做仓库使用。创建 Archive 表时存储引擎会创建名称以表名开头后缀为.archive的文件。根据测试结果来看同样数据量下Archive表会比MyISAM表要小约75%比支持事务处理的InnoDB表要小83%。Archive 表存储引擎采用了行级锁该引擎也支持列的AUTO_INCREMENT属性列AUTO_INCREMENT可以具有唯一索引或者非唯一索引。Archive 表适合支持日志或者数据采集(档案)类应用。适合存储大量的独立的作为历史记录的数据拥有很高的数据插入速度。但是对查询性能支持较差。Archive 表的详细特性特征支持B树索引不支持备份/时间点恢复在服务器中实现而不是在存储引擎中支持集群数据库支持不支持聚焦索引不支持压缩数据支持数据缓存不支持加密数据加密功能在服务器中实现支持外键支持不支持全文检索索引不支持地理空间数据类型支持支持地理空间索引支持不支持哈希索引不支持索引缓存不支持锁粒度行锁MVCC不支持存储限制没有任何限制交易不支持更新数据字典的统计信息支持4.2 Blackhole 引擎特点丢弃写操作读操作会返回空内容。Blackhole 没有实现任何存储机制它会丢弃所有插入的数据不做任何保存。单服务器会记录Blackhole 表的日志所以可以用于复制数据到备份数据库或者简单做日志记录但这种应用方式会碰到很多问题一般不推荐使用。4.3 CSV 引擎特点存储数据时以逗号分隔各个数据项。CSV引擎可以将普通的CSV文件作为MySQL的表来处理但不支持索引。CSV引擎可以是一种数据交换机制非常实用。CSV引擎存储的数据可以直接在操作系统里面使用文本编辑器打开或者使用Excel文件打开。对于数据的快速导入非常有优势。具体使用CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)ENGINE CSV; INSERT INTO test VALUES(1,record one),(2,record two); SELECT * FROM test;如果检查test.CSV(在数据库存储数据可用SHOW VARIABLES LIKE datadir;)文件其内容使用文本编辑器打开这种格式可以被 Microsoft Excel 等电子表格应用程序读取甚至写入。4.4 Memory 引擎特点置于内存的表。概述Memory采用的逻辑介质是内存响应速度很快但是当mysqld守护进程崩溃的时候数据会丢失。另外要求存储的数据是数据长度不变的格式比如Blob和Text类型的数据不可用(长度不固定的)。特点Memory同时支持哈希(HASH)索引和B树索引。Memory表至少比MyISAM表要快一个数量级。MEMORY表的大小是受到限制的。表的大小主要取决于两个参数分别是max_rows和max_heap_table_size。其中max_rows可以在创建表时指定max_heap_table_size的大小默认为16MB可以按需要进行扩大。数据文件与索引文件分开存储。缺点其数据易丢失生命周期短。基于这个缺陷选择MEMORY存储引擎时需要特别小心。使用Memory存储引擎的场景1.目标数据比较小而且非常频繁的进行访问在内存中存放数据如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小限制Memory表的最大的大小。2.如果数据是临时的而且必须立即可用得到那么就可以放在内存中。3.存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。4.5 Federated 引擎Federated引擎是访问其他MySQL服务器的一个代理尽管该引擎看起来提供了一种很好的跨服务器的灵活性但也经常带来问题因此默认是禁用的。4.6 Merge 引擎管理多个MyISAM表构成的表集合。4.7 NDB 引擎MySQL集群专用存储引擎。也叫做 NDB Cluster 存储引擎主要用于 MySQL Cluster 分布式集群环境类似于 Oracle 的 RAC 集群。MySQL中同一个数据库不同的表可以选择不同的存储引擎。常用存储引擎对比总表特点MyISAMINNODBMEMORYMERGENDB存储限制有64TB有没有有事务安全—支持———锁机制表锁即使操作一条记录也会锁住整个表不适合高并发的操作行锁操作时只锁某一行不对其它行有影响适合高并发的操作表锁表锁行锁B树索引支持支持支持支持支持哈希索引——支持—支持全文索引支持————集群索引—支持———数据缓存——支持—支持索引缓存只缓存索引不缓存真实数据不仅缓存索引还要缓存真实数据对内存要求较高而且内存大小对性能有决定性的影响支持支持支持数据可压缩支持————空间使用低高N/A低低内存使用低高中等低高批量插入的速度高低高高高支持外键—支持———其实这些东西大家没必要立即就给记住列出来的目的就是想让大家明白不同的存储引擎支持不同的功能。其实我们最常用的就是InnoDB和MyISAM有时会提一下Memory。其中InnoDB是MySQL默认的存储引擎。
