Mysql 데이터베이스 성능 최적화에 대해 알고 계시나요?

오늘날 데이터베이스 작업은 전체 애플리케이션, 특히 웹 애플리케이션의 성능 병목 현상을 점점 더 많이 발생시키고 있습니다. 데이터베이스의 성능과 관련하여 이는 DBA가 걱정해야 할 사항일 뿐만 아니라 우리 프로그래머가 주의해야 할 사항이기도 합니다

오늘날 데이터베이스 작업은 점점 더 전체 애플리케이션의 성능 병목 현상이 되고 있습니다. 웹에서는 매우 중요합니다. 애플리케이션은 특히 분명합니다. 데이터베이스의 성능에 관해서는 이는 DBA만이 고민해야 할 부분이 아니라, 우리 프로그래머들이 주목해야 할 부분입니다. 데이터베이스 테이블 구조를 설계하고 데이터베이스를 운영할 때(특히 테이블 조회 시 SQL 문) 우리 모두는 데이터 작업의 성능에 주의를 기울여야 합니다. 여기서는 SQL 문의 최적화에 대해 너무 많이 이야기하지 않고 가장 많은 웹 애플리케이션이 있는 데이터베이스인 MySQL에만 중점을 둘 것입니다. Mysql의

성능 최적화

는 하루아침에 달성할 수 없습니다. 단계별로 수행하고 모든 측면에서 최적화해야 최종 성능이 크게 향상됩니다. ㆍ 4가지 유형: 일반 인덱스, 기본 키 인덱스, 고유 인덱스, 전체 텍스트 인덱스]
•테이블 분할 기술(가로 분할, 수직 분할)

•읽고 쓰기[쓰기: 업데이트/삭제/추가] 분리 •저장 프로시저 [모듈식 프로그래밍, 속도 증가 가능]

•mysql 구성 최적화 [최대 동시성 my.ini 수 구성, 캐시 크기 조정]

•Mysql 서버 하드웨어 업그레이드

• Timed 불필요한 데이터 삭제 및 정기적인 조각 모음 수행(MyISAM)

데이터베이스 최적화 작업

데이터 중심 애플리케이션의 경우 데이터베이스 품질이 프로그램 성능에 직접적인 영향을 미치므로 데이터베이스 성능이 중요합니다. 일반적으로 데이터베이스의 효율성을 보장하려면 다음 네 가지 측면이 수행되어야 합니다.

1 데이터베이스 설계

2 SQL 문 최적화

3 데이터베이스 매개변수 구성

4 적절한 하드웨어 리소스 및 운영 시스템

또한 적절한 저장 프로시저를 사용하면 성능도 향상될 수 있습니다.
이 순서는 이 네 가지 작업이 성능에 미치는 영향도 보여줍니다.

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

세 가지 패러다임을 대중적인 방식으로 이해하는 것은 데이터베이스 디자인에 큰 이점이 됩니다. 데이터베이스 설계에 있어서 세 가지 패러다임을 더 잘 적용하려면 세 가지 패러다임을 대중적인 방식으로 이해하는 것이 필요합니다(대중적인 이해는 충분한 이해이지 가장 과학적이고 정확한 이해는 아닙니다).

첫 번째 정규형: 1NF는 속성(열)이 원자성이고 분해될 수 없도록 요구하는 속성에 대한 원자성 제약입니다(

관계형 데이터베이스
인 한 모두 1NF를 충족함)

두 번째 정규형: 2NF는 고유성입니다. 레코드 제약 조건에는 레코드에 고유 식별자, 즉 엔터티의 고유성이 있어야 합니다.

세 번째 정규형: 3NF는 필드 중복에 대한 제약으로, 필드에 중복이 없어야 합니다. 중복된 데이터베이스 디자인은 이를 수행할 수 없습니다.

그러나 중복성이 없는 데이터베이스는 최고의 데이터베이스가 아닐 수도 있습니다. 때로는 운영 효율성을 높이기 위해 패러다임 표준을 낮추고 중복된 데이터를 적절하게 보관해야 할 수도 있습니다. 구체적인 접근 방식은 개념적 데이터 모델을 설계할 때 세 번째 패러다임을 준수하고, 물리적 데이터 모델을 설계할 때 패러다임 표준을 낮추는 작업을 고려하는 것입니다. 정규형을 낮추는 것은 필드를 추가하고 중복성을 허용하는 것을 의미합니다.
☞ 데이터베이스 분류

관계형 데이터베이스: mysql/oracle/db2/informix/sysbase/sql server

비관계형 데이터베이스: (기능: 객체 지향또는 컬렉션)

NoSql 데이터베이스: MongoDB (특성적으로 문서 중심)

중간 중복성 또는 이유가 있는 중복성이 무엇인지 예를 들어보세요!

위는 부적절한 중복성이며, 그 이유는 다음과 같습니다.

여기서는 학생 활동 기록의 검색 효율성을 위해 학생 활동 기록 테이블에 단위 이름이 중복 추가됩니다. 단위정보 기록은 500개이며, 학생활동 기록은 1년에 약 200만개 정도의 데이터를 갖고 있다. 학생 활동 기록 테이블이 단위 이름 필드를 중복하지 않으면 3개의 int 필드와 1개의 타임스탬프 필드만 포함하고 16바이트만 차지하며 매우 작은 테이블입니다. 중복된 varchar(32) 필드를 사용하면 크기가 원래 크기의 3배가 되며 검색에는 훨씬 더 많은 I/O가 필요합니다. 게다가 레코드 수가 500개와 2,000,000개로 매우 다르기 때문에 단위 이름을 업데이트할 때 4,000개의 중복 레코드를 업데이트해야 합니다. 이러한 중복성은 단순히 역효과를 낳는다는 것을 알 수 있습니다.

주문 세부 정보 테이블에서 이 주문의 가격을 계산할 수 있으므로 주문 테이블의 가격은 중복 필드이지만 이러한 중복은 합리적이며 쿼리 성능도 향상시킬 수 있습니다.

위의 두 가지 예에서 결론을 내릴 수 있습니다.

1---n 중복은 1측에서 발생해야 합니다.

SQL 문 최적화

일반 SQL 최적화 단계

1. 다양한 SQL의 실행 빈도를 이해하려면 show status 명령을 사용하세요.

2. 실행 효율성이 낮은 SQL 문을 찾아냅니다. - (선택 강조)

3. 설명을 통해 비효율적인 SQL을 분석합니다.

4. 문제를 식별하고 그에 맞는 최적화 조치를 취합니다.

-- select语句分类
Select
Dml数据操作语言(insert update delete)
dtl 数据事物语言(commit rollback savepoint)
Ddl数据定义语言(create alter drop..)
Dcl(数据控制语言) grant revoke
-- Show status 常用命令
--查询本次会话
Show session status like 'com_%'; //show session status like 'Com_select'
--查询全局
Show global status like 'com_%';
-- 给某个用户授权
grant all privileges on *.* to 'abc'@'%';
--为什么这样授权 'abc'表示用户名 '@' 表示host, 查看一下mysql->user表就知道了
--回收权限
revoke all on *.* from 'abc'@'%';
--刷新权限[也可以不写]
flush privileges;

SQL 문을 최적화합니다. -show 매개변수

MySQL 클라이언트 연결에 성공한 후 show [session|global] status 명령을 사용하여 서버 상태 정보를 제공할 수 있습니다. 세션은 현재 연결의 통계 결과를 나타내고, 전역은 데이터베이스가 마지막으로 시작된 이후의 통계 결과를 나타냅니다. 기본값은 세션 수준입니다.

다음 예:

show status like 'Com_%';

여기서 Com_XXX는 XXX 문이 실행된 횟수를 나타냅니다.

주요 사항: Com_select, Com_insert, Com_update, Com_delete 이러한 매개변수를 통해 현재 데이터베이스 응용 프로그램이 주로 삽입 및 업데이트 또는 쿼리 작업을 기반으로 하는지와 다양한 유형의 SQL에 대한 대략적인 실행 비율을 쉽게 이해할 수 있습니다. . 얼마입니까?

사용자가 데이터베이스의 기본 상황을 이해하는 데 도움이 되도록 일반적으로 사용되는 여러 매개변수도 있습니다.

Connections: MySQL 서버에 연결을 시도한 횟수

Uptime: 서버가 작동하는 시간(초)

Slow_queries: 느린 쿼리 수(기본값은 느린 쿼리 시간 10초)

show status like 'Connections'
show status like 'Uptime'
show status like 'Slow_queries'

mysql 시간의 느린 쿼리를 쿼리하는 방법

Show variables like 'long_query_time';

mysql 느린 쿼리 시간 수정

set long_query_time=2

SQL 문 최적화-느린 쿼리 찾기

문제는 대규모 프로젝트에서 느린 실행 문을 빠르게 찾는 방법입니다. (느린 쿼리 찾기)

먼저 mysql 데이터베이스의 일부 실행 상태를 쿼리하는 방법을 이해합니다(예: mysql의 현재 실행 시간/총 실행 횟수를 알고 싶은 경우

선택/업데이트/삭제../현재 연결)

테스트를 용이하게 하기 위해 대규모 테이블(400만개)을 구축합니다.-> 저장 프로시저 사용Build

기본적으로 mysql은 느린 쿼리가 되도록 하세요.

mysql의 느린 쿼리를 수정하세요.

show variables like 'long_query_time' ; //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间

큰 테이블 구축->큰 테이블의 레코드에 대한 요구 사항이 있습니다. 레코드는 서로 다를 경우에만 유용합니다. 그렇지 않으면 테스트 효과가 매우 달라집니다. 실제 생성:

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
dname VARCHAR(20) NOT NULL DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

Test data

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

저장 프로시저를 정상적으로 실행하려면 명령 실행 종료 문자 수정 구분 기호 $$
임의를 반환하는 함수를 생성해야 합니다. 지정된 길이의 문자열

create function rand_string(n INT) 
returns varchar(255) #该函数会返回一个字符串
begin 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do 
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end

저장 프로시저 생성

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
set autocommit = 0; 
repeat
set i = i + 1;
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand());
until i = max_num
end repeat;
commit;
end 
#调用刚刚写好的函数, 1800000条记录,从100001号开始
call insert_emp(100001,4000000);

이때, 문 실행 시간이 1초를 초과하면 카운트됩니다.

느린 쿼리 SQL을 우리 로그 중 하나에 기록하면

기본적으로 낮은 버전의 mysql에서는 느린 쿼리를 기록하지 않습니다. mysql

binmysqld.exe - -safe-mode - -slow-query-log [mysql5.5를 지정할 수 있습니다. in my.ini]

binmysqld.exe –log-slow-queries=d:/abc.log [하위 버전 mysql5.0은 my.ini에 지정 가능]

느린 쿼리 로그는 다음 위치에 저장됩니다. 데이터 디렉터리 [mysql5.0 버전에서는 mysql 설치 디렉터리 /data/에 위치함], mysql5.5.19 아래에서는 이를 확인해야 합니다

my .ini의 datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"를 결정합니다.

mysql5.6에서 기본값은 기록 느린 쿼리를 시작하는 것입니다. ini가 있는 디렉터리는 C:ProgramDataMySQLMySQL Server 5.6, 구성 항목이 있습니다

slow-query-log=1

mysql5.5

binmysqld.exe - -safe -mode - -slow-query-log

에 대한 느린 쿼리를 시작하는 방법에는 두 가지가 있습니다.

my.ini 파일에서도 구성할 수 있습니다.

[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
slow-query-log

느린 쿼리 로그를 통해 실행 효율성이 낮은 SQL 문을 찾습니다. 느린 쿼리 로그에는 실행 시간이 long_query_time 설정을 초과하는 모든 SQL 문이 기록됩니다.

show variables like 'long_query_time';
set long_query_time=2;

dept 테이블에 데이터 추가

desc dept;
ALTER table dept add id int PRIMARY key auto_increment;
CREATE PRIMARY KEY on dept(id);
create INDEX idx_dptno_dptname on dept(deptno,dname);
INSERT into dept(deptno,dname,loc) values(1,'研发部','康和盛大厦5楼501');
INSERT into dept(deptno,dname,loc) values(2,'产品部','康和盛大厦5楼502');
INSERT into dept(deptno,dname,loc) values(3,'财务部','康和盛大厦5楼503');UPDATE emp set deptno=1 where empno=100002;

****테스트 문*** [emp 테이블의 레코드는 3600000일 수 있으며 효과는 분명히 느립니다.]

select * from emp where empno=(select empno from emp where ename='研发部')

e.empno로 주문을 가져오면, 속도는 더 빨라질 것입니다. 느림, 때로는 1분 이상

테스트 명령문

select * from emp e,dept d where e.empno=100002 and e.deptno=d.deptno;

느린 쿼리 로그 보기: 기본값은 데이터 디렉터리 데이터의 호스트 이름-slow.log입니다. 낮은 버전의 mysql은 mysql을 열 때 - -log-slow-queries[=file_name]을 사용하여 구성해야 합니다.

SQL 문 최적화-분석 설명 문제

Explain select * from emp where ename=“wsrcla”

는 다음 정보를 생성합니다.

select_type : 쿼리 유형을 나타냅니다.

table: 결과 집합을 출력하는 테이블

type: 테이블의 연결 유형을 나타냅니다.

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

explain select * from emp where ename='JKLOIP'

如果要测试Extra的filesort可以对上面的语句修改

explain select * from emp order by ename\G

EXPLAIN详解

id

SELECT识别符。这是SELECT的查询序列号

id 示例

SELECT * FROM emp WHERE empno = 1 and ename = (SELECT ename FROM emp WHERE empno = 100001) \G;

select_type

PRIMARY :子查询中最外层查询

SUBQUERY : 子查询内层第一个SELECT，结果不依赖于外部查询

DEPENDENT SUBQUERY:子查询内层第一个SELECT，依赖于外部查询

UNION :UNION语句中第二个SELECT开始后面所有SELECT，

SIMPLE

UNION RESULT UNION 中合并结果

Table

显示这一步所访问数据库中表名称

Type

对表访问方式

ALL：

SELECT * FROM emp \G

完整的表扫描 通常不好

SELECT * FROM (SELECT * FROM emp WHERE empno = 1) a ;

system：表仅有一行(=系统表)。这是const联接类型的一个特

const：表最多有一个匹配行

Possible_keys

该查询可以利用的索引，如果没有任何索引显示 null

Key

Mysql 从 Possible_keys 所选择使用索引

Rows

估算出结果集行数

Extra

查询细节信息

No tables ：Query语句中使用FROM DUAL 或不含任何FROM子句

Using filesort ：当Query中包含 ORDER BY 操作，而且无法利用索引完成排序，

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

通过收集统计信息不可能存在结果

Using temporary：某些操作必须使用临时表，常见 GROUP BY ; ORDER BY

Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据;

위 내용은 Mysql 데이터베이스 성능 최적화에 대해 알고 계시나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!