磊哥 | 作者
Java中文社群 | 来源
https://mp.weixin.qq.com/s/ZosZWVeME3V652uO1yrdoA
当然,本篇是关于性能优化的,那性能优化就应该一把梭子吗?还是要符合一些规范和原则呢?
所以,在开始之前(MySQL 优化),咱们先来聊聊 性能优化 的一些原则。
主动优化
被动优化
所谓的 主动优化 是指 不需要外力的推动而自发进行的一种行为, 比如当服务没有明显的卡顿、宕机或者硬件指标异常的情况下, 自我出发去优化的行为, 就可以称之为主动优化。
而 被动优化 刚好与主动优化相反,它是指 在发现了服务器卡顿、服务异常或者物理指标异常的情况下,才去优化的这种行为。
无论是主动优化还是被动优化都要符合 以下性能优化的原则:
优化不能改变服务运行的逻辑,要保证服务的正确性
优化的过程和结果都要保证服务的安全性
要保证服务的稳定性,不能为了追求性能牺牲程序的稳定性,比如: 不能为了提高 Redis 的运行速度,而关闭持久化的功能,因为这样在 Redis 服务器重启或者掉电之后会丢失存储的数据。
以上原则看似都是些废话,但却给了我们一个启发,那就是我们性能优化手段应该是: 预防性能问题为主+被动优化为辅。
也就是说,我们应该 以预防性能问题为主 ,在开发阶段尽可能的规避性能问题,而 在正常情况下,应尽量避免主动优化,以防止未知的风险 (除非是为了 KPI,或者是闲的没事),尤其对生产环境而言更是如此, 最后才是考虑被动优化 。
PS: 当遇到性能缓慢下降、或硬件指标缓慢增加的情况,如今天内存的占用率是 50%,明天是 70%,后天是 90% ,并且丝毫没有收回的迹象时,我们应该提早发现并处理此类问题(这种情况也属于被动优化的一种)。
所以我们本文会重点介绍 MySQL 被动性能优化的知识,根据被动性能优化的知识,你就可以得到 预防性能问题发生的一些方法 ,从而规避 MySQL 的性能问题。
本文我们会从问题入手,然后考虑这个问题产生的原因以及相应的优化方案。我们在实际开发中,通常会遇到 以下 3 个问题:
单条 SQL 运行慢
部分 SQL 运行慢
整个 SQL 运行慢
问题分析
造成单条 SQL 运行比较慢的常见原因 有以下两个:
未正常创建或使用索引
表中数据量太大
解决方案
索引 是一种能 帮助 MySQL 提高查询效率的主要手段 ,因此一般情况下我们遇到的单条 SQL 性能问题,通常都是由于未创建或为正确使用索引而导致的,所以在遇到单条 SQL 运行比较慢的情况下,你首先要做的就是 检查此表的索引是否正常创建。
如果表的索引已经创建了,接下来就要 检查一下此 SQL 语句是否正常触发了索引查询, 如果发生以下情况那么 MySQL 将 不能正常的使用索引:
在 where 子句中使用 != 或者 <> 操作符,查询引用会放弃索引而进行全表扫描;
不能使用前导模糊查询,也就是 '%XX' 或 '%XX%',由于前导模糊不能利用索引的顺序,必须一个个去找,看是否满足条件,这样会导致全索引扫描或者全表扫描;
如果条件中有 or 即使其中有条件带索引也不会正常使用索引,要想使用 or 又想让索引生效,只能将 or 条件中的每个列都加上索引才能正常使用;
在 where 子句中对字段进行表达式操作。
因此你要尽量避免以上情况,除了正常使用索引之外,我们也可以 使用以下技巧来优化索引的查询速度:
尽量使用主键查询,而非其他索引,因为主键查询不会触发回表查询;
查询语句尽可能简单,大语句拆小语句,减少锁时间;
尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型;
用 exists 替代 in 查询;
避免在索引列上使用 is null 和 is not null。
回表查询:普通索引查询到主键索引后,回到主键索引树搜索的过程,我们称为回表查询。
当表中数据量太大时 SQL 的查询会比较慢,你可以考虑 拆分表 ,让每张表的 数据量变小,从而提高查询效率。
垂直拆分
指的是将表进行拆分, 把一张列比较多的表拆分为多张表。
比如: 用户表中一些字段经常被访问,将这些字段放在一张表中,另外一些不常用的字段放在另一张表中,插入数据时,使用事务确保两张表的数据一致性。
把不常用的字段单独放在一张表;
把 text,blob 等大字段拆分出来放在附表中;
经常组合查询的列放在一张表中。
水平拆分
指的是将数据表行进行拆分,表的行数超过200万行时,就会变慢,这时可以 把一张的表的数据拆成多张表来存放。
通常情况下,我们使用 取模 的方式来进行表的拆分。
比如: 一张有 400W 的用户表 users,为提高其查询效率我们把其分成 4 张表 users1,users2,users3,users4,然后通过用户 ID 取模的方法,同时查询、更新、删除也是通过取模的方法来操作。
使用可以存下数据最小的数据类型;
使用简单的数据类型,int 要比 varchar 类型在 MySQL 处理简单;
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int;
尽可能使用 not null 定义字段,因为 null 占用 4 字节空间;
尽量少用 text 类型,非用不可时最好考虑分表;
尽量使用 timestamp,而非 datetime;
单表不要有太多字段,建议在 20 个字段以内。
问题分析
部分 SQL 运行比较慢,我们首先要做的就是 先定位 出这些 SQL,然后再看这些 SQL 是否正确创建并使用索引。也就是说,我们先要使用慢查询工具定位出具体的 SQL,然后再使用问题 1 的解决方案处理慢 SQL。
解决方案
MySQL 中自带了 慢查询日志 的功能,开启它就可以用来 记录在 MySQL 中响应时间超过阀值的语句 ,具体指 运行时间超过 long_query_time 值的 SQL ,则会被记录到慢查询日志中。
long_query_time 的默认值为 10,意思是运行 10S 以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们 手动来设置 这个参数, 如果不是调优需要的话,一般不建议启动该参数, 因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。
慢查询日志支持将日志记录写入文件,也支持 将日志记录写入数据库表。 使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启, 执行效果如下图所示:
slow_query_log 的值为 OFF 时,表示未开启慢查询日志。
开启慢查询日志 ,可以 使用如下 MySQL 命令:
mysql> set global slow_query_log=1
不过这种设置方式, 只对当前数据库生效 ,如果 MySQL 重启也会失效,如果要永久生效,就必须 修改 MySQL 的配置文件 my.cnf, 配置如下:
slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log
当你开启慢查询日志之后,所有的慢查询 SQL 都会被记录在 slow_query_log_file 参数配置的文件内,默认是 /tmp/mysql_slow.log 文件,此时我们就可以打开日志 查询到所有慢 SQL 进行逐个优化。
问题分析
当出现整个 SQL 都运行比较慢就说明目前数据库的承载能力已经到了峰值,因此我们需要使用一些 数据库的扩展手段来缓解 MySQL 服务器了。
解决方案
一般情况下对数据库而言都是 “读多写少” ,换言之,数据库的压力多数是因为大量的读取数据的操作造成的,我们可以采用 数据库集群 的方案,使用一个库作为 主库 ,负责写入数据;其他库为 从库 ,负责读取数据。这样可以缓解对数据库的访问压力。
(1)应用层解决方案
可以通过 应用层对数据源做路由 来实现读写分离。
比如: 使用 SpringMVC + MyBatis,可以将 SQL 路由交给 Spring,通过 AOP 或者 Annotation 由代码显示的控制数据源。
优点:路由策略的扩展性和可控性较强。
缺点:需要在 Spring 中添加耦合控制代码。
(2)中间件解决方案
通过 MySQL 的中间件做主从集群。
比如: Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。
优点:与应用层解耦。
缺点:增加一个服务维护的风险点,性能及稳定性待测试,需要支持代码强制主从和事务。
在 MySQL 中我们可以使用 explain 命令来分析 SQL 的执行情况, 比如:
explain select * from t where id=5;
id: 选择标识符,id 越大优先级越高,越先被执行;
select_type: 表示查询的类型;
table: 输出结果集的表;
partitions: 匹配的分区;
type: 表示表的连接类型;
possible_keys: 表示查询时,可能使用的索引;
key: 表示实际使用的索引;
key_len: 索引字段的长度;
ref: 列与索引的比较;
rows: 大概估算的行数;
filtered: 按表条件过滤的行百分比;
Extra: 执行情况的描述和说明。
all: 扫描全表数据;
index: 遍历索引;
range: 索引范围查找;
index_subquery: 在子查询中使用 ref;
unique_subquery: 在子查询中使用 eq_ref;
ref_or_null: 对 null 进行索引的优化的 ref;
fulltext: 使用全文索引;
ref: 使用非唯一索引查找数据;
eq_ref: 在 join 查询中使用主键或唯一索引关联;
const: 将一个主键放置到 where 后面作为条件查询, MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点。
本文我们介绍了 MySQL 性能优化的原则和分类,MySQL 的性能优化可分为:主动优化和被动优化,但无论何种优化都要 保证服务的正确性、安全性和稳定性。 它带给我们的启发是应该采用:预防 + 被动优化的方案来确保 MySQL 服务器的稳定性,而 被动优化常见的问题是:
单条 SQL 运行慢
部分 SQL 运行慢
整个 SQL 运行慢
因此我们给出了每种被动优化方案的问题分析和解决方案,希望本文可以帮助到你。
数据备份是一个网站能够正常运营的保障,数据备份包括网站源码备份和数据库备份,如果你使用的是ACCESS数据库,那么直接使用FTP下载数据库文件就可以了,但如果你使用了PHP+MYSQL进行网站建设,数据库备份就没有那么容易了。
如果你关注大数据科技动向,你对 NoSQL 一定不陌生,NoSQL 是一个分布式数据库。在过去时间,数据存储一直关系型数据库天下,有着良好的控制并发操作、事务功能。
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引。这种说法其实是不够正确的,正确的结论应该是
由于websql操作都是异步操作,当我们为了获取到websql操作的结果之后再进行后续操作时,往往是通过回调函数来实现的,当回调一多的时候,回调地狱就出现了,为了解决回调地狱问题,我将通过Promise来改写,后续调用时
left join(左联接) :返回包括左表中的所有记录和右表中联结字段相等的记录;right join(右联接) :返回包括右表中的所有记录和左表中联结字段相等的记录
下面将以 MySQL 社区的优先级从高到低来展示这些功能:MySQL 文档存储;默认 utf8mb4 编码;JSON 增强;CTEs(译者注:Common Table Expresssions 公共表格表达式)
日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的SQL,现在分享给你!可能有些SQL你还不常用
有没有办法得到这个不显示?一些标志,我在一个配置文件中设置某处?最佳答案创建Sequelize对象时,将false传递给logging参数:
NeDB 是使用 Node.js 实现的一个 NoSQL 嵌入式数据库操作模块, 可以充当内存数据库,也可以用来实现本地存储,甚至可以在浏览器中使用。 查询方式比较灵活,支持使用正则、比较运算符、逻辑运算符、索引以及 JSON 深度查询等,适用于不需要大量数据处理的应用系统
内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!