mysql性能调优

mysql性能调优 (DBA重要！)

提高MySQL系统的性能、响应速度

调优思路总结

手段 具体操作
升级硬件(替换有问题的硬件) (CPU/磁盘/内存等) (英文:CPU/DISK/MEMORY)
加大网络带宽 付费加大带宽
调整mysql服务运行参数 并发连接数，连接超时时间，重复使用的线程数...
调整与SQL查询相关的参数(优化SQL查询) 查询缓存、索引缓存...
启用慢查询日志 slow-query-log
网络架构不合理 调整网络架构
################################################################################
监控服务器是服务器的一种，也放在机房里，用来监控网站和数据库服务器的硬/软件资源，包括(CPU/磁盘/内存等)。
在没有监控器的情况下，如果查看系统的使用情况，看三大硬件的使用率(CPU/磁盘/内存)。如果发现使用率特别高，说明它空间不足，需要你去升级硬件，买更高配置的硬件。花钱换机器是最有效的办法。你去优化服务的参数，从根本上能把你服务器的性能提高5%就已经很了不起了。但是你得知道哪个硬件有问题，是哪个硬件的处理速度不行呢？所以要看服务运行时的一些值，获取哪个硬件有问题。
##################################################################################
top

# top 查看哪些程序占用CPU的百分比是多少。不同类型的资源占用CPU的使用情况。

显示结果如下：
top - 09:16:16 up 16 min, 1 user, load average: 0.00, 0.01, 0.05
Tasks: 115 total, 1 running, 114 sleeping, 0 stopped, 0 zombie
%Cpu(s): 4.8 us, 23.8 sy, 0.0 ni, 71.4 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 1016260 total, 412672 free, 307956 used, 295632 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 528812 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1511 root 20 0 157716 2108 1484 R 5.0 0.2 0:00.07 top
1 root 20 0 128164 6820 4052 S 0.0 0.7 0:01.33 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 0:00.02 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kworker/u2:0
7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root 20 0 0 0 0 S 0.0 0.0 0:00.25 rcu_sched
10 root rt 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
12 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kdevtmpfs
13 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 netns
14 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khungtaskd
15 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 writeback
16 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kintegrityd
17 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 bioset
18 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kblockd
########################################################################
uptime 单独看CPU的命令

# uptime
09:17:03 up 1:08, 1 user, load average: 0.00, 0.01, 0.05
连续在线的时间1小时8分钟 1分钟 5分钟 15分钟(的负载)

uptime 这个命令是单独看CPU的，主要是看平均负载(load average)分别是：1分钟的，5分钟的，15分钟的负载。如果它的数越大，说明CPU在单位时间内等待它处理的程序就越多。因为堆积的越来越多，所以数值就越来越大。因为它处理不来了，所以要等。说明你的CPU核数少，可以给它加几个核。一般的CPU是8核，16核。

up后面的数字代表连续在线的时间，数字越大，说明服务器越稳定，因为它代表连续在线的时间，是没有重启过的时间。如果你三天两天的重启一回，说明你的服务器不稳定。
########################################################################
free -m

# free -m
显示结果如下：
total used free shared buff/cache available
Mem: 992 300 403 7 288 516
Swap: 2047 0 2047
交换分区总共2G多 交换分区的使用量0 交换分区的空闲是2G多

free -m 这个命令是以兆(m)为单位，查看内存的使用情况，一般服务器的内存配置都是128G，64G。Mem是物理内存的使用情况。Swap是交换分区。上面显示的交换分区总共2G多，交换分区的空闲也是2G多。如果交换分区的空闲特别少的话，说明你的交换分区使用量很大，物理内存不足。所以这个时候要升级你的物理内存了。(单词含义：total总共多大。used使用量。free空闲。)
########################################################################
iostat

# iostat 查看硬盘的读取状态
显示结果如下：
Linux 3.10.0-693.el7.x86_64 (client50) 2018年11月29日 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
0.23 0.00 0.42 0.58 0.05 98.72

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 7.04 187.24 39.13 231550 48391
dm-0 6.63 178.88 37.48 221203 46343
dm-1 0.08 1.80 0.00 2228 0
读 写 读 写

iostat 这个命令是查看硬盘的io状态。查看硬盘存储数据时的存储速度(写)，和从硬盘上读取数据的速度(读)。
io中的i是往硬盘里写，o是从硬盘里读出来。kB_read/s和kB_read都代表读，kB_wrtn/s和kB_wrtn都代表写。读写越快越好，这个数值越大越好。
###############################################################
ifconfig eth0

如果硬件没问题，那可能是网络有问题。如果带宽太少，不够用的话，就花钱买带宽。比如现在带宽是100M，我们就升级为200M。
查看当前你用的网卡的传输速率。ifconfig后面加上网卡名，如eth0

# ifconfig eth0
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.50 netmask 255.255.255.0 broadcast 192.168.4.255
inet6 fe80::e8cf:cfd:8f7d:4435 prefixlen 64 scopeid 0x20<link>
ether 52:54:00:a5:17:22 txqueuelen 1000 (Ethernet)
RX packets 1263 bytes 98153 (95.8 KiB) 查看传输的速率，可以看到是不是带宽的问题
RX errors 0 dropped 10 overruns 0 frame 0 查看传输的速率
TX packets 207 bytes 29898 (29.1 KiB) 查看传输的速率
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 查看传输的速率
##################################################################
rpm -qa | grep -i mysql

如果硬件和网络都没问题，那可能是当前主机的数据库服务本身的配置问题，即提供数据库服务的软件本身的版本太低，导致服务运行时的参数有限制。

# rpm -qa | grep -i mysql 查看我们现在在用的数据库服务版本信息，i指忽略大小写
... ...
mysql-community-server-5.7.17-1.el7.x86_64 可以看到带有server字样的是5.7版本。

我们安装数据库软件时，服务起来时，如果不修改服务运行的参数，它就是默认的。
也许是那个默认值比较小。我们可以修改配置文件里面它服务运行时的默认值，把它调大，这样它处理的速度就变快。
可是我们在去调某个选项的值时，可能会受限于软件版本，它可能有个上限要求。
即使服务器有那么多的内存和磁盘，我们也用不了，导致获取不了那些资源来运行服务，然后处理速度就会变慢。
一般我们把服务器架构好之后，都会去调整一下服务的运行选项的值。
################################################################
并发及连接控制

连接数、连接超时

选项 含义
max_connections 允许的最大并发连接数
connect_timeout 默认连接超时，默认10秒，仅登陆时有效
wait_timeout 等待超时。等待关闭连接的不活动超时秒数，默认28800秒(8小时)
################################################################################
mysql> show variables; 查看所有变量
mysql> show global variables; 查看所有全局变量
mysql> show variables like "%time%"; 查看和时间有关的变量
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 | 默认连接超时
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_password_lifetime | 0 |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| log_timestamps | UTC |
| long_query_time | 10.000000 |
| max_execution_time | 0 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timestamp | 1543454998.270121 |
| wait_timeout | 28800 | 等待超时
+---------------------------------+-------------------+
############################################################################
mysql> show variables like "%conn%"; 查看和连接有关的变量

+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
+-----------------------------------------------+-----------------+
############################################################################

查看当前已使用的连接数
MariaDB [(none)]> show global status like "max_used_connections";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+

查看默认的最大连接数
mysql> show variables like "%max_connections%"; 服务曾经有过的最大并发(最大连接数)。默认最大连接数151
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+

理想的比率是 <=85%
当前已使用的连接数 /除以 默认的最大连接数<=85%是最好的状态。

Max_used_connections的值 /除以 max_connections的值 =85%
当比率为85%，相当于设置了max_connections这个值之后，预留了15%的空闲资源，留给高峰访问。
我们也可以设置为预留20%的空闲资源，也就是Max_used_connections的值 /除以 max_connections的值 =80%。

先查询Max_used_connections这个数以后，再通过计算得出max_connections的值应该设置为多少。

MariaDB [(none)]> show global status like "Max_used_connections";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+

MariaDB [(none)]> set global max_connections=数字(你算出的比较合理的数字)，假如我们算出的数是300。
##################################################################
mysql> set global max_connections=300; 在命令行修改默认最大连接数为300，仅本次生效，重启失效。

或者在配置文件里写，就能永久生效。
# vim /etc/my.cnf
[mysqld]
max_connections=300
... ...

mysql> show variables like "%max_connections%";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 300 | 可以查看到值已经变成了300
+-----------------+-------+
#######################################################################
服务程序的运行参数调整！
(这在DBA工作中很重要！要根据服务器的运行情况来调整，我们要知道这个思路！)！

mysql> show status; 查看状态，根据数据库当前的运行情况产生的一些值。

mysql> show global status; 查看所有的全局状态变量

mysql> show global status like "%conn%"; 模糊查询所有与连接(conn)相关的全局状态变量。

+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_connects | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 5 |
| Locked_connects | 0 |
| Max_used_connections | 1 | 曾经有过的最大连接数
| Max_used_connections_time | 2018-11-29 09:37:45 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+-----------------------------------------------+---------------------+

MariaDB [(none)]> show variables like "%conn%";
+--------------------------+-----------------+
| Variable_name | Value |
+--------------------------+-----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| extra_max_connections | 1 |
| init_connect | |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_user_connections | 0 |
+--------------------------+-----------------+
###################################################################
# netstat -tunlp | grep :3306
tcp6 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1490/mysqld

结果显示的tcp6，代表数据库是用tcp协议连接的，6代表它也用ipv6的地址。客户端用tcp连接数据库时，是要经过3次握手的。

mysql> show variables like "%timeout%"; 显示有关超时的变量
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 | 单位秒。等待连接超时。默认为10秒。
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 | 单位秒。等待执行操作的超时时间。默认28800秒
+-----------------------------+----------+

connect_timeout 等待连接超时,如果调为30秒，会造成拒绝服务攻击。
不能调太长比如30秒。上面显示connect_timeout的值是10秒，也就是TCP的3次握手，如果客户端10秒内没有连接上数据库，这个连接就断开了。假如我们把超时时间设置为30秒，就可能会对你的服务器造成攻击，就是拒绝服务攻击。客户端与服务器建立连接时，3次握手如果没有成功的话。服务器要等30秒后才会断开。假如别人攻击你了，无限制地向你发起连接请求。他写了一个死循环，循环1万次，这1万次就是要连接你的mysql服务。他一直连服务器，但是服务器给客户端回了应答包之后，客户端就不给服务器回应了。当这个客户端不给服务端回第3个包的时候，服务器就会等30秒。服务器就傻傻的等30秒才会断开连接，客户端就是不给服务端回。这样1万次循环，服务器就会等待1万个30秒。客户端有1万个连接，服务端就要有1万个来响应，这些资源都用来响应这个不是客户的攻击者！根本没有办法来响应正常的客户端的连接，提供不了正常的服务请求，就是拒绝服务攻击！

也不能太短。如果调成1秒，客户端还没连接上，就被断开连接了。这样客户端如果就是想要玩游戏，不停的登陆，服务器就不停的响应，就会很忙。所以调太大或者调太小都不好。

wait_timeout 等待执行操作的超时时间。是指客户端连接上数据库后,服务端等待客户端执行指令的超时时间。等客户端执行mysql的insert,select等SQL指令。默认28800秒除以3600秒=8小时。这个不能设置得太短。应该设置得比较长才好，或者直接默认配置就行。
###############################################################################
MariaDB [(none)]> flush status; 刷新状态的值，把当前变量的值重新清一下，重新计数。

mysql> show processlist; 查看当前都有谁访问自己,谁正在连自己,用的什么操作。processlist(程序列表)。

+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+

什么用户在哪个主机，有没有操作任何库呢，有没有执行过命令呢。命令执行的时间是什么，状态是什么。具体执行的操作是什么？
显示你当前数据库服务器的程序列表。比如还有其他人正在连着你的数据库服务器呢，它也能列出来。
比如我们之前搭建从库55，从库来访问我们数据库51的数据时，都得连上我们51，这样打这条命令就能显示出来。
###############################################################################
缓存参数控制

缓冲区、线程数量、开表数量

选项 含义
key_buffer_size 用于MyISAM引擎的关键索引缓存大小
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间
read_buffer_size 为顺序读取表记录保留的缓存大小
thread_cache_size 允许保存在缓存中被重用的线程数量
table_open_cache 为所有线程缓存的打开的表的数量。内存里的打开表的个数
##############################################################################
key_buffer_size 用于MyISAM引擎的关键索引缓存大小

当Key_reads和Key_read_requests这两个的值较低时，可适当加它的值。

MariaDB [(none)]> show global status like "key_read%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 0 |
| Key_reads | 0 |
+-------------------+-------+

MariaDB [(none)]> show variables like "key_buffer_size";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+

mysql> show variables like"%buffer%"; 查看有关缓存的变量，单位字节

+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 | 单位是字节
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 | 索引缓存大小。拿出多少内存存放索引信息。单位字节
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+

# echo $[8388608/1024] 字节除以1024=k
8192 算得出8192K
# echo $[8192/1024] K除以1024=M
8 算得出8M(兆)

key_buffer_size 的值是8388608字节，说明索引缓存空间的大小默认是8兆。8388608除以2次1024=8M。
可调为16，24M。这个变量是为了加快你的查询速度的。
####################################################################
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间。增大此值可提高order和group的速度

MariaDB [(none)]> show variables like "sort_buffer_size";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| sort_buffer_size | 2097152 |
+------------------+---------+

比如我们想要执行下面这条命令时， 按年龄字段排队，降序排队。
MariaDB [(none)]> select user,age from user order by age desc;
MariaDB [(none)]> select user,age from user group by age;

服务器要给我们排个队，这是mysqld的变量sort_buffer_size这个程序来帮我们执行的。
如果我们希望加快处理这个排队查询的速度，我们只要让这个程序拥有更多的硬件资源，它的处理速度就会变快。
想让处理排队的这个SQL命令的程序的速度变快，那就加大这个变量的值。
####################################################################
查看表记录读取缓存
此缓存值影响SQL查询的响应速度

MariaDB [(none)]> show variables like "read_%_size";
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+

read_buffer_size 为顺序读取表记录保留的缓存大小

MariaDB [(none)]> select * from user;
显示user这个表里的所有信息，你怎么存进去的，就怎么显示。这个是myslqd的变量read_buffer_size来响应你的。
假如我们客户端程序员编写的代码里经常有全表查的，那我们就把处理这种全表查询的mysqld的程序这个变量的值调大。加快处理全表查询的速度。
##################################################################
thread_cache_size 允许保存在缓存中被重用的线程数量。

mysql> show variables like"%thread_cache_size%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+

查看可重用线程数
MariaDB [(none)]> show variables like "thread_%_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+

查看当前的线程重用状态
MariaDB [(none)]> show global status like "thread_%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Threads_cached | 9 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------------+-------+

缓存线程的大小的个数，当前这个线程处理完用户的连接请求后，操作系统的内核不会把这个线程给杀死，还会让它活着，因为这个线程是重复使用的。我可以让有多少个这样处理方式的线程呢？默认是几个呢？就是这个变量的值决定的。上面查询到的值是9个，就是这个数据库服务起来后，会默认有9个这样的线程在内存里等着客户来连。等有客户端来访问我们数据库服务器时，我们就直接从内存中已经开着的线程里要一个来响应客户端。等这次连接断开之后，这个线程也不被杀死。只要你客户端同时连接的数没有超过9个，我们就不用创建新的线程来响应你的连接。这9个线程的连接断开之后，我们也不杀死它们。

好处是：我们已经开启了9个线程在内存里等待着客户访问，当有9客户访问时，线程就能马上响应，不用重新创建线程，响应的速度就会很快。

坏处是：如果我们开100个，这样100个客户来就能马上响应，但是，这100个线程都要在内存里呆着，每个线程都要占用内存大小，系统要分配空间给它们，假如1个2兆，100个就200兆。其他服务就不能用这个空间资源。当没有那么多连接的时候，这些空间就浪费了。

所以我们要根据服务器的运行情况来调整它的大小！我们在优化时，其实就是优化它的值！
#########################################################################
table_open_cache 为所有线程缓存的打开的表的数量。内存里的打开表的个数。

正常情况下，我们的表都是以文件的形式存放在硬盘里的。比如我们有一个文件是innodb存储引擎的，文件名叫a，那在数据库目录下对应的库里就有a.frm,a.idb。然后我们也有一个文件是myisam存储引擎的，文件名叫b，那它就有b.frm,b.MYI,b.MYD。我们在数据库中建立的表就是这样以文件的形式存放在硬盘里的。

当客户端连接上数据库访问a表，select * from a;他访问a表的数据就放在我们数据库服务器的硬盘里的a的表文件。处理的过程是，先把这个文件a.idb打开，打开之后调入内存里，然后再编辑内存里的东西。编辑完之后，再定期的把内存里的东西写入硬盘，然后再关闭这个文件。(即：打开，调入内存，关闭)

如果我们把table_open_cache_instances的值设置为1，就是说同一时间内，只有一个表是打开放入内存里的。假如有2个客户端来访问，客户端pc1访问a表，客户端pc2访问b表，那服务器就会同时打开这2个表，然后等客户端访问完之后，应该把它们给关闭了。因为我们之前设置table_open_cache_instances的值是1，所以只能有一个表继续留在内存里。

假设关掉的是b，那就是a表继续留在了内存里打开着。当有客户端pc3也来访问时：

情况1：客户端pc3访问的是a表，他就直接在内存里访问a表了，就不用重复在硬盘里打开表，然后再调入内存这个步骤，他能直接访问这个表的速度就会变快。

情况2：客户端pc3访问的是b表。因为之前b表被关闭了，所以这时候系统要重复，先到硬盘里打开b表，然后再调入内存，写完之后再关闭。假设这个时候，客户端pc4也来访问b表，又要再重复一遍这个过程。

既然经常访问的表就是这2张表，a表和b表。为什么你不把table_open_cache_instances的值设置为2呢？这样2张表被访问完了之后就不用被关闭了。当又有客户端来访问a和b表，就不用再重复去硬盘里打开，再调入内存的操作了。这样响应客户端的连接请求就很快！而且节省资源！因为去硬盘里打开表，再调入内存的操作也是要占用系统资源的！

查看已打开、打开过多少个表
MariaDB [(none)]> show global status like "open%tables";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 19 | 当前在缓存中打开表的数量
| Opened_tables | 1 | mysql服务器启动以来打开的表的数量
+---------------+-------+

查看可缓存多少个打开的表
mysql> show variables like"%table_open_cache";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 2000 | 系统默认的值是2000。
+----------------------------+-------+
########################################################################
假如系统硬件和带宽都没问题，这些参数我们也已经调了，给的值也都比较大，比较合理，可是访问的速度还是慢。
那有可能是SQL查询优化出了问题！查询优化，就是指查询缓存的优化！

mysql数据库服务器的结构有8大组件来组成。这要联系到之前学过的内存，具体如下：

MySQL服务 体系结构
管理工具: 命令
连接池: 空闲进程，内存，CPU三者是否都准备好。(客户端与服务器建立连接后，进入这个界面。MySQL> )
SQL接口: 传递命令给mysqld这个进程。
分析器: 检查客户端输入的命令是否正确。
优化器: 选最佳方案，以最快速度处理命令。
查询缓存: 缓存曾经查找过的数据。存储空间是从真机的物理内存里划分出来的。比如真机内存2G，mysql默认8m，可以自定义。
客户端请求查询一个数据，mysql先到内存里的查询缓存里寻找，如果没找到，就自己去硬盘里找，找到后先发一份给查询缓存，再回复给客户端。
存储引擎: create table db3.t1(.....)engine=innodb;
文件系统（硬盘）。
#################################################################################
查看缓存的大小

MariaDB [(none)]> show variables like "query_cache_%"; 查看缓存的大小
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 | 查询缓存的限制，约等于1M
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 | 默认查询缓存的大小
| query_cache_strip_comments | OFF |
| query_cache_type | OFF | 查询缓存没有开,在关闭的情况下,你设置查询缓存的大小也没用,要打开才行
| query_cache_wlock_invalidate | OFF | 查询缓存写锁无效没有开启。就是查询缓存写锁有效。
+------------------------------+---------+
#########################################################################################
query_cache_type 查询缓存的类型(控制是否开查询缓存)

查询缓存类型的值有0，1，2。(0是关，1和2都是开)。设置成数字时，对应的显示结果：0(OFF),1(ON)和2(DEMAND)。

0(OFF)关闭：不管有没有超出查询缓存的值，都不写入查询缓存。如果我们把数据库服务器的查询缓存给开了，因为缓存服务器默认是用内存来存储数据的，它调用的是服务器的物理内存，那剩下的留给程序员使用的物理内存就小了。工作中，数据库一般都不开查询缓存，用另一台单独的服务器作为查询缓存服务器，可联系到我们之前学过的memcached。程序员会把数据库中经常被访问的数据调入memcached缓存服务器里，缓存服务器默认是用内存来存储数据。比如双11时，程序员会把打折商品的有关数据(如图片，价格，厂家等信息)调到缓存服务器里来。客户端访问时，先访问缓存服务器的内存，如果有数据能查到，就直接回给客户端。

1(ON)：代表你的查询结果，没有超出查询缓存的限制，就都可以放进查询缓存里。query_cache_limit就是查询缓存的限制，上面查出对应的值是1048576，约等于1M。那么当我们select查找的结果小于等于1M的话，那都可以往查询缓存里放。如果大于1M，就不能放入。

2(DEMAND)：代表你的查询结果，没有超出查询缓存的限制，也都可以放进查询缓存里，但是需要你在查询时手动指定一下写入查询缓存！如果你不指定放入查询缓存里，它不会给你存！当sql语句中有SQL_CACHE关键词时才缓存,如：
select SQL_CACHE * from mysql.user;
#########################################################
query_cache_wlock_invalidate 查询缓存写锁无效。wlock中的w代表写，lock代表锁，合在一起就是写锁。

如果query_cache_wlock_invalidate的值是OFF，就是查询缓存写锁有效！这个写锁的设置对myisam存储引擎的表有效。处理查询多的表适合使用myisam存储引擎。对innodb存储引擎的表无效。

好处：让客户端查询到的结果和硬盘里的原表的数据一样！当被查询的表同时有被执行写操作时，数据库服务器不会从查询缓存里找数据来返回给客户端，而是等写操作完成后，重新从数据库里查找再返回给客户端。比如：

客户端1： mysql> select name from db1.user where name="tom" 从db1.user表中找一个名叫tom的数据
客户端2： mysql> update db1.user set name="harry" where name="tom" 把db1.user表中名叫tom的都改为harry
如果客户端2正在执行写操作的时候,客户端3：mysql> select name from db1.user where name="tom"

分析：
客户端1曾经查询db1.user表里的名叫tom的用户，系统已经将这次查询的结果写入了内存中的查询缓存。
然后客户端2正在修改db1库里的user表，想把名叫tom的用户改为harry。
当客户端2正在执行写操作时，也就是他还没改完的时候，客户端3想要查询这个表里的用户名叫tom的数据。
那么系统不会直接从查询缓存里调用！因为现在查询缓存里保存的名字叫tom，但是有客户端2正在改原表，之后将会改为harry。tom就不会存在表里了。所以系统会等修改完成后，才把结果返回给客户端2。这样就避免了查询到的结果和修改后的结果不一致。
当被查询的表，正在被别人改动的时候，要等到修改完成后，才能查询得到！
#########################################################################
查看当前的查询缓存统计

mysql> show global status like "%qcache%"; 查看当前的查询缓存统计
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 | 查询的数据是在查询缓存里找到了多少次。找到一次就+1。再找到一次，又加一次1。
| Qcache_inserts | 0 | 一共在查询缓存里找了多少次，就算没找到也算找了一次。
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

命中率百分比怎么算呢？
数据库接收到查询请求时，先到查循缓存里找，如果找不到，再到硬盘找。
如果Qcache_hits的值是100，Qcache_inserts的值是200。
就说明在查询缓存里一共找过200回，其中有100回是在查询缓存中找到的。
那么100除以200，就是50%，命中率一半。

如果命中率才10%，就说明在查询缓存里找到的次数少，因为查询缓存里没有，所以命中率低。
既然现在查询缓存小，那我们就把查询缓存往大里调！
当前我们的查询缓存命中率为0,是因为我们没有开启查询缓存,所以每次找都不从查询缓存里找,直接到硬盘里找,所以总数和命中率为0。
query_cache_type的值为OFF(查询缓存的类型，这个变量的值，决定了是否开查询缓存)

我们通过看查询缓存的状态，来判断查询缓存设置得是否合理！
##########################################################################
假如我们已经设置了，它的命中率也挺高的，那么就与查询缓存无关了。
说明我们服务器运行时，查询缓存的大小是合理的。速度还慢的话，就跟它没关系了，那就跟程序员有关了。

数据库服务器处理的查询请求是从哪里来的？是客户端访问网站，然后网站去访问数据库服务器。
数据库服务器上存的数据都是网站上的数据。网站上的网页文件都是程序员开发的。
网站服务的数据库目录在/var/www/html，里面有.html和.php的文件。

.php就是用来连接数据库的脚本文件，里面有连接数据库的命令，和查询插入数据的命令，select,insert。
查询时可以执行单表查，多表查，嵌套查。

如果程序员在这个脚本文件里，有大量的多表查和嵌套查！那服务器执行这个查询语句的速度就会变慢！
就会导致网站上等待页面显示结果的时间就会变长，用户的体验度下降会导致来访问的用户越来越少！

所以我们要知道是不是程序员对服务器做访问时，在那个脚本里查询的SQL命令太复杂，导致数据库处理速度慢呢？
这时候就需要用到开启查询日志。
###########################################################################
msyql日志类型

常用日志种类及选项:

类型 用途 配置
错误日志 记录启动/运行/停止过程中的错误消息 log-error[=name]
查询日志 记录客户端连接和查询操作 general-log
general-log-file=
慢查询日志 记录耗时较长或不使用索引的查询操作 slow-query-log
slow-query-log-file=
long-query-time=
-----------------------------------------------------------------------------------------------
错误日志：想记录服务运行过程中产生的错误信息，就启用错误日志
binlog： 想记录除查询之外的命令，就启用binlog日志
中继日志：这是从库上有的日志文件
查询日志：想记录所有的SQL命令操作，就启用查询日志
慢查询日志：记录慢查询的命令。
############################################################################
查询日志

# vim /etc/my.cnf
[mysqld]
general-log 启用查询日志,它和中继日志,其他日志不冲突,可同时写。
... ...

# systemctl restart mysqld

# ls /var/lib/mysql 如不指定存储名称和位置,它默认放在/var/lib/mysql,默认命名方式：主机名.log
client50.log ... 这个就是查询日志，是文本文件，可查看。

# tail -f /var/lib/mysql/client50.log 动态看这个查询日志。命令 tail -f 跟 tailf 一样
显示结果如下：
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument

另一个终端B开50
# systemctl restart mysqld
[root@client50 ~]# mysql -uroot -p123qqq...A
mysql> show databases;

终端A可以动态实时看到终端B的查询命令以及相关信息
# tail -f /var/lib/mysql/client50.log
... ...
2018-11-29T03:35:07.363997Z 4 Query show databases 这里跳出了信息，显示了客户端B刚执行的命令

工作中要不要开这个日志，取决于你想要达到什么样的目的。

坏处：当客户端访问，mysql这个服务既要响应客户端的连接操作，又要执行记录日志的步骤。
还要有存储空间来记录这个信息。因为这个日志也要占用空间。里面记录的命令越多，占用空间就越大。

好处：只要访问数据库，执行的所有命令全都记。假如你想看上午客户端访问数据库时，执行什么样的操作比较多，
访问的都是什么库下的什么表，就只要启用这个日志就OK了。
##################################################################
慢查询日志

优化SQL查询，记录慢查询，可以在配置文件里添加你需要的选项

选项 含义
slow-query-log 启用慢查询
slow-query-log-file 指定慢查询日志文件
long-query-time 超时时间(默认10秒)。如果不指定，默认10才超时。
log-queries-not-using-indexes 记录未使用索引的查询。建索引的目的是为了加快查询速度，如果每次查询都没有用到索引，那就白建了。

# vim /etc/my.cnf
[mysqld]
general-log 启用查询日志， 它和中继日志，其他日志不冲突，可以同时写
slow-query-log 启用慢查询日志，它和中继日志，其他日志不冲突，可以同时写
... ...

# systemctl restart mysqld

# ls /var/lib/mysql 如不指定存储名称和位置,它默认放在/var/lib/mysql,默认命名方式：主机名-slow.log
client50-slow.log ... 这个就是慢查询日志，是文本文件，可查看。

# tail -f client50-slow.log 动态查看慢查询日志
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument

新开一个终端B
# mysql -uroot -p123qqq...A
mysql> select user,host from mysql.user;
+-----------+---------------+
| user | host |
+-----------+---------------+
| u1 | % |
| webadmin2 | 192.168.4.% |
| webadmin | 192.168.4.254 |
| userweb | 192.168.4.57 |
| mysql.sys | localhost |
| root | localhost |
+-----------+---------------+
6 rows in set (0.00 sec) 查询时间只用了0秒，小于10秒，不记入慢查询日志

mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
| 0 |
+-----------+
1 row in set (11.00 sec) 查询时间用了11秒，大于10秒，记录入慢查询日志

终端A可以动态实时看到终端B超过了10秒的慢查询
# Query_time: 11.000498 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1543463165;
select sleep(11); 这里跳出了新信息，显示了客户端B刚执行的命令select sleep(11);

终端B开50
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.00 sec) 查询时间只用了3秒，小于10秒，不记录入慢查询日志

mysql> select sleep(15);
+-----------+
| sleep(15) |
+-----------+
| 0 |
+-----------+
1 row in set (15.00 sec) 查询时间用了15秒，大于10秒，记录入慢查询日志

# tail -f client50-slow.log
/usr/sbin/mysqld, Version: 5.7.17-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2018-11-29T03:46:05.525211Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 11.000498 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1543463165;
select sleep(11);
# Time: 2018-11-29T03:48:32.878332Z 这里跳出了新信息，显示了客户端B刚执行新的慢查询命令的时间
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 15.000382 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1543463312;
select sleep(15); 这里跳出了新信息，显示了客户端B刚执行的命令select sleep(15);
###################################################################
查看慢查询日志，使用mysqldumpslow工具

# mysqldumpslow /var/lib/mysql/client50-slow.log 专门统计慢查询记录的命令

Reading mysql slow query log from /var/lib/mysql/client50-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
# Time: N-N-29T03:N:N.525211Z
# User@Host: root[root] @ localhost [] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
SET timestamp=N;
select sleep(N)

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
Time: N-N-29T03:N:N.878332Z
# User@Host: root[root] @ localhost [] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
SET timestamp=N;
select sleep(N)

##################################################################
将慢查询的所有命令导入文件/tmp/sql.txt，方便我们查看

# mysqldumpslow client50-slow.log > /tmp/sql.txt

# cat /tmp/sql.txt
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
# Time: N-N-29T03:N:N.525211Z
# User@Host: root[root] @ localhost [] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
SET timestamp=N;
select sleep(N)

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
Time: N-N-29T03:N:N.878332Z
# User@Host: root[root] @ localhost [] Id: N
# Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
SET timestamp=N;
select sleep(N)
##########################################################################
调整网络架构 (网络结构不合理时)

如果我们开启了慢查询日志，但是里面啥都没记录。那有可能是公司的网络结构不合理。
说明有数据传输的瓶颈，就是有节点。之前我们做过分库分表，还做过数据的读写分离，
如果我们的分片服务器和数据的读写分离服务器都只有一台，当访问量大的时候，
分片服务器就会达到瓶颈，它要处理很多的访问，然后开始分片，1万并发和10万并发处理的速度当然不一样。
这就是数据传输的瓶颈，这时候就可以再来一台做负载均衡。多台一起提供数据分片的服务，就能解决这个问题。
如果我们网络中就是存在数据传输的瓶颈，那么我们再怎么优化都是没有用的。所以网络架构必须合理。
##############################################################################
调优思路总结

手段 具体操作
升级硬件 CPU/内存/硬盘
加大网络带宽 付费加大带宽
调整mysql服务运行参数 并发连接数，连接超时时间，重复使用的线程数...
调整与查询相关的参数 查询缓存、索引缓存...
启用慢查询日志 slow-query-log
网络架构不合理 调整网络架构

 

转载于:https://www.cnblogs.com/summer2/p/10798367.html