一、mysql的优化问题
- 我们在使用mysql时候,可能会出现一些性能低下的问题,也就是说我们在使用mysql时候,可能sql语句书写正确,逻辑思维也是没问题的,但mysql反应比较慢,这就是mysql效率比较低下,必须要进行优化。
二、慢查询日志
- 慢查询日志作用
MySQL可以设置慢查询日志,当SQL执行的时间超过我们设定的时间,那么这些SQL就会被记录在慢查询日志当中,然后我们通过查看日志,用explain分析这些SQL的执行计划,来判定为什么效率低下,是没有使用到索引?还是索引本身创建的有问题?或者是索引使用到了,但是由于表的数据量太大,花费的时间就是很长,那么此时我们可以把表分成n个小表,比如订单表按年份分成多个小表等。
- 打开慢查询日志
mysql> show variables like '%slow_query%';
说明慢查询日志已经打开
方法二:
我们也可在my配置文件中my.ini中:slow-query-log=1 打开慢查询日志 设置为0 关闭
- 设置慢查询日志时间
慢查询日志记录了包含所有执行时间超过参数 long_query_time(单位:秒)所设置值的 SQL语句的日志,在MySQL上用命令可以查看,如下:
这个值当然是可以修改的,如下:
mysql> set long_query_time = 1; #注意,单位是秒
现在修改成超过1秒的SQL都会被记录在慢查询日志当中!可以设置为0.01秒,表示10毫秒。
三、优化mysql的八种方案
1>书写合适的sql语句
- 我们可以用:explain 分析,看看是否我们的sql语句没用到索引,引起索引失效?sql语句没有进入到索引的B+树
2>增加连接池
- 原因分析:
我们知道,我们使用mysql时候,首先就是我们电脑客户端连接到mysql服务器上;那当我们用户量很大的时候,都要去操作数据库,创建连接,释放连接(每次都要重新创建一个mysql客户端对象)的过程,很耗时。 - 连接池作用:
重复使用连接(对象),也就是不用每次客户端登陆,我们都要创建连接,现在连接是可重复使用,节约时间,也节约了资源成本。
3>增加缓存池(redis数据库)
- 原因分析:对数据查询,修改操作很频繁,也就意味着我们文件IO次数很多。
有人可能会说:我们mysql不是有缓存机制嘛,Buffer pool机制,但我们不得不承认的是,Buffer pool内存就那么大,面临频繁的大量数据操作,力不从心.;redis数据库,一个第三方的数据库,专门去做缓存池 - 缓存池作用:
减少了文件IO(特别耗时)的次数
4>修改mysql查询缓存
- query_cache_type=1 开启缓存
- query_cache_size=10 改变缓存大小
改变配置文件中的数据,记得重启服务。
5> 修改索引和数据缓存
- 原因分析:
explain 发现sql语句用到索引 如果是使用索引的过程比较耗时 - 处理:
MyIsam: key_buffer_size=8M 索引缓存
innodb_buffer_pool_size=8M 索引和数据缓存
6>Mysql线程缓存
- 线程的作用:
处理具体任务。( 工人) - 线程池 : thread_cache_size=10
我们之前就提到过MySQL如果不在我们自己的服务器上的时候,我们要进行远程访问。既然要远程访问就离不开网络模型。 - MySQL所使用的是epoll+ 线程池的模型。这个线程中存放的是能够处理具体任务的线程,当有SQL请求到达MySQL Server的时候,在线程池中取一个线程来执行该SQL请求就可以了,执行完成后,不销毁线程,而是把线程再归还到线程池中,等待下一次任务的处理(当然MySQL会根据用户量,自动加大线程池的数量的)。
7>调整MySQL最大连接量的大小
- 原因分析:
当我们发现由于最大连接数量过低 导致有大量用户无法建立连接。 - 处理:
show variables like '%connect%';
max_connections=151
上面最大连接量151,我们可以适当改大点!
8>连接超时时间
- 原因分析:
如果设置连接超时时间过低,用户建立连接速度过慢。就会出现有一些用户无法和mysql正常建立连接。 - 处理:
show global variables like '%timeout%';
在配置文件中添加配置:wait_timeout=600。28800 单位秒