处理MySQL高水位表的相关测试

文档课题：处理MySQL高水位表的相关测试.
数据库：MySQL 5.7.21
系统：rhel 7.3
1、理论知识
MySQL中使用delete删除数据后并不会回收存储空间，而是等待新数据填补该空洞，若无数据填补，则此部分存储空间会造成资源浪费。此时需使用optimize table释放空间。对于写操作频繁的表，需根据实际情况定期进行optimize。optimize table是由各存储引擎来实现，因此并不是所有存储引擎都可以应用，目前只有MyISAM、BDB和InnoDB可以实现该功能，且optimize table在运行过程中，MySQL会锁表。

语法如下：
optimize [local | no_write_to_binlog] table tb_name [,tbl_name] ……
常用语句：
optimize table tablename;

2、准备数据
2.1、建测试表
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| booksDB            |
| fruitsDB           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE demo_table (
    ->   `id` bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    ->   `col1` varchar(10),
    ->   `col2` varchar(36),
    ->   `col3` varchar(36)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table demo_table add index idx_col1(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table demo_table add index idx_col23(col2,col3);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--使用存储过程向表添加100w条数据
mysql> delimiter $
mysql> create procedure proc_initData()
    -> begin
    ->     declare i int default 1;
    ->     while i <= 1000000 do
    ->         insert into demo_table(col1,col2,col3) values (i,i div 10,i div 100);
    ->         SET i = i + 1;
    ->     end while;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_initData();
Query OK, 1 row affected (21 min 16.97 sec)

--更新Cardinality参数
mysql> analyze table demo_table;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| booksDB.demo_table | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.01 sec)

2.2、删除前确认数据
--查数据量
mysql> select count(*) from demo_table;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.14 sec)

--查表文件大小
[mysql@mysql-leo-master booksDB]$ ll -hrS | grep demo_table
-rw-r-----. 1 mysql mysql 8.5K Sep  9 09:54 demo_table.frm
-rw-r-----. 1 mysql mysql 148M Sep  9 10:22 demo_table.ibd

--查索引信息
mysql> show index from demo_table;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo_table |          0 | PRIMARY   |            1 | id          | A         |      997144 |     NULL | NULL   |      | BTREE      |         |               |
| demo_table |          1 | idx_col1  |            1 | col1        | A         |      901931 |     NULL | NULL   | YES  | BTREE      |         |               |
| demo_table |          1 | idx_col23 |            1 | col2        | A         |      132603 |     NULL | NULL   | YES  | BTREE      |         |               |
| demo_table |          1 | idx_col23 |            2 | col3        | A         |       82456 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

Table：表名
Non_unique：是否唯一索引（0:是、1:否）
Key_name：索引名
Seq_in_index：索引中的列序列号，从1开始
Column_name：索引对应列
Collation：列在索引中到存储方式（A:升序 NULL:无分类）
Cardinality：索引中唯一值数目的估计值
Sub_part：如果列只是被部分编入索引，则为被编入索引的字符数目，若整列被编入索引，则为NULL
Packed：指示关键字如何被压缩，若没有被压缩，则为NULL
Null：索引列是否存在空值（YES:存在 空:不存在）
Index_type：索引存储数据结构

Cardinality可通过运行ANALYZE TABLE或myisamchk -a更新，根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。当进行联合时，基数越大MySQL使用该索引的机会就越大.

2.3、删除数据
--删一半数据
mysql> delete from demo_table where id>500000;
Query OK, 500000 rows affected (3.57 sec)

2.4、查删除后文件
[mysql@mysql-leo-master booksDB]$ ll -hrS | grep demo_table
-rw-r-----. 1 mysql mysql 8.5K Sep  9 09:54 demo_table.frm
-rw-r-----. 1 mysql mysql 148M Sep  9 10:40 demo_table.ibd

说明：如上所示ibd文件大小无变换.

--查索引信息
mysql> show index from demo_table;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| demo_table |          0 | PRIMARY   |            1 | id          | A         |      498930 |     NULL | NULL   |      | BTREE      |         |               |
| demo_table |          1 | idx_col1  |            1 | col1        | A         |      451289 |     NULL | NULL   | YES  | BTREE      |         |               |
| demo_table |          1 | idx_col23 |            1 | col2        | A         |       66349 |     NULL | NULL   | YES  | BTREE      |         |               |
| demo_table |          1 | idx_col23 |            2 | col3        | A         |       41258 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

说明：对比此前数据，索引查询中Cardinality字段减少一半.
3、相关优化
3.1、使用optimize优化
--使用optimize table优化
mysql> optimize table demo_table;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| booksDB.demo_table | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| booksDB.demo_table | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2.54 sec)

3.2、查看优化后的文件
[mysql@mysql-leo-master booksDB]$ ll -hrS | grep demo_table
-rw-r-----. 1 mysql mysql 8.5K Sep  9 10:47 demo_table.frm
-rw-r-----. 1 mysql mysql  64M Sep  9 10:47 demo_table.ibd

说明：如上所示，文件大小缩减到此前的一半.
3.3、使用engine优化
--如下测试另一种释放空间的方法，删除一半数据
mysql> delete from demo_table where id>250000;
Query OK, 250001 rows affected (1.50 sec)

--查文件大小
[mysql@mysql-leo-master booksDB]$ ll -hrS | grep demo_table
-rw-r-----. 1 mysql mysql 8.5K Sep  9 10:47 demo_table.frm
-rw-r-----. 1 mysql mysql  64M Sep  9 10:51 demo_table.ibd

--使用engine优化
mysql> alter table demo_table engine=innodb;
Query OK, 0 rows affected (1.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看文件大小
[mysql@mysql-leo-master booksDB]$ ll -hrS | grep demo_table
-rw-r-----. 1 mysql mysql 8.5K Sep  9 10:56 demo_table.frm
-rw-r-----. 1 mysql mysql  36M Sep  9 10:56 demo_table.ibd

说明：如上所示，使用"alter table ……"依然可以释放delete删除数据所占用的空间.

参考网址：https://blog.csdn.net/qq_38666502/article/details/124194759