数据库命名规范
- 数据库对象名称使用小写字母并用下划线分割
- 名称禁止使用MySQL保留关键字
- 命名见名识义，长度不能太长
sc_userdb 数据库 user_account 账户表
- 临时表 tmp为前缀, 日期后缀
- 备份表 bak前缀, 日期后缀
- 储存相同数据的列名和数据类型要一致
关联列查询, 数据类型不一致时会转换, 造成性能大幅下降
数据库基本设计规范
5.6版本以后成为了默认引擎
支持事务, 行级锁, 更好的恢复性, 高并发性能更好
utf8字符集 单个汉字占用3个字节, ASCII码占用一个
- 表和字段添加注释
- 尽量控制单表数据量大小, 建议在500万内
500万并不是数据库限制
历史数据归档, 分库分表
分区表在物理上存储在多个文件, 逻辑上表现为一个
谨慎选择分区键, 跨分区查询效率很低
使用物理分表管理大数据
mysql限制最多4096列,每行数据大小不超过65535字节 ??
作用: 减少磁盘io, 保证热数据的内存缓存命中率
利用缓存, 避免读入无用冷数据 (select *)
经常一起使用的列放入一个表中, 避免过多的关联查询 (垂直拆分列)
预留的字段难以做到 见名识意
无法准确确认 储存的数据类型
后期对字段类型修改时会对 全表锁定
高磁盘io
- 禁止在线上做数据库压力测试(上线后)
- 禁止从开发测试环境直接连接生产环境数据库
数据库索引规范
查询时通过索引, 减少随机IO, 增加查询性能,过滤完的数据越少,要读的就越少
如果存在多个索引, MySQL在执行查找时, 会评估哪个是最佳方案 索引太多会加长MySQL优化器生成查询计划的时间, 影响了查询效率
索引可以增加查询效率, 但是会降低插入和更新的效率
innodb表中需要主键, 不能使用频繁更新的列作为主键, 不使用uuid MD5 hash 字符串作为主键
innodb是索引组织表, 数据的逻辑储存顺序按照主键索引排列
主键值频繁更新会使数据的逻辑顺序频繁变动, 造成大量IO操作
uuid md5 hash 不能保证顺序增长,为了保持索引的顺序,插入小的值会插在中间,后面的全部后移,造成大量io操作
主键建议使用单独列自增ID
select update delete 语句的 where 从句中的列
order by, group by, distinct 中的字段
如果上述从句和order by等中的字段有多个，并不要给每一列都上索引，建立联合索引更好
多表join查询的关联列上的索引： MySQL的join查询是嵌套循环,如果没有索引效率很差
区分度最高的在最左侧,比如主键,唯一索引,区分度为1,每个值都不一样,通过主键查找,更新数据的性能是最高的
字段长度小的列 往左排,字段长度越小,每一"页"储存的列就越多,数据量越大,io性能越好
以上俩点相似的，把使用最频繁的列放在左侧
覆盖索引是包含了所有要查询字段的索引
可以避免innodb表进行二次查找, 二级索引保存的是行的主键信息, 通过主键二次查询获取信息
覆盖索引包含了所需的信息,避免二次查找
覆盖索引按照键值顺序存储,读取时候就变成了顺序io，增加效率
避免使用外键约束，但是要建立外键索引=》关联列的索引
外键的作用是保证数据的完整性(主表与子表)一致性,一条数据在主表中存在,才能在子表中存在
在每次写操作时都要检查外键约束是否成立,降低性能
数据的一致性建议用其他方法保证
数据库字段设计规范
字段类型会直接影响数据库的性能,影响索引的建立：
字段越大，索引占用空间也越大，一个页中所能存下的索引也越少，索引变差
非负整数采用无符号整形存储，无符号最大值比有符号多一倍
将字符串转为数字类型存储
例如IP转为数字类型
inet_aton() # a to n
inet_ntoa() # n to a
varchar(N) N代表字符，可储存N个中文字，不是字节
过大的长度会消耗更多的内存：物理存储时按实际使用长度，读取时按字段设置长度读取到内存
不支持内存临时表，只能用磁盘临时表，还会造成二次查询
把blob，text分离到单独的扩展表中，避免select *
只能使用前缀索引，不能有默认值
其它关系型数据库中并不存在这种类型
优点：本身是字符串类型，但是以整形存储，最多可以存储65535种媒体值
缺点:
修改媒体类型的值需要使用alter语句，会生成元数据锁，造成堵塞
排序效率低：排序前需要先转换，把媒体值转为字符串再排序=》无法使用索引
ENUM不要储存 整型媒体值
在这种列上建立索引，在索引上会额外储存一个状态空还是非空，占用索引的空间
进行比较和计算时要对null值做特别的处理, 可能使索引失效
- 使用
timestamp datetime来储存时间
timestamp 存储范围小 1970 ~ 2038年
timestamp 实际上是int存储 但可读性更高
超出timestamp范围的，使用datetime
计算时不丢失精度, 适用金额
占用空间是由定义的宽度决定的，每4个字节可以存9个数字，小数点占用一个字节
可以存储比 bigint 更大的 整型 数据
数据库SQL开发规范
一次解析多次使用，只传参数，比传sql语句更高效
防止sql注入
一般在where从句中，当列类型和参数类型不一致时，会发生隐式转换
导致索引失效
避免使用 %a% 或者 %a 进行模糊查找，a%可以使用到索引
一个sql只能利用到联合索引中的一列进行范围查询，那么这一列要放在联合索引的右侧，否则这一列右面的索引不会被用到了
使用 left join 或 not exists 来代替 not in操作，not in一般会使索引失效
给数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大产生安全风险
如果字段里有text等类型，会二次读取出来
消耗更多的cpu和io资源
无法使用覆盖索引
减少表结构带来的影响
insert into test(a,b,c) values(1,2,3);
子查询产生临时表, 无法使用索引，如果结果集数据量越大影响越大
临时表消耗过多的CPU和IO资源
关联的表占用内存 （join_buffer_size）
关联操作会产生临时表
MySQL最多关联61个表 建议不超过5个
数据库更适合批量处理操作 ( 分页 )
合并多个相同的操作 (比如 alter修改表 )
in操作可以使用到索引
in的数量不要超过500个
会把所有符合条件的数据加载到内存中, 再排序
消耗大量的CPU io 内存资源
建议使用程序取随机值
会导致无法使用索引 (time问题)
- 在明显不会重复时, 使用
union all 不使用 union
union会把所有数据放到临时表中去重
union all 不会去重
一个sql只能用一个cpu进行计算，
拆分后可以通过并行执行来提高处理效率
数据库操作行为规范
大批量操作会造成严重的主从延迟
binlog日志为row格式时会产生大量的日志
避免产生大事务操作
大表数据结构的修改会造成严重的锁表操作
pt-online-schema-change 工具
数据库维护的权限
当达到最大连接数时，允许一个有super权限的用户连接
只能在一个DB下使用，禁止跨库
原则上不赋予drop权限