MySQL 基础总结

SQL 基础

我们通常将 SQL 分为四类：

• DDL (数据定义语言)

  create drop alter

• DML(数据操作语言)

  insert delete update

• DQL(数据查询语言)

  select

• DCL(数据控制语言)

  grant revoke

事务

参考：https://tianzhichao.com/posts/26a608fc/

理论上来说，事务有着极其严格的定义，它必须同时满足四个特性：

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

事务的实现是通过 redo log && undo log，以及锁实现的

redo log 实现持久化和原子性，undo log 实现一致性状态变化，锁实现事务的隔离性。

redo log 是恢复提交事务修改的页操作，undo log 是回滚行记录到特定的版本。二者记录的内容也不同，redo log 是物理日志，记录页的物理修改操作，undo log
是逻辑日志，根据每行记录进行记录。

索引

索引是关系型数据库中用来提升查询性能最为重要的手段。

对于 MySQL 8.0 版本的 InnoDB 存储引擎来说，它支持三种类型的索引：

• B+ 树索引（默认索引）
• 全文索引（FULLTEXT）
• R 树索引

B+ 树目前在基于磁盘进行数据存储和排序上最有效率的数据结构。B+树是一颗平衡树，树的高度通常为 3 或 4，但是却可以保存从从百万级到十亿级的数据，而从这些数据里面查询一条数据，只需要 3 次或 4 次 I/O 操作。

B 树和 B+ 树的区别：https://www.jianshu.com/p/ace3cd6526c4

从应用层次上划分

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个列

从表记录的存储形式上来划分

• 聚簇索引：表记录的排列顺序和索引的排列顺序一致
• 非聚簇索引：表记录的排列顺序和索引的排列顺序不一致

聚簇索引和非聚簇索引

在 InnoDB 中，表数据文件本身就是按照 B+Tree 组织的一个索引结构。
聚簇索引就是按照每张表的主键构造一颗 B+ 树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。
一般建表会用一个自增主键做聚簇索引，没有的话 MySQL 会默认创建
在日常的开发任务中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需要找主键索引的二级索引，先找到主键索引再通过主键索引找到数据。

索引的执行计划

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
   • UNOIN 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 temparary MySQL 需要使用临时表来存储结果集，常用语分组和排序
   • Impossible where where 子句会导致没有符合条件的行
   • Distinct 发现第一个匹配行后，停止为当前的行组合搜索更多的行
   • Using where 查询的列未被索引覆盖，筛选条件并不是索引的前导列

索引设计的原则

1. 最适合索引的列是出现在 WHERE 子句和连接子句中的列
2. 索引列的基数越大（取值多、重复值少）索引的效果就越好
3. 使用前缀索引可以减少索引占用的空间，内存中可以缓存更多的索引
4. 索引不是越多越好，虽然索引加速的读操作，但是写操作（增、删、改）都会变慢，因为数据的变化会导致索引的更新，就如同书籍章节的增删需要更新目录一样
5. 使用 InnoDB 存储引擎时，表的普通索引都会保存主键的值，所以主键尽可能选择较短的数据类型，这样可以有效的减少索引占用的空间，提升索引的缓存效果。
6. InnoDB 使用的 B+ tree 索引，数值类型的列除了等值判断时索引会生效，使用 > |<| >=|<=|BETWEEN … AND … 、<> 时，索引仍然生效；对于字符串类型的列，如果使用不以通配符开头的模糊查询，索引也是起作用的，但是其他情况就会导致索引失效，这就意味着很有可能会做全表扫描。

索引失效场景

1. 联合索引不满足最左匹配原则

在联合索引的场景下，查询条件不满足最左匹配原则。

顾名思义，在联合索引中，最左则的字段优先匹配。因此，在创建联合索引时，where 子句中使用最频繁的字段放在组合索引的最左则。

例如：unoin_idx 联合索引组成：

KEY `union_idx` (`id_no`, `username`, `age`)

走索引的情况：

select * from t_user where id_no = "1002";
select * from t_user where id_no and username = "1002";
select * from t_user where id_no = "1002" and username = "111" and age = "1002";

以下不会走索引：

select * from t_user where username = "1002";

select * from t_user where age = "1002";

select * from t_user where username and age = "1002";

2. 使用 select *

在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引;

在 《阿里巴巴开发手册》的 ORM 映射章节中有一条「强制」的规范：

• 一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络消耗，尤其是 text 类型的字段。

3. 索引列参与计算

索引列参与了运算，会导致全表扫描，索引失效。

explain select * from t_user where id+1=2

索引列参与了计算，会导致全表扫描，索引失效

4. 索引列参使用了函数示例

索引列参与了函数处理，会导致全表扫描，索引失效。

exlain select * from t_user where SUBSTR(id_no, 1, 3) = "100"

此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引失效。同时，还伴随着性能问题

5. 错误的 Like 使用

模糊查询时(like语句)，模糊匹配的占位符位于条件的首部。

explain select * from t_user where id_no like '%00%'

针对 like 的使用非常频繁，但使用不当往往会导致不走索引。常见的 like 使用方式有：

• 方式一：like ‘%abc’;
• 方式二：like ‘abc%’;
• 方式三：like ‘%abc%’

其中方式一和方式三，由于占位符出现在首部，导致无法走索引。

索引本来就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

6. 类型隐式转换

参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。

explain select * from t_user where id_no = 1002;

id_no 字段类型为 varchar，但在 SQL 语句中使用了 int 类型，导致全表扫描。

7. 使用 OR 操作

explain select * from t_user where id = 2 or username = "Tom2"

上述的查询中，id 字段是有索引的，但是使用 or 关键字，索引竟然失效了。

当单独使用 username 进行查询时，会全表扫描，索引也就不会再走索引了。

8. 两列做比较

两列数据做比较，即便两列都创建了索引，索引也会失效

如果两个列数据都有索引，但在查询条件中两列数据进行了对比操作，则会导致索引失效。

explain select * from t_user where id > age;

9. 不等于比较

查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

当查询条件为字符串时，使用 “<>” 或 “!=” 作为条件查询，有可能不走索引，但也不全是。

explain select * from t_user where id_no != "1002" 

10. is not null

查询条件使用 is null 时正常走索引，使用 is not null 时，不走索引

explain select * from t_user where id_no is not null;

11. not in 和 not exists

• 查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
• 查询条件使用not exists时，索引失效。

在日常中使用比较多的范围查询有 in、exists、not in、not exists、between and

explain select * from t_user where id in (2,3);

explain select * from t_user where id_no in ('1001','1002');

explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain select * from t_user where id_no between '1002' and '1003';

上述四种语句都会正常走索引。

explain select * from t_use where id_no not in ("1002", "1003")

当使用 not in 时，不走索引？当条件列换成主键试试：

explain select * from t_user where id not in (2,3)

如果是主键，则正常走索引。

12. order by 导致索引失效

当查询条件涉及到 order by、limit 等条件时，是否走索引的情况比较复杂，而且与 MySQL 版本有关，通常普通索引，如果未使用 limit，则不会走索引。order by 多个字段时，可能不会走索引。其他情况使用 explain 验证。

explain select id from t_user order by age;
explain select id , username from t_user order by age;
explain select id_no from t_user order by id_no;

当索引覆盖的场景也正常走索引的。

13. 参数不同导致索引失效

当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。

explain select * from t_user where create_time > '2023-02-24 09:04:23';

explain select * from t_user where create_time > '2022-02-27 09:04:23';

上述两个查询，前者走索引，后者进行了全表扫描。

为什么同样的查询语句，知识因为查询的参数值不同，却会出现一个走索引，一个不走索引的情况呢？

答案很简单：上述索引失效是因为 DBMS 发现全表扫描比走索引效率更高，因此就放弃了走索引。

14. 其他

Mysql优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。

当然，还有其他一些是否走索引的情况，这与索引的类型时 B-tree 索引还是位图索引也有关系，就不再详细展开。

事务的隔离级别

事务的隔离级别是通过锁来实现

四种隔离级别分别是, 从低到高：

• READ-UNCOMMITTED（读取未提交）
• READ-COMMITTED（读取已提交）
• REPEATABLE-READ（可重复读）
• SERIALIZABLE（可串行化）

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

Mysql 默认采用的 REPEATABLE_READ 隔离级别

什么是脏读？幻读？不可重复读？

• 脏读（Dirty Read）:
  读取到了错误的数据，A 事务执行 update 操作，B 事务执行查询获取结果，此时 A 事务 RollBack 导致读取数据不正确。
• 不可重复读（Non-repeatable read）:
  在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读（Phantom Read）:
  一个事务的两次查询中夹杂着插入事务的操作，数据量不一致

参考：

• https://dev.mysql.com/doc/

• https://github.com/jackfrued/Python-100-Days/blob/master/Day36-40/37.SQL%E8%AF%A6%E8%A7%A3.md#ddl%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80

• https://www.51cto.com/article/702691.html