数据库学习案例20240304-mysql数据库案例总结（碎片，统计信息）

1  表中的碎片

         在InnoDB中删除行的时候，这些行只是被标记为“已删除”，而不是真正从物理存储上进行了删除，因而存储空间也没有真正被释放回收。InnoDB的Purge线程会异步地来清理这些没用的索引键和行。但是依然没有把这些释放出来的空间还给操作系统重新使用，这样会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能这样被InnoDB重新用来存储新的行。另外，删除数据就会导致页(Page)中出现空白空间，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时，这些空白空间则又会被利用起来，于是造成了数据的存储位置不连续。物理存储顺序与逻辑上的排序不同,这种就是数据碎片。

对于大量的UPDATE，也会造成文件碎片化，InnoDB的最小物理存储分配单位是页(Page)，而UPDATE也可能导致页分裂(Page Split)。频繁的页分裂，页会变得稀疏，并且被不规则地填充，所以最终数据会有碎片。

要计算表中碎片的大小，可以采用下面的计算公式。

下面通过具体的示例来演示如何计算表的碎片大小以及如何清理表的碎片。
1)查看表的状态信息，例如这里使用表“t”。

04:25:  [mgr]> show table status like '%t%'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-03-04 04:23:47
    Update_time: 2024-03-04 04:24:09
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

2)以上面的数据为例，计算表中的碎片大小。

（(Data_length+ Index_length)-rows*Avg_row_length）/1024

3)执行下面的语句清理碎片。

04:25:  [mgr]> alter table t engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

提示
除了使用alter table语句清理碎片以外，还可以使用以下的两种方式。
·备份原表数据，然后删除原表并创建一张与原表相同的新表，再将备份的数据导入新表中。
·使用第三方工具pt-online-schema-change进行在线整理表结构、收集碎片等操作。

2  统计schema大小

04:40:  [mgr]> SELECT TABLE_SCHEMA,(MAX(DATA_LENGTH)/1024/1024 +SUM(INDEX_LENGTH)/1024/1024) usersize FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;
+-------------------------------+------------+
| TABLE_SCHEMA                  | usersize   |
+-------------------------------+------------+
| mysql                         | 1.84375000 |
| information_schema            | 0.00000000 |
| performance_schema            | 0.00000000 |
| sys                           | 0.01562500 |
| mgr                           | 0.01562500 |
| mysql_innodb_cluster_metadata | 0.15625000 |
+-------------------------------+------------+
6 rows in set (0.01 sec)

3 查看每个schenma前10大小的表

SELECT 
TABLE_SCHEMA AS dbname,
TABLE_NAME AS tablename,
TABLE_ROWS AS tablerows,
ENGINE AS tableengine,
ROUND((DATA_LENGTH)/1024/1024,2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024/1024,2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2)AS Total_MB,
ROUND((DATA_FREE)/1024/1024,2)AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA NOT IN
('performance_schema','mysql','information_schema')
ORDER BY T1.TABLE_ROWS DESC
LIMIT 10;

4 统计信息

1 自动采集

2 手动采集

analyze table T;

04:52:  [mgr]> analyze table T;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| mgr.t | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

04:56:  [mgr]> 

5 mysql临时表

MySQL临时表在需要保存一些临时数据时是非常有用的。临时表只在当前会话的连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。由于临时表只属于当前的会话，因此不同会话的临时表可以重名。如果有多个会话执行查询时，使用临时表则不会有重名的担忧。所有临时表都存储在临时表空间，并且临时表空间的数据可以复用。MySQL的InnoDB存储引擎、MyISAM存储引擎和Memory存储引擎都支持临时表。
下面通过一个具体示例来演示如何使用临时表。
1)创建一张临时表。

05:05:  [mgr]> create temporary table tem01 (id bigint primary key);
Query OK, 0 rows affected (0.00 sec)

05:05:  [mgr]> insert into tem01 values(1);
Query OK, 1 row affected (0.00 sec)

05:06:  [mgr]> insert into tem01 values(2);
Query OK, 1 row affected (0.00 sec)

05:06:  [mgr]> insert into tem01 values(3);
Query OK, 1 row affected (0.00 sec)

05:06:  [mgr]> insert into tem01 values(4);
Query OK, 1 row affected (0.00 sec)

05:06:  [mgr]> show tables;
+---------------+
| Tables_in_mgr |
+---------------+
| gp            |
| t             |
+---------------+
2 rows in set (0.00 sec)

05:08:  [mgr]> 

1 当使用“show tables”命令显示数据库列表时，将无法看到临时表。

2  MGR组复制不能复制temp表
3 切换数据库临时表数据仍然在，退出当前的会话在登陆时表已经不存在

05:08:  [mgr]> use mysql
Database changed
05:09:  [mysql]> use mgr
Database changed
05:09:  [mgr]> select *From tem01;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

05:09:  [mgr]> exit
Bye
[root@mgr01 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

05:09:  [(none)]> use mgr
Database changed
05:09:  [mgr]> select *From tem01;
ERROR 1146 (42S02): Table 'mgr.tem01' doesn't exist
05:09:  [mgr]>

6 mysql索引

mysql索引默认存储格式为B+树索引。（节点之间存在指针连接）

可以看到，与B树最大的区别就是每一个叶节点都包含指向下一个叶节点的指针，并且叶节点的指针指向的是被索引的数据，而非其他的节点。非叶节点仅具有索引作用，跟数据有关的信息均存储在叶节点中。查找时存储引擎通过根节点一层层地进行二分搜索即可。由于B+树在内部节点上不包含数据信息，所以它占用空间更小；叶节点之间形成链表，从而方便了叶节点的遍历与范围查找。

3.查看MySQL中的索引

05:15:  [mgr]> desc select *from gp;
05:15:  [mgr]> explain select * from gp;

05:20:  [mgr]> show index from t\G
*************************** 1. row ***************************
        Table: t
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

05:20:  [mgr]> 

7 3种变量以及mysql开发

show global variables like '%%'   --全局变量

show session variables like '%%'  --可以在会话成立的变量

show status  状态变化变量。

set session transaction isolation level read uncommitted;

set session transaction isolation level repeatable read;

set session transaction isolation level read committed;

begin

set @a=10;

select * From timeevent where id>@a;

end

8 innodb 行锁

name列在表t没有索引。

INNODB行锁是通过索引实现的，索引只有通过索引查询数据时才会使用行级锁，否则INNODB将使用表级锁。

session 1

06:12:  [mgr]> begin;
Query OK, 0 rows affected (0.00 sec)

06:12:  [mgr]> select * From t where name='123' for update;
+----+------+-------+-------+-------+
| id | name | name1 | name2 | name3 |
+----+------+-------+-------+-------+
|  1 | 123  | NULL  | NULL  | NULL  |
|  5 | 123  | NULL  | NULL  | NULL  |
+----+------+-------+-------+-------+

session 2

06:12:  [mgr]> select * from t where name='1234541111115' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

06:12:  [mgr]> select * From performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 140047863931688:24:4:7:140047743513056
REQUESTING_ENGINE_TRANSACTION_ID: 11664
            REQUESTING_THREAD_ID: 220
             REQUESTING_EVENT_ID: 27
REQUESTING_OBJECT_INSTANCE_BEGIN: 140047743513056
         BLOCKING_ENGINE_LOCK_ID: 140047863929264:24:4:7:140047743494880
  BLOCKING_ENGINE_TRANSACTION_ID: 11662
              BLOCKING_THREAD_ID: 103
               BLOCKING_EVENT_ID: 197
  BLOCKING_OBJECT_INSTANCE_BEGIN: 140047743494880
1 row in set (0.00 sec)

查看锁定

select *from information_schema.innodb_trx where trx_statte='LOCK WAIT';

select *from sys.innodb_lock_waits\G