当面试官让你聊聊MySQL数据库性能优化,你还是只能回答优化sql,建索引吗?让我们看看还可以从哪些方面聊聊吧。其实你还可以从网速、数据量、数据库日志、内存等问题、硬件配置,当前占用资源、硬盘碎片或索引碎片等等诸多方面分析聊聊。
一、showtablestatusfromdb_name1、碎片查询分析showstatus可以查看MySQL服务器运行状态值。而showtablestatusfromdb_name可以查询db_name数据库里所有表的信息,是否有索引碎片等。MySQL的表空间设置和优化策略有什么?
这个命令中Data_free字段,如果该字段不为0,则产生了数据碎片。
showtablestatus查询结果中各列字段含义:
Name表名称
Engine表的存储引擎,对于分区表,Engine显示所有分区使用的存储引擎的名称。
Version版本;此列未使用。在MySQL8.0中删除.frm文件后,此列现在报告硬编码值10,这是MySQL5.7中使用的最后一个.frm文件版本。
Row_format行格式。对于MyISAM引擎,这可能是Dynamic,Fixed或Compressed。动态行的行长度可变,例如Varchar或Blob类型字段。固定行是指行长度不变,例如Char和Integer类型字段。
Rows行数。一些存储引擎,例如MyISAM,存储确切的计数。对于其他存储引擎,例如InnoDB,这个值是一个近似值,可能与实际值相差40%到50%。在这种情况下,请使用SELECTCOUNT(*)获得准确的计数。对于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的负担。
二、MySQL的表空间设置和优化策略如何清理碎片,加快索引扫描,给表空间瘦身呢,常用的方法可以从以下几个方面进行处理:
1、innodb_file_per_table参数设置为ON(基本上是默认打开的)打开该参数,创建表则会分2个文件,.frm存放元数据,.ibd存放表数据(表初始大小是KB);
关掉该参数off之后,创建表,只会生成.frm元数据文件,数据会存放到系统表空间,这样会不利于后期管理,系统表空间会逐渐膨胀,导致影响性能。
2、定期执行OPTIMIZETABLEtableName;MyISAM:
optimizetable表名;(OPTIMIZE可以整理数据文件,并重排索引)
对于MYISAM表,OPTIMIZETABLE的工作原理:如果表已删除或分隔行,就修复该表。如果索引页没有排序,就排序它们。如果表的统计信息不是最新的(而且修复不能通过对索引进行排序),就更新它们。
Innodb:
optimizetable表名;对于InnoDB的表,OPTIMIZETABLE的工作原理如下对于InnoDB表,OPTIMIZETABLE映射到ALTERTABLE…FORCE(或者这样翻译:在InnoDB表中等价ALTERTABLE…FORCE),它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。
、定期执行ALTERTABLEtableNameENGINE=InnoDB;Innodb:方法一:ALTERTABLEtablenameENGINE=InnoDB;(重建表存储引擎,重新组织数据)或:ALTERTABLEtbl_nameFORCE;
方法二:进行一次数据的导入导出使用mysqldump将表转储到文本文件,删除表,然后从转储文件重新加载
注意:第optimize、ALTERTABLE是高危操作,会锁表,影响业务,建议在低峰期操作。
#MYSQL#