MySQL数据库架构设计及优化

szrsu

583

63页

12次

2022-03-30

25墨值下载

目录 
一、  数据库开发规范的制定 .............................................................................................................4 
1  数据库设计规范简介 .............................................................................................................4 
2  数据库命名规范 .....................................................................................................................4 
2.1  所有数据库对像名称必须使用小写字母并用下划线分割 ..............................4 
2.2  所有数据库对像名称禁止使用 MySQL 保留关建字 .........................................4 
2.3  数据库对像的命名要能做到见名识义，并且最好不要超过 32 个字符 ......4 
2.4  临时库表必须以 tmp 为前缀并以日期为后缀 ...................................................4 
2.5  备份库，备份表必须以 bak 为前缀并以日期为后缀.......................................4 
2.6  所有存储相同数据的列名和列类型必须一致 ....................................................4 
3  数据库基础设计规范 .............................................................................................................5 
3.1  所有表必须使用 Innodb 存储引擎 .......................................................................5 
3.2  数据库和表的字符集统一使用 UTF8 ...................................................................5 
3.3  所有表和字段都需要添加注释 ..............................................................................5 
3.4  尽量控制单表数据量的大小...................................................................................5 
3.5  谨慎使用 MySQL 分区表 .........................................................................................5 
3.6  尽量做到冷热数据分离，减小表的宽度 .............................................................5 
3.7  禁止在表中建立预留字段 .......................................................................................6 
3.8  禁止在数据库中存储图片，文件等二进制数据 ...............................................6 
3.9  禁止在线上做数据库压力测试 ..............................................................................6 
3.10  禁止从开发环境，测试环境直连生产环境数据库 ...........................................6 
4  索引设计规范 ..........................................................................................................................6 
4.1  限制每张表上的索引数量 .......................................................................................6 
4.2  每个 Innodb 表必须有一个主键 ............................................................................6 
4.3  哪些常见列可建立索引 ...........................................................................................6 
4.4  如何选择索引列的顺序 ...........................................................................................6 
4.5  避免建立冗余索引和重复索引 ..............................................................................7 
4.6  对于频繁的查询优先考虑使用覆盖索引 .............................................................7 
4.7  尽量避免使用外键 ....................................................................................................7 
5  数据库字段设计规范 .............................................................................................................7 
6  数据库 SQL 开发规范 ............................................................................................................8 
6.1  建议使用预编译语句进行数据库操作 .................................................................8 
6.2  避免数据类型的隐式转换 .......................................................................................9 
6.3  充分利用表上已经存在的索引 ..............................................................................9 
6.4  一个 SQL 只能利用到复合索引中的一列进行范围查询 .................................9 
6.5  使用 left join  或 not exists  来优化 not in  操作 ................................................9 
6.6  数据库设计时，要考虑以后扩展 ..........................................................................9 
6.7  禁止使用 select * .......................................................................................................9 
6.8  禁止使用不含有字段列表的 insert 语句 .............................................................9 
6.9  避免使用子查询 ........................................................................................................9 
6.10  避免使用 JOIN 关联太多的表 ................................................................................9 
6.11  减少同数据库的交互次数 .................................................................................... 10 
6.12  使用 in  代替 or ....................................................................................................... 10 

6.13  禁止使用 order by rand()进行随机排序 ........................................................... 10 
6.14  HERE 从句中禁止对列进行函数转换和计算 ................................................... 10 
6.15  在明显不会有重复值时使用 union all 而不是 union .................................... 10 
6.16  拆分复杂的大 SQL 为多个小 SQL ...................................................................... 10 
7  数据库操作行为规范 .......................................................................................................... 10 
7.1  大批量写操作，要分批执行................................................................................ 10 
7.2  大表的数据结构修改要谨慎................................................................................ 10 
7.3  禁止为程序使用的账号赋予 super 权限 .......................................................... 11 
7.4  对于程序连接数据库账号，遵循权限最小原则 ............................................ 11 
二、  电商实例数据库结构设计 ..................................................................................................... 11 
1  用户模型设计 ....................................................................................................................... 11 
1.1  用户登录表 .............................................................................................................. 14 
1.2  用户信息表 .............................................................................................................. 15 
1.3  用户级别表 .............................................................................................................. 16 
1.4  用户地址表 .............................................................................................................. 17 
1.5  用户积分日志表 ..................................................................................................... 17 
1.6  用户余额变动表 ..................................................................................................... 18 
1.7  用户登录日志表 ..................................................................................................... 19 
2  商品模型设计 ....................................................................................................................... 21 
2.1  品牌信息表 .............................................................................................................. 21 
2.2  分类信息表 .............................................................................................................. 22 
2.3  供应商信息表 .......................................................................................................... 23 
2.4  商品信息表 .............................................................................................................. 23 
2.5  商品图片表 .............................................................................................................. 25 
2.6  商品评论表 .............................................................................................................. 25 
3  订单模型设计 ....................................................................................................................... 26 
3.1  订单主表................................................................................................................... 27 
3.2  订单详情表 .............................................................................................................. 28 
3.3  购物车表................................................................................................................... 28 
3.4  仓库信息表 .............................................................................................................. 29 
3.5  商品库存表 .............................................................................................................. 30 
3.6  物流公司信息表 ..................................................................................................... 31 
4  DB 规划 .................................................................................................................................. 32 
三、  MySQL 执行计划...................................................................................................................... 34 
1  执行计划分析 ....................................................................................................................... 34 
2  执行计划中的内容 .............................................................................................................. 34 
2.1  ID 列 .......................................................................................................................... 34 
2.2  SELECT_TYPE 列 ...................................................................................................... 35 
2.3  TABLE 列 ................................................................................................................... 36 
2.4  PARTITIONS  列 ...................................................................................................... 36 
2.5  TYPE 列 ..................................................................................................................... 37 
2.6  Extra 列 ..................................................................................................................... 37 
2.7  POSSIBLE_KEYS  列 ................................................................................................ 37 
2.8  KEY 列........................................................................................................................ 38 

of 63

25墨值下载

mysql，mysql优化，mysql规范

关注