MySQL 데이터베이스 최적화에 대한 자세한 설명

성숙한 데이터베이스 아키텍처는 처음부터 고가용성, 고확장성 등의 기능을 갖추도록 설계되지 않습니다. , 인프라가 점차 개선되었습니다. 이번 블로그 포스팅은 주로 MySQL 데이터베이스의 개발 주기에서 직면하는 문제와 최적화 솔루션에 대해 이야기하고 있습니다. 당분간은 프론트엔드 애플리케이션을 제쳐두고 대략적으로 다음 5단계로 나누어집니다.

1 , 데이터베이스 테이블 디자인

프로젝트가 승인된 후 개발 부서에서는 제품 부서의 요구에 따라 프로젝트를 개발합니다. 테이블 구조를 디자인하는 것입니다. 데이터베이스의 경우 이는 매우 중요합니다. 제대로 설계되지 않으면 액세스 속도와 사용자 경험에 직접적인 영향을 미칩니다. 느린 쿼리, 비효율적인 쿼리 문, 부적절한 인덱싱, 데이터베이스 정체(교착 상태) 등 많은 영향 요인이 있습니다. 물론 스트레스 테스트를 수행하고 버그를 찾아내는 테스트 엔지니어 팀이 있습니다. 테스트 엔지니어가 없는 팀의 경우 대부분의 개발 엔지니어는 초기 단계에서 데이터베이스 설계가 합리적인지 여부에 대해 크게 생각하지 않지만 프로젝트가 일정량 방문하면 최대한 빨리 기능 구현 및 제공을 완료합니다. 숨겨진 문제가 노출되면 다시 수정하기가 쉽지 않습니다.

2. 데이터베이스 배포

프로젝트 초기에는 운영 및 유지 보수 엔지니어가 등장하지 않습니다. 매우 크기 때문에 단일 배포로 QPS(초당 쿼리 속도) 약 1500을 처리할 수 있습니다. 고가용성을 고려하면 MySQL 마스터-슬레이브 복제 + Keepalived를 더블클릭 핫 백업에 사용할 수 있습니다. 일반적인 클러스터 소프트웨어에는 Keepalived 및 Heartbeat가 포함됩니다.

이중 머신 상시 대기 블로그: //m.sbmmt.com/

3. 데이터베이스 성능 최적화

MySQL을 일반 환경에 배포하는 경우 동시 연결이 발생하면 데이터베이스 처리 성능이 저하되고 하드웨어 리소스는 여전히 풍부합니다. 이때는 소프트웨어 문제를 고려해야 할 때입니다. 그렇다면 데이터베이스의 성능을 극대화하는 방법은 무엇입니까? 한편으로는 단일 서버에서 여러 MySQL 인스턴스를 실행하여 서버 성능을 극대화할 수 있습니다. 반면에 운영 체제와 데이터베이스의 기본 구성은 상대적으로 보수적이므로 특정 제한 사항이 적용됩니다. 이러한 구성은 가능한 한 많은 연결을 처리하도록 적절하게 조정될 수 있습니다.

구체적인 최적화에는 다음과 같은 세 가지 수준이 있습니다.

3.1 데이터베이스 구성 최적화

MySQL에서 일반적으로 사용되는 두 가지 스토리지 엔진이 있는데 그 중 하나가 MyISAM입니다. , 이는 트랜잭션 처리, 빠른 읽기 성능 및 테이블 수준 잠금을 지원하지 않습니다. 다른 하나는 트랜잭션 처리(ACID)를 지원하는 InnoDB입니다. 설계 목표는 대용량 데이터 처리를 위한 성능 및 행 수준 잠금을 극대화하는 것입니다.

• 테이블 잠금: 낮은 오버헤드, 큰 잠금 세분성, 높은 교착 상태 가능성, 상대적으로 낮은 동시성.

• 행 잠금: 높은 오버헤드, 작은 잠금 세분성, 낮은 교착 상태 가능성, 상대적으로 높은 동시성.

테이블 잠금과 행 잠금은 왜 발생하나요? 주로 데이터의 무결성을 보장하기 위한 것입니다. 예를 들어, 한 사용자가 테이블을 운영하고 있고 다른 사용자도 테이블을 운영하려는 경우 다른 사용자가 테이블 잠금 및 행을 운영하기 전에 첫 번째 사용자가 작업을 완료할 때까지 기다려야 합니다. 그것이 바로 자물쇠의 역할입니다. 그렇지 않고 여러 사용자가 동시에 테이블을 운영하게 되면 데이터 충돌이나 예외가 반드시 발생하게 됩니다.

위 내용을 토대로 보면 InnoDB 스토리지 엔진을 사용하는 것이 최선의 선택이며, MySQL 5.5 이상 버전에서는 기본 스토리지 엔진이기도 하다. 각 스토리지 엔진과 관련된 많은 매개변수가 아래에 나열되어 있습니다.

공개 매개변수 기본값:

max_connections = 151
#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右
sort_buffer_size = 2M
#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M
query_cache_limit = 1M
#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖
query_cache_size = 16M
#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值
open_files_limit = 1024
#打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死

MyISAM 매개변수 기본값:

key_buffer_size = 16M
#索引缓存区大小，一般设置物理内存的30-40%
read_buffer_size = 128K
#读操作缓冲区大小，推荐设置16M或32M

InnoDB 매개변수 기본값:

innodb_buffer_pool_size = 128M
#索引和数据缓冲区大小，一般设置物理内存的60%-70%
innodb_buffer_pool_instances = 1
#缓冲池实例个数，推荐设置4个或8个
innodb_flush_log_at_trx_commit = 1
#关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。
innodb_file_per_table = OFF
#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
innodb_log_buffer_size = 8M
#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

3.2 시스템 커널 최적화

대부분의 MySQL은 Linux 시스템에 배포되므로 운영 체제의 일부 매개변수도 MySQL 성능에 영향을 미칩니다. 다음은 Linux 커널의 적절한 최적화입니다.

net.ipv4.tcp_fin_timeout = 30
#TIME_WAIT超时时间，默认是60s
net.ipv4.tcp_tw_reuse = 1
#1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭
net.ipv4.tcp_tw_recycle = 1
#1表示开启TIME_WAIT socket快速回收，0表示关闭
net.ipv4.tcp_max_tw_buckets = 4096
#系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息
net.ipv4.tcp_max_syn_backlog = 4096
#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接

Linux 시스템에서는 프로세스가 연 파일 핸들 수가 시스템 기본값인 1024를 초과하는 경우 "너무 많은 파일이 열려 있습니다"라는 메시지가 표시되므로 열린 파일 핸들 제한을 설정해야 합니다. 조정.

3.3 하드웨어 구성

물리적 메모리를 늘리고 파일 시스템 성능을 향상시킵니다. Linux 커널은 핫 데이터를 저장하기 위해 메모리에서 캐시 영역(시스템 캐시 및 데이터 캐시)을 할당합니다. 파일 시스템 지연 쓰기 메커니즘을 통해 조건이 충족되는 경우에만 동기화가 발생합니다(예: 캐시 영역이 특정 비율에 도달하거나 동기화됨). 명령이 실행되고 있음). 즉, 물리적 메모리가 클수록 할당된 캐시 영역도 커지고 캐시된 데이터도 많아집니다. 물론, 서버에 장애가 발생하면 일정량의 캐시된 데이터가 손실됩니다.

SSD 하드 드라이브가 SAS 하드 드라이브를 대체하고, RAID 레벨이 RAID1+0으로 조정되어 RAID1 및 RAID5보다 읽기 및 쓰기 성능(IOPS)이 더 좋습니다. 결국 데이터베이스에 대한 부담은 주로 발생합니다. 디스크 I/O에서.

4. 데이터베이스 아키텍처 확장

비즈니스 규모가 증가함에 따라 단일 데이터베이스 서버의 성능은 더 이상 비즈니스 요구를 충족할 수 없습니다. 머신 추가를 고려할 시간이고 클러스터를 생성할 시간입니다~~. 주요 아이디어는 단일 데이터베이스의 로드를 분해하고, 디스크 I/O 성능을 돌파하고, 핫 데이터를 캐시에 저장하고, 디스크 I/O 액세스 빈도를 줄이는 것입니다.

4.1 마스터-슬레이브 복제 및 읽기-쓰기 분리

프로덕션 환경의 대부분의 데이터베이스는 읽기 작업이므로 단일 마스터-다중 슬레이브 아키텍처를 배포합니다. 마스터 데이터베이스는 쓰기 작업과 핫 더블 클릭을 담당합니다. 백업 및 여러 슬레이브 데이터베이스가 로드 균형을 수행하며 읽기 작업을 담당합니다. 주요 로드 밸런서는 LVS, HAProxy 및 Nginx를 포함합니다. 읽기와 쓰기를 어떻게 분리할 수 있나요? 대부분의 회사에서는 코드 수준에서 읽기와 쓰기를 분리하는 것이 더 효율적입니다. 또 다른 방법은 기업에서 거의 사용되지 않는 프록시 프로그램을 통해 읽기-쓰기 분리를 달성하는 것입니다. 일반적인 프록시 프로그램에는 MySQL Proxy 및 Amoeba가 있습니다. 이러한 데이터베이스 클러스터 아키텍처에서는 데이터베이스의 높은 동시성 기능이 크게 향상되고 단일 시스템 성능 병목 현상 문제가 해결됩니다. 하나의 슬레이브 데이터베이스가 2000 QPS를 처리할 수 있다면 5개의 슬레이브 데이터베이스가 10,000 QPS를 처리할 수 있으며 데이터베이스의 수평 확장성도 매우 쉽습니다.

쓰기 작업이 많은 애플리케이션에 직면할 때 단일 장치의 쓰기 성능이 비즈니스 요구 사항을 충족하지 못하는 경우가 있습니다. 이중 마스터가 있는 경우 데이터베이스 데이터 불일치가 발생합니다. 그 이유는 애플리케이션의 서로 다른 사용자가 두 개의 데이터베이스를 운영할 수 있고 동시에 업데이트 작업으로 인해 두 데이터베이스의 데이터베이스 데이터에 충돌이나 불일치가 발생하기 때문입니다. 단일 데이터베이스를 사용할 때 MySQL은 스토리지 엔진 메커니즘 테이블 잠금 및 행 잠금을 사용하여 데이터 무결성을 보장합니다. 여러 기본 데이터베이스를 사용할 때 이 문제를 해결하는 방법은 무엇입니까? Perl 언어를 기반으로 개발된 MySQL-MMM(Mysql용 마스터-마스터 복제 관리자, Mysql 마스터-마스터 복제 관리자)이라는 마스터-슬레이브 복제 관리 도구 세트가 있습니다. 이 도구의 가장 큰 장점은 단지 제공한다는 것입니다. 동시에 하나의 데이터베이스 쓰기 작업을 효과적으로 수행하여 데이터 일관성을 보장합니다.

4.2 캐시 추가

데이터베이스에 캐시 시스템 추가, 캐시 핫 데이터 to In memory, 메모리 캐시에 요청해야 할 데이터가 있는 경우, 읽기 성능 향상을 위해 더 이상 결과가 데이터베이스로 반환되지 않습니다. 캐시 구현에는 로컬 캐시와 분산 캐시가 포함되며, 이는 속도가 빠릅니다. 분산형은 대용량 데이터를 캐시할 수 있으며 확장이 쉽습니다. Memcached와 redis가 포함된 주요 분산형 캐싱 시스템은 안정적인 성능을 제공합니다. 데이터가 메모리에 캐시됩니다. 약 8w에 도달합니다. 데이터 지속성을 원한다면 redis를 사용하세요. 성능은 memcached보다 낮지 않습니다.

작업 프로세스:

4.3 하위 라이브러리

하위 라이브러리는 다양한 기반으로 구성됩니다. 테이블은 웹, bbs, 블로그 및 기타 라이브러리와 같은 다양한 데이터베이스로 구분됩니다. 비즈니스 규모가 큰 경우 분할된 라이브러리를 마스터-슬레이브 아키텍처로 사용하여 단일 라이브러리에 대한 과도한 부담을 더욱 피할 수도 있습니다.

4.4 테이블

데이터베이스의 특정 테이블에 수백만 개의 데이터가 있어 쿼리 및 삽입에 시간이 너무 오래 걸립니다. 단일 미터의 압력을 해결하는 것은 어떻습니까? 단일 테이블에 대한 부담을 줄이고 처리 효율성을 높이기 위해 이 테이블을 여러 개의 작은 테이블로 분할할지 여부를 고려해야 합니다. 이 방법을 테이블 분할이라고 합니다.

테이블 분할 기술은 프로그램 코드에서 SQL 문을 수정하고 다른 테이블을 수동으로 생성해야 하며 테이블 분할을 구현하는 방법도 비교적 간단합니다. 테이블이 분할된 후 프로그램은 마스터 테이블에서 작동하며, 이 마스터 테이블은 하위 테이블 간의 일부 관계와 데이터를 업데이트하는 방법만 가지고 있습니다. 다양한 쿼리를 기반으로 하므로 동시성과 디스크 I/O 성능이 향상됩니다.

테이블 분할은 세로 분할과 가로 분할로 구분됩니다.

세로 분할: 필드가 많은 원본 테이블을 여러 테이블로 분할하여 테이블 너비 문제를 해결합니다. 자주 사용하지 않는 필드를 별도의 테이블에 배치하거나, ​​큰 필드를 별도의 테이블에 배치하거나, ​​밀접하게 관련된 필드를 테이블에 배치할 수 있습니다.

수평 분할: 원본 테이블을 여러 개의 테이블로 분할합니다. 각 테이블은 동일한 구조를 가지고 있어 단일 테이블에서 대용량 데이터 문제를 해결합니다.

4.5 파티션

파티션은 테이블의 데이터를 여러 블록으로 나누는 것입니다. 이러한 블록은 파티셔닝 후 하나의 디스크에 있을 수도 있고 다른 디스크에 있을 수도 있습니다. 표면적으로는 여전히 하나의 테이블이지만 데이터는 여러 위치에서 해시됩니다. 이러한 방식으로 여러 하드 디스크가 동시에 서로 다른 요청을 처리할 수 있으므로 디스크 I/O 읽기 및 쓰기 성능이 향상되고 구현이 비교적 간단해집니다. .

참고: 캐시, 하위 라이브러리, 하위 테이블 및 파티션 추가는 주로 프로그래머가 구현합니다.

5. 데이터베이스 유지 관리

데이터베이스 유지 관리는 성능 모니터링, 성능 분석, 성능 등을 포함하는 운영 및 유지 관리 엔지니어 또는 DBA의 주요 업무입니다. 튜닝, 데이터베이스 백업 및 복구 등

5.1 성능 상태의 주요 지표

QPS, 초당 쿼리 수: 초당 쿼리 수, 데이터베이스가 초당 처리할 수 있는 쿼리 수

TPS, 초당 트랜잭션 수: 초당 처리되는 트랜잭션 수

상태 표시를 통해 실행 상태를 확인하세요. 300개 이상의 상태 정보 레코드가 있습니다. 다음과 같이 QPS와 TPS를 계산합니다.

가동 시간: 서버가 실행된 실제 횟수(초)

질문: 데이터베이스로 전송된 쿼리 수

Com_select: 쿼리 횟수, 실제 데이터베이스 작업 횟수

Com_insert: 삽입 횟수

Com_delete: 삭제 횟수

Com_update: 삽입 횟수 업데이트

Com_commit: 트랜잭션 수

Com_rollback: 롤백 수

그런 다음 계산 방법은 다음과 같습니다. 질문을 기반으로 QPS를 계산합니다.

mysql> show global status like 'Questions';
mysql> show global status like 'Uptime';

QPS = Questions / Uptime

基于Com_commit和Com_rollback计算出TPS：

mysql> show global status like 'Com_commit';
mysql> show global status like 'Com_rollback';
mysql> show global status like 'Uptime';
TPS = (Com_commit + Com_rollback) / Uptime

另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS

TPS计算方法：

mysql> show global status where Variable_name in('com_insert','com_delete','com_update');

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。

经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。

5.2 开启慢查询日志

MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

mysql> set global slow-query-log=on #开启慢查询功能
mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; #指定慢查询日志文件位置
mysql> set global log_queries_not_using_indexes=on; #记录没有使用索引的查询
mysql> set global long_query_time=1; #只记录处理时间1s以上的慢查询

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。

# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #查看最慢的前三个查询

也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。

分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log

分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql

pt-query-digest –type=binlog mysql-bin.000001.sql

分析普通日志：pt-query-digest –type=genlog localhost.log

5.3 数据库备份

备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。

Xtrabackup备份工具使用博文：//m.sbmmt.com/

5.4 数据库修复

有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。

myisamchk: myisam 테이블만 복구할 수 있으며 데이터베이스를 중지해야 합니다.

공통 매개변수:

-f –force 강제 복구, 오래된 임시 파일 덮어쓰기, 일반적으로 사용하지 않음

🎜>

5.5 추가적으로 CPU 및 I/O 성능 방법 확인

#%util: 100%에 도달하면 I/O가 매우 바쁘다는 의미입니다.

#await: 요청이 대기열에서 기다리는 시간은 읽기 시간에 직접적인 영향을 미칩니다.

I/O 제한: IOPS(r/s+w/s), 일반적으로 약 1200입니다. (IOPS, 초당 읽기 및 쓰기(I/O) 작업 수)

I/O 대역폭: 순차 읽기 및 쓰기 모드에서 SAS 하드디스크의 이론값은 약 300M/s이며, SSD 하드 디스크의 이론적인 값은 약 600M/s입니다.

위 내용은 MySQL을 3년 동안 사용해 본 후 요약한 주요 최적화 솔루션 중 일부입니다. 기능이 제한적이고 일부는 포괄적이지 않지만 기본적으로 중소기업의 데이터베이스 요구 사항을 충족할 수 있습니다. . 관계형 데이터베이스의 원래 설계의 한계로 인해 일부 BAT 회사는 막대한 양의 데이터를 관계형 데이터베이스에 넣었으며 대규모 데이터 쿼리 및 분석 측면에서 더 나은 성능을 달성하지 못했습니다. 따라서 NoSQL이 대중화되었으며, 비관계형 데이터베이스는 관계형 데이터베이스의 일부 단점을 보완하기도 합니다. 분산 파일 시스템은 HDFS, GFS 등과 같은 데이터 저장에 사용됩니다. 대규모 데이터 계산 및 분석에는 Hadoop, Spark, Storm 등을 사용합니다. 이것들은 운영과 유지보수에 관련된 최첨단 기술이며, 스토리지의 주요 학습 대상이기도 합니다. 더 나은 최적화 계획을 갖고 있는 블로거가 있으면 공유해 주세요.

위 내용은 MySQL 데이터베이스 최적화에 대한 자세한 설명입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(m.sbmmt.com)를 참고해주세요!