MySQL数据库优化-架构优化-SQL优化

优化方向

1. 表结构设计优化
2. SQL优化
3. 增加缓存层
4. 数据库参数配置优化
5. 大事务优化
6. 分库分表分区
7. 主从复制，读写分离
8. 升级服务器硬件

表结构优化

1. 为什么数据库表的设计会影响性能？

• 字段的数据类型：不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer
• 数据类型的长度：数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度。
• 表的存储引擎：常用的存储引擎有MyISAM、InnoDB、Memory，不同的存储引擎拥有不同的特性，所以要合理的利用每种存储引擎的长处和优点来提供数据的性能。MyISAM不支持事务，表级锁，但是查询速度快，InnoDB支持事务，行锁。

1. 优化方法

• 设计时需要满足数据库设计三大范式，根据场景选用合适的数据类型及长度；
• 根据场景选用合适的数据库存储引擎；
• 有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键；
• 表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等

SQL优化

1. 使用查询缓存，并优化查询SQL，尽量使查询缓存生效。

• 大多数的 MySQL 服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被 MySQL 的数据库引擎处理的。

1. 当只要一行数据时使用 LIMIT 1。

• 当查询表时，如果你已经知道结果只会有一条，请尽量使用LIMIT 1。这样，数据库引擎会在找到一条数据后停止搜索，而不是继续向下查找，可以提高SQL性能。

1. 添加索引。

• 索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
• 联表查询的关联字段（类型必须相同）请建立索引。这样，MySQL 内部会启动为你优化 Join 的 SQL 语句的机制。详细原因请查阅Join原理。

1. 避免使用 select * 。

• 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。

• [SELECT *] 和[SELECT 全部字段]的 2 种写法有何优缺点?

  ①前者要解析数据字典，后者不需要。

  ②结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。

  ③表字段名称如果改变，前者不需要修改，后者需要改

  ④后者可以建立索引进行优化，前者无法优化

  ⑤后者的可读性比前者要高

1. 尽量避免使用 in 或者 not in。

• 类似 select phone from t1 where phone not in (select phone from t2) 这样的查询语句效率都非常慢，可以使用以下两种方式进行调整替换：

  ① 使用 EXISTS 或 NOT EXISTS 代替；

  ② 使用联表查询替换，例如上述SQL改为：

  select phone from t1 left join t2 on t1.phone = t2.phone where t2.phone is null 。

• 并不是所有情况均不能使用in或者not in。如果是确定且有限的集合时，可以使用，如 IN （0，1，2）。

1. 尽量避免使用or 。

• in，not in，or等关键字都会使索引失效，效率很低，使用时请慎重。

1. 尽可能的使用 NOT NULL。

• 除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持NOT NULL。
• 原因：①NULL需要额外的空间进行存储，而且在进行比较时，会使程序变得复杂；②在 Oracle 里，NULL 和 Empty 的字符串是一样的；

1. EXPLAIN 你的 SELECT 查询，更好地进行SQL优化。

• 使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
• EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

1. 千万不要 ORDER BY RAND() 。

• ORDER BY RAND() 目的：将返回的数据行顺序打乱。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL 会不得不去执行 RAND()函数(很耗 CPU 时间)，而且这是为了每一行记录去记行，然后再对其排序。就算是你用了 Limit 1 也无济于事(因为要排序)。
• 如果真实场景的确需要随机打乱查询结果，也不要属于上述语法，有其他N种方法可以进行替代。

1. 永远为每张表设置一个 ID。

• 我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的(推荐使用 UNSIGNED)，并设置上自动增加的 AUTO_INCREMENT 标志。
• 使用 VARCHAR 类型来当主键会使用得性能下降。
• 在 MySQL 数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
• 在这里，只有一个情况是例外，那就是“关联表”的多个“外键”共同组成主键。例如，“成绩表”关联了学生表和课程表，在成绩表中，学生 ID 和课程 ID 共同组成主键。

1. 从 PROCEDURE ANALYSE() 取得建议。

• PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和表中的实际数据，最终给你一些有用的建议。只有表中实际数据的数据量非常大时，这个建议才能尽可能地准确。
• 一定要注意，这些只是建议，你才是最终做决定的人。

1. 使用 ENUM 替换 VARCHAR

• ENUM 类型是非常快和紧凑的，其实际上保存的是 TINYINT，只是外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或 “部门”，你知道这些字段的取值是有限而且固定的，那么，请使用 ENUM替代VARCHAR。

1. 把 IP 地址存成 UNSIGNED INT

• INET_ATON() 可以把一个字符串 IP 转成一个整形；
• INET_NTOA() 可以把一个整形转成一个字符串 IP；
• 两者配合使用，可以使IP字段类型由Varchar(15)转为UNSIGNED INT(10)，可以节省空间，提高查询效率（定长），而且方便ip比较，例 IP between ip1 and ip2。

1. 固定长度的表会更快

• 如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static”或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
• 固定长度的表会提高性能，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
• 并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

1. 拆分大的 DELETE 或 INSERT 语句

• 如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。这种情况下很容易导致一个高访问量的站点，web服务Crash，甚至整台服务器崩溃。

1. 列越小，越快

• 根据业务场景，每个字段选用适当的类型和适当的大小，尽量使数据变得紧凑，这样可以减少对硬盘的访问。
• 但是，也要留有足够的扩展空间，否则后期维护会非常麻烦。

1. UNION ALL 要比 UNION 快很多。

• 如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用 UNION

  ALL。

• UNION 和 UNION ALL 关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。 ① 对重复结果的处理：UNION 在进行表链接后会筛选掉重复的记录，Union All 不会去除重复记录。 ② 对排序的处理：Union 将会按照字段的顺序进行排序；UNION ALL 只是简单的将两个结果合并后就返回。

增加缓存层

1. 增加缓存层，可以减少数据库连接，降低数据库压力并提高效率。
2. 方式：可以使用redis、memcache等增加缓存层。

数据库参数配置优化

mysql是一个高度定制化的数据库系统，提供了很多配置参数(如最大连接数、数据库占用的内存等)，这些参数都有默认值，一般默认值都不是最佳的配置，需要根据应用程序的特性和硬件情况对mysql的配置进行调整。

大事务优化

大事务：运行时间比较长，操作的数据比较多的事务。

风险：锁定太多的数据，造成大量的阻塞和锁超时，回滚时所需时间比较长，执行时间长容易造成主从延迟。

操作：避免一次处理太多的数据，移除不必要在事务中的select操作。

分库分表分区

1. 分库，可以按照业务分库，分流数据库并发压力，使数据库表更加有条理性。
2. 分表，当一个表的数据量很大的时候，查询就变的很慢，所以减少表里的记录的数量是优化的一种方式。这种方式就是将一张表的数据拆分成多张表，这样每张表的数量就减少了，查询速度就相对来说就快了一些。

• 分表的方法有很多种：

  ① 如果这个业务是有流程的，那么我们通常会设计一个历史表或者归档表，用来存放历史数据，这样能保证实时数据效率比较高。

  ②针对某一张大表，可以根据查询条件分成多张表，比如时间，我们可以将半个月或者10天的数据放到一张表里(看具体数据量，个人认为3000W是个上限，最好控制到百万级别)，每过10天，我们就自动创建一张数据库表，然后将数据插入，如此，按照时间查询，就要先定位去那种表中去取数，这样，效率能够得到大幅度提升，当然，这么解决也有问题，比如跨表，需要union多张表，而且跨表没法支持索引。

  ③上面的方法是我们直接通过程序和数据库实现的最原始的分表解决方案，现在市面上有一些成熟的软件如mycat，也是支持分表的。

  一般来讲，数据库中的大表毕竟只是一少部分，仅需要对这少部分大表进行分表就可以了，没必要小表也进行分表，增加维护开发难度

1. 分区，分区的实现道理和分表一样，也是将相应规则的数据放在一起，唯一不同的是分区你只需要设定好分区规则，插入的数据会被自动插入到指定的区里，当然查询的时候也能很快查询到需要区，相当于是对外不透明的分表，出现跨表数据库自动帮我们合并做了处理，使用起来比分表更加方便，但是分区也有自己的问题，每一个数据库表的并发访问是有上限的，也就是说，分表能够抗高并发，而分区不能，如何选择，要考虑实际情况。

主从复制，读写分离

• 一台MySQL服务器同一时间点支持的并发数是有限的，当大量并发(如秒杀活动等，很多用户都同一时刻访问数据库)时，一台数据库处理不过来，所以增加MySQL服务器的数量也是一种增强数据库性能的方式。
• 通过使用MySQL主从复制，增删改操作走Master主服务器，查询走Slaver从服务器，这样就减少了只有一台MySQL服务器的压力。

升级服务器硬件

• 当所有优化手段都用了，性能仍需要优化，那么只有升级MySQL服务器端硬件了，更快的磁盘IO设备，更强的CPU，更大的内存，更大的网卡流量(带宽)等。

参考资料：

https://baijiahao.baidu.com/s?id=1660232228855199630&wfr=spider&for=pc

https://blog.csdn.net/zhoupan301415/article/details/78257783

《MySQL性能优化的21个最佳实践》