江苏龙网

  1. 首页 > 资讯 > 科技 > >

运维派官方|面试官邪魅一笑：MySQL千万级别大表，你要如何优化？( 二 )


分区的类型：
?RANGE分区：基于属于一个给定连续区间的列值 ， 把多行分配给分区 ?LIST分区：类似于按RANGE分区 ， 区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择 ?HASH分区：基于用户定义的表达式的返回值来进行选择的分区 ， 该表达式使用将要插入到表中的这些行的列值进行计算 。 这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式 ?KEY分区：类似于按HASH分区 ， 区别在于KEY分区只支持计算一列或多列 ， 且MySQL服务器提供其自身的哈希函数 。 必须有一列或多列包含整数值
分区适合的场景有：
?最适合的场景数据的时间序列性比较强 ， 则可以按时间来分区 ， 如下所示：CREATE TABLE members(firstname VARCHAR(25)NOT NULL,lastname VARCHAR(25)NOT NULL,username VARCHAR(16)NOT NULL,email VARCHAR(35),joined DATE NOT NULL)PARTITION BY RANGE(YEAR(joined))(PARTITION p0 VALUES LESS THAN(1960),PARTITION p1 VALUES LESS THAN(1970),PARTITION p2 VALUES LESS THAN(1980),PARTITION p3 VALUES LESS THAN(1990),PARTITION p4 VALUES LESS THAN MAXVALUE);
查询时加上时间范围条件效率会非常高 ， 同时对于不需要的历史数据能很容的批量删除 。
?如果数据有明显的热点 ， 而且除了这部分数据 ， 其他数据很少被访问到 ， 那么可以将热点数据单独放在一个分区 ， 让这个分区的数据能够有机会都缓存在内存中 ， 查询时只访问一个很小的分区表 ， 能够有效使用索引和缓存
另外MySQL有一种早期的简单的分区实现 - 合并表（merge table） ， 限制较多且缺乏优化 ， 不建议使用 ， 应该用新的分区机制来替代
垂直拆分
垂直分库是根据数据库里面的数据表的相关性进行拆分 ， 比如：一个数据库里面既存在用户数据 ， 又存在订单数据 ， 那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库 。 垂直分表是对数据表进行垂直拆分的一种方式 ， 常见的是把一个多字段的大表按常用字段和非常用字段进行拆分 ， 每个表里面的数据记录数一般情况下是相同的 ， 只是字段不一样 ， 使用主键关联
比如原始的用户表是：

运维派官方|面试官邪魅一笑：MySQL千万级别大表，你要如何优化？
本文插图

垂直拆分的优点是：
?可以使得行数据变小 ， 一个数据块(Block)就能存放更多的数据 ， 在查询时就会减少I/O次数(每次查询时读取的Block 就少) ?可以达到最大化利用Cache的目的 ， 具体在垂直拆分的时候可以将不常变的字段放一起 ， 将经常改变的放一起 数据维护简单
缺点是：
?主键出现冗余 ， 需要管理冗余列 ?会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力 ?依然存在单表数据量过大的问题（需要水平拆分） ?事务处理复杂
水平拆分 概述
水平拆分是通过某种策略将数据分片来存储 ， 分库内分表和分库两部分 ， 每片数据会分散到不同的MySQL表或库 ， 达到分布式的效果 ， 能够支持非常大的数据量 。 前面的表分区本质上也是一种特殊的库内分表 库内分表 ， 仅仅是单纯的解决了单一表数据过大的问题 ， 由于没有把表的数据分布到不同的机器上 ， 因此对于减轻MySQL服务器的压力来说 ， 并没有太大的作用 ， 大家还是竞争同一个物理机上的IO、CPU、网络 ， 这个就要通过分库来解决
实际情况中往往会是垂直拆分和水平拆分的结合 ， 即将Users_A_M和Users_N_Z再拆成Users和UserExtras ， 这样一共四张表
水平拆分的优点是:
?不存在单库大数据和高并发的性能瓶颈 ?应用端改造较少 ?提高了系统的稳定性和负载能力
缺点是：
?分片事务一致性难以解决 ?跨节点Join性能差 ， 逻辑复杂 ?数据多次扩展难度跟维护量极大


推荐阅读


上一篇：环球Tech|一图看懂微信支付分有啥用

下一篇：太平洋电脑网|生活在于折腾 尼康Z 50另类玩法探索微距世界