MySQL 数据库设计和注意事项

以下是个人见解,写错了,或者我本人理解错误,敬请谅解
如果采用本文章信息自建 MySQL 出了问题,请不要来找我
如果有更好的方案,或者建议,欢迎提交版本
TimAutumnWind (转载请注明出处 https://learnku.com/articles/50270)

1.请使用 InnoDB 存储引擎

InnoDB 有更好的 CPU 和 IO 性能,更好的备份和锁表机制,提高统计和调试效率。
另外,作为一 个系统,InnoDB 支持多种关键功能,其中最重要的是事务日志和行级锁。事务日志记录真正的数据库事务,但更重要的是数据崩溃恢复和回滚。
基于 InooDB 方式的 IO,能给予更安全数据保护和更好性能表现。另外,在大多数的情况下,行级锁可以提供更高的并发性能,因为用户只锁定他们正在写的数据,而读数据永远不会被阻塞

2.数据表、数据字段必须加入中文注释

方便日后新人小哥,更快理解熟悉;并且可读性更好
同时在 status 这类字段上标注:0 表示删除,1 表示正常 等枚举值。

3.必须使用 UTF8mb4 字符集

utf8 是通用的字符集,mb4 在 utf8 上进行了扩展,支持 emoji 等新的字符。

4.禁止使用存储过程、视图、触发器、Event、join等

高并发大数据的互联网业务,架构设计思路是“解放数据库 CPU,将计算转移到服务层”,数据库擅长存储与索引,CPU 计算在业务层更合理。
如果是低并发,小流量,当我没说

5.禁止存储大文件或者大照片

当图片较多时,分页查询速度明显变慢,之前1秒内响应,加了照片字段后,需要4~5秒左右才能响应。大文件和照片存储在文件系统,数据库里存 URI 更好

6.表必须有主键,例如自增主键

  1. 主键递增,数据行写入可以提高插入性能,可以避免 Page 分裂,减少表碎片提升空间和内存的使用
  2. 使用数字类型主键,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率
  3. 无主键的表删除,在 ROW 模式的主从架构,会导致备库夯住
  4. 更多使用业务主键,在分库分表会有更多便利性。

7.禁止使用外键,如果有外键完整性约束,需要应用程序控制

外键会导致表与表之间耦合,Update 与 Delete 操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。

8.把字段定义为 NOT NULL 并且提供默认值

  1. null 的列使索引/索引统计/值比较都更加复杂,对 MySQL 来说更难优化。
  2. null 这种类型 MySQL 内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多。
  3. null 值需要更多的存储空,无论是表还是索引中每行中的 null 的列都需要额外的空间来标识。
  4. 对null 的处理时候,只能采用 is null 或 is not null ,而不能采用 =、in、<、<>、!=、not in 这些操作符号

9.禁止使用 TEXT、BLOB 类型

会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能。

10.禁止使用小数存储货币

都 0202年 了,使用整数吧,小数容易导致 钱对不上 或者 精度问题

11.使用 varchar(20) 存储手机号

  1. 涉及到区号或者国家代号,可能出现+-()
  2. 手机号会去做数学运算么?
  3. varchar 可以支持模糊查询,例如:like“138%”

12.禁止使用ENUM,可使用TINYINT代替

  1. 增加新的 ENUM 值要做 DDL 操作
  2. ENUM 的内部实际存储就是整数,你以为自己定义的是字符串?

13.关于索引设计

  1. 单表索引建议控制在 5个 以内

  2. 索引并不是越多越好!索引可以提高效率同样可以降低效率。

  3. 索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。

  4. 因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。

  5. 禁止在更新十分频繁、区分度不高的属性上建立索引,例如:年龄,性别这种

  6. 更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能

  7. 性别 这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似

  8. 建立组合索引,必须把区分度高的字段放在前面,能够更加有效的过滤数据

14.关于SQL使用规范

  1. 不要使用 INSERT INTO t_xxx VALUES(xxx),必须显示指定插入的列属性,容易在增加或者删除字段后出现程序BUG

  2. 不要在 WHERE 条件的属性上使用函数或者表达式

    # 会导致全表扫描
    SELECT uid FROM t_user WHERE from_unixtime(day) >= '2019-10-09'
    # 优化写法
    SELECT uid FROM t_user WHERE day >= unix_timestamp('2019-10-09 00:00:00')
  3. 不要负向查询,以及%开头的模糊查询
    负向查询条件:NOT!=<>!<!>NOT INNOT LIKE等,会导致全表扫描
    %开头的模糊查询,会导致全表扫描

  4. 如果需要搜索,可以使用全文索引

  5. 不要在大表使用 JOIN 查询,禁止大表使用子查询,会产生临时表,消耗较多内存与 CPU,极大影响数据库性能

  6. 尽量不要使用OR条件,必须改为IN查询。旧版本 Mysql 的OR查询是不能命中索引的,即使能命中索引,也没有必要

  7. 应用程序必须捕获SQL异常,并有相应处理

15. 多使用 EXPLAIN 优化

做MySQL优化,我们要善用 EXPLAIN 查看SQL执行计划

以下是优化的注释

说明
key 使用到的索引名。如果没有选择索引,值是 NULL。可以强制索引
type 连接类型,一个好的 sql 语句最好可以达到 range 级别。杜绝出现 all 级别
rows 扫描行数,该值是个预估值,心里有数就好了
extra 详细说明,注意常见的不太友好的值有:Using filesort, Using temporary
key_len 索引长度

16.SQL 语句中 IN 包含的值不应过多

在使用IN的时候,MySQL 对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如

select id from table_name where num in(1,2,3)

对于连续的数值,能用between就不要用in了,再或者使用连接来替换。

17. SELECT 语句务必指明字段名称

SELECT * 增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性。当表结构发生改变时,前断也需要更新。所以要求直接在 select 后面接上字段名。

18. 当只需要一条数据的时候,使用最好使用 limit 1

这是为了使 EXPLAIN 中 type 列达到 const 类型

19. 如果排序字段没有用到索引,就尽量少排序

20. 如果限制条件中其他字段没有索引,尽量少用or

or 两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用 union all 或者是 union (必要的时候)的方式来代替“or”会得到更好的效果

21. 尽量用union all代替union

union 和 union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。当然,union all 的前提条件是两个结果集没有重复数据。

22. 不使用ORDER BY RAND()

select id from `table_name` order by rand() limit 10000;
# 上面的sql语句,可优化为
select id from `table_name` t1 join (select rand() * (select max(id) from `table_name`) as nid) t2 ont1.id > t2.nid limit 1000;

23. 使用合理的分页方式以提高分页的效率

select id,name from table_name limit 866613, 20
# 使用上述 sql 语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用 limit 分页查询会越来越慢。
# 优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的 id 来限制下一页的起点。比如此列中,上一页最大的 id 是 866612 。sql可以采用如下的写法:
select id,name from table_name where id> 866612 limit 20

24. 避免在 where 子句中对字段进行 null 值判断

对于 null 的判断会导致引擎放弃使用索引而进行全表扫描。前文有说,不使用 null

25. 避免在 where 子句中对字段进行表达式操作

select user_id,user_project from table_name where age*2=36;
# 中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:
select user_id,user_project from table_name where age=36/2;

26. 对于联合索引来说,要遵守最左前缀法则

举列来说索引含有字段id,name,school,可以直接用id字段,也可以id,name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面

27. 必要时可以使用force index来强制查询走某个索引

有的时候 MySQL 优化器采取它认为合适的索引来检索 sql 语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用 force index 来强制优化器使用我们制定的索引。同时也可以为某些条件下,用不到索引的语句,进行强制索引搜索

28. 注意范围查询语句

对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。

29. 软优化

  1. 注意查询语句优化
  2. 优化子查询
  3. 使用索引
  4. 分解表,冷字段也可以和热字段分开
  5. 中间表
  6. 尽量使用 inner join,避免left join
  7. 利用小表去驱动大表,利用
  8. 分析表,检查表,优化表
  9. 增加冗余字段,减少查询,别什么都拆的很散
  10. 分库分表,问就是 一主多从 或者 直接上 多主多从
  11. 缓存集群

30. 硬优化 - 钱钱钱

a. 配置多核心和频率高的 cpu ,多核心可以执行多个线程.
b. 配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘 I/O 时间,从而提高响应速度.

c. 配置高速磁盘或合理分布磁盘:高速磁盘提高 I/O ,分布磁盘能提高并行操作的能力.

d. 打个比喻,阿里云的服务器做自建数据库,服务器硬盘最好是 ESSD 那种级别,别给我上高效云盘

31. 优化数据库参数

  1. MySQL 服务的配置参数都在 my.cnf 或 my.ini ,下面列出性能影响较大的几个参数.
  2. key_buffer_size => 索引缓冲区大小
  3. table_cache => 能同时打开表的个数
  4. query_cache_size =>查询缓冲区大小
  5. query_cache_type => 前面参数的开关, 0 表示不使用缓冲区,1表示使用缓冲区
  6. 但可以在查询中使用 SQL_NO_CACHE 表示不要使用缓冲区
  7. 表示在查询中明确指出使用缓冲区才用缓冲区, 即 SQL_CACHE.
  8. sort_buffer_size => 排序缓冲区
  9. 更多参数 => www.mysql.com/cn/why-mysql/perform...
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!
网管 @ xxx
文章
18
粉丝
19
喜欢
31
收藏
19
排名:549
访问:6388
私信
所有博文