mysqlsql优化工具_优化体系--mysql数据库性能优化方法总结，值得收藏

概述

数据库优化必须先分析再进行具体优化，前面在MySQL数据库SQL优化中介绍了一些方法，今天主要从创建表、设计表和具体的SQL优化方面介绍一些性能优化方法。

01优化了创建表时表的性能

1. 始终为每个表设置一个 ID

每个表都应有一个 ID 字段作为主键，该字段应为 INT 或 Type，并带有自动递增的标志。因为使用该类型的主键会降低性能。

这里只有一个例外，那就是“相关表”的“外键”，即表的主键是由几个单独表的主键组成的。我们称之为“外键”。例如，如果有一个带有学生 ID 的“学生表”，并且有一个带有课程 ID 的“课程表”，那么“成绩”就是“关联表”，它与学生表和课程表相关联，在成绩中，学生 ID 和课程 ID 称为“外键”，它们共同构成主键。

2. 为搜索字段编制索引

自

简单来说，在创建表时，如果该表的后续查询总是涉及某个字段，或者是代码中写的字段，则可以考虑创建索引。

3. 使用 ENUM 代替

ENUM类型非常快速和紧凑。实际上，它被另存为 ，但它在表面上显示为字符串。这样一来，使用这个字段来制作一些选择列表是相当完美的。

如果您有一个字段，例如“”，并且您知道这些字段的值是有限且固定的，则应使用 ENUM 而不是 。

ENUM 是 MySQL 数据库独有的字段类型，会影响迁移到其他数据库。因此，如果将来要改变数据库的情况，必须谨慎使用。

4. 尽可能使用 NOT NULL

您应该始终将字段保留为 NOT NULL，这样可以相对节省空间（NULL 也需要空格）。

5. 将 IP 地址另存为 INT

如果使用整数而不是 （15） 字段来保存 IP，则可以节省大量空间（需要编写 IP 转换函数）。

6. 必须使用 UTF8 字符集

万国码，无需转码，无乱码风险，节省空间

02设计表格时的性能优化

1. 选择合适的存储引擎

它适用于需要大量查询的应用程序，但对于大量写入来说不是很好。即使您只需要一个字段，整个表也会被锁定，在读取操作完成之前，其他进程（甚至是读取进程）都无法运行。此外，COUNT（*） 等计算速度超快。

这

趋势是一个非常复杂的存储引擎，对于一些小型应用程序来说，它会比 慢。他支持“行锁”，所以写入操作多一些会更好。此外，他还支持更高级的应用程序，例如交易。

相对来说，它支持事务、行级锁、更好的并发性能，以及 CPU 和内存缓存页面优化，这使得资源利用率更高，因此在大多数情况下最好使用引擎。

2.固定长度的桌子会更快

表中不包括以下类型的字段：TEXT、TEXT、BLOB。只要包含这些字段之一，那么该表就不是“固定长度的静态表”，因此MySQL引擎将以不同的方式处理它。

固定长度的表提高了性能，因为MySQL搜索速度更快，而且由于这些固定长度的表很容易计算下一段数据的偏移量，因此它们自然可以更快地读取。如果字段不是固定长度的，那么每次想要查找下一个条目时，程序都需要查找主键。

此外，固定长度的表更易于缓存和重新生成。但是，唯一的副作用是固定长度的字段会浪费一些空间，因为无论您是否使用它们，固定长度的字段都会分配大量空间。

3. 垂直细分

垂直分割是一种将数据库中的表变成列中的多个表的方法，可以降低表的复杂度和字段的数量，从而达到优化的目的。（因为现在的公司经常一个表里有很多字段，太复杂了，这也是后面需要划分的东西）。

示例 1：Users 表中有一个字段是家庭住址，此字段是可选的，在数据库中操作时，除了个人信息外，您不需要经常读取或覆盖此字段。那么为什么不把他放在另一张桌子上呢？这将使您的表具有更好的性能，并且在许多情况下，只有用户 ID、用户名、密码、用户角色等才会频繁用于用户表。较小的表总是表现更好。

示例 2：有一个名为“”的字段，每次用户登录时都会更新该字段。但是，每次更新都会导致清空表的查询缓存。因此，您可以将此字段放在另一个表中，这样它就不会影响您不停地读取用户 ID、用户名和用户角色，因为查询缓存将帮助您大大提高性能。

另外，需要注意的是，你不会经常加入这些分隔字段形成的表，否则性能会比不分割时差，而且极数会下降。

03优化SQL语句

1.使用查询缓存

检查是否开启了缓存

mysql> select @@query_cache_type;

开启缓存，修改f，最后添加，重启MySQL生效

query_cache_type = 1
query_cache_size = 600000

开启MySQL查询缓存可以大大降低数据库服务器的CPU使用率，开启前约为120%，开启后降至10%。但是，使用查询缓存有很多限制。如果使用方案是只读的，并且更新很少，请考虑启用查询缓存。

2. 当只有一行数据时，使用 LIMIT 1

在这种情况下，添加 LIMIT 1 可以提高性能。这样，MySQL数据库引擎在找到一段数据后将停止搜索该数据，而不是继续搜索与记录匹配的下一段数据。

3. 联接表时使用等效类型的示例并对其进行索引

如果有许多 JOIN 操作，则应对 JOIN 的字段进行索引，并且这些字段的类型应保持一致。

4. 避免*

从数据库读取的数据越多，查询速度就越慢。因此，有必要养成服用所需物品的好习惯。

5. 拆分大的 OR 语句

如果您需要在在线网站上执行大量查询，则需要非常小心，以避免相应的操作会相应地停止整个网站。因为这两个操作会锁表，所以一旦锁表，其他操作就无法进入。

执行这么大量的 and 可以分几个部分执行，每个部分的执行都暂停并再次执行。

04 其他

1. 您的询问

使用关键字将使您了解MySQL如何处理SQL语句。这可以帮助您分析查询语句或表结构中的性能瓶颈。

查看行列可以让我们发现潜在的性能问题。

2. 从 （） 获取建议。

（）将允许MySQL分析字段及其实际数据，并提供一些有用的建议（只是建议）。只有当表中有实际数据时，这些建议才会变得有用，因为您需要有数据才能做出一些重大决策。

mysql>select * from mysql.user procedure analyse();