mysql의 성능을 최적화하는 방법은 무엇입니까? 성능 최적화를 달성하기 위한 실용적인 방법(초보자 참고용)

MYSQL은 아마도 가장 인기 있는 웹 백엔드 데이터베이스일 것입니다. 최근에는 웹 개발 언어가 급속히 발전하고 있습니다. PHP, Ruby, Python, Java는 각각 고유한 특성을 가지고 있습니다. 최근 NOSQL이 점점 더 많이 언급되고 있지만, 대부분의 설계자는 여전히 데이터 저장을 위해 MYSQL을 선택할 것이라고 믿습니다. 그렇다면 mysql은 어떻게 성능 최적화를 달성합니까? 다음 기사에서는 MySQL 성능 최적화를 위한 몇 가지 유용한 방법을 소개합니다. 이것이 모든 사람에게 도움이 되기를 바랍니다. .

PHP 중국어 웹사이트의 mysql 튜토리얼을 참조하세요: "6일 동안 MySQL 비디오 튜토리얼을 안내해 드립니다"

Mysql의 성능 최적화를 위한 실용적인 방법:

Improve 디스크 읽기 및 쓰기 속도

RAID0 특히 EC2와 같은 가상디스크(EBS)를 사용할 때에는 소프트 RAID0을 사용하는 것이 매우 중요합니다.

NOSQL 방식으로 MYSQL 사용

B-TREE는 여전히 가장 효율적인 인덱스 중 하나이며 모든 MYSQL은 여전히 ​​미래에도 사용할 수 있습니다.

HandlerSocket을 사용하여 MYSQL의 SQL 구문 분석 계층을 건너뛰면 MYSQL이 실제로 NOSQL이 됩니다.

디스크 쓰기 작업 줄이기

1 충분히 큰 쓰기 캐시 innodb_log_file_size를 사용하세요

하지만 1G innodb_log_file_size를 사용하는 경우 서버가 충돌하면 복구하는 데 10분 정도 걸립니다.

innodb_log_file_size를 0.25 * innodb_buffer_pool_size로 설정하는 것이 좋습니다.

innod b_flush_log_at_trx_commit = 0/2 쓰기 애플리케이션에 높은 보안(금융 시스템)이 포함되지 않거나 인프라가 충분히 안전하거나 트랜잭션이 매우 작은 경우 0 또는 2를 사용하여 디스크 작업을 줄일 수 있습니다.

3 이중 쓰기 버퍼링 방지

innodb_flush_method=O_DIRECT

적절한 스토리지 엔진 선택: InnoDB

데이터 테이블이 읽기 전용 또는 전체 텍스트 검색에 사용되지 않는 한(전체 텍스트 검색의 경우에는 , 아무도 MYSQL을 사용하지 않을 것입니다.) 기본적으로 InnoDB를 선택해야 합니다.

자체 테스트 중에 MyISAM이 InnoDB보다 빠르다는 것을 알 수 있습니다. 그 이유는 MyISAM이 인덱스만 캐시하는 반면 MyISAM은 데이터와 인덱스를 캐시하지 않기 때문입니다. 하지만 innodb_flush_log_at_trx_commit = 2을 사용하면 가까운 읽기 성능을 얻을 수 있습니다(100배 차이). 기존 MyISAM 데이터베이스를 InnoDB로 변환하는 방법:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

각 테이블에 대한 InnoDB 파일 생성:

innodb_file_per_table=1

이렇게 하면 ibdata1 파일이 너무 커지거나 제어할 수 없게 되는 것을 방지할 수 있습니다. 특히 mysqlcheck -o –all-databases를 실행할 때.

메모리에서 데이터를 읽고 메모리에 데이터를 저장하는 것을 보장합니다.

충분히 큰 innodb_buffer_pool_size

데이터를 완전히 innodb_buffer_pool_size에 저장하는 것이 좋습니다. 즉, 저장량에 따라 innodb_buffer_pool_size의 용량을 계획하는 것이 좋습니다. 이렇게 하면 메모리에서 데이터 전체를 읽을 수 있어 디스크 작업이 최소화됩니다. innodb_buffer_pool_size가 충분히 크고 하드 디스크 대신 메모리에서 데이터를 읽는지 확인하는 방법은 무엇입니까? 방법 1

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 129037 |
| Innodb_buffer_pool_pages_dirty   | 362    |
| Innodb_buffer_pool_pages_flushed | 9998   |
| Innodb_buffer_pool_pages_free    | 0      |  !!!!!!!!
| Innodb_buffer_pool_pages_misc    | 2035   |
| Innodb_buffer_pool_pages_total   | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

Innodb_buffer_pool_pages_free가 0인 경우, 이는 버퍼 풀이 모두 사용되었음을 의미하며 innodb_buffer_pool_size

InnoDB의 다른 여러 매개변수:

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

방법 2

또는 iostat를 사용해야 함을 의미합니다. d -x -k 1 하드 디스크의 작동을 보는 명령입니다.

서버에 계획을 위한 메모리가 충분한지

echo 1 > /proc/sys/vm/drop_caches를 실행하여 운영 체제의 파일 캐시를 지우면 실제 메모리 사용량을 볼 수 있습니다.

데이터 준비

기본적으로 데이터 조각만 한 번 읽은 후 innodb_buffer_pool에 캐시됩니다. 따라서 데이터베이스가 방금 시작되었으며 데이터를 준비하고 디스크의 모든 데이터를 메모리에 캐시해야 합니다. 데이터 준비를 통해 읽기 속도를 높일 수 있습니다.

InnoDB 데이터베이스의 경우 다음 방법을 사용하여 데이터를 준비할 수 있습니다.

1. 다음 스크립트를 MakeSelectQueriesToLoad.sql

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

2로 저장합니다. 매번 또는 백업하기 전에 데이터베이스를 다시 시작합니다. 워밍업 중 실행:

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

SWAP에 데이터 저장을 허용하지 마세요

전용 MYSQL 서버인 경우 SWAP를 비활성화할 수 있습니다. 공유 서버인 경우 innodb_buffer_pool_size가 큰지 확인하세요. 충분한. 또는 캐싱을 위해 고정된 메모리 공간을 사용하고 memlock 명령어를 사용하세요.

정기적으로 데이터베이스를 최적화하고 재구축하세요

mysqlcheck -o –all-databases를 사용하면 ibdata1이 계속해서 성장할 수 있습니다. 실제 최적화는 데이터 테이블 구조만 재구축할 수 있습니다.

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

充分使用索引

查看现有表结构和索引

SHOW CREATE TABLE db1.tb1/G

添加必要的索引

索引是提高查询速度的唯一方法，比如搜索引擎用的倒排索引是一样的原理。

索引的添加需要根据查询来确定，比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。

ADD UNIQUE INDEX
ADD INDEX

比如，优化用户验证表：

添加索引

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

每次重启服务器进行数据预热

echo “select username,password from users;” > /var/lib/mysql/upcache.sql

添加启动脚本到 my.cnf

[mysqld]
init-file=/var/lib/mysql/upcache.sql

使用自动加索引的框架或者自动拆分表结构的框架

比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以，经验不太丰富的人一开始就追求从 0 开始构建，实际是不好的做法。

分析查询日志和慢查询日志

记录所有查询，这在用 ORM 系统或者生成查询语句的系统很有用。

log=/var/log/mysql.log

注意不要在生产环境用，否则会占满你的磁盘空间。

记录执行时间超过 1 秒的查询：

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

相关推荐：

1. MySQL最新手册教程

2. MySQL 5.1参考手册