008、表

Innodb存储引擎类型表：

在innodb存储引擎表中，每张表都有个主键，如果在创建表时没有显示地定义主键，则innodb存储引擎会按如下方式选择或创建主键：

首先表中是否有非空的唯一索引（unique not null）如果有则该列即为主键；
不符合上述条件，innodb存储引擎自动创建一个6个字节大小的指针做为主键。

表空间介绍

Innodb存储引擎的逻辑存储结构和oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间tablespace.表空间又由段segment,区extent,页page组成。

启用了innodb_file_per_table的参数选项时，需要注意：每张表的表空间内存放的只是数据,索引和插入缓冲，其他类的数据,如撤销(undo)信息,系统事务信息，二次写缓冲（double write buffer）等还是存放在原来的共享表空间内。

因此即使在启用了参数innodb_file_per_table之后，共享表空间还是会不断地增加其大小。

段的介绍

表空间是由各个段组成,常见的段有数据段,索引段,回滚段。innodb存储引擎表是索引组织的，因此数据即索引，索引即数据。

那么数据段即为B+树的叶节点（leaf node segment），索引段即为B+树的非页节点(non-leaf node segment)。

Innodb存储引擎对于段的管理是由引擎本身完成，简化了DBA的管理。

区的介绍

区是由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB。对于大数据段，innodb存储引擎最多每次可以申请4个区.以此来保证数据的顺序性能。

创建表的注意事项

1、创建表的时候，表名不要用中文

2、创建表的时候，要为表选择一个与业务无关的字段作为主键，默认选id

3、线上管理一定要提前写好SQL，测试环境做好测试再在线上执行

4、和开发人员协商好，提前把sql语句发送给DBA

5、设计表时，后期表的记录数不要超过1000万行，字段数量不要超过30个，索引不要超过5个

MySQL也是有信息收集的，根据：information_schema.tables，每天读取一下这个表的信息，相当于完成了一次表信息的收集。

1、统计每个库的大小

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 != 'information_schema' and table_schema != 'mysql' group by table_schema;

mysql> use information_schema;
Database changed
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 != 'information_schema' and table_schema != 'mysql' group by table_schema;
+--------------------+----------------+----------------+-----------------+
| table_schema       | data_length    | index_length   | sum_data_length |
+--------------------+----------------+----------------+-----------------+
| performance_schema | 0.000000000000 | 0.000000000000 |  0.000000000000 |
| test               | 0.000015258789 | 0.000001907349 |  0.000017166138 |
+--------------------+----------------+----------------+-----------------+
2 rows in set (0.00 sec)

2、统计库里每个表的大小

select table_name,data_length,index_length,sum(data_length+index_length) as total_size 
from information_schema.tables 
where table_schema='test' group by table_name;

mysql> select table_name,data_length,index_length,sum(data_length+index_length) as total_size from information_schema.tables where table_schema='test' group by table_name;
+------------+-------------+--------------+------------+
| table_name | data_length | index_length | total_size |
+------------+-------------+--------------+------------+
| t1         |       16384 |            0 |      16384 |
| t2         |           0 |         1024 |       1024 |
| t3         |           0 |         1024 |       1024 |
+------------+-------------+--------------+------------+
3 rows in set (0.00 sec)

3、统计所有数据库的大小

select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;

mysql> select sum(data_length+index_length)/1024/1024/1024 from information_schema.tables;
+----------------------------------------------+
| sum(data_length+index_length)/1024/1024/1024 |
+----------------------------------------------+
|                               0.000862423331 |
+----------------------------------------------+
1 row in set (0.03 sec)

4、统计客户端的连接数

netstat -anlp|grep 3306|grep tcp|awk '{print $5}'|awk -F: '{print $1}'|sort|uniq -c|sort -nr|head -n20
mysql -uroot -proot -e "show processlist"|awk '{print $3}'|awk -F: '{print $1}'|sort|uniq -c|sort -nr

[root@ning ~]# netstat -anlp|grep 3306|grep tcp|awk '{print $5}'|awk -F: '{print $1}'|sort|uniq -c|sort -nr|head -n20
      1 
[root@ning ~]# mysql -uroot -proot -e "show processlist"|awk '{print $3}'|awk -F: '{print $1}'|sort|uniq -c|sort -nr
Warning: Using a password on the command line interface can be insecure.
      1 localhost
      1 Host

本文档主要讲：表的数据类型、统计信息、碎片、行记录的格式。

如何建表：建表和设计表的工作是开发的工作，开发人员需要把建表的sql list发送给DBA审核。

一个项目，前期设计时，DBA要配合产品经理、开发人员研究表结构的设计问题，中期DBA要优化sql语句、优化表，产生最优的执行计划，使sql按照最优的执行计划运行。后期，表新增字段、修改字段、创建索引等。

设计表时，表字段的数量一般不要超过30个。索引不要超过5个。

表的数据类型

这里并不列出所有类型，只介绍常用的数据类型。

整型：int、bigint、tinyint

字符类型：char、varchar

浮点型：float、double、decimal

日期类型：date、datatime、timestamp

大数据类型：text、blob

整形

int类型

整型，总共占的空间大小是4字节。比如以下两个，实际上没有任何区别：int(4),int(10)。

int类型最多可以存储多少位的数字？int类型最多存储10位长度的数字。所以int类型的字段定义时可以写成:int(10)--10位长度，int(11)--小于11位长度，int(4)--4个字节的长度。如果定义成了int(1)，并不代表只能存放一个长度的数字，因为int类型能存放10位长度的数字，实验如下：

mysql> desc t4;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t4 values(1),(11),(111);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t4;
+------+
| id   |
+------+
|    1 |
|   11 |
|  111 |
+------+

如果插入表时，数字的长度超过了10为数字，则会被转换为固定的数字：

mysql> insert into t4 values(1000000000000);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t4;
+------------+
| id         |
+------------+
|          1 |
|         11 |
|        111 |
| 2147483647 |
+------------+
4 rows in set (0.00 sec)

bigint

bigint类型能存放8个字节、19位长度的数字。一般情况下，不经常使用bigint类型。常用bigint类型储存的数据：手机号。

tinyint

该类型只占用1个字节，可以存放3位长度的数字。tinyint类型常用于存放sex、status等类型。

字符类型

1、varchar类型

varchar(10)、varchar(20)有何区别？

在MySQL5.1时，utf8字符集下，10/3、20/3代表能存放多少个汉字，在GBK字符集下，为10/2、20/2，所以，在5.1之后varchar(n)中的n代表存放的字节数。

而在MySQL5.5之后，varchar(n)中的n代表字符，即能存放多少长度的字符。

varchar是变长类型，varchar(n)中的n代表最长大小。

设计表的过程中，遵循一个原则：占用最少空间的原则，前提是字符长度够使用的情况下。

varchar设计时，经常使用取整原则，即varchar(10),varchar(20)等。

2、char类型

char字符类型是固定长度的字符类型。

一般情况下，设计表时，varchar和char不要混合使用，大多数的情况下，char类型被抛弃，只使用varchar类型。

浮点类型

浮点类型是包含小数点的数字，即小数点后包含几位的问题。

对浮点类型要求最严格的是关于钱的字段，最常用的浮点类型是decimal类型。而float和double有误差，被舍弃。

IP地址用什么类型的字段存放？

IP地址使用int类型存放，看示例：

1、将IP地址转换成数字

mysql> SELECT INET_ATON('192.168.100.111');
+------------------------------+
| INET_ATON('192.168.100.111') |
+------------------------------+
|                   3232261231 |
+------------------------------+
1 row in set (0.01 sec)

转换成数字以后，长度正好是10位数字，可以存放进int类型的字段中。

2、将数字转换回IP地址

mysql> SELECT INET_NTOA('3232261231');
+-------------------------+
| INET_NTOA('3232261231') |
+-------------------------+
| 192.168.100.111         |
+-------------------------+
1 row in set (0.00 sec)

为什么把IP地址用int类型存储？因为整形的读取速度快。

日期类型

日期类型分为date，datetime，timestamp，建议使用int存储日期。

在MySQL5.1时，timestamp占用4个字节，datetime占用8个字节

在MySQL5.6，datetime占用5个字节。

使用int存储日期类型，看下面示例：

1、日期转换为数字

mysql> SELECT UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2019-04-01 17:10:10');
+-----------------------+---------------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP('2019-04-01 17:10:10') |
+-----------------------+---------------------------------------+
|            1617268330 |                            1554109810 |
+-----------------------+---------------------------------------+
1 row in set (0.00 sec)

2、数字转换回日期

mysql> SELECT FROM_UNIXTIME('1617268237'),FROM_UNIXTIME('1554109810');
+-----------------------------+-----------------------------+
| FROM_UNIXTIME('1617268237') | FROM_UNIXTIME('1554109810') |
+-----------------------------+-----------------------------+
| 2021-04-01 17:10:37.000000  | 2019-04-01 17:10:10.000000  |
+-----------------------------+-----------------------------+
1 row in set (0.00 sec)

大数据类型

分为：text、blob

注意：不要把这两个类型与一般的业务表混合使用，也就是在一般业务表中，不要有大数据类型的字段。因为这两个字段占用空间太大，严重降低查询速度。

存储时，将text、blob单独拿出来，放在一张新表中。

大字段表：id、text、blob

业务表：id、其他字段

两个表通过主键进行关联查询使用。

表的碎片回收

1、创建实验表

mysql> use test;
Database changed
mysql> create table su(
    -> id int unsigned not null auto_increment,
    -> c1 int not null default '0',
    -> c2 int not null default '0',
    -> c3 int not null default '0',
    -> c4 int not null default '0',
    -> c5 timestamp not null,
    -> c6 varchar(200) not null default '',
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

2、创建插入数据的存储过程

mysql> use test;
Database changed
mysql> delimiter $$ --delimiter+空格+$$:定义结束符为$$,即碰到$$，就可以执行了
mysql> drop procedure if exists `insert_su` $$
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure `insert_su`(in row_num int)
    -> begin
    ->   declare i int default 0;
    ->   while i < row_num do
    ->     insert into su(c1,c2,c5,c3,c4) values(floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su',floor(rand()*20)));
    ->     set i = i+1;
    ->   end while;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;--注意delimiter后的空格

3、插入测试数据

mysql> use test;
Database changed
mysql> call insert_su(50000);
Query OK, 1 row affected, 3 warnings (57.06 sec)
mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.02 sec)

4、使用delete删除su表的数据，再次插入测试数据

mysql> delete from su;
Query OK, 50000 rows affected (0.34 sec)

mysql> call insert_su(50000);
Query OK, 1 row affected, 3 warnings (7 min 20.63 sec)

mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.01 sec)

5、查看表的状态、回收碎片

大表的删除会造成碎片。表到数据量在千万级别的表叫大表。

查看表的状态：

mysql> show table status like 'su' \G;
*************************** 1. row ***************************
           Name: su
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 49156    --该值是统计信息的大小，该值接近实际表数量，则统计信息是准确的
 Avg_row_length: 53
    Data_length: 2637824
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 100001
    Create_time: 2021-04-02 10:16:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

查看表的状态，主要关注：Rows，Avg_row_length，Data_length，Index_length

计算碎片的值(计算结果大小以kb为单位)：

--Data_length+Index_length-Rows*Avg_row_length
mysql> select 2637824-49156*53;
+------------------+
| 2637824-49156*53 |
+------------------+
|            32556 |
+------------------+
1 row in set (0.00 sec)

回收碎片的方法：

mysql> alter table su engine=innodb; --目的是重新整理表(放在夜间执行，因为数据量大会很费时间)
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

回收碎片完成以后，再次查看表的状态，如果数据量大，应该会看出与上面的结果的差异(数据量小，看不出)：

mysql> show table status like 'su' \G;
*************************** 1. row ***************************
           Name: su
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 50256
 Avg_row_length: 52
    Data_length: 2637824
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 100001
    Create_time: 2021-04-02 11:27:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> select 2637824-50256*52;
+------------------+
| 2637824-50256*52 |
+------------------+
|            24512 |
+------------------+
1 row in set (0.00 sec)

第二个回收碎片的方法：导入导出，即先把数据导出，重新建表，再进行导入。这种方法是最快的回收碎片的方法。

查看创建表的sql:

mysql> show create table su \G;
*************************** 1. row ***************************
       Table: su
Create Table: CREATE TABLE `su` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` int(11) NOT NULL DEFAULT '0',
  `c3` int(11) NOT NULL DEFAULT '0',
  `c4` int(11) NOT NULL DEFAULT '0',
  `c5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c6` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表的统计信息

MySQL不会自动收集统计信息。收集表的统计信息的两种方法：

1、访问information_schema.tables表

mysql> use information_schema;
Database changed
mysql> select * from tables; --访问这张表就是收集统计信息的一种方法。
--建议写一个定时任务，每天访问一下这张表，收集一下统计信息。

2、重启数据库

来自为知笔记(Wiz)

posted @ 2021-06-09 14:53 有点菜大人阅读(71) 评论(0) 收藏举报

刷新页面返回顶部

有点菜大人