互联网业务经常使用MySQL数据库作为后台存储，存储引擎使用InnoDB。我们结合互联网自身业务的特点和MySQL数据库的特点，描述在具体的业务场景下如何设计表和分表。本文从MySQL相关基础设施设计介绍入手，结合企业实际案例介绍分表分索引设计的实用技巧。1、InnoDB的记录存储方式是什么？大家都知道InnoDB存储引擎中的记录是按照主键的顺序存储的，依靠这个特性为表创建了主键聚簇索引。InnoDB是如何实现记录的“顺序存储”的？首先，我们需要知道“顺序”页面顺序和页面间顺序。页是InnoDB内外内存交换的基本单位。页间顺序：磁盘文件中的页是通过双向链表连接起来的，页间可能是物理排序的。在大多数情况下，它是逻辑上有序的；页内排序：页面中的每条记录都使用一个单项链表来连接记录，因此页面在逻辑上是有序的，利用slot数据结构在页面中实现接近二分查找的查询效率。图为InnoDB页面中的空间分布：PageHeader基于以上特点，我们来分析一下使用不同的主键对存储的影响：自增主键：主键的值自增，数据为顺序插入，所以页中的数据在物理上是连续的，一页满后顺序分配下一页。在没有删除操作的情况下，整个表的记录是按照写入的顺序连续存储在磁盘文件中的。这种存储方式的磁盘利用率很高，随机IO很低。插入效率相当高。业务主键：比如user表以uid为主键，product表以infoId为主键。这个有意义的主键称为业务主键。显然，业务主键不仅不能实现记录的物理连续性，还可能在插入数据时造成分页，造成分页。比如一个页面空间满了，存储主键值0~99，100条数据，如果要插入55条记录，页面没有空间了，需要拆分分为两个页面完成插入操作，两个分割的页面很难被填满，会造成页面碎片化，所以业务主键在写性能和磁盘利用率上不如自增主键。通过上面的分析，我们是不是可以得出一个结论：使用自增主键一定是好的？在我们分析InnoDB索引之前，现在下结论还为时过早。2.什么是主键索引？InnoDB会自动在表的主键上创建索引，数据结构采用B+Tree。根据存储的特??点，主键索引也称为聚簇索引。聚簇索引的索引结构和实际数据存储在一起，B+Tree叶子节点存储实际记录，如图：聚簇索引3、什么是非主键索引？既然记录是存储在主键索引结构中的，那么在其他列上创建的索引如何找到记录呢？我们可以很自然地认为非主键列上的索引可以先通过自己的索引结构找到主键值，然后利用主键值聚类索引上找到对应的记录。InnoDB就是这么做的，所以我们也把非主键列上的索引称为二级索引（因为一个查询需要找两棵索引树）。二级索引具有以下特点：主键索引以外的索引；索引结构的叶子节点中的Data是主键值；一个查询需要找到它自己和主键的两个索引。4.什么是联合指数？联合索引也称为多列索引。索引结构的键包含多个字段。排序时，先比较第一列。如果相同，则按第二列进行比较，依此类推。联合索引结构图如图所示：对联合索引联合索引的查询必须满足以下特点：key按照最左查找，否则不能使用索引；跳过中间列会导致后面的列无法使用索引；某列使用范围查询，后面的列不能使用索引。根据前缀索引的特点，联合索引（a,b,c）可以满足三种类型的查询（a），（a，b），（a，b，c）。五、总结了解了InnoDB的索引之后，我们来分析一下自增主键和业务主键的优缺点：在线业务中不会有直接使用主键列的查询。业务主键：写入、查询效率、磁盘利用率低，但可以使用主索引，依靠覆盖索引的特性，在某些情况下，也可以在非非索引上用一个索引完成查询主键索引（在后面的案例中会详细介绍）。与业务主键相比，自增主键的IO效率优势在SSD硬盘下几乎可以忽略不计，但是业务主键在业务查询性能上优势明显，所以在业务数据库中，我们使用业务首要的关键。6.电商业务分表设计与实践根据MyQL数据库的特点和自身业务特点，制定了一系列的数据库使用规范，可以有效指导项目开发中数据库表和索引的设计一线RD的流程。下面介绍电子商务业务中表和索引的主要设计原则和两个实际案例。1.表设计原则主键选择：我们之前比较分析过业务主键和自增主键的优缺点。结论是业务主键更符合业务查询需求，大部分互联网服务符合读多写少的特点。因此，所有在线业务都使用业务主键。索引个数：由于索引过多会导致索引文件过大，因此要求索引个数不超过5个。列类型选择：通常越小越简单越好，例如：BOOL字段使用TINYINT枚举字段统一使用TINYINT，交易金额统一使用LONG。因为BOOL和枚举类型使用TINYINT方便扩展，对于金额数据，虽然InnoDB提供了支持精确计算的DECIMAL类型，但是DECIMAL是存储类型而不是数据类型，不支持CPU原声计算，所以效率会更低，所以我们简单地处理将小数转换为整数并将它们存储在LONG中。分表策略：首先必须明确，数据库的性能问题通常发生在数据量达到一定程度之后！所以，要求我们提前做一个预估，不要等到拆分了再拆分。一般表的数据量控制在千万级别；常用的分表策略有两种：按键取模，均匀读写；按时间划分，清除冷热数据。2、实战案例一：用户表设计用户表包含字段：uid、nickname、mobile、addr、image...、switch；uid为主键，业务中有基于uid和mobile两种查询需求，所以需要在mobile上创建索引。开关列比较特殊，类型是BIGINT，用来保存用户的BOOL类型属性，每一位可以保存一个用户的属性，比如我们用第一位保存是否接收推送，第二位保存是否保存离线消息等。这种设计具有很高的可扩展性（因为BIGINT是64位的，可以存储64个状态，一般情况下很难用完），但是也带来了一些问题，switch查询频率高。由于InnoDB是行存储，要找到查询开关，您需要获取正行数据。针对以上场景，我们可以在表设计上做哪些优化呢？一种常见的解决方案是垂直划分表格，这种方法很常见，我们不再过多讨论。还有一个解决方案，我们可以利用InnoDB覆盖索引的特性，在uid和switch两列上创建一个联合索引，这样uid和switch两列的值都包含在secondary中索引，这样在用uid查询switch时，只需要通过secondary即可，所以不需要访问记录就可以找到switch，甚至可以通过secondary索引的叶子节点找到要查询的switch值，查询效率非常高高的。还有一点需要考虑。可想而知，开关的更换相当频繁。switch的变化会不会引起联合索引的变化（这里的变化指的是索引节点的分裂或者顺序调整）？答案是不！因为联合索引的第一个索引列uid是唯一的，不会改变，所以uid已经确定了索引的顺序。switch列的变化只会改变索引节点上第二个key的值，不会改变索引结构。案例二：IM子系统分表方案IM子系统包括四个主要的业务表：用户、联系人、云消息、系统消息。数据库按业务拆分，每个业务使用一个单独的实例。除系统消息表外，其他表以uid为key，对128取模，分为128张表。由于系统消息业务特殊，其分表方案与其他业务不同。我们先了解一下系统消息的业务特点：系统消息表中存放的是服务器发送的通知类消息。既然是通知，自然会有效果。我们规定系统消息的有效期为30天，所以我们针对以上特征采取如下措施分表方案：系统消息表按月分表，每个月的数据分为128张表.大家思考一个问题：在查询一个人的系统消息时，因为是按月分表，而且查询大多是跨月的（因为要查找30天内的消息），所以需要两次数据库交互。可以优化吗？我们可以冗余存储。具体优化方案如下：插入系统消息时，写入当月和上月两张表；从上个月开始阅读；这种冗余存储的方案我们可以保证一次查询就可以查到用户有效期内的所有系统消息，但是牺牲存储空间和写入效率不一定是最好的方案，但是在业务场景下还是可以的数据总量小，查询性能更重要可选。7.总结自增主键性能不一定高，需要结合实际业务场景分析；在大多数场景下，数据类型应该选择尽可能简单；索引越多越好，索引太多会导致索引文件太大；如果在索引文件中可以找到要查询的数据，存储引擎就不会去查找主键索引来访问实际的记录。
