Xiaopei's DokuWiki

These are the good times in your life,
so put on a smile and it'll be alright

User Tools

Site Tools


it:mysql

MySQL

MySQL 的 doc 真难看.

建表时 int(10) 的 10 是展示时的长度,储存时长度是固定的!

查慢 SQL,补索引

Online DDL

隔离级别

按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ)。MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别。你可以在命令行用 –transaction-isolation 选项,或在选项文件里,为所有连接设置默认隔离级别。 例如,你可以在my.inf文件的[mysqld]节里类似如下设置该选项:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

用户可以用 SET TRANSACTION 语句改变单个会话或者所有新进连接的隔离级别。它的语法如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意:默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你使用GLOBAL关键字,语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别(甚至在事务的中间),或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别:

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read)
未提交读(Read uncommitted) 可能 可能 可能
已提交读(Read committed) 不可能 可能 可能
可重复读(Repeatable read) 不可能 不可能 可能
可串行化(Serializable ) 不可能 不可能 不可能
  • 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据
  • 提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
  • 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读
  • 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

MySQL 四种事务隔离级的说明 - jyzhou - 博客园

INFORMATION_SCHEMA

MySQL :: MySQL 5.5 Reference Manual :: 20 INFORMATION_SCHEMA Tables

没有 DATABASES 表? 各数据库信息在 SCHEMATA 表中!

查看表最后修改时间/行数/大小

SELECT TABLE_NAME, UPDATE_TIME, TABLE_ROWS, concat(DATA_LENGTH/1024/1024, ' MB'), concat(INDEX_LENGTH/1024/1024,' MB') FROM TABLES WHERE TABLE_SCHEMA="database_name";

引擎

myisam 是默认的, 适合小型的应用或项目

innodb 支持事务处理和外键, 更适合大项目

tokudb Percona TokuDB

可以用 latin,比 utf8 性能高几倍。而 latin 中如果不得不放中文,可以用 urlencode

增量备份

MySQL 可通过 binary log 实现增量备份. binary log 中记录了自上一次备份(完整或增量)后, 数据库变动的 SQL 表示.

打开 binlog

binlog 默认关闭, 打开需做如下配置:

/etc/mysql/my.cnf
[mysqld]
# 省略其他配置...
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
 
log_bin			= /var/log/mysql/mysql-bin.log
binlog_do_db		= testbinlog
# binlog_ignore_db	= include_database_name
 
# 当设置了 do_db 时, 只备份 do_db
# 当设置了 ignore_db 时, 除 ignore_db 外都备份

备份

mysqldump 要用 ‘’–single-transaction –skip-lock-tables‘’ 避免锁表

$ mysqldump --single-transaction --skip-lock-tables --flush-privileges --all-databases > entire_database_server.sql

增量备份

$ mysqladmin flush-logs

打开 log_bin 后, mysql 会不断将对数据库的操作记录在 bin.log(二进制) 中, 而 flush-logs 让 mysql 结束在前一个 log 中记录, 新开 log, 这样, 前一个 log 便可安全地备份了.

但 flush-logs 有副作用, 它同时会将其他日志如 error.log flush.

查看

$ mysqlbinlog mysql-bin.000001 | grep -v '^#' | grep -v '/\*' | less

还原

参考

mysqldiff

tool and CPAN suite backend for comparing MySQL database schemas

Examples

# compare table definitions in two files
mysqldiff db1.mysql db2.mysql
 
# compare table definitions in a file 'db1.mysql' with a database 'db2'
mysqldiff db1.mysql db2
 
# interactively upgrade schema of database 'db1' to be like the
# schema described in the file 'db2.mysql'
mysqldiff -A db1 db2.mysql
 
# compare table definitions in two databases on a remote machine
mysqldiff --host=remote.host.com --user=myaccount db1 db2
 
# compare table definitions in a local database 'foo' with a
# database 'bar' on a remote machine, when a file foo already
# exists in the current directory
mysqldiff --host2=remote.host.com --password=secret db:foo bar

snippets

left join 的顺序不影响性能,应以可读性优先

mysql - Should SQL JOINs be placed in particular order for performance reasons? - Stack Overflow

The documentation for MySQL states “The join optimizer calculates the order in which tables should be joined”.

This order is determined based on information about the sizes of the tables and other factors, such as the presence of indexes.

You should put the joins in the order that makes the most sense for reading and maintaining the query.

show processlist

  • show full processlist
  • SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

死锁 deadlock

  • 记录所有死锁:修改配置(运行时,不需重启)innodb_print_all_deadlocks on
  • 查看死锁: SHOW ENGINE INNODB STATUS

在 group by 或别的列中使用 ALIAS in group by / other columns

答案是不行

SQL is implemented as if a query was executed in the following order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

general log

记录所有 SQL

mysql>set global general_log_file='/tmp/general.lg';    #设置路径,可选,默认在 /var/lib/mysql/{some_md5}.log

mysql>set global general_log=on;    # 开启general log模式

mysql>set global general_log=off;   # 关闭general log模式

update based on join

# MySQL UPDATE JOIN syntax:
 
UPDATE tableA a
LEFT JOIN tableB b ON
    a.name_a = b.name_b
SET
    validation_check = IF(start_dts > end_dts, 'VALID', '')
 
 
# ANSI SQL syntax:
 
UPDATE tableA SET validation_check = 
    (SELECT IF(start_DTS > end_DTS,'VALID','') AS validation_check
        FROM tableA
        LEFT JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

mysql < 导入时报错 ERROR 2006

ERROR 2006 (HY000): MySQL server has gone away - Stack Overflow

# Adding this line into my.cnf file solves my problem.
max_allowed_packet=64M
 
# You can also log into the database as root (or SUPER privilege) 
# and do:
set global max_allowed_packet=64*1024*1024
# doesn't require a MySQL restart as well 

另外导入时可以设置 –default-character-set=utf8,避免由于字符集问题误判 Duplicate entry for key

身份证号统计

; 年龄:
(YEAR(CURDATE()) - SUBSTRING(`card_id`,7,4)) AS 年龄,
 
; 所属省份:
CASE LEFT(`card_id`,2) WHEN 11 THEN '北京' WHEN 12 THEN '天津' WHEN 13 THEN '河北' WHEN 14 THEN '山西' WHEN 15 THEN '内蒙古' WHEN 21 THEN '辽宁'
    WHEN 22 THEN '吉林' WHEN 23 THEN '黑龙江' WHEN 31 THEN '上海' WHEN 32 THEN '江苏' WHEN 33 THEN '浙江' WHEN 34 THEN '安徽' WHEN 35 THEN '福建' WHEN      36 THEN '江西'
    WHEN 37 THEN '山东' WHEN 41 THEN '河南' WHEN 42 THEN '湖北' WHEN 43 THEN '湖南' WHEN 44 THEN '广东' WHEN 45 THEN '广西' WHEN 46 THEN '海南' WHEN 50 THEN '重庆' WHEN 51 THEN '四川'
    WHEN 52 THEN '贵州' WHEN 53 THEN '云南' WHEN 54 THEN '西藏' WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃' WHEN 63 THEN '青海' WHEN 64 THEN '宁夏' WHEN 65 THEN '新疆' END AS 所属省份,
 
; 性别:
IF (MOD(SUBSTRING(`card_id`,17,1),2),'男','女') AS 性别

查看几位数、量级

不完全精准,但对整体没有太大影响

select ceil(log10(wealth+1)) as wlevel, count(1)
from hosts
group by wlevel;
+----------+------------+
|   wlevel |   count(1) |
|----------+------------|
|        0 |         45 |
|        1 |         86 |
|        2 |        181 |
|        4 |         91 |
|        5 |        166 |
|        6 |        387 |
|        7 |        658 |
|        8 |        337 |
|        9 |         48 |
+----------+------------+
9 rows in set
Command Time: 0.000s
Format Time: 0.001s

查看性能

MySQL :: MySQL 5.7 Reference Manual :: 14.7.5.30 SHOW PROFILE Syntax

mysql> SET profiling = 1;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
 
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 ROWS affected (0.01 sec)
 
mysql> SET ROW_COUNT = 100;
# 看 100 条 profiles
 
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE;
+----------------------+----------+
| STATUS               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating TABLE       | 0.000056 |
| After CREATE         | 0.011363 |
| query END            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| STATUS             | Duration |
+--------------------+----------+
| query END          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 ROWS IN SET (0.00 sec)
 
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| STATUS               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating TABLE       | 0.000056 | 0.000028 |   0.000028 |
| After CREATE         | 0.011363 | 0.000217 |   0.001571 |
| query END            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 ROWS IN SET (0.00 sec)

按小时 group(MySQL Group By Hours)

SELECT   CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours
  ,      COUNT(*) AS `usage`
FROM     history
WHERE    created BETWEEN '2012-02-07' AND NOW()
GROUP BY HOUR(created)

Too many connections

mysql 检查 connections

/* Connections 是历史连接,
   The number of connection attempts (successful or not) to the MySQL server */
mysql> SHOW STATUS LIKE 'Conn%';
+---------------+-------+
| Variable_name | VALUE |
+---------------+-------+
| Connections   | 8     | 
+---------------+-------+
1 ROW IN SET (0.00 sec)
 
 
/* Threads_connected 或 show processlist 是当前连接 */
mysql> SHOW STATUS LIKE '%onn%';
+--------------------------+-------+
| Variable_name            | VALUE |
+--------------------------+-------+
| Aborted_connects         | 0     | 
| Connections              | 8     | 
| Max_used_connections     | 4     | 
| Ssl_client_connects      | 0     | 
| Ssl_connect_renegotiates | 0     | 
| Ssl_finished_connects    | 0     | 
| Threads_connected        | 4     | 
+--------------------------+-------+
7 ROWS IN SET (0.00 sec)
 
/*
   Time 列是当前状态的保持时间,
        The time in seconds that the thread has been in its current state. 
   Info 列是最后运行的 SQL
        The statement the thread is executing, or NULL if it is not executing any statement.        
*/
mysql> SHOW processlist;
+----+------+-----------------+--------+---------+------+-------+------------------+
| Id | USER | Host            | db     | Command | TIME | State | Info             |
+----+------+-----------------+--------+---------+------+-------+------------------+
|  3 | root | localhost       | webapp | Query   |    0 | NULL  | SHOW processlist | 
|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             | 
|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             | 
|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             | 
+----+------+-----------------+--------+---------+------+-------+------------------+
4 ROWS IN SET (0.00 sec)

关闭进程:在 mysql 中 kill processlist 的 id

``` KILL 3; ```

事务导致的问题

现象:

  • 数据更新或新增后数据经常自动回滚。
  • 表操作总报 Lock wait timeout exceeded 并长时间无反应

解决方法:

  • 应急方法:show processlist; kill掉出现问题的进程
  • 根治方法:select * from innodb_trx 查看有是哪些事务占据了表资源。
  • 我的方法:设置MySQL锁等待超时 innodb_lock_wait_timeout=50 ,autocommit=on

select for update 不是行锁,还会锁索引:Mysql 数据库死锁过程分析(select for update)_Mysql_脚本之家

bash 检查 connections

$ mysqladmin status
 
Uptime: 4661  Threads: 1  Questions: 200  Slow queries: 0  Opens: 16  Flush
tables: 1  Open tables: 6  Queries per second avg: 0.043

refs

修改 character set

# 修改表
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;
# 修改数据库
ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8;
 
# 检查
USE information_schema;
# 表
SELECT * FROM TABLES WHERE TABLE_SCHEMA='database_name';
# 数据库
SELECT * FROM SCHEMATA;

导入脚本

$ mysql < foo.sql

# 忽略运行时的错误(如重复 insert 等)
$ mysql -f < foo.sql

从命令行运行, 并将结果导出至文件

-e for execute

$ mysql -e 'QUERY' > result.out

mysqldump 常用参数

用 mysql > foo.csv 方式导出大量数据时,会由于内存耗尽退出。mysqldump 虽然有 -t -Tfoo.csv,但实际是 SELECT INTO FILE,只在 mysqld 机器上可用。

所以如果想导 CSV,需要手工做分页。分页需注意必须排序,没有默认排序这回事。

phpmyadmin 的 Export 功能更全, 如果只能用命令行的话, 可以先参考 phpmyadmin 的功能, 再查文档找 mysqldump 实现该功能的 options

  • –no-create-info
    忽略 create table
  • –no-data, -d
    no data,structure only
  • –opt
    same as specifying –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset. All of the options that –opt stands for also are on by default because –opt is on by default.
  • –compact
    the same as specifying –skip-add-drop-table, –skip-add-locks, –skip-comments, –skip-disable-keys, and –skip-set-charset options.
  • –disable-keys
    For each table, surround the INSERT statements with statements to disable and enable keys.
  • 但是 mysqldump 没有 CREATE TABLE IF NOT EXISTS 的选项, 需要 dump 后 sed 替换
  • –skip-extended-insert
    insert 一行一个 value, 较之一个 insert 多行 value(extended insert) 效率很低
  • –complete-insert, -c
    Use complete INSERT statements that include column names
  • –databases, –tables, –where
    Dump by query1)
    mysqldump --databases X --tables Y --where="1 limit 1000000"

赋予某用户表名符合某一规则的所有表的所有权限

GRANT ALL ON `foo\_%`.* TO foo@'localhost' IDENTIFIED BY ''

replace, 字符串替换

# e.g.1
mysql> UPDATE table_foo SET field_bar=REPLACE(field_bar, 'cancle', 'cancel');
 
# e.g.2
mysql> SELECT token, REPLACE(token, '|database', '') FROM USER;
+----------------------+---------------------------------+
| token                | REPLACE(token, '|database', '') |
+----------------------+---------------------------------+
| 030015|DATABASE      | 030015                          |
| 050150|DATABASE      | 050150                          |
...
+----------------------+---------------------------------+
36 ROWS IN SET (0.00 sec)

更多字符串函数 string functions: MySQL :: MySQL 5.0 Reference Manual :: 12.5 String Functions

substring_index 贴心函数!

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
 
mysql> SELECT SUBSTRING_INDEX('79297284 201709-2', ' ', 1);
        -> '79297284'
mysql> SELECT SUBSTRING_INDEX('79297284 201709-2', ' ', -1);
        -> '201709-2'

insert from select query

INSERT INTO _auth(token, password) 
SELECT concat('nk', REPLACE(token, "|nankai.edu.cn", "")) AS token, 
       'password' AS password  FROM USER 
WHERE token LIKE "%|nankai.edu.cn" ;

copy table, 复制表

CREATE TABLE student2 SELECT * FROM student

date, 日期时间函数

UNIX_TIMESTAMP() # 获得当前时间的 TIMESTAMP
FROM_UNIXTIME(unix_timestamp[,format]) # 将 TIMESTAMP 转为可读形式

change user password

mysqladmin

$ mysqladmin -u USER [-p OLDPASSWORD] password NEWPASSWORD

mysql

mysql> use mysql;
mysql> update user set password=password("NEWPASSWORD") where user='USER';
mysql> flush privileges;

删除密码:set password=null

忘了 root 密码后重置密码:Recover MySQL root Password - nixCraft

rename table

RENAME TABLE `tbl_name` TO `new_tbl_name`

root 远程登陆

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;
it/mysql.txt · Last modified: 2018/09/04 10:08 by admin