MySQL

Overview

MySQL supports group commit.

Read Write Splitting

Durability

Double Write Buffer:

Binlog

 max_binlog_size
 expire_logs_days

Percona has max_binlog_files

INFORMATION_SCHEMA Database

show create table information_schema.tables shows that INFORMATION_SCHEMA is stored in MEMORY engine.

select table_name, table_rows
from information_schema.tables
where table_schema = 'rainbow';

Note: For InnoDB tables, the table_rows is only a rough estimate used in SQL optimization.

mysql client

In interactive mode, insert into foo values('one'); produces something like Query OK, 1 row affected (0.04 sec). But with a script file, it produces nothing. If mysql is launched with a script file and with -v option, it produces:

--------------
insert into foo values('one')
--------------

mysqldump

Useful options:

--no-create-info
--complete-insert
--extended-insert=FALSE

outfile

Ubuntu Setup

Use sudo aa-status to check whether MySQL is runing in enforce mode. Add the following content to /etc/apparmor.d/usr.sbin.mysqld:

/home/jing/ r,
/home/jing/** rw,

Run:

sudo /etc/init.d/apparmor reload
sudo service mysql restart

Run:

mkdir /home/jing/outfile-dir
sudo chown mysql /home/jing/outfile-dir

Launch MySQL client with MySQL root user and run:

grant file on *.* to 'username'@'localhost';
flush privileges;

grant file on dbname.* does work. For reasons, refer to MySQL into outfile “Access denied” – permission issue.

Launch MySQL client with username. Run:

> use dbname;
> select * into outfile '/home/jing/outfile-dir/foo' from registration_type;
Query OK, 2 rows affected (0.00 sec)

CentOS 6.5 Setup

Run getenforce to make sure that SELinux is disabled. Make sure that file privilege is granted. MySQL client can write to files in /tmp by default.

Comments

Show table comment:

select table_comment
from information_schema.tables
where table_schema='database_name'
and table_name='table_name';

Date, Datetime and Timestamp

Some time zone abbreviation is ambiguous. CST means both Chinese Standard Time and Central Standard Time.

References:

MySQL Manual:

[11.3 Date and Time Types]
[12.7 Date and Time Functions]
[10.6 MySQL Server Time Zone Support]
[11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME]

create table hacker (
  id int,
  creation_date timestamp default current_timestamp,
  update_date timestamp default current_timestamp on update current_timestamp,
  expire_date timestamp default current_timestamp on update current_timestamp
);
create table actor (
  id int,
  creation_date datetime default current_timestamp,
  update_date datetime default current_timestamp on update current_timestamp,
  expire_date datetime default current_timestamp on update current_timestamp
);

I am in UTC+08 time zone. The beginning of Timestamp is ‘1970-01-01 00:00:01’. In my time zone, it is ‘1970-01-01 08:00:01’

mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

mysql> select unix_timestamp('1970-01-01 08:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 08:00:00') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

mysql> select unix_timestamp('1970-01-01 08:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 08:00:01') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+

The end of Timestamp is ‘2038-01-19 03:14:07’. In my time zone, it is ‘2038-01-19 11:14:07’.

mysql> select unix_timestamp('2038-01-19 03:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 03:14:07') |
+---------------------------------------+
|                            2147454847 |
+---------------------------------------+

mysql> select unix_timestamp('2038-01-19 11:14:07');
+---------------------------------------+
| unix_timestamp('2038-01-19 11:14:07') |
+---------------------------------------+
|                            2147483647 |
+---------------------------------------+

mysql> select unix_timestamp('2038-01-19 11:14:08');
+---------------------------------------+
| unix_timestamp('2038-01-19 11:14:08') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

mysql> select from_unixtime(1);
+---------------------+
| from_unixtime(1)    |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+

auto_increment

Create the following table:

create table p (
  id int not null auto_increment,
  name char(16),
  primary key (id)
);

Insert with a 0 id is impossible. In the following example, id is set to 1 even a 0 value is supplied. But id can be updated to 0.

mysql> insert into p(id, name) values(0, 'xiaoyu');
Query OK, 1 row affected (0.04 sec)

mysql> select * from p;
+----+--------+
| id | name   |
+----+--------+
|  1 | xiaoyu |
+----+--------+
1 row in set (0.00 sec)

mysql> update p set id = 0;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from p;
+----+--------+
| id | name   |
+----+--------+
|  0 | xiaoyu |
+----+--------+
1 row in set (0.01 sec)

GROUP BY

Date and Time Literal

MySQL does not recognize a ISO-8601 string. My local time zone is UTC+01:00. The first string is the ISO-8601 string. The second string is a timestamp literal for the local time zone. If MySQL support ISO-8601, 2015-02-02 00:00:00 should not be returned in the first query.

mysql> select registration_date from user where registration_date >= '2015-02-01T17:00:00.001Z';
+---------------------+
| registration_date   |
+---------------------+
| 2015-03-31 21:50:34 |
| 2015-02-03 00:00:00 |
| 2015-02-02 00:00:00 |
| 2015-02-03 00:00:00 |
+---------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> select registration_date from user where registration_date >= '2015-02-02 01:00:00';
+---------------------+
| registration_date   |
+---------------------+
| 2015-03-31 21:50:34 |
| 2015-02-03 00:00:00 |
| 2015-02-03 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

Composite Index

Multi Column indexes vs Index Merge
Multiple column index vs multiple indexes with MySQL 5.6
Section 8.5.3 of “Database Management Systems, the third edition”

A short summary:

If AND is frequently used for the involved columns, use composite index.
If OR is frequently used for the involved columns, use multiple index.
RANGE query is used for the involved columns, use composite index.

Performance Tuning

Configuration

timeout settings:

Books

Understanding MySQL Internals
High Performance MySQL: Optimization, Backups, and Replication
MySQL High Availability: Tools for Building Robust Data Centers

Internals

MISC

How to find the mysql data directory?: SHOW VARIABLES WHERE Variable_Name LIKE '%dir'
http://stackoverflow.com/questions/225772/compare-two-mysql-databases
unique key and unique index in create table syntax means the same thing.

http://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints

Null values in unique keys:

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.