前言

谁说MySQL查问千万级别的数据很拉跨？我明天就要好好的和你拉拉家常，畅谈到深夜，一起过元旦！这篇文章也是年前的最初一篇，心愿能带给大家些许播种，人不知;鬼不觉查找文档和参考实体书籍就写了这么多，本人都感觉到意外。不禁感叹到，晓得的越多，才晓得不晓得的更多。

开发人员或者是DBA都应该关注MySQL应用的存储引擎，抉择适合存储引擎对你的利用性能晋升是显著的。在浏览到本文的时候，必定是有肯定的MySQL或者其它数据库根底的，不然有些中央看着会很吃力。重点中央，我都进行了加粗解决，这样更容易获取要害知识点。

对于存储引擎，一篇文章也不可能八面玲珑，对集体感觉比拟重要、于工作无益的方面进行论述。如果真的去深挖，预计得一本书的篇幅。顺带还介绍一些数据类型抉择、字符集设置、索引的应用；视图、存储过程、函数以及触发器啊等等会在下一篇博文进行具体的形容。但本文不会做太具体的叙述。本篇文章以存储引擎的抉择为外围，如果有呈现瑕疵的中央，心愿您能留下贵重的倡议。

明天发现了一个神奇的参数：-site:xxxx.net

注释

一、存储引擎的抉择（表类型）

1、存储引擎的介绍

与到少数关系型数据库的区别在于MySQL有一个存储引擎的概念，针对不同的存储需要能够抉择最合适的存储引擎。MySQL中的插件式的存储引擎是其一大特色，用户能够依据利用的需要抉择如何存储、是否索引，是否应用事务。嘿嘿，你也能够依据业务环境去适配最适宜本人业务的存储引擎。

Oracle从中嗅到了商机，收买了MySQL，从此有了企业版（商业反对）。社区版仍旧能够收费下载。另一大魅力也是因为开源，社区高度沉闷，人人都可奉献。接下来介绍几种应用比拟多的存储引擎，存储引擎并无优劣之分，有的只是谁更适宜对应的生产业务环境。

MySQL5.0中反对的存储引擎有FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE、NDB Cluster、BDB、EXAMPLE、InnoDB（MySQL5.5以及MariaDB10.2之后的默认存储引擎）、PERFORMANCE_SCHEMA（非常规存储数据引擎）。上面给出MySQL与MariaDB反对的存储器引擎的比照，能够看出MariaDB新增了Aria引擎：

查看存储引擎

通过MySQL登录自带的字符界面输出show engines\G;或者应用反对MySQL查问的工具SQLyog、phpMyAdmin、MySQL workbench等查问反对的引擎，这里只展现局部哟：

[test@cnwangk ~]$ mysql -uroot -pEnter password: mysql> show engines\G;*************************** 2. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 3. row ***************************      Engine: MyISAM     Support: YES     Comment: MyISAM storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 6. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 8. row ***************************      Engine: InnoDB     Support: DEFAULT     Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES          XA: YES  Savepoints: YES9 rows in set (0.00 sec)

作用形容：

  • Engine：引擎名称（形容）；
  • Support：以后版本数据库是否反对该存储引擎，YES：反对、NO：不反对；Supports transactions, row-level locking, and foreign keys，集体字面上翻译这段话：反对事务、行级别锁和外键；
  • Comment：对该存储引擎的详情形容，比方形容该引擎否反对事务和外键；
  • Transactions：对该存储引擎是否反对事务的形容，YES：反对、NO：不反对；
  • XA：是否满足XA标准。XA标准是凋谢群组对于分布式事务处理(DTP)的标准。YES：反对、NO：不反对；
  • Savepoints：字面意思是保留点，对事物管制是否反对，YES：反对、NO：不反对。

小声哔哔，如果你能浏览明确官网的一些英文文档，这将有助于你对MySQL存储引擎的进一步了解，养成浏览源码或者文档的能力。

顺带的提一下MySQL的妹妹MariaDB。在MySQL的复刻版本MariaDB中10.2之前应用的自带的新引擎Aria，在MariaDB10.2之后应用的默认存储引擎也是InnoDB，足以看出InnoDB存储引擎的优良之处。MariaDB的API和协定兼容MySQL，另外又增加了一些性能，以反对本地的非阻塞操作和进度报告。这意味着，所有应用MySQL的连接器、程序库和应用程序也将能够在MariaDB下工作。在此基础上，因为放心甲骨文MySQL的一个更加关闭的软件我的项目，Fedora等Linux发行版曾经在最新版本中以MariaDB取代MySQL，维基媒体基金会的服务器同样也应用MariaDB取代了MySQL。

次要须要理解的几种存储引擎：

  • MyISAM
  • InnoDB
  • MEMORY
  • MERGE

上面将着重介绍我最近看书意识的几种罕用的存储引擎，比照各个存储引擎之间的区别，帮忙咱们了解不同存储引擎的应用形式。更多详情能够参考MySQL的官网文档。

2、局部存储引擎的个性

存储引擎/反对个性存储限度事务平安锁机制B树索引哈希索引全文索引集群索引数据缓存索引缓存数据可压缩空间应用内存应用批量插入速度外键反对
MyISAM有表锁反对反对反对反对低低高
InnoDB64TB反对行锁反对反对（5.6）反对反对反对高高低反对
MEMORY有表锁反对反对反对反对N/A中等高
MERGE没有表锁反对反对低低高
NDB有行锁反对反对反对低高高

InnoDB存储引擎在MySQL5.6版本开始反对全文索引。在MySQL5.7推出了虚构列，MySQL8.0新个性退出了函数索引反对。

2.1、MyISAM存储引擎

MyISAM是MySQL5.5之前默认的存储引擎。MyISAM不反对事务、不反对外键。劣势在于访问速度快，对事务完整性没有特殊要求或者以select和insert为主的利用基本上能够应用MyISAM作为存储引擎创立表。咱们先弄个例子进去演示，当时筹备了一张数据千万级别的表，看看这个存储引擎的个性：

我曾经创立好了数据库为test，在test中别离创立了两张表test和tolove。test表在创立的时候指定默认存储引擎为MyISAM，tolove表指定存储引擎为InnoDB。
应用MyISAM存储引擎创立的表tolove，查问存储有1kw数据的表tolove。

tips：你能够应用use test，切换到test数据库，就不必像我这样查问tolove表去指定test数据库了哟！

MySQL [(none)]> select count(*) from test.tolove;+----------+| count(*) |+----------+| 10000000 |+----------+1 row in set (0.000 sec)

再看演示应用InnoDB存储引擎创立的表test，同样为了演示，当时随机生成了1kw条数据。

MySQL [(none)]> select count(*) from test.test;+----------+| count(*) |+----------+| 10000000 |+----------+1 row in set (3.080 sec)

进行比照同样存储1kw条数据的表，应用MyISAM作为存储引擎查问速度堪称光速1 row in set (0.000 sec)，应用InnoDB存储引擎查问速度稍逊一筹1 row in set (3.080 sec)。

MyISAM在磁盘中存储的文件：

每个MyISAM在磁盘上存储成3个文件，其文件名和表名都雷同，扩展名别离是：

  • .frm：存储表定义；
  • .MYD：MYData，存储数据；
  • .MYI：MYindex，存储索引。

数据文件和索引文件能够寄存在不同的目录，均匀散布IO，取得更快的速度，晋升性能。须要指定索引文件和数据文件存储的门路，创立表时通过DATA DIRECTORY和INDEX DIRECTORY参数指定，表明不同MyISAM表的索引文件和数据文件能够寄存在不同的门路下。当然，须要给予该门路的拜访权限。

MyISAM损坏解决形式 ：

MyISAM类型的表可能会损坏，起因多种多样。损坏后的表有可能不能被拜访，会提醒须要修复或者拜访后提醒返回谬误后果。MyISAM类型的表，能够通过提供的修复工具执行CHECK TABLE语句查看MyISAM表的衰弱水平，应用REPAIR TABLE语句修复一个损坏的表。表损坏可能会导致数据库异样重新启动，须要尽快修复并确定起因好做应答策略。

应用MyISAM存储引擎的表反对3种不同的存储格局，如下：

  • 动态表，固定长度；
  • 动静表
  • 压缩表

动态表是MyISAM存储引擎的默认存储格局，字段长度是定长，记录都是固定长度。劣势在于存储迅速、容易缓存、呈现故障易复原；毛病是绝对耗存储空间。须要留神的是：如需保留内容前面的空格，默认返回后果会去掉前面的空格。

动静表蕴含变长字段，记录不是固定长度，存储劣势：占用空间绝对较小、但频繁删除和更新记录会产生碎片。这时，须要定期执行optimize table语句或者myisamchk -r命令来改善性能，呈现故障复原绝对较难。

压缩表由mysiampack工具创立，占用磁盘空间很小。因为每个记录是被独自压缩，所以拜访开始十分小。

梳理一下MyISAM存储引擎的要点，如下图1-2-2-1所示：

顺带安利一波，前段时间发现WPS也可能制作精美的思维导图，并且反对一键导入到doc文件中。普通用户最多可存储150个文件。之前也用过XMind、processon、gitmind等等，当初应用WPS更不便了。

2.2、InnoDB存储引擎

长处与毛病：InnoDB存储引擎提供了具备提交（commit）、回滚（rollback）和解体复原能力的事务平安。但比照MyISAM存储引擎，InnoDB写的解决效率绝对差一些，并且会占用更多的磁盘空间保留数据和索引。下图是我存储了1kw条数据的表，并且应用的是InnoDB存储引擎。student01表同样应用了InnoDB存储引擎，存储数据为100w条。从下图能够看出存储数据索引在.ibd文件中、表构造则存在.frm文件中。

2.2.1、主动增长列

InnoDB表的主动增长列能够手工插入，但插入的值为空或者0，则理论插入的将是主动主动增长后的值。

原本想持续应用bols那张表作为演示的，思来想去还是正经一点。为了演示，我又新增了一张表为autoincre_test，示意id设置为主键且自增长，存储引擎抉择InnoDB。而后插入了3条数据进行演示。查问以后线程最初插入数据的记录应用值：

MySQL [test]> create table autoincre_test(id int not null auto_increment,name varchar(16),primary key(id))engine=innodb;Query OK, 0 rows affected (0.018 sec)MySQL [test]> insert into autoincre_test values(1,'1'),(0,'2'),(null,'3');Query OK, 3 rows affected (0.007 sec)Records: 3  Duplicates: 0  Warnings: 0MySQL [test]> select * from autoincre_test;+----+------+| id | name |+----+------+|  1 | 1    ||  2 | 2    ||  3 | 3    |+----+------+3 rows in set (0.000 sec)select last_insert_id();MySQL [test]> select last_insert_id();+------------------+| last_insert_id() |+------------------+|                2 |+------------------+1 row in set (0.000 sec)

tips：能够通过alter table table_name=n;语句强制设置主动增长列的初始值，默认从1开始，但该强制的默认值是保留在内存中的，如果应用该值之前数据库重新启动，强制默认值则会失落，就须要从新设置，毕竟应用内存没有加载到磁盘中。

通过下面的演示，你会发现插入记录是0或者空时，理论插入的将是主动增长后的值。通过last_insert_id()函数能够查问以后线程最初插入数据的记录应用值。如果一次插入多条记录，则返回的是第一条记录应用的主动增长值，这里就不演示插入多条数据了。记住一点，能够应用last_insert_id()去查问id记录值。

对于InnoDB表，主动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列。但对于MyISAM表，主动增长列能够是组合索引的其它列。这样插入记录后，主动增长列是依照组合索引的后面几列排序后递增的。你能够创立一张表指定MyISAM存储引擎，而后将两列字段组合索引进行测试验证。

2.2.2、外键束缚

在MySQL中，目前反对外键束缚的存储引擎只有InnoDB。创立外键的时候，要求父表必须有对应的索引。子表创立外键的时候，也会主动创立对应的索引。上面将通过实例进行解说。能够从MySQL官网下载示例数据库world和sakila进行参考。

  • city表，FOREIGN KEY (CountryCode) REFERENCES country (Code)
  • country表
  • countrylanguage表，FOREIGN KEY (CountryCode) REFERENCES country (Code)

通过MySQL workbench或者Navicat逆向生成物理模型进行参考，更加直观。插一句，在MySQL的官网同样有一个sakila数据库是对于演员电影的，也提供了sakila的ERR物理模型图，这句话做了超链接，能够间接拜访。给出我之前逆向生成的world数据库的物理模型：

在创立索引时，能够指定在删除、更新父表时，对子表进行的相应操作蕴含：

  • restrict
  • cascade
  • set null和no action

其中restrict和no action雷同，restrict限度在子表有关联记录的状况下父表无奈更新；cascade示意在父表更新或删除的时候，级联更新或者删除子表对应记录；set null示意在父表更新或删除的时候，子表的对应字段被set null。抉择cascade以及set null时须要审慎操作，有可能导致数据失落。

在导入多个表的数据时，如果疏忽表之前的导入程序，能够临时敞开外键查看；同样执行load data和alter table时也能够临时敞开外键查看放慢解决的速度，晋升效率。敞开外键查看的命令为：

set foreign_key_checks=0;

执行完导入数据或者批改表的操作后，通过开启外键查看命令改回来：

set foreign_key_checks=1;

对于InnoDB类型的表，外键信息能够通过show create table或者show table status查看。比方查找world数据库中的city表：

MySQL [sakila]> show table status like 'city'\G

对于外键束缚就提这么多，没有演示创立以及删除，因为贴太多的SQL语句太占篇幅了。能够到MySQL官网下载world和sakila数据库进行测试。

2.2.3、存储形式

InnoDB存储表和索引有两种形式：

  • 共享表空间存储
  • 多表空间存储

应用共享表空间存储，这种形式创立的表的表构造保留在.frm文件中，数据和索引保留在innodb_data_home_dir和innodb_data_file_path定义的表空间中，能够是多个文件。在结尾介绍InnoDB存储引擎时也提到过文件存储地位。

应用多表空间存储，这种形式创立的表的表构造依然保留在.frm文件中，但每个表的数据和索引独自保留在.ibd文件中。如果是个分区表，则每个分区对应独自的.ibd文件，文件名为表名+分区名。能够在创立分区的时候指定每个分区的数据文件地位，以此来均匀散布磁盘的IO，达到缓解磁盘压力的目标。如下是在Windows下应用InnoDB存储了海量数据的文件：

应用多表空间存储须要设置参数innodb_file_per_table，重启数据库服务器能力失效哟。多表空间的参数失效后，只对新建的表失效。多表空间的数据文件无大小限度，无需设置初始大小，也不需设置文件的最大限度与扩大大小等参数。应用多表空间存储劣势在于不便单表备份和复原操作。尽管不能间接复制.frm和.ibd文件达到目标，但能够应用如下命令操作：

alter table table_name discard tablespace;alter table table_name import tablespace;

将备份复原到数据库中，单表备份，只能复原到原来所在的数据库中，无奈复原到其它数据库中。如过须要将单表复原至其它指标数据库中，则须要通过mysqldump和mysqlimport来实现。

留神：即使多表存储更有劣势，然而共享表存储空间仍旧是必须的，InnoDB将外部数据字典和在线重做日志存在这个文件中。

梳理一下InnoDB存储引擎的要点，如下图1-2-2-2所示：

对于InnoDB存储引擎就介绍到此处了，更多详情能够参考MySQL的官网文档。是不是发现了我只在MyISAM和InnoDB存储引擎做了总结的思维导图。没错，只做了这两个，因为这俩最罕用。至于为啥是粉色背景，因为老夫有一颗少女心！

2.3、MEMORY存储引擎

MEMORY存储引擎应用存在与内存中的内容来创立表。每个MEMORY表只对应一个磁盘文件，格局是.frm。MEMORY类型的表访问速度极快，存在内存中当然快。这就是Redis为什么这么快？不仅小？还能长久？咱回到正题，MEMORY存在内存中并默认应用hash索引，一旦服务敞开，表中数据会失落。创立一张名为GIRLS的表指定存储引擎为MEMORY，留神了在UNIX和Linux操作系统下，是对字段和表名大小是写敏感的，关键字不影响。

CREATE TABLE GIRLS (  ID int NOT NULL,GIRE_NAME varchar(64) NOT NULL,GIRL_AGE varchar(10) NOT NULL,  CUP_SIZE varchar(2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

还记得在介绍存储引擎做的那会张表格吗，有介绍到MEMORY反对B TREE索引。尽管MEMORY默认应用的索引是hash索引，然而你能够手动指定索引类型。例如默认手动指定应用关键字USING HASH：

-- 创立索引指定索引类型为hash。create index mem_hash USING HASH on GIRLS(ID);-- 查问索引类型，简化了一下，只展现了局部参数。mysql> SHOW TABLE STATUS LIKE 'GIRLS'\G*************************** 1. row ***************************           Name: GIRLS         Engine: MEMORY        Version: 10     Row_format: Fixed1 row in set (0.00 sec)

尽管MEMORY容易失落数据，然而在启动MySQL服务的时候，咱们能够应用--init-file选项，将insert into ... select或者load data infile这样的语句寄存在这个指定的文件中，就能够在服务启动时从持久稳固的数据源装载表。

服务器须要提供足够的内存来维持所有在同一时间应用的MEMORY表，当不在须要MEMORY表内容之时，开释被MEMORY表应用的内存。认真思考一下，如果内存用了不开释那将有多可怕。此时能够执行delete form 或truncate table亦或残缺地删除整个表，应用drop table。这里提一点，在Oracle中也同样反对truncate，应用truncate的益处在于不必再去思考回滚（rollback），效率更高。应用truncate须要在命令模式下应用，其它客户端工具可能不反对。

每个MEMORY表中寄存的数据量大小，受max_heap_table_size零碎变量束缚，初始值为16MB，能够依据需要调整。通过max_rows能够指定表的最大行数。

MEMORY存储引擎最大特色是快，次要用于内容变动不频繁的代码表，或者是为了做统计提供的两头表，效率更高。应用MEMORY时需谨慎，万一忘了这厮重启数据就没了就难堪了。所以在应用时，思考好重启服务器后如何获得数据。

对于MEMORY存储引擎就介绍到这里，大部分都是些理论知识，更多的须要本人去实际测试。

2.4、MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须后果完全相同，MERGE表自身没有数据，对MERGE类型的表能够进行查问、更新、删除操作，实际上是对外部的MyISAM表进行操作的。对于MERGE类型表的插入操作，通过insert_method子句定义插入的，能够有3个不同的值，应用first或last插入操作对应开始与最初一个表上。如果不定义这个子句，或者定义为NO，示意不能对MERGE表进行操作。

对MERGE表进行DROP操作，只是对MERGE的定义进行删除，对外部表没有任何影响。MERGE表上保留两个文件，文件名以表的名字开始，别离为：

  • .frm文件存储表定义；
  • .mrg文件蕴含组合表的信息，蕴含表组成、插入数据根据。

能够通过批改.mrg文件来批改表，但批改后须要应用flush tables刷新。测试能够先创立两张存储引擎为MyISAM的表，再建一张存储引擎为MERGE存储引擎的表。如下所示创立demo为总表指定引擎为MERGE，demo01和demo02为分表：

CREATE TABLE `merge_demo` (  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=MERGE UNION=(merge_demo01,merge_demo02)  INSERT_METHOD=LAST DEFAULT CHARSET=utf8 COLLATE=utf8_bin CREATE TABLE `merge_demo01` (  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin  CREATE TABLE `merge_demo02` (  `ID` INT(11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR(16) COLLATE utf8_bin NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

通过插入数据验证MERGE的确是一个MyISAM的组合，就是这么神奇。如下所示，只对demo01和demo02进行插入：

INSERT INTO study.`merge_demo01` VALUES(1,'demo01');INSERT INTO study.`merge_demo02` VALUES(1,'demo02');mysql [study]> select * from merge_demo;+----+--------+| ID | NAME   |+----+--------+|  1 | demo01 ||  1 | demo02 |+----+--------+2 rows in set (0.000 sec)

插入完数据，别离查看demo01和demo02各只有一条数据，总表能够看到俩分表的全副数据。要害是指定了insert_method=last。MERGE表和分区表的区别，MERGE并不能智能地将记录插入到对应表中，而分区表能够做到。通常咱们应用MERGE表来通明的对多个表进行查问和更新操作。能够本人在上面测试总表插入数据，看分表的状况，我这里就不贴代码了。

对于MySQL自带的几款罕用存储引擎就介绍到此，感兴趣的能够私下测试验证，更多参考请到官网获取API或者DOC文档。

除了MySQL自带的一些存储引擎之外，常见优良的第三方存储引擎有TokuDB，一款开源的高性能存储引擎，实用于MySQL和MariaDB。更多详情能够去TokuDB官网理解哟。

2.5、批改表的存储引擎

创立新表时，如果不指定存储引擎，零碎会应用默认存储引擎。在MySQL5.5之前默认的存储引擎为MyISAM，在MySQL5.5之后默认的存储引擎为InnoDB。如果想批改默认存储引擎，能够通过配置文件指定default-table-type的参数。对于存储引擎的查看，在下面介绍存储引擎的时候曾经有阐明了。

办法一：建表即指定以后表的存储引擎

在创立tolove表的时候就指定存储引擎，例如指定存储引擎为MyISAM，默认编码为utf8：

-- Create TableCREATE TABLE `tolove` (  `ID` int(11) NOT NULL AUTO_INCREMENT,`GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,  `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL,`CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

测试生成的数据量比拟大，随机生成了1千万条数据。查问（select）业务绝对较多，在建表的时候就指定默认存储引擎MyISAM，统计（count）的效率很高。以我的渣渣电脑，应用INNODB存储引擎，统计一次须要2~3秒左右。在下面讲到MYISAM的时候，曾经将查问工夫进行过比照。

办法二：应用alter table批改以后表的存储引擎

批改创立的tolove表为MYISAM引擎进行测试。

-- 批改创立的tolove表为MYISAM引擎进行测试ALTER TABLE test.`tolove` ENGINE=MYISAM;

批改test表的存储引擎为INNODB进行测试。

-- 批改表的存储引擎为INNODB进行测试ALTER TABLE test.`test` ENGINE=INNODB;

SHOW CREATE TABLE查问表的存储引擎，别离查问test表和tolove表，在讲存储引擎为MyISAM的时候，有演示过哟！

SHOW CREATE TABLE test.`test`;SHOW CREATE TABLE test.`tolove`;

如果在工具中无奈看全，能够导出成xml、csv、html等查问，以下是我查问出本人创立表时设置的存储引擎为InnoDB：

-- 显示出我创立的test表的SQL语句存储引擎为InnoDBCREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `STU_NAME` varchar(50) NOT NULL, `SCORE` int(11) NOT NULL, `CREATETIME` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8-- 显示出我创立的tolove表的SQL语句，存储引擎为MyISAMCREATE TABLE `tolove` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `GIRL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL, `GIRL_AGE` varchar(64) COLLATE utf8_bin DEFAULT NULL, `CUP_SIZE` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

存储引擎的批改就介绍这么多，看到我的自增长列（AUTO_INCREMENT）ID到了20000001，之前随机生成过一次1kw条数据哟！有一部分解释阐明我写在了代码块中，看起来更加难受。

3、存储引擎的抉择

在抉择适合的存储引擎时，应依据利用特点抉择适合的存储引擎。对于简单的利用零碎，你能够抉择多种存储引擎满足不同的利用场景需要。如何抉择适合的存储引擎呢？存储引擎的抉择真的很重要吗？

的确应该好好思考，在并不简单的利用场景下，可能MyISAM存储引擎就能满足日常开销。或者在另外一种场景之下InnoDB才是最佳抉择，综合性能更好，满足更多需要。

MyISAM是MySQL的默认的插件式存储引擎，是MySQL在5.5之前的默认存储引擎。如果利用以读和插入操作居多，只有很少的更新和删除操作，对事务完整性、并发性没有很高的需要，此时首选是MyISAM存储引擎。在web和数据仓库最罕用的存储引擎之一。

InnoDB用于事务处理应用程序，并且反对外键。是MySQL在5.5之后的默认存储引擎，同样也是MariaDB在10.2之后的默认存储引擎，足见InnoDB的优良之处。如果利用对事务完整性有较高的要求，在并发状况下要求数据高度一致性。数据除了插入和查问以外，还包含很多的更新和删除操作，那么InnoDB应该是比拟适合的存储引擎抉择。InnoDB除了无效地升高因为删除和更新导致的锁定，还能够确保事务的残缺提交（commit）、回滚（rollback）。对相似计费零碎或者财务零碎等对数据准确性要求比拟高的零碎，InnoDB也是适合的抉择。插点题外话，自己在工作中应用Oracle数据库也有一段时间，Oracle的事务的确很弱小，解决大数据压力很强。

MEMORY存储引擎将所有的数据存在RAM中，在须要疾速定位记录和其它相似数据的环境下，可提供极快的拜访。MEMORY的缺点在于对表的大小有限度，太大的表无奈缓存在内存中，其次是要确保表的数据能够复原，数据库异样重启后表的数据是可复原的。MEMORY表通常用于更新不太频繁的小表，疾速定位拜访后果。

MERGE用于将一组等同的MyISAM存储引擎的表以逻辑形式组合在一起，并作为一个对象利用它们。MERGE表的长处在于能够冲破对单个MyISAM表大小的限度，并通过将不同的表散布在多个磁盘上，改善MERGE表的拜访效率。对数据局仓库等VLDB环境很适宜。

最初，对于存储引擎的抉择都是依据他人理论教训去总结的。并不是肯定符合你的利用场景，最终须要用户对各自利用进行测试，通过测试来获取最合适的后果。就像我开始列举的示例，数据量很宏大，对查问和插入业务比拟频繁，我就开始对MyISAM存储引擎进行测试，的确比拟合乎我的利用场景。

对于存储引擎的抉择，总结简化如下图1-3：

4、表的优化（碎片整顿）

在开始介绍存MyISAM和InnoDB储引擎的时候，我也展现过存储大量数据所占的磁盘空间。应用OPTIMIZE TABLE来优化test数据库下的test表，优化之前，这张表占据磁盘空间大略在824M；通过优化之后，有显著的改善，零碎回收了没有利用的空间，test表所耗磁盘空间显著降落，优化之后只有456M。这里就不贴磁盘所占空间的截图了。

OPTIMIZE TABLE test.`test`;

优化之后，统计（count）数据效率也有所晋升，大略在2.5sec左右：

mysql [test]> select count(*) from test; -- 应用的是innodb存储引擎测试+----------+| count(*) |+----------+| 10000000 |+----------+1 row in set (2.468 sec)

优化之前，统计数据大略在3.080 sec。通过比照，效率晋升是可观的。

你也能够应用explain执行计划对查问语句进行优化。对于MySQL优化方面的常识，并不是本文的重点，就不做过多形容。

二、索引设计与应用

1、索引简介

在波及到MySQL的面试当中，会提到最左前缀索引，都被玩成梗了。

MySQL所有列类型都能够被索引，对相干列正当的应用索引是进步查问（select）操作性能的最佳办法。依据引擎能够定义每张表的最大索引数和最大索引长度，MySQL的每种存储引擎（MyISAM、InnoDB等等）对每张表至多反对16个索引，总索引长度至多为256字节。大多数存储引擎有更高的限度。

MyISAM和InnoDB存储引擎默认创立的表都是BTREE索引。在MySQL8.0之前是不只反对函数索引的，MySQL5.7推出了虚构列性能，在MySQL8.0开始反对函数索引，也是8.0版本的新个性之一。

MySQL反对前缀索引，对索引字段的前N个字符创立索引，前缀索引长度和存储引擎无关。有很多人常常会问到，MySQL反对全文索引吗？我的答复是：反对。MySQL5.6之前MyISAM存储引擎反对全文索引（FULLTEXT），5.6之后InnoDB开始反对全文索引。

为test表创立10个字节的前缀索引，创立索引的语法如下：

CREATE INDEX girl_name ON table_name(test(10));

同样能够应用alter table语句去新增索引，给girl表的字段girl_name新增一个索引：

ALTER TABLE test.`girl` ADD INDEX idx_girlname(girl_name);

对于应用索引的验证能够应用explain执行打算去判断。对于索引的简述就介绍这么多，更多基础知识能够参考官网文档或者权威书籍。

2、设计索引准则

索引的设计能够遵循一些已有的准则，创立索引的时候请尽量思考合乎这些准则。有助于晋升索引的应用效率。

搜寻的索引列，不肯定是所要抉择的列。最合适的索引列，往往是呈现在where子句中的列，或者是连接子句中指定的列，而不是呈现在select后抉择列表中的列。

应用惟一索引。思考某列中值的散布，索引列的基数越大，索引成果越好。

应用短索引。如果对字符串列进行索引，应指定一个前缀长度。比方char(100)，思考一下，反复度的问题。是全副索引来的快，还是对局部字符进行索引更优？

利用最左前缀。在创立一个N列的索引时，实际上是创立了MySQL可利用的N个索引。多列索引能够起几个索引的作用，利用索引中最右边的列表来匹配行。这样的列集称为最左前缀。都快被波及到MySQL的面试玩成梗了，哈哈。

留神不要适度应用索引。不要认为应用索引好处多多，就在所有的列上全副应用索引，适度应用索引反而会事与愿违。每个额定的索引会占用磁盘空间，对磁盘写操作性能造成损耗。在重构的时候，索引也得更新，造成不必要的工夫节约。

InnoDB存储引擎的表。对于应用InnoDB存储引擎的表，记录默认按肯定的程序保留。有如下几种状况：

  • 如果有明确定义的主键，则遵循主键程序保留；
  • 在没有主键，但有惟一索引的状况下，会遵循惟一索引程序保留；
  • 既没有主键又没有惟一索引，表中会主动生成一个外部列，并遵循这个列的程序保留。

以上就是对索引设计准则的简略介绍。

3、B-TREE与HASH索引

应用这些索引时，应该思考索引是否以后应用条件下失效！在应用MEMORY存储引擎的表中能够抉择应用HASH索引或者B-TREE索引，两种不同的索引有其各自实用的范畴。

HASH索引。只用于这类关系操作符：=、<=>的操作比拟，优化器不能应用HASH索引来减速order by操作。MySQL不能确定在两个值之间大概有多少行。

B-TREE索引。对于B-TREE索引，应用>、<、>=、<=、BETWEEN、!=或者<>、亦或是应用like 'condition'。其中'condition'不以通配符开始的操作符时，都能够应用相干列上的索引。

对于索引就介绍到这里。正当的应用索引将有助于晋升效率，但并不是应用的索引越多越好。

三、数据类型抉择

  • 字符串类型char与varchar
  • 浮点数和定点数
  • 日期类型

工作中，集体应用教训。Oracle外面应用BLOB存储大字段比拟频繁，TEXT绝对少见，应用VARCHAR2类型比拟多。但在MySQL中是不反对VARCHAR2类型的。

1、CHAR与VARCHAR

char和varchar类型相似，用于存储字符串，但它们保留和检索的形式不同。char类型属于固定长度（定长）类型的字符串，varchar属于可变长度的字符串类型。在MySQL的严格模式中，应用的char和varchar，超过列长度的值不会被保留，并且呈现谬误提醒。

char优缺点。char是固定长度，处理速度比varchar要快，但毛病是节约存储空间，没有varchar那么灵便。varchar。随着MySQL的一直降级，varchar类型也在一直优化，性能也在晋升，被用于更多的利用中。

MyISAM存储引擎：倡议应用固定长度的数据列代替可变长度的数据列。

InnoDB存储引擎：倡议应用VARCHAR类型。

MEMORY存储引擎：应用固定长度数据类型存储。

2、TEXT与BLOB

个别状况，存储大量的字符串时，会抉择char和varchar类型。而在保留较大文本时，通常抉择TEXT或者BLOB大字段，二者之间的区别在于BLOB能存二进制数据，比方：照片，TEXT类型只能存字符数据。这也是为什么我在开始的时候提及到集体工作中见到BLOB类型绝对较多。TEXT和BLOB还包含不同类型：

  • TEXT、LONGTEXT、MEDIUMINT、MEDIUMTEXT、TINYTEXT；
  • BLOB、LONGBLOB、MEDIUMBLOB、TINYBLOB。

区别在于存储文本长度和字节不同。

须要留神的点：

  • BLOB和TEXT值会引起一些性能问题，尤其是执行大量删除操作时；
  • 能够应用合成索引进步大字段的查问性能；
  • 在不必要的时候防止检索大字段；
  • 将BLOB和TEXT拆散到不同的表中。

3、浮点数与定点数

浮点类型个别用于示意含有小数局部的值。列举一些示例：

  • double类型：用于浮点数（双精度）；
  • decimal类型：MySQL中示意定点数；
  • float类型：用于浮点数（单精度）。

学过Java语言的同学，对这些浮点类型并不生疏吧。

留神点：浮点数存在误差问题，对精度比拟敏感的数据，防止对浮点类型做比拟。

4、日期类型

谈到日期类型，又让我想起了7年前学Java语言的时候，会写一个工具类（Utils.java），将罕用的解决日期的办法写进去而后调用。常常用到的一个办法（SimpleDateFormat），对工夫戳进行转换格式化。

MySQL中罕用的日期类型有：

  • DATE
  • DATETIME
  • TIME
  • TIMESTAMP

如果须要记录年月日时分秒，并且记录的年份比拟长远，最好用DATETIME，而不要应用TIMESTAMP工夫戳。TIMESTAMP示意的范畴比DATETIME短得多。

四、字符集（字符编码）设置

从实质上来说，计算机只能是被二进制代码（010101）。因而，不论是计算机程序还是解决的数据，最终都会转换成二进制代码，计算机能力辨认。为了让计算机不仅能做科学计算，也能解决文字信息，于是计算机字符集诞生了。

字符编码（英语：Character encoding）、字集码是把字符集中的字符编码为指定汇合中某一对象)（例如：比特模式、自然数序列、8位组或者电脉冲），以便文本在计算机中存储和通过通信网络的传递。常见的例子包含将拉丁字母表编码成摩斯电码和ASCII。其中，ASCII将字母、数字和其它符号编号，并用7比特的二进制来示意这个整数。通常会额定应用一个裁减的比特，以便于以1个字节的形式存储。

在计算机技术倒退的晚期，如ASCII（1963年）和EBCDIC（1964年）这样的字符集逐步成为规范。但这些字符集的局限很快就变得显著，于是人们开发了许多办法来扩大它们。对于反对包含东亚CJK字符家族在内的写作零碎的要求能反对更大量的字符，并且须要一种零碎而不是长期的办法实现这些字符的编码。

援用自维基百科对字符编码的介绍。

1、Unicode

Unicode是什么？是对立编码，是计算机科学畛域的业界规范。从最后的的1.0.0到目前最新的14.0版本，对应ISO/IEC 10646-N:xxxx。说一下UTF-8、UTF-16、UTF-16LE、UTF-32BE、UTF-32LE等等大家应该很相熟了。

2、常见字符集

常见的字符集：

  • UTF-8：泛用性最宽泛；
  • GBK：对中文反对十分敌对，在GB2312根底上进行了裁减；
  • GB2312：对中文字符集反对，；
  • GB18030：反对中文字符集，解决GBK强制力不够的问题。

3、MySQL反对的字符集

通过show character set;命令能够查看MySQL反对的字符集。我只展现局部：

mysql [test]> show character set;| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 || gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 || utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 || utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 || utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 || utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

或者你还能够应用DESC information_schema.CHARACTER_SETS查看所有字符集和字符集默认的校对规定。

查看相干字符集校对规定，能够应用SHOW COLLATION配合 LIKE含糊搜寻gbk字符集。

SHOW COLLATION LIKE 'gbk%';

MySQL字符集设置：默认能够过配置文件设置character-set-server参数。

  • Linux发行版中装置个别在my.cnf中配置；
  • Windows下在my.ini文件中配置
[mysqld]character-set-server=utf-8character-set-server=gbk

额定再提一点，判断字符集所占字节，能够应用函数LENGTH()：

SELECT LENGTH('中');

如果应用的是UTF-8编码，默认汉字是占用3个字节，应用GBK则占用2个字节。字符编码就介绍到这里。

五、MySQL示例数据库sakila

视图、存储过程、函数、触发器。这里给出我本人随机生成海量数据用到的函数和存储过程。

1、函数

创立函数，应用DELIMITER申明，应用CREATE FUNCTION创立函数，tolove表的创立在介绍存储引擎过程中曾经有展现过。

/** 创立函数 生成学号 **/DELIMITER $CREATE FUNCTION rand_number() RETURNS INTBEGIN    DECLARE i INT DEFAULT 0;    SET i= FLOOR(1+RAND()*100);    RETURN i;END $DELIMITER $

创立函数：用于生成姓名随机字符串

/** 创立函数 生成姓名随机字符串 **/DELIMITER $CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255)BEGIN    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';    DECLARE return_str VARCHAR(255) DEFAULT '';    DECLARE i INT DEFAULT 0;    WHILE i < n DO    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));    SET i = i+1;    END WHILE;    RETURN return_str;    END $DELIMITER $

2、存储过程

创立存储过程，应用CREATE PROCEDURE创立：

/** 创立存储过程 **/DELIMITER $CREATE PROCEDURE insert_tolove(IN max_num INT(10))BEGIN    DECLARE i INT DEFAULT 0;    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;    START TRANSACTION;    WHILE i< max_num DO    INSERT INTO test.`tolove`(ID,GIRL_NAME,GIRL_AGE,CUP_SIZE) VALUES(NULL,rand_name(5),rand_number(),NULL);    SET i = i + 1;    END WHILE;COMMIT;END $DELIMITER $

应用CALL调用存储过程，随机生成百万数据：

/** 调用存储过程 **/CALL insert_tolove(100*10000);

删除函数或者存储过程，应用DROP关键字

-- 删除函数rand_nameDROP FUNCTION rand_name;-- 删除存储过程insert_toloveDROP PROCEDURE insert_tolove;

3、触发器

创立触发器应用CREATE TRIGGER，这里就援用sakila数据库实例。如果存在，应用了判断语句 IF EXISTS，而后删除DROP TRIGGER曾经存在的触发器。

DELIMITER $$USE `sakila`$$DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`$$CREATE    /*!50017 DEFINER = 'root'@'%' */    TRIGGER `customer_create_date` BEFORE INSERT ON `customer`     FOR EACH ROW SET NEW.create_date = NOW();$$DELIMITER ;

4、sakila数据库

在文中我重复提到了MySQL的示例数据库sakila，是一个残缺的学习MySQL的好例子。蕴含了视图、存储过程、函数和触发器。能够去MySQL的官网获取SQL脚本。

文末留一个神秘的参数，通过此种形式能够过滤你不想看到的内容哟！无论在手机端或者PC端都可失效，亲测可用。

xxxx(检索的内容) -site:xxxn.net-- 或者xxxx(检索的内容) -site:xxshu.com

继续更新优化中...

总结

以上就是此次文章的所有内容的，心愿能对你的工作有所帮忙。感觉写的好，就拿出你的一键三连。在公众号上更新的可能要快一点，目前还在欠缺中。能看到这里的，都是帅哥靓妹。如果感觉总结的不到位，也心愿能留下您贵重的意见，我会在文章中进行调整优化。

原创不易，转载也请表明出处和作者，尊重原创。不定期上传到github或者gitee。认准龙腾万里sky，如果看见其它平台不是这个ID收回我的文章，就是转载的。linux系列文章：《初学者如何入门linux，原来linux还能够这样学》曾经上传至github和gitee。集体github仓库地址，个别会先更新PDF文件，而后再上传markdown文件。如果拜访github太慢，能够应用gitee进行克隆。

tips：应用hexo搭建的动态博客也会定期更新保护。

https://github.com/cnwangk/SQL-study

<H5 align=center>作者：龙腾万里sky</H5>