笔记整理2.12-数据库优化-my.cnf优化-慢查询日志分析

内核分为进程管理系统、内存管理系统、I/O管理系统和文件管理系统等四个子系统.

 

1. 硬件优化(cpu-内存不使用swap-硬盘高转速raid ssd-网络高速网卡-team)
2. 数据库设计与规划--以后再修改很麻烦，估计数据量，使用什么存储引擎
3. 数据的应用--怎样取数据，SQL语句的优化
4. 磁盘 IO优化
5. 操作系统的优化--内核、TCP连接数量
6. MySQL服务优化--内存的使用，磁盘的使用
7. my.cnf 内参数的优化
8. 分库分表思路和优劣

查询优化

1. 表结构合理，不宜过大；精确的类型。TIME合理使用DATE-DATETIME。 
2. 索引，建立合适的索引。 
3. 查询时尽量减少逻辑运算； 
4. 减少不当的查询语句，不要查询应用中不需要的列 
5. 减小事务包的大小； 
6. 将多个小的查询适当合并成一个大的查询，减少每次建立/关闭查询时的开销； 
7. 将某些过于复杂的查询拆解成多个小查询，和上一条恰好相反 
8. 建立和优化存储过程来代替大量的外部程序交互

 

数据库读写多的文件放在独立硬盘上
设置TCP连接数量限制，优化系统打开文件的最大限制
使用64位操作系统，64位系统可以分给单个进程更多的内存，计算更快 。 
禁用不必要启动的服务 
文件系统调优，给数据仓库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。 ext3 不错。 ext4 只是一个过渡的文件系统。 

 

可以考虑在挂载分区时启用 noatime 选项。 #不记录访问时间
[root@xuegod63 ~]# vim /etc/fstab #在挂载项中添加noatime选项就可以了。

UUID=ad959e1b-f05a-422f-8392-39e90253a7d8 /date xfs defaults,noatime 0 0

最小化原则:
1） 安装系统最小化。
2） 开启程序服务最小化原则。
3） 操作最小化原则。
4） 登录最小化原则。
5） 权限最小化。

存储引擎：

myisam 引擎，表级锁，表级锁开销小，影响范围大，适合读多写少的表，不支持事务。 表锁定不存在死锁 （也有例外）
innodb 引擎，行级锁，锁定行的开销要比锁定全表要大。影响范围小，适合写操作比较频繁的数据表。行级锁可能存在死锁。 

MySQL 不同的存储引擎支持不同的锁机制。

#开启后会将所有的死锁记录到error_log中 错误日志在my.cnf配置为log-error=/var/log/mysqld.log
[root@xuegod63 ~]# vim /etc/my.cnf #在未行加入以下两项
log-error=/var/log/mysqld.log
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M

[root@xuegod63 ~]# mysql -u root -p123456
mysql> show status; 看系统的状态
mysql> show engine innodb status \G#显示 InnoDB 存储引擎的状态
mysql> show variables;#看变量，在 my.cnf 配置文件里定义的变量值
mysql> show warnings;#警告报错
mysql> show processlist;#显示MySQL系统中正在运行的所有线程，可以看到每个客户端正在执行的命令

启用 mysql 慢查询：---分析 sql 语句，找到影响效率的 SQL
[root@xuegod63 ~]# vim /etc/my.cnf
[mysqld]
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/var/lib/mysql/slow.log #这个路径对 MySQL用户具有可写权限
long_query_time = 5 #查询超过 5 秒钟的语句记录下来
log-queries-not-using-indexes = 1#没有使用索引的查询

my.cnf 内参数的优化
优化总原则：给 MySQL的资源太少，则 MySQL施展不开：给 MySQL的资源太多，可能会拖累整个 OS。
40%资源给OS， 60%-70% 给MySQL（内存和CPU）

PHP发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果

查看：查询缓存
mysql> show status like 'qcache%';

1. Qcache_free_blocks #缓存中相邻内存块的个数。数目大说明可能有碎片。
如果数目比较大，可以执行：
mysql> flush query cache;
#对缓存中的碎片进行整理，从而得到一个空闲块。
2. Qcache_free_memory #缓存中的空闲内存大小
3. Qcache_hits #每次查询在缓存中命中时就增大。
4. Qcache_inserts #每次插入一个查询时就增大。即没有从缓存中找到数据
5. Qcache_lowmem_prunes #因内存不足删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返个数字最好长时间来看；如果返个数字在不断增长，就表示可能碎片非常严重，或者缓存内存很少。
如果Qcache_free_blocks比较大，说明碎片严重。 如果 free_memory 很小，说明缓存不够用了。
6. Qcache_not_cached # 没有进行缓存的查询的数量，通常是这些查询未被缓存或其类型不允许被缓存
7. Qcache_queries_in_cache # 在当前缓存的查询（和响应）的数量。
8. Qcache_total_blocks #缓存中块的数量

show status like '%key_read%';#关键字缓冲查询命中超过0.1%就高了

vim /etc/my.cnf
[mysqld]

innodb_print_all_deadlocks = 1#死锁记录
innodb_sort_buffer_size = 16M#死锁记录
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/var/lib/mysql/slow.log #这个路径对 MySQL用户具有可写权限 
long_query_time = 5 #查询超过 5 秒钟的语句记录下来
log-queries-not-using-indexes = 1#没有使用索引的查询
query_cache_size = 32m    #至少4M以存储数据结构，可扩展。整体100G，若此服务器只运行MySQL服务器。70-80G给mysql
max_connections = 500 #最大连接
wait_timeout = 10#空闲时间超过10秒断开连接
max_connect_errors = 100#多次失败后会被锁定直到执行mysql> FLUSH HOSTS;
table_open_cache=23#缓存23个表 2G 256-512个。1G 128-256
key_buffer_size = 512M #关键字缓存只做数据库的时候物理内存的80%
skip-name-resolve #关闭反查询速度快但只能用ip
validate-password=off #密码复杂关闭

lower_case_table_names=1#1区分不大小写0区分大小写
thread_cache_size=128#线程池缓存大小(新客户端连接不用重新创建线程)3G  ?> 64
sort_buffer_size=512K#排序会话的缓存大小
read_buffer_size=512K#全表扫描的MYISAM数据表线程指定缓存
read_rnd_buffer_size=1M#当从一个已经排序的键值表中读取行时，会先从该缓冲区中获取而不再从磁盘上获取。默认为256K
query_cache_type = 1#1表示缓存所有查询，2表示缓存select sql_cache的查询0是关闭

 可使用的配置

innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M
slow_query_log = 1
slow-query-log-file=/var/lib/mysql/slow.log
long_query_time = 5
log-queries-not-using-indexes = 1
query_cache_size = 32m
max_connections = 500
wait_timeout = 10
max_connect_errors = 100
table_open_cache=23
key_buffer_size = 512M
validate-password=off
thread_cache_size=24
sort_buffer_size=512K
read_buffer_size=512K
read_rnd_buffer_size=1M
query_cache_type = 1

慢查询分析

mysqldumpslow –s c –t 10 /var/lib/mysql/slow.log#命令统计
Count:414语句出现了414次；
Time=3.51s（1454）执行最长时间为3.51s，累计总耗费时间1454s；
Lock=0.0s（0）等待锁最长时间为0s，累计等待锁耗费时间为0s；
Rows=2194.9（9097604） 发送给客户端最多的行数为2194.9，累计发送给客户端的函数为90976404

cat /var/lib/mysql/slow.log#日志文件
show global status like '%slow%';#登录数据库中查询
set long_query_time = 10;
show variables like '%quer%';#慢查询状态查看

 mysqldumpslow -a -s t -t 2 /var/lib/mysql/slow.log

总结：
1、看机器配置，指三大件：cpu、内存、硬盘
2、看MySQL配置参数
3、查看MySQL行状态
4、查看MySQL的慢查询
依次解决了以上问题之后，再来查找程序方面的问题

32G内存优化mysql

https://blog.csdn.net/demonson/article/details/80349591