MySQL cheatsheet

Preface

Some useful tips for MySQL Source: Head First SQL

mysql

set mysql prompt in an option file, such as ~/.my.cnf

[mysql]
prompt=(\\u@\\h) [\\d]>\\_

edit sql at command line: use edit command to open an editor to do this

mysqladmin

check server working

$ mysqladmin -ulee -pmypass ping
mysqld is alive

status

$ mysqladmin -ulee -pmypass status
Uptime: 10887  Threads: 2  Questions: 238  Slow queries: 0  Opens: 179  Flush tables: 1  Open tables: 43  Queries per second avg: 0.021

process list

$ mysqladmin -ulee -pmypass processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 63 | lee  | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

MyISAM

for each table, there are three files:

three indexing method:

数据存放格式:

Innodb

数据存储:

数据和索引存放在一起,

CSV

play with csv storage engine:

(root@localhost) [test]> create table hellocsv ( id int(10) not null, name varchar(16) not null ) engine=csv;
Query OK, 0 rows affected (0.12 sec)

(root@localhost) [test]> insert into hellocsv values(1, 'lee'),(2, 'wang');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

(root@localhost) [test]> select * from hellocsv;
+----+------+
| id | name |
+----+------+
|  1 | lee  |
|  2 | wang |
+----+------+
2 rows in set (0.00 sec)

# csv tables data are stored in '.CSV' files
$ cat hellocsv.CSV 
1,"lee"
2,"wang"

权限级别

高级别覆盖低级别

data backup and recovery

logical backup

save data to a file: SELECT * INTO OUTFILE 'file-path' FROM ...

> select * from country;
+----+-----------+---------------+-----------+
| id | name      | continent     | president |
+----+-----------+---------------+-----------+
|  1 | zhongguo  | Asia          | Xi        |
|  3 | US        | North America | Obama     |
|  4 | UK        | Europe        | Cameron   |
|  5 | Singapore | Aisa          | Lee       |
| 10 | Japan     | Asia          | Abe       |
+----+-----------+---------------+-----------+
5 rows in set (0.00 sec)

> SELECT * INTO OUTFILE '/tmp/country.out'     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'     LINES TERMINATED BY '\n'     FROM country;
Query OK, 5 rows affected (0.00 sec)

$ cat /tmp/country.out 
1,"zhongguo","Asia","Xi"
3,"US","North America","Obama"
4,"UK","Europe","Cameron"
5,"Singapore","Aisa","Lee"
10,"Japan","Asia","Abe"

or, use mysqldump, this will generate table structure and data in different files

$ mysqldump -uroot -p -T/tmp test country --fields-enclosed-by=\" --fields-terminated-by=,
$ ll /tmp/country.*
-rw-r--r-- 1 root  root  1485 Sep  7 17:18 /tmp/country.sql
-rw-rw-rw- 1 mysql mysql  143 Sep  7 17:18 /tmp/country.txt

$ cat /tmp/country.sql 
...

--
-- Table structure for table `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `continent` varchar(16) DEFAULT NULL,
  `president` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

...

$ cat /tmp/country.txt 
"1","zhongguo","Asia","Xi"
"3","US","North America","Obama"
"4","UK","Europe","Cameron"
"5","Singapore","Aisa","Lee"
"10","Japan","Asia","Abe"

data recovery

load data file to table:

$ mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt

or

> LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';

Locking

Levels:

Query Optimization

基本思路和原则:

  1. 优化更需要优化的 Query;
  2. 定位优化对象的性能瓶颈;
  3. 明确的优化目标;
  4. 从 Explain 入手;
  5. 多使用 profile;
  6. 永远用小结果集驱动大的结果集;
  7. 尽可能在索引中完成排序;
  8. 只取出自己需要的 Columns;
  9. 仅仅使用最有效的过滤条件;
  10. 尽可能避免复杂的 Join 和子查询;

    复杂的join占用资源多,可能需要等待或者会让其它线程等待,可能比多个简单的查询还慢; Mysql对子查询的优化不佳,可能有索引而没被利用;

Group by

在优化 GROUP BY 的时候还有一个小技巧可以让我们在有些无法利用到索引的情况下避免filesort 操作,也就是在整个语句最后添加一个以 null 排序(ORDER BY null)的子句

(root@localhost) [test]> explain select * from news group by rate;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | news  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> explain select * from news group by rate order by null;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | news  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

profiling

(root@localhost) [test]> set profiling=1;   # enable profiling
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select * from country;
+----+-----------+---------------+-----------+
| id | name      | continent     | president |
+----+-----------+---------------+-----------+
|  1 | zhongguo  | Asia          | Xi        |
|  3 | US        | North America | Obama     |
|  4 | UK        | Europe        | Cameron   |
|  5 | Singapore | Aisa          | Lee       |
| 10 | Japan     | Asia          | Abe       |
+----+-----------+---------------+-----------+
5 rows in set (0.00 sec)

(root@localhost) [test]> show profiles;     # show profiling info
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00008700 | select * from country |
+----------+------------+-----------------------+
1 row in set (0.00 sec)

(root@localhost) [test]> show profile cpu, block io for query 1;    # detail info for a query
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000029 | 0.004000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| checking privileges on cached  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| sending cached result to clien | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
8 rows in set (0.00 sec)

Schema 设计优化

transection isolation levels

Lock problems

install MySQL from source

ref: http://dev.mysql.com/doc/refman/5.6/en/installing-source-distribution.html

multiple MySQL server instances on one machine

continue from above:

cd /opt/mysql
sudo mkdir conf
cd conf/ 

# create a conf file
sudo vi my.3364.cnf

        [client]
        port = 3364
        socket = /var/run/mysqld/mysqld.3364.sock


        [mysqld]
        user = mysql
        basedir = /opt/mysql
        datadir = /data/mysql/data1
        port = 3364
        socket = /var/run/mysqld/mysqld.3364.sock
        pid-file = /var/run/mysqld/mysqld.3364.pid

# create data directory

    cp /data/mysql
    sudo mkdir data1

# init db

    cp /opt/mysql
    sudo ./scripts/mysql_install_db --defaults-file=conf/my.3364.cnf

# start server

    sudo ./bin/mysqld_safe --defaults-file=conf/my.3364.cnf

# connect to server (default pass is empty)

     mysql --defaults-file=/opt/mysql/conf/my.3364.cnf -uroot -p

repeat above steps for more mysql server instances

or, using mysqld_multi to manage multiple instances

timezone

timezone variables:

`system_time_zone`: default to system timezone setting, can not be changed
`global.time_zone`: default to 'SYSTEM'
`session.time_zone`: default to `global.time_zone`, can be changed per session, results of `now()` depend on this


    MYSQL> select @@system_time_zone;
    +--------------------+
    | @@system_time_zone |
    +--------------------+
    | NZDT               |
    +--------------------+

    MYSQL> select @@global.time_zone;
    +--------------------+
    | @@global.time_zone |
    +--------------------+
    | SYSTEM             |
    +--------------------+

    MYSQL> select @@session.time_zone;
    +---------------------+
    | @@session.time_zone |
    +---------------------+
    | SYSTEM              |
    +---------------------+

    MYSQL> set session time_zone = '+08:00'; select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2015-12-06 08:54:46 |
    +---------------------+

    MYSQL> set session time_zone = 'SYSTEM'; select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2015-12-06 13:54:58 |
    +---------------------+

search and replace text

use the REPLACE function, all occurence will be replaced:

> update news set title = REPLACE(title, 'hello', 'hola') where id = 2;

save output to file

SELECT * FROM information_schema.CHARACTER_SETS LIMIT 2 
  INTO OUTFILE '/tmp/charsets.out' 
  FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n';

checkout the result:

$ cat /tmp/charsets.out 
"big5","big5_chinese_ci","Big5 Traditional Chinese",2
"dec8","dec8_swedish_ci","DEC West European",1

duplicate records in a table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;  
ALTER TABLE tmp_table DROP id;  
INSERT INTO table_name SELECT 0, tmp_table.* FROM tmp_table;  
DROP TABLE tmp_table;