MySQL 데이터베이스를 최적화하는 방법

mysql 데이터베이스 최적화 방법: 인덱스 인덱스 설정, 더 적은 선택 문 사용, 쿼리 캐싱 활성화, 적합한 스토리지 엔진 선택, 연결을 위해 또는 where 절 사용 방지, 대량의 데이터 반환 방지 등

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

1. Index Index

Index를 먼저 넣어두세요. 말할 필요도 없이 우리는 기본키 인덱스인 이 최적화 방법을 조용히 사용해 왔습니다. 때로는 신경 쓰지 않을 수도 있습니다. 적절한 인덱스가 정의되면 데이터베이스 쿼리 성능(속도)이 몇 배 또는 수십 배 향상됩니다.

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 열을 NOT NULL로 변경하면 성능 향상이 거의 없으므로 (튜닝 시) 문제가 발생할 것이라고 판단되지 않는 한 기존 스키마에서 이러한 상황을 먼저 찾아서 수정할 필요는 없습니다. 그러나 열에 인덱스를 작성하려는 경우 열을 NULL로 디자인하지 않도록 해야 합니다. 물론 예외도 있습니다. 예를 들어 InnoDB는 NULL 값을 저장하기 위해 별도의 비트를 사용하므로 희소 데이터에 대한 공간 효율성이 좋습니다. 그러나 이는 MyISAM에는 적용되지 않습니다.

6. 스토리지 엔진 선택

MyISAM과 InnoDB를 선택하는 방법과 관련하여 트랜잭션 처리나 외래 키가 필요한 경우 InnoDB가 더 나은 방법일 수 있습니다. 전체 텍스트 인덱싱이 필요한 경우 일반적으로 MyISAM이 시스템에 내장되어 있기 때문에 좋은 선택입니다. 그러나 우리는 200만 행의 레코드를 자주 테스트하지 않습니다. 따라서 조금 느리더라도 Sphinx를 사용하면 InnoDB에서 전체 텍스트 인덱스를 얻을 수 있습니다.

데이터의 크기는 어떤 스토리지 엔진을 선택하는지에 영향을 미치는 중요한 요소입니다. 대용량 데이터 세트의 경우 트랜잭션 처리 및 장애 복구를 지원하기 때문에 InnoDB를 선택하는 경향이 있습니다. 데이터베이스 크기에 따라 오류 복구 시간이 결정됩니다. InnoDB는 데이터 복구를 위해 트랜잭션 로그를 사용할 수 있으며 이는 더 빠릅니다. MyISAM이 이러한 작업을 수행하는 데

몇 시간 또는 며칠이 걸릴 수 있지만 InnoDB는 몇 분 밖에 걸리지 않습니다.

데이터베이스 테이블을 조작하는 습관도 성능에 큰 영향을 미치는 요인이 될 수 있습니다. 예: COUNT()는 MyISAM 테이블에서는 매우 빠르지만 InnoDB 테이블에서는 어려울 수 있습니다. 기본 키 쿼리는 InnoDB에서 매우 빠르지만 기본 키가 너무 길면 성능 문제가 발생할 수도 있으므로 주의해야 합니다. 많은 수의 insert 문은 MyISAM에서 더 빠르지만 업데이트는 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教程(视频)

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