MySQL 스토어드 프로시저에서 사용 여부

MySQL 스토어드 프로시저에서 사용 여부

우리는 새로운 프로젝트를 시작하고 있는데 MySQL에 저장 프로시저를 사용해야 할지 말아야 할지 정말 고민하고 있습니다.

스토어드 프로시저를 사용하여 비즈니스 모델 엔티티를 삽입하고 업데이트합니다.모델 엔티티를 나타내는 테이블이 몇 개 있으며, 이러한 저장 프로시저 삽입/갱신에서 이를 추상화합니다.

한편 모델 레이어에서 삽입 및 업데이트를 호출할 수 있지만 MySQL에서는 호출할 수 없고 PHP에서는 호출할 수 있습니다.

당신의 경험상 어떤 것이 가장 좋은 선택입니까? 두 접근법의 장점과 단점. 고성능으로 따지면 어떤 것이 가장 빠릅니까?

PS: 대부분 읽기가 가능한 웹 프로젝트입니다.고성능이 가장 중요합니다.

실제 프로그래밍 언어 코드와 달리 다음과 같습니다.

• portable이 아닙니다(모든 db는 자체 버전의 PL/SQL을 가지고 있습니다. 같은 데이터베이스의 다른 버전이 호환되지 않을 수 있습니다. 본 적이 있습니다.)
• 쉽게 테스트할 수 없음 - 테스트하려면 실제(개발) 데이터베이스 인스턴스가 필요합니다.따라서 빌드 중에 코드를 유닛 테스트하는 것은 사실상 불가능합니다.
• 쉽게 업데이트/해제할 수 없음 - 삭제/생성해야 합니다.즉, 실제 가동 중인 DB를 수정하여 릴리스해야 합니다.
• 라이브러리 지원이 없음(다른 사용자가 지원하는데 코드를 쓰는 이유)
• 다른 테크놀로지와의 연동이 용이하지 않다(그 테크놀로지로부터 Web 서비스를 호출해 본다).
• 그들은 Fortran처럼 원시적인 언어를 사용하며, 따라서 유용한 코딩이 이루어지도록 하는 것은 미숙하고 힘들기 때문에, 비록 그것이 그들의 주된 목적이지만, 비즈니스 로직을 표현하는 것은 어렵다.
• 디버깅/트레이싱/메시지 로깅 등을 제공하지 않습니다(일부 dbs는 이를 지원할 수 있습니다.단, 본 적이 없습니다).
• 구문을 지원하고 다른 기존 프로시저에 링크할 수 있는 적절한 IDE가 없음(예: Eclipse for java)
• 코드에 숙련된 사람들은 앱 코더보다 더 드물고 더 비싸다.
• 데이터베이스 서버에서 실행되기 때문에 보통 DB 서버의 부하가 증가하기 때문에 이들 서버를 사용하면 트랜잭션의 최대 처리량이 감소합니다.
• 상수를 효율적으로 공유할 수 없음(테이블을 만들고 절차 내에서 질문함으로써 해결되는 문제 - 매우 비효율적임
• 기타.

데이터베이스 고유의 액션(예를 들어 DB 무결성을 유지하기 위한 트랜잭션 내 액션)이 있거나 절차를 매우 원자적이고 단순하게 유지하는 경우 이러한 액션을 고려할 수 있습니다.

「하이 퍼포먼스」를 사전에 지정할 때는 주의가 필요합니다.그것은 종종 좋은 디자인을 희생시키면서 잘못된 선택을 하게 되고, 당신이 생각하는 것보다 훨씬 더 빨리 당신을 괴롭힐 것입니다.

스토어드 프로시저를 사용하면 위험을 감수할 수 있습니다(해본 적이 있고 돌아가고 싶지 않은 사람으로부터).전염병처럼 그들을 피하라고 권합니다.

프로그래밍 코드와 달리 다음과 같습니다.

• SQL 주입 공격을 거의 불가능하게 만듭니다(단,
  동적 구성 및 실행
  절차 내 SQL)
• 콜아웃의 일부로서 IPC 경유로 송신하는 데이터의 양이 대폭 삭감된다.
• 데이터베이스가 훨씬 더 나은 캐시 계획 및 결과 집합을 사용할 수 있도록 합니다(내부 캐싱 구조 때문에 MySQL에서는 그다지 효과적이지 않습니다).
• 격리하여 쉽게 테스트 가능(즉, JUnit 테스트의 일부가 아님)
• DB 고유의 기능을 사용할 수 있다는 점에서 이식성이 있으며 프로시저 이름 뒤에 추상화되어 있습니다(코드에서는 범용 SQL 타입의 것을 사용하고 있습니다).
• 코드로부터 호출된 SQL보다 느린 경우는 거의 없습니다.

하지만 보헤미안 말처럼 단점도 많다(이것은 단지 다른 관점을 제시하기 위한 것이다).자신에게 가장 적합한 것을 결정하기 전에 벤치마크를 해야 할 수도 있습니다.

퍼포먼스에 관해서는 미래의 MySQL 버전에서도 퍼포먼스를 발휘할 수 있는 가능성이 있습니다(SQL Server 또는 Oracle에서는 매우 편리합니다).하지만, 다른 모든 것에 대해서는...그들은 경쟁을 완전히 망친다.정리합니다.

• 보안:앱에 EXECUTE만 부여하면 문제 없습니다.SP가 업데이트 선택...을 삽입하고 누출은 발생하지 않습니다.즉, 모델을 글로벌하게 제어하고 데이터 보안을 강화합니다.

• 보안 2: 드물다는 것은 알지만, 가끔 서버에서 php 코드가 새어 나옵니다(즉, 일반에 공개됩니다).쿼리가 포함된 경우 공격자가 모델을 알 수 있습니다.좀 이상하지만 어쨌든 신호를 보내고 싶었어

• 태스크포스: 네, 효율적인 SQL SP를 작성하려면 특정 리소스가 필요하며 경우에 따라서는 비용이 더 많이 들 수 있습니다.그러나 클라이언트에 쿼리를 통합한다고 해서 이러한 리소스가 필요하지 않다고 생각되면...심각한 문제가 생길 거야웹 개발의 유추에 대해 말씀드리겠습니다.디자이너는 독자적인 테크놀로지로 작업할 수 있고 프로그래머는 비즈니스 레이어의 프로그래밍에 집중할 수 있기 때문에 뷰와 다른 뷰를 분리하는 것이 좋습니다.

• 비즈니스 레이어 캡슐화: 스토어드 프로시저를 사용하면 비즈니스가 속한 곳, 즉 데이터베이스를 완전히 격리할 수 있습니다.

• 신속한 테스트 가능: 셸 아래에 있는 하나의 명령줄을 사용하여 코드를 테스트합니다.

• 클라이언트 테크놀로지로부터의 독립성: 내일 php에서 다른 것으로 바꾸고 싶다면 문제 없습니다.네, 이 SQL을 다른 파일에 저장하는 것만으로 충분합니다.또한 sql 엔진을 바꾸기로 결정하면 할 일이 많아진다는 코멘트의 장점도 있습니다.대규모 프로젝트나 대기업에서는 (대부분의 비용과 인사 관리에 의해) 거의 발생하지 않기 때문에, 어쨌든 그렇게 하려면 충분한 이유가 필요합니다.

• 신속한 변화를 위한 3단계 개발 실시: 데이터베이스가 클라이언트 코드와 같은 서버 상에 없는 경우 데이터베이스용 서버는 1개뿐이지만 다른 서버가 존재할 수 있습니다.이 경우 SQL 관련 코드를 변경해야 할 때 php 서버를 업그레이드할 필요가 없습니다.

좋아요, 그게 가장 중요한 것 같아요. 가장 중요한 말인 것 같아.저는 두 가지 정신(SP vs 클라이언트)으로 발전했고, SP 스타일은 정말 마음에 듭니다.난 그저 Mysql이 그들을 위한 진짜 ID를 가지고 있기를 바랐을 뿐이야 왜냐하면 지금은 그게 좀 골칫거리거든.

스토어드 프로시저는 쿼리를 정리한 상태로 유지하여 일괄 처리를 한 번에 수행할 수 있으므로 사용하기 편리합니다.저장 프로시저는 실행 시마다 컴파일되는 쿼리와 달리 미리 컴파일되기 때문에 일반적으로 실행 속도가 빠릅니다.이는 데이터베이스가 원격 서버에 있는 상황에서 큰 영향을 미칩니다. PHP 스크립트에 쿼리가 있는 경우 애플리케이션과 데이터베이스 서버 간에 여러 통신(쿼리가 전송, 실행 및 반환됨)이 발생합니다.단, 스토어드 프로시저를 사용하는 경우 크고 복잡한 쿼리 대신 작은 CALL 스테이트먼트만 전송하면 됩니다.

스토어드 프로시저에는 독자적인 언어와 구문이 있기 때문에 프로그램 처리에 적응하는 데 시간이 걸릴 수 있습니다.하지만 익숙해지면 코드가 정말 깨끗하다는 것을 알게 될 것입니다.

성능 측면에서는 저장 프로시저를 사용하든 사용하지 않든 큰 이득이 되지 않을 수 있습니다.

저의 어려움은 질문과 직접 관련이 없을 수 있지만, 제 의견을 알려드리겠습니다.

많은 문제와 마찬가지로 스토어드 프로시저 또는 애플리케이션 계층 기반 솔루션의 사용에 대한 답변은 전반적인 노력을 촉진하는 질문에 의존합니다.

• 당신이 원하는 것.

배치 작업 또는 온라인 작업 중 하나를 수행하려고 합니까?완전히 거래되는 건가요?그 수술은 얼마나 반복됩니까?대기 중인 데이터베이스의 워크로드가 얼마나 됩니까?

• 그걸 얻기 위해 당신이 가진 것.

어떤 종류의 데이터베이스 기술을 가지고 있습니까?어떤 종류의 인프라입니까?귀사의 팀은 데이터베이스 기술에 대한 충분한 훈련을 받았습니까?귀사의 팀은 데이터베이스에 의존하지 않는 솔루션을 구축할 수 있습니까?

• 받을 시간이야.

그건 비밀이 아니에요.

• 건축.

솔루션을 여러 곳에 배포해야 합니까?리모트 통신을 사용하려면 솔루션이 필요합니까?귀사의 솔루션은 여러 데이터베이스 서버에서 작동하고 있습니까, 아니면 클러스터 기반 아키텍처를 사용하고 있습니까?

• 메인터넌스.

어플리케이션 변경에 얼마가 필요합니까?솔루션 유지보수를 위해 특별히 교육을 받았습니까?

• 변경 관리

데이터베이스 테크놀로지가 단시간, 중간시간, 장기간에 걸쳐 변화한다고 생각하십니까?솔루션을 자주 이행해야 합니까?

• 비용.

어떤 전략을 사용하여 솔루션을 구현하려면 비용이 얼마나 들까요?

그 점들의 종합이 답을 이끌어 낼 것이다.그래서 여러분은 전략을 사용할지 말지를 결정할 때 이 사항들을 하나하나 신경써야 합니다.저장 프로시저를 사용하는 것이 응용 프로그램 계층 관리 쿼리보다 나은 경우도 있고 쿼리를 실행하여 응용 프로그램 계층 기반 솔루션을 사용하는 것이 가장 좋은 경우도 있습니다.

스토어드 프로시저의 사용은 다음과 같은 경우에 더 많은 경향이 있습니다.

1. 데이터베이스 기술은 단기간에 변경할 수 있는 것이 아닙니다.
2. 데이터베이스 테크놀로지는 워크로드를 여러 프로세서, 메모리 및 리소스(클러스터링, 그리드)로 분할하기 위한 병렬화된 운영, 테이블 파티션, 기타 모든 전략을 처리할 수 있습니다.
3. 데이터베이스 기술은 스토어드 프로시저 정의 언어와 완전히 통합되어 있습니다.즉, 지원은 데이터베이스 엔진 내부에 있습니다.
4. 절차 언어를 사용하는 것을 두려워하지 않는 개발 팀이 있습니다(3위).결과를 얻기 위한 세대 언어).
5. 달성하고자 하는 작업은 데이터베이스 내에서 기본 제공 또는 지원됩니다(XML 데이터로 내보내기, 트리거, 스케줄링된 작업과의 데이터 무결성 및 일관성 관리 등).
6. 휴대성은 중요한 문제가 아닙니다.또한 테크놀로지의 변경을 단기간에 조직에 도입하는 것은 바람직하지 않습니다.일반적으로 애플리케이션 중심 및 계층 지향 개발자들에게 휴대성은 이정표처럼 여겨집니다.제 관점에서는 애플리케이션을 여러 플랫폼에 도입할 필요가 없거나 기술을 변경할 필요가 없거나 모든 조직 데이터를 이행하는 작업이 변경함으로써 얻을 수 있는 이점보다 더 많은 경우 이식성은 문제가 되지 않습니다.애플리케이션 계층에 의한 어프로치(휴대성)를 사용하면, 데이타베이스로부터 얻을 수 있는 퍼포먼스와 가치를 충분히 얻을 수 있습니다(시속 6000밀리 이하로 주행하는 페라리를 구입하는데 수천달러를 소비하는 이유).
7. 퍼포먼스가 문제입니다.첫째: 여러 경우 다른 응용 프로그램에서 데이터를 요청하는 것보다 하나의 저장 프로시저 호출을 사용하여 더 나은 결과를 얻을 수 있습니다.또한 수행해야 하는 일부 특성은 데이터베이스에 내장되어 있어 워크로드 측면에서 사용 비용이 저렴할 수 있습니다.애플리케이션 레이어 구동 솔루션을 사용하는 경우 데이터베이스 접속, 네트워크트래픽, 데이터 래핑(Java 또는 를 사용하는 등)에 관련된 비용을 고려해야 합니다.NET, JDBC/ADO를 사용하는 경우 암묵적인 비용이 발생합니다.데이터를 데이터베이스 데이터를 나타내는 오브젝트에 랩해야 하기 때문에 NET 콜에서는 데이터가 외부로 송수신 되었을 때의 처리, 메모리 및 네트워크 측면에서 인스턴스화에 관련된 비용이 발생합니다).

다음과 같은 경우 애플리케이션 계층 기반 솔루션을 사용하는 것이 더 공평해지는 경향이 있습니다.

1. 휴대성은 중요한 문제입니다.
2. 애플리케이션은 1개 또는 소수의 데이터베이스 저장소만 있는 여러 위치에 배포됩니다.
3. 어플리케이션에서는 기반이 되는 데이터베이스 테크놀로지에 의존하지 않는 비즈니스 지향의 규칙을 사용합니다.
4. 시장의 경향과 예산에 따라 테크놀로지 프로바이더를 변경해야 합니다.
5. 데이터베이스가 데이터베이스를 호출하는 저장 프로시저 언어와 완전히 통합되지 않았습니다.
6. 데이터베이스 기능은 한정되어 있으며, 데이터베이스 테크놀로지로 달성할 수 있는 것 이상의 요건을 갖추고 있습니다.
7. 어플리케이션은 외부 콜에 고유한 패널티를 지원할 수 있으며 비즈니스 고유의 규칙에 따라 트랜잭션 기반이며 사용자를 위한 비즈니스 모델로 데이터베이스 모델을 추상화해야 합니다.
8. 데이터베이스 운영의 병렬화는 중요하지 않으며 데이터베이스에는 병렬화 기능이 없습니다.
9. 데이터베이스 테크놀로지에 대한 충분한 훈련을 받지 않은 개발팀이 있으며 애플리케이션 기반 기술을 사용함으로써 생산성이 향상됩니다.

이것이 자신에게 어떤 것을 사용하는 것이 더 좋은지 자문하는 데 도움이 되시길 바랍니다.

스토어드 프로시저를 사용하지 않는 것이 좋습니다.

• MySQL에서 사용하는 언어는 매우 엉망입니다.
• 스토리지 프로시저에 어레이, 목록 또는 기타 유형의 데이터 구조를 전송할 수 없습니다.
• 스토어드 프로시저는 인터페이스를 변경할 수 없습니다.MySQL은 이름 있는 파라미터도 옵션 파라미터도 허용하지 않습니다.
• 새로운 버전의 애플리케이션 도입이 복잡해집니다.예를 들어, 애플리케이션 서버 10대와 데이터베이스 2대를 사용하고 있는 경우, 어떤 것을 먼저 업데이트하시겠습니까?
• 귀사의 개발자는 모두 스토어드 프로시저 언어를 배우고 이해할 필요가 있습니다.이것은 매우 엉망입니다(앞에서 말씀드린 바와 같습니다).

대신 레이어/라이브러리를 생성하여 모든 쿼리를 거기에 저장하는 것이 좋습니다.

넌 할 수 있다.

• 이 라이브러리를 업데이트하고 앱과 함께 앱 서버로 보내십시오.
• 어레이, 구조 등 풍부한 데이터 타입이 배포되어 있다
• 저장 프로시저 대신 이 라이브러리를 유닛 테스트합니다.

---

퍼포먼스:

• 스토어드 프로시저를 사용하면 어플리케이션 개발자의 퍼포먼스가 저하됩니다.이것이 가장 중요합니다.
• 복잡한 저장 프로시저 내에서 성능 문제를 식별하는 것은 매우 어렵습니다(일반 쿼리의 경우 훨씬 더 쉽습니다).
• 회선을 통해 단일 청크로 쿼리 배지를 전송할 수 있습니다(CLIENT_MULTI_STATEMENTS 플래그가 활성화된 경우). 즉, 저장 프로시저가 없으면 더 이상 지연이 발생하지 않습니다.
• 일반적으로 애플리케이션 측 코드는 데이터베이스 측 코드보다 확장성이 우수합니다.

데이터베이스가 복잡하고 응답이 있는 포럼 유형이 아닌 진정한 웨어하우징 SP가 분명히 도움이 될 것입니다.모든 비즈니스 로직을 사용할 수 있으며, 단 한 명의 개발자도 신경 쓰지 않습니다. SP에 전화하기만 하면 됩니다.저는 15개 이상의 테이블에서 이 결합을 하고 있는데, 당신은 새로운 개발자에게 이것을 설명할 수 없습니다.

개발자도 DB에 액세스할 수 없습니다. 좋습니다.데이터베이스 설계자와 유지관리자에게 맡겨주세요.테이블 구조가 변경된다고 판단되면 인터페이스 뒤에 숨길 수 있습니다.n-Tier, 기억나요?

MySQL InnoDB가 느리더라도 MyISAM은 이제 창밖으로 던져져야 합니다.웹 앱의 성능이 필요한 경우 적절한 캐시, 메모리 캐시 등이 필요합니다.

당신의 경우, 당신이 'Web'을 언급했기 때문에 저장 프로시저를 사용하지 않을 것입니다.데이터 웨어하우스라면 반드시 검토하겠습니다(저희 창고에 SP를 사용합니다).

힌트: 웹 프로젝트를 언급하신 후 nosql의 솔루션에 대해 생각해 보신 적이 있습니까?또한 빠른 DB가 필요하므로 PostgreSQL을 사용하는 것이 어떻습니까? (여기를 지지하려고 합니다.)

이전에는 MySql을 사용했지만 SQL에 대한 이해가 부족했습니다.Sql Server를 사용하는 데 상당한 시간이 걸렸고 데이터 계층과 애플리케이션 계층을 명확하게 구분하여 현재 0.5테라바이트의 서버를 관리하고 있습니다.

스토어드 프로시저는 개발이 매우 빠르기 때문에 ORM을 사용하지 않는 것이 답답할 때가 있습니다.ORM을 사용하면 작업 속도를 높일 수 있을 것 같습니다.

When your application reaches critical mass, the ORM performance will suffer, a well written stored procedure, will give you your results faster.

As an example of performance I collect 10 different types of data in an application, then convert that to XML, which I process in the stored procedure, I have one call to the database rather than 10.

Sql is really good at dealing with sets of data, one thing that gets me frustrated is when I see someone getting data from sql in a raw form and using application code to loop over the results and format and group them, this really is bad practice.

My advice is to learn and understand sql enough and your applications will really benefit.

Lots of info here to confuse people, software development is a evolutionary. What we did 20 years ago isn't best practice now. Back in the day with classic client server you wouldnt dream of anything but SPs.

It is absolutely horses for courses, if you are a big organisation with you will use multi tier, and probably SPs but you will care little about them because a dedicated team will be sorting them out.

The opposite which is where I find myself trying to quickly knock up a web app solution, that fleshes out business requirements, it was super fast to leave the developer (remote to me) to knock up the pages and SQL queries and I define the DB structure.

However complexity is growing and without an easy way to provide APIs, I am staring to use SPs to contain the business logic. I think it is working well and sensible, I control this because I can build logic and provide a simple result set for my offshore developer to build a front end around.

Should I find my software a phenomenal success, then more separation of concerns will occur and different implementations of n teir will come about but for now SPs are perfect.

You should know all the tool sets available to you and match them is wise to start with. Unless you are building an enterprise system to start with then fast and simple is best.

I would recommend that you stay away from DB specific Stored Procedures.

I've been through a lot of projects where they suddently want to switch DB platform and the code inside a SP is usually not very portable = extra work and possible errors.

Stored Procedure development also requires the developer to have access directly to the SQL-engine, where as a normal connection can be changed by anyone in the project with code-access only.

Regarding your Model/layer/tier idea: yes, stick with that.

• Website calls Business layer (BL)
• BL calls Data layer (DL)
• DL calls whatever storage (SQL, XML, Webservice, Sockets, Textfiles etc.)

This way you can maintain the logic level between tiers. IF and ONLY IF the DL calls seems to be very slow, you can start to fiddle around with Stored Procedures, but maintain the original none-SP code somewhere, if you suddently need to transfer the DB to a whole new platform. With all the Cloud-hosting in the business, you never know whats going to be the next DB platform...

I keep a close eye on Amazon AWS of the very same reason.

I think there is a lot of misinformation floating around about database stored queries.

I would recommend using MySQL Stored Procedures if you're doing many static queries for data manipulation. Especially if you're moving things from one table to another (i.e. moving from a live table to a historical table for whatever reason). There are drawbacks of course in that you'll have to keep a separate log of changes to them (you could in theory make a table that just holds changes to the stored procedures that the DBA's update). If you have many different applications interfacing with the database, especially if say you have a desktop program written in C# and a web program in PHP, it might be more beneficial to have some of your procedures stored in the database as they are platform independent.

This website has some interesting information on it you may find useful.

https://www.sitepoint.com/stored-procedures-mysql-php/

As always, build in a sandbox first, and test.

Try to update 100,000,000 records on a live system from a framework, and let me know how it goes. For small apps, SPs are not a must, but for large serious systems, they are a real asset.

ReferenceURL : https://stackoverflow.com/questions/6368985/mysql-stored-procedures-use-them-or-not-to-use-them