非DBA人员从零到一，MySQL InnoDB数据库调优之路(一)-建表

在离职的这段期间，除了忙于解决家里的事外，还对之前的工作做了一些总结，这次想根据我工作的经验结合书本理论介绍一下我对数据库调优的理解。

1.规范

一般公司内部要注意的规范有两种：表、字段命名和字段类型及长度控制

1.1 表、字段命名

先看一下下面的表名

T_USER_USERINFO：T的意思就是Table，USER代表这个表属于用户服务域，USERINFO代表这张表记录用户信息。

再看一下几个字段名

USERINFO_ID：用户信息主键ID

USERINFO_NAME：用户名称

总结下来我觉得公司的规范就是为了让我们建出来的表能使任何人在初次看到的时候能充分理解其意思，还有就是更好的管理。

1.2 字段类型及长度控制

为什么建表的时候也要严格控制字段的数据类型及长度，我们在这里只要知道严格控制字段的数据类型及长度可以影响1行数据的容量，关于数据类型和长度对表容量影响背后的逻辑我会在第二章节更细节地进行分析。

2 重要的理论与设计

2.1 局部性原理与磁盘预读

2.1.1 访问局部性

访问局部性（英语：Locality of reference）指的是在计算机科学领域中应用程序在访问内存的时候，倾向于访问内存中较为靠近的值。

访问局部性分为三种基本形式，一种是时间局部性，另一种是空间局部性。时间局部性指的是，程序在运行时，最近刚刚被引用过的一个内存位置容易再次被引用，比如在调取一个函数的时候，前不久才调取过的本地参数容易再度被调取使用。空间局部性指的是，最近引用过的内存位置以及其周边的内存位置容易再次被使用。空间局部性比较常见于循环中，比如在一个数列中，如果第3个元素在上一个循环中使用，则本次循环中极有可能会使用第4个元素。第三种为循序局部性。

局部性是出现在计算机系统中的一种可预测行为。系统的这种强访问局部性，可以被用来在处理器内核的指令流水线中进行性能优化，如缓存，内存预读取以及分支预测。

2.1.2 磁盘预读

为了尽量减少I/O操作，计算机系统一般采取预读的方式，预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k）。

2.2. B+树

在这篇博文我不会讨论为什么会使用B+树，我在这里只会给大家描述InnoDB的B+树是怎么样的，更详尽的分析将会留到下一篇博文进行阐述。

1.B+树非叶子节点只存储键值信息
2.B+树数据记录都存放在叶子节点中

2.2.1 B+树的数据量计算

我们已知2.1小节中磁盘预读选择了文件系统页的整倍数作为预读的长度规划，而在InnoDB中我们最后选择了文件系统的4页(16KB)作为我们表最基础单位页的存储空间，又因为非叶子节点的结构是： “页指针 + 键值”， 我们假设主键 ID 为 bigint 类型， 长度为 8字节（byte） ， 而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte） ， 因为一个页可以存放 16k 个 byte， 所以一个页可以存放的指针个数为 16384/14=1170 个。

按我们日常的推荐 B+树层数来计算三层结构我们第一层和第二层可以定位到1170 * 1170 个指针， 所以一共可以存放 1170*1170*（16KB/单条数据占用KB数）条数据，这也是我们平常假设当1条数据大少为1KB的时候我们的三层B+树的存储量为21902400行记录。

3. 结语

我们在工作中经常被教导建表很重要，通过第二小节的理论可以看出我们在建表的时候，因为已知加载一页数据的IO损耗是不变的，当我们如同第一小节要求那样在建表时候足够的合理，确保一条数据足够的小的时候，又因为访问局部性原理，更少的相邻页查询得到我们想要的结果，从而实现了查询速度的优化。

当然在工作中遇到更多的是各种约束下的查询，我会在下一篇博文结合其他内容，和大家分析我在工作中如何设计索引，达到高效的查询数据。

书籍：《MySQL技术内幕：InnoDB存储引擎（第2版）》

参考：访问局部性​​​​​​​

从局部性原理与磁盘预读原理来了解索引机制