mysql表的创建原则、主要数据类型、字符集和表碎片等知识讲解

1.mysql表创建原则：

禁止使用中文字段名；

禁止使用字符型做主键；

禁止无主键或是唯一索引的表出现；

秉承最小，最合适的原则选择数据类型。

text和blob存大量文字或者存图片的大数据类型建议不要与业务表放一起；

2.mysql主要数据类型：整型、浮点型、字符类型和日期类型

show databases;use mysql ;show tables;desc tt;

char与varchar区别：

char用于定长字符串，大小0-255，没达到定义位数，在后面补空格，超过会被截断

varchar变长长度，大小0-65535，没达到定义位数，不会在后面补空格，超过也会被截断

不确定长度时，使用varchar可节约磁盘空间，提高存储效率。

varchar (100)代表的是100个字符概念，在不同字符集下，存储空间不同，如utf-8: 为100*3+1=301字节。在gbk下为100*2+1=201字节

mysql第一行最大字节数为65535，使用utf8,一个字符点3字节，最大长度不能超过 （65535-1-2）/3=21844

例 ipv4这样的建议为int存放，需要使用到inet_aton和inet_ntoa两函数

select inet_aton('10.10.10.10'); -- 168430090

select inet_ntoa(168430090)

3.字符集：就是一套文字符号及其编码，是比较规则的集合。mysql字符集：字符集character和校对规则（collation）；字符集用来定义mysql数据字符串存储方式；校对规则定义比较字符串的方式 。

针对mysql5.7建议用utf8mb4字我们要集，是utf8的超集，查看方法：\s; 或者通过show variables like "char";来查看字符集的配置，只需要 在配置文件中的mysqld下加入character-set-server=utf8mb4即可。想要临时改变数据库字符集方式 ：set names utf8即可

4.表碎片：

碎片计算方法：show table status like '%table_name%'\g;命令来查看

rows: 0

avg_row_length: 0

data_length: 16384

max_data_length: 0

index_length: 0

碎片大小=数据总大小-实际表空间文件大小。

数据总大小=data_length+index_length

实际表空间文件大小=rows*avg_row_length

碎片大小=（数据总大小-实际表空间文件大小）/1024/1024

清除碎片两种方式：

（1）alter table table_name engine=innodb；即重新整理一遍全表数据，缺点：需要先给整表加个写锁，经历时间长。建议用percona-toolkit工具集，工具集命令：pt-online-schema_change可以在线处理表结构、收集碎片，给大表添加字段和索引，避免出现锁表导致阻塞。

./pt-online-schema_chage –user=root –passwork=root123 –host=localhost --alter=”engine=innodb” d=database,t=table_name –execute

（2）备份原表数据，然后删掉，重新导入到新表中（与原结构一样）

5.表统计信息：

（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_index from information_schema.tables

where table_schema!='information_schema' and table_schema!='mysql' group by table_schema;

（2）统计库中每个表大小：

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

（3）统计所有数据库的大小：

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

6.统计信息的收集方法

遍历information_schema.tables收集table_name表的统计信息：

mysql –uroot –pjiradbt

use jira

select * from information_schema.tables where table_name='worklog'\g;

show table status like '%table_name%';

show table status like '%worklog%'\g;

7.mysql库表常用命令总结：

use database_name 选择所创建的数据库

show databases 查看所有数据库

show tables 查看某库下所有表

create database database_name 创建数据库

drop database database_name 删除数据库

create table table_name （字段列表） 创建表

drop table table_name 删除表（表结构也删除）

delete from table_name where 或者 truncate table tablename 只删除表数据

insert into table_name (字段表表) values (对应字段的值) 往表中插入数据

update table_name set 字段名=某值 where 更新表中某行数据

select * from tablename where 查看表中数据

show create table table-name\g 查看建表语句

desc table_name 查看表结构

show table status 获取表基础信息

show index from table_name 查看当前表下索引的情况

show full processlist 查看数据库当前连接的情况