MySQL5.7数据库优化之表设计

一个数据库、表设计的优劣会影响到数据库的性能，所以合理的设计数据库是非常重要的。
最近看了MySQL5.7手册，手册第八章就是关于优化的，第十一章详细的介绍了各个字段。如果你有兴趣可以去看看，相信会收获颇丰。下面根据手册及结合平时开发经验还有大学学的数据库原理来谈谈一些自己的见解。由于水平有限，难免会有错误及疏漏的地方，欢迎指正。

一、数据库的创建

创建数据库非常简单，只不过，建议给数据库指定默认的字符集和排序规则。虽然表、字段均可指定字符集和排序规则。

字符集一般选utf8或者utf8mb4，utf8mb4是utf8的超集，兼容四个字节。utf8是utf8mb3的别名，最大支持三个字节，所以存储四个字节的字符时会出错，例如单字符表情占四个字节，使用utf8存储就会出错。

指定字段长度时，在绝大多数情况下，utf8与utf8mb4是一样的，但有一个需要注意，那就是定长类型(char)。char类型会预先分配存储空间，对于ut8是按照三个字节计算的，对于utf8mb4是按照四个字节分配的，所以utf8mb4会比ut8多占存储空间。根据业务特点选择是的字符，这里推荐如下：

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE=utf8mb4_general_ci;

二、表的设计

1、存储引擎的选择

我们可以使用SHOW ENGINES来查看所支持的存储引擎。

一般常见的有InnoDB、MyISAM、MEMORY、Archive。用的最多的是InnoDB、MyISAM。Archive 是存档型存储引擎，提供无阻塞插入和查询，但是也只能做插入和查询操作，MEMORY是内存型存储引擎，数据存储于内存中，常用于缓存。

此版本对InnoDB做了大量的优化、增加了很多特性，比如开始支持全文索引(fulltext)等。只不过mysql自带的全文索引不支持中文分词（从MySQL 5.7.6开始，MySQL提供了一个支持中文，日文和韩文（CJK）的内置全文ngram解析器，以及一个可安装的日文版MeCab全文解析器插件），中文分词可以使用coreseek。coreseek是在sphinx的基础上，增加了中文分词功能，所以可以用来做中文的全文索引。直接编译进mysql中性能更强。

下面说说Innodb和MyISAM这两个存储引擎的几个最重要的区别（只有了解了区别，才知道如何选择合适的存储引擎）：

1）存储方式不同

MyISAM数据、索引、结构分别存在不同的文件中（.MYD，.MYI，.frm）;
InnoDB默认数据和索引存储在同一个表空间文件中（.ibd），结构存储在另一个文件中（.MYI）。当然，也可以使用SET global innodb_file_per_table=1;使数据和索引存储在不同的表空间文件中。如果一张大表需要很多索引，这能起到很不错的优化效果。 对于小表就无所谓了。

2）锁的粒度不同

MyISAM只支持表级锁
InnoBD既支持表级锁，还支持行级锁
这也就是为什么InnoBD并发写（修改、删除）操作性能更好

3）InnoDB支持事务、外键约束等

4）索引实现方式不同

为了减少一次性磁盘IO开销，可以读取到更多的关键字数量，MyISAM和InnoDB均采用B+树作为索引结构，但是实现的方式却有不同。InnoDB采用的是聚簇索引，即主键的叶子节点保存了完整的数据记录。

下面举例说明这两者的差异：
假设我们要存储以下数据：

id	name	age
1	zhaoyi	13
3	sunsan	14
2	qianer	15
4	lisi	13
5	zhouwu	11
6	wuliu	10
7	zhengqi	21
8	wangba	15
9	fengjiu	17
10	chenshi	16
11	chushiyi	13
12	weishier	14

假设用MyISAM存储引擎存储，那么存储方式如下图

由上图可以看出，主键索引的叶子节点存储的是数据的地址，数据并不是按主键顺序存储的。 所以查询数据时，如果不指定排序，则会按存储的顺序检索出来，所以你会发现id=3却在id=2后面。
由于主键保存的时数据的地址，所以查询非常快，这也就是MyISAM适合高速读的原因
又由于插入时数据不需要排序，所以插入速度也非常快，这就是MyISAM适合高速并发插入的原因

假设用InnoDB存储引擎存储，那么存储方式如下图

由上图可以看出，主键索引是聚簇索引，叶子节点存储的是数据。
每次插入数据都会根据主键id进行排序插入，这也就是为什么InnoDB不适合高速并发插入的原因。 也是InnoDB表必须有一个主键的原因，而主键往往推荐使用自增整型，避免使用字符类型（char、varchar），因为整型排序更快，自增插入会加快插入的速度。

对于二级索引，MyISAM和Innodb实现也是不同的，虽然都是B+树，但是MyISAM的叶子节点保存的是数据的地址，与主键的实现方式是一致的。而InnoDB的二级索引的叶子节点保存的是主键id。

假设我们在name