关于MySQL的命名规范
1. 命名规范
1.1 建库规范
- 数据库,表,字段,索引全部用小写英文字母,英文单词之间用下划线(_)隔开。
1.2 表字段规范
- 列设计规范根据业务区分使用tinyint/int/bigint,分别会占用1/4/8字节。
- 使用tinyint来代替enum,enum增加新值要进行DDL操作。
- 根据业务区分使用char/varchar解读:
- 字段长度固定,或者长度近似的业务场景,适合使用char,能够减少碎片,查询性能高。
- 字段长度相差较大,或者更新较少的业务场景,适合使用varchar,能够减少空间 。
- 使用varchar(20)存储手机号,不要使用整数
- 设置lower_case_table_names=1,是使用大小写不敏感,数据库存储用小写,默认值为0。
示例:
root@localhost [wiz]>show variables like 'lower_case_table_names';
|| Variable_name|| Value ||
||lower_case_table_names ||0 ||
1.3 建表规范
- 每个表需要指定表主键。
- 字段名称用英文小写字母,单词之间用下划线(_)隔开。
- 列名必须见名知义。
- 每个字段需要有备注,字符串类型字段默认不能为NULL,但数字类型,时间类型的字段可以是NULL。
- 不要指定表存储引擎,字符编码。
- 表备注需清晰:如:run_status:运行状态 | 【0:未运行】【1:已运行】【2:已停止】|checkbox用|分隔开是为了模板自动生成代码时进行前端的JSON构建输出
- 下面5个字段是必须的:
id:varchar(36):主键唯一ID,新的设计可以考虑自增ID,需要考虑数据迁移方案
createdtime:datetime,创建时间
createduser:varchar(36),创建人ID
lastmodifiedtime:datetime,最后修改时间
lastmodifieduser:varchar(36),最后修改人ID
status:int状态【0:正常】【1:删除】
新的架构
id:varchar(36):主键唯一ID,新的设计可以考虑自增ID,需要考虑数据迁移方案
created_time:datetime,创建时间
created_user:varchar(36),创建人ID
last_modified_time:datetime,最后修改时间
last_modified_user:varchar(36),最后修改人ID
status:int状态【0:正常】【1:删除】
示例:CREATE TABLE `base_users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID主键', `username` varchar(20) NOT NULL COMMENT '用户名', `mobile` varchar(20) NOT NULL COMMENT '手机号码', `realname` varchar(50) DEFAULT NULL COMMENT '用户姓名', `idcardno` varchar(18) NOT NULL COMMENT '身份证', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `add_time` datetime NOT NULL COMMENT '添加时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'; create unique index uni_idcardno on base_users(idcardno); create index idx_username_mobile on base_users(username,mobile);
1.4 索引规范
1.4.1 建索引规则
- 唯一索引使用uni_[字段名]来命名。
- 非唯一索引使用idx_[字段名]来命名。
- 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)。
示例://唯一索引 create unique index uni_idcardno on base_users(idcardno); //普通索引 create index idx_username_mobile on base_users(username,mobile);
1.4.2 最佳索引规则
- Primary key > Unique key > 一般索引
2. MySQL备份还原
2.1 MySQL备份
备份工具:
mysqldump/mydump/xtrabackup,根据实际环境和需求来选择备份方式,判断依据:数据库大小,备份方式。
2.1.1 备份数据库
mysqldump -uroot -pmysql \ --set-gtid-purged=on \ --single-transaction \ --triggers --routines --events \ --databases sys > db_name.sql
2.1.2 表备份
mysqldump -uroot -pmysql \ --single-transaction \ --set-gtid-purged=on --triggers --routines --events \ --tables zabbix usrgrp > usrgrp.sql
2.1.3 备份表结构
-d, --no-data No row information.
2.1.4 mydumper还原
mydumper -u root -p mysql -h 192.168.50.x \ --port 3306 \ --triggers --events --routines \ --daemon \ --database zabbix -o ./data/
2.2 MySQL还原
mysql -uroot -pmysql --database dbname < x.sql
3. MySQL慢查询优化
yum install –y tcpdump percona-toolkit pt-query-digest/mysqldumpslow //用tcpdump抓包工具 tcpdump -s 65535 -x -nn -q -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt
4. MySQL连接工具
\192.168.50.89\soft 用户名/密码: test/test sqlyog
5. MySQL监控
zabbix/cacti
6. MySQL高可用
cetus https://github.com/Lede-Inc/cetus ProxySQL
7. 常用命令
//查看连接 show processlist; //数据库状态 status //查看Innodb状态 show engine innodb status\G; //查看日志 show binary logs; //kill查询 kill ID_number; //查看数据库 show databases; //查看表 use db_name;show tables; //查看表结构 show create table tbl_name\G; //查看数据库 select database(); //查看当前用户 select user();