MySQL 深入学习总结

作者：yandeng，腾讯 PCG 应用开发工程师

1.数据库基础

1.1 MySQL 架构

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，各层介绍：

1.1.1 连接层

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.1.2 服务层

1.1.3 引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

1.1.4 存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

1.2 数据引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

1.2.1 MyISAM

使用这个存储引擎，每个 MyISAM 在磁盘上存储成三个文件。

1. frm 文件：存储表的定义数据

2. MYD 文件：存放表具体记录的数据

3. MYI 文件：存储索引

1.2.2 InnoDB

InnoDB 是默认的数据库存储引擎，他的主要特点有：

1. 可以通过自动增长列，方法是 auto_increment；

2. 支持事务。默认的事务隔离级别为可重复度，通过 MVCC（并发版本控制）来实现的；

3. 使用的锁粒度为行级锁，可以支持更高的并发；

4. 支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度；

5. 配合一些热备工具可以支持在线热备份；

6. 在 InnoDB 中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

7. 对于 InnoDB 类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于 B+数的叶子节点上。

1.2.3 Memory

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是 frm。

1. 支持的数据类型有限制，比如：不支持 TEXT 和 BLOB 类型，对于字符串类型的数据，只支持固定长度的行；VARCHAR 会被自动存储为 CHAR 类型；

2. 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为 MEMORY 存储引擎的瓶颈；

3. 由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

4. 查询的时候，如果有用到临时表，而且临时表中有 BLOB，TEXT 类型的字段，那么这个临时表就会转化为 MyISAM 类型的表，性能会急剧降低；

5. 默认使用 hash 索引；

6. 如果一个内部表很大，会转化为磁盘表。

1.3 表与字段设计

1.3.1 数据库基本设计规范

1. 尽量控制单表数据量的大小，建议控制在 500 万以。500 万并不是 MySQL 数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小；

2. 谨慎使用 MySQL 分区表。分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据；

3. 禁止在数据库中存储图片，文件等大的二进制数据。通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息；

4. 禁止在线上做数据库压力测试。

1.3.2 数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型。列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多， 索引的性能也就越差；

2. 避免使用 TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据；

3. 尽可能把所有列定义为 NOT NULL。

1.3.3 索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过 5 个；

2. 禁止给表中的每一列都建立单独的索引；

3. 每个 InnoDB 表必须有个主键；

4. 建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。区分度最高的放在联合索引的最左侧（区分度 = 列中不同值的数量 / 列的总行数）。尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越