Mysql 优化之统一设计规范

最新推荐文章于 2024-07-18 19:18:48 发布

迷途小雨

最新推荐文章于 2024-07-18 19:18:48 发布

阅读量113

点赞数

分类专栏： Mysql 文章标签： Mysql设计 Mysql规范 Mysql优化

本文链接：https://blog.csdn.net/weixin_42715180/article/details/103261679

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库基本设计优化

所有数据库名称必须使用小写字母并使用下划线分割
所有数据库对象名称禁止使用Mysql保留关键字
数据库对象命名要做到见名识义，并且最好不要过长（32个字符）（因为过长不仅开发会比较复杂，也会比较消耗内存）
临时表必须以tmp为前缀并且以日期为后缀（为了方便管理）
数据库备份库必须以bak为前缀以日期为后缀
所有存储相同数据的列名和列类型必须一致（一般这个列都是作为关联列的，主要是避免的隐式类型转换，减少消耗）
数据库表的存储引擎尽量使用Innodb（支持事务，行级锁，更好的恢复性，高并发下性能更好）
数据库和表的字符集统一UTF8（兼用性好）
所有表和字段都必须要添加好注释（从一开始就做好维护，方便日后自己和他人）
尽量控制单表的数据量大小，建议控制在500万内（这并不是数据库的限制，只是为了更好的性能，可以使用历史数据归档，分表分库等手段控制表的大小）
谨慎使用Mysql分区表（分区表在物理上表现为多个文件，逻辑上表现为一个文件，谨慎选择分区键，建议使用物理分表）
尽量做到冷热数据的分离，减少表的宽度（减少磁盘的IO，保证热数据的内存缓存命中率）
禁止在表中建立预留字段（在线上对预留字段的修改会有锁表操作，还不如进行需要时在添加）
禁止数据库中存储图片和二进制的文件（都通过保存链接的形式保存）

索引设计规范

（索引非常非常重要，索引虽好但是不可滥用，不然会起反效果）

控制每张表上的索引数量，建议单表索引不超过5个（索引并不是越多越好）
禁止给表的每一列添加索引（在Mysql5.6之前执行查询语句是都是只能用到一个索引）
每一个Innodb表都必须有一个主键（如果没有主键Mysql默认用第一个无符号非空索引作为主键）
- 不使用更新频繁的列作为主键，不使用多列作为主键
- 不使用 UUID，MD5，HASH，字符串列作为主键
- 主键建议使用自增主键
我们的索引一般改怎么创建
- SELECT，DELETE,UPDATE语句中WHERE从句中的列
- 包含在ORDER BY,GROUP BY,DISTINCT中的字段
如何选择索引列的顺序
- 从左到右的顺序来使用
- 区分度最高的列放在联合索引的最左侧
- 尽量使用字段小的列放在联合索引的最左侧
- 使用最频繁的列放在联合索引的最左侧
避免建立冗余索引和重复索引
对于频繁的查询优先考虑使用覆盖索引（就是包含所有列的索引）
- 避免Innodb表进行索引的二次查找
- 可以把随机IO变为顺序IO加快查询的效率
尽量避免使用外键
- 不建议使用外键约束，但表与表之间的关联列上一定要建立索引
- 外键可用于保证数据的参照性完整，但建议在业务代码上完成
- 外键会影响父表和子表的写操作从而影响性能

数据库字段设计优化

优先选择符合存储需要的最小的数据类型
- 尽量能用数字类型存储的数据就用数字类型存储
- 对于不会有负数存在的数据用无符号的数字类型存储
- VARCHAT（N）N代表的是字符数，不是字节数
- 过大的长度会消耗更多的内存（数据存在到内存是按照字段的定义参数存取的）
避免使用TEXT、BLOB数据类型
- 建议把这个大型字段存储到一个扩展表中
- TEXT、BLOB数据类型是只能使用前缀索引的
避免使用ENUM类型（枚举类型）
- 修改ENUM值需要使用ALTER语句
- ENUM值得ORDER BY 语句效率低下，需要额外的操作
尽可能的把所有列定义为NOT NULL
- 索引NLLl列需要额外的空间保存，所以占用更多的空间
- 进行比较和计算式要对NULL值做额外的处理
同财务相关的金额类数据，必须使用decimal数据类型
- decimal为精准浮点数，在计算是不会丢失精度
- 该类型占用的空间按定义的宽度为准
- 可用于存储比bigint更大的整数数据
建议使用预编译语句进行数据库的操作
- 只传参数，比传递sql语句更加高效
- 相同的语句可以一次解析多次使用，提高处理效率
避免数据类型的隐式转换
- 隐式转换会导致索引的失效
- 例如：select name from tb_account where id = ‘11111’ 在数据库中id是一个数字类型，这边填字符串类型就是导致索引失效
充分利用表上已经存在的索引
- 避免使用双%的查询条件。如 name like ‘%陈%’
- 一个sql只能利用到复合索引中的一列进行范围查询
- 使用 left join 或者not exists 来优化not in 操作
禁止使用SELECT * 必须使用 SELECT 《字段列表》查询
- 消耗更多的cpu和IO以及网络带宽
- 无法使用覆盖索引
- 可以减少表结构变更带来的影响
避免子查询，可以把子查询优化成join操作
- 子查询结果集无法使用索引
- 子查询会产生大量的临时表操作，如果临时表过大会影响效率
- 消耗更多的cpu和IO资源
避免使用JOIN关联太多的表
- 关联的表太多会产生大量的临时表
- Mysql最多允许关联61张表，建议不要超过5张
禁止使用order by rand()进行随机排序
- 会把所有符合条件的数据装载到内存中进行排序
- 会消耗大量的内存、CPU资源、IO资源
- 推荐现在程序中获取一个随机值，然后根据随机值在数据库取数据
WHERE 从句中禁止对列使用函数或者进行计算（都会导致无法使用索引）
拆分复杂的大SQL为多个小SQL
- Mysql一个SQL只能使用一个cpu进行计算
- SQL拆分后可以通过并行执行来提高效率