开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第14天，点击查看活动详情

• 尽管在 Java 入门中已经对数据库有了一些学习，但是仍然不够深入，学习的只是最基本的 CRUD，以及常见的表操作，这里将深入学习 MySQL 性能优化与分析、 MySQL 事务与引擎、MySQL 日志以及非关系型数据库 Redis 。
• 关于 MySQL 的高级知识，如安全、视图、函数、存储过程、触发器、事件等内容，在日常开发中极少应用，这里不再过多叙述 (即使这里作出叙述也是对官方文档的照搬照抄，所以并无必要) ，如果后续工作中有具体的应用场景，将学习 MySQL 5.7 版本官方文档：dev.mysql.com/doc/refman/…

1.性能优化

1.1.语句优化

• 使用查询缓存优化查询：

  大多数的 MySQL 服务器都开启了查询缓存。这是提高性能最有效的方法之一，而且这是被 MySQL 引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放入一个缓存中。

SELECT username FROM user WHERE signup_date >= CURDATE()
SELECT username FROM user WHERE signup_date >= '2021-11-20‘

尽量避免使用类似 CURDATE() 、NOW()、RAND() 的 SQL 函数，诸如此类的函数不会开启 SQL 查询缓存。应使用变量来代替。

• 使用 Limit 1

  如果我们只需要 1 条结果，最好增加 Limit 1 限制，MySQL 引擎会在查询到一条数据后停止搜索，而不是继续往下查询。同时删除数据的时候也尽量使用 Limit 限制，不仅可以控制删除的条数，也可以缩小间隙锁的范围

• 子查询优化分页查询

  利用子查询优化超多分页场景。比如 LIMIT offset , n 语句在 MySQL 中是获取 offset + n 的记录，再返回 n 条。而利用子查询则是查出 n 条，通过 ID 检索对应的记录出来，可以提高查询效率。

• 避免使用 SELECT *

  从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果我们的数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。 所以，我们应该养成一个需要什么就取什么的好的习惯。

• 切记不要使用 ORDER BY RAND()

  如果你真的想把返回的数据行打乱了，你有 N 种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL 会不得不去执行 RAND() 函数 (很耗 CPU 时间) ，而且这是为了每一行记录去执行，然后再对其排序。就算是使用了 Limit 1 也无济于事 (因为要排序)。

• 拆分较大的 DELETE 或 INSERT 如果我们需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，我们需要非常小心，要避免我们的操作让我们的整个网站停止服务。这两个操作会锁表。 Apache 有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。如果我们把我们的表锁上一段时间，那么对于一个有很高访问量的站点来说，一段时间所积累的访问进程/线程，数据库链接，打开的文件数，可能会导致服务器挂掉。所以执行该操作时可以添加 LIMIT 控制数量。如：

  DELETE FROM user WHERE signup_date <= '2021-11-20' LIMIT 1000;
  

• 尽可能的避免赋值为 NULL 如果不是特殊情况，尽可能的不要使用 NULL。在 MYSQL 中对于 INT 类型而言，EMPTY 是 0 ，而 NULL 是空值。而在 Oracle 中 NULL 和 EMPTY 的字符串是一样的。NULL 也需要占用存储空间，并且会使我们的程序判断时更加复杂。当然现实情况是很复杂的，依然会有些情况下，我们需要使用 NULL 值。

1.2.表设计优化

• 永远为每张表设置一个 ID 主键 我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的 (推荐使用 UNSIGNED ) ，并设置上自动增加的 AUTO_INCREMENT 标志。 就算是我们 users 表有一个叫 "email" 的字段，我们也尽量避免让它成为主键。使用 VARCHAR 类型来当主键会使得性能下降。

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

• 固定长度的表会更快

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

• 垂直分割 "垂直分割" 是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

• 越小的列会越快 对于大多数的数据库引擎来说，硬盘读写可能是最重大的瓶颈。所以，把我们的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列 (如字典表，配置表)，那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果我们不需要记录时间，使用 DATE 要比 DATETIME 好得多。

• 选择正确的存储引擎

  MyISAM 适合于一些需要大量查询的应用，但是对于大量写操作的支持不是很好。甚至一个 update 语句就会进行锁表操作，这时读取这张表的所有进程都无法进行操作直至写操作完成。另外 MyISAM 对于 SELECT COUNT(*) 这类的计算非常迅速的。InnoDB 是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 要慢。它支持 "行锁" ，于是在写操作比较多的时候，会更优秀。并且还支持更多的高级应用，如事务。

  MyISAM 是 MYSQL5.5 版本以前默认的存储引擎，基于传统的 ISAM 类型，支持 B-Tree，全文检索，但是不是事务安全的，而且不支持外键。不具有原子性。支持锁表。InnoDB 是事务型引擎，支持 ACID 事务( 实现 4 种事务隔离机制 )、回滚、崩溃恢复能力、行锁。以及提供与 Oracle 一致的不加锁的读取方式。InnoDB 存储它的表和索引在一个表空间中，表空间可以包含多个文件。下文引擎中详细介绍各类引擎特点。