ITPub博客

mysql学习10:第五章:表

翻译 MySQL 作者:studywell 时间:2018-11-06 18:41:19 0 删除 编辑

1.  

创建表的原则:

l   禁止使用中文做字段名;

l   禁止使用字符型做主键;

l   禁止无主键或唯一索引;

1.1.   整型

int、tinyint是使用最多的整型类型。

                                             

主键选择

为什么要选择ID做主键:主键字段要选择不经常修改的、尽量与业务无关、无具体含义的。因为InnoDB是索引组织表,需要保证索引结构不经常防撞,避免造成性能的消耗。

int(4) 和int(10)都是占用4字节空闲,区别就是数据库中表示的数据位数,一个4位一个10位。有些在数字前自动用0补位。

1.2.   浮点型

常用decimal存储金钱字段,但在运算过程中会转成浮点运算,且会出现四舍五入的情况,建议使用int类型。

decimal(M,D);

D是小数部分位数,超过小数部分四舍五入截断,不足补足;

M是整数部分加小数部分的总长度,即插入整数部分不能超过M-D位,否则插入失败。

1.3.   时间类型

datetime:5.6前占8个字节,5.6后占5个字节,可用范围比timestamp大,物理存储上仅比timestamp多一个字节;

可以用int存储时间,通过两个函数转换:unix_timestamp和from_unixtime。

 [mysql]>select unix_timestamp('2018-11-06 16:42:00');

+---------------------------------------+

| unix_timestamp('2018-11-06 16:42:00') |

+---------------------------------------+

|                            1541493720 |

+---------------------------------------+

 

 [mysql]>select from_unixtime(1541493720);

+---------------------------+

| from_unixtime(1541493720) |

+---------------------------+

| 2018-11-06 16:42:00       |

+---------------------------+

 

从5.6开始,datetime和timestamp都支持自动更新为当前时间。

1.4.   字符串类型

text和blob这种大数据类型建议不要和业务表放一起。

char和varchar区别

char:用于定长字符串,范围0~255,不够空格补全存库;超过截断;

varchar:变长,范围0~65535,不够不补;超过截断;可节约空间提高存储效率。

varchar多用一到两个字节记录长度,数据位占用字节小于255用1字节记录长度;超过255则用2字节;还有一位用来记录是否为null值;

示例:varchar(100)

UTF8字符集:存储空间100*3+1=301字节;

GBK字符集:存储空间100*2+1=201字节;

 

 

mysql每行最大字节数65535,不同字符集下字符最大长度;

使用UTF8,每个字符最多占3个字节,最大长度不能超过(65535-1-2)/3=21844

使用GBK,每个字符2字节,最大长度不超过(65535-1-2)/2=36766

 

存储IP,推荐用int存储,使用inet_aton和inet_ntoa两个参数;

[mysql]>select inet_aton('10.98.156.210');

+----------------------------+

| inet_aton('10.98.156.210') |

+----------------------------+

|                  174234834 |

+----------------------------+

 

[mysql]>select inet_ntoa(174234834);

+----------------------+

| inet_ntoa(174234834) |

+----------------------+

| 10.98.156.210        |

+----------------------+

1.5.   字符集

mysql数据库字符集包括字符集(character)和校对规则(collation)两个概念。

字符集:定义mysql数据库字符串的存储方式;

校对规则:定义比较字符串的方式。

常用字符集:

l   GBK:每个汉字两个字节。

l   Latin1:停用。5.1前默认,1汉字或字母占1字节。

l   UTF8:每个汉字3个字节。

l   UTF8mb4:是utf8的超集,每个汉字4个字节。5.7建议使用。

 

数据库配置文件中相关参数

[mysql]>show variables like '%character%';

+--------------------------+----------------------------------+

| Variable_name            | Value                            |

+--------------------------+----------------------------------+

| character_set_client     | utf8                             |

| character_set_connection | utf8                             |

| character_set_database   | utf8mb4                          |

| character_set_filesystem | binary                           |

| character_set_results    | utf8                             |

| character_set_server     | utf8mb4                          |

| character_set_system     | utf8                             |

| character_sets_dir       | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

 

避免汉字乱码,做到三线统一;

连接端的字符集必须是UTF8;

    操作系统字符集必须是UTF8,

mysql数据库字符集必须是UTF8;

 

[mysql]>\s

--------------

mysql  Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using  EditLine wrapper

 

Connection id:             9

Current database:  mysql

Current user:         root@localhost

SSL:               Not in use

Current pager:             stdout

Using outfile:         ''

Using delimiter:     ;

Server version:             5.7.24-log MySQL Community Server (GPL)

Protocol version:   10

Connection:          Localhost via UNIX socket

Server characterset:      utf8mb4

Db     characterset:   utf8mb4

Client characterset:       utf8

Conn.  characterset:    utf8

UNIX socket:         /tmp/mysql.sock

Uptime:                 3 hours 6 min 28 sec

 

Threads: 1  Questions: 299  Slow queries: 3  Opens: 372  Flush tables: 1  Open tables: 144  Queries per second avg: 0.026

--------------

 

数据库临时修改字符集:命令行执行set names 字符集名称;

如:set names utf8

1.6.   表碎片产生的原因

delete操作产生数据碎片,碎片占用磁盘空间且读取效率低。

1.7.   碎片计算方法及整理过程

查看表统计信息:

[mysql]>show table status like 't'\G;

*************************** 1. row ***************************

           Name: t

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 2

 Avg_row_length: 8192

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 3

    Create_time: 2018-11-06 10:20:10

    Update_time: NULL

     Check_time: NULL

      Collation: utf8mb4_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.01 sec)

 

碎片大小计算

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

数据总大小=data_length+index_length

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

碎片大小MB=(数据总大小-实际表空间文件大小)/1024/1024

 

清除碎片的两种方法:

alter table table_name engine=innodb;会给表加写锁,时间长。

备份原表数据,删掉,重新导入新表中;

 

在线整理表结构的工具pt-online-schema-charge;

Mysql 5.7后已支持在线online ddl了。

1.8.   表统计信息

统计每个库大小

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;

 

统计库中每个表大小

SELECT

       TABLE_NAME,

       SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

       information_schema.`TABLES`

WHERE

       TABLE_SCHEMA = 'mysql'

GROUP BY

       TABLE_NAME;

 

统计所有数据的大小

SELECT

       SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE

FROM

       information_schema.`TABLES`;

 

1.9.   统计信息的收集方法

l   遍历information_schema_tables,收集su表的统计信息;

[mysql]>select * from information_schema.tables where table_name='t' \G;

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: mysql

     TABLE_NAME: t

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Dynamic

     TABLE_ROWS: 2

 AVG_ROW_LENGTH: 8192

    DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 0

 AUTO_INCREMENT: 3

    CREATE_TIME: 2018-11-06 10:20:10

    UPDATE_TIME: NULL

     CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_general_ci

       CHECKSUM: NULL

 CREATE_OPTIONS:

  TABLE_COMMENT:

 

l   重启mysql实例

l   show table status like ‘%table_name%’;

[mysql]>show table status like 't'\G;

*************************** 1. row ***************************

           Name: t

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 2

 Avg_row_length: 8192

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 3

    Create_time: 2018-11-06 10:20:10

    Update_time: NULL

     Check_time: NULL

      Collation: utf8mb4_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.01 sec)

 

1.10.     MySQL库表常用命令总结

登陆同时修改命令提示符:主机-用户-数据库-日期 mysql -uroot -p --prompt='\h-\u-\d-\D' 

退出:exit  quit  \q

命令行结束符号:;或\g

\c 取消当前命令执行,就不用删除了;

MySQL注释: #  或 --

\s  实例信息;

 

show database;显示实例下所有数据库;

show schemas; 显示实例下所有数据库;

show warings;查看警告

use database;选择数据库;

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

select user();得到登陆用户

select version();得到版本信息;

select now();得到当前日期时间;

seleect database();得到当前打开数据库;

create database db_name;创建数据据库;

create databse if not exists test1; 检测数据库不存在则创建

create databse if not exists test1 default character set 'utf8';创建时指定编码方式

show create database dbname;查看数据库信息     

alter databse dbname default character set 'gbk';修改指定数据库的编码方式

drop database db_name;删除数据库;

 

 

show tables;查看库下所有表;

show create table tab_name \G; 查看建表语句;

desc tab_name;查看表结构;

show table status;获取表基础信息;

show index from tab_name;查看当前表下索引情况;

 

create table tab_name:创建表;

drop table tab_name;删除表包括结构;

 

select * from tab_name;

delete from tab_name where ;或truncate table tab_name;

insert into tab_name (字段列表) values(对应字段值)

update tab_name set :字段名=某值(where);

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-2218966/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-04

  • 博文量
    278
  • 访问量
    395432