# 存储引擎

常见的几种存储引擎：

MyISAM ：一个传统引擎。不支持外键、事务、行级锁，只支持表级锁。
InnoDB ：从 MySQL 5.5 开始成为默认引擎，支持外键、事务、行级锁。
- 一般情况下使用 InnoDB 引擎更好。

# 外键

：让表 A 中的一个字段引用表 B 中的一个字段，从而让两张表的每行数据建立映射关系。

建立外键的条件：
- 表 A 的外键字段的值在表 B 中都存在。
- 表 B 中被引用的字段是 unique 的。
外键的几种映射关系：
- 一对一：表 A 中的数据与表 B 中的数据一一对应。
- 多对一：表 A 中的多条数据对应到表 B 中的一条数据。（此时，要在多的那方数据表中定义外键）
- 多对多：表 A 中的多条数据对应到表 B 中的多条数据。（此时，可以在任意一方数据表中定义外键）

定义外键的例子：

create table tb1(
    id int primary key auto_increment,     -- 定义字段 id ，并声明为主键
    num int not null,                      -- 定义字段 num
    foreign key(num) references tb2(id)    -- 将字段 num 声明为外键，引用 tb2 表的 id 字段
);

alter table tb1 add constraint num foreign key(num) references tb2(id);  -- 将字段 num 声明为外键

alter table tb1 drop foreign key num;      -- 取消外键

在 tb1 中插入一行包含外键的数据时，该外键值必须在 tb2 中存在，否则会报错。
删除 tb2 之前，必须先删除 tb1 中关联到它的所有行数据。

# 索引

：基于数据表中的某些列建立一个有序表，使得以后查询这些列时会更快，而不必遍历全表。

如果在索引表中找到了匹配的数据，MySQL 就能根据这条索引所映射的物理地址，直接读取完整列的原数据。
- 如果在索引表中没有找到匹配的数据，MySQL 依然会去遍历全表。
索引可以使查询速度更快。但每次修改数据时都需要刷新索引表，导致 insert、update、delete 操作变慢。
适合使用索引的情况：
- 表的主键就是一个唯一索引。
- 经常在 where 子句中出现的字段。
- 需要排序的列。
不适合使用索引的情况：
- 数据量不是很多的表。（优化效果不明显）
- 经常增删改的字段。
- 取值容易重复的列。（此时 MySQL 会优先选择遍历全表）

MySQL 在查询时使用索引的条件：

如果使用 where A = B 进行查询，当字符串 B 包含单列索引的那个字段、或组合索引的第一个字段时，才会使用相匹配的索引。
- 另外，如果 B 没有加上双引号，可能会被 MySQL 当做其它数据类型处理，而不使用索引。比如 where name=123 就会被看作 int 类型。
如果使用 where A or B 进行查询，
如果使用 where A like B 进行查询，当字符串 B 是常量、且不以 % 开头时，才会使用相匹配的索引。
如果 MySQL 判断使用索引的效率比全表扫描更低，就不会使用索引。

# 单列索引

：从单个列生成一个索引列。

适合优化只考虑一个字段的查询语句，比如：where name='one'

# 组合索引

：由多个列组成一个索引列。

定义的方法与单列索引类似：

alter table tb1 add index id_name(id, name);

组合索引适合优化同时考虑多个字段的查询语句，比如：where id>1 and name='one'
一张表可以有任意个单列索引、组合索引。

# 全文索引

：用于处理大文本。

# explain

可以在执行 SQL 命令时加上 explain 关键字作为前缀，分析执行命令时的查询过程。如下：

mysql> explain select * from tb1 where id!=0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | tb1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 20032  |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

type ：查询类型。效率从高到低依次分为：

const   # 查询某个唯一的索引键，找到了就立即返回。比如唯一索引
eq_ref  # 每个索引键只对应一行数据。比如唯一索引
ref     # 每个索引键可能对应多行数据
range   # 只检索索引表的某个范围。比如 where...between、in、is null
index   # 遍历了索引表
all     # 遍历了全表，效率最低

possible_keys ：该查询可能使用的索引。
key ：实际使用的索引。
ref ：使用了索引表的第几列。
rows ：找到目标数据需要读取的行数。

describe 关键字等价于 explain ，缩写为 desc 。

可以主动控制是否使用索引，便于测试索引的效果：

explain select * from tb1 use    index(index1) where name='one';  -- 只使用某些索引
explain select * from tb1 ignore index(index1) where name='one';  -- 忽略某些索引
explain select * from tb1 force  index(index1) where name='one';  -- 强制使用某些索引

# 事务

InnoDB 引擎支持事务。

可以将连续执行的多个 SQL 命令声明为一个事务：

BEGIN;           -- 开始一个事务，也可写作 START TRANSACTION
...
ROLLBACK;        -- 回滚到 BEGIN 时的状态
...
SAVEPOINT p1;    -- 声明一个回滚点
...
ROLLBACK TO p1;  -- 回滚到指定位置
...
COMMIT;          -- 提交事务（在此之前不会自动提交）

# 数据库锁

关于表级锁的命令：

LOCK TABLES [<tb> <lock_type>],...    -- 给指定表加锁，使当前客户端会话获得表锁，例如 LOCK TABLES tb1 WRITE, tb2 READ;
UNLOCK TABLES                         -- 释放当前会话的所有表锁

FLUSH TABLES                          -- 强制关闭所有被打开的表，并将写缓存中的数据写入磁盘
            tb,...                    -- 只处理指定的表
            WITH READ LOCK            -- flush 之后加上全局只读锁。这是全局锁，因此不需要获得 lock tables 的表锁

SHOW STATUS LIKE 'Table_locks%';      -- 统计获得表锁的耗时

客户端执行 LOCK TABLES 或 START TRANSACTION 时，都会隐式地释放已获得的表锁。
- 客户端断开会话时，服务器会释放其获得的表锁。

关于行级锁的命令：
```
SELECT * FROM ... WHERE ... LOCK IN SHARE MODE      -- 给查询到的所有数据行加行级共享锁
SELECT * FROM ... WHERE ... FOR UPDATE              -- 加行级排它锁

SHOW STATUS LIKE 'InnoDB_row_lock%';                -- 统计获得行锁的耗时
SELECT * FROM performance_schema.data_locks;        -- 显示所有获取行锁的请求，包括已经获得的、等待获得的
SELECT * FROM performance_schema.data_lock_waits;   -- 显示 data_lock_waits 中的哪些请求在被哪些请求阻塞
```
- InnoDB 的行锁是通过给索引中的索引键加锁来实现的。
  - 如果不使用索引进行查询，则行锁不起作用，只能使用表锁。
  - 如果针对不同的数据行加行锁，却使用相同的索引键，则也会发生锁冲突。
- InnoDB 在申请行锁时，会先隐式地申请该表的意向锁（intention lock），类型也为共享或排它。
  - 意向锁是一种特殊的表锁，表示意图对该表加行锁。
    - 意向锁不与意向锁冲突。
    - 意向共享锁与表级共享锁不冲突，其它意向锁与表级锁都冲突。
  - 意向锁由 InnoDB 自动获得、释放，客户端不能控制。
    - 使用意向锁，InnoDB 能更快地发现表级锁是否冲突。
  - 例：
    1. 事务 A 执行 SELECT * FROM tb1 WHERE id=1 FOR UPDATE; ，先请求获得对 tb1 表的意向排它锁，成功之后再请求获得对 id=1 的数据行的排它锁。
    2. 事务 B 执行 SELECT * FROM tb1 WHERE id=2 FOR UPDATE; ，与事务 A 不冲突，能够获得意向排它锁、行级排它锁。
    3. 事务 C 执行 LOCK TABLES tb1 READ; ，请求获得表级只读锁，但 tb1 表已有意向排它锁，因此阻塞等待。
- InnoDB 提供的行锁属于悲观锁，用户可以自己编程实现乐观锁。如下：
```
select name from tb1 where id = 1;                    -- 先查询下修改之前的值，这里假设此时 name 的值为 'one'
update tb1 set name='two' where id=1 and name='one';  -- 执行之后，根据返回值判断是否修改成功
```
  - 可以根据 timestap 等字段来判断数据是否被修改。

# 表碎片

InnoDB 表以 B+ 树结构存储数据。删除一行数据时，并不会释放存储空间，而是标记为 free ，等待写入新数据。这些 free 空间称为碎片（fragment）。
- 执行 delete、update 操作可能产生碎片。
- 如果碎片空间比新数据行体积小，则一直不能写入新数据。
- 一般按 data_free / (data_length + index_length) 计算碎片率。
- 如果碎片率长时间较高，建议手动清理碎片，从而减少存储空间、减少读取表的耗时。