mirror of https://github.com/qiurunze123/miaosha
12 KiB
12 KiB
mysql数据库设计与优化与架构 模拟场景（京东商城）
任何优化多需要场景，本次所有的场景为京东商城的数据库设计模拟！
有问题或者宝贵意见联系我的QQ,非常希望你的加入！
##简介：
设计：
1.数据库开发规范的制定
2.数据库结构与设计
3.mysql执行计划的分析
4.mysql数据库的备份和恢复
5.mysql高性能高可用架构变迁
场景说明
用户登录->选购商品->加购物车->检查库存->提交订单->货到付款-- Y -- 发货
|N--订单付款
用户模块 -- 完成用户注册和登录验证
商品模块 -- 前后台商品管理和游览
订单模块 -- 订单和购物车的生成和管理
仓配模块 -- 仓库库存和物流的管理
数据库设计规范
逻辑设计 + 物理设计
1.数据库命名规范
2.数据库基本设计规范
3.数据库索引设计规范
4.数据库字段设计规范
5.数据库的sql开发规范
数据库操作行为规范（运维标准）
数据库设计规范
1.所有的数据库对象名称必须使用小写字母并用下划线分割（mysql数据库大小写敏感）
例如: 不同的数据库名 DbName dbname 不同的表名 Table table taBLe
2.所有数据库对象名称禁止使用mysql保留关键字
例如 ：select id , username , from ,age from table_user 会造成mysql 歧义
3.数据库对象的命名要做到见名识义，并且最好不要超过32个字符
例如：秒杀用户表 miaosha_userdb good!!
用户账号表 user_account good!!
4.所有的临时表必须义tmp为前缀并且已日期为后缀，备份库备份表必须义bak为前缀并且已日期为后缀，大的可以到处sql
5.所有的数据存储的列名和列类型必须一致
custom_inf order_master
customer_id int unsigned customer_id int unsigned
必须一致否则会影响性能，一般这种列都是作为表的关联列，如果两个字段的数据类型不同，数据库则会进行隐式的数据类型
转换降低性能，造成列上的索引失效！！
存储规范
1.所有的表必须使用Innodb存储引擎 支持事物行级锁，更好的恢复性，并发性能更好
2.大部分数据库表字符集要统一 （大部分使用UTF-8） 编码乱码
3.所有的表和字段都要添加注释 数据字典的维护
4.尽量控制单表的数据量大小，建议控制在500万以内 当然500万并不是mysql的限制
过大可以通过历史数据归档，分库分表的手段来控制数据量的大小（订单表一类的比较重要）
5.尽量做到冷热数据分离，减少表的宽度 列最大4096列
减少磁盘io,保证热数据的内存缓存命中率，控制列数量也可以更加有效的利用缓存，避免读入无用的冷数据
经常使用的列放在一个表中
6.禁止在表中建立预留字段
预留字段命名很难做到见识义，无法选择合适的类型，对预留字段修改会对表进行锁定
行为规范
1.禁止存储图片，文件等二进制文件，造成大量的io操作 （存在相应的文件服务器上）
2.禁止再线上进行数据库的压力测试
3.禁止从开发环境测试环境直连生产环境
索引设计规范（不要滥用索引）
1.建议单表索引数量不超过5个 索引并不是越多越好过多索引降低效率，优化器过多索引选出最优解
innodb 是按照那个索引的顺序来组织表的呢 主键
不使用更新频繁的列作为主键，不使用多列主键
不使用uuid,md5,hash 字符串列作为主键 不能保证顺序增长
2.那些哪些列上建立索引？？？
1.我们经常在select update delete语句的where从句的列建立索引
2.包含在order by , group by , distinct 中的字段
3.多表join的关联列
3.如何选择索引的顺序？
联合索引中 索引从左到右的顺序
建立索引的目的 查询数据的时候可以根据索引来进行数据查找-- 从而减少磁盘的随机io , 增加查询的性能，所以
如果我们的索引能够过滤出更少的数据那么我们从磁盘读入的数据则越少
1.区分度最高的列放在联合索引的最左侧 区分度--数据唯一值的数量/总行数 区分度最大的就应该是主键了
2.尽量字段长度小的列放在联合索引的最左侧
3.使用最频繁的列放在最左侧
4，尽量避免冗余和重复的索引 重复索引了： primary key(id) , index(id) , unique index(id)
冗余： index(a,b,c) , index(a.b)
5.对于频繁使用的查询先优先考虑覆盖索引（包含了所有的查询字段的索引）
6.尽量使用外键 不建议使用外检约束，但是一定要在表与表之间的关联键上建立索引
数据库字段设计规范
1.优先选择符合存储需要的最小的数据类型
1.将字符串转换成数字类型存储 INET_ATON('255.255.255.255') = 4294967295 字符串转ip
将字符串转换成数字类型存储 INET_NTOA('4294967295') = 255.255.255.255 ip 转字符串
2.对于非负数据采用无符号整形进行存储 signed int -2147483648 -- 2147483647
unsigned int -0 -- 4294967295
3.VARCHAR(N)中的N代表的是字符数而不是字节数 使用UTF-8存储汉字varchar(255) = 765个字节 存储255个汉字
4.过大的长度会消耗更多的内存 varchar是一个可变的长度
2.避免使用text,blob数据类型 建议blob或者时text分离到单独的表中
避免使用enum数据类型
3.尽可能的所有列都定义为NOT NULL
索引null列需要额外的空间来保存，所以需要占用更多的空间
进行比较和计算的时候要对null值进行特别的处理
4.字符串存储日期型的数据不是正确的
无法用日期函数来进行计算和比较
用字符串存储日期要占用更多的空间
5.timestamp 和datatime 类型存储时间
timestamp 存储范围有限制 1970-01-01 00:00:01 ～2038-01-19 03:14:07
timestamp占用4字节和INT相同，但是INT可读性能高
6.财务相关的金额类数据，必须由decimal类型存储
decimal类型为精准的浮点数，在计算时不会丢失精度
占用空间由定义的宽度来决定
可用于存储比bigint更大的整数类型
数据库sql开发规范
1.建议使用预编译语句对数据库进行操作
2.避免数据类型的隐式转换 不同表的相同列的数据类型不一致 会导致索引失效
3.重复利用已经存在的索引
1.避免使用双%%的查询条件 如 a like '%1323%'
2.一个sql只能利用到复合索引中的一列进行范围查询
有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到，
在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧
3.使用left join 或者not exists 来优化not in（偶尔也会导致索引失效） 操作
4.程序链接不同数据库要使用不同的账号，禁止跨库连接为迁移和分库分表做预备
5.禁止select * 必须使用select <字段列表> （* 无法覆盖索引 减少表结构变更 对程序带来的影响）
6.insert 明确字段列表
7.禁止使用子查询，可以把子查询优化为join操作
子查询结果集无法使用索引
子查询会产生临时表操作，如果子查询数据量大则会更严重
8.避免使用过多的join 关联表
于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置
在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，
所占用的内存也就越大
如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，
就会影响到服务器数据库性能的稳定性
同时对于关联操作来说，会产生临时表操作，影响查询效率
Mysql最多允许关联61个表，建议不超过5个
9.减少同数据库的交互次数 多个相同的操作合并在一起
10.对应同一列进行or判断时，使用in代替or
in 的值不要超过500个
in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引
11.禁止order by rand() 进行随机排序
会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序
并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源
推荐在程序中获取一个随机值，然后从数据库中获取数据的方式
12.where 从句禁止对列进行函数转换和计算(导致无法使用相关列的索引)
SELECT（正确写法）
*
FROM
miaosha_message
WHERE
create_time >= '20190101'
AND create_time < '20190102'
SELECT （错误写法）
*
FROM
miaosha_message
WHERE
date
(create_time) = '20190101'
13.不会有重复值时使用UNION ALL 而不是UNION
UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL 不会再对结果集进行去重操作
14.拆分大sql变为小sql
大SQL:逻辑上比较复杂，需要占用大量CPU进行计算的SQL
MySQL 一个SQL只能使用一个CPU进行计算
SQL拆分后可以通过并行执行来提高处理效率
数据库操作行为规范
过大数据的（100万）批量写操作要分批多次操作
1.大批量操作可能会导致严重的主从延迟
2. binlog日志为row格式时会产生大量的日志
大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，
产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
3. 避免产生大事务操作
大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响
特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批
4.对于大表的修改使用pt-online-schema-change
1.原理： 会在原表的结构上建造一个新表 复制数据
2.避免延迟，修改时锁表
5.禁止super权限滥用
6.数据账号连接最小