优化

SQL 语句优化

  1. 不要使用 select *
  2. 减少子查询,使用关联查询 left join right join inner join 替代
  3. 减少使用 IN NOT IN,使用 exists not exists 或者关联查询语句替代
  4. or 尽量用 union 或者 union all 代替,在确认没有重复数据或者不用剔除重复数据时 union all 会更好
  5. 避免在 where 中使用 !=<> 和对字段进行 null 值判断,否则将引擎放弃使用索引而进行全表扫描
  6. 不做列运算,任何对列的操作都将导致表扫描
  7. 语句尽可能简单,一条语句只能在一个 CPU 运算,拆分语句以减少锁时间,一条大语句搞不好可以堵死整个库
  8. 不用函数和触发器,在程序内实现
  9. 列表数据不要拿全表,要使用 LIMIT 来分页,每页数量也不要太大
  10. 使用同类型进行比较
  11. 对于连续数值,使用 BETWEEN 不用 IN,SELECT id FROM t WHERE num BETWEEN 1 AND 5
  12. 可通过开启慢查询日志来找出较慢的 SQL

读写分离

经典的数据库拆分方案,主库负责写,从库负责读。 一般不要采用双主或多主引入很多复杂性,尽量采用其他方案来提高性能。 同时目前很多拆分的解决方案同时也兼顾考虑了读写分离。

字段设计

  • 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT,非负则加上 UNSIGNED
  • VARCHAR 的长度只分配真正需要的空间
  • 使用枚举或整数代替字符串类型
  • 尽量使用 TIMESTAMP 而非 DATETIME
  • 单表不要有太多字段,建议在 20 以内
  • 避免使用 NULL 字段,很难查询优化且占用额外索引空间
  • 用整型来存 IP

索引

缓存

  • MariaDB 内部:系统调优参数
  • 数据访问层:比如 MyBatis 针对 SQL 语句做缓存,Hibernate 可以精确到单个记录,这里缓存的对象主要是持久化对象
  • 应用服务层:这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略,这里缓存的对象是数据传输对象
  • Web 层:针对 Web 页面做缓存
  • 浏览器客户端:用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。 这里重点介绍下服务层的缓存实现,目前主要有两种方式:

  1. 直写式:在数据写入数据库后,同时更新缓存,维持数据库与缓存的一致性。 这也是当前大多数应用缓存框架的工作方式。这种实现非常简单,同步好,但效率一般。

  2. 回写式:当有数据要写入数据库时,只会更新缓存,然后异步批量的将缓存数据同步到数据库上。 这种实现比较复杂,需要较多的应用逻辑,同时可能会产生数据库与缓存的不同步,但效率非常高。

分区

扩展 里提到的垂直扩展、水平扩展。

  • 垂直扩展根据数据库里面数据表的相关性进行拆分。

例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。 简单来说是指数据表列的拆分,把一张列比较多的表拆分为多张表。 可以使得列数据变小,在查询时减少读取的 Block 数,减少 I/O,简化表的结构,易于维护。 但主键会出现冗余,需要管理冗余列,并会引起 Join 操作,让事务变得更加复杂。

  • 水平扩展是指数据表行的拆分,把一张的表的数据拆成多张表来存放。

例如,将用户信息表拆分成多个用户信息表,这样就可以避免单一表数据量过大对性能造成影响。 水平扩展可以支持非常大的数据量,但分表仅解决了单一表数据过大的问题,表的数据还是在同一台机器上,对并发能力没有什么提升,最好分库。 水平扩展支持非常大的数据量存储,应用端改造也少,但分片事务难以解决,跨节点 Join 性能较差,逻辑复杂。 尽量不要对数据进行分片,因为拆分会提升逻辑、部署、运维的复杂度。 如果实在要分片,尽量选择客户端分片架构,这样可以减少一次和中间件的网络 I/O。

分片原则

  • 能不分就不分
  • 分片数量尽量少,分片尽量均匀分布在多个数据结点上

因为一个查询 SQL 跨分片越多,则总体性能越差,虽然要好于所有数据在一个分片的结果,只在必要的时候进行扩容,增加分片数量。

  • 分片规则需要慎重选择做好提前规划

分片规则的选择,需要考虑数据的增长模式,数据的访问模式,分片关联性问题,以及分片扩容问题。 分片策略为范围分片,枚举分片,一致性 Hash 分片,这几种分片都有利于扩容。

  • 尽量不要在一个事务中的 SQL 跨越多个分片,分布式事务一直是个不好处理的问题

  • 查询条件尽量优化

尽量避免大结果集,这会消耗大量带宽和 CPU 资源,尽量为频繁使用的查询语句建立索引。

  • 通过数据冗余和表分区赖降低跨 Join 的可能

如果某个表的数据有明显的时间特征,比如订单、交易记录等,则他们通常比较合适用时间范围分片。 因为具有时效性的数据,往往关注其近期的数据,查询条件中往往带有时间字段进行过滤。 比较好的方案是,当前活跃的数据,采用跨度比较短的时间段进行分片,而历史性的数据,则采用比较长的跨度存储。

总体上来说,分片的选择是取决于最频繁的查询 SQL 的条件,因为不带任何 Where 语句的查询 SQL,会遍历所有的分片,性能相对最差。 这种 SQL 越多,对系统的影响越大,要尽量避免这种 SQL 的产生。

客户端架构

分片逻辑在应用端,例如 Tumblr 的 JetPants。

优点:

  • 应用直连数据库,降低外围系统依赖所带来的宕机风险
  • 集成成本低,无需额外运维的组件

缺点:

  • 限于只能在数据库访问层上做文章,扩展性一般,对于比较复杂的系统可能会力不从心
  • 将分片逻辑的压力放在应用服务器上,造成额外风险

中间件架构

分片逻辑在中间件服务中,在应用和数据中间加了一个代理层,例如 Youtube 的 Vitess。

优点:

  • 能够处理非常复杂的需求,不受数据库访问层原来实现的限制,扩展性强
  • 对于应用服务器透明且没有增加任何额外负载

缺点:

  • 需部署和运维独立的代理中间件,成本高
  • 应用需经过代理来连接数据库,网络上多了一跳,性能有损失且有额外风险

方案选择

  1. 确定架构,中小型规模或是比较简单的场景倾向于选择客户端架构,复杂场景或大规模系统倾向选择中间件架构
  2. 功能是否满足,比如需要跨节点 ORDER BY,那么支持该功能的优先考虑。
  3. 活跃度,不考虑开发停滞、无人维护的项目
  4. 开源优先,因为可能会有特殊需求要改动源码

系统调优参数

具体的调优参数内容较多,建议直接参考官方文档。

  • back_log 在暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中

如果连接数达到 max_connections,新请求将会被存在堆栈中以等待某一连接释放资源。 如果等待连接的数量超过 back_log,新请求会被拒绝。可以从默认的 50 升至 500。

  • wait_timeout 数据库连接闲置时间

闲置连接会占用内存资源,可以从默认的 8 小时减到半小时。

  • max_user_connection 最大连接数

默认为0无上限,最好设一个合理上限。

  • thread_concurrency 并发线程数

设为 CPU 核数的两倍。

  • skip_name_resolve 禁止对外部连接进行 DNS 解析

可以消除 DNS 解析时间,但需要所有远程主机用 IP 访问。

  • key_buffer_size 索引块的缓存大小

增加会提升索引处理速度,对 MyISAM 表性能影响大。 对于内存 4G 左右,可设为 256M 或 384M。 通过查询 show status like 'key_read%',保证 key_reads / key_read_requests 在 0.1% 以下最好。

  • innodb_buffer_pool_size 缓存数据块和索引块

对 InnoDB 表性能影响大。 通过查询 show status like 'Innodb_buffer_pool_read%' 保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests 越高越好。

  • innodb_additional_mem_pool_size InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小

当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率。 当过小的时候,数据库会记录 Warning 信息到数据库的错误日志中,这时就需要该调整这个参数大小。

  • innodb_log_buffer_size InnoDB 存储引擎的事务日志所使用的缓冲区

一般来说不建议超过 32MB。

  • query_cache_size 缓存 MariaDB 中的 ResultSet

就是一条SQL语句执行的结果集,只能针对 select 语句。 当某个表的数据有任何任何变化,都会导致所有引用了该表的 select 语句在 Query Cache 中的缓存数据失效。 当数据变化非常频繁的情况下,使用 Query Cache 可能会得不偿失。 根据命中率 (Qcache_hits/(Qcache_hits+Qcache_inserts)*100)) 进行调整,一般 256MB 差不多了,大型的配置型静态数据可适当调大。 可以通过命令 show status like 'Qcache_%' 查看目前系统 Query catch 使用大小。

  • read_buffer_size 读入缓冲区大小

对表进行顺序扫描的请求将分配一个读入缓冲区,会为它分配一段内存缓冲区。 如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能。

  • sort_buffer_size 执行排序使用的缓冲大小

如果想要增加 ORDER BY 的速度,首先看是否可以让 MariaDB 使用索引而不是额外的排序阶段。 如果不能,可以尝试增加变量的大小。

  • read_rnd_buffer_size 随机读缓冲区大小

当按任意顺序读取行时,将分配一个随机读缓存区。 进行排序查询时,会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。 但 MariaDB 会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

  • record_buffer 顺序扫描缓冲区

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果有很多顺序扫描,可以增加该值。

  • thread_cache_size 缓存连接服务的线程

保存当前没有与连接关联但是准备为后面新的连接服务的线程,可以快速响应连接的线程请求而无需创建新的。

  • table_cache 缓存表文件

类似于 thread_cache_size,但用来缓存表文件,对 InnoDB 效果不大,主要用于 MyISAM。

powered by Gitbook该文件修订时间: 2020-04-10 10:05:54

results matching ""

    No results matching ""

    results matching ""

      No results matching ""