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

이 문서에서는 PHP 데이터베이스 프로그래밍을 위한 MySQL 최적화 전략을 간략하게 설명합니다. 참고할 수 있도록 모든 사람과 공유하세요. 자세한 내용은 다음과 같습니다.

저는 며칠 전에 PHP의 병목 현상이 PHP 자체가 아니라 데이터베이스에 있는 경우가 많다는 기사를 봤습니다. PHP 개발에서는 데이터의 추가, 삭제, 수정, 확인이 핵심이라는 것을 우리 모두 알고 있습니다. PHP의 운영 효율성을 높이려면 프로그래머는 명확한 논리와 높은 효율성으로 코드를 작성해야 할 뿐만 아니라 쿼리문을 최적화할 수 있어야 합니다. 데이터베이스의 읽기 및 쓰기 속도에 대해 우리가 할 수 있는 일은 없지만 Memcache, mongodb 및 redis와 같은 일부 데이터베이스 클래스 확장 및 데이터 스토리지 서버의 도움으로 PHP는 더 빠른 액세스 속도를 달성할 수도 있으므로 이해하고 배우십시오. 이러한 확장은 또한 매우 필요합니다. 이 기사에서는 먼저 MySQL의 일반적인 최적화 전략에 대해 설명합니다.

몇 가지 MySQL 팁

1. SQL 문의 키워드는 대문자로 작성하는 것이 가장 좋습니다. 둘째, SQL 문이 실행될 때 키워드와 작업 개체를 구별하기 쉽습니다. 대문자로 변환하면 수동으로 대문자를 쓰면 쿼리 효율성이 약간 높아질 수 있습니다.
2. 데이터베이스에 데이터 행을 추가하거나 삭제하면 데이터 ID가 너무 커집니다. ALTER TABLE 테이블 이름 AUTO_INCREMENT=N을 사용하여 N부터 자동 증가 ID를 계산합니다.
3. int 유형에 ZEROFILL 속성을 추가하면 데이터에 자동으로 0이 추가됩니다.
4. 많은 양의 데이터를 가져올 때는 먼저 인덱스를 삭제하고 데이터를 삽입한 다음 인덱스를 추가하는 것이 좋습니다. 그렇지 않으면 MySQL은 인덱스를 업데이트하는 데 많은 시간을 소비하게 됩니다.
5. 데이터베이스를 생성하고 sql 문을 작성할 때 IDE에서 .sql 접미사를 사용하여 파일을 생성하면 IDE가 sql 구문을 인식하여 작성하기가 더 쉬워집니다. 더 중요한 것은 데이터베이스가 손실된 경우에도 이 파일을 찾고 현재 디렉터리에서 /path/mysql -uusername -ppassword 데이터베이스 이름 < filename.sql을 사용하여 전체 파일의 SQL 문을 실행할 수 있다는 것입니다(참고 - u -p 바로 뒤에 공백 없이 사용자 이름과 비밀번호가 옵니다.

데이터베이스 디자인 최적화

1. 데이터베이스 디자인은 세 번째 패러다임을 따르며, 쿼리의 편의를 위해 특정 데이터 중복이 있을 수 있습니다.

2. 데이터 유형 우선순위를 int > date, time > enum, char > varchar > blob로 선택하세요. 예를 들어 IP 주소를 unsign으로 변환할 수 있습니다. ip2long() 함수를 사용하여 int 형식으로 저장합니다.

3. char(n) 유형의 경우 데이터가 완료되면 n 값을 최대한 작게 유지하십시오.

4. 테이블 생성 시 파티션 명령을 사용하면 쿼리 효율성이 크게 향상됩니다. MySQL은 RANGE, LIST, HASH 및 KEY 파티션 유형을 지원하며, 그 중 RANGE가 가장 일반적으로 사용됩니다. :

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5 . 데이터베이스 엔진을 선택할 때 innodb와 myisam의 차이점에 주의하세요. 저장 구조: MyISAM은 디스크에 세 개의 파일로 저장됩니다. InnoDB의 모든 테이블은 동일한 데이터 파일(일반적으로 2GB)에 저장됩니다. 트랜잭션 지원: MyISAM은 트랜잭션 지원을 제공하지 않습니다. InnoDB는 트랜잭션 지원을 제공합니다.

테이블 잠금 차이점: MyISAM은 테이블 수준 잠금만 지원합니다. InnoDB는 트랜잭션과 행 수준 잠금을 지원합니다.

전체 텍스트 인덱스: MyISAM은 FULLTEXT 유형의 전체 텍스트 인덱스를 지원합니다(중국어에는 적용되지 않으므로 스핑크스 전체 텍스트 인덱스 엔진을 사용해야 함). InnoDB는 이를 지원하지 않습니다.
테이블의 특정 행 수: MyISAM은 테이블의 총 행 수를 저장하며 count(*) 쿼리가 매우 빠릅니다. InnoDB는 테이블의 총 행 수를 저장하지 않으므로 다시 계산해야 합니다.
외래 키: MyISAM에서는 지원되지 않습니다. InnoDB는

인덱스 최적화

1을 지원합니다. Innodb는 인덱스를 저장할 때 기본 키가 있어야 합니다. 지정하지 않으면 엔진이 자동으로 숨겨진 기본 키를 생성하고 기본 인덱스를 생성합니다. 인덱스는 기본키의 물리적 주소로, 데이터는 기본키에 의해 저장됩니다. 인덱스를 사용할 때마다 먼저 기본 인덱스를 찾은 후 기본 인덱스 아래의 데이터를 찾아야 합니다.

기본 키를 통해 검색하는 것이 매우 빠르다는 장점이 있습니다. 단점은 먼저 보조 인덱스를 통해 기본 인덱스를 찾아야 하기 때문에 보조 인덱스가 느려진다는 것입니다(보조 인덱스는 기본 인덱스의 위치입니다). .), 기본 인덱스를 통해 데이터를 찾습니다. 그리고 기본키가 불규칙한 경우 새로운 값을 삽입할 때 더 많은 데이터 블록을 이동해야 하므로 효율성에 영향을 미치므로 기본키로 정기적으로 증가하는 int형을 사용해보세요. 또한 데이터가 기본 키 바로 뒤에 위치하기 때문에 데이터에 특히 많은 양의 데이터가 포함된 열(text/blob)이 있는 경우 InnoDB는 쿼리 중에 많은 데이터 블록을 건너뛰게 되어 속도도 느려지게 됩니다.

2. myisam 인덱스의 각 인덱스는 동일하며 디스크의 각 행 주소를 가리킵니다. 모두 경량 포인터 데이터입니다. 단점은 각 인덱스가 기본 키를 통해 설정되지 않고, 클러스터형 인덱스에서 기본 키를 검색하는 것만큼 쿼리 속도가 빠르지 않다는 점입니다. 하지만 주소를 저장하기 때문에 새 값을 입력하면 비교 양상이 이동하고 변경됩니다.

3. 다중 조건 쿼리를 수행할 때 여러 조건에 대한 인덱스를 별도로 생성하는 경우 SQL 쿼리를 실행할 때 MySQL은 가장 가까운 인덱스만 선택하므로 다중 조건 쿼리가 필요한 경우 결합 인덱스를 사용합니다. 데이터 중복이 발생하더라도 설정해야 합니다.

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type 获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows 查找到数据要扫描的大概行数，可看出索引的优劣
extra 常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

마스터를
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

6으로 변경한 다음 슬레이브 시작 명령을 사용하여 마스터-슬레이브 복제를 시작합니다.

각 구성 수정 후 서버를 다시 시작하는 것을 잊지 마세요. 그런 다음 마스터 및 슬레이브 서버에서 show master/slave status를 사용하여 마스터/슬레이브 상태를 볼 수 있습니다.

데이터베이스에서 읽기와 쓰기 분리를 구현하는 것은 mysql_proxy, atlas 등과 같은 MySQL 미들웨어에 의존합니다. 이러한 미들웨어를 마스터 서버와 슬레이브 서버의 읽기와 쓰기를 분리하도록 구성함으로써 슬레이브 서버가 읽기 책임을 맡게 되므로 마스터 서버의 부담이 줄어듭니다.

데이터베이스 공유

인덱싱이든 캐싱이든 데이터베이스의 데이터 테이블에 있는 데이터의 양이 매우 클 경우 데이터베이스를 샤딩하여 사용할 수 있다는 부담이 큽니다. 여러 데이터베이스 서버 또는 여러 테이블 저장소를 사용하여 쿼리 부담을 줄입니다.

방법에는 수직 분할, 수평 분할, 결합 분할이 있습니다.

수직 분할: 데이터 테이블이 많은 경우 데이터베이스에서 밀접하게 관련된 테이블(예: 동일한 모듈, 자주 연결되고 쿼리됨)을 분할하여 서로 다른 마스터-슬레이브 서버에 배치합니다.

수평 샤딩: 테이블이 많지 않지만 테이블에 포함된 데이터의 양이 매우 많은 경우 쿼리 속도를 높이기 위해 해싱 및 기타 알고리즘을 사용하여 데이터 테이블을 여러 개로 나누어 배치할 수 있습니다. 다른 서버에서 쿼리 속도를 높이세요. 수평 샤딩과 데이터 테이블 파티셔닝의 차이점은 저장 매체의 차이에 있습니다.

조인트 분할: 데이터 테이블과 테이블에 포함된 데이터의 양이 매우 크기 때문에 조인트 분할이 필요한 경우가 많습니다. 즉, 수직 및 수평 테이블 분할이 동시에 수행되고 데이터베이스가 저장하기 위해 분산 행렬로 분할됩니다.

이러한 각 데이터베이스 최적화 방법은 기사를 작성하는 데 사용될 수 있습니다. 이러한 방법을 이해하고 기억하면 높은 데이터베이스 효율성을 달성하기 위해 필요할 때 의도적인 선택과 최적화를 수행할 수 있습니다.

다음으로 일반적으로 사용되는 PHP 데이터베이스 클래스 확장인 memcache, redis 및 mongodb의 기본 사용 시나리오와 사용 방법을 추가로 요약하겠습니다.

위 내용은 PHP의 MySQL 데이터베이스 최적화 전략에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!