0%

MySQL亿级表优化

场景

对单表2亿数据进行查询优化,测试几种常见的优化方式,给出解决方案

设计之初

设计之初需要考虑的问题很多。设备,存储引擎,水平分区,水平、垂直分表,表设计规范等

SQL优化&索引

SQL优化

常见的SQL优化:

使用limit对查询结果的记录进行限定
避免select *,将需要查找的字段列出来
使用连接(join)来代替子查询
拆分大的delete或insert语句
OR改写成IN
避免%xxx式查询
使用同类型进行比较,比如用'123'和'123'比,123和123比

联合索引

查看列的离散程度离散度更高的索引应该放在联合索引的前面,因为离散度高索引的可选择性高。
要想使用联合索引查询,最左是必选项,建议将查询条件顺序与索引建立顺序先后一致

如何查看是否使用索引

使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

备注

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来 存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

值分布很稀少的字段不适合建索引,例如”性别”这种只有两三个值的字段

优化like

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 新增一列,存储该字段的反转。比如原字段是abcd,取反存储为dcba,查询%bcd改成查dcb%。

缺点

只能从头尾匹配,不能从中间匹配

创建view

根据业务情况,基于一段时间或者经常被查询的数据创建view,通过做view来读写分离,写在table上,读在view表上

时间上的坑

这里有个坑,如果按照当天时间创建视图表

time >= DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');

那么在当天的24点时刻,视图表就是一张空表。

解决办法,将时间范围延长,改为昨天的23点

time >= DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY),INTERVAL 23 HOUR);

COUNT优化

优化分页,使用下面语句代替count函数

explain select * from `table_name`;

缺点

explain的rows是一个大概值,测试了几组数据差的还挺多,并且要注意的是当查询分页超过实际行数,不会使用索引,查询速度会降低很多

删除过期数据

比如删除100天以前的数据,若每天百万级数据,防止一次删除过多造成表锁死,需要批量删除。

批量删除优化

比起利用索引(time)删除数据,若改为id删除会有更好的速度。这里参考了一篇博客的方法,博客链接

1.找出符合条件的create_time的最大ID
select max(id) from table_name where create_time <= '2019-01-01 00:00:00';

2.接着按id删除
delete from table_name where id < maxId limit 10000;

使用python脚本,作为系统定时任务执行

亿级表优化解决方案

  1. 用时间作为联合查询最左条件约束,查询时必选
  2. 优化like语句 or not(最后没有选择优化Like,因为使用方案1之后比较了优化Like与不优化的查询时间差别不是很明显,就选择了更好的用户体验,支持模糊查询)
  3. 对当天的数据创建视图,查询当天数据默认使用视图查询
  4. 水平分区(RANGE非常适合日期列的分区)
    量级:亿级 50 000 * 43 * 120=258 000 000
    解释:一条线一天5万,一共43条线,最少保存120天
    每天 50 000 * 43=2150000
    按月份分为4张表,每张表约258 000 000/4=64500000 水平拆分为千万级