mysql数据库设计规范和优化
本文共 2270 字，大约阅读时间需要 7 分钟。
数据库的设计
1、数据库命名规范
• 所有的数据库对象名称必须使用小写字母并使用下划线分割 ( mysql 数据库对大小写敏感 )
• 所有数据库对象名称禁止使用 MySqL 保留关键字 eg. from 关键字
•
• 数据库对象的命名要见名之意 , 最好不要超过 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条军规解读：
优化方面
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）执行时以最左边的表为基础表循环与右边表数据做笛卡尔积
转载地址：http://mujdi.baihongyu.com/
你可能感兴趣的文章
如何用好碎片化时间，让思维更有效率？
查看>>
No.147 - LeetCode1108
查看>>
No.174 - LeetCode1305 - 合并两个搜索树
查看>>
No.175 - LeetCode1306
查看>>
No.176 - LeetCode1309
查看>>
No.182 - LeetCode1325 - C指针的魅力
查看>>
mysql:sql create database新建utf8mb4 数据库
查看>>
mysql:sql alter database修改数据库字符集
查看>>
mysql:sql drop table (删除表)
查看>>
mysql:sql truncate (清除表数据)
查看>>
scrapy:xpath string(.)非常注意问题
查看>>
yuv to rgb 转换失败呀。天呀。谁来帮帮我呀。
查看>>
yuv420 format
查看>>
YUV420只绘制Y通道
查看>>
yuv420 还原为RGB图像
查看>>
LED恒流驱动芯片
查看>>
驱动TFT要SDRAM做为显示缓存
查看>>
使用file查看可执行文件的平台性，x86 or arm ?
查看>>
qt5 everywhere 编译summary
查看>>
qt5 everywhere编译完成后，找不到qmake
查看>>