28 January 2015

Overview

MySQL supports group commit.

Read Write Splitting

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

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

Books

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

Internals

MISC

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.