MySQL基础

参考:《深入浅出MySQL》

SCHEMA

schema 为数据库对象的集合，schema里面包含了各种对象如tables, views, sequences, 可以视作”数据库”, show database可以看到schema.
不同数据库产品的schema概念是不同的:

MySQL: Schema 等同于 Datebase
SQL Server: schema中包含了数据库的表，字段，数据类型以及主键和外键的名称。

数据类型

数字:
- 整数:
  - TINYINT 1, SMALLINT 2, INT 4, BIGINT 8
- 小数:
  - 浮点数: float, double
  - 定点数: decimal, 常用来表示高精度数据, 比如货币
字符串:
- CHAR 255, 定长
- VARCHAR 65535, 变长
- TEXT: 还分为TEXT(65535), MEDIUMTEXT, LONGTEXT
- BOLB: 还分为BOLB(65535), MEDIUMBOLB, LONGBOLB, 与TEXT的区别是, BOLB可以存储二进制数据, 比如图片
日期:
- DATE: 2017-07-25
- DATETIME: 2017-07-25 21:57
- TIMESTAMP: 字符串的”2017-07-25 21:57”

DATETIME 和 TIMESTAMP类型的区别:
DATETIM 和 TIMESTAMP类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。
前者范围为 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.
其次，TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
第三，TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

运算符

符号	描述	备注
=	等于
>	大于
<	小于
!=	不等于	不可比较NULL, 例 1 != NULL也返回NULL
<>	不等于	同上
<=>	等于	NULL-Safe的比较, NULL<=>NULL为1, 1<=>NULL为0
IS NULL	等于NULL
IS NOT NULL	不等于NULL
BETWEEN	在两值之间
IN	在集合中
LIKE	模糊匹配

函数

字符串拼接: CONCAT(s1, s2, …)
LOWER(s1), UPPER(s1)
FLOOR(a): 返回小于a的最大整数
MOD(a,b): 返回x/y的模

字符集(编码)

查看数据库使用的字符集:

# 方式1
mysql> status;

# 方式2
show variables like 'character%';

如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。相对于UTF-8而言，GBK比较“小”，每个汉字只占2个字节，而 UTF-8汉字编码需要3个字节，这样可以减少磁盘I/O、数据库Cache以及网络传输的时间，从而提高性能。
如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8更好，因为GBK、UTF-16的西文字符编码都是2个字节(汉子3字节)，会造成很多不必要的开销。
utf8 vs utf8mb4
- utf8 表示西文需2字节, 汉字需3字节, 如果在utf8编码上使用vchar(100)这种类型, Mysql会为该列保留 “一个utf8最大占用空间x100” 也即 300字节.
- utf8 存在的问题: uft8最大能编码的Unicode范围是3字节, 对于超过3字节的无能为力(包括一些汉字, 以及emoji表情)
- utf8mb4 (后缀mb4意思是”most bytes 4”), “4字节 UTF-8 Unicode 编码”, utf8mb4可以最多表示4字节Unicode编码, utf8是utf8mb4的一个子集, utf8mb4使用与utf8相同的编码值和长度, 此外utf8mb4还包括utf8没有的4字节编码, 因此从旧版本的MySQL UTF8 升级数据时不用担心字符转换或丢失数据

@Ref 全面了解mysql中utf8和utf8mb4的区别 - 谢思华blog - OSCHINA

主键 & 外键

主键: 一个表只能有一个列作为主键, 主键的值不可重复, 不可为空(NULL)
- 主键一定是唯一性索引，唯一性索引并不一定就是主键
外键: 一个表中的FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
- FOREIGN KEY 约束用于预防破坏表之间连接的动作。
- FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
外键约束：
- (1)插入非空值时，如果主键值中没有这个值，则不能插入。
- (2)更新时，不能改为主键表中没有的值。
- (3)删除主键表记录时，可以在建外键时选定外键记录一起联删除还是拒绝删除。
- (4)更新主键记录时，同样有级联更新和拒绝执行的选择。

大家设计数据库时使用外键吗？ - 知乎
互联网行业应用不推荐使用外键：用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受IO能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩；
所以Hibernate多对一（many-to-one）/一对多（one-to-many）关联也就很少提及了。

Hibernate，JPA 对象关系映射之关联关系映射策略

索引

① 按照索引的物理存储来分:

聚集索引(clustered index)：聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key来约束。主键索引对应的B+树, 叶子节点是一行的完整数据
非聚集索引(non-clustered index)：它并不决定数据在磁盘上的物理排序，其叶子节点的数据是主键的值。所以使用普通索引查询的时候，需要先找到对应的主键值，再回主键索引的B+树上找到行数据（回表）

② 从逻辑角度, MySQL一共有五类索引:

唯一索引(UNIQUE INDEX), 唯一索引是不允许其中任何两行具有相同索引值的索引。主键是一种唯一性索引，它必须指定为“PRIMARY KEY”
主键索引：主键索引是一种特殊的唯一索引，不允许有空值, 主键索引也是聚簇索引
普通索引：非主键索引, 最基本的索引类型，没有唯一性之类的限制。
联合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用联合索引时遵循最左前缀集合
注意：建了一个(a,b,c)的联合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，每多一个索引都会增加写操作的开销和磁盘空间的开销。
候选索引：与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

③ 从数据结构角度:

BTREE索引: MyISAM和InnoDB存储引擎默认都是BTREE索引
HASH索引：
- 仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询
- 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
- 只有Memory存储引擎显示支持hash索引
FULLTEXT索引: 全文索引，在 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。FULLTEXT索引也是按照分词原理建立索引的。
RTree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

建立索引的原则

尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
限制索引的长度, 尤其是 FULLTEXT索引, 比如只在VARCHAR字段的前20字节做索引;
字符类型和数字类型作为索引的性能差别，肯定是用数字类型索引更好。
用字符串做索引扫描是否会有性能损耗？两者的主要差别就在于，字符类型有字符集的概念，每次从存储端到展现端之间都有一个字符集编码的过程。而这一过程主要消耗的就是CPU资源；对于In-memory的操作来说，这是一个不可忽视的消耗。如果要固化到具体测试结果，我们这边的经验数据是20%，具体值还是和环境和数据有关系。此外，latin1 和 UTF8 之间也有10%左右的性能差别。
时间加索引的话，性能上TIMESTAMP > DATETIME

MySQL引擎

InnoDB
- 支持事务
- 支持行级锁
MyISAM
- 不支持事务
- 支持表锁, 不支持行级锁 // 并发性能差
- 设计简单，某些场景下性能很好，例如获取整个表有多少条数据count(*)，性能很高。
Memory
- 不支持事务
- 支持哈希索引

SQL语句

MySQL官方文档中提供了一套示例数据库 Employees, MySQL官方文档中说明详见 http://dev.mysql.com/doc/employee/en/employee.html。里面详细介绍了此数据库，并提供了下载地址和导入方法。

mysql测试数据库employees一些sql语句_数据库_wepe12的博客-CSDN博客

插入

INSERT INTO table_name ( field1, field2,...fieldN )
                    VALUES
                    ( value1, value2,...valueN );

删除

DELETE FROM table_name [WHERE Clause]

更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

单表查询

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

子句: WHERE, HAVING, GROUP BY, ORDER BY, LIMIT

按照被执行的顺序: from、where、group by、having、select、order by、limit

WHERE: 是唯一一个是直接从磁盘获取数据的时候就开始判断的条件, 从磁盘取出一条记录, 开始进行where判断:判断的结果如果成立就保存到内存中, 如果失败则直接放弃
GROUP BY: 根据一个或多个列, 对结果集进行分组, 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
- 支持的聚集函数：SUM() 统计和, COUNT() 统计分组后的记录数: 每一组有多少记录, AVG() 统计平均值
- 示例1: 可以GROUP BY多个字段:
  SELECT user_name, SUM(order_price) FROM ORDER_TAB GROUP BY user_name, order_data
- 示例2: GROUP BY的回溯统计WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
  SELECT user_name, SUM(order_price) FROM ORDER_TAB GROUP BY user_name WITH ROLLUP 返回的数据会多一行, 该行的SUM等于GROUP返回结果再做一次求和
HAVING: 对GROUP BY的结果进行条件筛选, HAVING子句一般跟在GROUP BY子句后面。在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
- HAVING与WHERE的区别: WHERE是直接从磁盘取数据, 查询出的数据放入内存, 然后可以用聚合函数+GROUP BY分组, 分组后可以用HAVING再筛选
- 示例: SELECT user_name, SUM(order_price) FROM ORDERS_TAB GROUP BY user_name HAVING SUM(order_price)>100
ORDER BY: 排序
- 示例: SELECT order_id, order_price FROM ORDER_TAB ORDER BY order_data desc
LIMIT：限制
- 示例: SELECT * FROM table LIMIT 10 # 取出10条
- 示例: SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.
- 示例: SELECT * FROM table LIMIT 5,10; # 返回第6-15行数据

列去重

DISTINCT 列名: 根据该列名, 在结果中去重
- SELECT distinct(task_id), task_name from task; –带有distinct的列必须在第一个
- 但是上面的语句要id和name都相同的情况下才能虑重, 所以用下面的方式:
- SELECT task_id, count(DISTINCT task_name) FROM table –与其他函数使用时候，没有位置限制

WHERE的比较

WHERE查询支持的条件:

逻辑运算符与或非: AND, OR, NOT
运算符: >, <, <=, >=, =, like, between ... and, in, not in

下面是例子:

NULL:
- SELECT * FROM 表名 WHERE 字段名 IS NULL;
- SELECT * FROM 表名 WHERE 字段名 IS NOT NULL;
LIKE:
- SELECT * FROM 表名 WHERE 字段名 LIKE '%COM' # %多个字符，_单个字符
IN：
- WHERE column_name IN (value1,value2,...)
<, >, >=, <=
- 时间条件：
  - select count(*) from sometable where datetimecolumn>='2010-03-01 00:00:00'
  - select count(*) from sometable where datetimecolumn>=UNIX_TIMESTAMP('2010-03-01 00:00:00')

多表查询

多表查询包括: 隐性连接和显性连接,
WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。
JOIN……ON子句产生的连接称为显性连接。WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

隐性连接: SELECT T1.ID, T1.COLA, T2.COL2 FROM TABLE1 AS T1, TABLE2 AS T2 WHERE T1.ID=T2.ID

JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

SELECT T1.ID, T1.COLA, T2.COL2
FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2
ON T1.ID=T2.ID

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

多次JOIN:

select collum from TABLE1
LEFT JOIN TABLE2 ON condition2
LEFT JOIN TABLE3 ON condition3

UNION

UNION：操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。UNION会去掉重复的行, 但UNION ALL不会。

select columnA from table1
UNION
select collumB from table2

columnA和columnB必须是同类型

子查询

用括号()括起来的一个完整查询语句相当于一个Table, 子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。

子查询作为查询条件:

如果子查询返回单个结果, 可以用例如=，>，<等与子查询比较 :

SELECT customerNumber, checkNumber, amount FROM payments
WHERE amount = (SELECT MAX(amount) FROM payments);

如果子查询返回多个结果, 可以用 IN和NOT IN:

SELECT customerName FROM customers
WHERE customerNumber NOT IN (
    SELECT DISTINCT customerNumber FROM orders
);

FROM子句 + 子查询:

SELECT MAX(items), MIN(items), FLOOR(AVG(items))
FROM (
    SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails
    GROUP BY orderNumber
) AS lineitems;

EXISTS和NOT EXISTS: 当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询返回一个布尔值为TRUE或FALSE的值。以下查询说明了与EXISTS运算符一起使用的子查询：
SELECT * FROM tb_students_info
WHERE EXISTS (
SELECT dept_name FROM tb_departments WHERE dept_id=1
)

索引原理

参考列表:

B-Tree

B-Tree不是 Binary Tree（二叉树，每个节点最多有两个子树），B的意思是Balance, 一棵M阶的B-Tree满足以下条件：

每个结点至多有M个子节点；
除根结点和叶结点外，其它每个结点有至少有M/2个子节点；
根节点至少有 2个子节点；
所有叶子结点在同一层（所有叶节点具有相同高度）
每个非叶子节点由n-1个key和n个指针组成，其中 $ M<=n<=2M $, key和指针互相间隔，节点两端是指针。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
一个节点中的key从左到右非递减排列。

下图是一个M=2的B-Tree:
B-Tree

B+Tree

与B-Tree相比，B+Tree有以下不同点：

~~每个节点的指针上限为2M而不是2M+1~~
非叶子节点没有data, 只存储key(关键字) 和key之间的指针;
叶子节点只存储data和key, 没有key之间的指针;
相邻的叶子节点之间都有一个链表指针, 指向下一个key, 方便范围查找;

B+Tree

B-Tree复杂度分析和比较

B-Tree的时间复杂度分析(数阶=M, 树的高度=h, 索引了N个key):

树高h的上限: $\log_M ((N+1)/2)$
检索一个key的时间复杂度为: $O\log_M N$
插入复杂度: 插入会发生结点的分裂操作。分裂操作可以认为是常数级别, 所以插入时间复杂度基本等于 $O\log_M N$, 当然这是B-Tree都在内存中的情况
实际情况下, 数据库的B-Tree的节点并不都是在内存中, 插入操作会引起磁盘IO, 所以分析MySQL的B-Tree的插入代价还有考虑磁盘IO:
当插入操作引起了s个节点的分裂时，磁盘访问的次数为h(读取搜索路径上的节点)＋2s(回写两个分裂出的新节点)＋1（回写新的根节点或插入后没有导致分裂的节点）。因此，所需要的磁盘访问次数是h+2s+1，最多可达到3h+1。因此插入的代价是很大的。
删除复杂度: 理想情况下$O\log_M N$,
删除操作的磁盘IO次数: B-Tree的删除会发生结点合并操作。最坏情况下磁盘访问次数是 3h ＝（找到包含被删除元素需要h次
读访问）+（获取第2至h层的最相邻兄弟需要h-1次读访问）+（在第3至h层的合并需要h-2次写访问）+（对修改过的根节点和第2层的两个节点进行3次写访问）

一般实际应用中，M是非常大的数字，通常超过100，因此h非常小（通常不超过3），业界公认MySQL单表容量在1KW以下是最佳状态，因为这时它的BTREE索引树高在3~5之间。

MyISAM和InnoDB索引区别

MyISAM的主键or非主键索引, 叶子节点存储的都是数据的地址, 因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
InnoDB: 主键的索引B+Tree, 叶子节点存储的数据即一条完整记录(聚集索引), InnoDB的辅助索引的叶子节点存储的是 主键的值 (非聚集索引)

MySQL如何使用索引

MySQL的索引是B+Tree实现, 数据库表中每个索引都可以认为是一个B-Tree, 同B-Tree相比, B+Tree相邻叶子节点之间可以通过指针遍历

聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点存储的是内容是主键的值，通过非聚簇索引查询要再回主键的B树查询才能得到数据(回表), 所以也叫二级索引

使用索引

① 查询尽量使用到索引，避免使用全表扫描

MySQL中能够使用索引的典型场景, :

匹配全值（Match the full value），对索引中所有列都指定具体值(例如= 或者IN)，即对所有索引列都有匹配条件。
匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。例 where id > 10 and id < 25
匹配最左前缀（Match a left most prefix），仅仅使用索引中的最左边列进行查找，比如在 col1+col2+col3 字段上的联合索引能够被包含col1、（col1+col2）、（col1+col2+col3）的等值查询利用到，可是不能够被col2、（col2+col3）的等值查询利用到；mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如查询条件 (col1=1 and col2=2 and col3>3 and col4=4), 其中col4没有用到索引;
匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。select title from film_text where title like 'AFRICAN%';
如果列名是索引，那么使用 column_name is null 可以使用索引

② 不同类型的索引对性能有影响

对于频繁写入的情景, 普通索引比唯一索引更快 // why ?
对于查询的情况, 非聚簇索引比聚簇索引多一次回表, 如何避免非聚簇索引的回表?

③ 避免会索引失效的操作

在索引上使用函数: select * from trade where month(data) = 7
索引的类型转换: 字段定义类型varchar, 但是查询语句用 where id > 7, 相当于给id索引加上了CAST函数(隐式类型转换总是低精度类型→高精度类型)
*
<> 、not in、not exist、!=
like “%_”百分号在前
单独引用复合索引里非第一位置的索引列
不要将空的变量值直接与比较运算符（符号）比较, 应使用 IS NULL 或 IS NOT NULL 进行比较

待整理: 导致索引失效的可能情况

SQL优化

SQL优化一般步骤:
1 查询慢查询日志,找到语句;
2 使用explain;

事务和锁

MySQL中的锁

@TODO

事务特性：ACID

如果一个数据库声称支持事务的操作，那么该数据库必须要具备以下四个特性（事务的特性）：

原子性 Atomicity：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性 Consistency：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
隔离性 Isolation：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 Durability：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的隔离级别

▶ 脏读、幻读、不可重复读:

脏读: 就是指当一个事务正在访问数据, 并且对数据进行了修改, 而这种修改还没有提交到数据库中, 同时另外一个事务也访问这个数据, 然后读到了这个数据
不可重复读：一个事务前后查询 同一行记录两次, 两次查询到的记录不一致 (期间另外一个事务对 此行数据进行了修改并提交);
幻读：一个事务内前后两次查询 (相同的where条件), 查询出来记录的数目不一致 (期间有其他事务进行了del/insert), 好像之前读出来的是幻觉(phantoms);

▶ 事务的隔离级别:

未提交读(Read Uncommitted)：允许脏读, 也就是可能读取到其他事务中”未提交事务”修改的数据;
提交读(Read Committed)：(Oracle默认级别) 避免了脏读, 仍有”幻读”和”不可重复读”, 即一个事务中能读取到其他事务提交的数据;
可重复读(Repeated Read)：(InnoDB默认级别) 避免了不可重复读, 在同一个事务内的查询都是事务开始时刻一致的, 同时该级别下通过Gap锁机制避免了幻读;
串行读(Serializable)：完全串行化的读，~~~每次读都需要获得表级共享锁~~~，写锁排斥读写, 读锁排斥写 // 这里是加表级锁还是行锁? 需要根据where条件匹配到的列是主键/唯一/非唯一/非索引几种情况具体分析

参考列表:

@Ref Mysql官网对幻读行的介绍: Phantom Rows

数据库的锁

锁分为: 全局锁(数据库锁), 表级锁(表锁, meta锁), 行锁;

MyISAM 不支持行锁, 不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

表锁的使用:

lock table xxx read； // 读锁
// 其他线程只可以对表select查询，查询立刻返回
// 但是无法对update/delete，操作会阻塞
unlock tables

本机事务

start transaction; // 开始一个事务
// 开始事务后, 表是没有锁的, 因为start transaction会隐式的unlock表
语句
savepoint xxx;
语句
rollback to savepoint xxx;
// 在commit 之前, 其他线程可以查询表, 但看不到上面的结果
commit;
// 在commit 之后, 其他线程才可以看到改变

分布式事务

xa start 'test', 'db1';  -- 启动xid='text', 分支='db1'的分布式事务
SQL 语句...
xa end 'test', 'db1';
xa prepare 'test', 'db1'; -- 分支db1进入prepare状态
-- 必须xid='test'的所有分支都commit, 事务才算完成
xa commit 'text', 'db1';

DB1	DB2
xa start ‘transxxx’ ‘db1’	xa start ‘transxxx’ ‘db2’	开始事务
insert db1 ..	update db2 …
xa end ‘transxxx’ ‘db1’	xa end ‘transxxx’ ‘db2’
xa prepare ‘transxxx’ ‘db1’	xa prepare ‘transxxx’ ‘db2’	进入Prepare阶段
xa commit ‘transxxx’ ‘db1’	xa commit ‘transxxx’ ‘db2’	提交事务

命令说明:

开始事务: xa start xid, xid是某个分布式事务的唯一id, xid分三部分: 分布式事务标识+事务分支标识+formatid
- 上面的例子只有 “分布式事务标识+事务分支标识”, 后者用db1/db2表示在不同库上的事务分支
xa prepare:
xa commit:

MySQL Commands

连接

mysql -h 127.0.0.1 -u xxx -pXXX -P 3306

# --auto-rehash 启用自动补全(但我试了没用)
mysql -h主机地址 -P端口 -u用户名 -p密码 -D数据库名 --auto-rehash
#　Mysql的`schemas`和`数据库名`是等同的

# 连接成功后可以输入以下命令, 分号是必须的:
show databases;
use db_name;
show tables;
desc table_name; # 查看表结构
use table_name;

# 如果输入了一半sql命令想要放弃, 加上'\c'即可
select * from table_name \c

# 如果忘记了table在那个database 或schema, 表占用大小以及行数:
select * from  information_schema.tables where table_name = 'xxx'

explain

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了。所以使用explain可以查看。

导出

导出数据库结构,不带数据: mysqldump -h ip_addr -uxxx -pxxx -d DBName > dump.sql
如果要一并导出数据, 去掉-d参数.
导出表, 不带数据: mysqldump -h ip_addr -uxxx -pxxx -d DBName TableName > dump.sql

导入

mysql -u username -p -h localhost DATABASE-NAME < data.sql

mycli

官网: https://www.mycli.net/

安装:

pip install mycli

或:

brew update && brew install mycli

Usage:

# 获取帮助
mycli --help

# 连接数据库
mycli -h 主机地址 -p 端口 -u 用户