mysql数据库设计规范和优化

数据库的设计

1、数据库命名规范

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

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

• 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

优化方面 

1.存储引擎

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(秒)

Time:日志记录时间

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

Query_time：查询耗费时间 Lock_time 锁表时间  Rows_sent 发送给请求放的记录条数

Rows_examined: 语句扫描的记录条数

Set timestamp :语句执行的时间点

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

mysqldumslow --help

show profile工具

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）执行时以最左边的表为基础表循环与右边表数据做笛卡尔积