MySQL 저장 프로시 저는이를 사용하거나 사용하지 않습니다.

MySQL 저장 프로시 저는이를 사용하거나 사용하지 않습니다.

---

우리는 새로운 프로젝트의 시작 단계에 있으며 MySQL에서 저장 프로 시저를 사용해야하는지 여부가 궁금합니다.

저장 프로시 저는 비즈니스 모델 엔터티를 삽입하고 업데이트하는 데만 사용합니다. 모델 엔터티를 나타내는 여러 테이블이 있으며 해당 저장 프로 시저 삽입 / 업데이트에서 추상화합니다.

반면에 MySQL이 아닌 PHP에서 모델 계층에서 삽입 및 업데이트를 호출 할 수 있습니다.

경험상 가장 좋은 선택은 무엇입니까? 두 접근 방식의 장점과 단점. 고성능 측면에서 가장 빠른 것은 무엇입니까?

추신 : 대부분 읽기가 많은 웹 프로젝트이며 고성능이 가장 중요한 필수 조건입니다.

---

실제 프로그래밍 언어 코드와 달리 다음을 수행합니다.

• 이식 불가능 (모든 db에는 고유 한 버전의 PL / SQL이 있습니다. 때로는 동일한 데이터베이스 의 다른 버전 이 호환되지 않는 경우가 있습니다. 본 적이 있습니다!)
• 쉽게 테스트 할 수 없음-테스트하려면 실제 (dev) 데이터베이스 인스턴스가 필요 하므로 빌드의 일부로 코드를 단위 테스트하는 것은 사실상 불가능합니다.
• 쉽게 업데이트 / 해제 할 수 없음-삭제 / 생성해야합니다. 즉 , 프로덕션 DB를 수정 하여 해제해야합니다.
• 라이브러리 지원이 없음 (다른 사람이있는 경우 코드를 작성하는 이유)
• 다른 기술과 쉽게 통합 할 수 없습니다 (웹 서비스를 호출 해보십시오).
• 그들은 Fortran과 같은 원시적 인 언어를 사용하므로 유용한 코딩을 수행하는 데 우아하지 않고 힘들 기 때문에 일반적으로 그것이 주요 목적이지만 비즈니스 로직을 표현하기가 어렵습니다.
• 디버깅 / 추적 / 메시지 로깅 등을 제공하지 마십시오 (일부 dbs는 이것을 지원할 수 있습니다-나는 그것을 본 적이 없습니다)
• 구문 및 다른 기존 절차에 대한 링크를 지원하는 적절한 IDE가 부족합니다 (예 : Eclipse가 Java에서 수행하는 것과 같음)
• 코딩에 능숙한 사람들은 앱 코더보다 드물고 비싸다
• 이들의 "고성능"은 신화입니다. 데이터베이스 서버에서 실행되기 때문에 일반적으로 db 서버로드가 증가 하므로 일반적으로이를 사용하면 최대 트랜잭션 처리량이 감소 합니다.
• 상수를 효율적으로 공유 할 수 없음 (일반적으로 테이블을 만들고 절차 내에서 질문하여 해결됨-매우 비효율적 임)
• 기타

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

"고성능"을 사전에 지정할 때는주의가 필요합니다. 좋은 디자인을 희생시키면서 잘못된 선택으로 이어지는 경우가 많으며 생각보다 훨씬 빨리 물릴 것입니다.

자신의 위험에 처한 저장 프로 시저를 사용하십시오 (그곳에 있었지만 결코 돌아가고 싶지 않은 사람). 내 추천은 전염병처럼 그들을 피하는 것입니다.

---

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

• SQL 주입 공격을 거의 불가능하게 렌더링 ( 프로 시저 내에서
  동적
  SQL을 구성하고 실행 하지 않는 한 )
• 콜 아웃의 일부로 IPC 를 통해 전송되는 데이터가 훨씬 적습니다.
• 데이터베이스를 훨씬 더 나은 캐시 계획 및 결과 집합으로 설정 (내부 캐싱 구조로 인해 MySQL에서는 그다지 효과적이지 않음)
• 격리 상태에서 쉽게 테스트 할 수 있습니다 (예 : JUnit 테스트의 일부가 아님).
• 프로 시저 이름 뒤에 추상화 된 db 관련 기능을 사용할 수 있다는 의미에서 이식 가능합니다 (코드에서는 일반적인 SQL 유형의 항목에 갇혀 있음).
• 코드에서 호출되는 SQL보다 거의 느리지 않습니다.

그러나 보헤미안이 말했듯이 많은 단점도 있습니다 (이것은 다른 관점을 제공하는 것입니다). 자신에게 가장 적합한 것을 결정하기 전에 벤치마킹해야 할 것입니다.

---

성능에 관해서 는 향후 MySQL 버전에서 실제로 성능 을 발휘할 수있는 잠재력이 있습니다 (SQL Server 또는 Oracle에서 실제 대접입니다!). 하지만 나머지는 ... 그들은 완전히 경쟁을 날려 버렸습니다. 요약하겠습니다.

• 보안 : 앱에 EXECUTE 권한 만 부여 할 수 있으며 모든 것이 정상입니다. SP는 어떤 종류의 누출 가능성도없이 update select ...를 삽입합니다. 이는 모델에 대한 글로벌 제어 및 강제 데이터 보안을 의미합니다.

• 보안 2 : 드물다는 것을 알고 있지만 때때로 PHP 코드가 서버에서 유출됩니다 (즉, 공개적으로 표시됨). 쿼리가 포함 된 경우 가능한 공격자가 모델을 알고 있습니다. 이건 좀 이상하지만 어차피 신호를 보내고 싶었어

• 태스크 포스 : 예, 효율적인 SQL SP를 생성하려면 특정 리소스가 필요하며 때로는 더 비쌉니다. 그러나 클라이언트에서 쿼리를 통합하기 때문에 이러한 리소스가 필요하지 않다고 생각하면 심각한 문제가 발생할 것입니다. 웹 개발의 비유에 대해 언급하고 싶습니다. 디자이너가 자신의 기술로 작업 할 수있는 반면 프로그래머는 비즈니스 계층을 프로그래밍하는 데 집중할 수 있기 때문에 뷰를 나머지와 분리하는 것이 좋습니다.

• 비즈니스 계층 캡슐화 : 저장 프로 시저를 사용하면 비즈니스가 속한 비즈니스 인 망할 데이터베이스가 완전히 격리됩니다.

• 빠르게 테스트 가능 : 셸 아래에 하나의 명령 줄과 코드가 테스트됩니다.

• 클라이언트 기술로부터의 독립성 : 내일 PHP에서 다른 것으로 전환하고 싶다면 문제 없습니다. 좋아, 이러한 SQL을 별도의 파일에 저장하는 것만으로도 트릭을 수행 할 수 있습니다. 맞습니다. 또한 SQL 엔진을 전환하기로 결정했다면 할 일이 많을 것이라는 의견에서 좋은 점이 있습니다. 어쨌든 큰 프로젝트와 대기업의 경우 거의 발생하지 않기 때문에 (대부분 비용 및 HR 관리로 인해) 그렇게 할 충분한 이유가 있어야합니다.

• 애자일 3+ 계층 개발 적용 : 데이터베이스가 클라이언트 코드와 동일한 서버에 있지 않은 경우 다른 서버가있을 수 있지만 데이터베이스에 대해 하나만있을 수 있습니다. 이 경우 SQL 관련 코드를 변경해야 할 때 PHP 서버를 업그레이드 할 필요가 없습니다.

좋아요, 그게 제가 주제에 대해 말한 가장 중요한 것 같아요. 나는 두 가지 정신 (SP 대 클라이언트)으로 개발했으며 SP 스타일을 정말 좋아합니다. 나는 단지 Mysql이 그들에게 진짜 IDE를 가지고 있기를 바랬다. 왜냐하면 지금 당장 은 제한된 엉덩이에 일종의 고통 이기 때문이다 .

---

저장 프로시 저는 쿼리를 구성하고 일괄 처리를 한 번에 수행 할 수 있기 때문에 사용하기 좋습니다. 저장 프로시 저는 실행될 때마다 컴파일되는 쿼리와 달리 미리 컴파일되기 때문에 일반적으로 실행 속도가 빠릅니다. 이는 데이터베이스가 원격 서버에있는 상황에서 중요한 영향을 미칩니다. 쿼리가 PHP 스크립트에있는 경우 애플리케이션과 데이터베이스 서버간에 여러 통신이 있습니다. 쿼리가 전송되고 실행되고 결과가 반환됩니다. 그러나 저장 프로 시저를 사용하는 경우 크고 복잡한 쿼리 대신 작은 CALL 문만 보내면됩니다.

자체 언어와 구문이 있기 때문에 저장 프로 시저 프로그래밍에 적응하는 데 시간이 걸릴 수 있습니다. 그러나 익숙해지면 코드가 정말 깨끗하다는 것을 알 수 있습니다.

성능 측면에서 저장 프로 시저를 사용하거나 사용하지 않으면 큰 이득이 아닐 수 있습니다.

---

내 강인함이 질문과 직접 ​​관련이 없을 수도 있지만 내 의견을 알려줄 것입니다. :

많은 문제에서와 마찬가지로 저장 프로 시저 또는 응용 프로그램 계층 기반 솔루션 사용에 대한 답변은 전반적인 노력을 유도하는 질문에 의존합니다.

• 당신이 얻고 싶은 것.

배치 작업 또는 온라인 작업을 수행하려고합니까? 완전히 거래입니까? 이러한 작업은 얼마나 반복됩니까? 데이터베이스에 대해 대기중인 워크로드가 얼마나 무겁습니까?

• 그것을 얻기 위해 가지고있는 것.

어떤 종류의 데이터베이스 기술을 가지고 있습니까? 어떤 종류의 인프라? 귀하의 팀은 데이터베이스 기술에 대해 완전히 교육을 받았습니까? 당신의 팀은 데이터베이스와 무관 한 솔루션을 더 잘 구축 할 수 있습니까?

• 그것을 얻을 시간.

그것에 대한 비밀은 없습니다.

• 건축물.

솔루션을 여러 위치에 배포해야합니까? 원격 통신을 사용하려면 솔루션이 필요합니까? 솔루션이 여러 데이터베이스 서버에서 작동하고 있습니까? 아니면 클러스터 기반 아키텍처를 사용하고 있습니까?

• 유지 관리.

변경하려면 애플리케이션이 얼마나 필요합니까? 솔루션 유지를 위해 특별히 훈련 된 개인이 있습니까?

• 변경 관리.

귀하의 데이터베이스 기술이 단기, 중, 장기에 변할 것이라고 생각하십니까? 솔루션을 자주 마이그레이션해야한다고 생각하십니까?

• 비용

하나 또는 다른 전략을 사용하여 해당 솔루션을 구현하는 데 드는 비용은 얼마입니까?

이러한 포인트의 전체가 답을 이끌어 낼 것입니다. 따라서 전략을 사용할지 여부를 결정할 때이 각 사항을주의해야합니다. 저장 프로 시저를 사용하는 것이 응용 프로그램 계층 관리 쿼리보다 나은 경우가 있으며 쿼리를 수행하고 응용 프로그램 계층 기반 솔루션을 사용하는 것이 가장 좋은 경우가 있습니다.

다음과 같은 경우 저장 프로 시저를 사용하는 것이 더 적절합니다.

1. 데이터베이스 기술은 단기간에 변경하도록 제공되지 않습니다.
2. 데이터베이스 기술은 병렬화 된 작업, 테이블 파티션 또는 워크로드를 여러 프로세서, 메모리 및 리소스 (클러스터링, 그리드)로 분할하기위한 기타 전략을 처리 할 수 ​​있습니다.
3. 데이터베이스 기술은 저장된 proceduce 정의 언어와 완전히 통합됩니다. 즉, 데이터베이스 엔진 내부에 지원이 있습니다.
4. 결과를 얻기 위해 절차 적 언어 (3 세대 언어)를 사용하는 것을 두려워하지 않는 개발 팀이 있습니다.
5. 달성하려는 작업은 데이터베이스 내에서 기본 제공되거나 지원됩니다 (XML 데이터로 내보내기, 트리거, 예약 된 작업 등을 통해 적절하게 데이터 무결성 및 일관성 관리).
6. 이식성은 중요한 문제가 아니며 짧은 시간에 조직에 기술이 변경되지는 않지만 바람직하지 않습니다. 일반적으로 이식성은 응용 프로그램 중심 및 계층 중심 개발자의 이정표처럼 보입니다. 내 관점에서, 이식성은 애플리케이션을 여러 플랫폼에 배포 할 필요가 없을 때 문제가되지 않으며, 기술을 변경할 이유가 없거나 모든 조직 데이터를 마이그레이션하기위한 노력이 변화에 따른 이점. 애플리케이션 계층 기반 접근 방식 (이동성)을 사용하여 얻을 수있는 것은 데이터베이스에서 얻은 성능과 가치를 잃을 수 있습니다 (시간당 60 밀리 초 이하의 성능을 발휘할 수있는 페라리를 얻기 위해 수천 달러를 지출해야하는 이유 ?).
7. 성능이 문제입니다. 첫째 : 여러 경우에 다른 응용 프로그램의 데이터에 대한 여러 요청보다 단일 저장 프로 시저 호출을 사용하여 더 나은 결과를 얻을 수 있습니다. 또한 수행해야하는 일부 특성은 데이터베이스에 내장되어 있으며 워크로드 측면에서 사용 비용이 저렴할 수 있습니다. 애플리케이션 계층 기반 솔루션을 사용하는 경우 데이터베이스 연결, 데이터베이스 호출, 네트워크 트래픽, 데이터 래핑 (예 : Java 또는 .NET을 사용하는 경우)과 관련된 비용을 고려해야합니다. 데이터베이스 데이터를 나타내는 개체로 데이터를 래핑해야하므로 JDBC / ADO.NET 호출을 사용하므로 인스턴스화는 데이터가 외부로 들어오고 나갈 때 처리, 메모리 및 네트워크 측면에서 관련 비용이 발생합니다.

Using of application-layer driven solutions tends to be more addequate when:

1. Portability is an important issue.
2. Application will be deployed onto several locations with only one or few database repositories.
3. Your application will use heavy business-oriented rules, that need to be agnostic of the underlying database technology.
4. You have in mind to do change technology providers based on market tendencies and budget.
5. Your database isn't fully integrated with the stored procedure language that calls to the database.
6. Your database capabilities are limited and your requirement goes beyond what you can achieve with your database technology.
7. Your application can support the penalty inherent to external calls, is more transactional-based with business-specific rules and has to abstract the database model onto a business model for the users.
8. Parallelizing database operations isn't important, moreover, your database has not parallelization capabilities.
9. You have a development team which is not well-trained onto the database technology and is better productive by using an application-driven based technology.

Hope this may help to anyone asking himself/herself what is better to use.

---

I would recommend you don't use stored procedures:

• Their language in MySQL is very crappy
• There is no way to send arrays, lists, or other types of data structure into a stored procedure
• A stored procedure cannot ever change its interface; MySQL permits neither named nor optional parameters
• It makes deploying new versions of your application more complicated - say you have 10x application servers and 2 databases, which do you update first?
• Your developers all need to learn and understand the stored procedure language - which is very crap (as I mentioned before)

Instead, I recommend to create a layer / library and put all your queries in there

You can

• Update this library and ship it on your app servers with your app
• Have rich data types, such as arrays, structures etc passed around
• Unit test this library, instead of the stored procedures.

---

On performance:

• Using stored procedures will decrease the performance of your application developers, which is the main thing you care about.
• It is extremely difficult to identify performance problems within a complicated stored procedure (it is much easier for plain queries)
• You can submit a query batch in a single chunk over the wire (if CLIENT_MULTI_STATEMENTS flag is enabled), which means you don't get any more latency without stored procedures.
• Application-side code generally scales better than database-side code

---

If your database is complex and not a forum type with responses, but true warehousing SP will definitely benefit. You can out all your business logic in there and not a single developer is going to care about it, they just call your SP's. I have been doing this joining over 15 tables is not fun, and you cannot explain this to a new developer.

Developers also don't have access to a DB, great! Leave that up to database designers and maintainers. If you also decide that the table structure is going to get changed, you can hide this behind your interface. n-Tier, remember??

High performance and relational DB's is not something that goes together, not even with MySQL InnoDB is slow, MyISAM should be thrown out of the window by now. If you need performance with a web-app, you need proper cache, memcache or others.

in your case, because you mentioned 'Web' I would not use stored procedures, if it was data warehouse I would definitely consider it (we use SP's for our warehouse).

Tip: Since you mentioned Web-project, ever though about nosql sort of solution? Also, you need a fast DB, why not use PostgreSQL? (trying to advocate here...)

---

I used to use MySql and my understanding of sql was poor at best, I spent a fair amount of time using Sql Server, I have a clear separation of a data layer and an application layer, I currently look after a server with 0.5 terabytes.

I have felt frustrated at times not using an ORM as development is really quick with stored procedures it is much slower. I think much of our work could have been sped up by using an 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.

---

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.

---

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 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.

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