首页 > 其他 > 详细

008、表

时间:2021-06-09 18:42:27      阅读:32      评论:0      收藏:0      [点我收藏+]
Innodb存储引擎类型表:
在innodb存储引擎表中,每张表都有个主键,如果在创建表时没有显示地定义主键,则innodb存储引擎会按如下方式选择或创建主键:
  1. 首先表中是否有非空的唯一索引(unique not null)如果有则该列即为主键;
  2. 不符合上述条件,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、重启数据库
 





008、表

原文:https://www.cnblogs.com/youdiancaidaren/p/14866870.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!