mysql数据库设计规范和优化

淡淡的烟草味﹌ 2023-07-18 11:27 69阅读 0赞

# 数据库的设计 #

## 1、**数据库命名规范** ##

• 所有的数据库对象名称必须使用小写字母并使用下划线分割 ( mysql 数据库对大小写敏感 )

• 所有数据库对象名称禁止使用 MySqL 保留关键字  eg.  from 关键字

• [MySQL关键字查询][MySQL]

• 数据库对象的命名要见名之意  ,  最好不要超过 32 个字符 .

• 数据库操作的时候  , 总会导入导出表  ,  有一些临时表  ,  临时表的最好以 tmp 为前缀并且以日期为后缀

• 备份表的命名 ,  最好以 bak 为前缀并且以日期为后缀

• 所有存储相同数据的列名和列类型必须一致

##  2、**数据结构基本设计规范** ##

• 所有的表必须使用 Innodb 存储引擎

• 数据库和标的字符集统一使用 UTF-8  utf8-mb4

• 所有的表和字段都要添加注释

• 尽量控制单表数据量的大小  ,  尽量在 500 万行以内

• 尽量做到冷热数据分离 , 减小表的宽度

• 禁止在表中建立预留字段

• 禁止在数据库中存储图片 ,  文件等二进制数据

• 禁止在线上数据库做数据库压力测试        dev test   预发   pre

• 禁止从开发环境 , 测试环境直连生产环境数据库

## 3、**数据库索引设计规范** ##

• 限制每张表的上的索引数量  ,  建议单张表索引不超过  5  个

• Innodb 是按照主键的顺序来组织表的

• 不使用更新频繁的列作为主键  ,  不使用多列主键

• 不使用 UUID , MD5 , HASH ,  字符串列作为主键

• 主键建议使用自增 ID 使用主键

•

• 常见索引列建议

• select ,update , delete  语句中的 where  从句中的列

• 包含在 order by ,group by ,distinct  中的字段

• 多表 JOIN 的关联列

•

• 如何选择索引列的顺序

• 区分度最高的列放在联合索引的最左侧 ( 主键 ,  唯一索引列 )

• 尽量把字段长度最小的列放在联合索引的最左侧

• 使用频繁的列放到最左侧

## 4、**数据字段设计规范** ##

• 优先选择符合存储需要的最小的数据类型   手机号使用 char

• 避免使用  TEXT , BLOB 数据类型

• 避免使用  ENUM 数据类型

• 尽可能把列定义为  NOT NULL

• 使用  timestamp  或者 datetime  类型存储时间

• 同财务相关的金额类数据  ,  必须使用  decimal  类型

## 5、**数据库****SQL****开发规范** ##

• 充分使用表已经存在的索引查询

• 禁止使用  select \*  查询

• 禁止使用不含字段列表的  insert  语句

• 避免使用子查询  ,  可以把子查询优化  join  操作

• 避免使用  join  关联太多的表，建议不超过  5  个

• 减少同数据库的交互次数，使用  in  代替  or

• 禁止使用  order by rand ()  进行随机排序

• 确定不会有重复值时 使用  union all  而不是  union

• 拆分复杂的大  sql 为多个 小  sql

## 6、**数据库操作行为规范** ##

• 超过  100  万行的批量写操作  , 要 分批分次进行操作

• 禁止为程序使用的账号赋予 super  权限

• 对于程序连接数据库账号  ,  遵循权限最小原则

• 程序使用数据库账号只能在一个 DB 下使用  ,  不准跨库

• 程序使用的账号原则上不准有  drop  权限

### 58到家数据库30条军规解读： [https://www.jianshu.com/p/f634b5915872][https_www.jianshu.com_p_f634b5915872] ###

# 优化方面  #

## 1.存储引擎 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70][]

## sql慢查询的定位 ##

show variables like 'slow\_query\_log'

set global slow\_query\_log =on

set global slow\_query\_log\_file='/var/tmp/mysql/xxx-slow.log'

set global\_queries\_not\_useing\_indexes=on

set gloal\_query\_time=0.1(秒)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 1][]

Time:日志记录时间

User@host: 执行的用户及主机

Query\_time：查询耗费时间 Lock\_time 锁表时间  Rows\_sent 发送给请求放的记录条数

Rows\_examined: 语句扫描的记录条数

Set timestamp :语句执行的时间点

select .....  执行的具体语句

### mysqldumslow --help ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 3][]

### show profile工具 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 4][]

### sql 执行计划(explain) ###

table :对应的表

type:链接的类型（system,const,eq\_ref,ref,range,index,all）

possible\_keys:可能使用的索引

key:实际使用的索引

rows:预计扫描行数

extra:解析查询的额外信息（using index,using where ,usring temporary,usring filesort）

### 常见sql优化 ###

*   sql的查询一定要基于索引完成sql的结果集扫描
 *   避免索引列使用函数或者运算，这样会导致索引无效
 *  where字句中like%号，尽量防止右边
 *  使用索引扫描，联合索引中的列从左往右，命中越多越好
 *  尽可能使用SQL语句用到的索引完成排序，避免使用文件排序的方式
 *  查询有效的列信息即可，少用\*代理列信息
 *  永远用小结果集驱动与大结果集

1）以小结果集驱动能减少循环次数，从而减少对被动驱动结果集的访问，从而减少被驱动表的锁定

2）执行时以最左边的表为基础表循环与右边表数据做笛卡尔积

[MySQL]: https://dev.mysql.com/doc/refman/5.7/en/keywords.html
[https_www.jianshu.com_p_f634b5915872]: https://www.jianshu.com/p/f634b5915872
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70]: /images/20230528/bbdd27cd6ed94b35ad24a95e7bd6fbc4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 1]: /images/20230528/4eb474757f054fc3a40714fb5b47a5dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 2]: /images/20230528/cfacc347fea047a5974a0e0087bc7d11.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 3]: /images/20230528/d31f57bc09334a25b263f54980eaaac6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NkbXhkemI_size_16_color_FFFFFF_t_70 4]: /images/20230528/c653d69e31c14ec3b97269e69011cbb5.png