目录
1、如何定位低效率的SQL语句-慢查询日志?EXPLAIN 执行计划怎么分析?
4、通过show profiles和show profile for query查看执行状态
1、show table status from db_name【 如何判断是否有碎片?】
1)、innodb_file_per_table 参数设置为 ON(基本上是默认打开的)
2)、定期执行 OPTIMIZE TABLE tableName;
3、定期执行 ALTER TABLE tableName ENGINE = InnoDB;
一、MySQL 主备切换以及读写分离
首先你要知道MySQL 主备切换以及读写分离是大方向上的数据库性能优化的重要手段。是架构层面的优化垂直拆分、水平拆分。
顺便提一下数据表设计的规范程度称之为范式。三大范式
:
-
1NF:列不能再拆分
-
2NF:所有的属性都依赖于主键
-
3NF:所有的属性都直接依赖于主键(消除传递依赖)
更多内容可参看:MySQL读写分离架构MySQL 主备切换:主备一致、主备延迟、一主多从
二、SQL优化
这里提及的SQL优化内容包括sql语句的优化、慢查询日志、EXPLAIN 执行计划分析等。
1、如何定位低效率的SQL语句-慢查询日志?EXPLAIN 执行计划怎么分析?
查看博主另一篇文章详解:MySQL慢查询是怎么回事
2、sql语句优化常用的方法有哪些?
查看博主另一篇文章详解:sql优化常用的方法
3、如何优化索引?优化CRUD操作?优化分页?
查看博主另一篇文章详解:MySQL索引及索引优化、索引失效
通过慢日志查询可以知道哪些SQL语句执行效率低下,通过explain我们可以得知SQL语句的具体执行情况,索引使用等,还可以结合show命令查看执行状态。
4、通过show profiles和show profile for query查看执行状态
MySQL从5.0.37开始支持剖面系统来帮助用户了解SQL执行性能的细节,一般用于会话级,用于收集SQL语句的资源使用情况。
1)可以通过下面的方式来查看MySQL是否支持和开启了剖面系统:
select @@have_profiling;–YES支持
select @@profiling;–1开启
2)如果没有开启剖面系统,可以通过下面的SQL来打开它。
set profiling=1;
3)接下来就可以通过剖面系统来了解SQL的执行性能,例如:
select count(*) from test;–执行SQL语句
show profiles;–通过show profiles查看开启了profiling后的所有sql语句的id和消耗的时间
show profile for query 1;-- 注:此处的1为上一步查询结果显示的query_id值。
show profile cpu,source for query 1;–查看SQL语句的CPU及资源情况
三、配置优化
1、show variables查看MySQL服务器配置参数
2、show status查看MySQL服务器运行状态值
具体可以查看和调整哪些参数配置呢?传送门:
设置要根据自己的实际情况来设置,如果设置的值不在合理的范围内,并不是设置越大越好,可能设置的数值太大体现不出优化效果,反而造成系统的swap空间被占用,导致操作系统变慢,降低sql查询性能。
MySQL5.7及以后版本,改参数是动态的,修改后,无需重启MySQL,但是低版本,静态的,修改后,需要重启MySQL。
四、碎片整理优化
1、show table status from db_name【 如何判断是否有碎片?】
1)、碎片查询分析
show status
可以查看MySQL服务器运行状态值。而show table status from db_name
可以查询db_name 数据库里所有表的信息,是否有索引碎片等。MySQL 的表空间设置和优化策略有什么?
这个命令中 Data_free 字段,如果该字段不为 0,则产生了数据碎片。
show table status 查询结果中各列字段含义
:
-
Name 表名称
-
Engine 表的存储引擎,对于分区表,Engine 显示所有分区使用的存储引擎的名称。
-
Version 版本;此列未使用。在 MySQL 8.0 中删除 .frm 文件后,此列现在报告硬编码值 10,这是 MySQL 5.7 中使用的最后一个 .frm 文件版本。
-
Row_format 行格式。对于MyISAM引擎,这可能是Dynamic,Fixed或Compressed。动态行的行长度可变,例如Varchar或Blob类型字段。固定行是指行长度不变,例如Char和Integer类型字段。
-
Rows 行数。一些存储引擎,例如 MyISAM,存储确切的计数。对于其他存储引擎,例如 InnoDB,这个值是一个近似值,可能与实际值相差 40% 到 50%。在这种情况下,请使用 SELECT COUNT(*) 获得准确的计数。
对于 INFORMATION_SCHEMA 表,Rows 值为 NULL。
对于 InnoDB 表,行数只是 SQL 优化中使用的粗略估计。 (如果 InnoDB 表是分区的,这也是如此。) -
Avg_row_length 平均每行包括的字节数
-
Data_length 对于 MyISAM,Data_length 是数据文件的长度,以字节为单位。
对于 InnoDB,Data_length 是为聚集索引分配的近似空间量,以字节为单位。具体来说,它是聚集索引大小(以页面为单位)乘以 InnoDB 页面大小。
对于 MyISAM,Max_data_length 是数据文件的最大长度。给定使用的数据指针大小,这是可以存储在表中的数据总字节数。 -
Max_data_length 对于 MyISAM,Max_data_length 是数据文件的最大长度。给定使用的数据指针大小,这是可以存储在表中的数据总字节数。
未用于 InnoDB。 -
Index_length 对于 MyISAM,Index_length 是索引文件的长度,以字节为单位。
对于 InnoDB,Index_length 是为非聚集索引分配的近似空间量,以字节为单位。具体来说,它是非聚集索引大小的总和,以页面为单位,乘以 InnoDB 页面大小。 -
Data_free 已分配但未使用的字节数。
InnoDB 表报告该表所属表空间的可用空间。对于位于共享表空间中的表,这是共享表空间的空闲空间。如果您使用多个表空间并且该表有自己的表空间,则可用空间仅用于该表。空闲空间是指完全空闲范围中的字节数减去安全裕度。即使可用空间显示为 0,只要不需要分配新的扩展区,就可以插入行。
2)、产生碎片的原因
-
经常进行 delete 操作
经常进行 delete 操作,产生空白空间,久而久之就产生了碎片; -
update 更新
update 更新可变长度的字段(例如 varchar 类型),将长的字符串更新成短的。之前存储的内容长,后来存储是短的,即使后来插入新数据,那么有一些空白区域还是没能有效利用的。
由于碎片空间是不连续的,导致这些空间不能充分被利用;由于碎片的存在,导致数据库的磁盘 I/O 操作变成离散随机读写,加重了磁盘 I/O 的负担。
2、MySQL 的表空间设置和优化策略【 如何清理碎片?】
如何清理碎片,加快索引扫描,给表空间瘦身呢,常用的方法可以从以下几个方面进行处理:
1)、innodb_file_per_table 参数设置为 ON(基本上是默认打开的)
-
打开该参数,创建表则会分2个文件,.frm存放元数据,.ibd存放表数据(表初始大小是98304KB);
-
关掉该参数off之后,创建表,只会生成.frm元数据文件,数据会存放到系统表空间,这样会不利于后期管理,系统表空间会逐渐膨胀,导致影响性能。
2)、定期执行 OPTIMIZE TABLE tableName;
MyISAM
:
optimize table 表名;(OPTIMIZE 可以整理数据文件,并重排索引)
对于MYISAM表,OPTIMIZE TABLE 的工作原理:
如果表已删除或分隔行,就修复该表。
如果索引页没有排序,就排序它们。
如果表的统计信息不是最新的(而且修复不能通过对索引进行排序),就更新它们。
Innodb
:
optimize table 表名;
对于InnoDB的表,OPTIMIZE TABLE 的工作原理如下
对于InnoDB表, OPTIMIZE TABLE映射到ALTER TABLE … FORCE(或者这样翻译:在InnoDB表中等价 ALTER TABLE … FORCE),它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。
3、定期执行 ALTER TABLE tableName ENGINE = InnoDB;
Innodb
:
方法一:ALTER TABLE tablename ENGINE=InnoDB;(重建表存储引擎,重新组织数据)
或:ALTER TABLE tbl_name FORCE;
方法二:进行一次数据的导入导出
使用 mysqldump 将表转储到文本文件,删除表,然后从转储文件重新加载
注意:第optimize、ALTER TABLE是高危操作,会锁表,影响业务,建议在低峰期操作。