选自：mysql笔记.pdf

help命令

mysql提供的help命令，对于我们只记得命令中的关键词，但是忘记了命令的具体的参数等情况下，非常有用。 例如：

help show; help create; help grant; help drop;

还可以更进一步：我想创建一个新用户，只记得命令是’create user’开头的，但是忘记了后面一些具体参数，可以使用如下命令：

mysql> help create user;

强烈建议所有列都 not null，设置默认值。因为null有坑。

整形比字符型的字符集和校对规则更简单，相应的列的运算速度也就更快

浮点型统一选择decimal，因为float和double有精度问题，这是计算机表示浮点数的自身的固有问题

日期时间最好存储为时间戳格式，方便走索引。unix_timestamp(now()) / from_unixtime(123456) / from_unixtime(123456, ‘%Y’)

int(11)

11表示的是显示宽度；select 查询结果显示时，如果数字没有11位，会用空格补齐11位；也可以用0补齐，需要将列定义为 int(11) unsigned zerofill,则对于33，会显示为00000000033; 对于int类型的列，如果没有定义显示宽度，则默认为11,因为int类型数据的最大值是11位的。

decimal如果不设置精度和标度，则会将小数位四舍五入，最终存储在数据库中的没有小数位。

float 和 double 如果不设置精度和标度，则直接按实际精度存储和表示。

子句与子查询

子句：where / order by / limit 等都是子句 clause：分句，指的是where子句中的查询条件分句：and分句，or分句等。 子查询：查询中嵌套了别的查询。

子查询

按子查询的结果集分为4种：

标量子查询，一行一列，单个结果列子查询，一列多行行子查询，一行多列表子查询，多列多行

按照子查询的出现位置：

select后仅支持标量子查询from 后支持表子查询where / having 后，支持标量、行、列exists后，支持表子查询

如：有一张购买记录表order，有三列分别为user_id, price,year;即用户id、价格、年份。 查找每个用户购买价格最高的记录，要有user_id,price,year。

select * from order where ( user_id,price ) in (select user_id,max(price) from order group by user_id)

当where后的子查询是多列时（行或者表），必须用()将多列查询条件括起来。 当where后的子查询是多行时（列或者表），必须用in，或者用 = > < >= <= <> 与 any all some 合用 当where后的子查询是一行时（行或者标量），可以用= > < >= <= <> 与 。

又如： 有部门表departments,有列dep_id,dep_name; 有员工表employees,有列emp_id,emp_name,dep_id,salary; 有薪资等级表job_grades,有列grade_id,low,high 查询每个部门的员工个数：

select dep_name,(select count(emp_id) from employees e where e.dep_id=d.dep_id) from departments d;

查询工号为’11’的员工的部门名称：

select dep_name from departments where dep_id=( select dep_id from employees e where e.emp_id='11'); 或 select dep_name from departments d join employees e on e.dep_id=d.dep_id and e.emp_id='11'; 或 select dep_name from departments d join employees e on e.dep_id=d.dep_id where e.emp_id='11';

查询每个部门的平均薪资等级；

select dep_id,grad_id from (select dep_id, avg(salary) as s_avg from employees group by dep_id) a, job_grades g where s_avg>= g.low and s_avg<g.high

NULL的坑

对于null，只能用is null或者 is not null 来查询，其余运算符皆不可。

有表test1的数据如下：

c1c2111NULLNULLNULL

count(cname)，不会统计cname==null的记录

select count(c1),count(c2),count(*) from test1; --- 结果为： 2,1,3 select count(*) from test1 where c1 is null; ---结果为1; select count(c1) from test1 where c1 is null; ---结果为0;

= <>, in, not in, exists, not exists 这些运算都不能作用于NULL

select * from test1 where c1 = NULL; —结果集为empty; select * from test1 where c1 != NULL; —结果集为empty; select * from test1 where c1 in ( NULL ) ; —结果集为empty; select * from test1 where c1 in ( NULL, 2 ) ; —结果集为empty; select * from test1 where c1 not in ( NULL ); —结果集为empty; select * from test1 where c1 not in ( NULL, 2 ); —结果集为empty;

事务

atomicity：原子性，一个事务必须是一个原子操作。事务中的一批SQL语句要么全部成功地作用于数据库，要么全部不作用于数据库。可以参考java并发/多线程操作中的原子性问题（在java中有许多AtomicXXX类，利用了unsafe的CAS确保数据的原子性和一致性）

consistency：一致性，事务执行前，数据库中的数据在业务上是处于一致状态的；事务执行后，数据库中的数据在业务上是处于一致状态的。

isolation：隔离性，一个事务的执行不能被其它事务干扰。一个事务内部的操作及使用的数据对并发的其它数据是隔离的，并执行的各个事务之间不能

通过commit和rollback，来实现事务原子性。

通过事务的一致性视图和锁来实现事务隔离性

一致性读 、 快照读、 当前读

多版本并发控制(MultiVersion Concurrecy Control)，支持这种机制的数据库被称为多版本数据库。

mysql中的一致性读操作分为快照读和当前读。快照读是通过MVCC实现，当前读通过加锁实现。快照读：读取的不一定是当前最新的数据，而是历史数据的其中一个快照。当前读：用于先读后写的操作，即读取到数据后会立即对数据更新。更新自然必须是要在最新数据上更新，如果对旧数据进行更新，就很有可能出现更新丢失等问题。当前读会对读到的数据加锁后，再进行更新操作。如果数据已经被其它事务加锁（即其它事务正在操作这些记录），那么此事务就必须等待直到获得数据的锁，才能对数据读取并更新。普通的select操作都是快照读。显式地select …lock in share mode / for update 是当前读。在事务中的update \ delete 等操作也都是当前读。

如何实现快照读

每个事务启动前，都必须申请到一个事务id，事务id在记录中体现为一个隐藏字段tx_id。每个事务启动时，系统都会为其创建一个数组、这个数组中存储的是在事务启动的瞬间当前所有正在活动的事务的id，这被称为这个事务的一致性视图当事务要读取数据时，系统会根据每行数据的tx_id的值，判断这行数据对当前事务是否可见。如果不可见，就通过undo file文件，回退到上一版本，然后再判断。一直回退到根据数据的tx_id判断，数据对当前事务可见的版本，这样的读取方式就是快照读。所有对当前事务可见的版本的数据一起，就是对于当前事务的一个一致性数据快照。

如何实现当前读

当进行update或者delete\insert时，系统会给要更新的数据加锁，如果对数据加锁成功，则可以对数据进行读取并更新。如果数据已经被其它事务锁定，那就必须等待其它事务释放数据的锁。

不同的事务隔离级别如何实现

读未提交：读取和更新都不做任何控制。不采用MVCC功能，不加锁。读已提交：读取采用快照读，但是事务中每次读取都重新生成一致性视图、读取的是最新的快照数据。因此可以避免脏读、但会出现不可重复读的情况。更新时加锁，对于 lock in share mode \ update \ delete等都会加锁，因此不会出现第一类更新丢失。可重复读：读取采用快照读，且事务过程中都只读取事务启动瞬间的一致性快照，因此不会出现不可重复读。另外会加间隙锁，因此不会出现幻读。更新时加锁，不会出现更新丢失。但是会出现第二类更新丢失。

myisam的伪事务实现方式

myisam引擎本身并没有提供事务管理的功能。但是可以用lock tables t_name read | write，通过对表加锁来实现伪事务，隔离事务，实现安全的并发操作。

锁

共享锁（S）：事务获得共享锁后，只能读，不能更新。其它事务还可以继续获得相同记录的共享锁。 用户可以通过lock in share mode，对数据加S锁。 用户没有显式声明lock in share mode的select语句，在innodb内部都是一致性读实现的事务隔离，并不加锁。排他锁(X-eXclusion)：事务获得排他锁后，可以更新。其它事务不能获得相同记录的任何锁。 用户可以通过显式地声明 for update，对数据加X锁。

对于已经加了X锁的数据，虽然无法再加其它锁，但是仍可以对其做select操作，因为普通的select查询并不需要加锁。

行锁：在innodb中，查询条件上有索引时，单独的对某些索引加锁。没有索引时，就只能加表锁了，会造成冲突。注意是对索引加锁，不是对记录。因此如果一条索引下有多个记录，即便两个事务是要对同一个索引的不同记录加锁，仍然会有锁冲突。

间隙锁（gap-lock）：间隙是指，当我们进行范围查询时，如果结果集有多行，则在范围内、相邻两行之间的不存在的记录被称为“间隙”。innodb也会对这个间隙加锁，就是间隙锁（next-key lock）。 另外在进行相等的当前查询并时，如果要查询的记录不存在，也会加间隙锁。 例如：一个session执行如下查询：begin; select id,name from t_user where user_id=101 for update。 如果这时另一个session 执行插入：insert into t_user values(201,…)。会等待

next-key lock：行锁和间隙锁的组合实现。

表锁：查询条件上没有索引时，锁全表。 lock table t1 read;–表共享锁。lock table t1 write;–表独占锁

如果某个间隙已经被事务加了间隙锁，并不会阻止其它事务对同一个间隙加间隙锁，只是会排斥其它事务向间隙中插入数据。

意向锁

意向锁的是表级的，其目的是为了使得事务间的协调效率更高。 当一个事务需要对表加表独占锁时，首先要确保表没有被其它事务加了表共享锁、表独占锁、表也没有任何记录被加共享锁或者独占锁。 表共享锁、表独占锁标识都是打在表上的，很容易直接检测到是否已经被其它事务加锁。 但是对表中行的锁标记是打在了行上的，如果一行行去检查是否有行被加了锁，效率就很低了。 解决方案就是在给表中记录加上共享锁或者独占锁之前先给加个意向锁，相当于给表打个标记，标记此表中的记录会被加锁。 此时其它事务再加锁的话，就可以看到表上已经打了表级意向锁标识，不用再专门加锁了。

意向共享锁(IS,Intention-S)； 是指事务打算给表中的数据加共享锁，事务在给数据加共享锁之前必须先取得表的IS锁。是Innodb自动加的，用户无法主动干预。意向排他锁(IX)：是指事务打算给表中的数据加排他锁，事务在给数据加X锁之前，必须先取得表的IX锁。是Innodb自动加的，用户无法主动干预。

显式地在sql语句中使用 lock in share mode时。会对符合条件的记录加意向共享锁。如果没有符合条件的记录，则会在小于条件的最大关键字和大于条件的最小关键字之间加间隙锁？

innodb对于每种隔离级别采用的策略

串行化读：innodb会对所有事务的select查询，都隐式转换为 lock in share mode。可重复读：对于事务中的select 查询，采用一致性读取，对于同一个查询的多次执行，都只读第一次执行查询读取的那个版本的快照，因此不会出现不可重复读。对于 lock read\updae\delete，则采用锁策略。如果where后的查询条件上有唯一索引，则只锁定相应的索引记录。如果查询条件上没有唯一索引，则使用gap-lock或者next-record lock，来锁定扫描的索引范围，因此不会出现幻读。读已提交；对于事务中的select读取操作，采用一致性读取。对于同一个查询的多次执行，每次执行查询时都从数据库读取最新版本的快照，即采用半一致性读取，因此会出现不可重复读。而对于lock read\upate\delete，则只对数据加行级锁，不会加间隙锁，因此可能会出现幻读。

事务提交方式：隐式 与 显式

事务提交方式有两种：自动提交 和 手动提交。 隐式的事务提交方式：事务提交的默认方式，show variable like ‘%autocommit%’，一般是ON，即每条语句都会自动commit; 显式事务提交：如果隐式的事务提交方式状态不符合业务需求，可以通过set autocommit ON/OFF; 来设置后续语句的事务提交方式。

部分回退 与 保留点(savepoint xxx)

有时需要根据实际业务状况，对SQL语句做部分回退。 比如：

start transaction; sql1; savepoint s1; sql2; savepoint s2; sql3; savepoint s3; ... if(...) rollback to s1; --在某种情况下，业务状态需要回退到s1处，后续commit提交时，只会提交sql1。其后的语句都不会被提交至数据库。 if(...) rollback to s2; --在某种情况下，业务状态需要回退到s2处，后续commit提交时，只会提交sql1和sql2。其后的语句都不会被提交至数据库。 ... commit;

变量

系统变量前要用@@，自定义全局变量前要用@，自定义局部变量前什么也不用

系统变量

查看所有系统变量：show [ global|session ] variables;模糊查询某个 全局/当前会话 系统变量：show [ global|session ] variables like ‘tx%’;精准查询某个全局/当前会话 系统变量：select @@[ global|session ].var_name;设置 全局/当前会话 系统变量：set @@[ global|session ].var_name=‘var_value’;

自定义全局变量（作用域为当前会话）

定义变量：set @var_name=‘var_value’;查询变量：select @var_name;使用变量：update t1 set column1=@va_name;

自定义局部变量（自定义函数或者存储过程中）

声明：declare var_name;赋值：set var_name=‘var_value’;使用：select column1 into var_name from t1 where…

数据类型

enum

tinyint 1字节 smallint 2 midiumint 3 int 4 bigint 8

float 4 double 8 decimal(m,d) M+2

date 3(1000-01-01~9999-12-31) 15位(32768)-4位-5位 time 3(-838:59:59~838:59:59) 符号位2位？10位:6位:6位 year 1(1901~2155) 254 个值,8位即可。 datetime 8 timestamp 4(1970-01-01 00:00:00~2038-01-19 11:14:07) 1个符号位，2147483647个值，

char 1-255自定义；如果实际长度不足，则补足 varchar 1-255自定义：如果实际长度不足，不补，长度仅用作限定用。

tinyBlob（255个）blob（65535个）

profiling / profiles --显示最近执行的各个sql的耗时，以及sql执行过程中各个步骤的耗时；

select @@profilling; set @@profiling=1; show profiles; HELP SHOW PROFILE; show profile all ; show profile for query n;

当出现以下步骤时，说明需要优化

tmp table --临时表

redo log

redo log 的意义：redo log可以恢复mysql服务器因突然断电而丢失的事务。还可以用于主从复制。mysql服务器突然断电时为什么会丢失事务数据？因为客户端提交的事务数据会被mysql服务器缓存在服务器的内存中(buffer)，不会即时写入磁盘，因此突然断电有些事务数据会丢失。redo log为什么可以做到恢复因断电丢失的数据？因为每次数据更新时，mysql同时会将修改后的数据写入redo log中。当mysql服务器断电恢复后，会比较数据的tx_id与redo log中的tx_id，如果发现不一致就根据redo log重新提交事务。mysql的事务更新后的数据为什么不即时写入到磁盘文件？每次事务提交时，都做一次redo log，io一次，岂不是增加了耗时？降低了效率？ 因为mysql的数据文件的io是随机io，随机io每次都要寻道、旋转磁头，比较耗时。 而redo log的io是顺序io，顺序io由于每次写io时只需接着上次的位置继续往下写，不需要寻道、旋转磁头，非常快。redo log也有缓冲区，这会不会导致断电数据丢失问题？

redo log的实现原理

redo log只需确保未被刷回磁盘的事务被log即可。实现这一目的的方式是：redo log文件固定大小，当文件被写满后，就将缓存中的事务数据写入磁盘中去。是大小固定的、会被不断truncate的空间。 redo log在内存中也有缓存区，每秒会将缓冲区中的数据IO到磁盘。每当有事务提交时，也会将缓冲区中数据刷出到磁盘。 redo log的日志文件大小对mysql系统的影响： 设置较大值的情况下，顺序IO的重新寻道和旋转磁头的次数降低，可以大大提高顺序IO的性能。但是故障后恢复时，会由于redo log文件较大而增大数据恢复时间，在之前的mysql版本中，这会很慢，但是现在的版本不用再考虑这些问题，放心使用即可，

redo log相关参数

innodb_log_file_size:redo log 日志文件大小

binlog

binlog记录了所有操作的日志（除了select 和 show这类），即便操作目标行数为0。如果update …set … where …，即使根据查询条件没有匹配的记录，没有对任何记录进行update，也会被记录。 binlog是服务器层的功能。 binlog记录的是逻辑日志，是sql语句。redo log记录的是物理日志，是数据。 binlog只在一个事务提交完成后记录一条或几条sql语句。而redo log则是在事务进行中不断写入。 redo log的日志写入是并发的，多个事务进行中都在不断写入redo log.

analize \ check \ optimize table

mysql> help ana%; Name: ‘ANALYZE TABLE’ Description: Syntax: ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] …

alter table tbx

rename as tbnewx;–重命名表名称 add column cname … firset | after column_name; --添加字段 add index idx_name(column1_name,…);–添加索引 add primary key(column_name);–添加主键 drop primary key; drop index idx_name; add unique idx_name(column1_name,…);–添加唯一索引 alter column column_name set default xx | drop default;–设置默认值，取消默认值 change column column_name column_name_new …;–重新定义某字段，可以重命名字段 modify column column_name …;–重新定义某字段，但不能重命名 drop column column_name;–删除某字段

create table if not exists tbx_new like tbx;–复制表结构

create table tbx_new as select * from tbx;–复制表内容

desc tbx

show columns from tbx; show create table tbx;

系统信息函数

select version(),connection_id(),database(),schema(),user(),system_user(),session_user(),current_user(),charset(str),last_insert_id();

加密函数

password(‘pwd’),md5(‘xx’),encode(str,pswd_str)

日期时间函数

curdate(),curr_date()，curtime(),current_time(),now(),current_timestamp(),localtime(),sysdate(),localtimestamp();