单表优化

避免全表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 需求一：查询所有名字中包含李的用户姓名和手机号,并根据user_id字段排序。
EXPLAIN SELECT
    NAME,
    mobile
FROM user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;

--  添加联合索引，包含要查询的字段，实现索引覆盖，一并解决 like %在左边的问题。
ALTER TABLE user_contacts ADD INDEX idx_nm(NAME,mobile,user_id);

-- 删除索引
DROP INDEX idx_nm ON user_contacts;

-- 优化后，extra字段中包含filesort文件排序，还需要继续优化
-- 这里用user_id 字段排序，所以将user_id 放在联合索引的最前面
ALTER TABLE user_contacts ADD INDEX idx_nm(user_id,NAME,mobile);

联合索引不生效

1
2
3
4
5
6
7
8
9

-- 需求二：统计手机号是135、136、186、187开头的用户数量。
EXPLAIN SELECT
    COUNT(*)
FROM user_contacts
WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

-- extra = using where + using index,表示查询的列被索引覆盖了，但是无法通过索引直接获取数据。
-- 联合索引无法生效，根据mobile单独建立一个索引
ALTER TABLE user_contacts ADD INDEX idx_m(mobile);

查询大范围数据如何减少耗时

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 需求三：获取用户通讯录表第10万条数据开始后的100条数据。
EXPLAIN SELECT * FROM user_contacts LIMIT 4000000,10000;

-- 1. 通过索引进行分页，比如id字段如果是自增的话，就可以根据查询的记录数算出id的范围
EXPLAIN SELECT * FROM user_contacts WHERE id >= 100001 LIMIT 100;

-- 2.使用了子查询
-- 首先 定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;

-- 根据获取到的id 向后查询
EXPLAIN SELECT * FROM user_contacts
WHERE id >= (SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

多表优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 用户认证表：mob_auth,有11万数据，保存的是通过手机认证后的用户数据。
-- 与其他表关联的字段 user_id
SELECT COUNT(*) FROM mob_auth;

-- 紧急联系人表：ugncy_cntct_psn ,大概有22万条数据，用户注册成功后，保存填写的紧急联系人信息
-- 关联字段：user_id
SELECT COUNT(*) FROM ugncy_cntct_psn;

-- 借款申请表：loan_apply, 接近11万条数据，保存的是每次用户申请借款时填写的信息
-- 关联字段：user_id
SELECT COUNT(*) FROM loan_apply;

-- 需求1：查询所有认证用户的手机号以及认证用户的紧急联系人的姓名与手机号信息。
EXPLAIN SELECT
    ma.`mobile` '认证用户的手机号',
    ucp.`cntct_psn_name` '紧急联系人的姓名',
    ucp.`cntct_psn_mob` '紧急联系人的手机号'
FROM mob_auth ma
LEFT JOIN ugncy_cntct_psn ucp ON ma.`user_id` = ucp.`user_id`;

-- 优化：为 mob_auth的user_id字段添加索引
ALTER TABLE mob_auth ADD INDEX idx_uid(user_id);
-- 上面的索引没有生效，因为mob表是驱动表，驱动表建立索引也不会生效。
-- 一般情况下：左连接，左表是驱动表，右表被驱动表，右连接反之，我们应该在被驱动表连接字段上建立索引
ALTER TABLE ugncy_cntct_psn ADD INDEX idx_uid(user_id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 需求二：获取紧急联系人数量>8的，用户手机号信息。
-- 1.获取紧急联系人 > 8的用户的id
SELECT user_id,COUNT(user_id) c FROM ugncy_cntct_psn GROUP BY user_id
HAVING c > 8;

-- 2.获取认证用户的id和手机号
SELECT user_id,mobile FROM mob_auth;

-- 3.将上面两条SQL进行连接
EXPLAIN SELECT
    ucp.user_id,
    COUNT(ucp.user_id) c,
    m.user_id,
    m.mobile
FROM ugncy_cntct_psn ucp INNER JOIN (SELECT user_id,mobile FROM mob_auth) m
ON ucp.`user_id` = m.user_id
GROUP BY ucp.user_id HAVING c > 8 ORDER BY NULL;

– 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 需求3：获取所有智能审核的用户手机号和申请额度、申请时间、审核额度。

EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.audit_mod_cde = 2;

-- 选择性低（重复率高）的字段不适合创建索引
ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = 2;
-- 如果必须要通过选择性低的状态字段查询的话，可以根据业务需求，添加一个日期条件
EXPLAIN SELECT
    ma.`mobile` '用户认证手机号',
    la.`apply_limit` '申请额度',
    la.`apply_time` '申请时间',
    la.`audit_limit` '审核额度'
FROM mob_auth ma INNER JOIN loan_apply la ON ma.id = la.`mob_auth_id`
WHERE la.`apply_time` BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:59:59'
AND la.audit_mod_cde = 2;

什么是成本？

在MySQL中，一条查询语句的执行成本实际上是由两部分构成的：

• I/O 成本
  • MyISAM 和 InnoDB都需要将数据和索引存储到磁盘，当进行查询时，就需要把数据或者索引加载到内存中。从磁盘到内存这个加载过程，损耗的时间，我们称之为 I/O成本。
• CPU成本
  • 读取记录的时候，需要检测记录是否满足对应的搜索条件、对结果集进行排序等等操作发生的损耗都称之为 CPU成本。
• 成本常数
  • I/O成本常数：1.0 ，MySQL规定读取一个页面花费的成本默认是1.0
  • CPU成本常数：0.2，读取以及检测一条记录是否符合搜索条件的成本，默认是0.2

    单表查询成本

MySQL中找出成本最低方案的过程，大致如下：

1. 根据搜索条件，找出所有可能使用的索引。
2. 计算全表扫描的代价。
3. 计算使用不同索引，执行查询的代价。
4. 对比各种执行方案的代价，找出成本最低的那个。

根据搜索条件，找出所有可能使用的索引。

1
2
3
4
5
6
7
8

SELECT * FROM orders
    WHERE order_no
    IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
    AND expire_time> '2021-03-22 18:28:28'
    AND expire_time<= '2021-03-22 18:35:09'
    AND insert_time > expire_time
    AND order_note LIKE '%7****排1%'
    AND order_status = 0;

分析一下上面SQL中的涉及到的搜索条件：

1) IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)这个搜索条件，可以使用二级索引：idx_order_no。

2) AND expire_time > ‘2021-03-22 18:28:28’ AND expire_time <= ‘2021-03-22 18:35:09’这个搜索条件可以使用二级索引 idx_expire_time。

3) AND insert_time > expire_time： 这个搜索条件索引列没有和常数进行比较，所以就不能使用索引。

4) AND order_note LIKE ‘%7排1%’： order_note是有索引的，但是由于like查询 % 出现在了左边，不符合最左前缀原则，索引索引就失效。

5) AND order_status = 0; order_status 是处在联合索引的第二个位置上，所以索引是无法使用的。

1
2
3
4
5
6
7
8

EXPLAIN SELECT * FROM orders
    WHERE order_no
    IN ('DD00_6S', 'DD00_9S', 'DD00_10S')
    AND expire_time> '2021-03-22 18:28:28'
    AND expire_time<= '2021-03-22 18:35:09'
    AND insert_time > expire_time
    AND order_note LIKE '%7****排1%'
    AND order_status = 0;

计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描指的就是把聚簇索引中的记录和给定的搜索条件依次进行比较，把符合条件的记录加入到结果集中。要把聚簇索引对应的页面加载到内存中，然后在检测记录是否符合条件。

查询成本 = I/O成本+CPU 的成本,所以就计算全表扫描的代价需要两个信息：

1) 聚簇索引占用的页面数
2) 该表的记录数

1

show table status like 'orders'\G

1) rows :表示的是表中的记录数，值为10591。而实际的记录数为10567。在InnoDB中 rows是一个估值。但是计算成本的时候，按照 show table status like ‘orders’\G显示的结果来进行计算。

2) Data_length :表示当前表 占用的存储空间的字节数。对于InnoDB来说，聚簇索引占用的存储空间的大小就是该值的大小。

• Data_length = 聚簇索引的页面的数量 x 每个页面的大小。
• 聚簇索引页面数量 = 1589248 / 16 / 1024 = 97

得到上两个值之后，接下来计算一下全表扫描的成本：

• I/O成本： 97 x 1.0 + 1.1 = 98.1;
  • 97表示聚簇索引页面数
  • 1.0 表示加载一个页面的成本常数
  • 1.1 是一个微调值
• CPU成本： 10591 x 0.2 + 1.0 = 2119.2
  • 10591表中记录数，但是是估算值
  • 0.2 访问一条记录所需的成本常数
  • 1.0 微调值。
• I/O + CPU，全表扫描所需总成本 = 2217.3

计算使用不同的索引，执行查询的代价

使用idx_expire_time执行查询的成本分析

对应的搜索条件为：

1

AND expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'

上面的条件的区间范围是 2021-03-22 18:28:28 , 2021-03-22 18:35:09

使用idx_expire_time进行搜索会使用 二级索引 + 回表查询的方式，MySQL计算这种查询的成本的依赖两个方面的数据：

1) 范围区间的数量

• 查询优化器认为 读取索引一个范围区间的I/O 成本和读取一个页是相同。
• 范围区间的二级索引付出的I/O 成本： 1 x 1.0 = 1.0;

2) 需要回表的记录数

对于本例来说，就是要计算2021-03-22 18:28:28到2021-03-22 18:35:09这个范围中包含多少二级索引记录，计算过程：

根据MySQL的算法，测出的该二级索引的范围区间的记录数大约是：
AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ 之间大约有38条记录。

读取38条二级索引记录需要付出CPU成本：

• 38 x 0.2 + 0.01 = 7.61

在通过二级索引获取到记录之后，还要干两件事情：

1. 根据这些记录的主键值，到聚簇索引中做回表操作：
   • 二级索引之间有多少记录，就需要多少次回表，有多少次回表，就需要进行多少次的页面IO。
   • 上面的执行计划中预计有38条需要进行回表，回表带来的I/O成本： 38 x 1.0 = 38

2. 回表操作后，得到完整的用户记录，然后在检测其他的搜索条件是否成立。

   • 找到完整的记录之后，再检测

     1 2 3 4

     IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND insert_time > expire_time AND order_note LIKE '%7****排1%' AND order_status = 0;

   • 二级索引区间范围中共有38条数据，对应了聚簇索引汇中38条完整记录，读取并且检测是否符合其余查询条件的CPU成本： 38 x 0.2 = 7.6

本例中，使用idx_expire_time索引执行查询的成本如下：

• I/O成本： 38 x 1.0 + 1.0 = 39
• CPU成本： 38 x 0.2 +0.01 + 38 x0.2 = 15.21 (读取二级索引记录的成本 + 读取并检查回表聚簇索引的记录成本)

idx_expire_time执行查询的总成本就是： 39 + 15.21 = 54.21

补充

1. 为什么这里计算I/O 没有微调参数？
   I/O 相关的微调值 只有在全量表扫描时才会使用，当前两次I/O成本计算直接使用页数 × 1.0 来计算
2. 为什么这里计算CPU 有的加微调参数，有的没加？
   上面两处CPU成本计算
   +0.01 是 MySQL 给”读取索引记录”这一操作附带的固定启动成本，只挂在 cpu_index_tuple_cost 的计算路径上
   而”回表读取聚簇索引完整记录”走的是 cpu_tuple_cost 路径，公式里本来就没有这个常数项
   小结—>不同场景计算公式不同

使用idx_order_no执行查询的成本分析

idx_order_no 对应的搜索条件是

1

IN ('DD00_6S', 'DD00_9S', 'DD00_10S')

上面的条件是三个单点区间：

• 访问这三个范围区间的二级索引付出的I/O成本： 3 x 1.0 = 3
• 需要回表的记录数：为58

• 读取这些二级索引记录的CPU成本： 58 x 0.2 + 0.01 = 11.61
  • 根据这些记录的主键值到聚簇索引中做回表。所需的I/O成本： 58 x 1.0 = 58.0
  • 获取到完整记录之后，还需要用其他条件进行检查过滤， CPU成本: 58 x 0.2 = 11.6
• 所以本例中的成本总值为：
  • IO成本： 3 + 58 x 1.0 = 61 (范围区间数量 + 预估二级索引的记录数)
  • CPU成本： 58 x 0.2 + 0.01 + 58 x 0.2 = 23.21
  • 总成本： 61 + 23.21 = 84.21

对比各种方案，找出成本最低的

• 全表扫描：2117.3
• 使用idx_expire_time索引：54.21
• 使用idx_order_no索引：84.21

最终选择 idx_expire_time。

连接查询成本

驱动表的扇出值计算

连接查询成本的构成：

1) 单次查询驱动表的成本
2) 多次查询被驱动表的成本

对驱动表进行查询后，得到的记录条数称之为驱动表的扇出。

查询1

1

select * from orders as s1 inner join orders2 as s2;

假设s1是驱动表，只能使用全表扫描，扇出值很好计算，就是驱动表中有多少条记录，扇出值就是多少.

查询2

1
2

select * from orders as s1 inner join orders2 as s2
where expire_time> '2021-03-22 18:28:28'  AND expire_time<= '2021-03-22 18:35:09'

假设s1表是驱动表，对于驱动表的查询可以使用到索引idx_expire_time进行查询，此时区间范围的记录有多少，扇出值就是多少。

连接查询成本分析

连接查询成本计算公式：

连接查询的总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本。

• 左外或者右外连接只需要分别为驱动表和被驱动表选择成本最低的访问方法就可以了，因为驱动表固定的。
• 如果是内连接，驱动表和被驱动表可以互换的，所以要考虑两个问题：
  • 要选择最优表连接顺序
  • 分别为驱动表和被驱动表选择成本最低的访问方法

1
2
3
4
5
6
7
8
9
10

SELECT
    *
FROM
    orders AS s1
INNER JOIN orders2 AS s2 ON s1.order_no = s2.order_note
WHERE
    s1.expire_time > '2021-03-22 18:28:28'
    AND s1.expire_time <= '2021-03-22 18:35:09'
    AND s2.expire_time > '2021-03-22 18:35:09'
    AND s2.expire_time <= '2021-03-22 18:35:59';

在Explain 和查询语句之间，加一个 FORMAT = JSON，得出一个JSON格式的计划，里面就有该计划花费的成本：

1
2
3
4
5
6
7
8
9
10

explain FORMAT = JSON SELECT
    *
FROM
    orders AS s1
INNER JOIN orders2 AS s2 ON s1.order_no = s2.order_note
WHERE
    s1.expire_time > '2021-03-22 18:28:28'
    AND s1.expire_time <= '2021-03-22 18:35:09'
    AND s2.expire_time > '2021-03-22 18:35:09'
    AND s2.expire_time <= '2021-03-22 18:35:59';

order by优化

MySQL中有两种排序方式：

• 索引排序：通过有序索引进行顺序扫描，直接返回有序的数据。
• 额外排序：对返回的数据进行文件排序

order by 优化的核心原则： 尽量减少额外排序，通过索引直接返回有序数据。

索引排序

在排序查询中，如果能利用索引，就能够避免额外的排序操作。extra = Using index

1

select * from user where age = 18 order by name;

查询过程，找到 age = 18的记录，排序条件是name，name字段是联合索引的最左前列，是已经有序的了，所以不需要额外进行排序。

额外排序

所有不是通过索引直接返回排序结果的操作都是额外排序（FileSort排序）。Extra=Using filesort。

按照执行位置划分

1) 内存中 sort buffer

MySQL中为每个线程各维护了一块内存区域，用来进行排序，叫做 sort_buffer。

1
2
3

SHOW VARIABLES LIKE '%sort_buffer_size%';

SELECT 262144 / 1024;

以sort_buffer_size该参数为准
注意：sort_buffer_size并不是越大越好，因为是connection级别的参数，各大设置+高并发场景，可能会造成系统资源的耗尽。

2) sort buffer + 临时文件

如果加载的记录字段的总长度小于 sort buffer，就使用sort buffer，否则就会采用 sort buffer + 临时文件 进行排序。

按照执行方式划分

执行方式：max_length_for_sort_data 参数，如果用于排序的单条记录字段的长度 <= 该参数的值，就使用 全字段排序，否则就使用 rowid排序。

1
2

-- 如果单条记录字段的长度 < max... 选择全字段排序，否则 rowid排序。
SHOW VARIABLES LIKE 'max_length_for_sort_data';

全字段排序

• 将查询的所有的字段，全部加载进来 进行排序
  

• 优点：查询快，执行过程简单，缺点是 需要的空间比较大。

rowid排序

rowid排序不会将全部字段放入sort buffer中，所以在sort buffer中排序之后，还要回表查询。

• 优点：所需要的空间小。
• 缺点：会产生更多的回表查询，查询效率相对低一些。

排序总结

• 如果MySQL内存足够大，会优先选择全字段排序。
• MySQL的一个设计思想：要多利用内存，尽量减少磁盘访问

排序优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 创建联合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);

-- 为salary字段添加索引
ALTER TABLE employee ADD INDEX idx_sal(salary);

SHOW INDEX FROM employee;

-- 场景1： 只查询用于排序的索引字段,可以利用索引进行排序，最左原则
EXPLAIN SELECT
    e.`NAME`,
    e.`age`
FROM employee e ORDER BY e.`NAME`,e.`age`;

-- 场景2：排序字段在多个索引中，无法使用索引进行排序
EXPLAIN SELECT
    e.`NAME`,
    e.`salary`
FROM employee e ORDER BY e.`NAME`, e.`salary`;

1
2

-- 场景3：只查询用于排序的索引字段和主键值,可以利用索引进行排序
EXPLAIN SELECT e.`NAME`,e.`id` FROM employee e ORDER BY e.`NAME`;

1
2
3
4

-- 场景4：排序的索引字段，没有出现在查询的字段列表中，不会利用索引排序
EXPLAIN SELECT e.`dep_id` FROM employee e ORDER BY e.`NAME`;
EXPLAIN SELECT e.`id`,e.`dep_id` FROM employee e ORDER BY e.`NAME`;
EXPLAIN SELECT * FROM employee e ORDER BY e.`NAME`;

1
2

-- 场景5： 排序字段的顺序和索引的顺序不一致,无法利用索引排序
EXPLAIN SELECT e.`NAME`,e.`age` FROM employee e ORDER BY e.`age`,e.`NAME`;

group by优化

group by回顾

group by一般用于分组统计

1
2

-- 需求：按照城市进行分组，统计每个城市的员工的数量。
SELECT city,COUNT(*) num FROM emp GROUP BY city;

问题1：group by一定要配合聚合函数吗？

答：分组就是要去做统计的，否则分组没有意义了，所以一般都要配合聚合函数去使用，比如：count、sum、avg…

问题2：group by后面跟的字段，一定要出现在select列表中吗？

答：是一定要出现。如果没有没法确定是哪一个分组的值了。在标准SQL规范中，这点是必须的。（MySQL中大部分版本没有强制规定，Oracle中强制规定的），编写SQL时尽量在select列表中列出分组字段，确保查询的可移植性和可读性。

问题3：where和having的区别？

group by + where

1
2
3
4

EXPLAIN SELECT city,COUNT(*) num FROM emp WHERE age > 30 GROUP BY city;

-- 为emp表的age字段添加索引
ALTER TABLE emp ADD INDEX idx_age(age);

使用到了临时表，进行了文件排序

group by + having

1
2

-- 需求：查询每个城市的员工数量，获取到员工数量不低于3的城市。
SELECT city,COUNT(*) num FROM emp GROUP BY city HAVING num >=3;

where和having区别：

• having 子句用于分组后的筛选，where子句用于行条件的筛选。
• having 都是配合分组和聚合函数一起出现。
• where子句中不能使用聚合函数，但是having可以。

group by 执行原理

MySQL的分组中为什么包含排序？

• 聚合函数的使用：排序可以帮助最快找到最高或者最低的值。
• Top N查询：排序后，获取每个分组中前N个记录，更加方便。
• 结果的可读性：排序之后，可以更好的理解分组统计之后的结果。
• 用户需求：用户希望按某个列分组之后，进行排序。

group by优化

优化方案1：group by默认要进行排序，在合适的场景下，取消这个默认排序。

===> 使用 ORDER BY NULL

1
2

-- 取消分组排序
EXPLAIN SELECT city,COUNT(*) num FROM emp GROUP BY city ORDER BY NULL;

没有filesort

优化方案2：为group by字段添加索引，让其一开始的时候就是有序的。

1
2

EXPLAIN SELECT city,COUNT(*) num FROM emp WHERE age = 30 GROUP BY city;
ALTER TABLE emp ADD INDEX idx_age_city(age,city);

优化方案3：尽量使用内存表

如果group by需要统计的数据不多，可以尽量使用内存临时表，因为如果内存放不下，就会下沉到磁盘临时表中，导致性能下降。

1
2
3
4
5
6
7
8
9
10
11

-- 调整临时表大小
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';

SELECT 17179869184 / 1024 / 1024 /1024; -- 默认是16M

SELECT 16777216 * 1024;

SET GLOBAL tmp_table_size = 16777216;

-- MySQL配置文件中 设置
-- [mysqld] 下面，添加tmp_table_size = 16777216,重启生效。

优化方案4：使用SQL_BIG_RESULT优化

如果数据量特别大，数据就算放到临时表中（临时表已经够大了），但是还是会因为数据的插入达到上限，再转成磁盘临时表，还是会影响到数据库的性能。
如果预数据量比较大，我们使用SQL_BIG_RESULT直接提示MySQL 直接磁盘临时表。

• 禁用内存优化
• 适用于大结果集

一旦添加了SQL_BIG_RESULT，MySQL就不会再用B+树结构存储临时表数据，存储效率低，会选择使用数组，直接用数组存储。

1
2

-- SQL_BIG_SORT
EXPLAIN SELECT SQL_BIG_RESULT city ,COUNT(*) FROM emp GROUP BY city;

没有使用临时表

JOIN回顾：MySQL中用来进行连表操作，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN操作有几种方式，取决于最终数据的合并效果：

• 左外连接
• 右外连接
• 内连接
  

  驱动表和被驱动表

  什么是驱动表？
  •多表关联查询的时候，第一个被处理的表就是驱动表，使用驱动表关联其他表。
  •驱动表的确定是非常关键的，会直接影响到多表关联的顺序，还有决定了后续的关联查询的性能。
  通过Explain执行计划，验证一下不同的连接查询情况下，驱动表的选择

1）连接查询没有where条件的时候

2) 连接查询存在where条件：

• 带where条件的表就是驱动表，否则是被驱动表

1
2
3
4
5

EXPLAIN SELECT * FROM student s1 LEFT JOIN score s2 ON s1.`number` = s2.`number` WHERE s2.`id` = 2;

EXPLAIN SELECT * FROM student s1 RIGHT JOIN score s2 ON s1.`number` = s2.`number` WHERE s1.`id` = 2;

EXPLAIN SELECT * FROM student s1 RIGHT JOIN score s2 ON s1.`number` = s2.`number` WHERE s2.`id` = 2;

3) 驱动表选择的原则：在对最终的结果集没有影响的情况下，优先选择结果集小的那张表作为驱动表。

JOIN 算法原理

SNL 算法

Simple Nested Loop JOIN 简单嵌套循环连接

简单嵌套循环连接就是一个双层for循环，通过循环外层表的行数据，逐个与内层表的行数据进行比较来获取结果。

1
2
3
4
5
6
7
8
9
10
11

-- 连接用户与订单表，连接条件 用户表id = 订单表的user_id
select * from user u left join order o on u.id = o.id;

-- 转换成代码后
for(URow :  use表){
    for(ORow : order){
        if(URow.id = ORow.id){
            return URow;
        }
    }
}

SNL的特点

• 简单粗暴，容易理解，就是通过双层循环比较数据，获取结果。
• 查询效率非常低，假设A表有N行，B表有M行，SNL开销：
  • A表扫描1次
  • B表扫描M次
  • 一个有N个内循环，每个内循环就要M次，一共是N*M次。

Index Nested Loopjoin 索引嵌套循环连接

与SNL的区别：减少了内层表的数据的匹配次数，主要是因为在join字段（指的是被驱动表中的用于连接的字段）上建立了索引。

从原来 匹配次数=外层表行数*内层表行数，变成了匹配次数=外层表行数* 内层表连接字段的索引高度，从而提升了JOIN的性能。
注意：使用Index Nested loopjoin算法，前提是被驱动表的匹配字段，必须建立索引。

Block Nested Loop Join 块嵌套循环连接

在没有使用到索引字段进行表连接的时候，MYSQL会选择使用BNL块嵌套循环连接，通过加入buffer缓冲区，降低内循环的次数。

调整join buffer的大小

1
2
3
4
5
6
7

-- JOIN buffer 的大小,默认大小是256K。
SHOW VARIABLES LIKE '%join_buffer%';

SELECT 262144 / 1024;

-- 设置JOIN Buffer的大小
SET SESSION join_buffer_size = 262144;

JOIN使用总结：

1. 永远用小的结果集去驱动大的结果集（本质就是减少外层循环的数据数量）。
2. 应该为匹配的条件 增加索引（減少内层表的循环匹配次数）。
3. 增加 join buffer的大小（一次缓存的越多，内层表扫描的次数就越少）
4. 减少不必要的字段查询（字段越少，join buffer所缓存的数据就越多）

in函数 和 exists函数

小表去驱动大表，目的是为减少数据库连接的次数

in函数

1
2

-- 需求： 使用in函数将所有部门下的员工查出来
SELECT * FROM employee e WHERE e.`dep_id` IN (SELECT id FROM department);

in函数执行原理

• in语句，只执行一次，将部门表中所有的id字段查询出来，并缓存，接下来就是比较的过，伪代码：

1
2
3
4
5
6
7
8

for(did : dept){ --小表  SELECT id FROM department
    for(eid : emp){ 大表
        if(did == eid){ SELECT * FROM employee e WHERE e.`dep_id` = dept_id
            return e;
            break;
        }
    }
}

exists函数

1) exists函数介绍

• 是一个用于判断子查询是否返回结果的条件函数，返回值是boolean类型。通常使用的场景是判断一个子查询是否至少返回一行数据，根据判断结果进行相应操作。

2) exists函数语法

1
2

select col from table where exists(subquery);
subquery：是一个子查询，用于判断是否存在满足特定条件的数据行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- 查询有员工的部门
-- 连接查询
SELECT
 DISTINCT d.*
FROM department d LEFT JOIN employee e ON d.`id` = e.`dep_id`
WHERE e.`dep_id` IS NOT NULL;

-- exists函数
SELECT * FROM department d WHERE EXISTS(
    SELECT 1 FROM employee e WHERE e.`dep_id` = d.`id`
);

-- 2.查询没有员工的部门
SELECT
 DISTINCT d.*
FROM department d LEFT JOIN employee e ON d.`id` = e.`dep_id`
WHERE e.`dep_id` IS NULL;

-- exists函数
SELECT * FROM department d WHERE NOT EXISTS(
    SELECT 1 FROM employee e WHERE e.`dep_id` = d.`id`
);

-- 3.查询有部门的员工
SELECT * FROM employee e WHERE e.`dep_id` IS NOT NULL; -- 有问题SQL

SELECT * FROM employee e WHERE EXISTS(
    SELECT 1 FROM department d WHERE d.`id` = e.`dep_id`
);

-- 4.查询没有部门的员工
SELECT * FROM employee e WHERE NOT EXISTS(
    SELECT 1 FROM department d WHERE d.`id` = e.`dep_id`
);

3) exists特点

• exists子句返回的是一个布尔类型值，如果有符合条件的数据返回true，否则返回FALSE
• 如果为true，外层的查询语句就会进行匹配，否则外层查询语句就不进行查询。

5) exists执行原理

1
2
3
4
5
6
7
8
9
10
11
12

SELECT * FROM employee e WHERE EXISTS(
    SELECT 1 FROM department d WHERE d.`id` = e.`dep_id`
);

-- 先循环 SELECT * FROM employee e
-- 再判断 SELECT 1 FROM department d WHERE d.`id` = e.`dep_id`

for(e : emp){
    if(exists(e.dept_id)){
        return e;
    }
}

in和exists的区别

• 如果子查询得出的结果集记录比较少，主查询中的表比较大并且有索引，该用in。
• 如果主查询结果集得出的记录比较少，子查询的表比较大，并且有索引，该用exists。

一句话：in后面跟的是小表，exists后面跟的是大表。

MySQL 调优 金字塔

越向上难度越大，回报减少。
1.硬件和OS的调优，十分复杂，需要有对硬件和OS了解比较深的人员去进行化。
2.MySQL调优，调整表结构、优化SQL、恰当的使用索引，清除多余的索引等等。
3.架构调优，需要考虑实际的业务场景

1. 可以将非数据库的任务，放到数据仓库，搜索引擎或者缓存。
   2.评估并发量，决定是不是要做分布式。
   3.根据读压力，考虑是否进行读写分离。
   业务DBA：从业务需求讨论到表结构审核、SQL语句审核、上线、索引的更新。

什么是慢查询日志

记录查询花费大量时间的SQL的日志，就是慢查询日志。
long_query_time采数：该参数会设定一个阈值，超过该值的SQL，就是慢查询SQL。

SQL查询性能下降的原因

查询性能变低的最基础的原因，就是访问的数据太多了。
对于低效的查询，可以通过下面两个步骤分析：
1.确认是否在检索大量超过需要的数据。可能是访问了很多的行，也有可能是访问了很多的列。
2.确认MySQL服务层是否在分析大量超过需要的数据行

请求了不需要的数据

1. 查询不需要的记录
   MySQL会查询出全部的数据集(type : all)，客户端应用程序会接收全部的数据集，然后抛弃其中大部分数据

2. 总是取出全部的列
   select * …..,注意 是否需要真的返回全部的列。取出全部的列，会导致优化器无法完成索引覆盖扫描。
   一些DBA是严格禁止使用 select *，尤其使用二级索引时，会导致回表，性能下降明显。
   什么时候可以用 select *？
   • 如果应用程序使用了某种缓存机制，可能就需要获取全部的数据进行缓存，这时候可以选择去用，但是要考虑付出的代价。

3. 重复查询相同的数据
   不断重复同样的查询，返回相同的记录，也是十分消耗资源，影响MYSQL性能的，这个时候可以考虑进行缓存

扫描了过多的额外记录

如果确定了查询只返回需要的数据以后，接下来就应该看看查询时为了返回需要的数据，是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标：

1) 响应时间：服务时间+排队时间

* 服务时间：数据库处理这个查询时，真正花了多少时间。
* 排队时间：指的是服务器因为等待某些资源而没有真正执行查询的时间。有可能是等待行锁。

2) 扫描的行数和返回的行数

*   理想情况下，扫描的行数和返回的行数应该是相同的。

3) 扫描的行数和访问的类型

*   在Explain分析结果中，字段type反映了访问的类型。
*   访问类型，从慢到快：全表扫描 --> 索引扫描 --> 范围扫描 --> 唯一索引扫描 --> 主键扫描

MySQL中使用下面三种方式应用where条件，从好到坏：
1) 在索引中使用where条件，过滤不需要的数据，这个操作存储引擎中完成。
2) 使用覆盖索引扫描来返回记录，直接从索引中过滤不需要的记录并且返回命中的结果。是在MySQL的服务层完成，不需要再回表查询。
3) 从数据表中返回数据，存在回表，过滤不需要的数据，在MySQL的服务层完成的，MySQL需要从数据表读取数据然后进行过滤。
返回的行数少，扫描的行数多，优化方式：
1) 使用索引覆盖扫描，把所有需要用的列都放到索引中。
2) 改变库表的结构。
3) 重写复杂SQL。

慢查询日志分析

MySQL慢查询，全名叫做慢查询日志，作用是用来记录MySQL中响应时间超过设定的阈值的SQL语句。
默认情况不启动慢查询日志。
慢查询相关的参数
慢查询参数解释：

• slow_query_log：是否开启慢查询日志，ON (1)表示开启，OFF(0)表示关闭。
• slow_query_log_file：MySQL数据库慢查询日志的存储的路径。
• long_query_time：慢查询的阈值，当查询时间大于设定的阈值的时候，会记录到日志。

可以通过设置slow_query_log的值，来开启慢查询日志

1
2

mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)

上面这种设置方式，只对当前的窗口有效的，MySQL重启之后就会失效。如果想要永久生效，需要修改my.cnf

1
2

slow_query_log =1
slow_query_log_file=/var/lib/mysql/test-slow.log

重启MySQL让配置生效

1

[root@localhost ~]# service mysqld restart

开启慢查询日志之后，什么样的SQL才会记录到慢查询日志中呢？是由参数long_query_time来控制的，默认情况下值是10秒。
重新设置值：

1

mysql> set global long_query_time=1;

注意：设置完成后，需要重新连接MySQL才能看到修改后的值。

• log_output：
  • 值为FILE，表示将日志存储到文件，默认值。
  • 也可以设置存储到数据库：log_ouput=TABLE，但是耗费更多系统资源。
• log_queries_not_using_indexes：开启后，未使用索引的查询，也会被记录到慢查询日志中。

日志内容解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

----查看日志 tail -f /var/1ib/mysql/test-slow.1og:
# Time: 2023-08-30T07:52:49.392140Z
# User@Host: root[root] @  [192.168.52.1]  Id:     4
# Query_time: 7.653827  Lock_time: 0.000095 Rows_sent: 3  Rows_examined: 4000003
use test_slowlog;
SET timestamp=1693381969;
SELECT * FROM test_index LIMIT 4000000,3;
# Time: 2023-08-30T07:53:12.301259Z
# User@Host: root[root] @  [192.168.52.1]  Id:     4
# Query_time: 3.328770  Lock_time: 0.000216 Rows_sent: 4  Rows_examined: 5000000
SET timestamp=1693381992;
-- 写一条执行时间超过1秒的SQL
SELECT * FROM test_index WHERE
hobby = '2000001' OR hobby = '2000011' OR hobby = '2000021'
OR dname = 'name40000000' OR 'name400000100' LIMIT 0, 1000;

• Time: 执行时间
• User: 用户信息
• Query_time: 查询语句执行的时间
• Lock_time: 等待锁的时间
• Rows_sent: 查询结果行数
• Rows_examined: 查询扫描的行数
• SET timestamp: 事件戳
• SQL的具体信息

慢查询SQL的优化思路

SQL执行时间长的原因：

• 等待的时间长：大概率是由于锁表或锁冲突导致的，让查询一直处于等待状态。
• 执行的时间长
• 查询的SQL写的烂
• 索引失效
• 关联的JOIN太多
• 服务器调优及各个参数设置

慢查询优化的思路：

• 优先选择优化 高并发执行的SQL，因为高并发执行SQL发生问题带来的后果，更加严重。
  • 比如下面两种情况：
  • SQL1：每小时执行10000次，每次20个IO，优化后每次18个IO，每小时节省2万次IO。
  • SQL2：每小时执行10次，每次20000个IO，优化后每次减少2000个IO，每小时节省2万次IO
  • SQL2的并发度更高，SQL更难去优化，SQL1更好优化一些，但是SQL2属于高并发SQL，更加急需优化。
• 定位优化对象的性能瓶颈
  • IO：数据访问的时候消耗了太多的时间，查看是否正确的使用了索引。
  • CPU：数据运算花费了太多的时间，数据运算的分组、排序是不是有问题
  • 网络带宽：如果网络带宽受限，数据传输的速度也将受到限制，从而导致查询的响应时间增加了。
• 明确优化的目标（最终的优化结果，就是给用户一个好的体验）
  • 需要根据数据库当前的状态。
  • 数据库中与当前该条SQL的关系。
  • 当前SQL的具体功能。尽量选择优化那些对业务影响比较大的SQL。
• 从explain执行计划入手
  • 只有explain能告诉你当前SQL的状态。
• 永远用小的结果集去驱动大的结果集
  • 小的结果集驱动大的结果集，目的就是减少内层表读取的次数。

1
2
3
4
5

for(int i = 0; i < 5; i++){
    for(int i = 0; i < 1000; i++){

    }
}

如果小的循环在外层，对于数据库的连接来说，就是只连接5次，进行5000次操作，否则反过来就是连接1000次，每次做5次操作，就会增加资源的浪费

• 尽可能的在索引中完成排序
  • 因为索引本身就是排好序的，排序字段在索引中的话，速度是比较快的。
• 只获取自己需要的列
  • 不要用select *
• 只去使用最有效的过滤条件
  • 误区：where后面的条件越多越好，实际上应该是用最短的路径访问到数据才是最好的。
• 尽可能避免复杂JOIN和子查询
  • 每条SQL的JOIN 操作，建议不超过3张表
  • 将复杂的SQL拆分成多个小SQL 单个表执行，获取到结果 去程序中封装就可以。
• 合理设计并且利用索引

如何判断是否需要创建索引？

1. 较为频繁的作为查询条件的字段，应该创建索引
2. 唯一性太差的字段不适合单独的去创建索引。比如：性别 状态 类型字段，当一条query所返回的数据超过了全表的15%的时候，就不需要再使用索引扫描。
3. 更新频繁的字段不适合创建索引。因为需要维护索引文件。
4. 不会出现在where条件中的字段 不需要创建索引。

如何选择合适的索引？

1. 对于单键索引，尽量选择针对于当前查询效果更好的索引。
2. 选择联合索引时当前查询中过滤性最好的字段应该在 联合索引的最左侧。

创建策略

索引在查询中的作用

一个索引就是一颗B+Tree，索引可以让我们的查询快速定位和扫描到我们需要的数据记录上，加快查询速度。

索引列的类型尽量小

1）定义表结构时一定会指定列类型，举例说：针对整数类型，就有很多选项：tinyint、smallint、int、bigint.。占用的存储空间是逐渐增加的。
2） 选择较小数据类型创建列的好处
•使用较小的数据类型，可以在查询时带来性能上的一些提升。
•较小的数据类型占用的索引存储空间比较少的，一个数据页就可以容纳更多的数据记录，从而減少磁盘/0性能的消耗。同时也意味着内存中，是可以有更多的数据页缓存，从而提高数据库的读写效率。
3）主键类型选择小类型非常重要
•主键值，不仅是在聚簇索引中存储，其他的二级索引的节点上也会存储主键值。选择较小的主键数据类型，可以节省存储空间，并且提高查询效率。

—>索引列类型尽量小好处

1. 有利于提高查询效率
2. 减少存储空间，一个是自身的存储空间，一个是作为主键在二级索引中的存储空间。

索引列的选择性尽量高

索引列的选择性是指在一个数据库表中，某个特定列的值的唯一性和多样性程度。
索引列的选择性衡量方式：选择性=不同值的数据量/总行数，选择性通常是介于0~1之间的值。

选择性高低的区别：
•选择性越高，表示该列中的值越多样化，就更加接近唯一。
•选择性越低，表示该列中重复值比较多。
选择性高的索引，可以让MySQL在查询时过滤掉更多的行。唯一性索引的选择性1，这是最好的索引选择性，性能也是最好的

1
2

-- 索引选择性计算方式
SELECT COUNT（DISTINCT ename） / COUNT（*） FROM emp；

前缀索引

对于不能直接使用全量值作为索引列的数据类型，比如字符串类型
通过选取不同的前n个数据作为索引内容，计算索引列的选择性后，选择索引列的选择性最高的n 值，作为最后进行索引的数据计算

针对于blob、text、很长的varchar类型，MySQL是不支持索引它们全部的长度，需要建立前缀索引.

1
2

-- 前缀索引的创建
alter table tablename add key index（column（n））

前缀索引的缺点：前缀索引是一种能使索引更小，更快的有效办法，但是它的缺点也很明显，MySQL中无法使用前缀索引做
order by、group by，也无法使用前缀索引进行索引覆盖。
前缀索引使用的注意事项：使用的是较短的前缀，可能会降低索引的选择性，影响查询效率。所以在使用前缀索引之前，需要仔
细考虑分析数据分布，确保前缀长度的选择是合适

不同前缀长度的索引选择性：

多列索引的创建原则

1. 选择性最高的列放在最前面，因为选择性高的列通常可以更好的筛选数据，减少检索的数据量。
2. 根据运行频率最高的查询，来调整索引的顺序。
3. 覆盖查询需要的列，创建联合索引的时候，要注意这个联合索引是否是一个覆盖索引。可以避免在索引中和表中来回的跳转（回表）。
4. 避免冗余索引：如果已经有了一个联合索引，再创建一个包含该索引一部分的索引是没有必要的。冗余索引会增加索引的维护成本。
5. 权衡索引的长度：太长索引会占用更多的存储空间，同时也会降低索引检索效率。
6. 避免使用过多列：只去选择那些在查询中被经常使用到的列。
7. 只在查询条件中被经常使用，或者排序分组中经常使用的字段，去创建索引。

三星索引

针对查询而言，一个三星索引，可能是其最好的索引。

三星索引需要满足的条件

1. 一星：索引将相关的记录放到了一起。
2. 二星（排序星）：索引中的数据顺序和查找中的排序顺序一致。
3. 三星（宽索引星）：索引中包含了所要查询的全部列的数据。

具体含义

• 一星：如果一个查询相关的索引行是相邻的，或者相距比较近，那么需要扫描的索引片的宽度就会缩短。一星要求让索引片尽量变窄，也就是索引的扫描范围越小越好。
• 二星（排序星）：当查询需要排序时，使用的索引本身是有序的，就可以不用再去额外排序了。
• 三星（宽索引星）：如果索引中包含了所要查询的全部列的数据，就是一个覆盖索引，查询不需要回表，减少了 I/O 的次数。

案例一：完全满足三星要求

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

--- 建表语句
CREATE TABLE customer(
    number INT,
    username VARCHAR(10),
    nicename VARCHAR(10),
    sex INT,
    city VARCHAR(10)
);

CREATE INDEX idx_cun ON customer(city, username, nicename);

---- 查询语句

SELECT username, city
FROM customer
WHERE city = '北京' AND username = 'lisi'
ORDER BY city;

—- 评估该索引满足几颗星

• 第一颗星：✅ 满足。city、username 作为索引前列，能有效减少索引片大小，减少需要扫描的行数。
• 第二颗星：✅ 满足。ORDER BY city，而 city 字段在组合索引的最左侧，本身已经排好序。
• 第三颗星：✅ 满足。SELECT 查询的 username 和 city 字段都在联合索引中，无需回表。

案例二：评估现有索引（满足二颗星）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

--- 建表语句
CREATE TABLE customer2(
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(100) DEFAULT NULL,
    sex INT(11) DEFAULT NULL,
    age INT(11) DEFAULT NULL,
    c_date DATETIME DEFAULT NULL,
    PRIMARY KEY (id)
);

SELECT
    user_name,
    sex,
    age
FROM customer2
WHERE user_name LIKE '大%' AND sex = 1
ORDER BY age;

CREATE INDEX idx_usa ON customer2(user_name, sex, age);

评估结果

• 第一颗星：✅ 满足
• 第二颗星：❌ 不满足。因为查询语句中使用 age 进行排序，而 user_name 采用了范围匹配，导致 age 无法保证有序。
• 第三颗星：✅ 满足

结论：修改后满足 1、3 星。

为满足第二颗星重新设计索引， 优化后的索引

1

CREATE INDEX idx_sau ON customer2(sex, age, user_name);

重新评估

• 第一颗星：❌ 不满足。sex 字段的选择性低，字段值重复率高，无法有效缩小索引片。
• 第二颗星：✅ 满足。在 sex 等值条件下，age 是有序的。
• 第三颗星：✅ 满足。

结论：修改后满足 2、3 星。

总结

同时满足三星的情况是比较少的。所以在设计索引的时候，尽可能满足两颗星就已经是不错的选择了。

使用策略

最佳左前缀法则

使用索引时，where后面的条件需要从索引的最左前列开始，并且不能够跳过索引中的列使用。创建的是联合索引，在使用时要遵守该法则

原理
MYSQL在创建联合索引时的规则是：首先会对联合索引最左边的字段进行排序（例子中user_name），在第一个字段的基础之上，再对第二个字段进行排序

最佳左前缀原则其实是和B+树的结构有关系，最左字段肯定是有序的，第二个字段则是无序的
联合索引的排序方式是：先按照第一个字段进行排序，如果第一个字段相等再根据第二个字段排序
所以如果直接使用第二个字段 user_age 通常是使用不到索引的

不要在索引列上做任何操作

不要在索引列上做任何操作，包括了比如计算、使用函数、自动的或者手动进行数据类型转换，都会导致索引的失效，从而使查询转向全表扫描。

范围条件放最后

在编写查询语句的时候，where条件中如果有范围条件，并且范围条件之后还有其他过滤条件的话，那么范围条件之后的列就都将会索引失效

like 查询注意事项

like查询以％开头的话，就会使索引失效，％出现在左边索引失效，％出现在右边索引正常使用。

like % 在关键字左边导致索引失效原因

1. %在右边：有B+树的索引顺序，按照首字母的大小进行排序，%如果是在右，匹配的是首字母。所以可以在B+树上进行有序查找，查找首字母符合要去的数据。
2. %在左边：匹配的是字符串尾部的数据，尾部的字母是没有顺序的，所以无法按照索引顺序查询，索引失效。
3. 两个%：查询任意位置的字母满足条件就可以。与%在左的问题一样只有首字母有序，其他位置的字母是无序的，所以索引失效。

null值注意事项

避免使用 is null、is not null、！= 、or

关于null值的说明可能有以下三种情况
1）定义1：null值代表着一个未确定的值，MySQL认为任何和nul值进行比较的表达式的结果都为null。所以认为每一个null值都
是独一无二。（nulls_unequal）
2）定义2：null值在业务上就是代表没有，所有的null 就被算作是一份。(null_equal)
3）定义3：null值完全没有意义，在统计的时候就不要算进来了 (nulls_ignored)

假设：一个表中的某个列c1列，记录分别为（2,1000,null,null），第一种情况表中根据c1统计的记录数为4.
第二种表中的c1的记录数为3，第三种表中c1的记录数为2

在MySQL5.7.2版本之后，MySQL将这个值写死为nulls_equal。总的来说对于列的声明，尽可能的不要允许为null！

explain 可以模拟优化器执行sql 语句，从而分析sql语句处理过程，得到sql语句或者表结构性能瓶颈

MySQL架构体系

MySQL是由连接池、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成的，可以分为四层：
• 连接层
• 服务层
• 引擎层
• 文件系统层

MYSQL 查询过程

主要字段

id字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14

--创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8'；

--创建表
CREATE TABLE LICTd INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR（100））；
CREATE TABLE L2C1d INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR（100））；
CREATE TABLE L3（id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR（100））；
CREATE TABLE L4Cid INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR（100））；

-- 每张表插入3条数据
INSERT INTO LI（title） VALUES（'fesco001'）， （'fesco002'）， （'fesco003'）；
INSERT INTO L2（title） VALUES （'fesco004'）， （'fesco005'）， （'fesco006'）；
INSERT INTO L3（title） VALUES（'fesco007'），（'fesco008'）， （'fesco009'）；
INSERT INTO L4（title） VALUES（'fesco010'）， （'fesco011'），C'fesco012'）；

id字段表示的是查询的序列号，包含一组数字，表示查询中执行子句或者操作表的顺序

select_type字段

• table：表名
• select_type：表示【查询类型】，主要用于区别普通查询、联合查询、子查询等等复杂查询。

1. simple

1
2

-- simple
EXPLAIN SELECT * FROM L1 WHERE id = 1;

simple 表示简单的 select 查询，查询中不包含子查询和 UNION。

2. primary / subquery（复杂查询）

1
2
3
4
5
6

-- 复杂查询
EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (
        SELECT id FROM L3 WHERE title = 'fesco008'
    )
);

• primary：查询中如果包含任何复杂的子部分，最外层查询将会被标记为 primary
• subquery：在 select 或者 where 列表中包含的子查询

3. union / derived / union result（合并查询）

1
2

-- 合并查询 注意UNION连接L3和L4，L3被标记为derived，L4被标记为union
EXPLAIN SELECT * FROM (SELECT * FROM L3 UNION SELECT * FROM L4) a;

• union：union 连接的两个 select 查询，除了第一个查询被标记为 derived，第二个及以后的表的 select_type 都是 union
• derived：在 from 列表中包含的子查询被标记为 derived（派生表）
• union result：union 的结果

type字段

type字段显示的是连接类型，type描述了找到所需要的数据，所使用的【扫描方式】，是一个非常重要的指标。

– 完整的连接类型比较多
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery >
index_subquery > range > index > ALL
– 简化之后，我们可以只关注一下几种
system > const > eq_ref > ref > range > index > ALL

一般来说，需要保证查询至少到 range 级别，最好是能到 ref，否则就证明我们的 SQL 需要进行优化调整。

type 字段值的含义：

1. system：表中就仅仅只有一行数据的时候，比较少见。
2. const：const 表示命中的是主键索引或者是唯一索引，表示通过索引一次就获取到对应的数据记录。

1

EXPLAIN SELECT * FROM L1 WHERE id = 3;

主键索引等值查询，type 为 const。

1
2
3

-- 为L1表添加唯一索引
ALTER TABLE L1 ADD UNIQUE(title);
EXPLAIN SELECT * FROM L1 WHERE title = 'fesco003';

唯一索引等值查询，type 同样为 const，Extra 为 Using index。

3. eq_ref：对于前一个表中的每一行，后表只有一行被扫描。只有当连表使用索引的部分都是主键或者唯一非空索引时，才会出现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 准备测试数据
create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

-- eq_ref：两表通过主键关联查询
EXPLAIN SELECT * FROM user u LEFT JOIN user_balance ub ON u.id = ub.uid;

user 表（u）type 为 ALL，全表扫描 4 行；user_balance 表（ub）通过主键关联，type 为 eq_ref，key 为 PRIMARY，ref 为 test_explain.u.id，对 user 表的每一行只在 user_balance 中扫描一行。

4. ref：使用了普通索引（非唯一性索引），对于前表的每一行，后表有可能有多于一行的数据被扫描，会返回所有匹配某个单独值的行。

5. range：索引上的范围查询，检索给定范围的行。between、in函数、>、＜都是范围查询
   
6. index：出现index 表示SQL使用了索引，但是没有通过索引进行过滤。需要扫描索引上的全部数据。
   
7. ALL：全表扫描，没有使用索引。
   

type类型总结：
• system：不进行磁盘I0，查询系统表，仅仅返回一条数据
• const：查找主键索引，最多返回1或者0条数据。属于精确查找
• eq_ref：查找唯一索引，返回数据最多1条，属于精确查找
• ref：查找非唯一性索引，返回匹配某一条数据的多行记录，属于精确查找。
• range： 查找某个索引的部分索引，只检索给定范围的行级，属于范围查找。
• index：查找所有的索引树，比ALL快一些。
• ALL：不使用任何索引，直接全表扫描

possible_keys 与 key说明

• possible_keys：显示可能应用到这张表上的索引，一个或者多个，查询涉及到的字段上如果存在索引，该索引将会被列出，但是实际查询不一定用到。
• key：实际使用的索引，若为null，则表示没有用到索引。两种可能

1. 没有建立索引
2. 建立索引，但是索引失效
   查询中使用了覆盖索引，该索引只会出现在key列表中
   

   key_len字段

key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。
key_len字段能够帮助你检查是否充分的利用了索引，ken_len越长，说明索引利用的越充分

1
2
3
4
5
6

CREATE TABLE L5（
    a INT PRIMARY KEY，
    b INT NOT NULL，
    C INT DEFAULT NULL，
    d CHAR （10） NOT NULL
);

ref/rows/patition/filtered字段说明

分区/分表/分库

解决的层次不同、拆分粒度不同。可以按”从单机到分布式”的顺序来理解：分区 → 分表 → 分库，拆分力度依次增大。

一、分区（Partition）

层次：单库单表内部，逻辑上还是一张表。

做法：把一张表的数据，按某种规则（如范围、哈希、列表）拆成多个物理文件存放，但对应用来说仍然是一张表，SQL 不用改。

常见分区类型

特点

• ✅ 应用无感知：SQL 不变，还是查同一张表。
• ✅ 单库内解决：不涉及多库、多实例。
• ❌ 仍受单机限制：数据量、连接数、磁盘 I/O 还是同一台机器。
• ❌ 跨分区查询可能变慢：如果 WHERE 条件没带分区键，要扫描所有分区。

适用场景

单表数据量过大（如几千万到上亿），但还没到需要拆库的程度。

二、分表（Sharding / 分表）

层次：同一个库内，把一张表拆成多张独立的表。

做法：按规则把数据分散到 orders_0、orders_1、orders_2…… 多张表里。应用需要知道数据在哪张表，或者通过中间件路由。

常见拆分方式

特点

• ✅ 突破单表数据量瓶颈：每张表数据变少，查询更快。
• ✅ 仍在同一个库：事务、连接还在一起，相对简单。
• ❌ 应用需要改：SQL 要改表名，或引入中间件。
• ❌ 跨表查询麻烦：JOIN、ORDER BY、COUNT 等要合并结果。
• ❌ 仍受单库限制：磁盘、连接数、CPU 还是同一台机器。

适用场景

单表数据量极大，但单库的整体压力还能承受。

三、分库（分库）

层次：把数据拆分到多个数据库实例（可能在不同机器上）。

做法：按规则把不同的表，或同一张表的不同部分，放到不同的数据库实例中。通常和分表结合，形成 “分库分表”。

常见拆分方式

特点

• ✅ 突破单机瓶颈：CPU、内存、磁盘、连接数都能横向扩展。
• ✅ 提升可用性：一个库挂了，其他库还能用。
• ❌ 架构复杂度高：需要处理分布式事务、跨库 JOIN、全局 ID、数据一致性。
• ❌ 运维成本高：多实例部署、监控、备份、扩容。

适用场景

单库已经扛不住，数据量、并发量都到了单机上限。

四、三者对比

五、演进顺序（推荐路径）

实际架构演进通常是：

1
2
3
4
5
6
7

单库单表
   ↓ 数据量变大
分区（单库内拆分，应用无感知）
   ↓ 还不够
分表（单库内多表）
   ↓ 单库扛不住
分库分表（多库多表，分布式）

核心原则：能分区就不分表，能分表就不分库。因为每往上一层，复杂度、运维成本、开发成本都大幅上升。分库分表是最后的手段，不是首选。

六、一句话总结

• 分区：一张表在单库内拆成多个物理文件，应用无感知。
• 分表：一张表拆成多张表，还在同一个库，应用要改。
• 分库：数据拆到多个数据库实例，突破单机瓶颈，但复杂度最高。

三者是递进关系，拆分力度和复杂度依次增大，应按需选择，避免过度设计。

Extra字段说明

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE users（
    uid INT PRIMARY KEY AUTO_INCREMENT，
    uname VARCHAR（20），
    age INT （11）
）；
INSERT INTO users VALUES（NULL，'lisa'，10）；
INSERT INTO users VALUES（NULL，'Tisa'，10）；
INSERT INTO users VALUES （NULL，'rose'， 11）；
INSERT INTO users VALUES （NULL，'jack'，12）；
INSERT INTO users VALUES （NULL，'sam'，13）；

EXPLAIN SELECT * FROM users ORDER BY age

对查询进行了优化，减少回表的次数。Using index dbondition只适用于二级索引

数据库事务

什么是数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果将使数据库从一种一致性状态变迁到另一种一致性状态。事务是逻辑上的一组操作，要么全部执行，要么全部不执行。

事务的四大特性(ACID)

1. 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
2. 一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。
3. 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
4. 持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

SQL 标准定义的四个隔离级别

1. Read Uncommitted（读取未提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. Read Committed（读取已提交）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3. Repeatable Read（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. Serializable（可串行化）：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

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

1. 脏读（Dirty Read）：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个事务 RollBack 了操作，则后一个事务所读取的数据就会是不正确的。
2. 不可重复读（Non-repeatable read）：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新原有的数据。
3. 幻读（Phantom Read）：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几行（Row）数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。

各个事务隔离级别对脏读、不可重复读、幻读的支持情况

事务冲突与解决

数据竞争冲突场景与解决

ACID DB

一种写入数据库的方式
ACID DB Transactions:

• Atomicity:
  All writes succeed or none of them do
  Jondan - $10, Loriaa Keef +$10
  数据一致性，有￥10 支付，肯定要有￥10 收入

• Consistency:
  All fails occur gracefully, no invariants are broke
  eg.
  Need at least one officer on shift
  Delek“Lomy’and write “Osscur”
  Failuer in the mildle? Now no security guard
  数据发生故障时，能够优雅方式处理故障

• Isolation:
  Appears if all transations are executed independently of each other,
  No race conditions
  所有交易操作都独立进行，不存在并发竞争关系
  并发竞争关系
  eg. 两个线程同时对数据库同一数据原值0 进行+1操作，由于两次读取时，都是读到的是数据0，+1 操作完落库的结果是1，虽然应该加两次

• Durability:
  comitted writes, don’t get lost data on disk

Atomicity, Consistency, Durability 都能通过预写日志(write ahead log)实现，
Isolation is hard and slow

read committed Isolation

当multi读写任务几乎同时发送到数据库，时间上差不多处于同一时期，no way to know exact order, 无法保证操作结果合法性

commit : a write state, only happens when all are finished
在数据库中所有操作都是完成态

dirty writes

对于相同数据的写操作同时由不同的用户发起，二者写的内容不同，任务顺序不可控，可能会导致最后结果不符合双方预期

解决办法： row level locks(grab rows in same order)
通过加锁机制，处于写入执行过程中的数据被保护起来，不再穿插执行其他用户任务，执行完一整套操作，再执行下一套操作，保证数据准确性

dirty reads

对于上图中操作，当t1 corina 收入+10 的操作没有commit 的时候，T2 打算读取corina 收入+10 之后的余额时，可能由于账户消除等原因导致读取异常

解决办法：也可以用锁，但是锁的成本太高
这里可以采用存储老数据直到commit 的时候

现在余额是100，old 指针指向100， 花10元后新值是90，new 指针指向90
现在收入+10
将old指针从100 改到90 ，再计算, 这样即使commit 没有完成，也能将old data 给出去

snapshot isolation

用来解决并发事务之间的读写冲突（一个事务正在读某条数据，同时另一个并发事务正在修改这条数据。）
读事务不要去读“当前正在被修改的数据”，而是读事务开始时的一个快照。
实现写不会阻塞读，读也不会读到未提交的写。
如果用锁，写过程在commit 之后读才能去读，造成阻塞

repeatable read

• Non-repeatable Read 不可重复读/ Read Committed : 同一个事务里，前后两次读取同一条数据，结果不一样。
  eg.

1. t1 读a = 30； t1 还没commit 时，t2 修改 a = 40 ；t1 再读 a= 40
2. 数据库中所有数据原始相加为100，000， 现在依次读取单个数据所有值，但在读取过程中，还没有commit,中间某个数据K发生变化，由10，000变成0，少的10，000 添加到已读的某个数据C上，这样就会导致最终K 读到0 ，最终所有数据相加比100，000 少10，000，但实际上总和咩有变
   

• Repeatable Read 可重复读: 同一个事务里，你第一次读到某条数据之后，即使其他事务把它修改并提交，你再次读取这条数据，仍然得到第一次读到的结果。
  eg. t1 读a = 30； t1 还没commit 时，t2 修改 a = 40 ；t1 再读 a= 30

加锁实现有阻塞
T1 第一次读 → 20
T2 修改 → 等待
T1 第二次读 → 20
T1 COMMIT
T2 才能修改

使用snapshot
T1 第一次读, 创建快照 → 读快照 ->20
T2 修改
T1 第二次读 → 读之前快照 20

write skew

两个事务各自读取到一个一致的快照，然后分别修改不同的数据，最后两个事务都成功提交，但合起来违反了业务规则。

Note: Before setting themselves to inactive, each doctor must first scan the database to check if there is at least one other active doctor
When they both do these reads it looks like there is another doctor active and they both set their statuses to inactive
Because we’re not grabbing the locks of both doctors, each can perform the read
and then write successfully at the same time, thus breaking the invariant

解决方案：写入时将其他active 状态的rows 进行行锁，即更改一个active 后才能更改下一个
对于同时修改的情况，谁先grab 更多的行锁，谁就能先进项写入

If I’m holding the locks of all of the rows that I read, I know that my predicate statement （”there is another active doctor”） will remain true when I write

phantom 幻影

occur when two people write new rows that conflict, no locks to grab the new row
两个新数据都需要处理，会造成数据库不一致问题

解决： 预填入
这样为填入的数据就可以拥有相对应锁对象，就能够通过locks对数据写入进行控制

First we check to see if anyone has claimed the cupcakes and then since the email is empty we try to grab the lock to make the write

actual serial execution

随着cpu 运算速度的提高，之前依赖多线程多核同时执行的任务现在可以在一个core 按照顺序执行
但这种方式有一定缺陷，
比如在disk 上运行导致速度变慢 —> 把数据放在内存上运行
—> 好处速度快，可以使用hash, 二叉树等手法进行查询 但内存size 有限，不能持久化
—> 网络传输数据量大，导致网络通信时间长，
—>解决，将操作脚本sql function 前提在数据库启动时通过store procedure 过程直接传输到数据库中，之后数据传输仅传数据本身和操作标识，减少数据传输量；但缺点就是要对版本和代码进行管理和维护，对开发不友好，

双相锁定机制（Two-Phase Locking / 2PL）

关系型数据库管理并发事务、保证数据一致性的核心理论基础
这个协议的核心，就是把一个事务的生命周期严格划分为两个阶段，对锁的操作有明确的”单向”要求：

• 增长阶段（加锁阶段）：事务在这个阶段可以按需申请新的锁（如读锁S或写锁X），但绝对不能释放任何已经持有的锁。你可以把它理解为事务在”收集资源”。
• 收缩阶段（解锁阶段）：事务一旦开始释放第一个锁，就立即进入这个阶段。此后，它只能释放已有的锁，不能再申请任何新锁。这标志着事务在”释放资源”。

这个”先增长，后收缩”的规定，保证由2PL管理的事务调度结果是冲突可串行化的，即并发执行的结果与某种串行执行的结果等价，从而保证数据一致性

严格两阶段锁（Strict 2PL）

在2PL上增加
事务持有的所有排他锁（写锁），必须等到事务最终提交（COMMIT）或回滚（ROLLBACK）时才能一次性全部释放

优点：
· 防止级联回滚：避免了其他事务读到未提交的脏数据后，又因源事务回滚而被连带撤销。
· 简化故障恢复：系统恢复时只需关注已提交事务的锁状态，逻辑更清晰

谓词锁

是一种逻辑锁，它锁定的不是一个具体的物理数据行，而是一个满足特定搜索条件（谓词）的“逻辑数据集合”
锁住“一类数据”，而不是“一行数据”
它能从根本上解决“幻读（Phantom Read）”问题，是实现最高隔离级别“可串行化（SERIALIZABLE）”的一种重要理论方案。

它的思想是：即使目标数据行现在不存在，也先把满足 age = 25 这个“条件”锁住。这样，当其他事务想插入满足该条件的新行时，就会发现这个条件被锁住了，必须等待，从而避免了幻读

巨大的实现代价：

• 实现极其复杂：判断两个复杂查询条件（如 (age > 20 AND city = ‘Beijing’) 与 (age < 30 AND city = ‘Shanghai’)）是否存在交集，本质上是一个NP-Complete（NP完全）问题，计算开销极大。

• 并发度风险：过于严格的逻辑锁可能会锁住一些事实上并不存在冲突的数据范围，反而降低系统的并发处理能力。

索引范围锁定

索引范围锁（Index Range Lock） 是数据库中一种基于索引结构的锁机制，它的核心作用是锁定一个索引范围内的所有记录及其之间的间隙（Gap），以防止其他事务在该范围内插入、修改或删除数据。

在 MySQL/InnoDB 中，它被具体实现为 Next-Key Lock（临键锁），即 Record Lock（记录锁） + Gap Lock（间隙锁） 的组合。它是在 可重复读（REPEATABLE READ） 隔离级别下，解决幻读问题的核心技术手段。

1. 索引范围锁的构成

2. 一个具体的例子

假设有一张表 users，主键 id 目前有数据：1, 3, 5, 7, 9。

1
2

索引（主键）分布：1, 3, 5, 7, 9
间隙：(-∞,1), (1,3), (3,5), (5,7), (7,9), (9,+∞)

现在执行查询：

1

SELECT * FROM users WHERE id = 5 FOR UPDATE;

在 REPEATABLE READ 隔离级别下：

• 如果 id=5 存在，InnoDB 会加一个 Record Lock，锁定 id=5 这一行，阻止其他事务修改或删除它。
• 但不会锁间隙，因为这是一个精确等值查询且目标存在。

如果执行范围查询：

1

SELECT * FROM users WHERE id BETWEEN 3 AND 7 FOR UPDATE;

InnoDB 会锁定：

这样，整个索引区间 [3, 7] 被彻底锁住，其他事务无法插入任何值在 3~7 之间的新行，也无法修改或删除 3、5、7 这三行，从而保证了当前事务两次查询的结果完全一致——幻读被阻止了。

3. 索引范围锁的触发条件

4. 为什么需要索引范围锁？

5. 索引范围锁的代价与风险

6. 如何减少索引范围锁的影响？

7. 范围锁 vs. 谓词锁

总结

索引范围锁（Next-Key Lock）是 InnoDB 在 REPEATABLE READ 隔离级别下，利用 B+Tree 索引结构，通过“记录锁 + 间隙锁”组合实现的、用于防止幻读的实用锁机制。

• 优点：在索引有序的前提下，能高效锁定范围，保证可重复读语义。
• 代价：牺牲部分并发性能，增加死锁风险。
• 最佳实践：尽量使用主键或唯一索引进行精确查询，减少范围锁定；在能接受幻读的场景下，可考虑降级隔离级别到 READ COMMITTED 以换取更高并发。

可串行化快照隔离 Serializable Snapshot Isolation

是一种既提供最高级别“可串行化”事务隔离保证，又能保持“快照隔离”高性能的并发控制算法
结合了两种隔离级别的优点：像可串行化一样严格保证数据一致性，避免各种并发异常；同时在性能上又非常接近快照隔离，避免了传统悲观锁机制带来的大量阻塞和性能损耗

解决了以下两种方案的问题：
快照隔离 (SI)：性能好，读操作不阻塞写操作。但存在“写倾斜”等并发异常，无法保证完全可串行化，可能产生不可串行的执行结果。
两阶段锁 (2PL)：能保证可串行化，但采用悲观策略。事务在操作前就必须获取锁，容易导致阻塞、死锁，在高并发场景下性能和扩展性较差。

SSI 基于快照隔离，采用了一种更“乐观”的策略：

1. 乐观执行：事务可以基于其快照自由读写，不会因为潜在的冲突而被阻塞，因此读写性能很高。
2. 提交前检测：当事务准备提交时，数据库会检查其执行过程中是否与其他并发事务产生了可能导致非可串行化结果的读写依赖关系（如读-写冲突）。
3. 裁决与重试：如果检测到这类有问题的依赖（例如，形成了一个依赖环），为了保持数据一致性，数据库会中止（Abort）当前事务或相关事务，并通知应用层进行重试。如果未检测到问题，则事务成功提交。

尽管 SSI 很强大，但使用它时仍需注意：

• 必须处理重试：由于可能存在事务被中止的情况，应用程序必须正确捕获序列化失败错误，并重试整个事务。这是使用 SSI 的代价，也是应用需要承担的职责。
• 高冲突下的性能：如果系统并发事务间的数据访问冲突非常严重，会导致大量事务被中止和重试，反而可能因为重试的额外开销而降低系统整体性能。

数据库索引(database index)

索引是一种数据结构，针对特定key, 可以使读取数据加快，但写入速度降低
处理常见数据量过大情况下进行查找问题，避免挨个遍历数据(无论是从前往后还是从后往前找)，提高查找效率O(n)
比如
具体查找，找到key = xxx的 那条数据
范围查找，找到key在[xxx, xxx]之间的数据

MySQL官方对索引定义：索引是MySQL存储引擎用于快速查找记录的一种数据结构。
简单理解为：索引是排好序的数据结构，帮助我们快速的查询数据库表中的数据。
• 索引是一种特殊的数据文件
。 MyISAM存储引擎：数据文件和索引文件是分开的。索引文件中保存的是数据记录的地址。
。InnoDB存储引擎：表数据本身就是按照B+Tree组织的索引结构。ibd文件 就是数据+索引存储文件。
• 索引是一种数据结构
。索引是一个独立的、物理的数据结构，它是由一个表中的一个字段或者多个字段的值组合成的集合。
• MySQL中，默认使用的是B+Tree结构管理索引。

没有索引

查找数据需要从磁盘中一条一条进行对比

二叉查找树索引

使用二叉查找树，构建索引
• 任何一个节点的左子树上的键值，都必须小于当前节点。
• 任何一个节点的右子树上的键值，都必须大于当前节点。
• 每个节点分别保存，字段数据和指向数据记录的物理地址的指针。|

平衡二叉树索引

平衡二叉树通过让树的叶子节点自动旋转和调整，让整课树保持平衡状态。
平衡二叉树的特点：
• 在符合二叉查找树的条件下，还满足任何节点的两个子树的高度差最大为1.

AVL树 在失去平衡之后（两个子树的高度差>1），可以通过旋转使其恢复平衡。

AVL树的优点
•叶子节点的层级相对减少了。
• 形态上能够保持平衡
• 查找效率提升，大量的顺序插入操作也不会导致查询性能下降
AVL树的缺点：
• 一个节点最多分裂出两个子节点，树的高度太高，导致IO次数过多
• 节点里面只保存一个关键字，每次操作获取的目标数据太少

哈希索引(hash index)

通过哈希函数对某一特定key值进行计算生成hash map key值，将数据存储或者内存地址存在对应的hash map key值对应的bucket中
这样可以实现O(1)时间复杂度的查找效率，对于读写都是
hash map key值唯一且有限，比如从1-100000000
对于哈希函数计算结果相同的情况，可以在bucket中通过链表进行存储或者在hash map 中继续查找有无空位填入

• Hash索引底层是由Hash表实现的，是根据键值<key，value>存储数据。
• Hash索引非常适合 根据Key查找value值，也就是单个key的查询或者说等值查询。

个人理解： 类似将数据根据关键key通过哈希函数强制进行归类，这样在查找的时候，进行同样的哈希计算，直接过滤掉哈希值不同的情况，从而降低挨个查找的工作量

小结

Hash索引的优点：
•O(1) reads and writes
•索引结构是比较紧凑的，因为只存储对应的hash值，如果只是做等值查询，不包含排序或者范围查询的需求，可以选择Hash索引。
•在没有哈希冲突的情况下，等值查询访问hash索引的速度是比较快的，理论上比B+Tree快

Hash索引的缺点：
•索引结构是比较紧凑的，因为只存储对应的hash值，如果只是做等值查询，不包含排序或者范围查询的需求，可以选择Hash
•在没有哈希冲突的情况下，等值查询访问hash索引的速度是比较快的，理论上比B+Tree快。
•哈希索引只包含哈希值和行指针，不存储字段值，不能使用索引中的值来避免读取行。
•哈希索引只支持等值比较查询。不支持任何范围查询。
•哈希索引的数据并不是按照索引值顺序存储的，所以就无法用于排序。
•在遇到大量的Hash值相等的情况时，性能下降明显。

缺点简记：
由于hash function 会将数据均匀分布到不同的bucket中，这使得在存储磁盘中存储地址不连续，导致连续查找性能较差，只能存储在内存中
存内存的话RAM 访问性能高，但是expensive, size less, key 也要适配ram 的存储方式
不能范围查找，O(n), 需要遍历所有数据才能找到符合条件的数据
不能排序，同上

应用场景
• 在MYSQL中 哈希索引主要应用于内存表，也就是Memory引擎。
• InnoDB引擎不支持 对表中字段创建Hash索引

预写日志

主要用于解决数据丢失问题，在对数据进行操作的每一步都将操作日志记录下来存在磁盘中，当数据丢失时，可以通过根据之前的日志进行操作
从而来重建索引和数据
这个方案也适用于其他类型索引情况
对于哈希索引，需要先写入日志，再更改哈希索引

B-tree索引

B-tree结构和存储


B-Tree的查找操作

1. B-Tree的每个节点的元素，可以视为一次I/O的读取，树的高度就表示了最多的IO次数。
2. 在相同数量的总元素的个数下，每个节点的元素个数越多，高度越低，查询需要的IO次数就越少。
   

• B-Tree的优点
。 B树可以在内部节点存储键值和相关的记录数据，因此把频繁访问的数据放在靠近根节点的位置，可以提高热点数据的查询效率。
• B-Tree的缺点
。 B-Tree中每个节点不仅包含数据的key值，还有data数据。当data数据较大的时候，会导致每个节点存储的key值減少了，就会导致B树的层数变高。增加查询的1/O次数。
• 使用场景：B树的使用场景主要应用于文件系统以及部分数据库索引，比如MongoDB，大部分的关系型数据库索引是使用的 B+树实现

B+tree索引

B-Tree存在的问题
•在B树中，每个节点都会存储数据，如果每个节点存储的都是行数据，那么占用的内存就会大大的增加，树的高度也会变高，就会增加I/O操作的次数。
•B树适用于随机访问，但是范围查询是不适合的，因为范围查询通常需要顺序访问一系列的键值，不是随机访问。由于B树的结构的特点，无法有效的执行范围查询

B+Tree 在B-Tree基础之上做了一些优化。B+Tree更加适合实现存储索引结构。InnoDB引擎就是通过B+Tree实现其索引结构的。

【解决读多写少的问题】

一颗m阶的B+树要满足下列要求：

1. 每个分支节点至多有m颗子树。
2. 根节点或者没有子树，或者至少有两棵子树。
3. 除了根节点以外，其他每个分支节点至少要有【m/2】 棵子树。
4. 有n棵子树的节点，恰好有n个关键字。子树的个数与该节点的关键字的个数相同。（b-tree 有n-1 个关键字）
5. 所有的叶子节点包含全部的关键字，以及指向相应记录的指针，而且叶子节点的关键字，自小到大顺序链接。并且所有的叶子节点链接到了一起。
6. 所有的分支节点中，仅包含它的各个子节点中最大的关键字以及指向子节点的指针。
7. B+树中，只有叶子节点保存数据，其他节点仅仅是索引，没有任何的数据关联

B+Tree结构存储索引的特点
从MySQL数据页的角度看B+Tree：
• MySQL的InnoDB存储引擎中，最小存储单元就是页（每页默认大小是16KB）
•MySQL的设计者将一颗B+Tree的节点的大小 就设置为了等于一个页（16KB），这样做的目的是为了每个节点只需要一次IO就能够完整的载入一页数据。

MySQL B+Tree存储索引的特点

1. MySQL的B+Tree 分支节点的数据页，存放的是”关键字＋指针”。
2. 叶子节点的数据页，存放的”关键字＋全部数据”，这里单指聚簇索引来说。
3. B+Tree的根节点是保存在内存中的，子节点存储在磁盘上的。
4. 所有的节点按照索引键大小排序，构成一个双向链表，便于范围查询。

B+Tree的查找操作
两种查找方式：
1.跟B-Tree一样，通过指针实现随机查找，从根节点开始。
2.根据叶子节点进行顺序查找，在一个节点的内部可以实现折半查找，在多个节点之间，因为是通过指针连接的，所以要使用顺序查找。

单个元素的查询：

范围查询：IO次数更少，查询简便。

B+Tree的优势
对于B-Tree，B+Tree具有以下优势：

1. B+Tree的中间节点是没有数据，所以同样大小的磁盘页，B+Tree可以容纳更多的节点元素（保存更多的索引），在数据量相同的情况下，B+Tree比B-Tree会更加的矮胖，因此查询时lO次数也就更少。
2. B+Tree的查询效率是更加稳定的，B+Tree在查询时必须要找到叶子节点，而B-Tree只需要找到匹配的元素就可以了。因此B-Tree的查找性能是不稳定的，最好的情况是只查根节点，最坏的情况是找到叶子节点，而B+Tree的查找每次都是稳定的。
3. B+Tree扫库和扫表的能力更强，如果我们需要根据索引进行数据表的扫描，对B-Tree 需要将整棵树遍历一遍，而B+Tree只需要遍历所有的叶子结点即可 +子节点之间有指针连接）。
4. B+Tree排序能力更强，在上面范围查询的例子中，B+Tree天然具有排序的功能

一棵B+Tree可以存放多少数据？

MySQL中将B+Tree的节点的大小，设置为等于一个页（16KB）•

上面是一棵高度为2的B+Tree，存在一个根节点和 若干的叶子节点，那么这棵B+Tree的能够存放的总记录数为：

根节点指针数*单个叶子节点的记录数

计算步骤：

1. 计算根节点的指针数：假设表的主键是int类型，占用4个字节，指针大小为6个字节。一个页大概可以存储：
   16384/（4b+ 6b）=1638，一个节点最多存储1638个索引指针。
2. 计算每个叶子节点存储的记录数：假设一行记录的大小为1KB，那么一页就可以存储16行数据，16KB/ 1KB=16。
3. 高度为2的B+Tree可以存放的记录数为：16384 x 16=26208条 数据记录，以此可以推算出高度为3的B+Tree可以存放的记
   录数为：1638 x 1638 x 16=4千万条数据。
   InnoDB中的B+Tree高度一般为1~3层，就可以满足干万级别的数据存储

LSM-TREE索引 + SSTable

lsm-tree： log structure merge tree ，日志结构合并树
解决写多读少 (利用顺序I/O)的问题，磁盘利用率比B+Tree（一般为50%左右）高(利用compaction)
sstable： Sorted String Table ，排序字符串表

一些前置相关知识

后端开发常见层式结构：时间轮、跳表、LSM-Tree

1. 海量并发的定时任务组织：时间轮
2. 高并发读写的有序结构组织：跳表
3. 空间利用率以及写性能高的磁盘数据组织：LSM-Tree
   时间轮：linux内核、skynet、kafka、netty
   跳表：redis.lucene（倒排索引）、rocksdb
   Ism-tree: leveldb rocksdb分布式关系型数据库 tidb （mysql） cockroachdlb （pg）

时间轮

适用于在多线程环境执行海量并发定时任务

通过指定

• 最小时间精度(最低层级执行时间误差),min_time_precision，
• 最大时间范围(整个时间轮的时间范围，周期时长，超出会出错)max_time_range，
• 层级（决定任务映射次数和每次映射执行时间）level，
  定制时间轮框架

当时间轮运行时，最低层级时间指针minlevelPointer每移动一次（经过min_time_precision时间）
就会执行当前时刻上存储的多个任务，当minlevelPointer移动到的当前层级最后一个时间格子的时候
执行完当前定时任务，就会从更高一层级的当前level(+1)Pointer指定的时间格子获取定时任务
给到当前最低层级的每个时间格子(根据具体任务过期时间与当前时间间隔计算该放在哪个时间格子里)
然后等待时间执行
当更高一层级的level(+1)Pointer移动到的当前层级最后一个时间格子的时候
就会从更高两层级的当前level(+2)Pointer指定的时间格子获取定时任务
给到当前level+1层级的每个时间格子，等待时间执行
以此类推

好处：
只用关心最低层级时间周期minlevelPeriod内的任务，即最近minlevelPeriod 时间范围内的任务
不用过早遍历轮询高层层级时间范围里的任务是否要执行
用空间换时间，把大量不同到期时间的任务映射到有限的时间槽（bucket）中，避免对海量任务进行全局排序或反复扫描。

区分任务队列

优先队列：

“我必须知道谁最早到期。”
↓
排序
↓
找最早任务

时间轮：
“我不关心谁最早。”
↓
按照时间分桶
↓
当前时间到了哪个 bucket
↓
处理 bucket

优先队列解决的是“有序性”，时间轮解决的是“时间范围内的快速定位”。

跳表

多层级有序链表
数据的增删改查都要先进行节点的查询，所有数据存在最后一层

数据查找时层级从上到下依次查找，直到找到匹配的节点或者到达最底层
比如现在要插入17，
第一层找到6再往后找是null, 就往下移动一层接着从6开始找
6-25 17小于25，下移
6-9 17大于9，从9开始找
9-25 17小于25，下移
9-12 17大于12，从12开始找
12-19 17小于19，但已经是最后一层
17应该插入在12和19之间，指针层级随机生成，因为如果始终保持理想多层有序链表，那么每次插入删除都要重新生成层级结构，计算开销大

redis 有序集合 为什么使用跳表 而不是红黑树？

红黑树相比跳表本身没有空间浪费，且时间复杂度更稳定，但它本质是平衡二叉树，由于回溯过程复杂，并不能直接用来进行范围查找
B+树相比跳表都是最后一层包含所有数据，但是查找时间复杂度跟树高度有关，比调表更高，二者适用场景不同，
跳表适用于组织内存数据
B+树适用于组织磁盘数据

布隆过滤器

详见

顺序I/O

顺序IO
• 磁盘访问时间：寻道时间 + 旋转时间 + 传输时间； 大概10ms
。寻道时间：8ms~12ms；
。旋转时间：7200转/min（半周 4ms）；
。传输时间：50M/s（约0.3ms）；
• 磁盘随机 IO ＜ 磁盘顺序IO~内存随机 IO ＜内存顺序IO(大概10ns)；

内存访问速度几乎是磁盘的100w倍

Lsm-tree

【解决写多读少的问题】
B+TREE 添加数据分裂时，空间利用率要占50%，随机IO，写入慢
lsm-tree 为基础的存储引擎空间利用率可以实现30%，顺序IO，写入快，每次追加日志写入
lsm-tree 不是数据结构，而是数据存储组织的一种方式

整体运行流程就是当用户写入时，先对磁盘日志进行预写入，然后将数据暂存在内存里面的Memtable（跳表）中
当Memtable 快要写满时，将数据固定下来变成静态数据，存到内存 Immutable Memtable （跳表） 中，不再支持写入，只读，等待刷进磁盘中
数据进入磁盘中以level0 ssTable 形式存在，有序结构，多个ssTable 中 多个操作可能针对同一个key 即同一条数据多次操作，造成数据处理重复
这时可以进行level 0层ssTable数据 compaction,减少数据冗余，形成 level 1 层数据，
level 1 层数据合并压缩排序再生成level 2 以此类推直到level n ， n 一般为7
这样到level n 基本可以保证该层没有重复的数据，几乎占整体数据的 90%
同时对于经常操作的数据使用在整个架构的上方，现在内存中，然后在level 0 中
这样对于读取来说，热key 甚至可以直接在内存中读到
另外对于不同层的数据读取，可以引入布隆过滤器判断数据是否存在，误差可控

稀疏索引

稀疏索引不是给“列”分类，而是给“索引项”分类：索引不为每一条数据都建立索引项，只为部分数据建立索引项。
稀疏索引（Sparse Index） 是一种索引结构，它不为表中的每一行数据都建立索引项，而是为部分数据块（如数据页）或特定间隔的数据建立索引条目。

你可以把它理解为书的目录页——目录只列出每个章节的起始页码，而不是每个字、每句话出现的页码。

1. 稀疏索引 vs. 稠密索引

2. 稀疏索引在数据库中的典型应用

（1）InnoDB 的聚簇索引（主键索引）

InnoDB 的聚簇索引本质上是一个稀疏索引 + 稠密索引的混合体：

• B+Tree 的内部节点：只存储每个子页的最小键值作为索引条目 → 这是稀疏索引。
• B+Tree 的叶子节点：存储了完整的行数据，且所有数据都在这层 → 叶子节点内部是稠密的（所有行都存在）。

当你通过主键查找时，过程是：

1. 从根节点开始，利用稀疏的索引条目（每个节点的最小键值）快速定位到目标数据页。
2. 到达叶子节点（数据页）后，在页内进行顺序扫描或二分查找找到具体行。

（2）文件系统中的目录索引

操作系统中的文件目录也是一种稀疏索引：

• 每个目录项只记录文件名和对应的数据块起始地址。
• 查找文件时，先在目录中定位到文件元数据，再根据元数据去数据区读取具体内容。

3. 为什么需要稀疏索引？

✅ 优点

❌ 缺点

4. 稀疏索引 vs. 覆盖索引

5. 实际例子

场景：用户表 users，主键 id 自增列

1
2
3

数据页1：id 1~100
数据页2：id 101~200
数据页3：id 201~300

稀疏索引（聚簇索引内部）：

• 查询 SELECT * FROM users WHERE id = 150：

  1. 在稀疏索引中找到 101（最大的 ≤ 150），定位到数据页2。
  2. 在数据页2内顺序扫描，找到 id=150 的行。

• 查询 SELECT * FROM users WHERE id BETWEEN 50 AND 250：

  1. 稀疏索引定位到起始页（数据页1）。
  2. 顺序扫描所有数据页（1、2、3），直到超出范围。

6. 什么时候该考虑稀疏索引？

总结

• 稀疏索引是一种用空间换时间的反向策略——它牺牲了一点查询精度（需要额外扫描），换来了更小的索引体积和更低的维护成本。
• 在数据库中，聚簇索引（主键）天然就是稀疏索引，而辅助索引（二级索引）通常是稠密索引。
• 如果你在建表时选择了自增主键，实际上已经在享受稀疏索引带来的好处了——主键索引的 B+Tree 内部节点就是稀疏的，大大减少了内存占用和 I/O 次数。

聚簇索引

索引的分类：
1.按照字段的特性分类：主键索引、普通索引、前缀索引。
2.按照数据结构分类：B+Tree索引、Hash索引。
3.按照物理存储方式分类：聚簇索引、辅助索引（二级索引）。

聚簇索引的定义
聚簇索引并不是一种单独的索引类型，聚簇索引是一种数据存储的方式。
例如InnoDB的聚簇索引使用的数据结构就是B+Tree存储索引和数据。

• ‘聚簇”含义：表示数据行和相邻的键值是紧凑的存储在一起。
• 辅助索引（二级索引）：叶子节点是不会保存引用行的物理地址的，而是保存行的主键值

聚簇索引 VS 非聚簇索引

• 聚簇索引（主键索引）：将数据存储与索引放到了一起，索引结构的叶子节点保存了具体的行数据。
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点存储的是指向数据行对应的地址。
从存储引擎的角度去看，聚簇索引和非聚簇索引的区别：

• InnoDB存储引擎【聚簇索引】
。在InnoDB存储引擎中，默认使用B+Tree存储索引和数据，InnoDB中利用主键创建的索引，就是聚簇索引。
。聚簇索引的二级索引：叶子节点是不会保存引用行的物理地址的，而是保存行的主键值
。对于聚簇索引，数据的物理存放顺序与索引顺序是一致的（主键必须是自增id）。

•MyISAM存储引擎【非聚簇索引】
。在MyISAM存储引擎中，默认也是B+Tree索引，但是主键索引和辅助索引都是非聚簇索引。
。非聚簇索引不管是主键索引还是二级索引，其索引结构的叶子节点保存的都是一个指向对应行记录的物理地址。
。非聚簇索引中辅助索引的检索无需访问主键索引。
。非聚簇索引的存储引擎，表数据存储与索引顺序是无关

• InnoDB聚簇索引就是按照主键索引的顺序构建B+Tree。B+Tree的叶子节点就是行记录，行记录和主键值紧凑的存储在一起
的。
•InnoDB中主键索引就是数据表本身。主键索引中存放了整张表的数据。
InnoDB表中 要求必须要有聚簇索引：
。如果表定义了主键，主键索引就是聚簇索引。
。 如果表没有定义主键，则第一个非空unique列 作力聚簇索引。
• 否则以上都没有，InnoDB会重建一个隐藏的row-id 作为聚簇索引

回表

回表是数据库执行查询时的一种操作过程，简单来说就是：先通过辅助索引找到主键值，再根据主键值到主索引（聚簇索引）中取回整行数据。
这个动作之所以叫“回表”，是因为数据读取路径走了两步（索引 -> 主键 -> 数据行），相当于“返回”到主表（聚簇索引）中去查找。

如何解决回表问题？答：使用覆盖索引。（覆盖索引： 所查询信息在当前索引值中能找到，“索引和查询之间的关系”，而不是一种索引结构。）
• 如果一个索引包含了所要查询的所有的字段值（不需要回表），这个索引就是覆盖索引。
如何实现覆盖索引？
•将被查询的字段建立联合索引，这样就可以直接返回索引中的数据了，避免了去聚簇索引中去定位行记录。

索引下推

用于查询优化。可以在索引遍历的过程中，对索引中包含的字段先做判断，不符合条件的记录过滤，作用就是减少回表的次数
将存储引擎层（Engine）的过滤条件下推到索引遍历的过程中，提前过滤掉不符合条件的记录，从而减少回表次数

比如下面在辅助索引中添加age 信息后，同时满足age 要求才会去主键索引中获取完整行数据

在不使用 ICP 时，存储引擎会先通过索引把所有满足“索引键范围”的记录全部回表，然后在 Server 层再过滤；使用 ICP 后，存储引擎在索引遍历时就直接把不满足条件的记录过滤掉，只有满足条件的才回表。

数据库主键类型选择

自增的优点
•字段长度比UUID小。
•在写的方面，由于是自增，新增的数据永远是在后面，有序，这点对性能有很大的提升。
•数据库自动编号，速度快，增量增长按顺序存放，检索比较快。
•数字型，占用的空间小，容易排序。
自增的缺点
•由于自增，比较容易通过网络爬虫获取当前系统的业务量。
•高并发场景下，竞争自增锁会降低数据库吞吐能力。
•数据迁移或者是分库分表场景下，自增方式不再适合

自增主键的本质是单库单表内的局部唯一，它依赖单个数据库实例的全局计数器(由 MySQL 的 AUTO_INCREMENT 机制维护)。
一旦数据被拆分到多个库/表，或需要合并多个数据源，这个”局部唯一”就失效了，会导致主键冲突、无法全局排序、迁移困难等问题。
因此分库分表和迁移场景下，需要改用全局唯一 ID 生成方案（如雪花算法）

自增ID的内部结构：
新增的行一定会在原有最大数据行的下一行，MySQL的寻址定位很快，不会为计算新行的位置而作出额外的消耗，较少了页的分裂。

UUID的优点：
•主键在任何时候都不会冲突。进行分库分表，还是做合并存储的时候，都能保证主键全局的唯一性。
•可以在应用层生成，提高数据库的吞吐能力。
UUID的缺点：
•与自增相比，最大的缺陷就是随机IO。
•字符串类型要比整数类型更加消耗空间，而且要比整数类型操作慢

UUID的内部结构：
如果是uuidInnoDB无法做到总是把新插入的行放到索引的最后，每次都需要为新的行寻找合适的位置，分配新的空间。
还包括一些问题：
• 产生大量的随机IO
• 页分裂操作频繁

页分裂：
页分裂（Page Split） 是数据库（如 MySQL InnoDB）或操作系统文件系统中，当数据页（Page）已满，却需要插入新数据时，系统被迫将当前页拆分成两个页的过程。
简单来说：一个数据页装不下了，必须分一分为二，腾出空间给新数据。

总结：
如果是使用InnoDB应该尽可能的选择主键自增顺序插入。
但是如果是在分库分表场景下，分布式主键ID的生成方案优先选择：雪花算法生成全局的唯一ID。

雪花算法：
一种开源分布式 ID 生成算法，在分布式系统中生成全局唯一且有序的 ID，非常适合作为分库分表后的主键 ID 或全局订单号
它生成的 ID 是一个 64 位的长整型（Long）数字，由以下部分组成：

• 1位符号位：固定为 0，保证 ID 是正数。
• 41位时间戳：记录毫秒级时间，可以使用约 69 年，让 ID 整体趋势递增。
• 10位机器标识：通常拆分为 5 位数据中心 ID 和 5 位机器 ID，最多支持 1024 个节点，保证不同机器生成的 ID 不重复。
• 12位序列号：同一毫秒内可生成 4096 个不同的 ID，应对高并发场景。

小结

聚簇索引的优点：
1.可以将相关的数据保存在一起。
2.数据访问更快。
3.使用覆盖索引进行扫描查询时，可以直接使用叶节点中的主键值。
4.辅助索引使用的是主键作为指针，不是使用地址值。
聚簇索引的缺点：

1. 随机主键会导致页分裂问题（主键生成选择UUID的情况）
2. 使用辅助索引查询时，需要回表

多维索引（multi dimensional index）

一种为了高效处理基于多个属性（维度）进行查询而设计的数据库索引结构，是处理多条件、空间、向量等复杂查询需求的利器。无论是用树结构的R树、KD树，还是搜索引擎风味的倒排索引，或是OLAP的数据立方体，它们的目标都一样：在海量数据中，让多维度、组合式的查询变得更快。

相关实现数据结构：
R-Tree
R*-Tree
KD-Tree
QuadTree
GiST / SP-GiST

用例子加深理解

假设有一张地图数据表 places，有 x（经度）、y（纬度）、name 三个字段。

1. 联合索引：INDEX idx_xy (x, y)

• 它的排序规则是：先按 x 排，x 相同再按 y 排。
• 查询 WHERE x = 1 AND y = 2：能高效利用索引（先定位 x=1，再在结果中找 y=2）。✅
• 查询 WHERE y = 2 AND x = 1：优化器通常能调整顺序，也能用上。✅
• 查询 WHERE y = 2：由于 y 不是联合索引的最左列，这个索引基本失效，无法用于减少扫描范围。❌
• 这是典型的一维有序列表（先排第一列，再排第二列）。

2. 覆盖索引：INDEX idx_xy_cover (x, y, name)

• 当我们执行 SELECT x, y, name FROM places WHERE x = 1 时，查询所需的所有列 (x, y, name) 都包含在这个联合索引中。
• 因此，数据库直接读取索引页就能返回结果，完全不用去读原始数据行（主键聚簇索引），省去了昂贵的回表操作。这就是“覆盖”的含义。

3. 多维索引（如 R-Tree）：SPATIAL INDEX idx_xy_mdi (x, y)

• 它将 (x, y) 视为二维平面上的一个点，所有点构成一个空间。
• 查询 WHERE x BETWEEN 0 AND 10 AND y BETWEEN 20 AND 30：这是个典型的二维矩形范围查询。多维索引能直接在这个平面空间里进行搜索，同时利用 x 和 y 两个维度的信息来剪枝，效率很高。
• 而联合索引面对这个查询，通常只能利用 x 的范围（0-10）来定位，然后对命中的每一行再检查 y 是否在 20-30 之间，无法同时利用两个维度的范围信息，效率相对较低。

一句话总结核心区别

• 联合索引：解决多条件排序和查找的问题，但受“最左匹配”约束。
• 覆盖索引：解决减少数据读取的问题，是一种避免回表的优化技巧。
• 多维索引：解决多维度同时过滤的问题，无顺序限制，专为空间、向量等复杂场景设计。

所以，在实际业务中，它们经常是组合使用的。比如，创建一个联合索引，恰好覆盖了查询需要的所有列，那这个联合索引同时也是一个覆盖索引。而当你遇到地理围栏、图像特征匹配等场景时，才需要考虑引入多维索引这种更专业的结构。

为什么要有系统设计

当我们作为用户去访问社交媒体或者互联网数据时，这些数据肯定是需要已存在一个地方，我们才能访问到
假如数据存放在一台计算机内存上面，计算机的存储一般分为ram 和硬盘，ram 读取速度快，但是只能临时存储
硬盘读取速度慢，但可以永久存储，虽然也会面临断电导致存储异常问题
在这种情况下，我们更倾向于使用硬盘存储
假如现在使用一台计算机上的硬盘存储，一个用户只访问这一台计算机是理想的
但成千上万的用户同时访问这一台计算机
就会面临访问性能问题
为了解决访问性能问题，可以选择通过提供多台服务器来分担访问压力
但是数据是在一台服务上面的，如果用户访问了其他没有存储数据的服务器，就不会访问到数据
即数据不能共享
这样为进一步解决数据共享的问题，引入数据库单纯处理数据存储的问题，所有服务器都通过数据库来访问数据
这时数据库就要承担起数据能够快速访问(读写)，更高可靠性的责任
比如断电时如何备份数据
实际情况是一个数据库还不能承担所有的数据存储任务，往往还会同时存在多个数据库,
当一个数据库数据量足够大时，如何进行分片存储