Introduction
MySQL is the most popular open- source database system on the market and has been around since 1995. Oracle has owned MySQL since 2010, and MySQL is distributed in two versions: The Community Edition (open source), and the Enterprise Edition, which you must pay for and of course it comes with a series of enhanced features not available in the free version.
It should be mentioned that the Community Edition version (open source) is more popular than the Enterprise version, and can be downloaded from the MySQL website (https://www.mysql.com/). This edition has all the features you need for safe web applications that are reliable and secure. Websites such as Google, Wikipedia, Facebook, YouTube, and so on, are some of the biggest users of MySQL.
I'm curious whether you know what MySQL means.
MySQL is a combination of My (the name of the daughter of co-founder Michael Widenius) and SQL stands for Structured Query Language.
This book is an excellent choice if you want to learn advanced concepts at the performance level. Also, you will learn how to analyze and optimize complex queries with different techniques. You will learn to configure and optimize MySQL servers and then I will teach you how to secure your database, using back-up strategies.
We will also go through all the new features of MySQL 8.0 including the Group Replication and InnoDB cluster. Together, we will look at the best surveillance tools that exist on the market today. Throughout this book, I will share my experiences and techniques to help you better manage MySQL in its everyday management and to have a better understanding of its complex and broad architectures. Finally, I will share some techniques with you, which will allow you to make more efficient MySQL 8.0 databases and which I use with Fortune 500 customers.
In this chapter, we will cover the following topics:
- Why Oracle decided to go with MySQL 8.0
- Why MySQL 8.0 is the next generation
- Why it is so important to have a good architecture design
- A review of the chapter
Why MySQL 8?
In short, Oracle has decided to create a new version of MySQL with the number 8 simply because MySQL 6 already existed in 2007 but was then abandoned.
So why isn't is called MySQL 7, then? Because Oracle already has a version 7, called MySQL Cluster, and this version is 7.5 (GA). So, the next number that is available is 8. At the moment, MySQL version 8.0 is in DMR, which means it is in development and not available for production. However, I encourage you to download and test the new features.
Why is MySQL 8.0 the next generation?
As you can guess, MySQL 8.0 will be the next generation of databases because it will include the traditional side of SQL and NoSQL with JSON. The new features are not just those that I just mentioned; you will also find the following:
- Common table expression (CTE): A type of temporary table associated with a query that will allow you to use the WITH command for recursion.
- Invisible indexes: These are invisible to the MySQL optimizer; that is, they are maintained by the optimizer but not used by it. This has the big advantage of being able to test the behavior of the MySQL 8.0 server during evaluation and not the index.
- Oracle has also added to MySQL 8.0 what are called persisting configuration variables, and this new feature has the ability to make persistent changes to the MySQL configuration online.
- The data dictionary has been optimized, a very good improvement.
- Great features have been added for replication as well.
Let's not forget the new roles that will facilitate user management—and many other surprises besides.
Why it is so important to have a good MySQL architecture design
It must be said that MySQL is easy to use and its operation is very fast. MySQL requires at first a general knowledge of SQL to work effectively with it. MySQL does not require much more knowledge, but a little knowledge of common relational database management system (RDBMS) is helpful.
However, despite the fact that MySQL comes with a minimal configuration to help your database launch and perform well, MySQL needs more attention to maintain optimal performance, and in general a good understanding of MySQL's configuration becomes more important.
The fact is that the more your database grows with time and demands more hardware resources such as memory, processors, and disk speed, the slower response times will be; this is a sign that your data model need to be revised and adjusted to meet growing and critical demands. The better your data model is at the beginning, the easier it will be to re-adjust your configuration along the way.
The more your business or website grows the more you need a good solid MySQL architecture. During this growth phase, you will notice that you will be asking yourself questions such as the following ones:
- Do I need to completely rethink my MySQL architecture?
- Should I start thinking about having a large MySQL infrastructure?
- Do I need to review my queries?
- Do I need to create indexes?
Imagine the questions and doubts when we are in the phase where MySQL is likely to under-perform. That's when you desperately start looking at your options on Google and YouTube to see how you can improve your architecture.
This is what I call the critical or gray-zone phase. You will try to make all kinds of changes with the hope of solving your performance problems when the solution was simply to have a good architecture and a logical structure of your data right from the start.
Fortunately, MySQL 8.0 will provide a detailed understanding of high availability with InnoDB Cluster, Global Replication, and all the other features that have been added or improved. I must mention that I have been so far pleasantly surprised by the new performance of MySQL 8.0.
Summary
The objective of this book is to progressively give you a detailed understanding of advanced MySQL concepts and how they can be applied to complex situations, such as the following:
- How to optimize complex queries
- How to improve your architecture for large databases
- How to manage complex high availability more effectively
- The best tips and techniques for complex situations
- The new features of MySQL 8.0 that can help you make your large databases even more efficient
Do not forget to explore the monitoring tools that let you identify queries that are the slowest and most complex.
I will also share my favorite tips that I use every day to help Fortune 500 companies, and finally I will guide you in this book through the most complex situations that you may face on a daily basis.