参考地址:
1、JavaGuide
2、(1) Mysql高性能优化规范建议 - 听风。 - 博客园
未与作者取得联系,侵删
本文在其基础上会有修改
数据库命名规范
- 所有数据库对象名称必须使用小写字母并用下划线分割
- 所有数据库对象名称禁止使用 MySQL 保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)
- 数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符
- 临时库表必须以 tmp_为前缀并以日期为后缀,备份表必须以 bak_为前缀并以日期 (时间戳) 为后缀
- 所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)
数据库基本设计规范
1. 所有表必须使用 Innodb 存储引擎
没有特殊要求(即 Innodb 无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用 Innodb 存储引擎(MySQL5.5 之前默认使用 Myisam,5.6 以后默认的为 Innodb)。
Innodb 支持事务,支持行级锁,更好的恢复性,高并发下性能更好。
2. 数据库和表的字符集统一使用 utf8mb4
mysql里面的utf8mb4才是真的utf8,参考
mysql使用utf8mb4经验吐血总结_胡金水的博客-CSDN博客_utf8mb4
3. 所有表和字段都需要添加注释
使用 comment 从句添加表和列的备注,从一开始就进行数据字典的维护
4. 尽量控制单表数据量的大小,建议控制在 500 万以内。
500 万并不是 MySQL 数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题。
可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小
数据库字段设计规范
1. 优先选择符合存储需要的最小的数据类型
原因:列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的 IO 次数也就越多,索引的性能也就越差。
方法:
a.将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据(ipv6怎么办)
MySQL 提供了两个方法来处理 ip 地址
- inet_aton 把 ip 转为无符号整型 (4-8 位)
- inet_ntoa 把整型的 ip 转为地址
插入数据前,先用 inet_aton 把 ip 地址转为整型,可以节省空间,显示数据时,使用 inet_ntoa 把整型的 ip 地址转为地址显示即可。
b.对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储
2. 尽可能把所有列定义为 NOT NULL
索引 NULL 列需要额外的空间来保存,所以要占用更多的空间。
进行比较和计算时要对 NULL 值做特别的处理
3. 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用 4 字节和 INT 相同,但比 INT 可读性高
超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储
经常会有人用字符串存储日期型的数据(不正确的做法)
- 缺点 1:无法用日期函数进行计算和比较
- 缺点 2:用字符串存储日期要占用更多的空间
4. 同财务相关的金额类数据必须使用 decimal 类型
- 非精准浮点:float,double
- 精准浮点:decimal
Decimal 类型为精准浮点数,在计算时不会丢失精度
占用空间由定义的宽度决定,每 4 个字节可以存储 9 位数字,并且小数点要占用一个字节
可用于存储比 bigint 更大的整型数据
索引设计规范
1. 限制每张表上的索引数量,建议单张表索引不超过 5 个
索引并不是越多越好!索引可以提高效率同样可以降低效率。
索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。
2. 每个 Innodb 表必须有个主键
Innodb 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种。
Innodb 是按照主键索引的顺序来组织表的
- 不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引)
- 不要使用 UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)
- 主键建议使用自增 ID 值
3. 常见索引列建议
- 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
- 多表 join 的关联列
4. 如何选择索引列的顺序
建立索引的目的是:希望通过索引进行数据查找,减少随机 IO,增加查询性能 ,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。
- 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)
- 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO 性能也就越好)
- 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)
5. 对于频繁的查询优先考虑使用覆盖索引
覆盖索引:就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
覆盖索引的好处:
- 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的,对于 Innodb 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
看这里,看这里
文章总目录:博客导航
参考文章:https://blog.csdn.net/u_ascend/article/details/113393140