mysql是在数据库层面进行优化的，详细讲解mysql数据库性能优化

本文内容列表：

• 1、mysql优化包括哪些内容？
• 2、如何优化mysql数据库？谁能提供具体的解决方案？
• 3.超详细的MySQL数据库优化
• 4、通过分区表和数据库优化MySQL数据库性能

mysql优化包括哪些内容？

MySQL 优化主要有两个方面：

1.配置优化

配置优化其实包括两个方面：操作系统内核优化和MySQL配置文件优化

1）系统内核对于特殊的mysql服务器来说，优化无非是内存使用优化、连接数、超时处理、TCP处理、等等..根据硬件配置本身进行优化。这里我就不详细说了；

2）mysql配置通用优化包括：IO处理通用参数、最大连接数设置、缓存使用参数设置、慢日志参数设置、innodb相关参数设置等。如果存在主从关系，则将主从同步相关的参数设置得足够好。网上有很多相关的配置文件，大同小异。大部分常用的设置都修改了，已经差不多够用了。 ？这是非常资源密集型的。

2）使用最小连接

MySQL的优点是简单，但这实际上是它在某些方面的缺点。 MySQL优化器的效率很高，但是由于统计信息量有限，优化器的工作过程出现偏差的机会较多。对于复杂的多表Join，一方面因为优化器有限，另一方面在Join方面下的功夫还不够，所以性能还是远远落后于之前的关系型数据库如Oracle。但如果单表查询简单的话，差距会很小，在某些场景下甚至会比这个数据库之前更好

3）尽可能排序

排序操作会占用更多的CPU资源，所以减少在缓存命中率较高且IO能力充足的场景下，排序会极大地影响SQL的响应时间。

对于MySQL来说，减少排序的方法有很多，比如：

通过索引排序进行优化

减少排序涉及的记录数

除非必要，不要对数据进行排序

) 尽量不要选择*

当数据量较小，访问次数不大时，select * 没有作用。但当量级达到一定程度时，仍然与执行效率和IO资源的使用有关。应该用什么字段来获取字段，减少不必要的资源。

5）尝试使用join代替子查询

虽然Join的性能不好，但相比MySQL的子查询还是有巨大的性能优势。 MySQL的子查询执行计划总是存在很大的问题。虽然这个问题已经存在很多年了，但在迄今为止发布的所有稳定版本中都很常见，并且没有太多改进。虽然官方很早就承认了这个问题，并承诺会尽快解决，但至少到现在为止我们还没有看到更好地解决这个问题的版本。

如何优化mysql数据库？谁能提供具体的解决方案？

1。解释：解释sql的执行计划，下面的sql不会被执行

2。解释分区：用于查看分区

3的表执行计划。解释补充：验证

4，显示危险：

5。 show建表：查看建表语句详细信息，方便用户优化建表

6. show index：查看Table的所有索引，显示table_name的索引，它们可以从information_schema.statistics表中获取相同的信息。基数列很重要，代表数据量。

7。 show table status：查看数据库表的底层大小和表结构。您还可以从 information_schema.tables 表中获取基础表信息。

8。 show status [global|session]：可以查看mysql服务器当前的内部状态信息。各种指标可以帮助判断mysql服务器的负载情况。默认是会话。类似于 information_schema.global_status 和 information_schema.session_status

9.showvariables[global|session]：查看mysql系统变量的当前值，其中一些变量可能会影响sql语句的执行。与information_schema.global_variables和information_schema.session_variables类似；

10。 information_schema：包含的表数量与mysql版本有关。

超详细的MySQL数据库优化

一方面，数据库优化找出系统瓶颈，提高MySQL数据库的整体性能。另一方面，需要合理的结构设计和参数调整，提高用户响应速度，同时也尽可能节省系统资源。 ，让系统提供更大的负载。

1. 优化概述

2. 优化

作者将优化分为软优化和硬优化两类。软优化一般涉及数据库操作。 ，并对操作服务器硬件和参数设置进行硬优化。

2.1 软优化

2.1.1 查询语句优化

1、首先我们可以使用 EXPLAIN 或 DESCRIBE 命令（简称：DESC）来分析查询语句执行信息。

2. 示例：

显示：

显示查询数据的索引、读取次数等信息。

2.1.2 优化子查询

在MySQL中，尽量使用JOIN代替子查询。由于子查询需要嵌套查询，因此查询嵌套时会创建临时表。创建和删除临时表都会有较大的系统开销，而连接查询不会创建临时表。因此，效率比嵌套子查询要高。

2.1.3 使用索引

索引是提高数据库查询速度的最重要方法之一。对于索引，可以参考作者的MySQL数据库索引文章，有更详细的介绍，这里记录一下使用索引的三个主要注意事项：

2.1.4 分解表

对于列很多的表，如果有几个列。使用频率较低，应将它们分开形成一个新表。

2.1.5 中间表

对于需要大量连接的表，可以创建中间表，以减少查询带来的连接耗时。

2.1.6 添加冗余列

与创建中间表类似，添加冗余以减少连接查询。

2.1.7 分析表、检查表、优化表

分析表主要分析表中关键词的分布情况，检查表主要检查是否有错误。优化表主要是为了消除删除或更新造成的表空间浪费。

1.分析表：使用ANALYZE关键字，如ANALYZE TABLE user;

2.检查表：使用CHECK关键字，如CHECK TABLE user [option]

仅适用于MyISAM，共有五个参数值：

3、优化表：使用关键字OPTIMIZE，如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] user TABLE；

LOCAL|NO_WRITE_TO_BINLOG 表示不写日志。优化表仅适用于 VARCHAR、BLOB 和 TEXT。根据OPTIMIZE TABLE语句可以消除文件碎片并在执行过程中添加只读键。

2.2 硬件优化

2.2.1 硬件三件套

1. 多核配置和高频CPU，其他核心可以运行多个线程。

2、配置大内存，增加内存，增加缓存容量，从而减少磁盘I/O时间，提高响应速度。

3、配置高速磁盘或足够的分布式磁盘：高速磁盘增加I/O，分布式磁盘可以增加并行运行的能力。

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率，从而提高MySQL服务器性能。 MySQL服务配置所有参数都在my.cnf或my.ini中。下面列出了对性能影响较大的参数。

2.2.3 分库和表

因为数据库压力很大，所以第一个问题就是高峰期系统性能可能会比较低，因为数据库负载高对性能有影响。还有一点，如果你的数据库因为压力过大而崩溃了怎么办？那么这个时候就需要将系统进行数据库和表+读写分区的划分，即将数据库划分为多个数据库，分布到多个数据库服务中，然后成为主数据库来处理写入请求。然后每个主库挂载至少一个从库，由从库处理读请求。

2.2.4 缓存集群

如果用户数量增加，可以继续添加机器。例如，如果在系统级别不断添加引擎，则可以处理更高的并发请求。那么如果数据库层面的写并发越来越高，就要扩容数据库服务器，通过分库分表的方式扩容机器。如果数据库层面的读并发越来越高，就会扩容，增加更多的从库。但这里有一个很大的问题：数据库本身并不是真正用来做高并发请求的，所以一般来说，数据库引擎每秒带来的并发量是几千个量级，而那个数据库所使用的引擎。配置比较高，机器比较贵，性价比非常高。如果只是不断地添加机器，那真的是错误的。因此，高并发架构中通常会包含缓存。缓存系统旨在执行高并发。因此，一台机器承载的并发量为每秒数万，甚至数十万，承载高并发的能力比数据库系统多一到两个数量级。因此，对于写少读多的请求，完全可以根据系统的业务特点引入缓存集群。具体来说，在写入数据库时​​，同时将数据的副本写入缓存集群，然后使用缓存集群来执行大部分读取请求。这种情况下，通过缓存集群，可以用更少的机器资源承载更多的并发。

一个完整、复杂的高并发系统架构总会包括：各种复杂的自建基础设施系统。各种美丽的建筑设计。因此，大多数小文章能够对产生想法产生影响，但仅限于数据库优化想法。

MySQL数据库性能优化分为不同的表和数据库

分表是分散数据库压力的好方法。

表分区最简单的含义就是将表结构划分为若干个表，然后可以将这些表放在同一个库中，也可以放在不同的库中。

当然，你首先要知道什么情况下需要分表。个人认为，如果单表的记录数是几百万到几千万，就有必要使用分表了。

子表分类

**1.垂直分表**

将同一个表中可以存在的内容人为地划分为多个表。 （所谓原始是指按照关系型数据库第三范式的要求，必须在同一个表中。）

分表的原因：按照数据活跃度来进行分割（因为活跃数据不同，加工方法也不同）

案例：

对于博客系统来说，文章标题、作者、类别、创建时间等变化频率慢，查询多，最好有良好的实时数据。我们称之为冷数据。对于博客浏览量、回复数、类似统计信息或其他变化频率较高的数据，称为活跃数据。因此，在设计数据库结构时，必须考虑表分区。首先是垂直工作台分离过程。

将桌子垂直划分成这样：

首先，存储机的使用方式不同。 MyIsam可以用于冷数据，以便更好地查询数据。对于活跃的数据，可以使用Innodb，它可以有更好的更新速度。

其次，再设置一个从库用于冷数据，因为查询的操作较多，从而加快查询速度。对于热点数据，主库分表的横向处理可能相对较多。

其实对于一些特殊的活跃数据，也可以考虑使用memcache、redis等缓存，当量达到一定量时更新数据库。或者 nosql 数据库，例如 mongodb。这只是一个例子，我先不讲了。

**2。水平分表**

从字面上看，就是将一个大表结构水平切分成相同结构的不同表，比如用户信息表、user_1、user_2等。类似，但是表是根据一些特定的规则来划分的，比如根据用户ID划分模块。

分表的原因：按照数据量的大小来划分，保证一张表的容量不会太大，从而保证一张表的查询等处理能力。

案例：同上例，博客系统。当博客量达到较大水平时，应采用水平切分，以减轻各个表的压力，提高性能。例如，如果博客的冷数据表被分成100张表，那么当100万用户同时浏览时，如果一张表，就会发出100万次请求。但是现在分表之后，每个表都是可以的。发出10000个数据请求（由于我们无法得到绝对平均值，所以这只是一个假设），那么压力就会大大降低。

延伸：为什么要拆分表和分区？

在我们日常开发中，经常会遇到大表。所谓大表是指存储了数百万甚至数千万条记录的表。表太大，导致数据库查询和插入时间较长，导致性能不佳。如果有并发查询，性能会更差。表分区和表分区的目的是为了减少数据库负载，提高数据库效率。总的来说就是为了提高增删改查表的效率。

什么是子表？

表分区就是将一张大表按照一定的规则分解为多个具有独立存储空间的实体表。我们可以称之为子表。每个Table对应三个文件，MYD数据文件，MYI索引文件，.frm表结构文件。这些子表可以分布在同一个磁盘上，也可以分布在不同的机器上。应用程序读写时，会根据预定义的规则找到匹配的子表名，然后进行操作。

什么是分区？

分区与表分区类似。两者都根据规则分解表。不同的是，表拆分将大表分解为其实体的若干个表，而分区则将数据分为多个部分并将它们存储在多个位置，这些位置可以在同一磁盘上，也可以在不同的机器上。分区后，表面上仍然是一张表，但数据转移到了多个位置。 app读写时，仍然以大表的名义进行操作，db自动管理分区数据。

**MySQL表和分区之间有什么关系？ **

1.两者都能提高mysql的性能，在高并发情况下有很好的性能。

2、表分离和表分离并不矛盾，可以一起发挥作用。对于那些访问量大、表数据量大的表，我们可以将表分区和分区结合起来（如果采用组合分区表方式，如果不能配合分区，可以尝试使用其他子表）。对于访问量较小但表数据较多的表，我们可以使用分区。

3.分表技术比较麻烦。您必须手动创建子表。应用服务器在读写时必须计算子表的名称。最好使用联接，但还需要创建子表并管理子表之间的并集关系。

4、与分表相比，分表操作方便，不需要创建分表。

我们知道，对于大规模的互联网应用，单个数据库表的数据量可以达到千万甚至上亿，同时面临着很高的并发压力。 Master-Slave结构只能扩展数据库的读能力，写操作仍然集中在Master上。 Master不能无限制地连接Slave库。如果需要进一步扩大数据库的吞吐能力，可以考虑使用分库。表拆分策略。

**1。表分区**

在对表进行分区之前，需要选择合适的表分区策略（使用哪个字典作为表分区列、将数据分区到多少个表中），以便对数据进行分区。余额分布在多个表中，不影响正常查询。在企业级应用中，org_id（组织的主键）经常被用作子表列，而在互联网应用中它往往是userid。定义了分表策略后，在存储和查询数据时，需要定义从哪张表中查找数据。

存储数据的数据表 = 分片表列内容 % 分片表数量

**2 ，子库**

分表可以解决单表数据量过大导致查询效率降低的问题，但无法给数据库访问带来质的提升。面对高并发的写访问，Master无法承受高并发的写请求。此时，无论你如何扩展Slave服务器，都是没有意义的。我们通过划分数据库（称为子数据库）来提高数据库的可写性。分支数据库采用关键字取模的方法来路由数据库。

存储的数据库 = 子数据库列的内容 % 数据库数量

**3.即分表分库**

数据库分表可以解决单表海量数据查询性能问题。库可以解决单个数据库的并发访问压力问题。

当数据库同时面临海量数据存储和高并发访问时，需要同时使用表策略和数据库策略。一般表库分离策略如下：

中间变量 = 关键字 %（数据库数量 * 单库数据表数量）

数据库 = 舍入（中间变量 / 单库数据表数量）数据库 )

表 = (中间变量 % 一个数据库的数据表数量)

示例：

1. 分库和分表

显然，主表的无限增长（即就是，表非常重要，比如用户表）会严重影响性能，分库加表是很好的解决方案，都是性能优化。在我们当前的案例中，我们有一个包含超过 1000 万条记录的用户表成员。提问速度非常慢。我同事的做法是，先将其散列成100张表，从member0到member99，然后将记录按照mid分布到这张表中。一个很酷的代码可能是这样的：

复制代码，代码如下：

?php

for ($i=0;$i 100; $i++ ){

// echo "创建表 db2.members{$i} LIKE db1.members

";

echo "插入成员{$ i} SELECT * FROM 成员 WHERE mid%100={$i}

";

}

?

2.不停地修改mysql表结构

同样是成员表，前期设计的表结构不够。随着数据库的不断运行，冗余数据量也会不断增加。我同事用了如下方法解决：

首先创建一个临时表：

/*创建临时表*/

创建一个member_tmp表LIKE member

然后将member_tmp表的结构更改为新的结构，然后使用上面的循环导出数据，因为一次性导出1000万条数据是错误的。 Mid 是主键，导数是一个区间一个区间。实际上，一次出口了 50,000 件商品。嗯，这里省略了

。然后重命名，替换成新表：

/*这句相当经典的语句*/

Renamemembers TABLE TOmembers_bak,members_tmp TOmembers;

这样就可以了，基就不会了失去。达到了，并且不需要停下来更新表结构。然而，在 RENAME 过程中，表实际上是被锁定的，所以当有多个在线操作时，选择一个操作是一个技巧。这样操作之后，原来8G多的表突然变成了2G多。