你的位置:首页 > 数据库

[数据库]mysql优化


1.启用InnoDB的每张表一个数据文件设置

InnoDB可以把每个数据库的信息保存在一个.idb数据文件中,每个.ibd文件代表自己的表空间,通过这样的方式可以更快地完成类

似“TRUNCATE”的数据库操作,当删除或截断一个数据库表时你也可以回收未使用的空间,这样配置的另一个好处是你可以将某些数据

 库表存放在一个单独的存储设备,这大大提升你磁盘的I/O负载

MySQL5.6及以上的版本默认启用了innodb_file_per_table=1

 

2.将MySQL数据分储到独立分区上,注意此设置在MariaDB上无效

建议使用单独的磁盘(最好是SSD)用于MySQL服务,创建新分区,格式化新分区,并挂载新分区这里略过,假设/ssd是我们新分区挂载的

目录,MySQL数据目录是/var/lib/mysql, 接下来复制整个数据目录到新分区中 cp -Rp /var/lib/mysql /ssd/  复制完成后重命名原来的数据目

录 mv /var/lib/mysql /var/lib/mysql-backup 接下来创建一个符号链接 ln -sv /ssd/mysql /var/lib/mysql

 

3.优化使用InnoDB的缓冲池,InnoDB引擎在内存中有一个缓冲池用于缓存数据和索引,这有助于你更快地执行MySQL/MariaDB查询语

句,在一个专用的机器上,你可能会把60%~70%的内存分配给innodb_buffer_pool_size,如果机器还运行了其它的服务应该重新考虑这个值

得设置

 

4.在MySQL中避免使用swappiness,‘交换’是一个当系统移动部分内存到一个称为‘交换空间’的特殊磁盘空间时的过程,当系统用完

物理内存后就会出现这种情况,系统将信息写入磁盘而不是释放一些内存,磁盘要比内存慢得多因此 sysctl -w vm.swappiness=0 关闭

swappiness

 

5.设置MySQL的最大连接数,只有当执行MySQL请求的时候才会建立连接,执行完成后会关闭连接并被新的连接取代,记住,太多的连

接会导致内存的使用量过高并且会锁住你的MySQL服务器,一般小网站100~200,较大可能需要500~800甚至更多,可以动态地改变

max_connections的值而无需重启MySQL服务器 set global max_connections = 500;

 

6.配置MySQL的线程缓存数量,当客户端断开连接时,如果当前线程数小于thread_cache_size,它的线程将被放入缓存中,下一个请求通

过使用缓存池中的线程来完成,要提高服务器的性能,你可以将此值设置相对高一些,查看线程缓存命中率 show status like

‘threads_created’; show status like 'connections'; 使用以下公式来计算线程池的命中率 100 - ((threads_created / connections) *

100) 如果得到一个较低的数字,意味着大多数mysql连接使用新的线程,而不是从缓存中加载,这时需要增加thread_cache_size值,这里有

一个好处是可以动态地改变thread_cache_size,而无需重启MySQL服务,通过以下方法实现 mysql > set global thread_cache_size = 16;

 

7.禁用MySQL的DNS反向查询,在配置文件中添加以下选项 skip_name_resolve

 

 

8.配置MySQL的查询缓存容量,如果你有很多重复的查询并且数据不经常改变---请使用缓存查询,query_cache_size 设置为GB级会降低服

务器的性能,原因是更新过程中线程需要锁定缓存,通常设置为200~300MB应该足够了,网站比较小可以先给64M并在以后及时去增

加,在配置文件中添加如下配置 query_cache_type = 1 query_cache_size = 64M  query_cache_limit = 256K  query_cache_min_res_unit = 2k

 

 

9.配置临时表容量和内存表最大容量,tmp_table_size 和 max_heap_table_size 这两个变量的大小应该相同,它们可以让你避免磁盘写

入,tmp_table_size 是内置内存表的最大空间,如果表的大小超出限值将会被转换为磁盘上的MyISAM表,这会影响数据库的性能,建议

在服务器上设置这两个值为每GB内存给64M, tmp_table_size= 64M  max_heap_table_size= 64M

 

10.启用MySQL慢查询日志

 

11.检查MySQL的空闲连接,空闲连接会消耗资源,空闲连接是出于‘sleep’状态并且保持了很长一段时间的连接,使用 mysqladmin

processlist -uroot -p | grep “sleep” 查看空闲连接 set global wait_timeout= 60 要重启也有效则需修改配置文件,默认的值为28800秒

 

12.有时候MySQL/MariaDB 数据库中的表很容易崩溃,尤其是服务器意外关机,文件系统突然崩溃或复制过程中仍然访问数据库,有一

个称为‘mysqlcheck’的免费开源工具,它会自动检查,修复和优化linux中数据库的所有表  mysqlcheck --auto-repair --check --optimize -

-all-databases

mysqlcheck --auto-repair --check --optimize databasename