Mysql 性能优化

huazi 2020-06-10 AM 82℃ 0条

数据库的性能可能从四个方面进行优化

效果上

SQL及索引 > 数据库表结构 > 系统配置 > 硬件

成本:

SQL及索引 < 数据库表结构 < 系统配置 < 硬件

Mysql 的优化是持续进行的,主要分为在两个阶段进行:

1.上线前的数据库设计(系统配置,数据库表结构及索引以及SQL语句)

2.上线后的持续监控优化(慢查询及性能监控)

产品整体的优化思路可能按照下面的原则逐步进行:

  • 第一步:采用分布式缓存redis、memcached等降低对数据库的读操作。
  • 第二步:如果缓存使用过后,数据库访问量还是非常大,可以考虑数据库读、写分离原则。
  • 第三步:当我们使用读写分离、缓存后,数据库的压力还是很大的时候,这就需要使用到数据库拆分了。

数据拆分

数据库拆分原则:通过某种特定的条件,按照某个维度,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面以达到分散单库(主机)负载的效果。

垂直拆分

一个数据库有很多表构成,可以根据业务类型将表分类,放到不同的数据库,这样就把数据库的压力进行了分摊。

比如电商平台,可以根据业务分为 用户数据库,店铺数据库,商品数据库,商品交易业务数据库等。

优点:

拆分后业务清晰,拆分规则明确;

系统之间整合或者扩展容易;

数据维护简单

缺点:

部分数据表之前无法 join,只能通过接口解决,提高了系统复杂度;

性能受单个数据库的瓶颈限制,不易数据库扩展和性能提高;

事务处理复杂

水平拆分

水平拆分的场景就是通常说的分库分表

相对于垂直拆分,水平拆分不是将表的数据做分类,而是按照某种字段的某种规则来分散到多个库中,每个表中

包含一部分数据。可以理解为 一个表中的0~1000行 在库 A 中,而 1001~2000 行在库 B 中……

切分策略

1.查询切分

将 ID 和 数据库的映射关系保存在一个单独的库中

查询切分

优点:ID和库的Mapping算法可以随意更改。
缺点:引入额外的单点。

2.范围切分

按照时间或者 ID 区间进行切分

范围切分

优点:单表大小可控,天然水平扩展。
缺点:无法解决集中写入瓶颈的问题。

3.Hash 切分

使用 mod (求模)运算进行切分

hash切分

数据水平切分后我们希望是一劳永逸或者是易于水平扩展的,所以推荐采用 mod 2^n 这种一致性Hash。

以统一订单库为例,我们分库分表的方案是 32*32 的,即通过 UserId 后四位 mod 32 分到 32 个库中,同时再将UserId后四位 Div 32 Mod 32 将每个库分为 32 个表,共计分为 1024 张表。线上部署情况为 8 个集群(主从),每个集群4个库。

配置参数优化

如果使用的是 InnoDB,在参数优化的选项里最重要的有两个:

  • innodb_buffer_pool_size
  • innodb_log_file_size

innodb_buffer_pool_size:

InnoDB使用一个缓冲池来保存索引和原始数据,如下图所示:

InnoDB存储引擎内存结构

如果是在独立的服务器上运行 mysql 服务,按照经验,可以将缓存池大小设置为服务器内存的 70%~80%。如果还是有其他的服务,则需要减去其他服务所占的内存。

innodb_log_file_size:

InnoDB使用日志来减少提交事务时的开销。

InnoDB用日志把随机I/O变成顺序I/O。

img

当一个日志文件写满后,innodb 会自动切换到另一个日志文件,而且会触发数据库的checkpoint,这回导致innodb缓存脏页的小批量刷新,会明显降低innodb的性能。如果innodb_log_file_size设置太小,就会导致innodb频繁地checkpoint,导致性能降低。而如果设置太大,由于事务日志是顺序I/O,大大提高了I/O性能,但是在崩溃恢复InnoDB时,会导致恢复时间变长。

数据表结构设计优化

InnoDB 逻辑存储结构

InnoDB 所有数据都存在一个叫做表空间(tablespace)的地方(ibdata1)中。表空间由段(segment)、区(extent)、页(page)组成。InnoDB逻辑存储存储结构如下图:

1560834709696

(1)段(segment)

常见的数据段有 数据段、索引段、回滚段

(2)区(extent)

区是由连续的页组成的空间,每个区的大小为1M。为保证区里面 页 的连续性,InnoDB 引擎一次从磁盘中申请4~5个区。默认情况下,InnoDB 引擎页为 16KB,即一个区中一共有64个连续的页。

(3)页(page)

页是InnoDB磁盘管理的最小单位,默认每个页大小为16KB。常见的页类型有:

  • 数据页(B-tree Node)
  • undo页(undo Log Page)
  • 系统页(System Page)
  • 事务数据页(Transaction system Page)
  • 插入缓冲位图页(Insert Buffer Bitmap)
  • 插入缓冲空闲列表页(Insert Buffer Free List)
  • 未压缩的二进制大对象页(Uncompressed BLOB Page)
  • 压缩的二进制大对象页(compressed BLOB Page)
(4)行

InnoDB存储引擎是按行进行存放的。

一个页中存放的行数据越多,其性能越高,这也是为什么创建字段的时候应该按照最小可用原则。

索引设计优化

索引类型主要有 B+树索引、聚集索引、辅助索引

用的最多的是 B+树索引。

设计和使用索引时,可以遵循以下原则:
  • 选择数据类型遵循小而简单的原则,这样做的好处是可以节省索引空间,对于较短的键值,索引页中能容纳更多的键值,这样查找速度也会提升。下表是mysql各个字段类型的大小和使用范围: 字段 存储大小(单位:字节) 最小值 最大值 TINYINT 1 -128 127 SMALLINT 2 -32768 32767 MEDIUMINT 3 -8,388,608 8,388,607 (838萬) INT 4 -2,147,483,648 2,147,483,647 (21億) BIGINT 8 -9,223,372,036,854,775,808 9,223,372,036,854,775,807 (922京) FLOAT 4 -3.402823466E+38 3.402823466E+38 DOUBLE 8 -1.7976931348623157E+308 1.7976931348623157E+308 DECIMAL* 每9个数字4个字节 ~ -1E+66 ~ 1E+66 TIMESTAMP 4 1970(unix时间戳) 2038(unix时间戳) DATETIME 8 1001年 9999年 所以,如果存储IP地址,使用UNSIGNED INT存储,刚好够用,比使用字符串占用更少空间,搜索更快。 同样的。而时间使用DATETIME存储,比使用字符串(19字节)足足少了11字节。
  • 整形数据比起字符,处理开销更小,在MySQL中,建议使用内置的日期和时间数据类型,而不是用字符串来存储时间。
  • 利用覆盖索引进行查询,避免回表。Explain返回的Using index就代表从索引中查询。这也是为什么要避免使用SELECT *的原因之一。
  • 尽量指定列为NOT NULL,NULL会使索引、索引统计和值更加复杂,并且需要额外的存储空间。这个可以查看这篇文章《一千个不用NULL的理由》
  • 建议在选择性高的列上建立索引,最好是唯一索引,区分度越大,则我们扫描的记录数越少,例如性别区分度不大,就不适合做索引。
  • 更新非常频繁的数据不适合建索引。频繁更新会导致变更B+索引树,重建索引,这个过程很消耗数据库性能。
  • 利用最左前缀原则,比如建立一个联合索引(a,b,c),我们可以利用的索引就有(a),(a,b),(a,b,c)
  • 如果确定有多少条数据,使用limit限制一下,MySQL在查找到对应条数的数据的时候,会停止继续查找
  • 删除不再使用的索引
  • join语法,尽量将小的表放在前面,在需要on的字段上,数据类型保持一致,并设置索引,否则MySQL无法使用索引来 Join 查询
  • like “xxx%”可以用到索引,like”%xxx%”则不行
  • 在设计开发阶段,数据库字段的定义要避免出现由数据类型定义不当造成的隐式转换

参考:

《MySQL性能优化系统整理》 | shuwoom.com

阿里P8架构师谈(数据库系列):架构设计之数据库垂直、水平拆分六大原则

大众点评订单系统分库分表实践

标签: Mysql

非特殊说明,本博所有文章均为博主原创。

评论啦~