如何在Mysql中存储十亿手机号码
十亿手机号码, 如何在Mysql中存储, 并保证快速读写
首先, 十亿的数据, 单表存储是不现实的, 单表两千万读性能已经大打折扣了
那么我们第一步, 需要考虑
分区
那么如何分区呢?
- 号码的格式有 136xxx，137xxx。那么我们可以按手机号段、国际区号对手机号进行分区
- 我们也可以进行
哈希分区,
这样查询某个手机号是否存在这种业务就能更快，因为一张表被划分成了很多张小表。
并且如果涉及多张小表 MySQL 还可以多线程并发查，效率提升很多。
如果考虑获取某一号码段的所有手机号，那最好还是按照范围分区，可以使逻辑查询范围更小。
但是 hash 分区数据可能比范围分区更加均衡。
注意，对于 HASH 分区个数最好是 2^n。因为对于 2^n 取余相当于对 2^n - 1 取与运算，增加了查询时的计算分区的效率
存储引擎
既然是建表, 表的存储引擎应当如何选择? Innodb 还是 MyISAM 呢?
那么应该问清楚, 是提前载入好的字典表形式呢? 还是面向用户的存储行为呢?
字段和索引
确立好了表的存储引擎, 那么字段如何设计呢?
- 如果是纯数字, 那么
bigint即可 - 如果存在有特殊字符, 那么可以选择
varchar
字段的索引有两种方式, Hash 和 B-Tree
Hash索引是非常不建议的, 在数据量大的情况, 会发生hash碰撞。
不论hash的算法多么精确，当数据量大的时候都有可能发生hash碰撞
并且hash不支持范围查询、模糊查询、排序
因此我们选择 B-Tree
参考链接
对树形数据存储的表设计
闭包表
闭包表是解决分级存储的一个简单而优雅的解决方案，它记录了树中所有节点间的关系，而不仅仅只有那些直接的父子节点。
| 方案 | 表数量 | 查询子 | 查询树 | 插入 | 删除 | 引用完整性 |
|---|---|---|---|---|---|---|
| 邻接表 | 1 | 简单 | 困难 | 简单 | 简单 | 是 |
| 枚举路径 | 1 | 简单 | 简单 | 简单 | 简单 | 否 |
| 嵌套集 | 1 | 困难 | 简单 | 困难 | 困难 | 否 |
| 闭包表 | 2 | 简单 | 简单 | 简单 | 简单 | 是 |
邻接表
常用的邻接表设计, 都会增加一个 parent_id 字段
比如区域表
1 | CREATE TABLE `oa_dept` ( |
路径枚举
路径枚举则是存储一条节点对于树的路径, 就好像unix的路径一样.
嵌套集
嵌套集解决方案是存储子孙节点的相关信息，而不是节点的直接祖先。我们使用两个数字来编码每个节点，从而表示这一信息，可以将这两个数字称为nsleft 和 nsright。
参考文档
事务原理
事务具有四大特性, 原子性, 隔离性, 一致性, 持久性
- 原子性: 指的是一个事务操作是不可分割的, 执行要么全部成功, 要么全部失败
- 隔离性: 指的是, 多个事务并发操作时, 互相是隔离的, 某事务不该被其他事务所干扰
- 一致性: 指的是事务遵循数据的完整性,唯一性约束
- 持久性: 指的是事务如果已经提交, 那么对数据的变更就是永久的
在谈原理之前, 我想先假设出一个事务场景, 并提出一些问题, 以便更好的理解事务原理:
- 都知道Mysql的隔离级别有四种: 读未提交, 读已提交, 可重复读, 序列化读
- 四种隔离级别是如何实现的?
- 原子性是不可分割的
- 那么, 全不成功, 进行回滚, 如何实现恢复为原始数据的回滚过程呢?
- 全部成功, 进行提交, 如何实现数据在磁盘上的真实更新的呢?
- 持久性说, 事务已提交对数据的变更就是永久的. Mysql如何保障的, 提交总是要更新磁盘(后文用刷盘指代)的吧?
- 假设是先刷盘后提交, 那么在刷盘的时候宕机了, 该怎么恢复处理呢?
- 假设是先提交后刷盘, 那么在刷盘的时候宕机了, 该怎么恢复处理呢?
我们通常默认使用 InnoDB 作为存储引擎, 那么, 接下来, 从问题出发, 讨论 InnoDB事务原理 吧
四种隔离级别是如何实现的?
在此之前, 先重新整理下, 已知的四种隔离级别
假设有事务A, B同时并发操作同一条数据, 事务A查询数据, 事务B更新数据.
- 读未提交: 事务A会查询到事务B未提交却修改过的数据, 若是事务B回滚, 事务A读取的数据将是不正确的, 即: 脏读
- 读已提交: 事务A仅可查询事务B提交成功后的数据, 但若是事务A进行先后两次查询(重复读), 将出现前后数据不一致的情况, 即: 不可重复读
- 可重复读: 保证了在事务A重复读, 不受其他事务更新的影响, 前后数据一致, 但若有其他事务插入数据, 重复读将出现, 后读数据集更多的情况, 即: 幻读
- 序列化读: 所有事务排队, 一条条的处理, 完美解决并发出现的问题
自上而下, 隔离级别越高, 事务之间的影响也越来越小, 越来越隔离, 数据越来越安全
同样的, 性能也越来越低, 当开启序列化读, 巨量事务排队的情况, 数据的安全性是拉满了, 可性能却不尽人意
Mysql的默认事务隔离级别是: 可重复读
那么, 接下来介绍, 如何实现这四种隔离级别的呢?
标准SQL事务隔离级别实现原理
解决并发事务的常见方式, 就是通过悲观锁来进行并发控制了, 标准SQL事务隔离级别的实现是依赖锁的, 我们看看它是如何实现的:
1.
多版本并发控制(MVCC)
1 | 通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在. |
参考链接
深入理解索引
什么是索引下推
[1]: https://blog.csdn.net/sinat_29774479/article/details/103470244
InnoDB引擎的索引原理是什么
来自MAKA后端笔试的一道题, InnoDB引擎的索引是如何提升搜索效率的?
其实本质上是谈谈对InnoDB索引原理的了解程度
MySql
Mysql是一款开源的关系型数据库管理系统
目录
Mysql的软件架构
Mysql大体分为Server层和存储引擎层这两块.
Server层包含连接器,查询缓存,分析器,优化器,执行器等Mysql核心服务功能, 以及所有的内置函数,存储过程,视图,触发器等 都在这一层实现
存储引擎层负责数据的存储与读取, 常见的存储引擎有Innodb,Myisam
连接器
连接器负责跟客户端建立连接, 获取权限, 维持和管理连接
查询缓存
查询缓存主要用来缓存所执行的SELECT语句
打开查询缓存后, Mysql会将查询语句进行哈希计算, 并将得到的哈希值与查询结果存放在查询缓存中
分析器
MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
- 第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
- 第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。
优化器
优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
执行器
当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。
这里说明一下为什么查询表的执行权限不在优化器之前检测？
是因为有些时候，SQL语句要操作的表不只是SQL字面上那些。比如有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。
如何使用存储过程
看到一个问题: 为什么开发中不推荐使用存储过程和触发器?
作为开发看来, 存储过程是数据库的”自定义函数”, 而触发器则是数据库的”行为钩子”
存储过程是由流程控制语句和SQL语句书写的一组SQL语句集合
比如在ERP开发中, 十几张表联表查询的情况下, 将多组SQL写入存储过程, 一次调用即可取得结果
- 简化开发, 应用程序代码减少, 提高了开发效率
- 提高查询性能, 直接编译成物理计划, 减少了parse和查询优化步骤
- 减少了网络IO开销
缺点也是有的
- 你需要同时维护两套代码, 同时增加了开发, 测试, 上线的复杂度
- 存储过程无法支持分库分表, 有些分布式中间件不支持存储过程
- 存储过程中的控制逻辑, 是各家数据库自已的方言, 移植到其他数据库成本高
使用存储过程和触发器取决于使用场景
如果你某些业务涉及大量SQL操作, 并且稳定, 极少改动, 使用它也许是不错的
请衡量利弊, 做好取舍
可以参考该链接, 其中有更详细的讨论: https://www.zhihu.com/question/57545650