【MySQL】MySQL优化

MySQL优化

我们都知道数据库是系统最脆弱的部分，因为应用可以多实例部署，而数据库若进行分库则挑战不小。当请求压力增大后一定阈值后，容易导致数据库卡死或崩溃。那么究竟该如何进行MySQL优化呢？

这里有8中最基本的方法供大家参考。

在这里，先介绍MySQL的一种日志——慢查询日志。

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。

我们可以通过SQL语句查看慢查询日志的当前开启状况和存储的位置。

mysql> show variables like '%slow_query%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| slow_query_log      | ON               |
| slow_query_log_file | WIN7X64-slow.log |
+---------------------+------------------+
2 rows in set, 1 warning (0.16 sec)

当然这两个字段是可以进行修改的。在MySQL的配置文件中有相关的字段。如图：

其中slow_query_log字段表示当前慢查询日志是否开启（1表示开启，0表示关闭，slow_query_log_file字段表示慢日志目前存储的文件名。而long_query_time字段表示当前慢查询日志所设置的超过时间，即执行时间超过目前设置的值，当前执行的SQL就会被记录在慢查询日志当中。当然这个字段也可以通过SQL语句进行查询，例如：

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.001000 |
+-----------------+----------+
1 row in set, 1 warning (0.06 sec)

long_query_time字段不仅可以通过配置文件修改也可以通过SQL语句进行修改。如：

mysql> set long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

现在修改成超过1秒的SQL都会被记录在慢查询日志当中！

慢查询日志，默认名称是host_name-slow.log，存放在MySQL的数据路径下，其内容格式显示大致如下：

# Query_time: 0.012000  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 139
use tuluneducation;
SET timestamp=1534527397;

将log_queries_not_using_indexes参数设置为ON，可以捕获到所有未使用索引的SQL语句。

了解了慢查询日志后，我们再来看看找出问题之后又该如何进行MySQL优化！ 例如：

select id,author from subject where content like '%linux%' and title like '%c++ linux%';

通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间，那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优化。

在这种情况下，我们可以进行两种情况的优化：

• 1、书写合适的SQL语句。看看是否因为我们的SQL语句书写错误或者语句过于陈余导致了查询没有使用到索引，继而导致了效率低下。
• 2、如果通过explain查询后发现查询过程中使用到了索引，但是还是效率很低。此时需要分析是否是使用索引的过程比较耗时，原因是索引存储在文件中，也需要执行文件IO，导致效率低下。具体解决方法可以通过改变索引和数据缓存来解决此问题。具体操作可以在MySQL的配置文件中改变不同引擎下的索引缓存或者数据缓存达到解决目标。MyISAM和InnoDB引擎所对应的字段分别为：
  • MyIsam: key_buffer_size=8M 索引缓存
  • innodb_buffer_pool_size=8M 索引和数据缓存

3、若不是上述两种问题，而是因为项目在创建连接的时候出现问题。例如此时用户量非常大（假定为100w），每一个用户都要创建连接，执行SQL语句并且释放连接，此时会重复创建100w次，十分耗时。在这种情况下我们可以通过增加连接池的方式来解决问题。作用是重复使用连接（对象）。

4、若用户可以正常连接数据库，但是当前线程或任务的主要行为是对数据进行查询修改，当对象非常庞大时，必定会进行大量的文件IO，而文件IO次数的频繁必然会导致非常耗时，从而使效率低下。此时，我们可以通过增加缓存池的方式来进行解决，例如增加redis数据库专门用于存储缓存，而redis数据库相对于MySQL数据库是属于独立机制，让其专门用于存储缓存，可以大大减少MySQL负载的压力，从而减少文件IO的次数提高数据库的效率。

5、除了增加redis数据库专门用于存储缓存之外，若进行数据库连接的用户人数不至于新增其他设配，我们可以通过修改MySQL自身的数据查询缓存来提高效率。具体操作依然是改变配置文件中的相关字段，如：

• query_cacge_type修改MySQL的缓存设置是否开启，其中1表示开启；0表示关闭。
• query_cache_size表示设置缓存的大小。这里的大小要注意参考当前环境的内存大小是否能够支持自己所设置的大小，若自身环境不足以支持，还是建议新增缓存池（参考第4点优化过程）。
• 修改完毕之后需要重启MySQL服务。

6、我们都知道MySQL是C/S系统（客户端服务器），需要进行网络连接，也就是说C/S系统实际上是一种网络模型，而这种网络模型底层是使用C++中的epoll和线程池完成的，所谓线程池是用来处理任务的，它本身存储的就是各种任务的线程。而线程主要就是处理具体问题的（例如处理任务的工人）。当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还到线程池中，等待下一次任务的处理（当然MySQL会根据用户量，自动加大线程池的数量的）。因此如果我们发现是线程池出现问题，则需要改变线程池的大小。即改变MySQL的线程缓存大小。

• 具体操作为在配置文件中修改thread_cahe_size字段。

7、如果在和MySQL建立连接过程中出现问题，例如同时连接的用户量大于MySQL的连接量，则会出现问题，此时需要调整MySQL的最大连接量。

• 具体修改配置文件中的max connections字段，

• 可以通过SQL语句show variables like ‘%connect%’;查看MySQL此时的最大连接量。

mysql> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 100             |
| max_connections                               | 151             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+
9 rows in set, 1 warning (0.01 sec)

8、增大默认连接时间。如果连接超时时间过低，则若用户建立连接的速度过慢，会导致有一些用户无法和MySQL正常建立连接。可以通过show global variables like ‘%timeout%’;查看此时的连接超时时间（wait_timeout字段，默认值为28800s，一般不需要修改）。

mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set, 1 warning (0.03 sec)

• 若非要修改可以在配置文件中添加wait_timeout字段（例如wait_timeout=30000），然后重启MySQL服务。

不同的场合有不同的优化操作，我们需要根据实际情况来判断应该进行怎样的优化。这里就简单的说一说常见的8种优化方式，在实际优化过程中还得具体问题具体分析。