MySQL优化

目录

SQL和索引优化

应用优化

连接池

增加cache缓存层

MySQL Server优化：查看MySQL用户手册

MySQL查询缓存

索引和数据缓存

MySQL线程缓存

并发连接数量和超时时间

---

SQL和索引优化

1、使用慢查询日志=》 设置慢查询时间=》记录慢查询SQL=》explain分析SQL执行计划=》优化措施

2、大表用索引

3、尽量用连接查询代替in，in会产生中间表，影响效率

应用优化

连接池

应用上一般访问数据库，都是先和MySQL Server创建连接，然后发送SQL语句，Server处理完成后，再把结果通过网络返回给应用，然后关闭和MySQL Server的连接，因此短时间大量的数据库访问消耗的TCP三次握手和四次挥手所花费的时间就很大了。添加连接池模块，相当于应用和MySQL Server事先创建一组连接，当应用需要请求MySQL Server时，不需要再进行TCP连接和释放连接了，一般连接池都会维护以下资源：

<1>连接池里面保持固定数量的活跃TCP连接，供应用使用

<2>若应用瞬间访问MySQL的量比较大，那么连接池会实时创建更多的连接给应用使用

<3>当连接池里面的TCP连接一段时间内没有被用到，连接池会释放多余的连接资源，保留它设置的最大空闲连接量就可以啦。

增加cache缓存层

业务上增加redis、memcache，一般用缓存把经常访问的数据缓存起来

MySQL Server优化：查看MySQL用户手册

对于MySQL Server端的优化，主要指的是MySQL Server启动时加载的配置文件的配置项内容的优化(就是my.ini或者my.cnf)，下面我们看看它的配置文件中有哪些是我们需要重点关注的优化参数

MySQL查询缓存

MySQL的查询缓存是把select查询语句上一次的查询结果记录下来放在缓存当中，下一次再查询相同内容的时候，直接从缓存中取出来就可以了，不用再进行一遍真正的SQL查询。但是当两个select查询时中间出现insert，update，delete语句的时候，查询缓存就会被清空。查询缓存适用更新不频繁的表，因为当表更新频繁的话，查询缓存也总是被清空，过多的查询缓存的数据添加和删除，就会影响MySQL的执行效率，还不如每次从磁盘上查来得快(缓存指的就是一块内存，内存I/O比磁盘I/O快得多)

可以在MySQL上通过以下命令，来查看查询缓存的设置：

通过show status命令，可以查看MySQL查询缓存的使用状况，如下：

 可以通过set命令设置上面的缓存参数开启MySQL查询缓存功能，也可以找到MySQL的配置文件(windows是my.ini,linux是my.cnf)，修改query_cache_type参数为1就可以了，然后重启MySQL Server就可以使用了，如下： 

set query_cache_type=1;
set query_cache_type=10;

索引和数据缓存

主要指的是innodb_buffer_pool_size配置项，从名字上就能看到，该配置项是针对InnoDB存储引擎起作用的，这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓冲区大小。innodb_buffer_pool_size是同时为数据块和索引块做缓存，这个值设得越高，访问表中数据需要的磁盘I/O就越少。

set innodb_buffer_pool_size=512M;

MySQL线程缓存

主要指配置文件中thread_cache_size配置项。给大家讲过MySQL Server网络模块采用经典的I/O复用+线程池模型，之所以引入线程池，主要就是为了业务执行的过程中，不会因为临时创建和销毁线程，造成系统性能降低，因为线程的创建和销毁是很耗费性能的，所以线程池就是在业务使用之前，先创建一组固定数量的线程，等待事件发生，当有SQL请求到达MySQL Server的时候，在线程池中取一个线程来执行该SQL请求就可以了，执行完成后，不销毁线程，而是把线程再归还线程池中，等待下一次任务的处理(MySQL会根据连接量，自动加大线程池的数量)

set thread_cache_size=10;

配置完thread_cache_size，重启MySQL Server服务生效

并发连接数量和超时时间

MySQL Server作为一个服务器，可以设置客户端的最大连接量和连接超时时间，若数据库连接统计数量比较大，这两个参数的值需要设置大一些

查看连接数量：

show variables like '%connect%';

在配置文件(my.cnf或my.ini)最下面，添加配置：max_connections=2000，然后重启MySQL Server，设置生效。

查看超时时间：

show global variables like '%timeout%';

MySQL Server对于超时未通信的连接，进行主动关闭操作。设置超时时间，超过设置时间没有请求就主动断开，单位是秒，在配置文件中添加配置：wait_timeout=600。