Episode Details

Explore the essentials of MySQL database design with Lois Houston and Nikita Abraham, who team up with MySQL expert Perside Foster to discuss key storage concepts, transaction support in InnoDB, and ACID compliance. You'll also get tips on choosing the right data types, optimizing queries with indexing, and boosting performance with partitioning. MySQL 8.4 Essentials: https://mylearn.oracle.com/ou/course/mysql-84-essentials/141332/226362 Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ X: https://x.com/Oracle_Edu Special thanks to Arijit Ghosh, David Wright, Kris-Ann Nansen, Radhika Banka, and the OU Studio Team for helping us create this episode. --------------------------------------------------------- Episode Transcript:

00:00

Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we'll bring you foundational training on the most popular Oracle technologies. Let's get started!

00:26

Lois: Hello and welcome to the Oracle University Podcast. I'm Lois Houston, Director of Innovation Programs with Oracle University, and with me today is Nikita Abraham, Team Lead of Editorial Services.

Nikita: Hi everyone! Last week, we looked at installing MySQL and in today's episode, we're going to focus on MySQL database design.

Lois: That's right, Niki. Database design is the backbone of any MySQL environment. In this episode, we'll walk you through how to structure your data to ensure smooth performance and scalability right from the start.

00:58

Nikita: And to help us with this, we have Perside Foster joining us again. Perside is a MySQL Principal Solution Engineer at Oracle. Hi Perside, let's start with how MySQL handles data storage on the file system. Can you walk us through the architecture?

Perside: In the MySQL architecture, the storage engine layer is part of the server process. Logically speaking, it comes between the parts of the server responsible for inputting, parsing, and optimizing SQL and the underlying file systems.

The standard storage engine in MySQL is called InnoDB. But other storage engines are also available. InnoDB supports many of the features that are required by a production database system. Other storage engines have different sets of features. For example, MyISAM is a basic fast storage engine but has fewer reliability features.

NDB Cluster is a scalable distributed storage engine. It runs on multiple nodes and uses additional software to manage the cluster.

02:21

Lois: Hi Perside! Going back to InnoDB, what kind of features does InnoDB offer?

Perside: The storage engine supports many concurrent users. It also keeps their changes separate from each other. One way it achieves this is by supporting transactions. Transactions allows users to make changes that can be rolled back if necessary and prevent other users from seeing those changes until they are committed or saved persistently.

The storage engine also enables referential integrity. This is to make sure that data in a dependent table refers only to valid source data. For example, you cannot insert an order for a customer that does not exist.

It stores raw data on disk in a B-tree structure and uses fast algorithms to insert rows in the correct place. This is done so that the data can be retrieved quickly. It uses a similar method to store