修改字段默认值sql_信不信这5大MySQL优化指南，你一定用得上！

如果面试官问你：你会从哪些维度来优化MySQL性能？你会如何回答？

所谓性能优化一般都是针对MySQL查询的优化。既然我们是在优化查询，那么我们自然要先知道查询操作经过了哪些环节，然后再思考哪些环节可以优化。

我之前写过，有兴趣的朋友可以看一下。我使用其中一张图片来展示查询操作需要经历的基本步骤。

SQL查询链接

下面从5个角度介绍一些MySQL优化的策略。

1、连接配置优化

处理连接是MySQL客户端和MySQL服务器建立关系的第一步。第一步迈不好，就别谈后面的故事了。

既然连接是双方的事，我们自然会从服务器端和客户端两方面进行优化。

1.服务器配置

服务器需要做的就是接受尽可能多的客户端连接。也许您遇到过错误1040：错误太多？就是服务器心胸不够宽广，布局太小了！

我们可以从两个方面解决连接不足的问题：

mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.01 sec)

mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout  | 28800 |+---------------+-------+1 row in set (0.01 sec)

MySQL有很多配置参数，并且大多数参数都提供默认值。默认值是MySQL作者精心设计的，完全可以满足大多数情况的需要。在不了解参数含义的情况下不建议贸然修改。

2、客户端优化

客户端能做的就是尽量减少与服务器建立连接的次数。如果可以使用已建立的连接，则使用它们。不要每次执行 SQL 语句时都创建一个新连接。服务器和客户端的资源都将不堪重负。

解决方案是使用连接池来重用连接。

常见的数据库连接池有DBCP、C3P0、阿里巴巴的Druid等。前两种很少使用，后两种目前正在热火朝天地进行。

但需要注意的是，连接池越大越好。例如Druid默认最大连接池大小为8，默认最大连接池大小为10，盲目增大连接池大小可能会降低系统执行效率。为什么？

对于每个连接，服务器都会创建一个单独的线程来处理它。连接越多，服务器创建的线程就越多。当线程数量超过CPU数量时，CPU必须分配时间片来进行线程的上下文切换。频繁的上下文切换会造成很大的性能开销。

官方给出了数据库连接池大小的推荐值公式，CPU核心数*2+1。假设服务器CPU核心数为4，只需将连接池设置为9即可。这个公式为一定程度上也适用于其他数据库，面试时可以吹嘘一下。

2、架构优化

1.使用缓存

系统中难免会出现一些慢查询的情况。这些查询要么数据量大，要么是复杂查询（很多关联表或者复杂计算），导致查询长时间占用连接。

如果这类数据的有效性不是特别强（不是每时每刻都在变化，比如日报），我们可以将这类数据放入缓存系统中，在缓存有效期内直接从缓存系统中获取的数据。数据，从而减轻数据库的压力，提高查询效率。

缓存使用情况

2.读写分离（集群、主从复制）

在项目的早期阶段，数据库通常运行在一台服务器上。用户的所有读写请求都会直接影响到这台数据库服务器。毕竟单台服务器所能承受的并发量是有限的。

为了解决这个问题，我们可以同时使用多台数据库服务器，将其中一台作为组长，称为节点，其他节点作为组员，称为从属。用户只向本节点写入数据，读请求则分发到各个从节点。这种解决方案称为读写分离。给组长和组员组成的小组起个名字，称之为集群。

这是集群

许多开发商对“奴隶”这个冒犯性的词感到不满（因为他们认为这会与种族歧视、黑人奴隶等联系在一起），因此发起了更名运动。

受此影响，MySQL也将逐渐停止使用slave、slave等术语，代之以and。遇到了就明白了。

使用集群时必须面对的一个问题是如何保持多个节点之间的数据一致性。毕竟写请求只是发送到节点，只有节点的数据才是最新的数据。如何将节点上的写操作同步到各个从节点上？

主从复制技术来了！

是实现MySQL主从复制功能的核心组件。该节点会记录所有对从节点的写操作。从节点将有一个专用的I/O线程来读取节点并将写操作同步到当前从节点。

主从复制

这种集群架构对于减轻主数据库服务器的压力有非常好的效果。但是，随着业务数据越来越多，如果某个表的数据量急剧增加，那么单表的查询性能就会明显下降，而这个问题无法通过读写分离来解决。毕竟，所有节点都存储完全相同的数据。单表查询性能较差。当然，所有节点的性能也很差。

这时我们可以将单个节点的数据分散到多个节点上进行存储，也就是分库分表。

3、分库、分表

分库分表中的节点含义比较广泛。如果数据库作为节点，则为子数据库；如果单表作为节点，则为子表。

大家都知道分库分表分为垂直分库、垂直分表、水平分库、水平分表，但是每次记不住这些概念的时候，我会详细解释一下，帮助大家理解。

1）垂直分库

垂直分库

我们在单一数据库的基础上，进行了多次垂直切割，按照业务逻辑拆分成不同的数据库。这是垂直分库。

垂直分库

2）立式分工作台

立式工作台

垂直分表就是对单个表进行垂直切割（或多次切割），将一个表的多个字分割成多个小表。这个操作需要根据具体业务来判断。通常，将经常使用的字段（热字段）分为一张表，将不经常使用或不立即使用的字段（冷字段）分为一张表，以提高查询速度。

立式工作台

以上图为例：通常商品详情比较长，在查看商品列表时，往往不需要立即显示商品详情（一般点击详情按钮后就会显示），但是将显示产品的更多重要信息。 （价格等）被显示。根据这个业务逻辑，我们将原来的产品表划分为垂直的子表。

3) 卧式工作台

将单个表的数据按照一定的规则（行话称为分片规则）保存到多个数据表中，并对数据表进行水平（或多次）切割，实现表的水平切分。

等级评分表

等级评分表

4）水平分库

水平分片就是对单个数据库进行水平切分，往往伴随着水平分片。

水平分库

水平分库

5）总结

水平划分主要是为了解决存储瓶颈；垂直划分主要是为了减轻并发压力。

4.消息队列调峰

通常，用户请求会直接访问数据库。如果同时在线的用户数量非常多，很有可能压垮数据库（参考明星出轨或者公布恋情时微博的状态）。

这种情况下，可以通过使用消息队列来减轻数据库的压力。无论同时有多少个用户请求，都会先存储到消息队列中，然后系统从消息队列中有序地消费请求。

队列调峰

3.优化器——SQL分析与优化

在处理完连接、优化缓存和其他架构事务之后，SQL 查询语句就进入了解析器和优化器的领域。如果这一步有问题，那只能是SQL语句的问题。

只要你的语法没有问题，解析器就不会有问题。另外，为了防止你写的SQL运行效率低下，优化器会自动做一些优化，但如果真的很糟糕，优化器也救不了你，只能眼睁睁地看着你的SQL查询变成慢查询。

1、查询慢

慢查询是执行非常慢的查询（这句话听起来像是废话……）。只有了解MySQL中存在哪些慢查询，我们才能进行有针对性的优化。

因为开启慢查询日志有性能成本，所以MySQL默认关闭慢查询日志功能。使用以下命令查看当前慢查询状态。

mysql> show variables like 'slow_query%';+---------------------+--------------------------------------+| Variable_name       | Value                                |+---------------------+--------------------------------------+| slow_query_log      | OFF                                  || slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |+---------------------+--------------------------------------+2 rows in set (0.00 sec)

指示当前是否启用慢查询日志，并指示慢查询日志的存储位置。

除了上述两个变量之外，我们还需要确定什么是“慢”指标，即执行一个查询需要多长时间才算慢。默认为 10S。如果改为0，则记录所有SQL。

mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

1）打开慢日志

打开慢日志有两种方法。

此修改方法在系统重启后仍然有效。

# 是否开启慢查询日志slow_query_log=ON# long_query_time=2slow_query_log_file=/var/lib/mysql/slow.log

mysql> set @@global.slow_query_log=1;Query OK, 0 rows affected (0.06 sec)
mysql> set @@global.long_query_time=2;Query OK, 0 rows affected (0.00 sec)

2）日志分析速度慢

MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询工具。为了演示这个工具，我们首先构造一个慢查询：

mysql> SELECT sleep(5);

然后我们查询耗时最多的慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log
Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.logCount: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  SELECT sleep(N)

在，

更多使用方法可以查看官方文档，或者执行--help获取帮助。

2.查看运行线程

我们可以运行show full来查看MySQL中运行的所有线程，检查它们的状态和运行时间，并杀死那些不顺眼的线程。

在，

3.检查服务器运行状态

使用SHOW查看MySQL服务器的运行状态。有两个范围：and和，一般使用like+通配符进行过滤。

-- 查看select的次数mysql> SHOW GLOBAL STATUS LIKE 'com_select';+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_select    | 168241 |+---------------+--------+1 row in set (0.05 sec)

4.查看存储引擎运行信息

SHOW用于显示存储引擎当前的运行信息，包括事务持有的表锁和行锁信息；事务锁等待状态；线程信号量等待；文件IO请求；池统计数据和其他数据。

例如：

SHOW ENGINE INNODB STATUS;

上述语句可以显示存储引擎当前运行的各种信息。您可以通过它来查找MySQL当前存在的问题。由于篇幅限制，这里我就不解释信息的含义了。你只需要知道MySQL提供了这样的监控工具即可。只需等到需要时再使用即可。

5、执行计划

通过慢查询日志，我们可以知道哪些SQL语句执行慢，但是为什么慢呢？慢在哪里？

MySQL提供了执行计划查询命令。通过这个命令，我们可以查看SQL的执行计划。所谓的执行计划就是：优化器会优化我们写的SQL语句吗（比如把外连接改为内连接查询，子查询优化为连接查询……），优化器估计执行这条SQL需要哪些索引的成本，最后决定使用哪个索引（或者最终选择不使用索引，而是全表扫描），优化器执行单表的策略是什么等等。

.6.3之后，还可以分析 , 和 语句，但通常我们还是用它们来进行查询。

本文主要从多个宏观角度介绍MySQL的优化策略，因此这里不再详细解释。

6. SQL和索引优化

1）SQL优化

SQL优化是指SQL本身的语法没有问题，但是有更好的写法来达到同样的目的。例如：

对于最后一个，我们举一个简单的例子。下面两条语句可以达到同样的目的，但是第二条语句的执行效率比第一条语句（存储引擎使用的）执行效率高很多。我们来感受一下：

-- 1. 大偏移量的查询mysql> SELECT * FROM user_innodb LIMIT 9000000,10;Empty set (8.18 sec)
-- 2.先过滤ID（因为ID使用的是索引），再limitmysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;Empty set (0.02 sec)

2）索引优化

为慢查询创建合适的索引是一种非常常见且非常有效的方法，但是索引是否会被高效地使用则是另一回事了。

4.存储引擎和表结构

1.选择存储引擎

一般情况下，我们会选择MySQL的默认存储引擎。然而，当对数据库性能的要求不断提高时，存储引擎的选择也成为关键的影响因素。

建议根据不同的业务选择不同的存储引擎，例如：

2. 优化字段

字段优化的最终原则是：使用能够正确存储数据的最小数据类型。

1) 整数类型

MySQL提供了6种整数类型，分别是：

不同的存储类型有不同的最大存储范围，自然占用不同的存储空间。

例如，如果该标志被删除，则建议使用它来代替。

2）字符类型

您是否直接将所有字符串字段设置为格式？就算不够，你也会直接设置为（1024）的长度？

如果不确定该字段的长度，则必须选择它，但需要额外的空间来记录该字段当前占用的长度；因此，如果字段的长度是固定的，尽量使用char，这样会节省很多内存空间。

3）不为空

尝试将非空字段设置为NOT NULL并提供默认值，或者使用特殊值而不是NULL。

因为NULL类型存储和优化会出现性能不佳的问题，具体原因这里就不展开了。

4）不要使用外键、触发器和视图函数

这也是《阿里巴巴开发手册》中提到的原则。原因有以下三个：

5）图片、音视频存储

不直接存储大文件，而是存储大文件的访问地址。

6）大字段分割和数据冗余

大字段拆分实际上就是前面提到的垂直表拆分。它将不常用的字段或数据量较大的字段拆分出来，以避免列太多、数据量太大，特别是如果你习惯写*.列数多、数据量大带来的问题会被严重放大！

字段冗余原则上不符合数据库设计范式，但非常有利于快速检索。例如，当合同表中存储客户ID时，可以冗余存储客户姓名，这样查询时就不需要根据客户ID获取用户名。因此，对业务逻辑做一定程度的冗余也是一种比较好的优化技巧。

5、业务优化

严格来说，业务优化已经不再是MySQL调优的手段，但业务优化可以非常有效地降低数据库访问压力。这方面的一个典型例子就是淘宝。下面给大家举几个简单的例子。主意：

程序员专属T恤

产品直接购买链接