mysql 如何去整理表数据,碎片整理
发布网友
发布时间：2022-04-25 12:27
共3个回答
懂视网
时间：2022-04-29 21:16
例子一：
在数据库中往往有以下的统计操作，要统计某种类型的样本有多少个，并且找出大于500个样本的类型，以及拥有的样本数目。具体例子如，在SQL中，一个表的定义如下：
CREATE TABLE t_account( account varchar(100), account_type TINYTEXT, PRIMARY KEY (account), };
account为账号，account_type为该账号的类型，写出一个sql，统计账号数累计超过5000个账号类型，并显示对应的账号数，即结果中每行是（账号类型，账号数）
select account_type, count(account) from t_account group by account_type having count(account)>5000;
例子二：（Leetcode196: Delete Duplicate Emails）
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
找到要保留的数据 然后用not in 来删除不再这些数据中的记录。大家很容易就想到如下的sql语句：
delete from Person where Id not in (select * from (select min(Id) from Person group by Email));
但是mysql删 除动作不能带有本表的查询动作，意思是你删除users表的东西不能以Person表的信息为条件 所以这个语句会报错，执行不了。只要通过创建临时表作为查询条件。具体实现如下：
delete from Person where Id not in (select * from (select min(Id) from Person group by Email) as w);
数据库常用操作整理
标签：
热心网友
时间：2022-04-29 18:24
MySQL 的碎片是 MySQL 运维过程中比较常见的问题，碎片的存在十分影响数据库的性能，本文将对 MySQL 碎片进行一次讲解。
判断方法：
MySQL 的碎片是否产生，通过查看
show table status from table_nameG;
这个命令中 Data_free 字段，如果该字段不为 0，则产生了数据碎片。
产生的原因：
1. 经常进行 delete 操作
经常进行 delete 操作，产生空白空间，如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，久而久之就产生了碎片；
演示：
创建一张表，往里面插入数据，进行一个带有 where 条件或者 limit 的 delete 操作，删除前后对比一下 Data_free 的变化。
删除前：
删除后：
Data_free 不为 0，说明有碎片；
2. update 更新
update 更新可变长度的字段(例如 varchar 类型)，将长的字符串更新成短的。之前存储的内容长，后来存储是短的，即使后来插入新数据，那么有一些空白区域还是没能有效利用的。
演示：
创建一张表，往里面插入一条数据，进行一个 update 操作，前后对比一下 Data_free 的变化。
CREATE TABLE `t1` ( `k` varchar(3000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
更新语句：update t1 set k='aaa';
更新前长度：223 Data_free：0
更新后长度：3 Data_free：204
Data_free 不为 0，说明有碎片；
产生影响：
1. 由于碎片空间是不连续的，导致这些空间不能充分被利用；
2. 由于碎片的存在，导致数据库的磁盘 I/O 操作变成离散随机读写，加重了磁盘 I/O 的负担。
清理办法：
MyISAM：optimize table 表名；（OPTIMIZE 可以整理数据文件,并重排索引）
Innodb：
1. ALTER TABLE tablename ENGINE=InnoDB；(重建表存储引擎，重新组织数据)
2. 进行一次数据的导入导出
碎片清理的性能对比：
引用我之前一个生产库的数据，对比一下清理前后的差异。
SQL执行速度：
select count(*) from test.*_11;
修改前：1 row in set (7.37 sec)
修改后：1 row in set (1.28 sec)
结论：
通过对比，可以看到碎片清理前后，节省了很多空间，SQL执行效率更快。所以，在日常运维工作中，应对碎片进行定期清理，保证数据库有稳定的性能。
热心网友
时间：2022-04-29 19:42
--创建DEMO表
CREATE TABLE DEMO(
id int unsigned,
body text
) engine=myisam charset=utf8;
--插入5条测试数据
INSERT INTO DEMO VALUES(1, 'AAAAA');
INSERT INTO DEMO VALUES(2, 'BBBBB');
INSERT INTO DEMO VALUES(3, 'CCCCC');
INSERT INTO DEMO VALUES(4, 'DDDDD');
INSERT INTO DEMO VALUES(5, 'EEEEE');
