mysql 데이터베이스에 대한 일반적인 최적화 작업 공유

이 글은 주로 mysql 데이터베이스의 일반적인 최적화 작업을 소개합니다. 이 글은 Index index, use를 포함한 mysql 데이터베이스의 일상적인 개발 및 사용 경험을 요약한 것입니다. SELECT*, EXPLAIN SELECT 및 쿼리 캐시 및 기타 관련 정보를 활성화하면 필요한 모든 사람에게 확실한 참고 가치가 될 것이라고 믿습니다.

머리말

데이터 중심 애플리케이션의 경우 데이터베이스 품질이 프로그램 성능에 직접적인 영향을 미치므로 데이터베이스 성능이 중요합니다. 중요한. 따라서 모든 사람은 mysql 데이터베이스의 최적화 작업을 이해해야 합니다. 이 기사에서는 주로 mysql 데이터베이스의 일반적인 최적화 작업을 요약하고 있으므로 자세한 소개를 살펴보겠습니다.

1. Index

물론, 우리는 이 최적화 방법을 묵묵히 사용해왔으며 이것이 바로 Primary Key입니다. 색인. 때로는 신경 쓰지 않을 수도 있습니다. 적절한 인덱스가 정의되면 데이터베이스 쿼리 성능(속도)이 몇 배 또는 수십 배 향상됩니다.

일반 인덱스

를 사용하여 쿼리 속도를 향상시킵니다.

테이블 생성, 인덱스 생성

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
index [索引名] (column_name)
);

인덱스 생성

CREATE INDEX index_name ON tab_name (column_name)

인덱스 삭제

DROP INDEX index_name FROM tab_name

인덱스 보기

SHOW index FROM tab_name

기본 키 index

쿼리 속도와 고유 제약 조건을 높이는 역할

테이블 생성 및 인덱스 생성

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
PRIMARY KEY(column_name)
);

인덱스 생성

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

인덱스 삭제

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

고유 인덱스

쿼리 속도와 고유 제약 조건을 높이는 역할

테이블 생성 및 인덱스 생성

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
unique [索引名] (column_name)
);

인덱스 생성

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

인덱스 삭제

DROP UNIQUE INDEX index_name FROM tab_name

2. 덜 사용 SELECT*

어떤 사람들은 다음과 같은 경우 쿼리하고 싶은 항목을 선택할 수 있습니다. 데이터베이스를 쿼리하는 것은 부적절한 동작입니다. 전부가 아닌 사용하고 싶은 데이터를 얻어야 하는데, 선택하게 되면 웹 서버의 부담이 커지고, 네트워크 전송의 부하가 늘어나 쿼리 속도가 자연스럽게 떨어지기 때문입니다.

3. EXPLAIN SELECT

이 기능을 한 번도 본 적이 없는 분들이 많을 것으로 추정되지만 꼭 사용해 보시길 권해드립니다. explain은 mysql이 인덱스를 사용하여 select 문을 처리하고 테이블을 조인하는 방법을 보여줍니다. 더 나은 인덱스를 선택하고 더 최적화된 쿼리 문을 작성하는 데 도움이 될 수 있습니다. 주요 용도는 선택하기 전에 설명을 추가하는 것입니다.

EXPLAIN SELECT [查找字段名] FROM tab_name ...

4. 쿼리 캐시 활성화

대부분의 MySQL 서버에는 쿼리 캐시가 활성화되어 있습니다. 이는 성능을 향상시키는 가장 효과적인 방법 중 하나이며 MySQL 데이터베이스 엔진에 의해 처리됩니다. 다수의 동일한 쿼리가 여러 번 실행되면 쿼리 결과가 캐시에 저장되므로 이후의 동일한 쿼리는 테이블을 조작할 필요 없이 캐시된 결과에 직접 액세스할 수 있습니다.

첫 번째 단계는 query_cache_type을 ON으로 설정한 다음 시스템 변수 have_query_cache를 사용할 수 있는지 쿼리하는 것입니다.

show variables like 'have_query_cache'

그 후 메모리 크기를 할당합니다. 쿼리 캐시에 캐시를 제어하고 쿼리 결과의 최대값을 제어합니다. 관련 작업은 구성 파일에서 수정됩니다.

5. NOT NULL 사용

애플리케이션에서 필요하지 않더라도 NULL(null 값)이 될 수 있는 열이 포함된 테이블이 많습니다. 저장하세요. NULL의 경우에도 마찬가지입니다. NULL 허용이 열의 기본 속성이기 때문입니다. 실제로 NULL 값을 저장해야 하는 경우가 아니면 일반적으로 열을 NOT NULL로 지정하는 것이 가장 좋습니다.

NULL 가능 열이 인덱스, 인덱스 통계 및 값 비교를 더 복잡하게 만들기 때문에 NULL 가능 열이 포함된 쿼리는 MySQL에 대해 최적화하기가 더 어렵습니다. NULL이 될 수 있는 열은 더 많은 저장 공간을 사용하고 MySQL에서 특별한 처리가 필요합니다. NULL 가능 열이 인덱싱되면 각 인덱스 레코드에는 추가 바이트가 필요하며 이로 인해 MyISAM에서는 고정 크기 인덱스(예: 정수 열이 하나만 있는 인덱스)가 가변 크기 인덱스가 될 수도 있습니다. <… 문제. 그러나 열에 인덱스를 작성하려는 경우 열을 NULL로 디자인하지 않도록 해야 합니다. 물론 예외도 있습니다. 예를 들어 InnoDB는 NULL 값을 저장하기 위해 별도의 비트를 사용하므로 희소 데이터에 대한 공간 효율성이 좋습니다. 그러나 이는 MyISAM에는 적용되지 않습니다.

6. 스토리지 엔진 선택

对于如何选择MyISAM和InnoDB,如果你需要事务处理或是外键，那么InnoDB可能是比较好的方式。如果你需要全文索引，那么通常来说MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要

几个小时甚至几天来干这些事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM表中会非常快，而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts语句在MyISAM下会快一些，但是updates在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM也许会更适合。当然，在大型的环境下使用MyISAM也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

使用varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

where 子句中使用参数，会导致全表扫描,因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

위 내용은 mysql 데이터베이스에 대한 일반적인 최적화 작업 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!