mysql 递归查询_MySQL、Redis 的性能及优化

常用的数据库有关系型的 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 进行基准测试.

mysql 性能测试

通过 redis-benchmark 测试的读写性能:

redis 性能测试

上述 benchmark 测试中可以得出数据库读写性能分别如下:

性能对比

可以看到，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 优化方法.

1. 缩短键值对的存储长度；
2. 使用 lazy free(延迟删除)特性
3. 设置键值的过期时间;
4. 禁用长耗时的查询命令；
5. 使用 slowlog 优化耗时命令；
6. 使用 Pipeline 批量操作数据；
7. 避免大量数据同时失效；
8. 客户端使用优化；
9. 限制 Redis 内存大小；
10. 使用物理机而非虚拟机安装 Redis 服务；
11. 检查数据持久化策略；
12. 禁用 THP 特性；
13. 使用分布式架构来增加读写速度。

参考资料

1. MySQL 数据库优化全剖析[https://www.youtube.com/playlist?list=PLD3Xyx6ef38ynWdMi2xY-JRPqfhgtc1Lv]