MySQL Autobiography

Introduction: I am a hard-working little dolphin. My online name is MySQL. I was born on May 23, 1995. I am an authentic post-95s generation. Don't underestimate me. I am now the most popular open source database in the world. There are 800 databases in the world. 10,000 instances.
I have consulted a lot of reference materials for writing this article, and have also received guidance and help from many friends. Special thanks to:

Jimmy Yang - Alibaba Cloud Database Researcher, former Oracle InnoDB Architect.
Peng Lixun - Chief Engineer of Cloud Database, MySQL ACE Director, one of the founding members of MySQL China User Group.
Tan Yu - Chief Architect of Jiuzhang Arithmetic, former Alibaba technical director, head of database intelligent operation and maintenance R&D, member of the founding team of OceanBase.

"The fledgling"


I am a hard-working little dolphin. My online name is MySQL. I was born on May 23, 1995. I am an authentic post-95s generation. Don't underestimate me. I am now the most popular open source database in the world. There are 8 million databases in the world. example.

Monty

I don't remember much about the early years. I just remember that my father wanted me to be able to query data faster and help him solve some work problems. Monty's father loves me very much. He is by my side every day, teaching me skills, and seeing me take a new code every time I improve, I really hope that my daughter will become a phoenix. I remember when I was 6 years old, I was still an ugly duckling, the code name was V3.23, and people started to know me. I felt that I was small and cute, not as old-fashioned as some predecessors (Oracle), although they were very experienced, they asked them to work hard Prepare for half a day, and it's terribly expensive. I work undercover everywhere, basically free of charge, and open source my way of doing things. Many people have started to help me improve, and I feel that I have made rapid progress.

"Brightness of the Sword"

I remember that in 2005, when I was 10 years old, I had evolved into V5.0. I used to only look up data from tables. Now I have learned a lot of advanced skills (such as: views, stored procedures, functions, triggers), and I also organize the internal data in the warehouse called information_schema, so that everyone can know me better. They can query the warehouse to do many advanced things. For the first time, I know how to cooperate with others to complete things, because I learned distributed transactions ( XA). Although I have learned a lot of skills, my hands and feet are always awkward. My father helps me wipe my butt every day (there are too many bugs).

It was not until 2008, when I was 13 years old and evolved into V5.1, that I began to boldly go out to pick up guests. At that time, many Chinese friends began to understand me. They used to hire old antiques such as Oracle. Later, the Internet business developed rapidly, and it was too expensive to hire Oracle, so they got in touch with me closely. I remember that Sina, NetEase, Alibaba, Baidu, Tencent and other small partners are very enthusiastic to me. I am very happy. They handed me a lot of business and helped me improve my skills.

The pressure is huge. Alibaba said that I would be responsible for all the business. They launched an operation to go to the IOE, and there was a hand-picking festival. I was very panicked. Every year on 11.11, everyone buys their hands together. I have never seen so many people, especially those sisters, who can't stop at all. I have to keep recording their orders. I feel like I can't hold it anymore, you see, Here are their hands:

Taken from the shopping mall in Venice, Italy

"That was the first snow in 2002", oh, I remember wrong, it was 0:00 on November 11, 2012, I was completely broken, and it took 30 minutes of rest to recover. My friends knew that I was under too much pressure and couldn’t bear it, so I found an intermediary agent to help me share the pressure. At that time, there were many intermediaries, such as Vitess, Cobar, TDDL, DDS, MyCAT, etc. The intermediaries gave me a mediocre job It is relatively simple. As long as you take a small amount of data each time, the pressure is instantly reduced a lot.

"Lovely"

I have been paired with MyISAM brother since I was a child. Although I did a good job many times, when I encountered several things at the same time, he was unable to do what he wanted. In order to avoid mistakes, I could only let him do it one by one. It feels like we are not efficient and I am also very annoyed.

Later, a young man named InnoDB took the initiative to help me. I was so happy. When many people came back to life, I slowly began to recommend InnoDB brother to deal with it. He is really good. He can work on several things at the same time and never go wrong. I fell in love with him slowly, but I couldn't let go of MyISAM until 2010, when I had a showdown and officially started a new life with InnoDB brother.

In 2013, I just turned 18 years old, and the code name was upgraded to V5.6. InnoDB is also more powerful, and the efficiency has been improved by 2 times. In addition, I have recorded a lot of running data in performance_schema. If you encounter problems, you can quickly diagnose the cause. Now programmers all over the world like me, and there are many DBAs (database administrators) who study my way of doing things very thoroughly and continue to give me suggestions for improvement. I am no longer the ugly duckling I used to be, and local tyrants like AWS and Facebook also like to work with me.

"Unique Secret"

My father taught me some unique secrets since I was a child. I feel that although I don’t have enough internal skills, my moves are very good. Beginners like them very much. I will introduce a few tricks to you:

1. Main and backup avatars


The so-called clone technique means that the master and backup can handle things at the same time, which is my specialty.

I can instantly change into a clone, or even multiple clones. When there are a lot of things, these clones can be used together, which is very efficient. My avatar technique is too practical in the Internet circle. It can set up a read-write separation structure in seconds. Don't be too cool.

And the clones of those old seniors are more old-fashioned. They usually fight alone, and occasionally cultivate a clone. When they can't hold it, they change their clones on top, and they usually watch from the side. Later, they also learned my avatar technique, but it always felt like drawing a tiger after a cat, haha.

However, the old guy from Oracle is very skilled and has this RAC cheat book, which he said is very good, but I have never learned it.

2. SHOW

Here, I can use the SHOW method to view a lot of internal data, such as clones, libraries, tables, views, functions, users, help, and everything can be shown and displayed. DBAs especially like these tricks.

show slave status; # View the status of the slave (standby database)
show processlist; #View the current connection information, you can see who is currently accessing me, and you can also see the currently running slow SQL
show databases; #View database list
show tables; #View table list
show create table t1; #View table definition
show grants for user1; #View which permissions the user has
show engine innodb status; #View the running details of innodb, all deadlocks and memory information are here

And the seniors always hid their little things and didn't have this skill at all.

Three, page turning artifact


When people search for information, the following pages are often displayed

My page-turning artifact is dedicated to doing this. It's very powerful. Look at the tricks:

#MySQL uses the paging syntax of limit to view 10 pieces of data starting from the 90th
select * from user
order by name limit 90,10

Those old guys played the following way, which is really embarrassing.

#Oracle uses rownum paging syntax
select *
from(
select rownum as rn,
a.*
from user a
order by name
where rownum<100
)
where rn>90

Later, the old man of the SQL standard organization developed a new paging syntax. The following is the template. Do you understand? Anyway, I feel dizzy after reading it, and I can't remember it.

Well, there are still many, many secrets of mine, I will tell you later, and then I will talk about a few major changes of mine.

"Marrying into a rich family / leaving home"

My family was originally in MySQL AB in Sweden, and I also have some partners in the United States. In 2008, a local tyrant named SUN in Silicon Valley of the United States was eyeing our family and said that he would give us 1 billion US dollars for development. The family couldn’t resist the temptation, so we moved to SUN’s place. The SUN family has a big business. Their boss is called SPARC (a reduced instruction set CPU architecture, which is the opposite of Intel), the second is called Solaris (a UNIX operating system, an old antique), and Java, which only makes money but does not make money, although The family has a big business, but I always feel puffy, not ordinary puffiness. Soon, in 2009, the big local tyrant Oracle bought SUN with 7.4 billion US dollars. My God, my family in SUN has not yet recognized it, and I have to move again.

After arriving at Oracle, I finally reunited with my brother InnoDB. InnoDB moved to Oracle’s house in 2005 and has been flirting with me. This time it was considered a lover who finally got married (Actually, my first love, BerkeryDB, was also at Oracle’s house, but That's all in the past).

Sadly my dad Monty, an open source fighter who disliked Oracle so much, parted ways with us. He went back to Finland on his own to raise my sister MariaDB, but when he got to SUN's house, Dad felt that it was impossible to be with me forever and started to spend time on MariaDB. Look, below is a beautiful photo of my sisters, MariaDB sister and I are very similar.

MySQL, MariaDB

Dad hopes I don't get bullied at Oracle's house, and if Oracle doesn't want me, I can still live with MariaDB. Now MariaDB has also come out to work by herself, and sometimes we will PK, but I hope she will grow up quickly. Dad is in his sixtieth year, and recently went to China to meet old friends. He also said that he would work with his sister until he was 100 years old.

I've been working on avatars, and in the early days, avatars would just imitate my passwords (Statement mode). By 2008 (v5.1), I sent the spell details (Row mode) to her and she was able to flex herself. In 2015, when I was 20 years old, I also upgraded to V5.7, and the avatar was even more powerful. In the past, when the avatar was very busy, she could not keep up. Now our information can be synchronized concurrently, and she has completely kept up. my rhythm. At that time, JSON was also popular in the industry (the one that beat XML down), so I also quietly learned JSON, and that geospatial technology, which I learned when I was 20 years old. In addition, InnoDB has also practiced the skills of dynamically adjusting memory. The use of memory can be like the golden hoop of Sun Wukong. It can be big when it is big, and small when it is small.

Oracle has been giving me the code name of V5.8, and it will be announced to the world. I feel that I have made great progress, and V8 is more in line with my temperament improvement, so in 2018, I decided to change from V5.7 to V8. 0 code. MyISAM brother is really sorry, I feel like I have to leave you completely. I have fully integrated with InnoDB in V8, and my confidential warehouse (metadata) is all InnoDB. I also learned window functions and CTE syntax, which can be said to be more powerful in data analysis and complex SQL processing. I am practicing Hash Join (a relatively advanced algorithm, the seniors are very proficient), but it is still in the beginner stage, everyone laughed.

"Celebrity Trouble"

Because I am well-known in the industry, according to the report of the scouts, there are 31 rookies practicing my moves, OceanBase, TiDB, TDSQL, SequoiaDB, ShardingSphere, Vitess, HotDB, and AWS's Aurora, Alibaba Cloud PolarDB, Cloud GaussDB, etc. They all say that they are stronger than me, they have learned distributed and cloud-native martial arts secrets, and they are still practicing HTAP magic. My God, I'm more afraid, and I don't know how to fight back, but "I'm still me, different fireworks".

I'm not very good at big data processing, but some newcomers are very talented at this. For example, there is a famous ClickHouse child born in Russia recently. He also imitated some of my moves. I am very optimistic about him. There is also a little brother named Doris in China who also learned a trick and a half from me. It is said that he can't pull it. We have just born a younger brother of HeatWave, and he also has a talent for data analysis. However, he rarely goes out and has been living in my cloud. Now the family revolves around him, I hope he grows up soon.

I don't know what the future will hold. Those NoSQL peers are always provoking me, including MongoDB, Redis, Nebula Graph, TDengine, etc. In the past 10 years, everyone has moved to the cloud to live. I feel that Oracle is very divided. The cloud in our family seems to be tepid. At the earliest, my family didn’t let me live on the cloud. Instead, AWS and Alibaba Cloud took me as their top card. They gave me the No. 1 room on the cloud, and I made them tens of billions in cash. I'm a flower in the wall and a fragrance outside the wall, you know.

In fact, I was not very popular when I went to Oracle's house. I felt like a concubine, worried that I would steal the limelight from the main house. There were always rumors that Oracle bought me just to get me. Brother InnoDB has been encouraging me to live, and we are also relying on each other. After all, if I am gone, he will definitely not be able to live. Although I have been upgrading, I don't know when V9 will be released, because everyone is going to the cloud now, so my core task is to gain a firm foothold on the cloud, and I can't take care of other things.

I also have a nemesis called Elephant (PostgreSQL), actually she was born in the 70s, and calling her eldest sister would probably slash me with a knife. When she was a teenager, some accidents happened at home. Later, she became fascinated by object-oriented magic, thinking that it was a peerless magic. In the end, she didn't practice it. She is a good student of a university teacher, a type of scholar who knows everything, and is known as the most powerful open source database. I feel that I understand it, but I am not as reliable as I really do it, so I don't care about her. However, she is more open than me, has many friends, and has made rapid progress in recent years. There is also an openGauss brother in China who is very good to her. Maybe it will be better than the blue. I still have to be careful.

It's my turn to do nucleic acid. I'll talk to you here today. Next time I'm free, I'll talk about it again. It's still in the old place of the public account "Cloud Database Technology". My status at home is in urgent need of improvement. If you are using MySQL, remember to like and retweet for me. My family still has a few treasures of MySQL training underwear to give away.