第6章--MySQL存储引擎与数据库操作管理--2019-01-29

数据库原理与应用基础第6章MySQL存储引擎与数据库操作管理第6章MySQL存储引擎与数据库操作管理存储引擎字符集数据库操作管理知识点小结本章实验第6章MySQL存储引擎与数据库操作管理数据库是存储数据库对象的容器，是指长期存储在计算机内，有组织和可共享的数据的集合。数据库的存储方式有特定的规律。MySQL数据库的管理主要包括数据库的创建、选择当前操作的数据库、显示数据库结构以及删除数据库等操作。本章将介绍MySQL存储引擎与数据库操作管理。第6章MySQL存储引擎与数据库操作管理MySQL存储引擎MySQL字符集MySQL数据库操作管理知识点小结本章实验MySQL存储引擎存储引擎就是指表的类型。数据库的存储类型决定了表在计算机中的存储方式。用户可以根据不同的存储方式、是否进行事务处理等来选择合适的存储引擎。MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写自己的存储引擎。MySQL提供了插件式(pluggable)的存储引擎，存储引擎是基于表的。同一个数据库，不同的表，存储引擎可以不同。甚至，同一个数据库表在不同的场合可以应用不同的存储引擎。建表可以指定存储引擎，可以使用不同引擎，优化和逻辑时选着合适的存储引擎来存储的一种形式。同一个表可以使用多个存储引擎。MySQL存储引擎1.查看MySQL支持的存储引擎：SHOWENGINES2.查看显示支持的存储引擎信息：SHOWVARIABLESLIKE'have%'3.查看默认的存储引擎：SHOWVARIABLESLIKE'storage_engine'在命令行的界面中，“\g”或“\G”作用与分号作用相同，”\G”可以让结果更加美观。比如：SHOWENGINES\G;MySQL存储引擎InnoDB存储引擎：为处理巨大数据量时的最大性能设计。完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。是事务（Transaction）安全的，并且支持外键（foreignkey）。InnoDB表空间分为共享表空间与独享表空间。其中，表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。MySQL存储引擎InnoDB存储引擎的特点：给MySQL数据库提供事务，包括回滚，包括修复能力，多版本并发控制事务安全。创建表结构是存储在“.fim”文件中，数据和索引，数据分别存储在innodb表空间中。缺点是读写读取效率比较低，占用的空间也比较大。MySQL存储引擎MyISAM存储引擎：是默认存储引擎，它是基于传统的ISAM类型，ISAM是IndexedSequentialAccessMethod(有索引的顺序访问方法)的缩写，它是存储记录和文件的标准方法。MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩，而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。如果事务回滚将造成不完全回滚，不具有原子性。如果执行大量的查询操作时，MyISAM是更好的选择。MySQL存储引擎MyISAM存储引擎特点：创建表时会存储为3个文件，文件的名字与表名相同，分别是“frmMYDMYI”，frm存储表结构，MYD存储表数据，MYI存储表索引分别保存在这三个文件中。Myisam的表支持三种存储格形分别是：静态型，动态型，压缩型。静态是myisam默认存储格式，字段是固定长度；动态包括变长字段，字段长度是不固定的；压缩型要选择工具dimpaik创建占用磁盘空间比较小。Myisam的优势在于磁盘空间比较小，处理速度快。缺点是不支持事务，没有事务的完整性，安全性以及事务的并发性处理。MySQL存储引擎MEMORY存储引擎：将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。默认使用哈希(HASH)索引，而不是B+树索引。速度非常快，但在使用上还是有一定的限制：只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存。MySQL存储引擎MEMORY存储引擎特点：1）MySQL的特殊存储引擎，它是使用在存储下内存中的内容来创建表，而且所有的数据也是存储在内存中速度快。2）基于memory的存储引擎实际对应一个磁盘文件，文件名与表名相同，类型也与ifim但只存储表结构，数据存储在内存中有利于快速处理，可以提高表的处理效率。3）需要注意服务器需要足够的内存来维持数据引擎来使用，不想使用时需要删除不然系统内存不够。4）缺点是数据存储在内存上，如果意外断电或发生意外时会造成数据丢失。Memory很少使用到，生命周期比较短，一般都是一次性的，为了我们快速读取。MySQL存储引擎MERGE存储引擎：是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作，实际上是对内部的MyISAM表进行的。对MERGE表进行DROP操作，这个操作只是删除MERGE的定义，对内部的表没有任何影响。MySQL中的其他存储引擎(1)BLACKHOLE存储引擎使用场合：验证存储文件语法的正确性来自二进制日志记录的开销测量，通过比较，允许二进制日志功能的BLACKHOLE的性能与禁止二进制日志功能的BLACKHOLE的性能。被用来查找与存储和引擎自身不相关的性能瓶颈。(2)CSV存储引擎起主要用途就是通过数据库中的数据导出成一份报表文件。(3)ARCHIVE存储引擎ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。MySQL存储引擎的选择（1）MyISAM适用场景是不需要事务支持、并发相对较低、数据修改相对较少、以读为主、数据一致性要求不是非常高。（2）InnoDB适用场景是需要事务支持、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成、数据更新较为频繁。（3）MEMORY适用场景是需要很快的读写速度、对数据的安全性要求较低。MEMORY存储引擎对表的大小有要求，不能是太大的表。第6章MySQL存储引擎与数据库操作管理MySQL存储引擎MySQL字符集MySQL数据库操作管理知识点小结本章实验MySQL字符集字符集：一套文字符号及其编码、比较规则的集合。MySQL支持的字符集：默认是latin1（西欧ISO_8859_1字符集的别名）latin1字符集是单字符编码，而汉字是双字节编码，由此可能导致MySQL数据库不支持中文字符查询或者中文字符乱码等问题。MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以使用不相同或相同的字符集。查看字符集的两种方式：•showcharacterset;•使用information_schema.character_sets表用于查看字符集的详细信息，比如显示所有的字符集和该字符集默认校对规则。MySQL字符集MySQL字符集包括字符集和校对规则两个概念。字符集用来定义MySQL存储字符串的方式。校对规则定义比较字符串的方式。字符集和校对规则是一对多的关系，两个不同的字符集不能有相同的校对规则，每个字符集有一个默认校对规则。MySQL支持30多种字符集的70多种校对规则。每个字符集至少对应一个校对规则。查看相关字符集的校对规则的方法：•通过SHOWCOLLATIONLIKE’***’;命令。•通过系统表information_schema.COLLATIONS来查看。MySQL字符集的选择选择MySQL字符集的考虑因素：(1)满足应用支持语言的要求，如果应用要处理的语言种类多，要在不同语言的国家发布，就应该选择Unicode字符集，就目前对MySQL来说，选择utf-8。(2)如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假若已经有数据是GBK文字，如果选择uft-8作为数据库字符集，就会出现汉字无法正确导入或显示的问题。MySQL字符集的选择（续）(3)如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字GBK。因为，相对于UTF-8而言，GBK比较“小”，每个汉字占用2个字节，而UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库Cache以及网络传输的时间。如果主要处理的英文字符，只要少量汉字，那么选择UTF-8比较好。(4)如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定常字符集的处理速度要比变长字符集的处理速度快。(5)考虑客户端所使用的字符集编码格式，如果所有客户端都支持相同的字符集，则应该优先选择字符集作为数据库字符集。这样可以比避免因字符集转化带来的性能开销和数据损失。MySQL字符集的设置MySQL的字符集和校对规则有4个级别的默认设置：服务器级字符集和校对规则数据库级字符集和校对规则表级字符集和校对规则字段级字符集和校对规则服务器级字符集和校对规则查询当前的服务器的字符集：使用”showvariableslike'character_set_server';”命令。查看校对规则：使用“showvariableslike'collation_server';”命令。服务器字符集和校对规则，可以在MySQL服务启动的时候确定。例：若要指定字符集为gbk（校验规则为与其对应的默认的校验规则）•在my.cnf配置文件中设置：[mysqld]character-set-server=gbk;•在启动时指定字符集为gbk，命令如下:mysqld–character-set-server=gbk数据库字符集和校验规则数据库的字符集和校验规则在创建数据库的时候指定，也可以在创建完数据库后通过“alterdatabase”命令进行修改。如果数据库中已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。显示当前数据库字符集和校验规则可用以下两条命名分别查看:showvariableslike‘character_set_database‘showvariableslike‘collation_database’数据库字符集和校验规则设置的规则：如果指定了字符集和校对规则，则使用指定的字符集和校对规则：如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符和校对规则。表字符集和校验规则在创建表的时候指定可以通过altertable命令进行修改如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。要显示当前表字符集和校验规则可用以下如下命名查看:showcreatetable表名;表字符集和校验规则设置表的字符集的规则：如果指定了字符集和校对规则，使用指定的字符集和校对规则；如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。列字符集和校验规则MySQL可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况。列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。连接字