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 条件的属性上使用函数或者表达式

  3. 例如

  4. 会导致全表扫描

  5. SELECT uid FROM t_user WHERE from_unixtime(day)>=’2019-10-09’

  6. 优化写法

  7. SELECT uid FROM t_user WHERE day>=unix_timestamp(‘2019-10-09 00:00:00’)

  8. 不要负向查询,以及 %开头 的模糊查询

  9. 负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描

  10. %开头 的模糊查询,会导致全表扫描

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

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

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

  14. 应用程序必须捕获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
粉丝
17
喜欢
26
收藏
11
排名:557
访问:5953
私信
所有博文