一、千亿级海量数据高并发场景主键设计选择
索引原理分析
聚簇索引:
数据存储在主键索引中
数据按主键顺序存储
二级索引:
除主键索引以外的索引
叶子中存储主键值
一次查询需要走两遍索引
主键大小会影响所有索引的大小
联合索引
key由多个字段组成
最左匹配原则
一个索引只创建一棵树
按第一列排序,第一列相同按第二列排序
使用注意:如果不是按照最左开始查找,无法使用索引、不能跳过中间列、某列使用范围查询,后面的列不能使用索引
hash索引适用点查询
MySQL会进行查询优化,语句顺序会调整
索引使用优化分析
存储空间
索引文件大小
字段大小->页内节点个数->树的层数
BigInt类型主键3层可以存储约10一条数据 16KB/(8B(key)+8B(指针))=1K 10^3*10^3*10^3=10亿 32字节主键3层可以存储6400W
主键选择
自增主键,顺序写入,效率高
随机主键,节点分裂、数据移动
查询场景
自增主键:写入磁盘利用率高,每次查询走两级索引
随机主键:写入磁盘利用率低,每次查询走两级索引
业务主键:写入、查询磁盘利用率都高,可以使用一级索引
联合主键:影响索引大小,不易维护,不建议使用
联合索引使用
按索引区分度排序:区分度一个值可以区分多少数据
覆盖索引:热点查询建联合索引
索引下推:
字符串索引
设置合理长度
不支持%开头的模糊查询
索引失效问题分析
A=xx OR B=xx 5.1开始,引入Index merge技术,对同一个表可以使用多个索引分别扫描
隐式类型转换 WHERE moblie = '135XXXX'; WHERE mobile = 135XXX;
索引列包含计算 WHERE name - 20 =30
数据范围影响:索引区分度过低、条件超出索引范围
MySQL库表设计实践
联合索引:优于多列独立索引
索引顺序:选择性高的在前面
覆盖索引:Key里面包含要查询的数据
索引排序:索引同时满足查询和排序
数据库字符集使用utf8mb4
VARCHAR按实际需要分配长度
文本字段建议使用VARCHAR
时间字段建议使用long
bool字段建议使用tinyint
枚举字段建议使用tinyint
交易金额建议使用long
禁止使用“%”前导的查询
禁止在索引列进行数学运算,会导致索引失效
表必须有主键,建议使用业务主键
单张表中索引数量不超过5个
单个索引字段不超过5个
子串索引使用前缀索引,前缀长度不超过10个字符
是否分表
建议单表不超过1KW
分表方式
取模:存储均匀&访问均匀
按时间:冷热库
分库
按业务垂直分
水平拆分多个库
二、千亿级海量数据高并发场景分库分表设计方法论
分库分表方案
垂直拆分 分表(宽表变窄表)
水平拆分 分库分表 读写均匀,冷热库拆分
三、千亿级海量数据高并发场景分库分表时间落地方案
用户库分表
存储均匀&访问均匀->取模 uid mod 128 按非分表参数查询 考虑映射表
商品库分表(基因注入法 公共值取模分区)
两个维度查询必须满足
映射表太重
系统消息库分表
时效性强
冷热数据拆分
分表分少了怎么办 业务修改路由算法,后台清理数据