MySQL 최적화를 위한 캐시 최적화에 대한 자세한 설명(1)

가장 많이 묻는 질문은 MySQL 데이터베이스 성능 최적화에 관한 것이므로 최근에는 MySQL 데이터베이스 성능 최적화에 대한 주제를 쓸 계획입니다. MySQL 성능 최적화에 관심이 있는 중급 MySQL DBA 및 기타 친구들에게 도움이 되기를 바랍니다.

블로거가 내 글에 표시를 해서 기쁘네요. 마크를 알게 된 후에는 거의 돌아오지 않고 계속해서 관심을 기울이고 있어요. 그러나 블로거가 블로그를 클릭하면 이 블로그가 가치 있고 자신의 지식 부족을 보완할 수 있다고 느끼는 측면에서 알 수 있습니다. 블로그에서 가장 중요한 것은 자신에게 도움이 된다는 것입니다. 다른 사람에게도 도움이 된다면, 그것이 최고의 결과입니다. 제가 블로그를 고집하는 목적은 지식 포인트를 잊어버렸을 때 가능한 한 빨리 믿을 수 있는 해결책을 찾을 수 있도록 하는 것입니다. 요약된 지식을 기억하면 더 천천히 잊어버리게 됩니다. 오랜 시간이 지나면 지식의 이 부분이 마침내 자신의 말로 바뀌게 되며, 그러면 더 이상 잊어버리는 것을 두려워하지 않게 됩니다. 이번 블로그에서는 계속해서 캐싱 최적화에 대해 이야기해보겠습니다.

먼저 mysql 버전을 살펴보겠습니다. 제 맥에 설치된 버전은 5.7인데 내용이 많이 바뀌었습니다. 여기서 이야기하는 것은 주로 버전 5.6입니다.

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

1. MySQL 캐시 분류

MySQL 최적화는 인터뷰 중에 매우 큰 시스템을 의미한다고 합니다. 이런 종류의 최적화는 SQL 문 최적화 측면에서도 효과가 있지만 논리적 측면에서 최적화됩니다. 하지만 모든 논리 수준을 최적화할 수 없고 모든 인덱스가 추가되었으며 테이블 구조가 합리적으로 설계되어 있는데 왜 높은 동시성이 발생해도 MySQL이 이를 처리할 수 없는 이유는 무엇입니까? 물론, MySQL에 대한 부담은 다른 측면을 통해 완화될 수 있습니다. 여기서는 이에 대해 당분간 논의하지 않겠습니다. MySQL의 경우 모든 컴퓨팅 리소스가 낭비되지 않고 서비스를 제공할 수 있도록 머신의 성능을 최대한 끌어내야 합니다. MySQL은 서버, 특히 Linux 서버에서 실행됩니다. 그러면 서버의 하드디스크, CPU, 메모리, 네트워크 등이 모두 MySQL의 성능에 영향을 미칩니다. MySQL은 매우 많은 메모리를 소비합니다. 온라인 서버의 MySQL 메모리는 약 80%를 소비합니다. 메모리가 너무 작으면 실제로 다른 최적화를 위한 공간이 거의 없습니다.

또한 연결도 MySQL 성능에 영향을 미치는 중요한 측면입니다. MySQL 클라이언트와 MySQL 서버 간의 연결은 MySQL 클라이언트와 MySQL 서버 간의 반복적인 핸드셰이크의 결과입니다. 각 '핸드셰이크'는 신원 확인, 권한 확인 등을 거칩니다. 핸드셰이크에는 특정 네트워크 리소스와 MySQL 서버 메모리 리소스가 필요합니다.

잠금 경쟁을 언급해야 합니다. 동시성 성능 요구 사항이 상대적으로 높은 데이터베이스의 경우 치열한 잠금 경쟁이 발생하면 데이터베이스 성능이 큰 타격을 받게 됩니다. 잠금 경합은 예상 수요와 관계없이 스레드 컨텍스트 전환의 오버헤드를 크게 증가시킵니다.

2. 상태 표시 및 변수 표시

MySQL 시리즈의 처음 몇 블로그에서는 이러한 명령을 자주 볼 수 있으므로 이 두 가지를 살펴보겠습니다. 이 명령은 MySQL 시스템 관리자에게 어떤 정보를 표시합니까?

show status

MySQL 서비스가 실행 중일 때 MySQL의 상태 서비스 인스턴스 정보는 동적입니다. 현재 MySQL 서버 연결의 세션 상태 변수 정보를 표시하려면 이 명령을 사용하세요. 기본적으로 변수 이름 의 첫 글자는 대문자로 표시됩니다.

변수 표시

변수 표시는 MySQL 서비스 인스턴스의 다양한 시스템 변수를 표시하는 데 사용됩니다(예: 전역 시스템 변수, 세션 시스템 변수, 정적 변수), 이러한 변수에는 MySQL 컴파일 타임 매개변수의 기본값 또는 my.cnf에 설정된 매개변수 값이 포함됩니다. 시스템 변수나 매개변수는 정적 개념입니다. 기본적으로 시스템 변수 이름은 모두 소문자입니다.

MySQL 명령 show status 또는 show session status를 사용하면 현재 MySQL 서버 연결의 세션 변수 정보를 볼 수 있습니다. 세션 상태의 변수 값은 관련되어 있습니다. 현재 MySQL 클라이언트에 유효합니다(예: Opened_tables, Opened_table_definitions 상태 변수).

캐싱 메커니즘

缓存之所以有效，主要是因为程序运行时对内存或者外存的访问呈现局部性特征，局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据（时间局部性）以及未来即将访问到的数据（空间局部性）保存到缓存中，甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

三、MySQL 超时

在使用MySQL的过程中，可能会出现各种超时（timeout）异常，典型的有连接超时、锁等待等。

查看超时时间的类型有哪些：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name        | Value  |
+-----------------------------+----------+
| connect_timeout       | 10    |
| delayed_insert_timeout   | 300   |
| 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      | 3600   |
| wait_timeout        | 28800  |
+-----------------------------+----------+

1、连接超时（connect_timeout）

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果，并且是多次握手的结果，每次握手，除了验证账户名和身份信息外，还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障，可以通过connect_timeout参数来设置，防止它们之间重复握手。

interactive_timeout指的是交互式的终端，在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端，比如PHP实例化的Mysql连接，一直占用着，超过了这个参数设置的值，就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集，MySQL客户机在该值设置的时间内不能接受完毕，则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据，在设置值内不能读取完毕，则会自动断开连接。

InnoDB锁等待超时

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50  |
+--------------------------+-------+

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值，当出现锁等待的时候，等待时长超过该值会导致锁等待的SQL回滚（不是整个事务回滚）。如果希望整个事务回滚，需要开启innodb_rollback_on_timeout参数。

mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF  |
| innodb_rollback_segments  | 128  |
+----------------------------+-------+

innodb_rollback_on_timeout设置为true 后，遇到事务超时，会回滚整个事务的操作。

复制连接超时

当主从配置是，从服务器（slave）从主服务器（master）读取二进制日志失败后，从服务器会等待 slave_net_timeout 后，从新从master机拉去二进制日志。可以设置成10s.

mysql> show variables like 'slave_net_timeout';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| slave_net_timeout | 3600 |
+-------------------+-------+

这部分总结，应该是周日晚上就该整理好的，结果拖到了今天。后面的计划又要后延了，拖延症真严重。

위 내용은 MySQL 최적화를 위한 캐시 최적화에 대한 자세한 설명(1)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!