MySQL

MySQL索引原理及慢查询优化

架构

Server层

连接器、查询缓存、分析器、优化器、执行器。

连接器

管理连接，权限验证；

查询缓存

命中则直接返回结果；查询缓存的失效非常频繁，所以对于更新压力的大的数据库，它的查询缓存命中率很低。MySQL8.0版本直接将查询缓存删掉。

分析器

词法分析，语法分析，搞清楚SQL语句的语义

优化器

执行计划生成（有join操作时，决定表的连接顺序），选择索引，且索引选择根据

  1. 扫描行数：根据索引的区分度（Cadinality）来估算记录数

  2. 临时表

  3. 是否排序

优化器存在选错索引的可能性。解决方案

  1. 使用analyze table解决

  2. 在应用端使用force index来强行指定索引

  3. 修改SQL语句

执行器

判断当前用户对该数据表有操作权限，操作引擎，返回结果；

存储引擎

负责数据的存储和提取 ，是插件式的。

  1. InnoDB：支持行级锁，表级锁，事务，外键，MySQL5.5版本后的默认引擎；

  2. MyISAM：支持表级锁；

SQL语句的执行

一条UPDATE语句是如何执行的

mysql> update T set c=c+1 where ID=2;

与SELECT语句不同的是，更新流程还涉及到两个日志模块：

内容

位置

写入规则

redolog

物理日志，记录的是“在某个数据页上进行了什么操作”

InnoDB引擎持有的

循环写，空间用完后会覆盖掉就内容

binlog

逻辑日志，记录的是SQL语句

MySQL的Server层实现，所有引擎都可以用

追加写，一个文件写完会切换到下一个，不会覆盖以前的文件

执行器和InnoDB引擎在执行UPDATE语句时的内部逻辑

  1. 执行器找引擎取ID=2的行，如果内存中有则返回，若没有则从磁盘读入内存，再返回；

  2. 执行器拿到数据后更新，并将更新后的数据给引擎写入。

  3. 引擎将新数据更新到内存，同时将这个更新操作记录到redo log中，此时redo log处于prepare状态。然后告诉执行器执行完成，随时可以提交事务。

  4. 执行器生成这个操作的bin log，并将其写入磁盘。

  5. 执行器调用引擎的提交事务的接口，引擎把redo log改成commit状态，更新完成。

order by是怎么工作的

全字段排序

所谓全字段是指查询内容的所有字段都放入sort_buffer中进行排序。

select city,name,age from t where city='杭州' order by name limit 1000;

  1. 初始化sort_buffer，确定放入name, city, age三个字段；

  2. 从索引city上找到第一个满足city='杭州'的主键id；

  3. 到主键id索引上取出整行数据，取name, city, age三个字段的值，存入sort_buffer中；

  4. 从索引city上找到下一个满足city='杭州'的主键id；

  5. 重复3、4步直到city不再满足查询条件；

  6. 对sort_buffer中的数据按照字段name进行快速排序；

  7. 按照排序结果，把前1000行返回给客户端；

rowid排序

当查询要返回的字段很多的话，需要放入sort_buffer中的字段数太多，导致内存中能同时放下的行数很少，要分成很多临时文件，导致排序效率低。因此rowid排序只将主键和待排序的字段放入sort_buffer中，所以排好序后，还需要再访问一次原表。比全字段排序多访问了一次表的主键索引。

范围查询是怎么走索引的

表

页的大小通常是4KB

锁

在InnoDB中，锁是加在索引上的。

分类

  1. 悲观锁/一致性锁定读（当前读）

    1. 行级锁

      1. 共享锁

      2. 排它锁

    2. 表级锁

      1. 表锁

      2. 元数据锁：保证读写的正确性（变更表结构操作的安全性）

    3. 全局锁：对整个数据库实例上锁。应用场景是做全库逻辑备份。

  2. 乐观锁/一致性非锁定读（快照读）

    1. MVCC

锁的算法

  1. Record Lock：单个行记录上的锁

  2. Gap Lock：锁定一个范围，但不包含中间行记录本身。开区间。

  3. Next-Key Lock：Record+Gap Lock。前开后闭区间。

锁问题

脏读

不可重复读

幻读

一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。幻读仅专指”新插入的行“。

产生幻读的原因：

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的间隙。

死锁

死锁的产生：多个线程（事务）出现循环资源依赖，涉及的线程都在等待别的线程释放资源。

死锁的解决

  1. 设置超时时间

  2. 主动死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以继续执行。但是死锁检测会耗费大量CPU资源。

事务

特性

  1. 原子性

  2. 一致性

  3. 隔离性

  4. 持久性

两阶段提交

两阶段提交：将redo log的写入拆成了prepare和commit两个步骤，目的是使两份日志之间保持逻辑一致。

隔离级别

  1. 读未提交：一个事务还未提交时，他做的变更可以被另一个事务看到。

  2. 读已提交：一个事务只有提交后，他做的变更才能被其他事务看到。

  3. 可重复读：一个事务执行过程中看到的数据，总跟他启动时看到的数据一致。

  4. 串行化：写锁+读锁+读写互斥。冲突时，后访问的事务需要等前一个事务执行完成后才能继续执行。

隔离级别

脏读

不可重复读

幻读

读未提交

可能

可能

可能

读已提交

不可能

可能

可能

可重复读

不可能

不可能

可能

串行化

不可能

不可能

不可能

幻读是不能通过行锁来避免的，因为行锁无法锁住INSERT操作。

InnoDB默认隔离级别——可重复读

MVCC：同一条记录在系统中可以存在多个版本（多个快照数据）。

MVCC在InnoDB中的实现：

RC和RR隔离级别是利用consistent read view（一致性视图）方式支持的。即在某一时刻对事务系统打快照，把当时的事务系统状态记下来，之后的所有读操作根据其事务id和快照中的事务状态作比较，以此判断read view对于事务的可见性。

RR隔离级别和RC隔离级别的区别是创建快照的时机不同：

  1. 在”可重复读“级别下，这个视图是事务启动时创建的，整个事务存在期间都在使用这个视图。

  2. 在”读提交“级别下，这个视图是在每个SQL语句开始执行的时候创建的。

实现的核心要素（基于undo log的版本链+ReadView）

  1. 事务版本号。每开启一个新事物，事务版本号会递增；

  2. 数据行的隐藏列。

    1. DB_TRX_ID：记录操作该数据的事务的ID

    2. DB_ROLL_PTR：指向上一个版本数据在undo log里的位置指针

    3. DB_ROW_ID：隐藏ID，用于创建聚集索引

  3. undo log。每条记录在更新时都会同时记录一条回滚操作。记录上的最新值可以通过回滚操作得到前一个状态的值。

  4. read view。 其中的重要属性：

    1. trx_ids：当前未提交的事物版本号集合

    2. low_limit_id：当前系统最大的事务版本号（创建view时刻），>=low_limit_id的事务对于view都是不可见的。

    3. up_limit_id：当前系统未提交的最小的事务版本号（创建view时刻），<up_limit_id的事务对于view一定是可见的。

    4. creator_trx_id：创建当前view的事务版本号

UPDATE语句的操作逻辑

  1. 获得一个新的事务版本号

  2. 把该表数据拷贝到undo log中

  3. 修改该表数据

  4. 把该数据行的事务版本号改为当前事务版本号，并把DB_ROLL_PTR地址指向undo log数据地址

快照读的逻辑（RC和RR隔离级别下的判断逻辑是一样的，只是readview生成时机不同。）

  1. 当前事务id < readview中的最小活动事务id时，trx_id < up_limit_id，该行数据对当前事务是可见的，返回。

  2. 当前事务id >= 最大事务id时，trx_id >= low_limit_id，该行数据对当前事务是不可见的，匹配失败。

  3. up_limit_id <= trx_id < low_limit_Id，则将trx_id与活跃事务集合中的事务进行匹配

    1. 不在集合中：说明已经这个数据的在创建本事务之前就已经提交过了，可以显示该行数据，返回。

    2. 在集合中&&trx_id == creator_trx_id，可以显示该行数据，返回。

    3. 在集合&&trx_id != creator_trx_id，则匹配失败

  4. 匹配失败时，根据数据行隐藏列db_roll_ptr找到上个版本的数据行，再对该数据行的版本号进行匹配。

当前读：在一个事务中进行UPDATE操作时，先读后写中的读是读当前的值，而不是快照中的值。也是因为UPDATE操作加锁了的缘故。在事务中使用以下语句会使用当前读；

select ... for update;
select ... lock in share mode;

索引

索引：为了快速查找数据的数据结构。

索引常见的模型

索引的数据结构

哈希表

只有等值查询的场景。对于处理范围查找和排序性能非常差，只能通过全表扫描来处理。

有序数组

等值查询+范围查询场景；查询效率高，更新效率低。所以只适合静态存储引擎。

搜索树

B树

多路平衡二叉树，一颗m叉的BTree特性如下

  1. 根结点至少有两个子女

  2. 每个非根节点所包含的关键字个数j满足ceil(m/2)-1 <= j <= m-1

  3. 树中每个节点至多包涵m个孩子

  4. 所有叶子节点都在同一层

B树可以在非叶子节点上存储数据，所以在进行查找时，总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量随机IO，这也是B树最大的性能问题。

B+树

所有访问或者修改一条数据的SQL时间复杂度都是O（logn)，也就是树高

区别：

  1. B+树中所有叶子节点都是通过指针连接在一起。所以范围扫描十分方便。

  2. B+树的所有数据都存储在叶子节点中。

当数据表数据量为N，每个磁盘块数据项的数量是m，则高度h=log(m+1)N

分类

根据叶子节点内容分为：

  1. 主键索引/聚簇索引

  2. 非主键索引/二级索引/辅助索引（有时候需要再到主键索引上搜索，称为回表）

对于辅助索引来说，一般情况下需要用在辅助索引上查找到的主键再到主键索引上搜索对应行，称为回表；

但是，当辅助索引上的叶子节点中包含了搜索的内容，则不需要回表了。这样的索引称为覆盖索引。覆盖索引是一个常用的性能优化手段。比如建立身份证号和姓名的索引，需求是根据身份证号查询他的姓名，则不需要回表，减少了语句的执行时间。

联合索引：根据多少列建立索引。

索引下推：在回表之前，先对已知的条件进行判断，判断成功则回表，判断失败则不返回该行。

建索引的几大原则

  1. 最左匹配原则

  2. 尽量选择区分度高的列作为索引，区分度公式count(distinct clo)/count(*)

  3. 尽量扩展索引，不要新建索引。

InnoDB逻辑结构

change buffer：当更新操作时，如果数据页不在内存中，则将更新缓存到change buffer当中，当下次访问该数据页时，再将其调入内存，并进行change buffer中的更新操作，这个操作称为merge。

另外，唯一索引的更新不能使用change buffer。因为对于唯一索引，所有更新操作都要判断该操作是否违背唯一性约束，需要将数据调入内存。实际上只有普通索引可以使用。

buffer pool：数据页读入内存放到缓冲池中。

SQL优化

查一行数据时，可能会出现”被锁住“和”执行慢“的例子。

”执行慢“：有可能是后开启的事务对这行数据进行了很多次修改操作，导致undo log很长，先开启的事务再次查询这行数据时，需要查找很多次合适的版本数据，因此执行慢。

慢查询性能问题

  1. 索引没有设计好；

  2. SQL语句没写好；

  3. MySQL的优化器选错了索引；

慢查询

查询优化神奇——explain命令

explain结果中的参数

  1. table 查找的表

  2. possible_keys 可能选择的索引

  3. key 实际选择的索引

  4. key_len 索引的长度

  5. rows 待查询的行数，核心指标，绝大部分rows小的语句执行一定很快，所以优化语句基本上都是在优化rows

慢查询优化基本步骤

  1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE（禁止查询使用缓存）

  2. where条件单表查，锁定最小返回记录表。把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

  3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

  4. order by limit形式的SQL语句让排序的表优先查询

  5. 了解业务使用场景

  6. 建索引时遵循几大原则

TODO分库分表

PreviousMyBatisNext其他技术

Last updated

Was this helpful?