Thursday, November 5, 2015

High Performance MySQL: CH4 Optimizing Schema and Data Types

1- It’s harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry.

2- The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make it a priority

Number types
3- TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT.
4- MySQL lets you specify a “width” for integer types, such as INT(11). This is meaningless
for most applications: it does not restrict the legal range of values, but simply specifies the number of characters MySQL’s interactive tools (such as the command-line client) will reserve for display purposes. For storage and computational purposes, INT(1) is identical to INT(10)

Real Numbers
1- we have FLOAT, DOUBLE and DECIMAL,
2- DECIMAL is for storing exact fractional numbers (so accurate),
3- DECIMAL is slow as the server itself performs DECIMAL math in MySQL 5.0 and newer, because CPUs don’t support the computations directly. Floating-point math is significantly faster, because the CPU performs the computations natively.

String Types
1- we have CHAR and VARCHAR
2- VARCHAR uses extra bytes to store the value's length

BLOB and TEXT types
1- we have types:TINYTEXT, SMALL TEXT, TEXT, MEDIUMTEXT, and LONGTEXT (TEXT is synonym for SMALLTEXT)
2- we have TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. BLOB is a synonym for SMALLBLOB,
3- The only difference between the BLOB and TEXT families is that BLOB types store binary data with no collation or character set, but TEXT types have a character set and collation
4- Memory storage engine doesn’t support the BLOB and TEXT types, queries that use BLOB or TEXT columns and need an implicit temporary table ( he mentioned a trick to put them in Memory which is using SUBSTRING, check page 122 ).

Using ENUM
1- An ENUM column can store a predefined set of distinct string values.
2- ENUM list is fixed if you want to update you should perform ALERT TABLE request.


Date and Time Types
1- DATETIME: stored like this YYYYMMDDHHMMSS
2- TIMESTAMP type stores the number of seconds elapsed since midnight
3- TIMESTAMP uses only four bytes of storage, so it has a much smaller range than DATETIME
4- TIMESTAMP is timezone aware, if you store or access data from multiple time zones, the behavior of TIMESTAMP and DATETIME will be very different.
5- MySQL currently does not have an appropriate data type for this, but you can use your own storage format

Bit-Packed Data Types
1- we have BIT, SET

General Notes
1- be very careful with completely “random” strings, such as those produced by MD5(), SHA1(), or UUID(). Each new value you generate with them will be distributed in arbitrary ways over a large space, which can slow INSERT and some types of SELECT queries

2- Object-relational mapping (ORM) systems (and the “frameworks” that use them) are another frequent performance nightmare.

3- People often use VARCHAR(15) columns to store IP addresses. However, they are really unsigned 32-bit integers, not strings.

4- ENUM has some performance issues.

5- try not to use NULL, maybe you can use "NO VALUE" or -1 or ...

6- if you have a table with counter value, you may have concurrency issue. You can get higher concurrency by keeping more than one row and updating a random row.

Speeding Up ALTER TABLE
1- In general, most ALTER TABLE operations will cause interruption of service in MySQL.
2- MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time,
3- MySQL 5.1 and newer include support for some types of “online” operations that won’t lock the table for the whole operation. Recent versions of InnoDB also support building indexes by sorting, which makes building indexes much faster and results in a compact index layout.
4- you can use some tools for altering table to swap to a temp server then come back, some of these tools are two. the “online schema change” tools from Facebook’s database operations team (https://launchpad.net/mysqlatfacebook),Shlomi Noach’s openark toolkit (http://code.openark.org/), and Percona Toolkit (http://www.percona.com/software/).
5- Not all ALTER TABLE operations cause table rebuilds. some changes can be applied directly to the table .frm file (this file has information about table format). example of such an update is changing a default value of a column 

No comments:

Post a Comment