【面试】数据库总结4（MySQL优化思路）

5 MySQL数据库优化思路

5.1 优化层次

5.1.1 连接——对数据库的配置优化（目标都是硬件本身的优化）

5.1.2 缓存——架构优化

5.2 优化器——SQL语句分析与优化

5.2.1 慢查询日志  Slow query log

5.1.2慢日志分析

5.2 SHOW PROFILE

5.2.1查看是否开启

5.2.2查看profile 统计(命令最后带一个s)

5.2.3其他系统命令

5.3 EXPLAIN 执行计划

5.3.1 id

5.3.2 select type 语句类型

5.3.3  type连接类型

5.4 存储引擎与表结构优化

5.4.1存储引擎的选择

5.4.2字段定义

5.5 优化总结

---

5 MySQL数据库优化思路

5.1 优化层次

5.1.1 连接——对数据库的配置优化（目标都是硬件本身的优化）

第一个环节是客户端连接到服务端

连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。

比如报了一个Mysql: error1040: Too many connections的错误。

可以从两个方面来解决连接数不够的问题:

1、从服务端来说，我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以:

(1）修改配置参数增加可用连接数，修改 max_connections的大小:
show variables like 'max_connections'; --修改最大连接数，当有多个应用连接的时候

(2）或者，或者及时释放不活动的连接。

交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。

show global variables like 'wait_timeout';--及时释放不活动的连接，注意不要释放连接池还在使用的连接

2、从客户端来说，可以减少从服务端获取的连接数。

如果我们想要不是每一次执行SQL都创建一个新的连接，可以引入连接池（如C3P0），实现连接的重用。

对于一个客户端，只要维护一个连接池就可以了。

5.1.2 缓存——架构优化

（1）缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题:一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。

我们可以用第三方的缓存服务来解决这个问题，例如Redis。

（2）集群，主从复制

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。

做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做基于主从复制的读写分离。

读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。会有一定程度的延迟。

（3）分库分表

垂直分库，减少并发压力。水平分表，解决存储瓶颈。
垂直分库的做法，把一个数据库按照业务拆分成不同的数据库:

水平分表：把单张表的数据按照一定规则分布到多个数据库。 

5.2 优化器——SQL语句分析与优化

5.2.1 慢查询日志  Slow query log

（1）打开慢日志开关

因为开启慢查询日志是有代价的（跟bin log. optimizer-trace一样)，所以它默认是关闭的:

show variables like 'slow_qucry%';

除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variable