mysql 优化的思路:(是关键的灵魂)
优化的目的:、
为了快速响应客户端的响应;
优化手段:
1.硬优化
升级硬件(硬盘 内存 )
2.软优化
优化服务运行的参数
影响数据库服务的性能的因数有那些:
1.网络带宽 解决办法(网络测速软件) 卖带宽
2.服务器的硬件配置低 解决办法 查看硬件的使用率(CPU 内存 存储设备)
3.数据库服务软件版本低,导致性能低下 解决办法 (查看数据库服务,运行时的运行参数(查看变量值))
mysql> show variables\G;
mysql> show variables like "%password%";
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+---------------------------------------+--------+
14 rows in set (0.00 sec)
2.硬件的查看思路:
CPU //查看命令
top //每5秒钟刷新一次
[root@host2 ~]# uptime
10:30:35 up
1:47, 1 user, load average:
0.00, 0.01, 0.05
0.00, 0.01, 0.05//越少越好,占用率越少
内存
[root@host2 ~]# free -m
total used free shared buff/cache available
Mem: 992 320 364 6 307 482
Swap: 2047 0 2047
//要保持有20%的使用的遇留空间,不然访风值就会出问题
Mem //物理内存的使用率
Swap //交换空间的使用率
存储设备:
转速15000转/分钟
固体硬盘
top查看: 0.0 wa, 数值越大说明等待硬盘的处理的时间就越长
云主机:16cpu 128G 1000T
修改永久的密码配置:
vim /etc/my.cnf
[mysqld]
validate_password_length=6
validate_password_policy=0
:wq
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# mysql -u root -p
mysql> show variables like"%password%";
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+---------------------------------------+-------+
14 rows in set (0.01 sec)
临时修改密码规则:
set global
validate_password_policy=2;
mysql> set global validate_password_policy=2;
Query OK, 0 rows affected (0.01 sec)
mysql> set validate_password_policy=2;
ERROR 1229 (HY000): Variable 'validate_password_policy' is a GLOBAL variable and should be set with SET GLOBAL //报错原因是:这个变量是全局变量
设置会影响服务性能参数的值:
1、目的:
通过根据服务器目前状况,修改Mysql的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。
2.并发连接数量 //mysql> set global max_connections = 300;
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 |
+-----------------------------------------------+-----------------+
9 rows in set (0.00 sec)
mysql> show global status ; //查看状态
mysql> show global status like "%conn%"; //查看连接状态
mysql> show global status like "Max_used_connections"; //查看最大的连接的最大数量
曾经有过的最大连接数/并发连接数 = 0.85 * 100%
Max_used_connections/max_connections=0.85
3.连接超时时间
mysql> show variables like "%time%";
| connect_timeout | 10 //客户端与服务器建立连接时,三次握手的超时时间
| interactive_timeout | 28800 | //3.2建立连接后,服务器等待客户输入sql命令的超时时间
connect_timeout这两个的设置的作用:拒绝服务攻击,这个值设置得太长也不好,设置得太短也不好
过大容易:拒绝服务攻击 过短容易:消耗内存资源
interactive_timeout
[root@host53 ~]# echo $[28800/3600]
8
知识补充:
三次握手:是在tcp协议下才可以进行
ss ntulp | grep mysql //半开式扫描 ——就是利用三次握手的原理
4.可以重复使用的线程数量
mysql> show variables like "%thread%";
| thread_cache_size | 9
mysql 默认就会开9个线程,等待客户端的响应,系统不会杀死这些进程,在内存等待客户端进行访问,这样响应的速度就会加快。
如果设置太大,就会占用内存资源,
如果过少 ,就会让客户端的等待时间过长
5.所有mysql程序打开表的数量
mysql> show variables like "%table%";
| table_open_cache | 2000 |
客户端访问数据库,有可能访问同一张表,或者不同的表
如果设置太少,服务器会把表调到内存,(内存的速度,要比硬盘的速度快)
6.缓存的设置
6.1.管理工具:提供服务的软件
软件安装后,提供的管理命令 (软件自带的命令)
连接池:检查服务器是否有资源响应客户端有
是否有空闲的线程,等待连接请求
连接成功后要输入sql命令
sql接口:把sql命令传递给mysql程序
mysql> select * from t1;
分析器:检查sql命令是否正确;(输入命令的报错机制)
优化器: 对用户执行的sql命令进行优化;(自动完成,不需要人为干预)
查询缓存:存储曾经查找过的结果,(存储空间时mysql服务启动时从物理主机的物理内存划分出来)
存储引擎:软件自带的管理工具:表的处理器,不同的存储引擎支持不同的功能和数据存储方式 myisam innodb
文件系统: 数据服务用来存储数据的硬盘
/var/lib/mysql
[root@host2 ~]# df -h
文件系统 容量 已用 可用 已用% 挂载点
/dev/mapper/rhel-root 17G 7.3G 9.8G 43% / //这是一个逻辑卷
[root@host2 ~]# lvscan //查看逻辑卷
ACTIVE '/dev/rhel/swap' [2.00 GiB] inherit
ACTIVE '/dev/rhel/root' [<17.00 GiB] inherit
//所有的数据存储到/var/lib/mysql 实质时存储到 /的逻辑卷中
mysql处理查询的过程:
1.首先要跟服务器建立,连接请求
2.建立连接请求,到库里表里查询数据
3.把查询结果返回给查询缓存空间
4.查询缓存空间返回给客户端
mysql> show variables like "�che%"; //查询缓存的种类
mysql> show variables like "query_cache_%"; //查询缓存的参数
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.01 sec)
query_cache_wlock_invalidate | OFF
当客户端查询mysqlSam 存储引擎的表时,由此时有客户端对表执行写操作的话,数据库不好从查询缓存里查找结果返回给客户端,而是等写操作完成后,从新
从查询缓存里,直接查找结果返回给客户端,如果没有就到库里表里查询结果,再主动的将结果返回给查询缓存,查询缓存再把结果返回给客户端。
如果有客户执行查询操作,数据库就会进行写锁,不能写进数据,等客户执行网查询操作,才能执行写入操作,
这样保证了数据的一致性。
| query_cache_type | OFF | //查询缓存的开关 on 就是相当于0;默认是关的
| query_cache_type 0|1|2
0 禁止使用查询缓存
1 若查询结果没有超过查询缓存大少的设置 ,查询结果会自动存储到查询缓存里
2 明确指定要把查询结果存储
| query_cache_min_res_unit | 4096 //查询缓存的最少存储大少,最少是4k (4096是字节单位)
存储过数据就不能再存储数据,必须等清空才可以再一次存储数据,这样保证数据的一致性
生产环境中:
把访问次数比较多的数据放到缓存服务器里,热度不高的放到数据库里
查询结果的统计信息:
mysql> show global status like "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 | //当前查询结果是查询缓存里找到,此变量值就会自动加1
| Qcache_inserts | 0 | //服务器接收到一次查询请求,此变量就会自动加1
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
| Qcache_hits 这个值越高,表示查询缓存的性能就越高。
锁引缓存:
mysql> mysql> show global status like "%key%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_assign_to_keycache | 0 |
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 6 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6695 |
| Key_blocks_used | 3 |
| Key_read_requests | 6 |
| Key_reads | 3 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+
11 rows in set (0.00 sec)
mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size |
8388608 |
+-----------------+---------+
1 row in set (0.01 sec)
索引缓存默认是8M,设置索引缓存,就会表目录下生成相关的索引文件,存放索引信息(存储的排队信息)
索引缓存就是把排队信息放在内存里,如果没有就放在硬盘里,(这样的查找速度比较慢)
性能优化
mysql> explain select user from mysql.user where user="mysql"\G; \\查看表中的索引信息(也可以查看看表的查看速度)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 276
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
key_buffer-size 用于 MyISAM 引擎的关键索引缓存大小
sort_buffer_size 为每个要排序的线程分配此大小的缓存空间
read_buffer_size 为顺序读取表记录保留的缓存大小
thread_cache_size 允许保存在缓存中被重用的线程数量
table_open_cache 为所有线程缓存的打开的表的数量
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
general-log
:wq
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# ls /var/lib/mysql
host51.log
[root@host51 ~]# vim /var/lib/mysql/host51.log
生产环境中:根据业务需求进行配置
启动慢查询日志:记录超过超时时间显示查询结果的sql命令。
(默认没有启用)
[root@host51 ~]# vim /etc/my.cnf
[mysql]
slow-query-log
:wq
[root@host51 ~]# systemctl restart mysqld
[root@host51 ~]# ls /var/lib/mysql
host51-slow.log
:wq
[root@host51 ~]# vim /var/lib/mysql/host51-slow.log
Time Id Command Argument
模拟一个报错信息
mysql> select
sleep(11),"name"; //模拟报错 //
sleep(11) 等待多长时间再执行命令
+-----------+------+
| sleep(11) | name |
+-----------+------+
| 0 | name |
+-----------+------+
1 row in set (11.00 sec)
[root@host51 ~]# vim /var/lib/mysql/host51-slow.log //查看错误信息
Time Id Command Argument
# Time: 2018-07-23T07:59:10.198806Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 11.000477 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1532332750;
select sleep(11),"name";
mysql> select sleep(3) "abc","bcd";
+-----+-----+
| abc | bcd |
+-----+-----+
| 0 | bcd |
+-----+-----+
1 row in set (3.00 sec)
[root@host51 ~]# vim /var/lib/mysql/host51-slow.log //默认值是是10秒,3秒不会报错
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 11.000477 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1532332750;
select sleep(11),"name"
[root@host51 ~]# mysqldumpslow /var/lib/mysql/host51-slow.log //mysql软件自带命令查看统计结果
Reading mysql slow query log from /var/lib/mysql/host51-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
# Time: N-N-23T07:N:N.198806Z
# 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),"S"
[root@host51 ~]# mysqldumpslow /var/lib/mysql/host51-slow.log > /root/sql.txt
将错误信息统计结果另存为,发给程序员,让程序员修改
数据存储架构不合理:数据传输有瓶颈
查看数据架构;这个就是非常大的问题
总结:mysql性能要结合zabbix一起使用;
env 是linux 的系统表量
variables mysql的数据库表量