Java学习笔记

大表优化过程

zszdevelop大约 11 分钟

大表优化过程

1. 场景问题

有个用户操作记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死。严重影响业务

2. 方案概述

  • 方案一：优化现有mysql数据库

    优点：不影响现有业务，源程序不需要修改代码，成本低

    缺点：有优化瓶颈，数据量过亿就玩不动了

  • 方案二：升级数据库类型

    换一种100%兼容mysql的数据库

    优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能

    缺点：多花钱

  • 方案三：一步到位，大数据解决方案，更换newsql/nosql数据库

    优点：扩展性强，成本低，没有数据容量瓶颈

    缺点：需要修改源程序代码

以上三种方案，按顺序使用即可，数据量在亿级别以下的没必要换nosql，开发成本太高。

3. 方案一：优化现有mysql数据库（重点）

总结：

  1. 数据库设计和表创建时就要考虑性能
  2. sql的编写需要注意优化
  3. 分区
  4. 分表
  5. 分库

3.1 数据库设计和表创建表时就要考虑性能

mysql 数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高。则mysql 性能高。

设计表时要注意：（非空，合适的类型，单表字段不超过20）

  1. 表字段避免null值出现，null 值很难查询优化且占用额外的索引空间。推荐默认数字0代替null。
  2. 尽量使用INT 而非BIGINT，如果非负则加上UNSIGNED (这样数值容量会扩大一倍)，当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
  3. 使用枚举或整数代替字符串类型
  4. 尽量使用TIMESTAMP 而非 DATETIME
  5. 表单不要有太多字段，建议在20以内
  6. 用整型来存IP

索引

  1. 索引并不是越多越好，要根据查询有针对性的创建，考虑在where和 order by 命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
  2. 应尽量避免在where 子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
  3. 值分布很少的字段不适合建索引，例如”性别“这种只有两三个值得字段
  4. 字符字段只建前缀索引
  5. 字符字段不要做主键
  6. 不用外键，由程序保证约束
  7. 尽量不用UNIQUE,由程序保证约束
  8. 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引

简言之就是使用合适的数据类型，选择合适的索引

原来开发人员跑路，措施无法执行，放弃

3.2 sql的编写需要注意的优化

  1. 使用limit 对数据查询的记录进行限定
  2. 避免select * ，将需要查询的字段列出来
  3. 使用连接（join）来代替子查询
  4. 拆分大的delete或insert语句
  5. 可通过开启慢查询日志来找出慢的SQL
  6. 不做列运算：SELECT id WHERE age+1=10，任何对列的操作都会导致表扫描，它包含数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
  7. sql语句尽可能简单：一条sql只能在一个cpu运算，大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
  8. OR 改写成IN: OR的效率是n级别，IN的效率是log（n）级别，in的个数建议控制在200以内
  9. 不用函数和触发器，在应用程序中实现
  10. 避免%xxx式查询
  11. 少用JOIN
  12. 使用同类型进行比较，比如用’123‘和’123‘比，123 和 123比
  13. 尽量避免在WHERE 子句中使用!= 或<> 操作符，否则将引擎放弃使用索引而进行全表扫描
  14. 对于连续数值，使用BETWEEN 不用 IN: SELECT id FROM t WHERE num BETWEEN I AND 5
  15. 列表数据不要拿全表，要使用LIMIT 来分页，每页数量也不要太大

引擎

使用innodb

3.3 分区

Mysql在5.1 版本引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。Mysql 实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

用户的SQL 语句是需要针对分区表做优化，SQL条件中带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，我测试，查询时不带分区条件的列，也会提高速度，故该措施值得一试。

分区的好处：

  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服务器提供其自身的哈希函数。必须有一列或多列包含整数值

我首先根据月份把上网记录表RANGE分区了12份，查询效率提高6倍左右，效果不明显，故：换id为HASH分区，分了64个分区，查询速度提升显著。问题解决!

结果如下：

PARTITION BY HASH (id)PARTITIONS 64 

select count() from readroom_website; --11901336行记录  
/ 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. /  
select * from readroom_website where month(accesstime) =11 limit 10;  
/ 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */

3.4 分表

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

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

但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本。故：只适合在开发初级就考虑到了大量数据存在，做好了分表处理，不合适应用上线了在做修改，成本太高！不建议采用

3.5 分库

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

4. 方案二：升级数据库，换一个100%兼容mysql的数据库

mysql性能不行，那就换个。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容mysql的数据库。

开源选择

  1. tiDB https://github.com/pingcap/tidbopen in new window
  2. Cubrid https://www.cubrid.org/open in new window
  3. 开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。

云数据库

  1. 阿里云POLARDB
  2. https://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wHopen in new window

官方介绍语：POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量可达 100T，性能提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。

5. 方案三详细说明：去掉mysql，换大数据引擎处理数据

数据量过亿了，没得选了，只能上大数据了。

开源解决方案

hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的!

云解决方案

这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。

国内做的比较好的当属阿里云。

我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。

MaxCompute可以理解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展现，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。

当然你也可以选择阿里云hbase等其他产品，我这里主要是离线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行sql，费用不超过100块钱就解决了数据处理问题。

面试真题

数据库都有哪些优化方式

  • 设计表时：合适的索引、合适的数据类型、非空，单表字段不超过20
  • 编写sql时：禁止一切会影响sql速度的语句
  • 分表：水平拆分和垂直拆分
  • 分库：读写分离

你做过的具体优化：

之前有个记录用户操作的日志表，有4000万条数据，我们要对数据进行相关统计的时候都非常慢。

当时我们

  1. 检查了索引是否合理（对操作动作和操作人加了索引）
  2. 对所有NULL字段设置默认值
  3. 当时还有一些sql写的很不规范
    1. select 把所有字段都列出来了，而不是需要查询的字段
    2. 把OR 改成了IN (OR的效率是n级别，IN的效率是log（n）级别)
    3. WHERE 子句中使用!= ，导致了放弃了索引进行了全表扫描
    4. 使用like 语句的时候，以%开头，导致全表扫描
  4. 当时也想过进行分表，不过单表字段其实不多，且需要改代码。就没做了

参考文章

记录一次MySQL两千万数据的大表优化解决过程，提供三种解决方案open in new window

这大概是最全的sql优化方案了open in new window

Loading...