Mysql 数据表碎片整理

Mysql 数据表碎片整理,表统计信息，字符集

为什么会产生碎片

当我们删除一些大表的无用数据，发现数据文件的大小并没有减少，因为使用delete删除数据的时候，只是删除标识位，没有整理数据文件，因此并不会彻底释放表空间

1:碎片计算方法及整理

碎片大小=（数据总大小-实际表空间文件大小）/1024/1024
数据总大小=data_length+index_length
实际表空间文件大小=rows * avg_row_length

清除碎片的两种方法：

（1） alter table table_name engine=innodb
(2)备份原表数据，然后删除原表，重新导入到新表中
缺点：需要先给整表加个写锁，需要经历比较长的时间

使用pt-online-schema-change,可以在线整理表结构，收集碎片，给大表增加字段和索引，避免出现锁表导致阻塞读写操作，Mysql5.7 版本不需要使用这个命令了，可以直接在线“”Online DDL“”
pt-online-schema-change –user=root –socket=/tmp/mysql.sock –alter=”engine=innodb” D=database,t=table –execute

2:表统计信息

2.1 统计每个库的大小
select TABLE_SCHEMA,sum(DATA_LENGTH)/1024/1024 as DATA_LENGTH,sum(INDEX_LENGTH)/1024/1024 as INDEX_LENGTH,sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024 as DATA_LENGTH_INDEX_LENGTH from information_schema.tables where table_schema !=’information_schema’ and table_schema!=’mysql’ group by table_schema;

2.2统计每个表的大小
select table_name,data_length,index_length,sum(data_length+index_length) as total_size from information_schema.tables where table_schema=’djangotest’ group by table_name;

2.3统计所有数据库的大小
select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables

3:字符集

3.1 首先连接终端的字符集必须是utf8
查看Linux 系统的字符集方法
[root@mq184 percona-toolkit-2.2.20]# locale
LANG=en_US.UTF-8
或者
[root@mq184 percona-toolkit-2.2.20]# cat /etc/locale.conf
LANG=”en_US.UTF-8”
3.2 mysql 数据库的字符集必须都是UTF8，查看方法

mysql> \s

mysql Ver 14.14 Distrib 5.6.40, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 3531
Current database: djangotest
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.5.5-10.1.32-MariaDB Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /opt/lampp/var/mysql/mysql.sock
Uptime: 78 days 4 hours 43 min 49 sec

Threads: 3 Questions: 2288122 Slow queries: 0 Opens: 4347 Flush tables: 1 Open tables: 64 Queries per second avg: 0.338

或者可以通过show variable like ‘%char%’; 查看数据库字符集的配置，配置文件中相应的配置
[mysqld]
character-set-server=utf8