MYSQL结构与索引

存储引擎

InnoDB和MyISAM

索引

索引是什么？就是数据库的目录

为什么索引能提高速度？

速度很快，其次是数据唯一性有保障（可以创建唯一索引），加速排序和分组（因为索引本身是排好序的）

聚集索引和非聚集索引

聚集索引：数据的物理顺序和列值的逻辑顺序相同（主键）

查询的时候，因为聚集索引 B+ 树的叶子节点存储了完整数据，直接查就行

非聚集索引：就是顺序没关系（除了聚集就全是非聚集）

查询的时候，因为非聚集索引 B+ 树的叶子节点存储的是 索引值 和 对应主键值

1、需要先在这个树找到该行的主键值

2、使用主键值，去聚集索引 B+ 树二次查找

这个过程就叫做「回表」

索引的数据结构

是树，因为树天生有二分查找的特性，查询速度高

索引失效

索引失效就是违反了数据库使用索引的原则，转变成了全表扫描

查询条件类

1、Like通配符前置

LIKE '%关键字

这个没什么说的，就是索引是左侧顺序匹配的，相当于词典，只给右边的字母显然不能找到索引。

2、最左前缀原则

在使用复合索引的时候，只有带着最左边的索引成分的时候，索引才能正常使用。

索引失效的时候，查询的性能就变成了全表扫描，效率慢。

CREATE INDEX idx_col ON table (a, b, c);a是最左边

WHERE a = 1 AND b = 2可以，因为有a，索引成立

WHERE b = 2 AND c = 3不可以，因为没有a，索引失效

3、否定条件

比如 ！= not in，范围太大，使用索引成本高于直接进行全表扫描。

操作索引类

1、对索引进行运算

where DATE(time) = '2025-1-1'

因为这相当于对索引列的所有数据都计算一遍，等同于全表扫描。

2、索引隐形转换

比如id是int，结果和string类型比较，内部转换，同1。

MYSQL索引为什么使用 B+树？

平衡二叉树AVL：AVL实现平衡主要是靠旋转，旋转是非常耗时的，所以不能用（增加和删除）

红黑树：和AVL相比，红黑树不追求完美的平衡，只是大致的平衡。对比AVL，查询略降，删除大提高

因为红黑树同时引入了颜色，当插入或删除数据时，只需要进行 O(1)次数的旋转以及变色就能保证基本的平衡，不需要像 AVL 树进行 O(lgn)次数的旋转。总的来说， 红黑树的统计性能高于 AVL。

红黑树应用：HashMap链表+红黑树解决哈希冲突 ，TreeMap 红黑树存储排序键值对

在“高且瘦”的树中，你每次比较都可能需要访问一个新的节点。如果每个节点都位于磁盘的不同页（Page）上，那么查找一个数据就可能需要很多次磁盘I/O。比如树高20，就需要最多20次I/O，这无法接受。

B树：矮胖树，一个节点可以有多个子节点，每个节点都会存储数据本身，节点内的键值是排好序的，节点之间也满足排序关系。

B+树直接看和B树的区别

数据存储：B+树的Data只存在于叶子节点中，非叶子节点只有索引。（B树什么节点都存数据）

叶子结点：B+树中只有叶子节点有指针，叶子节点之间通过指针链接起来形成一个有序链表（B树叶子节点是相互独立的）

查找：B+树什么查找都得走到叶子节点，查找路径是稳定的（B树可能在非叶子节点就命中）

Data，可能是行的全部数据（如 Innodb 的聚簇索引），也可能只是行的主键（如 Innodb 的辅助索引），或者是行所在的地址（如 MyIsam 的非聚簇索引）。

(重要)由于B+树非叶子节点不存Data，所以每个节点能存的索引更多，树更矮。

因此，B+树优势：

1、因为树的结构更矮胖，磁盘IO次数少（读写）

2、因为叶子节点是通过双向链表连接的，查询到一条数据就可以沿着链表的上下直接顺序查询，IO路径规律，范围查询效率更高

SELECT * FROM users WHERE age BETWEEN 20 AND 30

IO路径规律——>磁头不需要大动

3、因为Data都存在于叶子节点里面，最差的情况就是一般情况，查询性能稳定。

4、因为双向链表和Data存于叶子，全表遍历效率高，只需要遍历链表就行

SQL优化

慢SQL优化

1、定位慢查询

在配置文件启用，long_query_time = 1：设置阈值；low_query_log = 1：开启慢查询日志。

然后执行 SHOW FULL PROCESSLIST；看status和time

2、分析慢查询情况

定位之后，对这个语句执行加上EXPLAIN；

• 看type：类型，all是全表，其他次之
• 看rows：行数

3、解决