태그 보관물: MySQL

MySQL 프로시저 변수값에 따른 where 및 order by 변경

개요

MySQL 프로시저내에서 인수로 전달받은 값에 따라 where 및 order by 부분을 달리하고 싶은 경우가 있습니다. Dynamic SQL로 가능하지만 이 글에서는 case 문을 이용한 방법을 살펴보겠습니다.

where 절

프로시저의 인수로 전달받은 값에 따라 where 절의 조건을 변경하는 방법입니다. 회사ID 가 0이면 모든 회사를 보여주고 회사ID가 0 보다 크면 해당하는 자료만 보여주는 쿼리를 보겠습니다. _company_id 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    1 = 1
    and case when _company_id > 0 then company_id = _company_id else 1 = 1 end

7행을 보면 and 뒤쪽에 case 문을 이용해서 조건을 변경한 것을 알 수 있습니다.

order by

프로시저 인수로 특정한 필드의 정렬 조건을 넘겨받는 경우가 있습니다. 토글 형태로 오름차순과 내림차순이 번갈아 가면서 order by 부분에 적용되어야 합니다. 정렬을 적용해야 하는 필드가 적으면 조건으로 나누어 처리할 수 있습니다. 하지만 필드의 개수가 많아지면 조건으로 나누어 작업하는 것이 조합이 많아지게 되어 불가능 합니다. where 절과 마찬가지로 case 문을 이용해서 order by 부분의 필드와 정렬기준을 변경할 수 있습니다.

회사이름과 직원수 필드로 정렬하는 쿼리문을 예로 들어보겠습니다. _company_name_sort_order, _employee_sort_order 가 인수로 전달받은 변수입니다.

select
    *
from
    company
where
    company_id > 0
order by
    case when lower(_company_name_sort_order) = 'a' then company_name end asc
    , case when lower(_company_name_sort_order) = 'd' then company_name end desc
    , case when lower(_employee_sort_order) = 'a' then employee end asc
    , case when lower(_employee_sort_order) = 'd' then employee end desc

8-11행을 보면 case 문으로 전달된 변수의 값으로 정렬기준을 변경한 것을 알 수 있습니다. 쿼리문이 이상하게 보이는 것 같지만 이런 방식으로 프로시저의 크기를 줄일 수 있습니다.

이상으로 MySQL 프로시저 변수값에 따른 where 및 order by 변경 방법에 대해 알아보았습니다.

C# MySQL InnoDB engine Insert

개요

개발을 진행하다 보면 짧은 시간에 많은 양의 자료를 DB에 입력해야 하는 경우가 있습니다. 처음에는 적은양의 자료입력을 염두에 두고 개발을 진행합니다. 그러나 설계 변경에 따라 자료량이 크게 증가하게  되는 경우가 있습니다. 적은량의 처리에 맞추어진 자료입력 부분을 변경없이 적용하면 성능저하가 크게 나타나게 됩니다. 자료 입력 시 DB 접속 및 반복을 최소화해서 수행해야 합니다. 이것은 모든 DB에 적용되는 원칙입니다.

MySQL DB 스토리지 엔진

MySQL DB는 여러가지 스토리지 엔진을 지원합니다. 자세한 사항은 공식 문서를 참고하면 됩니다. InnoDB, 다른 스토리지 엔진이 있습니다. 그 중 InnoDB engine에 입력을 수행하는 방식에 대해 알아보도록 하겠습니다. 꼭 MySQL 에 한정된 것이 아니고 다른 DB에도 공통적으로 적용되는 것이라 생각하시면 됩니다.

입력방식

근래에 진행했던 프로젝트에서 특정 테이블에 하루에 한번씩 Insert 가 되고 같은 일자에 Update 되어야 하는 부분이 있었습니다. 처리해야 되는 자료의 성격때문에 초기에 예상했던 자료보다 몇배가 되는 양을 입력해야 하는 상황이 발생했습니다. 스토리지엔진은 InnoDB engine을 사용했습니다. 자료의 양이 많아지고 인덱스까지 추가되어 있어 성능저하가 심각하게 나타났습니다. 자료 입력 방식을 변경하고 인덱스 필드를 조정하기로 하고 작업을 진행했습니다.

Insert 문을 수행할 때 한번의 명령으로 많은양의 자료를 입력할 수 있는 방법이 있습니다. 이것을 고려했으나 프로그램 구조를 변경해야 해서 적용하지 않았습니다. 예를 들면 다음과 같은 방식 입니다.

INSERT INTO your_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9);

원래의 입력방식은 프로시저를 호출해서 한 행을 입력하거나 갱신하는 것 이었습니다. 프로시저 호출 부분을 바꾸기는 어려워서 한행을 입력하고 commit 이 반복되지 않도록 하나의 작업 단위로 묶어 처리되도록 했습니다(MySqlTransaction 클래스 사용).

var connectionString = "server=localhost;database=yourdatabase;uid=root;password=yourpassword";

var connection = new MySqlConnection(connectionString);
var transaction = connection.BeginTransaction();

try
{
	for (int i = 0; i < 100; i++)
	{
		//자료입력 부분
	}

	transaction.Commit();
}
catch (Exception ex)
{
	transaction.Rollback();
}
finally
{
	connection.Close();
}

자료를 반복해서 입력하고 문제가 없을 경우 commit 하도록 변경했습니다. MyISAM engine인 경우 트랜젝션을 지원하지 않아 의미가 없습니다. 하지만 InnoDB engine이라면 입력 성능이 향상됩니다. 성능 향상을 위한 여러 요소가 있습니다. 그렇지만 일단 자료 입력 부분에서 최대한 성능이 저하되지 않도록 해야 합니다.

이상 MySQL InnoDB engine 사용 시 성능향상 부분에 대해 간략하게 알아보았습니다.