When Songo was in school, there were a lot of prejudices about MySQL, mainly in the following aspects:

  1. MySQL does not support transactions (actually MyISAM has table locks, but it is inefficient)
  2. MySQL stores a relatively small amount of data, suitable for small projects, large projects or Oracle, DB2, etc

After all these years, Songo’s own development has been centered around MySQL, and I feel I need to be fair.

To be fair

First question

Regarding the first issue of not supporting transactions, there is a historical reason for this. MySQL was designed from the very beginning to be pluggable, allowing companies and individuals to customize their own storage engines to suit their own needs (of course, the average company or individual does not have this capability). MyISAM, a storage engine developed by MySQL, is widely used. MyISAM supports table locks rather than row locks, so it is less efficient in handling high concurrent write operations. In addition, MyISAM does not support foreign keys (although foreign keys are rarely used in actual projects).

But the problem is not insoluble. This brings us to InnoDB, another well-known storage engine in MySQL.

The InnoDB storage engine, developed by Innobase Oy, a company based in Helsinki, Finland, is even older than MySQL.

When InnoDB was first developed, it was developed as a complete database, so it has complete functions. Once developed, the founders tried to sell the database, but failed to find a buyer.

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

But InnoDB was so good that it finally caught the eye of Oracle in 2006 and snapped InnoDB up.

MySQL, which launched MyISAM, floundered, eventually being bought by Sun for $1 billion in 2008. The deal cemented Sun’s position as the leader in open source, but the company’s liquidity has been weak. Sun itself was eventually acquired by Oracle in 2009. At that time, Songge was still in high school. One day when he had lunch, the TV in the dining room broadcast the NOON news of CCTV. I saw this news and still have some impressions.

After Oracle acquired Sun, InnoDB and MySQL became Oracle products, which made integration easier, and InnoDB gradually became the default storage engine for MySQL in later releases. In the latest MySQL8, InnoDB is also used as the storage engine for metadata tables.

InnoDB storage engine has the following features:

  1. Support transactions
  2. Four levels of transaction isolation are supported
  3. Multi-version reading is supported
  4. Row-level locking is supported
  5. Read/write blocking is related to the transaction isolation level
  6. Support caching, can cache index, also can cache data
  7. The entire table and primary keys are stored in a Cluster, forming a balanced tree
  8. .

Of course, InnoDB is not always good. In actual development, it is up to the specific scenario to choose whether to use InnoDB or MyISAM.

So the first problem is solved.

Second question

The second question is a real sore spot.

If you compare MySQL to Oracle, it’s a little bit worse. After all, one charge is free, and the charge is very expensive. But the problem is not insoluble.

I believe many friends have heard that many large factories in China have used MySQL to store data. Big companies use MySQL because they have the ability to develop their own storage engine. Small companies usually don’t have the ability to develop their own storage engine, but Oracle can’t afford it. So what do you do?

In recent years, the rise of distributed database middleware can just solve this problem. In the field of Java, there are many similar tools, such as Sharding-JDBC, MyCat, etc., through these tools, you can achieve database sub-database sub-table, as well as dynamic expansion of data table, read and write separation, distributed transaction solution, etc. With these tools, the application scenario of MySQL has been greatly improved.

On the other hand, micro-services have become popular in recent years, which is not a simple speculation concept. Micro-service architecture divides a large project into many small micro-services, and each micro-service handles a small part of its own affairs, which is more in line with the characteristics of human division of labor and cooperation. In a microservice architecture, we have less need for large tables, less need for joint queries with multiple tables, and more use of MySQL.

Therefore, the second problem can also be solved.

According to Songge, Internet companies use MySQL or more, traditional software companies, may prefer Oracle and other databases.

Then again, cloud computing is the future.

conclusion

Why write this article? Because Songo plans to publish several articles on the use of distributed database middleware, MyCat and Sharding-JDBC, to make your MySQL truly comparable to large databases. This article is a primer.

Then Songo will update MyCat first.

Pay attention to the public account [Jiangnan little Rain], focus on Spring Boot+ micro service and front and back end separation and other full stack technology, regular video tutorial sharing, after attention to reply to Java, get Songko for you carefully prepared Java dry goods!