MySQL存储引擎、索引、索引失效

MySQL

Docker 安装 MySQL8.0，安装见docker-compose.yaml

操作类型

SQL 程序语言有四种类型，对数据库的基本操作都属于这四种类，分为 DDL、DML、DQL、DCL

1. DDL(Dara Definition Language 数据定义语言)，是负责数据结构定义与数据对象定义的语言，由 create、alter、drop、truncate 四个语法组成

   • create table 创建表
   • alter table 修改表
   • drop table 删除表
   • truncate table 清空表

2. DML(Data Manipulation Language 数据操纵语言)，主要是进行插入数据、修改数据、删除数据的操作，由 insert、update、delete 语法组成

3. DQL(Data Query Language 数据查询语言)，用来进行数据库中的数据查询，最常用的就是 select 语句

4. DCL(Data Control Language 数据控制语言)，用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视

存储过程

MySQL数据存储位置可以通过SHOW VARIABLES LIKE 'datadir';去获取，每一个数据库都会有一个文件，每一张表都会有一个*.ibd文件，这个文件存储着表数据、索引、UNDO日志等等…

表空间文件结构

表空间有文件头(File Header)、段(Segment)、区(Extent)、页(Page)

1. 段(Segment)，段是表空间的逻辑分区，用于管理不同类型的数据，如表的数据段、索引段、溢出段等
2. 区(Extent)，每个区由多个连续的页组成，默认大小为 1MB（即 64 个连续的 16KB 页）
3. 页(Page)，页是 InnoDB 表空间文件的基本存储单元，每页存储不同的数据内容，如行数据、索引、回滚信息等
4. 行(Row)，行是表中数据的基本逻辑单位，代表每一条记录；记录以特定格式存储在数据页中，并包含实际的列值、元信息（如事务 ID、回滚指针等）

数据页结构

数据库I/O操作的最小单位是页，与数据库相关的内容都会存储在页结构里。数据页包括七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）

行(Row)格式分类

MySQL 支持以下几种行格式，具体格式由表的 ROW_FORMAT 定义：Compact（紧凑格式）、Redundant（冗余格式，MySQL 早期版本的默认格式）、Dynamic（动态格式）、Compressed（压缩格式）

CREATE TABLE example (
    id INT,
    name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

以Compact为例

存储引擎

可以通过SELECT * FROM INFORMATION_SCHEMA.ENGINES;查询数据库支持存储引擎，常见的存储引擎有InnoDB、MyISAM

InnoDB 存储引擎

InnoDB是现在默认的存储引擎，具体参考官方文档

1. 事物支持

   • 支持事物，遵循ACID特性

2. 行级锁

   • 采用行级锁，支持高并发
   • 结合多版本并发控制(MVCC)，减少锁争用

3. 外键约束

   • 支持外键约束，确保数据一致性和完整性

4. 崩溃恢复

   • 使用 Redo Log 和 Undo Log 来确保数据在系统崩溃后可以恢复

5. 索引

   • 聚簇索引（Clustered Index）存储数据，主键索引和行数据一起存储
   • 辅助索引，辅助索引存储索引键和指向主键的引用，回表

MyISAM 存储引擎

MyISAM存储引擎是基于较旧的ISAM存储引擎的扩展，具体参考官方文档

1. 无事物支持
2. 表级锁
3. 高效读操作
4. 非聚簇索引，数据和索引分开存储
5. 压缩表
6. 不支持外键

选择存储引擎

1. 如果系统需要 事务支持、高并发写入、数据一致性（如银行、订单系统）
   选择 InnoDB

2. 如果系统以 读操作为主、不需要事务支持（如报表系统、数据统计）
   选择 MyISAM

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构，索引的作用就相当于书的目录

索引分类

1. 按照存储方式划分

   • 聚簇索引：索引结构和数据存一起存放的索引（InnoDB中的主键索引）
   • 非聚簇索引：索引结构和数据分开存放的索引，如二级索引，MyISAM引擎下的索引

2. 按照应用维度划分

   • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个
   • 普通索引：仅加速查询
   • 唯一索引：加速查询 + 列值唯一（可以有 NULL）
   • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值
   • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
   • 全文索引：对文本的内容进行分词，进行搜索

3. 按照数据结构划分

   • BTree 索引：最常用的索引类型，叶子节点存储value
   • 哈希索引：类似键值对的形式，一次即可定位
   • 全文索引：对文本的内容进行分词，进行搜索

BTree

B-Tree（Balanced Tree，平衡树）是一种自我平衡的树数据结构，保持数据有序，时间复杂度为 $O(\log n)$

数据库使用B+Tree的优势

• 更高效的磁盘 IO：非叶子节点占用更少的存储空间，能减少磁盘读取次数，提高性能。
• 更快的范围查询：叶子节点形成链表，适合处理范围查询和排序查询。
• 易于维护：插入和删除操作的复杂度较低，树的平衡性易维护。
• 良好的扩展性：能适应大规模数据和高并发场景。

索引失效

1. 字段类型隐式转换

   -- 索引失效
   SELECT * FROM user WHERE id = '1';
   -- 索引生效
   SELECT * FROM user WHERE id = 1;
   

2. 索引列参与运算

   -- 索引失效
   SELECT * FROM user WHERE id + 1 = 10;
   -- 索引生效
   SELECT * FROM user WHERE id = 10;
   

3. 索引列使用函数

   -- 索引失效
   SELECT * FROM users WHERE SUBSTR(name, 1, 3) = 'Tom'
   -- 索引生效
   SELECT * FROM users WHERE name = 'Tom'
   

4. 索引列使用like，且%在前面

   -- 索引失效
   SELECT * FROM users WHERE name LIKE '%Tom%'
   -- 索引生效
   SELECT * FROM users WHERE name = 'Tom'
   

5. 数据量比较少，优化器不走索引

6. 索引列使用!=、<>、!<、!>、NOT IN、NOT LIKE、NOT BETWEEN、IS NOT NULL

   -- 索引失效
   SELECT * FROM users WHERE id != 1;
   -- 索引生效
   SELECT * FROM users WHERE id = 1;
   

7. 容易误解的地方是 <、> 走不走索引，在MySQL8.0中，<、> 针对数字类型走索引会走索引

   • 当匹配的数据量超过表总数据量的20%-30%时，优化器倾向于选择全表扫描，索引也会失效

8. 索引列使用OR

9. 索引列使用复合索引， 违反最左前缀原则