MySQL: Top 10 Design Tips

This article is a fraction of a Number of Articles on MySQL, to access them click here.

      1.Understand Your Technology Tools

MySQL is great platform to manage your database, and to be able to understand every aspect is not that easy, but the one thing that we can do is that, we can try our best to find what those are, specifically focusing on those aspects that will maximize MYSQL’s strength catering to our needs.

Here are some general points :

❖ Maximize MySQL strengths

❖ Scale out / HA Options

❖ Different Storage Engines

❖ Query Cache

❖ Minimize MySQL weaknesses

❖ No Online Alter

❖ Backup Strategies

❖ Instrumentation

Disk = Memory = Performance

“Save that Memory”

When going over the questions like “Which data to use?” always remember that every single counts, because your database will always keep on growing. So make sure to keep these points in your mind :

❖Average 25% – 30% saving on engagements

❖Better 60% (200GB System)

❖Best 78% (8GB per master with 12 masters)

Less disk accesses and more data in memory

2. Choosing the data

To design a database, you first must identify what information belongs in it.

The database must contain the data needed for the website to perform its purpose.

Here are a few examples:

✦ An online catalog needs a database containing product information.

✦ An online order application needs a database that can hold customer information and order information.

✦ A travel website needs a database with information on destinations, reservations, fares, schedules, and so on.

In many cases, your application might include a task that collects information from the user. For instance, customers who buy products from a website must provide their address, phone number, credit card information, and other data in order to complete the order. The information must be saved at least until the order is filled. Often, the website retains the customer information to facilitate future orders so the customer doesn’t need to retype the information when placing the next order. The information also provides marketing opportunities to the business operating the website, such as sending marketing offers or newsletters to customers.

❖MySQL has 9 numeric data types

❖ Oracle for example has only 1

❖Integer: TINYINT, SMALLINT,

MEDIUMINT, INT, BIGINT

❖Floating Point: FLOAT, DOUBLE

❖Fixed Point: DECIMAL

❖Other: BIT, (ENUM maybe)

❖Favorite signs of poor design

❖ INT(1)

❖ BIGINT AUTO_INCREMENT

❖ no UNSIGNED used

❖ DECIMAL(31,0).

❖INT(1) – 1 does not mean 1 digit

❖ (1) represents client output display format only

❖ INT is 4 Bytes, TINYINT is 1 Byte

❖ TINYINT UNSIGNED can store from 0 – 255

❖ BIT is even better when values are 0 – 1

❖ BIGINT is not needed for AUTO_INCREMENT

❖INT UNSIGNED stores 4.3 billion values

❖ You should be partitioning when at billions of rows

❖BIGINT is applicable for some columns

❖ e.g. summation of values

❖Best Practice

❖ All integer columns UNSIGNED unless there is a

reason otherwise

❖ Adds a level of data integrity for negative values

3. Application Data Type Efficiencies

 

“Data Efficiency refers to efficiency of the many processes that can be applied to data such as storage, access, filtering, sharing, etc., and whether or not the processes lead to the desired outcome within resource constraints.” – Wikipedia

 

So by Data Efficiency in MySQL, we mean to use the most basic, really necessary, expandable data type for categorizing, storing and manipulating the data stored in your database.

Some Examples are:

❖Using Codes or ENUM

❖ A description is a presentation layer function

❖ e.g. ‘M’, ‘F’ instead of ‘Male’, ‘Female’

❖ e.g. ‘A’, ‘I’ instead of ‘Active’, ‘Inactive’

❖BINARY(16/20) v CHAR(32/40)

❖ MD5() or HASH() Hex value with twice the length

❖INT UNSIGNED for IPv4 address

❖ VARCHAR(15) results in average 12 bytes v 4 bytes

4. NOT NULL

Always use NOT NULL unless there is a reason not to, because this will prevent a lot your database from a lot of “bad”.

❖Saves up to a byte per column per row of data

❖Double benefit for indexed columns

❖Don’t use frameworks or tools

❖NOT NULL DEFAULT ” is bad design

5. Be Wary of TEXT/BLOB

Its important to understand the difference between the BLOB and TEXT, so you can use them properly.

A BLOB is a binary string to hold a variable amount of data. For the most part BLOB’s are used to hold the actual image binary instead of the path and file info. Text is for large amounts of string characters. Normally a blog or news article would constitute to a TEXT field.

Here is more on that http://dev.mysql.com/doc/refman/5.7/en/blob.html

Problems that may arise due to this:

❖Data collection options

❖Incomplete Options

❖ Slow Query Log

❖ SHOW PROCESSLIST

❖ Application level logging

❖ General Lag

6. Transactions

A transaction in MySQL is an operation conducted sequentially focused on database manipulation, performed as a single task or work unit.  A transaction will never be complete unless each individual operation within the work unit is successful. If any operation within the transaction fails, the entire transaction will fail.

So make sure to:

❖Always design for transactions

❖Always use transactions

❖Use a transactional storage engine

7. Data Integrity is Key

The term integrity refers to the accuracy or correctness of data in the database. Integrity constraints ensure that the changes made to the database by authorized users do not result in a loss of data consistency. Thus, the integrity constraints guard against accidental damages to the databases. A database maybe subjected to any number of integrity constraints of arbitrary complexity.

8. Leverage the Query Cache

Query Cache can be a great benefit. Deterministic v Non Deterministic SQL

MySQL Query Cache is not the only type of caching you should consider.

9. Naming Standards

Naming a databases’ keys, integrities etc, is really important not only for you but for others who you may choose to work with you, so keep it simple and easy, and managae a metadata.

Here are some key points:

❖Name all Primary Keyʼs Uniquely

❖ e.g. customer_id, order_id not id

❖Use Data Dictionary SQL to verify data types

❖ Data Types & Lengths

❖Be Descriptive

❖ e.g. invoice_date not just date

❖Avoid Reserved Words

❖ e.g. date, time, timestamp

10. Testing, Testing, Testing

The goal of a testing environment is not to test your software, it is to break your software until you know what might happen in the future and you can already make a plan around it!

❖You must have a testing environment

❖Testing on a Production server is not an option