Raw data
- Data volume
mysql> select count(*) as total from ad_visit_history;
+---------+
| total |
+---------+
| 1187096 | //There are more than 1.18 million pieces of data in total
+---------+
1 row in set (0.04 sec) - Table file size stored on hard disk
[root@BlackGhost test1]# ls |grep visit |xargs -i du {}
382020 ad_visit_history.MYD //Data files account for 380M
127116 ad_visit_history.MYI //Index files account for 127M
12 ad_visit_history.frm //Structural files account for 12K - Check the index information
mysql> show index from ad_visit_history from test1; //Check the index information for this table
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| ad_visit_history | 0 | PRIMARY | 1 | id | A | 1187096 | NULL | NULL | | BTREE | |
| ad_visit_history | 1 | ad_code | 1 | ad_code | A | 46 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | unique_id | 1 | unique_id | A | 1187096 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ad_code_ind | 1 | ad_code | A | 46 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | A | 30438 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ip_ind | 1 | ip | A | 593548 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | port_ind | 1 | port | A | 65949 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | session_id_ind | 1 | session_id | A | 1187096 | NULL | NULL | YES | BTREE | |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.28 sec) Information description of columns in index information.
Table: The name of the table.
Non_unique: 0 if the index cannot include duplicates.1 if possible.
Key_name: The name of the index.
Seq_in_index: The column sequence number in the index, starting at 1.
Column_name: Column name.
Collation: How columns are stored in the index.In MySQLSHOW INDEX syntax, there is a value of'A'(ascending order) or NULL (no classification).
Cardinality: An estimate of the number of unique values in the index.Updates can be made by running either ANALYZE TABLE or myisamchk-a.Cardinality is counted based on statistics stored as integers, so even for small tables, this value does not have to be accurate.The larger the cardinality, the greater the chance MySQL will use the index when federating.
Sub_part: The number of characters indexed if the column is only partially indexed.NULL if the entire column is indexed.
Packed: Indicates how keywords are compressed.NULL if not compressed.
Null: If the column contains NULL, it contains YES.If not, it is empty.
Index_type: Storage index data structure method (BTREE, FULLTEXT, HASH, RTREE)
Delete half of the data
mysql> delete from ad_visit_history where id>598000; //Delete half of the data
Query OK, 589096 rows affected (4 min 28.06 sec)
[root@BlackGhost test1]# ls |grep visit |xargs -i du {} //Corresponding to MYD, MYI file size does not change
382020 ad_visit_history.MYD
127116 ad_visit_history.MYI
12 ad_visit_history.frm In general, if half of the data is deleted from the database, the corresponding.MYD,.MYI file should also become half of the previous one.But after deleting half of the data,.MYD.MYI has not decreased even 1KB, which is terrible.
Let's take a look at the index information
mysql> show index from ad_visit_history;
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| ad_visit_history | 0 | PRIMARY | 1 | id | A | 598000 | NULL | NULL | | BTREE | |
| ad_visit_history | 1 | ad_code | 1 | ad_code | A | 23 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | unique_id | 1 | unique_id | A | 598000 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ad_code_ind | 1 | ad_code | A | 23 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | A | 15333 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ip_ind | 1 | ip | A | 299000 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | port_ind | 1 | port | A | 33222 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | session_id_ind | 1 | session_id | A | 598000 | NULL | NULL | YES | BTREE | |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec) By contrast, it is reasonable that this index query and the last index query contain basically the same data information as the last one.
Optimize with optimize table
mysql> optimize table ad_visit_history; //Optimize after deleting data
+------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| test1.ad_visit_history | optimize | status | OK |
+------------------------+----------+----------+----------+
1 row in set (1 min 21.05 sec) - Check out the size of.MYD,.MYI files
[root@BlackGhost test1]# ls |grep visit |xargs -i du {}
182080 ad_visit_history.MYD //Data files are almost half what they were before optimization
66024 ad_visit_history.MYI //Indexed files are the same, almost half as good as before optimization
12 ad_visit_history.frm - Check the index information
mysql> show index from ad_visit_history;
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| ad_visit_history | 0 | PRIMARY | 1 | id | A | 598000 | NULL | NULL | | BTREE | |
| ad_visit_history | 1 | ad_code | 1 | ad_code | A | 42 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | unique_id | 1 | unique_id | A | 598000 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ad_code_ind | 1 | ad_code | A | 42 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | from_page_url_ind | 1 | from_page_url | A | 24916 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | ip_ind | 1 | ip | A | 598000 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | port_ind | 1 | port | A | 59800 | NULL | NULL | YES | BTREE | |
| ad_visit_history | 1 | session_id_ind | 1 | session_id | A | 598000 | NULL | NULL | YES | BTREE | |
+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec) From the above data, we can see that the indexing opportunities of ad_code, ad_code_ind, from_page_url_ind, etc. are almost all increased by 85%, which improves the efficiency a lot.
Summary
Combined with the information from mysql's official website, this is how I understand it.When you delete data, MySQL does not reclaim it, the storage space occupied by the deleted data, and the index bits.Instead, it's there waiting for new data to fill in the gap, so there's a shortage. If there's no data to fill the gap for a while and a half, it's a waste of resources.So for tables that are more frequently written, optimize them regularly, once a month, depending on the actual situation. For example.There are 100 php programmers who have resigned, but they have just left and php positions are still there. These positions will not be dismissed and will wait for a new php program to fill these vacancies.It's hard to get a good programmer.I think I'll be there most of the time.Ha-ha.
Some uses and descriptions of OPTIMIZE in the manual
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... OPTIMIZE TABLE should be used if you have deleted a large part of the table or if you have made many changes to a table with variable length rows (a table with VARCHAR, BLOB, or TEXT columns).Deleted records are kept in the link list, and subsequent INSERT operations will reuse the old record location.You can use OPTIMIZE TABLE to reuse unused space and defragment data files. In most settings, you do not need to run OPTIMIZE TABLE at all.Even if you make a lot of updates to variable-length rows, you don't need to run them regularly, either once a week or once a month, only for specific tables. OPTIMIZE TABLE only works on MyISAM, BDB, and InnoDB tables. Note that MySQL locks the table while OPTIMIZE TABLE is running.
I have a different view on why deleting tables does not reduce the space of tables; I think that because of the control of mysql MVCC, deleted data is actually still there, and the version number marked for deletion is not what you said was empty there.