mysql性能优化explain了解

总结索引的设计原则：

1. **最适合**索引的列是出现在**WHERE子句**和连接子句中的列。
2. 索引列的基数越大（取值多、重复值少），索引的效果就越好。
3. 使用**前缀索引**可以减少索引占用的空间，内存中可以缓存更多的索引。
4. **索引不是越多越好**，虽然索引加速了读操作（查询），但是写操作（增、删、改）都会变得更慢，因为数据的变化会导致索引的更新，就如同书籍章节的增删需要更新目录一样。
5. 使用 InnoDB 存储引擎时，表的普通索引都会保存主键的值，所以**主键要尽可能选择较短的数据类型**，这样可以有效的减少索引占用的空间，提升索引的缓存效果。

#### 范式理论

范式理论是设计关系型数据库中二维表的指导思想。

1. 第一范式：数据表的每个列的值域都是由原子值组成的，不能够再分割。
2. 第二范式：数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系。
3. 第三范式：所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。

explain select * from tb_student where stuname='林震南'\G
```

```
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
```

在上面的 SQL 执行计划中，有几项值得我们关注：

1. `select_type`：查询的类型。
    - `SIMPLE`：简单 SELECT，不需要使用 UNION 操作或子查询。
    - `PRIMARY`：如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
    - `UNION`：UNION 操作中第二个或后面的 SELECT 语句。
    - `SUBQUERY`：子查询中的第一个 SELECT。
    - `DERIVED`：派生表的 SELECT 子查询。
2. `table`：查询对应的表。
3. `type`：MySQL 在表中找到满足条件的行的方式，也称为访问类型，包括：`ALL`（全表扫描）、`index`（索引全扫描，只遍历索引树）、
`range`（索引范围扫描）、`ref`（非唯一索引扫描）、`eq_ref`（唯一索引扫描）、`const` / `system`（常量级查询）、`NULL`（不需要访问表或索引）。
在所有的访问类型中，很显然 ALL 是性能最差的，它代表的全表扫描是指要扫描表中的每一行才能找到匹配的行。
4. `possible_keys`：MySQL 可以选择的索引，但是**有可能不会使用**。
5. `key`：MySQL 真正使用的索引，如果为`NULL`就表示没有使用索引。
6. `key_len`：使用的索引的长度，在不影响查询的情况下肯定是长度越短越好。
7. `rows`：执行查询需要扫描的行数，这是一个**预估值**。
8. `extra`：关于查询额外的信息。
    - `Using filesort`：MySQL 无法利用索引完成排序操作。
    - `Using index`：只使用索引的信息而不需要进一步查表来获取更多的信息。
    - `Using temporary`：MySQL 需要使用临时表来存储结果集，常用于分组和排序。
    - `Impossible where`：`where`子句会导致没有符合条件的行。
    - `Distinct`：MySQL 发现第一个匹配行后，停止为当前的行组合搜索更多的行。
    - `Using where`：查询的列未被索引覆盖，筛选条件并不是索引的前导列。

从上面的执行计划可以看出，当我们通过学生名字查询学生时实际上是进行了全表扫描，不言而喻这个查询性能肯定是非常糟糕的，
尤其是在表中的行很多的时候。如果我们需要经常通过学生姓名来查询学生，那么就应该在学生姓名对应的列上创建索引，通过索引来加速查询。

创建索引规则
create index idx_student_name_1 on tb_student(stuname(1));

explain select * from tb_student where stuname='林震南'\G
```

```
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 5
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

#### 数据完整性

1. 实体完整性 - 每个实体都是独一无二的

   - 主键（`primary key`） / 唯一约束（`unique`）
2. 引用完整性（参照完整性）- 关系中不允许引用不存在的实体

   - 外键（`foreign key`）
3. 域（domain）完整性 - 数据是有效的
   - 数据类型及长度

   - 非空约束（`not null`）

   - 默认值约束（`default`）

   - 检查约束（`check`）

     > **说明**：在 MySQL 8.x 以前，检查约束并不起作用。

#### 数据一致性

1. 事务：一系列对数据库进行读/写的操作，这些操作要么全都成功，要么全都失败。

2. 事务的 ACID 特性
   - 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行
   - 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态
   - 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行
   - 持久性：已被提交的事务对数据库的修改应该永久保存在数据库中

3. MySQL 中的事务操作
   - 开启事务环境
     ```SQL
     start transaction
   - 提交事务

     ```SQL
     commit
   - 回滚事务
     ```SQL
     rollback
     ```