关于mysql的delete和主从同步问题

news/2025/2/22 12:14:19

mysql的delete并不会真正删除磁盘空间，而只是标记相应的区域，在合适的时候还可以再利用。如果要真正腾出磁盘空间，还必须使用optimize table xxx进行磁盘碎片处理，但是这个命令会在相应的库下产生一个很大的#sql-xxx文件（增长速度特别快，根据清理的表的大小），所以不能等磁盘已经快满了才想起来清理，正所谓天晴修屋顶。optimize命令会锁表，一般根据table的增长速度和删除等情况综合考虑决定optimize命令的执行频率，比如一个月执行一次（在访问量小的时候执行）。

trancate命令可以直接删除表空间，但是无法按照条件删除，只能是全部清空。

基于以上的问题，我们必须根据某表的增长情况提前规划表的分库分表，一般不要让mysql的表超过千万，否则查询效率会比较低下。

show table [from|in db_name] status like '%table_name%' 来查看表的状态信息

data_free选项代表数据碎片。 

或者查询系统表

SELECT ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='XXXXXDB' and TABLE_NAME='XXXXXTBLTE' LIMIT 1;

SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='XXXXXDB' and TABLE_NAME='XXXXXTBLTE' ;

 

查询整个库的表碎片情况，并排序

按照data_free排序

SELECT CONCAT(table_schema, '.', table_name)                   AS  TABLE_NAME
      ,engine                                                  AS  TABLE_ENGINE 
      ,table_type                                              AS  TABLE_TYPE
      ,table_rows                                              AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')    AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')    AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                        AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END     AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')           AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                       AS  TB_FRAG_RATE
FROM information_schema.TABLES  
ORDER BY data_free DESC limit 10;

限定碎片空间大小

SELECT CONCAT(table_schema, '.', table_name)                    AS  TABLE_NAME
      ,engine                                                   AS  TABLE_ENGINE 
      ,table_type                                               AS  TABLE_TYPE
      ,table_rows                                               AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')     AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')     AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                         AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END      AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')            AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                        AS  TB_FRAG_RATE
FROM information_schema.TABLES  
WHERE ROUND(DATA_FREE/1024/1024,2) >=50
ORDER BY data_free DESC;

限定data_free并按照free空间排序

SELECT TABLE_SCHEMA
      ,TABLE_NAME 
      ,ENGINE
      ,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
      ,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB
FROM information_schema.TABLES 
WHERE DATA_FREE >=10*1024*1024
ORDER BY FREE_SIZ_MB DESC;

.....


针对MySQL的不同数据库存储引擎，在optimize使用清除碎片，回收闲置的数据库空间，把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。 

OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MyISAM引擎表，直接使用如下命令进行优化 optimize table table1[,table2][,table3]  如果同时优化多个表可以使用逗号分隔。

InnoDB引擎的表分为独享表空间和同享表空间的表，我们可以通过show variables like 'innodb_file_per_table';来查看是否开启独享表空间。

共享表空间时，数据保存在ibdata1这个文件中，这个文件会随着时间的增长，会变得越来越大，占据大量的磁盘空间。

独享表空间时，将表空间分别单独存放。MySQL开启独享表空间的参数是Innodb_file_per_table，会为每个Innodb表创建一个.ibd的文件。

# vi /etc/my.cnf
开启独享表空间，并指定ibdata1大小为1G，ibdata2大小200M，自动扩张。
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:1G;ibdata2:200M:autoextend
innodb_file_per_table

开启了独享表空间的是无法对表进行optimize操作的，如果操作，会返回如图信息，最后的一条Table does not support optimize, doing recreate + analyze instead。因为该结构下删除了大量的行，此时索引会重组并且会释放相应的空间因此不必优化。 可以使用alter table table.name engine='innodb'来代替。或者使用insert into xxx select 这个方法代替：

停服状态下（无新数据写入或者更新）

create table table.bak.name like table.name;

insert into table.bak.name select * from table.name;

truncate table table.name;

insert into  table.name select * from table.bak.name;


或者用RENAME命令效率可能更高


CREATE TABLE <NEW.NAME.TABLE> LIKE <TABLE.CRASHED>;
INSERT INTO <NEW.NAME.TABLE> SELECT * FROM <TABLE.CRASHED>;
RENAME TABLE <TABLE.CRASHED> TO <TABLE.CRASHED.BACKUP>;
RENAME TABLE <NEW.NAME.TABLE> TO <TABLE.CRASHED>;
DROP TABLE <TABLE.CRASHED.BACKUP>;

优化过后，可以使用 show index from table.name;   来比较前后的索引机会大小来观察优化效果。

 

关于optimize和insert into select的效率比较：

在4核8G内存机器上实测一个320万数据的表分别用时10分钟和6分钟，所以一般insert into select效率相对较高点儿。

 

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中 `information_schema`.`TABLES`字段,查看 DATA_FREE 字段,大于0话,就表示有碎片。

 

#!/bin/sh  

time=`date +"%Y-%m-%d"`
SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'db.name'"

optimize_table_name=$(mysql -uxxxxxx -pxxxxxxx -e "$SQL"|grep -v "TABLE_NAME")

echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>optimize_table_$time.log

for table_list in $optimize_table_name
do

echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>optimize_table_$time.log
#mysql -uxxxx -pxxxxxx  -e  "alter table $table_list engine=innoDB" 

done
echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>optimize_table_$time.log

 

在执行OPTIMIZE之前最好是停服，让数据不在变动。不能停服也要锁表。

FLUSH TABLES WITH READ LOCK;

完事儿之后一定记得释放

UNLOCK TABLES;

整理碎片的时候需要建立临时表在临时目录，要求临时目录必须比将要整理的表大。否则会报ERROR 1878(HU000): temporary file write failure的错误。

临时目录可以通过配置文件tmpdir设置，默认是/tmpdir。可以通过show variables like 'tmpdir'查询。

 

table optimize步骤
1.查询某个表的数据量select count(*) from xxx;
2.查询其碎片数量 show table status like 'xx';
3.准备一些查询的sql语句，分别记录执行时间，以为后面做对比
4.停服+锁表（flush tables with read lock）
5.执行optimize table xxx;
6.记录执行耗时
7.相同的sql执行一下，记录时间
8.unlock tables;

参考链接：

https://www.cnblogs.com/zhizhao/p/7825469.html

https://www.cnblogs.com/langdashu/articles/5900068.html

https://jingyan.baidu.com/article/6fb756eca47c6b241858fba3.html

https://blog.csdn.net/jgmydsai/article/details/44174353

http://m.codes51.com/article/detail_139089.html

https://blog.csdn.net/cpongo6/article/details/88793499

https://www.cnblogs.com/zhangjinghe/articles/7599988.html

https://blog.csdn.net/hsd2012/article/details/51485250

https://www.cnblogs.com/flish/articles/5537679.html

https://www.toutiao.com/i6730869910135636494/?utm_campaign=operation&utm_medium=navigation&channel=news_tech&utm_source=oppo_llq_xinwen

https://mip-unjs-com.sm-tc.cn/c/mip.unjs.com/ziliaoku/dn/106809.html

http://blog.51yip.com/mysql/1222.html

https://blog.csdn.net/Simpletwt/article/details/99640243

https://blog.csdn.net/hsd2012/article/details/51485250

https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me

https://codeplayer.vip/p/j7sca

https://www.cnblogs.com/kerrycode/p/10943122.html

 

 

DROP TABLE IF EXISTS xxxxx

CREATE TABLE IF NOT EXISTS `%s` LIKE xxxxx

 

df -h 可以查看磁盘分区的占比，可以大概了解某个分区还剩多少可用。

du -h --max-depth=1 xxx目录 可以了解某个目录下所有目录的占用情况

ll -sr 可以了解某个目录中文件的大小

根据这些命令基本就能定位哪些文件增长特别快

mysql主从同步：

在主库 show master status 

在从库 show slave status

mysql的bin-log日志记录

Centos下MySQL主从同步配置 - 山高我为峰 - 博客园

主从不同步的解决办法 - 乌鸦上人 - 博客园

Mysql数据库添加从库,主从同步 - qq_33004309的博客 - CSDN博客

MySQL主从复制——主库已有数据的解决方案 - qq_42606051的博客 - CSDN博客

MySQL主从不一致情形与解决方法 - 君子敏于行而讷于言 - CSDN博客

MySQL主从配置：主从介绍、配置主和从 、测试主从同步-handsome灿的博客-51CTO博客


http://www.niftyadmin.cn/n/3568790.html

相关文章

通过Ipv6连接数据库的几项注意

通过Ipv6连接数据库的几项注意

0.首先主机【包括客户机和服务器】要支持ipv6 ping6 -I eth0 IPV6_ADDRESS 可以判断连通性 由于在ping本地的ipv6地址时&#xff0c;需要指定一个用来发送数据包的网络界面&#xff0c;所以需要添加-I参数1.mysql监听IPV6 配置文件配置 bind_address :: [或者IPV6地…
阅读更多...
Nginx获取自定义header

Nginx获取自定义header

想要获取自定义header&#xff0c;必须开启下划线支持 underscores_in_headers on;可以配置在http或者server中。参考 http://nginx.org/en/docs/http/ngx_http_core_module.html#underscores_in_headers。 因为默认Nginx是不支持下划线的&#xff0c;貌似是http协议的问题&am…
阅读更多...
没时间解释了快上车！0.04折老司机建站节福利来了

没时间解释了快上车！0.04折老司机建站节福利来了

CNNIC日前发布第39次统计报告称&#xff0c;国内网站去年增速高达14.1%。这意味着2016年&#xff0c;我国网站数量增长大约60万&#xff0c;网站作为企业信息化的基础迎来再次爆发。业内人士认为&#xff1a;H5自助建站迎来了最好的发展时机&#xff0c;“苦逼这么多年的建站行…
阅读更多...
ElasticSearch初步学习

ElasticSearch初步学习

本文来自 《Elasticsearch: 权威指南》 https://www.elastic.co/guide/cn/elasticsearch/guide/current/index.html ElasticSearch安装与基本使用 安装 去下载版本https://www.elastic.co/downloads/elasticsearch 解压之后 cd elasticsearch-<version> ./bin/elas…
阅读更多...
0.04折老司机建站节福利来了，没时间解释了快上车！

0.04折老司机建站节福利来了，没时间解释了快上车！

CNNIC日前发布第39次统计报告称&#xff0c;国内网站去年增速高达14.1%。这意味着2016年&#xff0c;我国网站数量增长大约60万&#xff0c;网站作为企业信息化的基础迎来再次爆发。业内人士认为&#xff1a;H5自助建站迎来了最好的发展时机&#xff0c;“苦逼这么多年的建站行…
阅读更多...
POI操作ppt，合并，转图片

POI操作ppt，合并，转图片

引入POI compile group: org.apache.poi, name: poi-ooxml, version: 4.1.0 compile group: batik, name: batik-bridge, version: 1.6-1 compile group: org.apache.xmlgraphics, name: batik-anim, version: 1.11 /*** https://www.w3cschool.cn/apache_poi_ppt/apache_po…
阅读更多...
读书笔记 effective c++ Item 10 让赋值运算符返回指向*this的引用

读书笔记 effective c++ Item 10 让赋值运算符返回指向*this的引用

一个关于赋值的有趣的事情是你可以将它们链在一起&#xff1a; 1 int x, y, z; 2 3 x y z 15; // chain of assignments 同样有趣的是赋值采用右结合律&#xff0c;所以上面的赋值链被解析成下面这个样子&#xff1a; 1 x (y (z 15)); 在这里&#xff0c;15被赋值给z,然…
阅读更多...
IDEA 使用方法快捷键

IDEA 使用方法快捷键

CtrlShift Enter&#xff0c;语句完成“&#xff01;”&#xff0c;否定完成&#xff0c;输入表达式时按 “&#xff01;”键CtrlE&#xff0c;最近的文件CtrlShiftE&#xff0c;最近更改的文件ShiftClick&#xff0c;可以关闭文件Ctrl[ OR ]&#xff0c;可以跑到大括号的开头…
阅读更多...
最新文章

Copyright @ 2022~2023