Can MySQL only be used for small projects? It’s time to say some justice!

When I was in school, many people had some prejudices against MySQL. The prejudices were mainly concentrated in the following aspects:

1. MySQL does not support transactions (in fact, MyISAM does Table lock, but the efficiency is relatively low)

2. The amount of data stored in MySQL is relatively small, which is suitable for small projects. For large projects, Oracle, DB2, etc.

After so many years, I myself My development has always been based on MySQL, so I think I need to say a few words of justice.

Fair words

The first question

Regarding the first issue of not supporting transactions, there are certain historical reasons for this. From the beginning of MySQL's design, the storage engine has been pluggable, allowing companies or individuals to define their own storage engines according to their own needs (of course, ordinary companies or individuals do not actually have this capability). The most widely used storage engine developed by MySQL is MyISAM. MyISAM supports table locks but does not support row locks, so it is less efficient when dealing with high concurrent write operations. In addition, MyISAM does not support foreign keys (although foreign keys are now used in actual projects). Already used less).

But this problem is not unsolvable. This has to be said about InnoDB, another famous storage engine in MySQL.

The InnoDB storage engine was developed by a company called Innobase Oy in Helsinki, Finland. The InnoDB storage engine has a history even longer than MySQL.

When InnoDB was first developed, it was developed as a complete database, so its functions are very complete. After it was developed, the founder wanted to sell the database, but no buyer was found.

Later, after MySQL2.0 was launched, this pluggable storage engine attracted the attention of Heikki Tuuri, the founder of Innobase Oy. After communicating with MySQL, he decided to introduce InnoDB as a storage engine into MySQL. Although MySQL supports InnoDB, it actually mainly promotes its own MyISAM.

But InnoDB was so good that in 2006, it successfully attracted the attention of the big devil Oracle, and acquired InnoDB with a wave of its hand.

MySQL mainly promoted its own MyISAM, and its life was very bleak. It was finally acquired by Sun for US$1 billion in 2008. This operation consolidated Sun's position as the leader in the open source field, but Sun has always been The company's liquidity was relatively weak, and eventually Sun itself was acquired by Oracle in 2009. I was still in high school at that time. One day when I was having lunch, CCTV’s noon news was played on the TV in the restaurant. I saw this news and I still have some impressions of it.

After Oracle acquired Sun, InnoDB and MySQL became Oracle products, and the integration became very easy. In later versions, InnoDB gradually became the default storage of MySQL. engine. In the latest MySQL8, the metadata table also uses InnoDB as the storage engine.

InnoDB storage engine mainly has the following features:

1. Supports transactions

2. Supports 4 levels of transaction isolation

3. Supports multiple versions Read

4. Supports row-level locks

5. Read and write blocking is related to transaction isolation level

6. Supports caching, which can cache both indexes and data

7. The entire table and primary key are stored in Cluster mode to form a balanced tree

8. ...

Of course, this does not mean that InnoDB is necessarily good. In practice, During development, you still have to choose whether to use InnoDB or MyISAM based on specific scenarios.

So the first question is self-defeating.

The second question

The second question is indeed a flaw.

If you compare MySQL with Oracle, it will definitely feel a little worse. After all, one is free and the other is charged, and the charged ones are very expensive. But this problem is not unsolvable.

I believe many friends have heard that many major domestic manufacturers use MySQL to store data. Large manufacturers use MySQL because they have the ability to develop their own storage engines. Small manufacturers generally do not have this strength and cannot develop their own storage engines, but Oracle cannot afford it, so what should we do?

The distributed database middleware that has emerged in recent years can just solve this problem very well. In the Java field, there are many similar tools, such as Sharding-JDBC, MyCat, etc. Through these tools, database sub-databases and tables can be well realized, as well as dynamic expansion of data tables, read-write separation, distributed transaction resolution, etc. With these tools, the application scenarios of MySQL have been greatly improved.

On the other hand, microservices have become popular in recent years. This is not a simple concept. The microservice architecture splits a large project into many small microservices. Each microservice handles its own small tasks. For some things, this is more in line with the characteristics of human division of labor and cooperation. In the microservice architecture, our need for large tables and joint queries on multiple tables will be reduced, and MySQL will be more useful.

Therefore, the second problem can also be solved.

As far as I know, many Internet companies use MySQL. Traditional software companies may prefer databases such as Oracle.

But having said that, cloud computing is also a direction in the future.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Can MySQL only be used for small projects? It’s time to say some justice!. For more information, please follow other related articles on the PHP Chinese website!