MySQL存储引擎、索引、索引失效
MySQLDocker 安装 MySQL8.0，安装见docker-compose.yaml
操作类型
SQL 步伐语言有四种类型，对数据库的基本操作都属于这四种类，分为 DDL、DML、DQL、DCL
[*] DDL(Dara Definition Language 数据定义语言)，是负责数据结构定义与数据对象定义的语言，由 create、alter、drop、truncate 四个语法构成
[*]create table 创建表
[*]alter table 修改表
[*]drop table 删除表
[*]truncate table 清空表
[*] DML(Data Manipulation Language 数据操纵语言)，主要是进行插入数据、修改数据、删除数据的操作，由 insert、update、delete 语法构成
[*] DQL(Data Query Language 数据查询语言)，用来进行数据库中的数据查询，最常用的就是 select 语句
[*] DCL(Data Control Language 数据控制语言)，用来授权或回收访问数据库的某种特权，并控制数据库操纵变乱发生的时间及结果，可以或许对数据库进行监督
存储过程
MySQL数据存储位置可以通过SHOW VARIABLES LIKE 'datadir';去获取，每一个数据库都会有一个文件，每一张表都会有一个*.ibd文件，这个文件存储着表数据、索引、UNDO日志等等…
表空间文件结构
表空间有文件头(File Header)、段(Segment)、区(Extent)、页(Page)
[*]段(Segment)，段是表空间的逻辑分区，用于管理不同类型的数据，如表的数据段、索引段、溢出段等
[*]区(Extent)，每个区由多个连续的页构成，默认巨细为 1MB（即 64 个连续的 16KB 页）
[*]页(Page)，页是 InnoDB 表空间文件的基本存储单元，每页存储不同的数据内容，如行数据、索引、回滚信息等
[*]行(Row)，行是表中数据的基本逻辑单元，代表每一条记录；记录以特定格式存储在数据页中，并包含现实的列值、元信息（如变乱 ID、回滚指针等）
https://i-blog.csdnimg.cn/direct/b8813da563c94bce91700fc6c7c4b9e0.png
数据页结构
数据库I/O操作的最小单元是页，与数据库相关的内容都会存储在页结构里。数据页包括七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）
https://i-blog.csdnimg.cn/direct/3f763024b50842ada36191e1f6e9f5cf.png
https://i-blog.csdnimg.cn/direct/b23f7337740141c5b56f8a6d881fc7bb.png
行(Row)格式分类
MySQL 支持以下几种行格式，具体格式由表的 ROW_FORMAT 定义：Compact（紧凑格式）、Redundant（冗余格式，MySQL 早期版本的默认格式）、Dynamic（动态格式）、Compressed（压缩格式）
CREATE TABLE example (
id INT,
name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;
以Compact为例
字段内容说明行头信息INFO_BITS, HEAP_NO 等用于管理行的元信息隐蔽列TRX_ID, ROLL_PTR 等用于支持变乱和回滚用户数据id=1定长数据直接存储用户数据name=‘Alice’包括长度前缀和现实数据用户数据age=30定长数据直接存储用户数据bio 指向溢出页假如数据过大，存储在溢出页 存储引擎
可以通过SELECT * FROM INFORMATION_SCHEMA.ENGINES;查询数据库支持存储引擎，常见的存储引擎有InnoDB、MyISAM
https://i-blog.csdnimg.cn/direct/99674540dd3c45c8818f652aeda51069.png
InnoDB 存储引擎
InnoDB是如今默认的存储引擎，具体参考官方文档
[*] 事物支持
[*]支持事物，遵循ACID特性
[*] 行级锁
[*]接纳行级锁，支持高并发
[*]联合多版本并发控制(MVCC)，减少锁争用
[*] 外键约束
[*]支持外键约束，确保数据一致性和完整性
[*] 崩溃规复
[*]利用 Redo Log 和 Undo Log 来确保数据在系统崩溃后可以规复
[*] 索引
[*]聚簇索引（Clustered Index）存储数据，主键索引和行数据一起存储
[*]辅助索引，辅助索引存储索引键和指向主键的引用，回表
MyISAM 存储引擎
MyISAM存储引擎是基于较旧的ISAM存储引擎的扩展，具体参考官方文档
[*]无事物支持
[*]表级锁
[*]高效读操作
[*]非聚簇索引，数据和索引分开存储
[*]压缩表
[*]不支持外键
选择存储引擎
[*] 假如系统需要 变乱支持、高并发写入、数据一致性（如银行、订单系统）
选择 InnoDB
[*] 假如系统以 读操作为主、不需要变乱支持（如报表系统、数据统计）
选择 MyISAM
索引
索引是一种用于快速查询和检索数据的数据结构，其本质可以当作是一种排序好的数据结构，索引的作用就相当于书的目录
索引分类
[*] 按照存储方式分别
[*]聚簇索引：索引结构和数据存一起存放的索引（InnoDB中的主键索引）
[*]非聚簇索引：索引结构和数据分开存放的索引，如二级索引，MyISAM引擎下的索引
[*] 按照应用维度分别
[*]主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个
[*]平凡索引：仅加速查询
[*]唯一索引：加速查询 + 列值唯一（可以有 NULL）
[*]覆盖索引：一个索引包含（大概说覆盖）全部需要查询的字段的值
[*]联合索引：多列值构成一个索引，专门用于组合搜索，其服从大于索引合并
[*]全文索引：对文本的内容进行分词，进行搜索
[*] 按照数据结构分别
[*]BTree 索引：最常用的索引类型，叶子节点存储value
[*]哈希索引：雷同键值对的情势，一次即可定位
[*]全文索引：对文本的内容进行分词，进行搜索
BTree
B-Tree（Balanced Tree，平衡树）是一种自我平衡的树数据结构，保持数据有序，时间复杂度为 O ( log n ) O(\log n) O(logn)
比力项BTreeB+Tree数据存储位置数据存储在叶子节点和非叶子节点数据仅存储在叶子节点索引节点存储内容键和值仅存储键范围查询服从较低，需遍历多个节点高效，叶子节点形成链表次序遍历需要中序遍历整棵树通过叶子节点链表直接遍历树高度较高（非叶子节点存储更多数据）较低（非叶子节点存储更少数据）适用场景一样平常的搜索和存储场景数据库索引、文件系统的最佳选择 数据库利用B+Tree的上风
[*]更高效的磁盘 IO：非叶子节点占用更少的存储空间，能减少磁盘读取次数，提高性能。
[*]更快的范围查询：叶子节点形成链表，得当处理惩罚范围查询和排序查询。
[*]易于维护：插入和删除操作的复杂度较低，树的平衡性易维护。
[*]良好的扩展性：能适应大规模数据和高并发场景。
索引失效
[*] 字段类型隐式转换
-- 索引失效
SELECT * FROM user WHERE id = '1';
-- 索引生效
SELECT * FROM user WHERE id = 1;
[*] 索引列参与运算
-- 索引失效
SELECT * FROM user WHERE id + 1 = 10;
-- 索引生效
SELECT * FROM user WHERE id = 10;
[*] 索引列利用函数
-- 索引失效
SELECT * FROM users WHERE SUBSTR(name, 1, 3) = 'Tom'
-- 索引生效
SELECT * FROM users WHERE name = 'Tom'
[*] 索引列利用like，且%在前面
-- 索引失效
SELECT * FROM users WHERE name LIKE '%Tom%'
-- 索引生效
SELECT * FROM users WHERE name = 'Tom'
[*] 数据量比力少，优化器不走索引
[*] 索引列利用!=、<>、!<、!>、NOT IN、NOT LIKE、NOT BETWEEN、IS NOT NULL
-- 索引失效
SELECT * FROM users WHERE id != 1;
-- 索引生效
SELECT * FROM users WHERE id = 1;
[*] 轻易误解的地方是 <、> 走不走索引，在MySQL8.0中，<、> 针对数字类型走索引会走索引
[*]当匹配的数据量超过表总数据量的20%-30%时，优化器倾向于选择全表扫描，索引也会失效
[*] 索引列利用OR
[*] 索引列利用复合索引， 违反最左前缀原则
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]