MySQL을 위한 상위 10가지 최적화 방법

1. 가장 적합한 필드 속성을 선택합니다

테이블의 필드 너비를 최대한 작게 설정합니다. 문자의 상한은 255바이트(고정 공간 점유)이며 상한은 varchar의 크기는 65535바이트(실제 점유 공간)이고, 텍스트의 상한은 65535입니다. char 처리는 varchar보다 효율적입니다.

필드를 NOT NULL로 설정해 보세요. 쿼리를 실행할 때 데이터베이스는 NULL 값을 비교할 필요가 없습니다.

2. 하위 쿼리 대신 JOIN을 사용하세요

JOIN이 더 효율적인 이유는 MySQL이 이 논리를 완료하기 위해 메모리에 임시 테이블을 만들 필요가 없기 때문입니다. 2단계 쿼리가 필요합니다(통합 쿼리 조건) 플러스 인덱싱이 더 빠릅니다).

3. 수동으로 생성한 임시 테이블 대신 유니온(UNION)을 사용하세요

임시 테이블을 사용해야 하는 두 개 이상의 SELECT 쿼리를 하나의 쿼리로 결합합니다.

SELECT 이름, 전화 FROM 클라이언트 UNION SELECT 이름, 생년월일 FROM 작성자 UNION SELECT 이름, 공급자 FROM 제품;

4. Transactions

서브 쿼리(Sub-Queries), 연결(JOIN) 및 UNION(Union)을 사용하여 다양한 쿼리를 생성할 수 있지만 하나 또는 몇 개의 SQL 문만으로 모든 데이터베이스 작업을 완료할 수는 없습니다. 특정 종류의 작업을 완료하려면 일련의 명령문이 필요한 경우가 더 많습니다.

결과는 다음과 같습니다. 명령문 블록의 모든 명령문이 성공적으로 작동하거나 모두 실패합니다. 즉, 데이터베이스 내 데이터의 일관성과 무결성을 유지할 수 있습니다. BEGIN 키워드로 시작하여 COMMIT 키워드로 끝납니다. 이 기간 동안 SQL 작업이 실패하면 ROLLBACK 명령은 BEGIN이 시작되기 전의 상태로 데이터베이스를 복원할 수 있습니다.

5. 테이블 잠금

트랜잭션은 데이터베이스의 무결성을 유지하는 매우 좋은 방법이지만, 독점성으로 인해 특히 대규모 애플리케이션 시스템에서 데이터베이스 성능에 영향을 줄 수 있습니다. 트랜잭션이 실행되는 동안 데이터베이스가 잠기므로 다른 사용자 요청은 트랜잭션이 끝날 때까지만 기다릴 수 있습니다.

LOCK TABLE inventory WRITE 
SELECT Quantity FROM inventory 
WHEREItem='book'; 
... 
UPDATE inventory SET Quantity=11 
WHEREItem='book'; 
UNLOCK TABLES

여기에서는 SELECT 문을 사용하여 일부 계산을 통해 초기 데이터를 가져오고 UPDATE 문을 사용하여 새 값을 테이블에 업데이트합니다. WRITE 키워드가 포함된 LOCK TABLE 문은 UNLOCK TABLES 명령이 실행되기 전에 인벤토리에 대한 삽입, 업데이트 또는 삭제를 위한 다른 액세스가 없도록 보장합니다.

6. 외래 키를 사용하여 테이블을 잠그는 방법

은 데이터의 무결성을 유지할 수 있지만 데이터의 관련성을 보장할 수는 없습니다. 이때 외래 키를 사용할 수 있습니다. 예를 들어 외래 키를 사용하면 각 판매 레코드가 기존 고객을 가리키도록 할 수 있습니다. 여기서 외래 키는 customerinfo 테이블의 CustomerID를 salesinfo 테이블의 CustomerID에 매핑할 수 있습니다. 유효한 CustomerID가 없는 레코드는 salesinfo에 업데이트되거나 삽입되지 않습니다.

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

예제에서 "ON DELETE CASCADE" 매개변수에 주의하세요. 이 매개변수는 customerinfo 테이블의 고객 레코드가 삭제될 때 salesinfo 테이블의 고객과 관련된 모든 레코드도 자동으로 삭제되도록 보장합니다. MySQL에서 외래 키를 사용하려면 테이블을 생성할 때 테이블 유형을 트랜잭션에 안전한 InnoDB 유형으로 정의해야 합니다. 이 유형은 MySQL 테이블의 기본 유형이 아닙니다. 이는 CREATE TABLE 문에 TYPE=INNODB를 추가하여 정의됩니다.

7. 인덱스 사용

쿼리 문에 MAX(), MIN() 및 ORDERBY와 같은 명령이 포함되어 있으면 성능 향상이 더욱 분명해집니다.

JOIN, WHERE 판단 및 ORDER BY 정렬에 사용될 필드에 인덱스를 구축해야 합니다. 중복된 값이 많이 포함된 데이터베이스의 필드를 색인화하지 마십시오. ENUM 유형 필드의 경우 customerinfo의 "province".. 필드와 같이 다수의 중복 값을 가질 가능성이 매우 높습니다. 반대로 이러한 필드에 인덱스를 구축하는 것은 도움이 되지 않습니다. 데이터베이스 성능을 줄입니다.

일반 인덱스(KEY 또는 INDEX 키워드로 정의된 인덱스)의 유일한 작업은 데이터에 대한 액세스 속도를 높이는 것입니다. 따라서 쿼리 조건(WHEREcolumn=) 또는 정렬 조건(ORDERBYcolumn)에서 가장 자주 나타나는 데이터 열에 대해서만 인덱스를 생성해야 합니다.

고유 인덱스의 이점: 첫째, MySQL의 이 인덱스 관리가 단순화되고 인덱스가 더 효율적이 됩니다. 둘째, 새 레코드가 데이터 테이블에 삽입될 때 MySQL은 새 레코드의 이 필드를 자동으로 확인합니다. 값이 이미 레코드의 이 필드에 나타났습니다. 그렇다면 MySQL은 새 레코드 삽입을 거부합니다. 즉, 고유 인덱스는 데이터 레코드의 고유성을 보장할 수 있습니다. 많은 경우 고유 인덱스를 생성하는 목적은 액세스 속도를 향상시키는 것이 아니라 단지 데이터 중복을 피하기 위한 것입니다.

8. 최적화된 쿼리 문

SELECT FROM order WHERE YEAR(OrderDate)<2001; 
SELECT FROM order WHERE OrderDate<"2001-01-01";
SELECT FROM inventory WHERE Amount/7<24; 
SELECT FROM inventory WHERE Amount<24*7;

MySQL이 쿼리에서 자동 유형 변환을 수행하지 않도록 하세요. 변환 프로세스로 인해 인덱스도 무효화되기 때문입니다.

9. 인덱스 실패

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

当全表扫描速度比索引速度快时，mysql 会使用全表扫描，此时索引失效。

应尽量避免在 where 子句中使用 or,and,in,not in 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，合理使用 union all（允许重复的值，请使用 UNION ALL）。

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10    
union all    
select id from t where num=20

10. 引擎的选取

MyISAM 索引文件在数据库中存放的对应表的磁盘文件有.frm,.MYD,*.MYI 结尾的三个文件：

frm 文件是存放的表结构，表的定义信息；

MYD 文件是存放着表中的数据；

MYI 文件存放着表的索引信息；

InnoDB 存储引擎在磁盘中存放的对应的表的磁盘文件有.frm,.ibd 这两个文件；

frm 文件是存放表结构，表的定义信息；

ibd 文件是存放 表中的数据、索引信息；

详细出处参考：https://blog.csdn.net/jinxingfeng_cn/article/details/16878355

性能方面的优化：

explain 执行计划 ==>https://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

推荐教程：《MySQL教程》

위 내용은 MySQL을 위한 상위 10가지 최적화 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!