MySQL相关书籍的读书笔记（四）

最新推荐文章于 2022-04-01 15:24:42 发布

原创最新推荐文章于 2022-04-01 15:24:42 发布 · 296 阅读

0 ·

CC 4.0 BY-SA版权

jathams（人勤事事易，人懒事事难）

数据库同时被 2 个专栏收录

16 篇文章

订阅专栏

MySQL

11 篇文章

订阅专栏

表碎片
原因：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

效率最高；
创建索引后，再次查看执行计划，对比两次结果，看索引是否起到作用。