本文转载自微信公众号《JavaBuilder》，作者cxuan。如需转载本文，请联系Java开发者公众号。我们在MySQL入门章节主要介绍了基本的SQL命令、数据类型和函数。掌握了以上知识之后，就可以进行MySQL的开发了，但是要想成为一名合格的开发者，还需要具备一些更高级的技能，下面我们就来探讨一下MySQL需要哪些高级技能。MySQLStorageEngineStorageEngine概述数据库的核心点是存储数据，而数据的存储就不能避免与磁盘打交道。那么数据如何存储、如何存储是存储的关键。所以存储引擎相当于数据存储的引擎，驱动数据在磁盘层面进行存储。MySQL的架构可以按照三层模型来理解。存储引擎也是MySQL的组成。它是一种软件。多种存储引擎可用于不同的数据库应用程序。用户可以根据自己的需要选择合适的存储引擎。以下是MySQL支持的存储引擎：MyISAMInnoDBBDBMEMORYMERGEEXAMPLENDBClusterARCHIVECSVBLACKHOLEFEDERATED默认情况下，如果创建表时没有指定存储引擎，将使用默认存储引擎。如果要修改默认存储引擎，可以在参数文件中设置default-table-type，可以查看当前存储引擎showvariableslike'table_type'；奇怪，怎么不见了？在线验证暂时5.5.3取消了该参数。可以通过以下两种方式查询当前数据库支持的存储引擎showengines\g创建新表时，可以通过添加ENGINE关键字来设置新表的存储引擎。createtablecxuan002(idint(10),namevarchar(20))engine=MyISAM;在上图中，我们指定了MyISAM的存储引擎。不知道表的存储引擎怎么办？可以通过showcreatetable查看。如果不指定存储引擎，从MySQL5.1开始，MySQL默认内置的存储引擎已经是InnoDB。创建一个表并查看。如上图所示，我们没有指定默认的存储引擎。检查下表可以看到默认的存储引擎是InnoDB。如果你的存储引擎要更换，可以使用altertablecxuan003engine=myisam;替换它。替换完成后会显示“0rowsaffected”，但实际上已经操作成功。我们通过showcreatetable查看表的sql，了解存储引擎的特点。它将介绍几种常用的存储引擎及其基本特性。这些存储引擎是MyISAM、InnoDB、MEMORY和MERGEMyISAM在5.1版本之前，MyISAM是MySQL的默认存储引擎。MyISAM并发性差，使用场景较少。主要特点是不支持事务操作，不存在ACID的特性。这种设计是出于性能和效率的考虑。不支持外键操作。如果强行加外键，MySQL不会报错，但外键不起作用。MyISAM默认的锁粒度是表级锁，所以并发性能比较差，加锁速度更快，锁冲突更少，更不容易出现死锁。MyISAM会在磁盘上存储三个文件，文件名与表名相同，扩展名分别为.frm（存储表定义）、.MYD（MYData，存储数据）、MYI（MyIndex，存储索引）。这里需要特别注意的是MyISAM只缓存索引文件，不缓存数据文件。MyISAM支持的索引类型有全局索引（Full-Text）、B-Tree索引、R-Tree索引Full-Text索引：它的出现是为了解决文本模糊查询效率低下的问题。B-Tree索引：所有索引节点按照平衡树的数据结构存储，所有索引数据节点都在叶子节点中R-Tree索引：其存储方式与B-Tree索引有些不同，主要设计用于存储空间和多维数据的字段作为索引。目前的MySQL版本只支持几何类型的字段作为索引。与BTREE相比，RTREE的优势在于范围搜索。如果数据库所在的主机宕机，MyISAM的数据文件很容易损坏，而且很难恢复。增删改查和查询性能：SELECT性能高，适用于InnoDB的很多查询，从MySQL5.1开始，默认的存储引擎变成了InnoDB存储引擎。与MyISAM相比，InnoDB存储引擎发生了重大变化。主要特点是支持事务操作，具有事务ACID隔离特性，默认隔离级别为可重复读（repetable-read），通过MVCC（并发版本控制）实现。可以解决脏读和不可重复读的问题。InnoDB支持外键操作。InnoDB默认的lock-granularity行级锁，并发性能较好，但可能会出现死锁。和MyISAM一样，InnoDB存储引擎也有一个.frm文件存储表结构定义，但不同的是InnoDB的表数据和索引数据是存储在一起的，并且都位于B+号的叶子节点上，而MyISAM的表数据和索引数据是分开的。InnoDB有一个安全的日志文件，用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。InnoDB和MyISAM支持的索引类型是一样的，但是由于文件结构的不同，具体实现上有很大的不同。在CRUD性能方面，如果进行大量的CRUD操作，建议使用InnoDB存储引擎，删除行，不重建表。MEMORYMEMORY存储引擎使用内存中的内容来创建表。每个MEMORY表实际上只对应一个磁盘文件，格式为.frm。MEMORY类型的表访问速度非常快，因为它们的数据存储在内存中。默认使用HASH索引。MERGEMERGE存储引擎是一组MyISAM表的组合。MERGE表本身没有数据。MERGE类型的表的查询、更新、删除等操作实际上是在MyISAM内部表上进行的。MERGE表在磁盘上有两个文件，一个是.frm文件，存放表定义，另一个是.MRG文件，存放MERGE表的组成等。在实际开发过程中选择合适的存储引擎，我们往往会根据应用的特点选择合适的存储引擎。MyISAM：如果应用平时以检索为主，只有少量的插入、更新、删除操作，事物的完整性和并发性不是很高，通常建议选择MyISAM存储引擎。InnoDB：如果使用外键，要求并发度高，要求数据一致性，那么通常选择InnoDB引擎。一般各大互联网公司对并发和数据完整性的要求都比较高，所以一般使用InnoDB存储引擎。MEMORY：MEMORY存储引擎将所有数据存储在内存中，在需要快速定位时可以提供极快的访问。MEMORY通常用于不经常更新的小表，用于快速访问得到结果。MERGE：MERGE在内部使用MyISAM表。MERGE表的优点是可以突破单个MyISAM表的大小限制，通过将不同的表分布在多个磁盘上，可以有效提高MERGE表的访问效率。选择正确的数据类型我们经常遇到的问题之一就是在建表时如何选择正确的数据类型。通常，选择正确的数据类型可以提高性能，减少不必要的麻烦。让我们一起讨论如何选择合适的数据类型。CHAR和VARCHAR的选择Char和varchar是我们经常用来存储字符串的两种数据类型。Char一般存储定长字符串，属于定长字符类型。比如下面的值char(5)存储的是字符Section''''5字节'cx''cx'5字节'cxuan''cxuan'5字节'cxuan007''cxuan'5字节可见，不管你的值写的是什么，一旦指定了char字符的长度，如果你的字符串长度不够指定字符的长度，那就用空格填充，如果超过了字符串的长度，就只存储指定长度的字符的字符。?这里注意：如果MySQL使用非严格模式，可以存储上表的最后一行。如果MySQL使用严格模式，那么表上面的最后一行会报错。?如果使用varchar字符类型，我们看一下例子值varchar(5)存储字节''''1byte'cx''cx'3bytes'cxuan''cxuan'6bytes'cxuan007''cxuan'6个字节可见，如果使用varchar，那么存储的字节会按照实际值存储。你可能会奇怪为什么varchar的长度是5，但是它需要存储3个字节或者6个字节，因为在使用varchar数据类型存储的时候，默认会在末尾加上一个字符串长度，占用1个字节（如果列声明的长度超过255，使用两个字节）。varchar不会填充空字符串。一般用char存储定长字符串，如“身份证号、手机号、邮箱等”；varchar用于存储可变长度的字符串。由于char的长度是固定的，因此其处理速度比VARCHAR快很多，但缺点是浪费存储空间。但是随着MySQL版本的不断演进，varchar数据类型的性能也在不断的提升和提高，所以在很多应用中，更多的是使用VARCHAR类型。在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则不同MyISAM：推荐使用定长数据列，而不是变长数据列，即CHARMEMORY：使用定长处理，CHAR和VARCHAR会使用TreatInnoDBasCHAR:推荐使用VARCHAR类型的TEXT和BLOB。一般在保存较少的文本时，我们会选择CHAR和VARCHAR。在保存数据量大的文本时，我们往往选择TEXT和BLOB；TEXT和BLOB的主要区别在于BLOB可以存储二进制数据；而TEXT只能存储字符数据。TEXT可以细分为三种类型：TEXTMEDIUMTEXTLONGTEXTBLOB和BLOBMEDIUMBLOBLONGBLOB。不同区间，用户应根据实际情况选择满足需要的最小存储类型。下面主要介绍一下BLOB和TEXT的一些问题。TEXT和BLOB在删除数据后会有一些性能问题。为了提高性能，建议使用OPTIMIZETABLE函数对表进行碎片整理。合成索引还可用于提高文本字段（BLOB和TEXT）的查询性能。合成索引是根据大文本（BLOB和TEXT）字段的内容创建一个哈希值，并将这个值存储在对应的列中，这样就可以根据哈希值找到对应的数据行。一般使用md5()、SHA1()等哈希算法。如果哈希算法生成的字符串有尾部空格，不要将它们存储在CHAR和VARCHAR中。我们来看看这个使用方法。首先创建一张Zhang表，在表中记录blob字段和hash值向cxuan005插入数据，hash值作为info的hash值。然后插入两条数据。插入一条信息为cxuan005的数据。如果要查询info为cxuan005的数据，可以通过查询hash列来查询。这是一个综合索引的例子。如果要对BLOB进行模糊查询，就必须使用前缀索引。其他优化BLOB和TEXT的方法：不需要时不要检索BLOB和TEXT索引将BLOB或TEXT列分离到单独的表中。浮点数和定点数的选择浮点数是指包含小数的数值。将浮点数插入指定列后，超出指定精度后，对该浮点数进行四舍五入。MySQL中的浮点数是指float和double，定点数是指十进制，定点数可以更准确的保存和显示数据。让我们通过一个例子来解释浮点数的准确性。首先，创建一个表cxuan006只是为了测试浮点数问题。所以这里我们选择float的数据类型然后插入两条数据然后执行查询。可以看到两条查询结果每条数据进行的舍入是不一样的。为了清楚的看出浮点数和定点数的精度，我们来看一个例子。首先将cxuan006的两个字段修改为相同的长度和小数位，然后插入两条数据进行查询操作。发现与定点数相比，浮点数会产生误差。MySQL中的日期类型选择是“DATE、TIME、DATETIME、TIMESTAMP”。本文138张图带你入门MySQL。日期类型的区别我们已经介绍过了，这里不再赘述。下面主要介绍TIMESTAMP的选择与时区有关，更能体现当前时间。如果记录的日期需要不同时区的人使用，最好使用TIMESTAMP。DATE用于表示年、月和日。如果实际应用值需要保存年月日，可以使用DATE。TIME用于表示小时、分钟和秒。如果实际应用值需要保存时、分、秒，可以使用TIME。YEAR用于表示年份，YEAR有2位（最好用4位）4位格式年份。默认值为4位。如果实际应用只保存年份，YEAR类型保存1个字节就完全没问题。既可以节省存储空间，又可以提高表的运行效率。MySQL字符集让我们来认识一下MySQL字符集。简单地说，字符集就是一组文本符号、编码和比较规则。1960年，美国标准化组织ANSI发布了第一个计算机字符集，这就是著名的ASCII（AmericanStandardCodeforInformationInterchange）。自ASCII编码以来，各个国家和国际组织都研究了一套自己的字符集，如ISO-8859-1、GBK等，但各个国家都使用自己的字符集，这给可移植性带来了很大的困难。因此，为了统一字符编码，国际标准化组织（ISO）规定了一个统一的字符标准——Unicode编码，它容纳了几乎所有的字符编码。下面是一些常用的字符编码字符集是负长编码ASCII是单字节7位编码ISO-8859-1是单字节8位编码GBK是双字节编码UTF-8没有1-4字节编码UTF-16没有2字节或4字节编码UTF-32是4字节编码对于数据库来说，字符集很重要，因为数据库中存储的数据大部分都是各种字符，字符集是对数据库非常重要存储、性能、系统迁移都非常重要。MySQL支持多种字符集，可以使用showcharacterset；查看所有可用字符集或使用selectcharacter_set_name,default_collat??e_name,description,maxlenfrominformation_schema.character_sets;查看。使用information_schema.character_set查看字符集和排序规则。
