MySQL性能优化（硬件、系统配置、表结构、SQL语句）

MySQL优化计划

大家一定都知道，在面试中提到数据库的时候，都会讨论到数据库优化相关的问题。互联网上关于数据库优化的文章琳琅满目。今天这篇文章，我就从几点来总结归纳一套MySQL数据库优化方案，让你不再因为学习这篇文章而被面试官批评！

价格：硬件优化>系统配置优化>表结构优化>SQL命令优化>索引优化。

效果：索引优化 > SQL语句优化 > 表结构优化 > 系统配置优化 > 硬件优化。

硬件优化

硬件优化无非是优化MySQL所在服务器的CPU、内存和磁盘。现代Web数据库需要大内存和高IO（百度服务器内存：96G - 128，2个实例，8到16个CPU）。不同版本的MySQL对多核CPU的支持不同。

服务器硬件对MySQL性能的影响及优化方案

1.CPU 对于MySQL应用，建议使用带有S.M.P的多通道对称CPU。建筑学。例如，您可以使用两个 Intel Xeon 3.6 GHz 处理器。现在我推荐专门使用4U服务器作为数据库服务器，而不仅仅是mysql。

2。物理内存 对于使用MySQL的数据库服务器，建议服务器内存不小于2GB。建议使用4GB以上物理内存。不过，可以说内存对于当今的服务器来说是一个可以忽略不计的问题。工作中遇到的高端服务器基本上都是16G以上内存。

3。磁盘寻道能力（磁盘输入/输出），以目前的高速SCSI硬盘（7200转）为例。这种硬盘理论上每秒可以寻道7200次，这是由物理特性决定的。没有办法改变它。 MySQL每秒都会执行大量复杂的查询操作，对磁盘的读写量可想而知。因此，磁盘I/O被广泛认为是MySQL性能的最大限制因素之一。对于日均访问量超过100万PV的系统，由于磁盘I/O限制，MySQL性能会非常差！为了解决这个限制，您可以考虑以下解决方案： 使用RAID-0+1磁盘阵列。请注意不要尝试使用 RAID-5。 MySQL 在 RAID-5 磁盘阵列上的效率不会如您所期望的那样。

注：在系统初始设计时应考虑上述计划。

优化系统配置

基本配置

当然，根据您的工作负载或硬件，还有其他设置可以发挥作用：慢速内存和快速磁盘、高并发和写入密集型工作负载，您可以需要进行特殊调整。但我们的目标是让您快速获得强大的 MySQL 配置，而无需花费太多时间调整一些非必要的 MySQL 设置或阅读文档来找出哪些设置对您很重要。

Linux 系统上的 MySQl 配置文件一般位于 /etc/my.cnf

innodb_buffer_pool_size

这是安装 InnoDB 后应设置的第一个选项。缓存区是缓存数据和索引的地方。默认大小为128M。越高越好，具体取决于 CPU 架构，这可确保您使用内存而不是硬盘来进行大多数读取操作。典型值为5-6GB（8GB内存）、20-25GB（32GB内存）、100-120GB（128GB内存）。

innodb_log_file_size

这是重做日志的大小。 Do MySQL 5.1 的日志记录很难调整，因为一方面您希望增加它以提高性能，另一方面您希望减少它以更快地从崩溃中恢复。

幸运的是，自 MySQL 5.5 以来，崩溃恢复性能有了显着提升，因此您可以同时拥有高写入性能和崩溃恢复。在 MySQL 5.5 之前，总重做日志大小限制为 4 GB（默认情况下可以有 2 个日志文件）。 MySQL 5.6 对此进行了改进。从一开始就将 innodb_log_file_size 设置为 512M（因此有 1GB 重做日志）将为您提供足够的空间用于写入操作。如果你知道你的应用程序需要频繁写入数据，并且你使用的是MySQL 5.6，那么你可以一开始就将其设置为4G。

max_connections

如果经常出现连接数太多的错误，那是因为 max_connections 的值太低了。这是很常见的，因为应用程序没有正确关闭数据库连接，并且您需要比默认的 151 个连接更高的值。 max_connection 设置为较高值（例如 1000 或更高）时的主要缺点是，当 1000 个或更多活动事务启动时，服务器将变得无响应。在应用程序中使用连接池或在 MySQL 中使用进程池可以帮助解决这个问题。

InnoDB 配置

从 MySQL 5.5 版本开始，InnoDB 是默认的存储引擎，并且使用量远远超过任何其他存储引擎。因此，需要仔细配置。

innodb_file_per_table

此设置告诉InnoDB是否需要将所有表的数据和索引存储在共享表空间中（innodb_file_per_table = OFF）或将每个表的数据放在单独的.ibd文件中（innodb_file_per_table =关闭）innodb_file_per_table = 开启）。每个表一个文件允许您在删除、截断或重建表时回收磁盘空间。一些高级功能（例如数据压缩）也需要它。但它并没有带来任何性能的提升。您不希望每个表一个文件的主要场景是如果您有大量表（例如 10,000+）。

在MySQL 5.6中，该属性的默认值为ON，因此大多数情况下您不需要执行任何操作。在以前的版本中，您必须在加载数据之前将此属性设置为ON，因为它只影响新创建的表。

innodb_flush_log_at_trx_commit

默认值为1，表示InnoDB完全支持ACID功能。当您主要关心的是数据安全性时（例如在主节点上），此值最合适。但对于磁盘速度（读取和写入）较慢的系统，这将带来巨大的开销，因为重做日志中的每次更改刷新都需要额外的 fsync。将其值设置为2会导致可靠性（reliable）降低，因为提交的事务每秒只保存到重做日志一次，但这对于某些场景是可以接受的，例如主节点的备份节点。这个值是可以接受的。 z 值为 0 速度较快，但在系统故障时可能会导致数据丢失：仅适用于备份节点。

innodb_flush_method

此配置决定数据和日志如何写入硬盘。一般来说，如果您有硬件RAID控制器且其独立缓存采用回写机制并具有电池断电保护，则应设置为O_DIRECT；否则在大多数情况下应将其设置为 fdatasync （默认值）。 sysbench 是一个很好的工具，可以帮助您决定此选项。

innodb_log_buffer_size

此配置确定为尚未提交的事务分配的缓冲区。默认值 (1 MB) 通常就足够了，但如果您的事务包含大型二进制对象或大型文本字段，则此缓冲区将快速填满并触发额外的 I/O 操作。查看状态变量Innodb_log_waits，如果不为0，则增加innodb_log_buffer_size。

其他设置

query_cache_size

查询缓存是一个已知的瓶颈，即使没有太多并发性。最好的选择是从一开始就禁用它，设置 query_cache_size = 0（现在是 MySQL 5.6 中的默认值）并使用其他方法来加速查询：优化索引、添加副本以分散负载或启用额外的缓存（例如 memcache 或 redis）。如果您已为应用程序启用查询缓存并且没有遇到任何问题，您可能会发现查询缓存很有用。如果你想禁用它，你必须小心这一点。

log_bin

如果您希望数据库服务器作为主节点的备份节点，则需要启用二进制协议。如果这样做，请务必将 server_id 设置为唯一值。即使使用单个服务器，如果您想在某个时间点执行数据恢复，此功能（打开二进制日志记录）也很有用：从最新备份（完整备份）恢复并将更改应用到二进制日志（增量备份）。创建后，二进制日志将永久保存。因此，如果您不想耗尽磁盘空间，可以使用 PURGE BINARY LOGS 删除旧文件或设置 expire_logs_days 指定自动删除日志的天数。

二进制日志记录并非没有开销，因此如果您在非主复制节点上不需要它，建议禁用此选项。

skip_name_resolve

当客户端连接数据库服务器时，服务器会进行主机名解析，当 DNS 较慢时，连接建立也会很慢。因此，建议在启动服务器而不执行 DNS 查找时禁用skip_name_resolve 选项。唯一的限制是稍后在 GRANT 命令中只能使用 IP 地址，因此在将此设置添加到现有系统时需要小心。

表结构的优化

由于MySQL数据库是基于行（Row）存储的数据库，数据库是以IO页（块）的方式工作的，也就是说，如果我们每条记录占用的空间如果数据量减少，每页可以存储的数据行数就会增加，每个IO可以访问的行数也会增加。另一方面，处理相同行数的数据需要访问更少的页面，这意味着IO操作次数减少，从而直接提高性能。

而且，由于我们的内存是有限的，增加每页存储的数据行数就相当于增加了每个内存块中缓存存储的数据量。它还增加了交换内存时数据命中的概率，即缓存命中的频率。

数据类型的选择

数据库操作中最耗时的就是IO处理。大多数数据库操作90%以上的时间都花在读写IO上。因此，最大限度地减少IO读写次数可以极大地提高数据库操作的性能。

我们无法改变数据库中需要存储的数据，但是我们可以思考数据是如何存储的。以下针对数组类型的优化建议主要适用于高记录、高数据量的场景，过于精细的数据类型设置会增加维护成本，过度优化会带来其他问题。 。

数字类型

除非绝对必要，否则不要使用 double。这不仅是存储长度的问题，也是准确性的问题。

同样，固定精度小数也不建议使用decimal。建议将它们乘以固定倍数，转为整数存储，这样可以大大节省存储空间，并且不会产生任何额外的维护成本。

对于数据量较大时存储整数，建议区分tinyint / int / bigint选项，因为这三者占用的存储空间也有很大不同，可以肯定负数字段会不被使用。 ，建议添加无符号定义。当然，如果数据库数据量较小，则没有必要严格区分这三种整数类型。

字符类型

除非绝对必要，否则不要使用文本数据类型。它的处理方式决定了它的性能低于处理 char 或 varchar 类型时。

对于固定长度的字段，建议使用char类型。对于变长数组，尝试使用varchar并且只设置一个合适的最大长度，而不是任意给出很大的最大长度限制，因为不同的长度范围，MySQL会有不同的存储处理。

（注：char(n)无论数组是否存储数据，都占用n个字符的存储空间；varchar不存储时不占用空间，占用的空间与存储数据的长度一样多，可以节省存储空间。）

TimeType

尝试使用timestamp类型，因为它只需要datetime类型一半的存储空间。然而，时间戳中存储的数据仅限于1970-2038年期间。

对于只需要精确到某个日期的数据类型，建议使用日期类型，因为它的存储空间只有3个字节，小于时间戳。

enum 和 set

对于状态数组，可以尝试使用enum来存储，因为它可以大大减少存储空间，而且即使需要添加新类型，也只需添加到最后即可修改结构而不重建表数据。 。

如果您存储预定义的属性数据怎么办？您可以尝试使用集合类型。即使有多个属性，您仍然可以轻松操作它并节省大量存储空间。

字符编码选择

字符集直接决定了MySQL中数据存储的编码方式。由于相同的内容使用不同的字符集所占用的空间差异很大，因此使用合适的字符集可以帮助我们尽可能的减少数据量，从而减少IO操作的次数。

1。对于可以用纯拉丁字符表示的内容，不需要选择latin1以外的字符编码，因为这样会节省大量的存储空间。

2。如果我们能够确定不需要存储多种语言，就没有必要使用utf8或者其他Unicode字符编码，这样会造成大量的存储浪费。

数据库表的适当分配

有时我们可能希望将整个对象分配给数据库表。这对于应用程序开发非常有好处，但有时会影响性能。带来更大的问题。

当我们的表中有一个很大的类似文本的字段或者很大的varchar类型时，如果我们在访问这个表的时候大部分时间都不需要这个字段，那么我们应该毫不犹豫的将其拆分。放到另一个独立的表中，以减少常用数据占用的存储空间。这样做的明显好处是每个数据块中可以存储的数据项数量可以大大增加，不仅减少了物理I/O的数量，而且还大大提高了内存中的缓存命中率。

表数据有适度冗余

为什么需要冗余？这样岂不是增加了每条数据的大小，减少了每个数据块中可以存储的记录数量？这实际上会增加每条数据的大小。记录大小减少了每条记录可以存储的数据量，但在某些场景下我们仍然需要这样做。

1。经常被引用的独立小字段，只能通过连接2个（或更多）大表来获得。

2。在这种场景下，由于每次连接只是检索某个小字段的值，且该连接检索的记录较大，因此会造成大量不必要的IO，可以通过用空间换取时间来优化。尽管冗余，但要保证数据的一致性不被破坏，同时保证冗余字段也被更新。

default 尝试使用not null（尝试将默认设置为非空）

null类型比较特殊，SQL很难优化。虽然MySQL的null类型和Oracle的null类型不同进入索引，但是如果是复合索引的话，那么这个NULL字段会极大的影响整个索引的效率。另外，索引中处理NULL也比较特殊，也会占用额外的存储空间。

很多人认为null会节省一些空间，所以尝试用null来达到节省IO的目的，但大多会适得其反。虽然确实可能节省一些空间，但它会引入许多额外的优化问题。不但没有节省IO量，反而增加了SQL IO量。所以尽量保证默认值不为null，这也是优化表结构设计的一个好习惯。

为每个表设置ID

对于数据库中的每个表，我们应该将ID设置为它的主键，最好的类型是INT（推荐UNSIGNED），并设置为自动增量AUTO_INCREMENT（自增）旗帜。

即使您的用户表包含名为“电子邮件”的主键字段，也不要将其设为主键。使用 VARCHAR 类型作为主键会降低性能。此外，您应该在程序中使用表 ID 来构建数据结构。

另外，MySQL数据核心下有一些操作需要用到主键。在这些情况下，簇、分区等主键的性能和设置就非常重要了……

这里只是一种情况，例外的是“关联表”的“外键”，它是这个表的主键表由多个单独表的主键组成。我们称这种情况为“外键”。例如：有一个包含学生 ID 的“学生表”和一个包含课程 ID 的“课程表”。那么“成绩表”就是一个将学生表和课程表连接起来的“关联表”。在成绩表中，学生ID和课程ID称为“外键”，共同构成主键。

SQL 命令优化

1.为了优化你的查询，你应该尽量避免全表扫描。首先，您应该考虑在与位置和顺序相关的列上创建索引。

2。尽量避免在where子句中使用!=或运算符，否则引擎将放弃使用索引并执行全表扫描。

3。尽量避免考虑where子句中字段的空值，否则引擎将放弃使用索引并执行全表扫描。

例如：

select id from t where num is null

可以将num设置为默认值0，并确保表中num列没有空值，然后像这样查询：

select id from t where num=0

4。尽量避免在where子句中使用or附加条件，否则会导致引擎停止使用索引并执行全表扫描。

例如：

select id from t where num=10 or num=20

可按如下方式查询：

select id from t where num=10
union all
select id from t where num=20

5。以下查询也会导致全表扫描。

例如：

select id from t where name like '%abc%'

如果想提高效率，可以考虑全文检索。

6.in 和 not in 也要谨慎使用，否则会导致全表扫描。

例如：

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

对于连续值，如果可以使用 Between，则不要使用 v

select id from t where num between 1 and 3

7。如果where子句中使用了参数，也会导致全表扫描。

因为SQL只在运行时解析局部变量，但优化器不能将访问计划的选择推迟到运行时，所以必须在编译时选择。但是，如果在编译时创建访问计划，则变量的值仍然未知，并且不能用作索引选择的输入。

以下命令执行全表扫描：

select id from t where num=@num

您可以更改它以强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8。尽量避免对where子句中的字段进行表达式操作，这会导致引擎放弃使用索引。并进行全表扫描。

例如：

select id from t where num/2=100

应更改为：

select id from t where num=100*2

9。尽量避免对where子句中的字段执行函数操作，这将导致引擎放弃使用索引并执行全表扫描。

例如：

select id from t where substring(name,1,3)='abc' --name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0 --'2005-11-30'生成的id

应更改为：

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10。不要在where子句中“=”左侧进行函数、算术运算或其他表达式运算，否则系统可能无法正确使用索引。

11。使用索引字段作为条件时，如果索引是复合索引，则必须使用索引中的第一个字段作为条件，以保证系统使用该索引，否则不会使用该索引。并且字段的顺序应尽可能与索引的顺序一致。

12。不要写无意义的问题。

如果需要生成空表结构：

select col1,col2 into #t from t where 1=0

此类代码不会返回任何结果集，但会消耗系统资源。它应该更改为：

create table #t(...)

13。在许多情况下，使用exists 代替in 会更好。与选择。

select num from a where num in(select num from b)

替换为以下语句：

select num from a where exists(select 1 from b where num=a.num)

14。并非所有索引都对查询有效。 SQL根据表中的数据进行查询优化。如果索引列中存在大量重复数据，则 SQL 查询可能会失败。使用索引，比如一个表中有一个性别字段，几乎一半是男性，一半是女性，那么即使是基于性别的索引，也不会对查询性能产生影响。

15。索引越多越好。索引虽然可以提高匹配选择的效率，但是也降低了插入和更新的效率，因为索引在插入或更新的过程中可能会被重建，所以如何建立索引需要仔细考虑，具体情况具体分析。案例依据。表上的索引最好不要超过6个。如果太多，就应该考虑在一些不常用的列上创建索引。

16。应尽可能避免更新聚集索引的数据列，因为聚集索引数据列的顺序就是表记录物理存储的顺序。一旦列值发生变化，就会导致整个表的记录重新排序，这会花费很多钱。 H、如果应用系统需要频繁更新聚集索引的数据列，则必须考虑是否将该索引构建为聚集索引。

17。尝试使用数字字段。对于仅包含数字信息的字段，尽量不要将其设计为字符字段。这将降低查询和连接性能并增加存储开销。这是因为引擎在处理查询和连接时会逐一比较字符串中的每个字符，而对于数字类型只需比较一次就足够了。 ？在相对较小的领域进行搜索当然效率更高。

19。不要在任何地方使用 select * from t，将 * 替换为特定的字段列表，并且不返回任何未使用的字段。

20。尝试使用表变量而不是临时表。如果表变量包含大量数据，请注意索引非常有限（仅限主键索引）。

21。避免频繁创建和删除临时表，减少系统表资源消耗。

22。临时表并非无用，适当使用它们可以使某些例程更加高效，例如当您需要重复引用大型表或常用表中的特定数据集时。但是，对于一次性事件，最好使用导出表。

23。创建临时表时，如果一次性插入大量数据，可以使用select来代替创建表，避免大量日志，提高速度。如果数据量不大，应该先建表再插入，以缓解系统表资源。

24。如果使用临时表，则必须在存储过程结束时显式删除所有临时表，首先截断表，然后删除表。因此，您可以避免系统表的长期锁定。

25。尽量避免使用游标，因为游标效率较低。如果游标控制的数据超过10000行，就应该考虑覆盖。

26。在使用基于游标的方法或临时表方法之前，应该首先寻找基于集合的解决方案来解决问题。基于集合的方法通常更有效。

27。与临时表一样，游标也不是不可用。对小数据集使用 FAST_FORWARD 游标通常比其他逐行处理方法更好，特别是当必须引用多个表来检索所需数据时。在结果集中包含“总计”的例程通常比使用游标更快。如果开发时间允许，您可以尝试基于游标的方法和基于集合的方法，看看哪种方法效果更好。

28。在所有存储过程和触发器的开始处设置 SET NOCOUNT ON，并在结束处设置 SET NOCOUNT OFF。每次转储存储过程和触发器后，无需向客户端发送 DONE_IN_PROC 消息。

29。尽量避免向客户端返回大量数据。如果数据量太大，就应该考虑相应的要求是否合理。

30。尽量避免大事务，提高系统并发性。 来源：chonglian.blog.csdn.net/article/details/123049998