1 Overburdened database

Fatty Chang’s database is already overwhelmed.

This system was first written by two interns. According to the original design, it was only for internal users to play, and people could exchange some unused things on it. Later, in order to make some money in the tide of the Internet, it was wrapped with a layer of Web coat, so that the outside world can also access it.

People didn’t expect that the power of the Internet would be so huge and the number of users would be so large that the Mysql database used by their system would soon collapse.

The zhang big fat that serves as technical director early already applied to eldest brother brushstroke charge, bought the server of a high performance specially to deal with, but the user that surging but comes soon gives high performance to eat to connect dregs all do not remain.

Zhang Big fat anxiety-ridden: “eldest brother, how to do?”

The eldest, also a technician, asked, “Have you analyzed why the database is under so much pressure?”

“It’s just too much reading and writing, especially for very complex queries like the most popular items in the last 24 hours or something like that, where you have to write complex SQL and it’s just too slow to run.”

“I thought we were talking about reading and writing separation. Why don’t we try it?”

“Boss, you don’t know, this is really difficult to deal with, in order to achieve the separation of reading and writing, we have to split the database into master library and slave library. It is relatively easy, but we also have to change the system code, write data using master library, read data using Slave library. You know, we have a system from the last century, typical legacy code, and it’s too cumbersome to change.”

The boss said, “Well, that has to change. You know, this system is the biggest source of revenue for our company now. If you don’t want to change, you can quit. I have to go to Li Xiaofeng to do it.”

Zhang Big fat always looks down upon sycophant Li small crazy, technology not zha ground, rise to quite fast, enter a company together, already compare oneself tall one class now.

Zhang Big fat hurriedly say: “don’t don’t, or I come”

Dapuan zhang with several brothers and legacy code struggle for several months, the workload is no less than a rewrite. Zhang Fat deeply realized that, although the existing code is very bad, but after countless people’s repair, barely able to work. Now they write again from scratch, the problem is more, a lot of small details are not considered, was measured out countless bugs.

But the benefits were huge. The rewrite clarified the business, implemented read/write separation, and also used caching. Finally, after two days and nights, the new system went live.

Zhang Big fat thinking good days will begin, new code, new system, should be able to support for a period of time.

2 Complex query

But a week after the launch of the new system, the problem appeared again, this time mainly focused on some complex SQL queries, the most deadly SQL queries have dozens of rows! Seriously dragging down the database!

Zhang Dapang asked DBA Xiaoliang to do optimization. After looking at it for a long time, Xiaoliang said: “No way, your business is too complicated, you see there are so many tables doing Join, how can it be fast?”

Zhang big fat say: “this have no way, database is so design of, you understand of, no matter how also get satisfaction first normal form. Why don’t you create a View that encapsulates this complex query so we can use it easily?”

“That is to change the same thing, ah, the actual query is still in, no essential change, still slow.”

“Oh, how can we do this, we have more than 20 complex queries, how can we speed up?

Hot_products (ID, name, desc, total_sold) hot_products(id, name, desc, total_sold) is a simple SQL query.

Xiao Liang’s words inspired Zhang Dabang: in fact, a single set of database tables for statements, searches, transactions and other different behavior is not appropriate!

Now complex data query and simple data modification are using the same domain model and database tables, the current database tables are mainly for adding and modifying data and are not friendly to complex queries. Can we build a separate database for queries?

With this special query library, users in the interface to initiate a query when the processing is very simple, a SQL is done, not even through the business domain layer, in other words, the database model and the presentation layer is corresponding! No more getting data from raw database tables, converting it to domain objects, and then to presentation layer objects, as was the case before!

But how can this specialized query library be updated? More importantly, can you tolerate data delays?

3 CQRS

Zhang Big fat to his own ideas and distress to the boss.

The eldest brother patted his shoulder: “it seems that you boy enlightened, think quite in-depth, from the business to see the data delay can bear, such as the hot product in the past 24 hours, a little outdated data will not have a significant impact on users. As long as you can reach a final agreement.”

“So how do we update this specialized query library?”

“I’ve been looking at something called CQRS,” the boss said. “This problem you’ve got can be solved in the same way.”

“What is CQRS?”

“Command Query Responsibility Segregation of commands and queries, if you look at this diagram.”

“This is similar to the picture I just drew,” Zhang dapu said

“So the idea is the same. In CQRS, the emphasis is on the separation of Query and Command. The idea behind it is that the data that users read is usually outdated, like the hottest product in the last 24 hours, so why read it from the database and convert it into a domain model? DTO, VO, and finally at the UI level? Why not be straightforward and create a direct data source for ‘reads’? This new data source doesn’t have to be a relational database, it can be a Cache and can be stored directly as XML/JSON data, as long as the interface is easy to query.”

“Yes, that’s what I thought at first. What about this Event?”

“Event is an Event. For example, someone places an order and a product has been sold. In this case, a ProductSold Event can be released, which contains the PRODUCT ID, price, selling time and other attributes. It can become any Read Model, like the hottest product of the last 24 hours.”

“One more question, if we use CQRS, does our application need to completely separate all commands and queries from the new data source? But many queries are simple enough to use a relational database.”

“No, don’t overdo it. There’s a price to pay for introducing a new technology. We’re turning synchronous operations into asynchronous operations. So use that to solve your current problem, which is a complex query.” The boss finally claps the board.

(after)

What you see is just the tip of the iceberg, more wonderful articles, please move to “code farmers turn over 2016 article essence” or “code farmers turn over the first half of 2017 article essence”

Any tips you’d like to share? Welcome to contribute! My contact information: Wechat: Liuxinlehan QQ: 3340792577

Code farmers turn

Use storytelling techniques