MySQL规范及优化

最新推荐文章于 2024-12-19 14:27:47 发布

hering.lee

最新推荐文章于 2024-12-19 14:27:47 发布

阅读量174

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： MySQL优化

本文链接：https://blog.youkuaiyun.com/weixin_43249277/article/details/82867770

数据库专栏收录该内容

3 篇文章

订阅专栏

本文探讨了数据库设计的基本规则，包括避免在数据库中进行复杂运算、控制单表数据量、遵循数据库三大范式以及拒绝大SQL、大事务和大批量操作。同时，详细介绍了字段规则，如字符串转数字、使用Enum或SET、避免NULL字段、少用TEXT/BLOB类型。此外，还讲解了索引规则，强调了合理添加索引的重要性，并提供了SQL优化技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、基本规则

1.1 、尽量不在数据库做运算
运算尽可能移到程序端CPU。

1.2、控制单表数据量

     1.2.1、一年内的单表数据量预估
      >纯INT不超过1000W
      >含CHAR不超过500W

     1.2.2、合理分表不超载
      > 取模
                例如：id%n=0 —> tabel0;
                 id%n=1 —> tabel1;
                 id%n=2 —> tabel2;
                    …
      > 时间
                 例如可以根据月份、季度、年进行分表
      > 哈希
                 例如一个电商网站，需要记录用户的所有购买记录，如果将所有的记录放到
                 一张userBuy表中，势必会非常巨大，所以我们可以通过对userid进行分表处
                 理，将不同的用户购买记录放到不同的分表中，比如对userid做crc32的hash
                 计算，然后用得到的哈希值进行取余，取余的余数就是你的分表数，余数则
                 是该userid需要记录进入的表。
      > 区域
                 例如主键id范围进行分表

      > 引擎.
                 可以使用Mysql的MyISAM存储引擎，因为其支持MERAGE类型，结合
               UNION来实现数据表的分割和数据同步。这种方式的优点就是可以保留表
                 的外键、事务以及其它属性，但是缺点就是查询性能比较低，同步也不够
                 灵活，所以大多不推荐这种方式实现分表。

1.3、遵循三大范式

      > 第一范式（1NF）
                1NF是指数据库中表的每一列都是不可分割的基本数据项，同一列的数据不
              能有多个值。如果出现重复，就可能需要定义一个新的实体（即：生成一
              个新的表）来描述关系。
              例如：公司中某个人员隶属于多个部门，那么在人员中，这个人部门字段
              可以存储多个值，但是这样违反了1NF。
              解决办法：此时可以生成一个新表，表示人员与部门的关系。

      > 第二范式（2NF）
                 2NF是在1NF的基础上建立的，所以满足2NF的必须满足1NF；
              2NF要求数据库中表的每个实例或行必须能唯一的区分。为实现区分通                            常需要为表加上一个列（主键）或多列（联合主键）；
              2NF要求实体的属性完全依赖于主键。如果存在依赖主键一部分的属性
              那么这个属性和主键应该分离出来形成一个新的实体，新实体于原实体之间
              是一对多的关系。简而言之，就是2NF非主属性不能部分依赖于主键。

      > 第三范式（3NF）
                 3NF依赖于2NF；
                 3NF要求一个数据库表中不包含在其他表中已经存在的非主键的字段；
                 3NF表属性不能依赖其他表中非主键的属性；

     1.4、拒绝3B
                     >大SQL（Big SQL）
                     >大事务（Big Transaction）
                     >大批量（Big Batch）

2、字段规则

                     >将字符串转化为数字：查询快、占用空间小，比如无符号INT存储IP。
                     >优先使用Enum或SET:可能值已知且有限的字符串类型。
                     >避免使用NULL字段：很难进行查询优化、NULL列加索引，需要额外的
                        空间、含NULL复合索引无效。
                     >少用并拆分TEXT/BLOB
                        Text类型处理性能远低于VARCHAR(强制生成临时表、浪费更多空间)
                     >不在数据库中存图片

3、索引规则

                     >谨慎合理添加索引
                        改善查询
                        减慢更新
                        能不加索引则不加索引
                     >大量重复值索引无效
                        例如：性别列
                     >不在索引列做数学或函数运算
                        无法使用索引
                        导致全表扫描
                     >尽量不用外键
                        可到达其它表意味着“锁”
                        高并发容易死锁

4、SQL优化

                     4.1、OR
                        · 同一字段将OR改写为IN
                        · 不同字段将OR改写为UNION
                           举例：Select * from opp where phone=‘010-88886666’ or cellPhone=
                                    ‘13800138000’;
                                      优化：
                                       Select * from opp where phone=‘010-88886666’
                                       union
                                       Select * from opp where cellPhone=‘13800138000’;

                     4.2、避免负向查询和%前缀查询
                              >NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
                              >%前缀：使用不了索引、导致全表扫描。

国庆就要放假了，提前一天回家，啊啊啊啊啊啊啊，没心情写了，暂时就这些吧先，祝万千猿友假期愉快！