常用的数据库有关系型的 MySQL、非关系型的 Redis 等数据库,它们可以很好的应用于不同场景. 了解数据库的性能,可以灵活的应对不同的业务场景.
在当前的众多 IT 建设中,MySQL 和 Redis 是十分常见的俩款数据库,它们有着不同的业务场景. 而如今全民上网的时代,互联网活动众多,数据量巨大. 在各大活动中互联网服务的性能也将极大影响用户的体验.
对于 MySQL 和 Redis 的性能优化是有必要掌握的知识. 本文主要考虑以下数据库及性能测试工具:
- Redis: Redis 是一种高性能 key-value 存储的开源 NoSQL 数据库.
- MySQL: MySQL 是最常用的开源关系型数据库,它可以持久化存储大量的数据.
- SysBench: 性能测试工具,支持 IO、CPU、Mutex、内存分配、POSIX 线程、MySQL、PostgreSQL、Oracle 等性能测试.
- Redis-Benchmark: Redis 的性能测试工具.
下文将对 Redis 及 MySQL 的性能进行测试. 并梳理了一些常用的 MySQL 优化及 Redis 优化方法.
数据库读写性能
2.1 读写性能测试
通过 sysbench 以及 redis-benchmark 在以下环境下分别对 MySQL 及 Redis 进行了测试.
- 系统: docker/linux
- CPU: 6 核
- 内存: 2G
- 磁盘: Mac 闪存
如下所示, 通过 sysbench 对 MySQL 进行基准测试.
![babddaf90b2f42bbe3f9ee4d3d675176.png](https://i-blog.csdnimg.cn/blog_migrate/2a22fb1ffda748fb5b8a37e619a92fda.jpeg)
mysql 性能测试
通过 redis-benchmark 测试的读写性能:
![0a736d1d2d50de4dc0cb3bf80868f11e.png](https://i-blog.csdnimg.cn/blog_migrate/60018e16692dab57fc3f54cfa0f05b16.jpeg)
redis 性能测试
上述 benchmark 测试中可以得出数据库读写性能分别如下:
![425811adf5823c392aeb017c62a8355f.png](https://i-blog.csdnimg.cn/blog_migrate/de7b2ed1824629bab938b999c4b2b332.jpeg)
性能对比
可以看到,Redis 的读写速度显著高于 MySQL,大约在 10 倍左右. 这是由于 MySQL 的数据主要存储在硬盘内,而 Redis 则主要存储于内存中. 在《Redis in Action》中提到,Redis 的性能会是普通关系型数据库的 10 - 100 倍.
本文测试使用的 Mac 闪存大约是普通速度的 9 倍.借助本文数据可以推算出的 Redis 的读速度是普通硬盘下 MySQL 的 70 倍. 也是符合《Redis in Action》给出的关系型数据库及 MySQL 性能对比的.
2.2 性能与场景选择
两种数据库对比,性能上 Redis 占优势. 但是在持久化存储以及关系型上,MySQL 更占优势. 这些特点也让它们能够分别适用于不同场景.
Redis 的使用场景主要如下:
- 缓存: 一些经常读取的数据可以通过 redis 进行缓存,提高访问速度.
- 消息队列: Redis 支持 list 数据结构,它可以用来做简单的消息队列,通过 lpush、rpop 来完成入队、出队操作.
- 计数器: 日注册用户数、点赞数、评论数、转发数等.
- 排行榜: redis 提供了 zset 这种有序、唯一的集合. 相比直接在数据库进行 order by 获取,redis 排行榜更高效.
- 分布式锁: 由于 redis 是单线程的. 通过 redis 提供的 setnx、getset 可以实现分布式锁.
- 结合秒杀系统: 通过缓存机制提高可负载并发量,在秒杀结束后,再将数据插入数据库.
MySQL 则主要用于持久化数据的存储,尤其是一些关系型数据,在对性能没有秒杀、高并发等的特殊要求时,通常都会先选择 MySQL 进行数据存储. 在有性能需求时,再在它的基础上进一步优化. 大部分 web 站点存储都需要用到 MySQL 关系型数据库. 以下是一些常用的使用场景:
- Web 网站系统
- 日志记录系统
- 数据仓库系统
性能调优
3.1 MySQL 性能调优
一般在进行数据库开发设计时,不会过多考虑性能的问题,但是当数据量急剧上升,业务暴涨时,需要能及时定位到性能问题,并进行优化. 下面是一些定位及优化 sql 语句的方法.
1> 查看 sql 操作的执行频率.
show global status like 'Com_______';show global status like 'Innodb_rows_%';
2> 定位低效 sql
show processlistUserIdHostdbCommandTimeStateInfo
3> 优化方式
针对 sql 的优化,可以参考下列的一些原则:
- 改进模式
- 在磁盘上使用连续的块存储 MySQL 数据.
- 固定长度选择 char 类型而不是 varchar
- varchar 在读取字符串时需要读取到字符串末尾,char 则在快速、随机访问时效率很高.
- INT 类型存储较大数字 (40 亿、2^ 32 级别)
- DECIMAL 类型存储货币可以避免浮点数表示错误
- 避免用 blob 类型存储对象,而是存储对象的位置
- 适当的使用 NOT NULL 约束提升搜索性能
- 索引的使用
- select、group by、order by、join 等操作的列使用索引更快
- 索引通常自平衡的 B 树,可以保持数据有序
- 设置索引会将数据存储在内存中,占用内存空间
- 设置索引后写入操作会变慢,因为写数据需要更新索引
- 加载大量数据时,禁用索引再加载数据,再重建索引,可能更快
- 避免高成本的联接操作
- 分割数据表
- 热点数据拆分到单独的数据表中,有助于缓存
- 调优查询缓存
以下给出了常见的 sql 语句优化方式:
insert 优化: 多个数据插入一张表尽量使用一条 insert 而非多个 insert. 或者使用一个事务. 多个 insert 尽量保证数据是有序插入的.
order by 优化: MySQL 有 filesort 通过文件系统的排序和 using index 通过索引的排序.
- 这一部分优化建议是减少额外排序,通过索引返回数据;where 和 order by 使用相同索引; order by 的顺序与索引顺序相同; 保持全部升序 asc 或降序 desc; 从而避免 filesort.
- filesort 优化: mysql 的 filesort 有两种排序算法,两次扫描算法以及一次扫描算法. 其中一次扫描算法内存开销大但效率更高.
group by 优化: group by 也会进行排序操作, 进行分组时也要尽量使用索引. 可以通过 order by 避免 group by 排序结果带来的消耗.
子查询(嵌套查询)优化: 尽量使用多表连接查询替换子查询.
or 优化: 建议使用 union 替换 or.
limit(分页查询) 优化: 在索引上完成分页排序操作,再使用主键获得所需要的列内容. 将 limit 查询转换成某个位置的查询.
4> explain 查看执行计划
id: 操作表的顺序,当多表操作时,代表表的执行顺序. 如果 id 相同,从上至下依次加载. 若不同,先查询 id 大的表.
select_type: 表示 select 的类型.
- SIMPLE: 简单的 select 查询、不包含子查询或 UNION.
- PRIMARY: 查询中包含复杂子查询.
- SUBQUERY: select 或 where 中包含子查询.
- DERIVED: 在 from 列表中的子查询标记为 DERIVER,MySQL 会递归执行子查询放在临时表中.
- UNION: 若第二个 SELECT 出现在 UNION 后,则标记为 UNION. 若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为 DERIVED.
- UNION RESULT: 从 UNION 表获取结果的 SELECT.
table: 展示关于那一张表.
type: 访问类型,是重要的一个指标. 通过 type 可以大致了解耗时情况. 一般需要保证查询至少达到 range ,最好达到 ref 级别. 以下是 type 的类型,从上到下性能依次变低.
- NULL: 不访问任何表、索引( 如 select now() ),直接返回结果. 性能最高.
- system: 表仅有一行记录,const 类型的特例,一般不出现.
- const: 通过索引一次就找到. 只匹配一行数据,所以快.
- eq_ref: 类似 ref. 区别在于使用的是唯一索引. 使用主键的关联查询. 关联查询出的记录只有一条.
- ref: 非唯一性索引扫描,返回匹配某单独值的所有行. 本质上是索引访问,返回所有匹配某个单独值的所有行.
- range: 只检索给定返回的行,使用一个索引选择行. where 之后出现 between, , in 等操作.
- index: 与 ALL 区别为 index 只遍历了索引树,比 ALL 快.
- all: 将遍历全表找到匹配的行. 遍历了数据文件.
possible_keys: 显示可能应用在这张表的一个或多个的索引.
key: 实际使用的索引. NULL 则表示没有使用索引.
key_len: 表示索引中使用的字节数,该值为索引字段最大可能的长度,在保证精度的情况下该值越小越好.
ref: 显示索引的哪一列被使用了
rows: 扫描的行的数量.
extra: 其它的执行计划信息.
5> 优化工具
- trace 工具: 可以查看优化器的执行计划.
- show profile: 可以查看 sql 语句的耗时详情,可以看到时间具体耗费的阶段.
3.2 Redis 性能优化
Redis 也是十分常用的数据库,它是一种非关系型的 kv 键值对存储器. Redis 是基于单线程模型的,数据主要存储于内存中,它的性能要求也是十分高的. 下面是一些常见的 Redis 优化方法.
- 缩短键值对的存储长度;
- 使用 lazy free(延迟删除)特性
- 设置键值的过期时间;
- 禁用长耗时的查询命令;
- 使用 slowlog 优化耗时命令;
- 使用 Pipeline 批量操作数据;
- 避免大量数据同时失效;
- 客户端使用优化;
- 限制 Redis 内存大小;
- 使用物理机而非虚拟机安装 Redis 服务;
- 检查数据持久化策略;
- 禁用 THP 特性;
- 使用分布式架构来增加读写速度。
参考资料
1. MySQL 数据库优化全剖析[https://www.youtube.com/playlist?list=PLD3Xyx6ef38ynWdMi2xY-JRPqfhgtc1Lv]