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: 扫描的⾏的数量.
redis是nosql数据库吗
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. 使⽤分布式架构来增加读写速度。
参考资料

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。