MySQL中的B+ Tree

在MySQL的InnoDB引擎创建的表，使用的B+ Tree的结构存储的数据，并对B+ Tree进行了优化。

B+ Tree只有单项指针， MySQL在这个基础上做了双向指针，这是支持范围查找的关键。

为什么B+ Tree查询快？

MySQL默认B+ Tree的节点大小为16KB。

以主键索引举例，主键类型为bigint，默认占用8字节。

当做数据的key存储在非叶子节点中，因为要找到叶子节点中的数据，所以key需要自己下层索引的磁盘地址，这个占用6字节。

那么一个节点最大索引（key）数量为16KB/(8+6)=1170

假设表中一行的数据量大小是1KB，那么一个h3（高度3）的B+Tree，可以存储1170*1170*16=2KW+的数据。

因为高度为3，所以2KW数据最终只执行了3次磁盘IO交互就完成了查找。

在高版本的MySQL中，在MySQL启动时，会将非叶子节点放到内存中，这样通过一次磁盘交互就可以找到数据，所以查询很快。

索引的存储

索引会占用磁盘空间，因为所有的索引都是一个采用B+ Tree结构的数据文件，存储在磁盘中。

区别在于主键索引（聚簇索引）在B+ Tree的叶子节点上有数据。

其他索引（非聚簇索引）的叶子节点上存储的是主键。

当使用其他索引会先找到叶子节点上的主键，然后通过主键索引找到数据。

MySQL单表为什么最大两千万？

2000W其实是一个推荐值。

从为什么B+ Tree查询快中了解到一个h3的B+Tree，在数据都是1KB大小时会有这样一个公式1170*1170*16=2KW+，如果每条数据只有180B，那么1170*1170*91=12456W+，就会达到1E的数据量在一个h3的B+Tree，查询最多也只需要3次IO。

主键类型的选择

主键类型选择int，bigint与varchar。

bigint(推荐)：在B+ Tree结构的遍历查找中，会进行大小比较，整型比较大小快，支持的数据量更大。

int：与bigint优势大体相同，但是支持的数据量不够大，并且使用一些工具方法生成ID是位数也不够。

varchar（不推荐）：在B+ Tree结构的遍历查找中，比较大小会进行转hash码逐位比较，性能比整型差，占用空间也大。

主键属性可以选择自增，UUID，雪花等。

自增：在B+ Tree结构中是十分友好的增加数据方式，只会往树的右边插入数据，很少会出现触发树平衡的情况。

如果需要分表分库，也会有自增ID重复的情况。

同时也存在一定程度上的业务问题，比如用户知道ID为自增后，通过循环调用接口的方法获取所有数据。

UUID（不推荐）：UUID的值是随机的，可能会往B+ Tree的左边插入数据，如果左边节点中的key满了，那么会导致整个树进行分裂来重新平衡，这很消耗性能，而UUID可能会平凡触发这个操作。

雪花等其它方法（推荐）：以雪花算法为例，它是趋势递增，新的主键小于之前的概率很小，不会频繁触发树平衡机制。

Explain执行计划详解

id属性：

  • id的顺序代表出现SELECT关键字的顺序

  • 如果被加载的表文件的ID属性内容是相同的，则根据自上而下的顺序进行加载的.

  • 如果被加载的表文件的ID属性内容是不相同的。则ID较大的表，就先被加载。 ID较小的表，则后被加载.

  • 如果被夹在的表文件的ID属性内容既有相同有存在不同时。则ID较大的表，就先被加载。ID相同的表则按照自上而下顺序进行加载。

  • 没有id的查询代表是中间结果,采用Memory类型临时表处理，比如union关键字就会产生，尽量不要产生临时表。

select_type属性：

  • Simple：简单查询，不包含子查询，不包含联合查询，不包含连接查询

  • Primary：复杂查询中最外层的 select,看到primary通常代表一个嵌套查询

  • derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

  • union：在 union 中的第二个和随后的 select

  • union result：从 union 去重临时表检索结果的 select

  • subquery：包含在 select 中的子查询（不在 from 子句中），并且这个子查询再执行时不需要得到外部查询的帮助

  • dependent subquery：表示当前查询语句是一个子查询，并且执行条件依赖与外部查询提供的条件

table属性：

这一列表示 explain 的一行正在访问哪个表。

partitions属性：

说明查询作用在哪个分区表上，基本不用这个。

type属性(重要)：

表示当前查询语句是否用到索引，索引的使用方式，当前查询语句的评价级别

以下按照最差级别到最好级别的顺序排列

  1. all：全表扫遍，没有任何索引可以使用时，这是最差的情况，应该避免。

  2. index：和ALL一样，不同就是mysql只需扫描索引树，无需回表，这通常比ALL快一些。

  3. range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。如果定位的数据行操作了表中总行数的30% 以上，就会导致mysql放弃使用索引定位。（开发中至少要达到这个级别）

  4. ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

  5. eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。

  6. const：定位数据行，使用到索引，采用主键索引来进行直等的方式（where id = 3）

  7. system：操作表只有一行数据，并且数据内容是固定不变的。（实际开发中不存在）

  8. null：无需访问表或者索引，比如获取一个索引列的最大值或最小值

范围查询一般到range即可。

用户查询用，一般不会使用主键字段如库表中的ID来进行查询，而是非主键字段，所以达到ref即可，而不是const级别

 

possible_keys属性：

这一列显示查询可能使用哪些索引来查找。

 

key属性：

表示当前查询语句执行时真实使用到的索引，如果这个字段为null，则有两种可能，一是当前表中没有索引，二是当前表中有索引但是失效了。

possible_keys显示可能使用的索引有多个，mysql会选择性能最高的索引。

key_len属性：

如果本次查询使用了索引，则key_len内容不为空，在多字段索引中，这里的数字表示当前采用索引字段的数据长度，这个值越大，定位数据更加精准。

ref属性：

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

rows属性：

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

filtered属性：

是一个百分比的值,代表 (rows * filtered) / 100 ,这个结果将于前表产生交互。

Extra属性(重要)：

这里提供了很多信息，在创建索引优化时，可以进行有效的参考

性能好：

  • Using index：表示MySQL正在使用覆盖索引（covering index）来满足查询，这意味着MySQL可以仅通过索引而不必扫描表来获取所需的数据。

  • Using index condition: 表示MySQL正在使用索引来执行WHERE子句中的条件过滤。（索引下推ICP）

  • MRR：Multi-Range Read：多块顺序读优化。

  • Using join buffer: 表示MySQL正在使用连接缓冲区来处理查询。连接缓冲区是一种内存结构，用于存储连接操作的中间结果，以便优化查询性能。

性能差：

  • Distinct: 表示MySQL正在执行DISTINCT操作以过滤结果集中的重复记录，建议入库前做唯一校验，而不是查询时Distinct

  • Using temporary: 表示MySQL需要创建一个临时表来处理查询。通常是由于涉及到排序（ORDER BY）和分组（GROUP BY）操作而导致的。

建议按排序字段顺序建索引或加内存，确保临时表能够装载到内存。

  • Using filesort: 表示MySQL正在对结果集进行排序操作，但无法使用索引来完成。这意味着MySQL必须在磁盘上创建一个临时文件来完成排序。

建议按排序字段顺序建索引或加内存，确保临时表能够装载到内存。

  • Using where: 表示MySQL正在使用WHERE子句来限制返回的结果集，但是无法使用索引来加速查询。

建议加索引或利用ICP机制达成Using index condition。

索引下推ICP（MySQL 5.6）

在MariaDB 5.3.3（MySQL是在5.6）开始引入的一种叫做Index Condition Pushdown（索引下推，以下简称ICP）的查询优化方式，正常是默认开启的。

对于 InnoDB 表，ICP 仅适用于辅助索引。ICP 的目标是减少完整行读取的次数，从而减少 I/O 操作。对于 InnoDB 聚簇索引，完整的记录已经被读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。

ICP开启/关闭情况对比

假设有一张用户表有id，name，age三个字段。

# 创建索引
ALTER TABLE user ADD INDEX age_name (age, name);
# 需要执行的SQL语句
SELECT * FROM user WHERE age = 18 AND name LIKE '%张%';

因为 LIKE '%张%' 会导致索引失效，所以 age_name 这个索引只能命中前半段。

在关闭 ICP 的情况下，会先从age_name 索引的B+Tree查询到 age = 18 的数据，然后去主键索引的B+Tree树中获取完整数据，再进行WHERE name LIKE '%张%' 的筛选工作。

再开启ICP的情况下，从 age_name 索引拿数据不变，但是因为age_name这个B+Tree的非叶子节点，就包含了name的值，所以判断完age=18后，直接在这些索引上筛选 name LIKE '%张%' 的数值，之后才去主键索引的B+Tree树中获取对应的数据。

也就是说 where name LIKE '%张%' 筛选掉的数据越多，ICP的加速效果也就越好。

注意事项

  • 有几个关于ICP的事情要注意：

  • ICP只能用于二级索引，不能用于主索引。

  • 也不是全部where条件都可以用ICP筛选，如果某where条件的字段不在索引中，当然还是要读取整条记录做筛选，在这种情况下，仍然要到server端做where筛选。

  • ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

  • ICP 不支持在虚拟生成列上创建的辅助索引。InnoDB 支持对虚拟生成列创建辅助索引。

  • 无法下推涉及子查询的条件。

  • 无法下推涉及存储函数的条件。存储引擎无法调用存储函数。

  • 无法下推触发器中的条件。

使用了索引依旧缓慢

真正确定执行效率的是扫描行数与回表次数。

假设数据库有100W数据，创建了联合索引后，某个SQL利用索引还是需要扫描60W行数据，然后再回表，这样的效率依旧缓慢。

所以在explain中的减少 row 行数也同样重要。

sql中慢查询阈值为long_query_time=10s 当sql执行后大于10s就会被记录到慢sql日志 一般建议缩小到1s。

降序索引（MySQL 8）

在MySQL 8 之前所有索引都是按升序创建的。当Order By多字段都是desc或asc还可以通过优化器能够向后扫描升序索引 (给出降序）。

但是如果Order By多字段一个是 desc 一个是 asc ，在explain 解析的 Extra 中必然会出现 Using filesort，这时一种效率极差的情况。

因为索引的B+Tree上都是升序排序，Order By多字段都是升序可以正常使用，都是降序也可以向后扫描升序索引 (给出降序)，但一个升一个降就无法利用索引了。

在MySQL 8之后增加了降序索引

# 创建索引可以指定age和create_time的升序降序
ALTER TABLE user ADD INDEX name_age_create_time(name,age asc, create_time desc);

原理也很简单，在B+Tree的非叶子节点就是倒序创建的。

MySQL多表关联执行计划（NLJ）

NLJ嵌套循环连接

MRR与BKA

MRR（Multi-Range Read）

假设一张表有 a 字段的索引与主键id索引。

# 执行下面的语句在B+Tree上会发生什么？
select * from t1 where a>=1 and a<=100;

索引a与索引id都是升序排列，首先从索引a上筛选1-100的数据，但是a = 1的数据，他的id有可能不等于1，可能等于999。

那么从索引a上获取的1-100的数据他们对应的 id 值是乱序的，那么每次只能根据一个主键 id 查到一行数据。因此，回表肯定是一行行的。

在索引 a 上是范围查找，在索引 id 上是随机访问，性能差在这里随机访问上。

Multi-Range Read 优化的重点也就是随机访问，虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为主键的生成器一般都是有序的，所以按照主键索引的顺序查找，对磁盘的读比较接近顺序读，能够提升读性能。

这就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：

  1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

  2. 将 read_rnd_buffer 中的 id 进行递增排序；

  3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

另外需要说明的是，如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

下面两幅图就是使用了 MRR 优化后的执行流程和 explain 结果。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

BKA（Batched Key Access）

理解了 MRR 性能提升的原理，我们就能理解 MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ 算法的优化。

以下面的SQL举例

SELECT tbl1.col1,tbl2.col2 from tbl1,tbl2 WHERE tbl1.col3 = tbl2.col3 AND tbl1.col3 = 1

执行流程为

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。

也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。

既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。

图中，我在 join_buffer 中放入的数据是 R1~R100，表示的是只会取查询需要的字段。当然，如果 join buffer 放不下 R1~R100 的所有数据，就会把这 100 行数据分成多段执行上图的流程。

那么，这个 BKA 算法到底要怎么启用呢？

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

CTE（MySQL 8）

MySQL Common Table Expressions（CTE，公用表表达式）是在MySQL 8.0及更高版本中引入的一种高级SQL构造，它允许用户定义一个临时的结果集，这个结果集可以在同一个查询中被多次引用，从而简化复杂的查询逻辑和提高代码的可读性。通过 WITH 关键字定义，可以把复杂的子查询抽象出来，给其命名，提高查询的可读性和可维护性。在同一个查询中，可以多次引用同一个 CTE，无需重复书写相同子查询。

作用：

  1. 临时结果集定义：CTE允许你在查询中定义一个临时的、只在当前查询上下文中存在的中间结果集，就像一个临时的视图一样，但它不会持久化存储在数据库中。

  2. 递归查询支持：特别地，MySQL CTE还支持递归查询，这对于处理层次型数据（如组织架构、菜单树等）非常有用，能够方便地实现树形结构的遍历和展现。

  3. 查询复用：你可以在一个查询的不同部分多次引用同一个CTE，避免了在多个地方重复相同的子查询，减少了代码冗余。

  4. 查询分解：将复杂的查询逻辑分解成易于理解的部分，每一部分作为一个单独的CTE，这样可以增强查询的模块化和维护性。

下面是使用CTE解决不定层级树形迭代问题，修改其中的值也可以使用在很多需要查询层级树的地方。

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

INSERT INTO employees VALUES 
(1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Employee 1', 2),
(4, 'Employee 2', 2),
(5, 'Manager B', 1),
(6, 'Employee 3', 5);

INSERT INTO employees VALUES 
(7, 'Employee 4', 2),
(8, 'Employee 5', 2),
(9, 'Employee 6', 5),
(10, 'Employee 7', 5),
(11, 'Manager C', 1),
(12, 'Employee 8', 11),
(13, 'Employee 9', 11),
(14, 'Employee 10', 11),
(15, 'Manager D', 1),
(16, 'Employee 11', 15),
(17, 'Employee 12', 15),
(18, 'Employee 13', 15),
(19, 'Manager E', 1),
(20, 'Employee 14', 19),
(21, 'Employee 15', 19),
(22, 'Employee 16', 2),
(23, 'Employee 17', 5),
(24, 'Employee 18', 11),
(25, 'Employee 19', 15),
(26, 'Employee 20', 19);

-- 定义一个名为org_structure的递归公共表表达式（CTE），用于构建组织结构层次
WITH RECURSIVE org_structure AS (
		 -- 初始化：选取公司最高级别（CEO）的员工信息
    SELECT emp_id, emp_name, manager_id, 1 AS level , CONCAT('',emp_id) as path
    FROM employees
    WHERE emp_name = 'CEO'

    UNION ALL
		-- 递归部分：通过JOIN操作连接employees表和已生成的org_structure表，获取下一级别的员工信息
    SELECT e.emp_id, e.emp_name, e.manager_id, os.level + 1 , CONCAT(os.path,',',e.emp_id) 
    FROM employees e
    JOIN org_structure os ON e.manager_id = os.emp_id
)
-- 最终查询结果：从org_structure CTE中选择需要展示的字段，并按照层级(level)和员工ID(emp_id)排序
SELECT emp_id, emp_name, manager_id, level ,path
FROM org_structure
ORDER BY level, emp_id;

开窗函数（MySQL 8）

开窗函数是在MySQL8.0以后才新加的功能，因此，要想直接使用开窗函数，则mysql版本要8.0以上。

其实开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。接下来，我们介绍开窗函数的相关语法；具体语法如下：

-- partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别 执行，在跨越分区边界时重新初始化。
-- order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和 partition by子句配合使用，也可以单独使用。
-- frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])

JSON特性

利用JSON解决动态数据问题，MySQL 5.7以后提供了JSON数据类型，可以直接对JSON存储、提取与解析。

因为JSON是弱约束的，因此存储数据非常灵活，同时也可基于虚拟列实现索引优化。

JSON字段可以理解可以存储动态数据，类似MongoDB的文档。

# 查询可以通过extra->'$.dist_request_id'='1648885683619_96674'查询到JSON中的值
SELECT * from document WHERE extra->'$.dist_request_id'='1648885683619_96674';

# 可以从JSON中映射虚拟字段，这个字段会不可以修改，会随着JSON中的数据变换而变化
ALTER TABLE document 
Add COLUMN `v_request_id` varchar(32)  
GENERATED ALWAYS AS (json_unquote(json_extract(`extra`,_utf8mb4'$.dist_request_id'))) VIRTUAL NULL;

# 创建虚拟字段是为了创建索引，来进行查询速度优化
create index idx_v_request_id on document(v_request_id);

explain select * from document where v_request_id = '1648885683619_96673';

什么时候分库分表（水平分表）？

什么时候分？

分库分表主要是优化数据量过大，各种优化都效果很差的情况，如无必要，不要分表。

比如在MySQL中，默认一页数据16K，按每一行数据1K计算，单表数据量不建议超过2400W，如果数据大小1K，数据量又超过2400W产生了4h的B+Tree，就考虑分表。

分库还是分表？

首先要确定，要解决的是什么问题？

单库单表：原始方案

单库多表：有效缩小磁盘扫描范围，目的是优化磁盘IO

多库多表：提供数据库并行处理能力，目的是优化并行计算能力

分表方案和产生的问题

分表方式产生的问题

  • 范围分片

最简单的就是根据数据ID分片

比如mysql节点1：存放id=1-10000的数据

mysql节点2：存放id=20000-30000的数据

mysql节点3：存放id=30000-40000的数据

优势：

容易扩容，易于管理和理解

缺点：

尾部热点：ID越靠后的数据一般越新，也就是越容易被访问，所以最后一个节点压力也就越大

资源浪费：和尾部热点同理，越靠前的服务器越不容易被访问，那么分配给它们的资源并没有被充分利用，导致资源浪费。

  • Hash分片

根据Hash取模，比如 id%3 这种方式来判断数据应该存储到哪一个节点上

优势：

数据分配均匀，不会出现尾部热点的问题，某种意义上是一种负载均衡的规则。

缺点：

扩容困难，比如id%3代表着有3个mysql节点，如果要扩容1个节点，那么所有的数据都要按照id%4来重新分配。

扩容问题可以考虑一致性Hash来解决。

分布式事务问题

引入XA、TCC、SAGA等分布式事务解决方案。

跨库JOIN关联查询

解决方案1：程序先查A表，再循环查询B表

解决方案2：MyCat、ShardingSphere支持两表跨库Join

跨节点分页查询问题

单节点各取n条，之后在程序合并运算取top n

全局主键ID问题

采用分布式主键生成器，推特SnowFlake、美团Leaf（不建议UUID，无序主键会产生页分裂）

垂直分表

什么是垂直拆分？

InnoDB是有结构的。

从前面了解到B+Tree一个节点是16KB，其实对应下面的页

  • row：行数据，也就是一张表中的行数据

  • page页：；管理数据基本单位，每一页的默认大小：16KB

  • Extent区：保存页的单位，默认大小1MB

关系：区由连续页组成，页由连续行组成。1024/16=64(即：一个1M的区有64个页)

假设一行数据为1K，那么1页16K大小，也就能存储16条数据，1亿数据需要625万页

垂直分页后，小表的1行数据为64字节（1K=1024字节），即1页256条数据，1亿数据需要39万页。

通过将重要字段单独剥离成小表，让每页容纳更多行数据，页减少后，缩小数据扫描范围，达到提高执行效率的目的。

比如下面对商品表进行拆分

因为分成了2张表，所以SQL要进行调整

-- 垂直拆分前
SELECT * FROM 商品表 WHERE 商品标题 = '辣条';

-- 垂直拆分后
SELECT * FROM 商品基本信息表 a,商品详情表 b WHERE a.商品id = b.商品id and a.商品标题 = '辣条';

1亿数据拆分前需要625W页来存储，拆分后小表只需要39W页。

假设没有利用上索引，出现了全表扫描，那么利用小表驱动大表，从小表39W页定位到数据，然后通过商品ID去大表拿到完整数据，这样来提高查询效率。

如何垂直拆分？

要根据表情况进行分析

条件1：单表数据总量过千万

条件2：条件2：字段超过20个，且包含了超长的Varchar，CLOB，BLOB等字段

情况1：

日志表：字段有日志（很长），创建时间，编号

这种就不需要，因为这三个字段都是关键字段，分不开

情况2：

字段很多，远超过20个，但是数据量很少，只有几百，几千条

不需要，因为数据量不够，给不到SQL压力

那些字段要放到小表中？

从代码上来讲：数据查询、排序时需要的字段，如分类编号，商品ID，逻辑表示等

从业务上来讲：高频访问的小字段，如商品名称，标题，价格等

那些放在大表？

低频访问字段：最后更新时间，配送信息，售后声明等

大字段：商品图文详情，图片BLOB，JSON元数据等

MySQL事务的四大特性ACID及其原理

ACID四大特性

  1. 原子性(Atomicity)：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么不执行。

  2. 一致性(Consistency)：在事务开始之前和事务结束之后，数据不被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。

  3. 隔离性(Isolation)：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其他事务运行效果。

  4. 持久性(Durability)：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

ACID实现原理

事务的隔离性是通过数据库锁以及MVCC机制实现的。

事务的一致性由undo log来保证：undo log是逻辑日志，记录了事务的Insert、update、delete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。

事务的原子性和持久性由redo log来保证：redo log被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

事务的各个隔离级别都是如何实现的

读未提交

采用的是读不加锁原理

事务读不加锁，不阻塞其他事务的读和写

事务写阻塞其他事务写，单不阻塞其他事务读

读已提交&可重复读

READ COMMITTED: 每次读取数据千都生成一个ReadView

REPEATABLE READ: 在事务里每次读取数据时生成一个ReadView

串行化

串行化的实现采用的时读写都加锁的原理

串行化的情况下，对于同一行事务，写会家写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等待前一个事务执行完成，才能继续执行。

MySQL查询语句中七个查询命令的特性

查询的优先级（谁先执行）

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

FROM

将硬盘上的表文件加载到内存中，生成一个全新的临时表。

WHERE

遍历FROM生成的临时表，根据条件进行过滤，并保存到一个新的临时表中，因为是逐行判断，所以无法使用聚合函数

GROUP BY

对临时表的数据先排序后分组，如果是多字段，那么第二字段开始操作的是上一个字段生成的临时表。

如果SELECT操作的临时表的GROUP BY提供的，那么会遍历所有分组字段的临时表的第一行内容。

HAVING

将GROUP BY生成的临时表中不满足条件的数据进行删除，并不会生成新的临时表。

这是操作的是一整个临时表，所以可以使用聚合函数。

SELECT

SELECT操作的临时表由FROM或WHERE提供的，那么会根据指定字段读取内容生成新的临时表。

如果由GROUP BY或HAVING提供的，那么会遍历GROUP BY生成的每一个临时，只读取第一行内容。

ORDER BY

专门针对SELECT生成的临时表中的数据进行排序，排序后的数据组成一个新的临时表。

LIMIT

对临时表的数据进行截取，并生成新的临时表。

七个命令中，只有HAVING不会生成临时表，其它都会。

七个命令中，只有FROM是从表文件从加载到内存，其他命令操作的都是上一个命令生成的临时表。

在当前命令执行完后，MySQL会销毁上一个命令生成的临时表。

多字段分组时，从第二个字段开始，操作的是上一个字段生成的临时表，所以多字段分组会生成多张临时表。

七个查询命令，那些命令执行的效率过慢

执行最慢的的是GROUP BY

原因：group by执行时间首先将临时表中的数据进行排序（排序等于全表遍历），然后再进行分组

order by

原因：需要对select生成的临时表中的数据进行一次排序（全表遍历），然后将拍好顺序的数据行组成一个全新的临时表

where

原因：需要对表中所有的数据行进行遍历，如果当前的临时表中的数据行过多，就会导致where执行效率非常慢

limit

原因：如果指定行数过大，就会导致查询速度慢。比如指定截取到20000行，它会从第0行开始找到20000行，指定的太大会遍历的数据过多，导致查询慢。

多表查询

多表查询没有指定表文件的加载顺序，也会导致查询速度过慢，也就是常说的小表驱动大表

 

索引那些情况下会失效

  1. 查询条件包含or，可能导致索引失效

  2. 如果字段类型时字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效

  3. 索引字段模糊搜索，只支持前置条件模糊搜索

  4. 联合索引，查询的条件列不是联合索引的第一列，索引失效

  5. 在索引列上使用Mysql内置函数，索引失效

  6. 对索引列运算，如 +、-、*、/ ，索引失效

  7. 索引字段上使用 !=或者 < >,not in时，可能会导致索引失效

  8. 索引上字段使用is null ,is not null可能会导致索引失效

  9. 左联机查询或右连接查询查询关键的字段编码格式不一样，可能导致索引失效

  10. Mysql优化器估计使用全表扫描比使用索引快，则不使用索引。

fen