大数据量表的优化思路

大数据量表的优化思路

第一节 问题描述

1.1 常见问题

  1. 单表数据量达到千万级别后,查询操作耗时过久。
  2. 针对海量的数据表进行修改操作。

1.2 MySql模拟

创建千万条模拟数据,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#创建MyISAM模式表方便批量跑数据
CREATE TABLE `logs1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`logtype` varchar(255) DEFAULT NULL,
`logurl` varchar(255) DEFAULT NULL,
`logip` varchar(255) DEFAULT NULL,
`logdz` varchar(255) DEFAULT NULL,
`ladduser` varchar(255) DEFAULT NULL,
`lfadduser` varchar(255) DEFAULT NULL,
`laddtime` datetime DEFAULT NULL,
`htmlname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1811 DEFAULT CHARSET=utf8 COMMENT='日志表';


#创建存储过程
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
DECLARE n int DEFAULT 1;
DECLARE in_dateTime TIMESTAMP DEFAULT '2020-01-01 00:00:00';
while n <= 10000000 do
INSERT INTO `logs1`(`logtype`,`logurl`,`logip`,`logdz`,`ladduser` ,`lfadduser`,`laddtime`,`htmlname`) VALUES (FLOOR(1 + (RAND() * 2)), '/index', '0:0:0:0:0:0:0:1', null, null, 'null', in_dateTime, '首页');
set in_dateTime = in_dateTime + interval 1 MINUTE_SECOND;
SET n = n + 1;
end while;
END;

#执行存储过程
CALL my_insert();


#数据插入成功后修改表模式InnoDB 时间稍微久点
alter table `logs1` engine=InnoDB;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询表中行数,用时2.6s左右
select count(*) FROM logs1;

#分页查询,id从1811开始,查询第6001条到6010条数据,用时0.025s
select * from logs1 limit 6000,10;

#用时0.048s
select * from logs1 limit 60000,10;

#用时0.256s
select * from logs1 limit 600000,10;

#用时4.372s
select * from logs1 limit 9000000,10;

分页查询方式会从数据库第一条记录开始扫描,所以越往后,查询速度越慢,而且查询的数据越多,也会拖慢总查询速度。


第二节 解决流程

2.1 索引

explain得知查询并未走索引,首先增加索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# type=all全表查询,先创建索引
explain select COUNT(*) FROM logs1 where logtype = 2;

#增加唯一索引和普通索引
ALTER TABLE logs1 ADD UNIQUE index_id (id)
ALTER TABLE logs1 ADD INDEX index_logtype (logtype)
ALTER TABLE logs1 ADD INDEX index_laddtime (laddtime)
ALTER TABLE logs1 DROP INDEX index_logtype
ALTER TABLE logs1 DROP INDEX index_laddtime

#type是选择性低的属性,而time则不是,所以索引的影响也不同
#全表查询 5.073->索引 4.226
explain select logtype from logs1 where logtype > 1;
#ALL 3.566->range 0.426
explain select laddtime from logs1 where laddtime >= '2020-04-14 04:00:00';

索引并非万能的,如果我们查询的属性列是多个。

1
2
3
4
5
6
7
8
9
#index 4.572
explain select id,logtype,laddtime from logs1 where laddtime >= '2020-04-14 04:00:00';

#增加where后判断条件
#0.966->0.853
explain select id,logtype,laddtime from logs1 where laddtime >= '2020-04-14 04:00:00' and id > '9001812' and logtype = 2;

#创建组合索引
ALTER TABLE logs1 ADD INDEX index_union (id,logtype,laddtime)

2.2 优化SQL

避免select *,将需要查找的字段列出来

使用连接(join)来代替子查询

拆分大的delete或insert语句

通过开启慢查询日志来找出较慢的SQL

不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边

sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库

OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内

避免%xxx式查询

尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描

对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5

列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大

2.3 分区

MySql 5.1版本引入,用户需要在建表的时候加上分区参数。分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实际上是通过对一组底层表的对象封装,但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义,没有全局索引。

分区的好处是:

  1. 可以让单表存储更多的数据
  2. 分区表的数据更容易维护,可以通过清楚整个分区批量删除大量数据,也可以增加新的分区来支持新插入的数据。另外,还可以对一个独立分区进行优化、检查、修复等操作
  3. 部分查询能够从查询条件确定只落在少数分区上,速度会很快
  4. 分区表的数据还可以分布在不同的物理设备上,从而搞笑利用多个硬件设备
  5. 可以使用分区表赖避免某些特殊瓶颈,例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
  6. 可以备份和恢复单个分区

分区的限制和缺点:

  1. 一个表最多只能有1024个分区
  2. 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
  3. 分区表无法使用外键约束
  4. NULL值会使分区过滤无效
  5. 所有分区必须使用相同的存储引擎

分区的类型:

  1. RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区
  2. LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
  3. HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
  4. KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值
  5. 具体关于mysql分区的概念请自行google或查询官方文档,我这里只是抛砖引玉了。

2.4 分表

分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次查询分成多次查询,然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分,通常以某个字段做拆分项。比如以id字段拆分为100张表: 表名为 tableName_id%100

但:分表需要修改源程序代码,会给开发带来大量工作,极大的增加了开发成本,故:只适合在开发初期就考虑到了大量数据存在,做好了分表处理,不适合应用上线了再做修改,成本太高!!!而且选择这个方案,都不如选择我提供的第二第三个方案的成本低!故不建议采用。

2.5 分库

把一个数据库分成多个,建议做个读写分离就行了,真正的做分库也会带来大量的开发成本,得不偿失!不推荐使用。

2.5 升级数据库

选用一款兼容MySql的高性能数据库。

2.6 换大数据引擎处理数据

当数据量过亿,可能实现无法支撑,只能更换Hadoop等大数据引擎来代替传统数据库。

或者购买专业的大数据云解决方案,比较便宜省人力。


参考:

🔗 记录一次MySQL两千万数据的大表优化解决过程,提供三种解决方案