千亿级海量数据并发 分库分表设计方法论

目录

一、索引原理、优化、失效

1.1 索引原理分析

1.1.1 聚簇索引

1.1.2 二级索引

1.1.3 联合索引

1.2 索引使用优化分析

1.2.1 存储空间

1.2.2 主键选择

1.2.3 联合索引使用

1.2.4 字符串索引

1.3 索引失效问题

 二、Mysql库表设计 注意点

 三、Mysql 海量数据 分表 方案

---

一、索引原理、优化、失效

1.1 索引原理分析

1.1.1 聚簇索引

 聚簇索引，索引树和数据放一起（数据存储在主键索引中），叶子节点存放的是数据。

数据按主键顺序存储。

自增主键 VS 随机主键，在写性能上，自增主键能顺序写入，随机主键需要挪动数据，可能发生页面分裂。在读性能上。。。。。。待学

1.1.2 二级索引

二级索引，索引树的叶子节点存放的是 主键值。

除主键索引以外的索引都是二级索引。

一次查询需要走两遍索引。

主键大小会影响索引大小。（假如主键数据类型为varchar(64)，索引的主键节点  64*Num_records*Num_indexes ）

 

1.1.3 联合索引

 联合索引，key由多个字段组成。按第一列排序，第一列相同按第二列排序。

最左原则，不可跳过中间值原则，

某列使用范围查询的话，后面的列不能使用索引。

1.2 索引使用优化分析

1.2.1 存储空间

索引文件大小。

字段大小------> 页内节点个数 --------> 树的层数

 

所以主键数据类型大小很重要。关系到索引大小，太大，就不行了。页的大小是一定的，16KB，所以还关系到一页能存的数据节点数。

1.2.2 主键选择

自增主键：顺序写入，效率高。但是为了保密需要uid和自增主键ID 搭配使用。所以每次查询需要走两级索引。

随机主键：随机写入，数据挪动和页面分裂，写入效率低。如果UID是随机生成的，那查询的时候就不需要走两级索引。如果UID还是要搭配随机生成的主键ID来使用，那还是需要走两级索引。

业务主键：雪花算法。分为三部分，第一部分为当前时间戳，具体到毫秒，第二部分为分布式机子的机子ID，第三部分为顺序生成的一段数据，合体为 业务主键UID。这样写入磁盘和查询磁盘效率都高，使用一级索引。

联合主键：影响索引大小，不易维护，不建议使用。

1.2.3 联合索引使用

联合索引，创建的时候，按照索引区分度排序，能排除最多数据的 索引字段，放在最前面优先进行筛选。

如果走某个索引为了查询获得某个字段，为了避免二级索引，可以直接将 目标字段和该索引字段放一起组成联合索引，避免二级索引，实现索引覆盖。

索引下推。（引擎筛选好再返回服务器，本分）

1.2.4 字符串索引

 索引字段的长度影响索引的大小，所以要设置合理长度。

不支持%开头的模糊查询。

1.3 索引失效问题

1.索引区分度太低，找到一个要回表一个，数目太大，索性放弃索引，直接进行 全表扫描。

2. A=XXXX OR B = XLXLXL  Mysql5.1之前会走不了索引，5.1之后 引擎可以合并两边的结果，不会索引失效。

3.隐式类型转换，会索引失效，去走全表扫描。所以在写查询条件时要注意 字段数据类型正确。

4.索引列包含计算。 where name-20=30

5.数据范围影响。数据范围包含所有 索引数据，那就直接 全表扫描。

 

 二、Mysql库表设计 注意点

 

 三、Mysql 海量数据 分表 方案

1.垂直拆分

将宽表拆分为多个表。 部分数据字段可能会频繁读写，把它们和不经常变动的字段区分开来。减少损耗。

2.水平拆分

数据量太大，就取模，水平分表，均匀读写。

 在UID和Phone之间建个 MAP映射表。应对 where phone = 'XXXX'时要找太多次子表。

 

 UID和PID采用雪花算法生成。TIMESTAMP+特定随机码+主机ID+count计数器  为 UID。 PID也采用雪花算法生成，其中的特定随机码和其所属UID里的特定随机码，保持一致。这样在以PID分表时就能保证一个UID的所有PID都被分在一个子表里。

 

 为保证时效性，计算 “30天内XXX”的指标时，不用跨越两个表，采用“空间换时间”的方法，每次生成一条二月的数据时，不仅插入二月的表里，也插入三月的表里。查询的时候只要在三月的表里进行查询即可。到了三月的时候，二月的表如果不再需要的话也可以删了，因为三月的表里已有。