- 表碎片
原因：delete操作时，MySQL并不会把数据文件真实删除，而只是将数据文件的标识位删除，也没有整理数据文件，不会彻底释放表空间。
计算：show table status like ‘%table_name%’;
mysql> show table status like '%overtime_allowance%';
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+----------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+----------------+
| overtime_allowance | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 118 | 2019-02-28 13:15:44 | NULL | NULL | utf8mb4_general_ci | NULL | row_format=COMPACT | 加班补贴表 |
| overtime_allowance_detail | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 101 | 2019-02-28 13:15:44 | NULL | NULL | utf8mb4_general_ci | NULL | row_format=COMPACT | 加班补贴明细表 |
+---------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+----------------+
2 rows in set (0.34 sec)
mysql> show table status where name='overtime_allowance';
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+------------+
| overtime_allowance | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | 118 | 2019-02-28 13:15:44 | NULL | NULL | utf8mb4_general_ci | NULL | row_format=COMPACT | 加班补贴表 |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+------------+
1 row in set (0.28 sec)解决：第一种：alter table table_name engine=innodb;作用就是重新整理一遍全表数据，整理之后数据连续性好，全表扫描变快；
第二种：备份元彪数据，然后删掉，重新导入到新表中（备份恢复时可能会用）；
- 表的统计信息
就是统计每个库的大小、表的大小、数据和索引的大小等；
统计数据库
中每个库的大小；
mysql> select table_schema,sum(data_length)/1024/1024/1024 as data_length,sum(index_length)/1024/1024/1024 as index_length,sum(data_length+index_length)/1024/1024/1024 as sum_data_length from information_schema.tables where table_schema != 'infomation_schema' and table_schema != 'mysql' group by table_schema;
+--------------------+----------------+----------------+-----------------+
| table_schema | data_length | index_length | sum_data_length |
+--------------------+----------------+----------------+-----------------+
| account_manager | 0.000564575195 | 0.000106811523 | 0.000671386719 |
| activiti_001 | 0.000381469727 | 0.000442504883 | 0.000823974609 |
| dbandcache | 0.000015258789 | 0.000000000000 | 0.000015258789 |
| dbandcache2 | 0.000015258789 | 0.000000000000 | 0.000015258789 |
| hyetec-portal | 0.003005981445 | 0.000701904297 | 0.003707885742 |
| hyetec-urm | 2.418289184570 | 0.000183105469 | 2.418472290039 |
| information_schema | 0.000152587891 | 0.000000000000 | 0.000152587891 |
| moa | 0.529266357422 | 0.001052856445 | 0.530319213867 |
| performance_schema | 0.000000000000 | 0.000000000000 | 0.000000000000 |
| sdyy | 0.000045776367 | 0.000000000000 | 0.000045776367 |
| substation | 0.004638671875 | 0.000106811523 | 0.004745483398 |
| sys | 0.000015258789 | 0.000000000000 | 0.000015258789 |
| zhgl | 0.002090454102 | 0.000000000000 | 0.002090454102 |
+--------------------+----------------+----------------+-----------------+
13 rows in set (1.02 sec)
## 统计库中每个表的大小：
mysql> select table_name,data_length,index_length,sum(data_length+index_length) as total_size from information_schema.tables where table_schema='sdyy' group by table_name;
+------------+-------------+--------------+------------+
| table_name | data_length | index_length | total_size |
+------------+-------------+--------------+------------+
| call_log | 16384 | 0 | 16384 |
| sys_log | 16384 | 0 | 16384 |
| t_user | 16384 | 0 | 16384 |
+------------+-------------+--------------+------------+
3 rows in set (0.12 sec)
统计所有数据库大小
mysql> select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;
+----------------------------------------------+
| sum(data_length+index_length)/1024/1024/1024 |
+----------------------------------------------+
| 2.963734141551 |
+----------------------------------------------+
1 row in set (1.26 sec)- 统计信息的收集方法：
- 常用命令：
--查看表结构
desc table_name;
--查看执行计划：
explain select * from table_name ;
--分析下：先看第五列type，如果为ALL，代表全表扫描，后边的其他数据就可以不用看了；非ALLL下，再看key列的数据；如果是NNULL表示没有使用索引；再看rows，查看需要查询的行数；再看extra；观察是否使用了using filesort或者Using temporary;
mysql> explain select * from year_vocation ;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | year_vocation | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.15 sec)
--延申下sql如何优化
- 先看表的数据类型是否设计的合理，是否遵循选取数据类型越简单越小原则；
- 表中的碎片是否整理；
- 表的统计信息是否收集，只有统计信息收集准确，执行计划才能帮助我们优化sql；
- 查看执行计划：检查索引的使用情况；如果没有使用索引，考虑创建索引；如果有索引，考虑索引是否正确；
- 在创建索引之前，还要查看索引的选择性，判断该字段是否适合创建索引。这里的索引选择性指的是不重复的索引值（也成为基数）和数据表的记录总数的比例；这个比例值越高，则查询效率越高；主键索引和唯一索引的选择性为1
效率最高； - 创建索引后，再次查看执行计划，对比两次结果，看索引是否起到作用。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
