MySQL Database Design

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 indexes. This allows you to run queries based on a sort order that is different from the row's natural order. InnoDB has its own buffer pool. This is a memory cache that stores recently accessed data. And as a result, queries on active data are much faster than queries that read from the disk. InnoDB also has performance features such as multithreading and bulk insert optimization. 04:13 Lois: So, would you say InnoDB is generally the best option? Perside: When you install MySQL, the standard storage engine is InnoDB. This is generally the best choice for production workloads that need both reliability and high performance. It supports transaction syntax, such as commit and rollback, and is fully ACID compliant. 04:41 Nikita: To clarify, ACID stands for Atomicity, Consistency, Isolation, and Durability. But could you explain what that means for anyone who might be new to the term? Perside: ACID stands for atomic. This means your transaction can contain multiple statements, but the transaction as a whole is treated as one change that succeeds or fails. Consistent means that transactions move the system from one consistent state to another. Isolated means that changes made during a transaction are isolated from other users until that transaction completes. And durable means that the server ensures that the transaction is persisted or written to disk once it completes. 05:38 Lois: Thanks for breaking that down for us, Perside. Could you tell us about the data encryption and security features supported by InnoDB? Perside: InnoDB supports data encryption, which keeps your data secure on the disk. It also supports compression, which saves space at the cost of some extra CPU usage. You can configure an InnoDB cluster of multiple MySQL server nodes across multiple hosts to enable high availability. Transaction support is a key part of any reliable database, particularly when multiple concurrent users can change data. By default, each statement commits automatically so that you don't have to type commit every time you update a row. You can open a transaction with the statement START TRANSACTION or BEGIN, which is synonymous. 06:42 Nikita: Perside, what exactly do the terms "schema" and "database" mean in the context of MySQL, and how do they relate to the storage structure of tables and system-level information? Perside: Schema and database both refer to collections of tables and other objects. In some platform, a schema might contain databases. In MySQL, the word schema is a synonym for database. In InnoDB and some other storage engines, each database maps to a directory on the file system, typically in the data directory. Each table has rows data stored in a file. In InnoDB, this file is the InnoDB tablespace, although you can choose to store tables in other tablespaces. MySQL uses some databases to store or present system-level information. The MySQL and information schema databases are used to store and present structural information about the server, including authentication settings and table metadata. You can query performance metrics from the performance schema and sys databases. If you have configured a highly available InnoDB cluster, you can examine its configuration from the MySQL InnoDB cluster metadata database. 08:21 Lois: What kind of data types does MySQL support? Perside: MySQL supports a number of data types with special characteristics. BLOB stands for Binary Large Object Block. Columns that specify this type can contain large chunks of binary data. For example, JPG pictures or MP3 audio files. You can further specify the amount of storage required by specifying the subtype-- for example, TINYBLOB or LONGBLOB. Similarly, you can store large amounts of text data in TEXT, TINYTEXT, and so on. These types, BLOB and TEXT, share the same characteristic, that they are not stored in the same location as other data from the same row. This is to improve performance because many queries against the table do not query BLOB or TEXT data contained within the table. MySQL supports geographic or spatial data and queries on that data. These include ways to represent points, lines, polygons, and collections of such elements. The JSON data type enables you to use MySQL as a document store. A column of this type can contain complete JSON documents in each row. And MySQL has several functions that enable querying and searching for values within such documents.  10:11 Adopting a multicloud strategy is a big step towards future-proofing your business and we’re here to help you navigate this complex landscape. With our suite of courses, you'll gain insights into network connectivity, security protocols, and the considerations of working across different cloud platforms. Start your journey to multicloud today by visiting mylearn.oracle.com. 10:38 Nikita: Welcome back. Perside, how do indexes improve the performance of MySQL queries? Perside: Indexes make it easier for MySQL to find specific rows. This doesn't just speed up queries, but also ensures that newly inserted rows are placed in the best position in the data file so that future queries will findthem quickly. 11:03 Nikita: And how do these indexes work exactly? Perside: Indexes work by storing the raw data or a subset of the raw data in some defined order. An index can be ordered on some non-unique value, such as a person's name. Or you can create an index on some value that must be unique within the table, such as an ID. The primary index, sometimes called a clustered index, is the complete table data stored on a unique value called a Primary Key. 11:38 Lois: Ok. And what types of indices are supported by InnoDB? Perside: InnoDB supports multiple index types. Raw data in most secondary indexes are stored in a BTREE structure. This stores data in specific buckets based on the index key using fixed-size data pages. HASH indexes are supported by some storage engines, including the memory storage engine. InnoDB has an adaptive HASH feature, which kicks in automatically for small tables and workloads that benefits from them. Spatial data can be indexed using the RTREE structure.  12:25 Nikita: What are some best practices we should follow when working with indexes in MySQL? Perside: First, you should create a Primary Key for each table. This value is unique for each row and is used to order the row data. InnoDB doesn't require that tables have an explicit Primary Key, but if you don't set one, it creates a hidden Primary Key. Each secondary index is a portion of the data ordered by some other column. And internally, each index entry uses the Primary Key as a lookup back to the rest of the row. If your Primary Key is large or complex, this increases the storage requirement of each index. And every time you modify a row, MySQL must update every affected index in the background. The more indexes you have on a table, the slower every insert operation will be. This means that you should only create indexes that improve query performance for your specific workload. The sys schema in MySQL Enterprise Monitor have features to identify indexes that are unused. Use prefix and compound keys to reduce indexes. A prefix key contains only the first part of a string. This can be particularly useful when you have large amounts of text in an index key and want to index based on the first few characters. A compound key contains multiple columns, for example, last name and first name. This also speeds up queries where you're looking for only those values because the secondary index can fulfill the query without requiring a lookup back to the primary indexes.  14:35 Lois: Before we let you go, can you explain what table partitioning is? Perside: Table partitioning is enabled by using a plugin. When you partition a table, you divide its content according to certain rules. You might store portions of the table based on the range of values in a column. For example, storing all sales for 2024 in a single partition. A partition based on a list enables you to store rows with specific values in the partition column. When you partition by hash or key, you distribute rows somewhat evenly between partitions. This means that you can distribute a large table across multiple disks, or you can place more frequently accessed data on faster storage. Explain works with partitioning. Simply prefix any query that uses partition data, and the output shows information about how the optimizer will use the partition. Partitioning is one of the features that is only fully supported in Enterprise Edition. 15:57 Lois: Perside, thank you so much for joining us today. In our next episode, we’ll dive deep into MySQL security. Nikita: And if you want to learn more about what we discussed today, visit mylearn.oracle.com and search for the MySQL 8.4: Essentials course. Until next week, this is Nikita Abraham… Lois: And Lois Houston signing off! 16:18 That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.