Mysql 索引、锁与MVCC等相关知识点

文章目录

Mysql锁的类型

MySQL中有哪些锁：

  1. 乐观锁（Optimistic Locking）：假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。常用于读多写少的场景。

  2. 悲观锁（Pessimistic Locking）：假设并发操作时会发生冲突，因此在操作期间持有锁来避免冲突。常用于写多读少的场景。

  3. 全局锁（Global Lock）：对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。一般用于备份、恢复等操作。

  4. 表级锁（Table Lock）：对整个表加锁，其他连接无法修改或读取该表的数据，但可以对其他表进行操作。

  5. 意向共享锁（Intention Shared Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取共享锁。

  6. 意向排它锁（Intention Exclusive Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取排它锁。

  7. 页级锁（Page Lock）：对数据页（通常是连续的几个行）加锁，控制并发事务对该页的访问。适用于数据较大且并发量较高的场景。

  8. 行级锁（Row Lock）：对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。并发性高，但锁管理较复杂。

  9. 记录锁（Record Lock）：行级锁的特定类型，锁定单个行，确保其他事务无法同时修改或读取该行。

  10. 共享锁（Shared Lock）：也称为读锁，多个事务可以同时持有共享锁并读取数据，但不能修改数据。适用于同时读取同一数据的场景。

  11. 排它锁（Exclusive Lock）：也称为写锁，事务持有排它锁时，其他事务无法同时持有共享锁或排它锁，用于保护数据的写操作。

  12. 间隙锁（Gap Lock）：锁定一个范围的键，但不包括这些键的实际值。用于防止其他事务在范围内插入数据。

  13. 临建锁（Metadata Lock）：锁定数据库对象的元数据，如表结构，用于保证数据定义的一致性。

各种锁解析

锁使用

使用方式：
乐观锁示例：

悲观锁：
悲观锁的实现通常通过使用SELECT … FOR UPDATE或使用LOCK IN SHARE MODE语句来加锁。

-- 事务1：查询并修改订单状态
START TRANSACTION;
-- 查询订单状态，并持有排它锁
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE;
-- 执行一些业务逻辑判断...
-- 修改订单状态
UPDATE orders SET status = 'completed' WHERE order_id = 1;
COMMIT;

全局锁：

-- 事务1：加全局锁
FLUSH TABLES WITH READ LOCK;
-- 执行一些需要全局锁的操作...
-- 解除全局锁
UNLOCK TABLES;

表级锁的使用：

-- Session 1
START TRANSACTION;
-- 在Session 1中加共享锁
#显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁
#隐式上锁（默认，自动加锁自动释放
insert、update、delete //上写锁
-- 解锁
UNLOCK TABLES;
COMMIT;

InnoDB引擎的页级锁的示例

-- Session 1
START TRANSACTION;
-- 获取某个数据页的共享锁
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
-- 执行一些只读操作
-- 解锁
COMMIT;

行级锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

排它锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加排它锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等
-- 解锁
COMMIT;

意向共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中获取意向共享锁
LOCK TABLES products INTENTIONAL READ;
-- 执行一些只读操作，例如SELECT语句，对表进行读操作
-- 解锁
UNLOCK TABLES;

意向排它锁：

-- Session 1
START TRANSACTION;

-- 在Session 1中获取意向排它锁
LOCK TABLES products INTENTIONAL WRITE;

-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等

-- 解锁
UNLOCK TABLES;

间隙锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中使用范围查询，并对查询结果的间隙加锁
SELECT * FROM products WHERE price BETWEEN 10 AND 20 FOR UPDATE;
-- 执行一些需要对查询结果进行修改的操作，例如UPDATE、DELETE等
-- 解锁
COMMIT;

临建锁：

-- Session 1
SELECT GET_LOCK('my_lock', 10);

-- 执行一些需要加锁的操作

SELECT RELEASE_LOCK('my_lock');

记录锁：

-- Session 1
START TRANSACTION;

-- 获取某个记录的共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- 执行一些读操作

-- 释放锁
COMMIT;

-- Session 2
START TRANSACTION;

-- 获取某个记录的排他锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 执行一些写操作

-- 释放锁
COMMIT;

锁使用详细示例
锁解析及使用-微信

MVCC

MVCC(Mutil-Version Concurrency Control)，多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。用于支持读已提交(RC）和可重复读(RR）隔离级别的实现。

数据库通过加锁，可以实现事务的隔离性，串行化隔离级别就是加锁实现的，但是加锁会降低数据库性能。
因此，数据库引入了MVCC多版本并发控制，在读取数据不用加锁的情况下，实现读取数据的同时可以修改数据，修改数据时同时可以读取数据，只有在InnoDB引擎下存在。

MVCC主要是用来解决【读-写】冲突的无锁并发控制，可以解决以下问题：

在并发读写数据时，可以做到在读操作时不用阻塞写操作，写操作不用阻塞读操作，提高数据库并发读写的性能。
可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决【写-写】引起的更新丢失问题。

一般数据库中都会采用以上MVCC与锁的两种组合来解决并发场景的问题，以此最大限度的提高数据库性能。

MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突。
MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突。

在InnoDB存储引擎，针对每行记录都有固定的两个隐藏列【DB_TRX_ID】【DB_ROLL_PTR】以及一个可能存在的隐藏列【DB_ROW_ID】。

隐式字段描述是否必须存在
DB_TRX_ID事物Id，也叫事物版本号，占用6byte的标识，事务开启之前，从数据库获得一个自增长的事务ID，用其判断事务的执行顺序是
DB_ROLL_PTR占用7byte，回滚指针，指向这条记录的上一个版本的undo log记录，存储于回滚段（rollback segment）中是
DB_ROW_ID隐含的自增ID（隐藏主键），如果表中没有主键和非NULL唯一键时，则会生成一个单调递增的行ID作为聚簇索引否

MVCC实际上是使用的update undo log 实现的快照读。

当事务对某一行数据进行改动时，会产生一条Undo日志，多个事务同时操作一条记录时，就会产生多个版本的Undo日志，这些日志通过回滚指针（DB_ROLL_PTR）连成一个链表，称为版本链。

MVCC能否解决幻读问题：
首先可以明确的是，MVCC在快照读的情况下可以解决幻读问题，但是在当前读的情况下是不能解决幻读的。

快照读和当前读

快照读【Consistent Read】

也叫普通读，读取的是记录数据的可见版本（可能是过期的数据），不加锁，不加锁的普通select语句都是快照读，即不加锁的非阻塞读。
快照读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

如下语句：

select * from tableName;

当前读
也称锁定读【Locking Read】，读取的是记录数据的最新版本，并且需要先获取对应记录的锁，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁。如下语句：

SELECT * FROM student LOCK IN SHARE MODE;  # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ...  # 排他锁
DELETE FROM student WHERE ...  # 排他锁
UPDATE student SET ...  # 排他锁

当前读每次都会重新生成一个Read View，新增、删除、修改、排他锁、共享锁都是当前读。

当前读状态下可以解决幻读问题

读视图【Read View】

Read View提供了某一时刻事务系统的快照，主要是用来做可见性判断, 里面保存了对本事务不可见的其他活跃事务。

当事务在开始执行的时候，会产生一个读视图（Read View），用来判断当前事务可见哪个版本的数据，即可见性判断。
实际上在innodb中，每个SQL语句执行前都会生成一个Read View。

Read View重要的四个属性：

  • creator_trx_id
    创建当前read view的事务ID
  • m_ids
    当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但还没有提交的事务;
  • m_low_limit_id
    表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
  • m_up_limit_id
    当前系统中事务的 id 值最大的那个事务 id 值再加 1，也就是系统中下一个要生成的事务 id。

ReadView 会根据这 4 个属性，结合 undo log 版本链，来实现 MVCC 机制，决定一个事务能读取到数据那个版本。

读已提交只能读Read View中比自己小的事务ID，可重复读能读取比自己大的已提交的事务ID

在读已提交(Read Committed)的隔离级别下实现MVCC，同一个事务里面，【每一次查询都会产生一个新的Read View副本】，这样可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。
读已提交下只要数据的事务ID不在m_ids中，就能查到当前数据

在可重复读(Repeatable read)的隔离级别下实现MVCC，【同一个事务里面，多次查询，都只会产生一个共用Read View】，所有就算期间有事务已经提交m_ids也不会改变，以此解决不可重复读的并发问题。

原博客
MVCC解析-微信

mvcc案例解析

串行化的解决

读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁

索引类型

存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。

B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

  • 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
  • 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
  • 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。
    基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

  • 查询必须从索引的最左边的列开始。
  • 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
  • 存储引擎不能使用索引中范围条件右边的列。

哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和HEAP存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

  • MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
  • 不能使用 HASH 索引排序。
  • HASH 索引只支持等值比较，如“=”“IN()”或“”。
  • HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分

普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。仅支持InNoDB与MyISAM引擎。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

全文索引解析

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

实际使用区分

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

索引类型原文

索引失效情况

  1. 不使用索引列进行查询：如果查询中没有使用到任何索引列，MySQL将无法使用索引进行优化查询，这样查询的效率可能会较低。为了避免这种情况，应该在查询中使用适当的索引列。

  2. 数据类型不匹配：如果查询中使用了索引列但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较。在设计表结构时，应该确保索引列和查询的数据类型匹配。

  3. 前缀索引的使用不当：在某些情况下，为了提高索引的效率和节省存储空间，可以使用前缀索引。然而，如果使用前缀索引的长度过短，那么查询的结果可能会不准确。此外，如果使用前缀索引的列进行排序或分组操作，也会导致索引失效。

  4. 使用函数或表达式进行查询：如果在查询中使用了函数或表达式，MySQL将无法使用索引进行优化查询。例如，select * from table where year(date_column) = 2021; 在这种情况下，将无法使用date_column上的索引。

  5. 索引列的顺序不正确：复合索引是指包含多个列的索引。如果查询的条件中的列的顺序与复合索引的列的顺序不一致，MySQL将无法使用索引进行优化查询。因此，在建立复合索引时应该根据查询条件中最常用的列进行排序。

  6. 数据更新频繁：索引是为了提高查询性能而创建的，但在数据更新频繁的情况下，索引会导致插入、更新和删除操作的性能下降。因此，在设计表结构时需要权衡查询和更新的频率，并根据实际情况来确定是否创建索引。

  7. 索引过多或过少：如果表中的索引过多，可能会导致查询性能下降。每个索引都需要额外的存储空间，并且在数据更新时需要维护索引的一致性。另一方面，如果表中没有足够的索引，某些查询可能会变得很慢。因此，在设计表结构时需要合理地选择索引。

并不是索引一定会走
有一些特殊情况，索引能触发但是不会匹配:单次查询超过30%, IN子表数量过多

一张表最多支持多少个索引? 一个表最多16个索引,最大索引长度256字节。
一个sql中的一张表，最多只会走一个索引！

B+树索引详细解析
索引知识点

索引建立规范

1、索引的数量要控制：

  • 单张表中索引数量不超过5个
  • 单个索引中的字段数不超过5个
  • 对字符串使⽤用前缀索引，前缀索引长度不超过8个字符
  • 建议优先考虑前缀索引，必要时可添加伪列并建立索引

2、主键准则

  • 表必须有主键
  • 不使用更新频繁的列作为主键
  • 尽量不选择字符串列作为主键
  • 不使用UUID MD5 HASH这些作为主键(数值太离散了)
  • 默认使⽤非空的唯一键作为主键
  • 建议选择自增或发号器

3、重要的SQL必须被索引，比如：

  • UPDATE、DELETE语句的WHERE条件列
  • ORDER BY、GROUP BY、DISTINCT的字段

4、多表JOIN的字段注意以下：

  • 区分度最大的字段放在前面
  • 核⼼SQL优先考虑覆盖索引
  • 避免冗余和重复索引
  • 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

  • 不在低基数列上建立索引，例如“性别”
  • 不在索引列进行数学运算和函数运算

6、尽量不使用外键：外键用来保护参照完整性，可在业务端实现

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率

10、反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量

11、能使用唯一索引就要使用唯一索引，提高查询效率

12、研发要经常使用explain，如果发现索引选择性差，必须让他们学会使用

SQL编写规范

(1) sql语句尽可能简单大的sql想办法拆成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)
(2) 事务要简单，整个事务的时间长度不要太长
(3) 避免使用触发器、函数、存储过程
(4) 降低业务耦合度，为sacle out、sharding留有余地
(5) 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
(4) 不要用select *，查询哪几个字段就select 这几个字段
(5) sql中使用到OR的改写为用 IN() (or的效率没有in的效率高)
(6) in里面数字的个数建议控制在1000以内
(7) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：
select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;
(9) 使用union all替代union
(10) 避免使⽤大表的JOIN
(11) 使用group by 分组、自动排序
(12) 对数据的更新要打散后批量更新，不要一次更新太多数据
(13) 减少与数据库的交互次数
(13) 注意使用性能分析工具
Sql explain / showprofile / mysqlsla
(14) SQL语句要求所有研发，SQL关键字全部是大写，每个词只允许有一个空格
(15) SQL语句不可以出现隐式转换，比如 select id from 表 where id=‘1’
(16) IN条件里面的数据数量要少，我记得应该是500个以内，要学会使用exist代替in，exist在一些场景查询会比in
快
(19) 不使用负向查询，如not in/like
(19) 关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用
(20) 禁止在数据库中跑大查询
(21) 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率
(22) 禁止使⽤order by rand()
(23) 禁⽌单条SQL语句同时更新多个表

exlpain字段解析

通过EXPLAIN，可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

在这里插入图片描述

  • id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。id相同时，优先级由上而下。

  • select_type列
    select_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION RESULT联合临时表结果等。

  • table列
    table表示SQL语句查询的表名、表别名、临时表名。

  • partitions列
    partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。

  • type列
    type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。具体有以下值，性能由好到差依次是：
    system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

    • system
      当表中只有一行记录，也就是系统表，是 const 类型的特列。
    • const
      表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。
    • eq_ref
      表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。
    • ref
      表示使用非唯一性索引进行等值查询。
    • ref_or_null
      表示使用非唯一性索引进行等值查询，并且包含了null值的行。
    • index_merge
      表示用到索引合并的优化逻辑，即用到的多个索引。
    • range
      表示用到了索引范围查询。
    • index
      表示使用索引进行全表扫描。
    • ALL
      表示全表扫描，性能最差。

  • possible_keys列
    表示可能用到的索引列，实际查询并不一定能用到。

  • key列
    表示实际查询用到索引列。

  • key_len列
    表示索引所占的字节数。表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。

  • ref列
    表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。如果没用到索引，则显示为NULL。

  • rows列
    表示执行SQL语句所扫描的行数。

  • filtered列
    表示按条件过滤的表行的百分比。

  • Extra列
    表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。

    • Using filesort
      表示使用了外部排序，即排序字段没有用到索引。排序时没有按照建立复合索引字段的顺序进行，因此产生了外部的索引排序，效率低。
    • Using temporary
      表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。分组时没有按照建立复合索引字段的顺序进行，因此产生了临时表和外部的索引排序。效率低
    • Using where
      表示使用了where条件搜索，但没有使用索引。
    • Using index
      表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。
    • Using join buffer
      表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。
    • Using index condition
      表示用到索引下推的优化特性。
    • impossible where（一般重要指标）
      where子句的值总是false，不能用来获取任何元组。例如 where num > 1 and num < 0。
    • Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
    • Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
    • Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

使用案例及字段解析

explain案例解析

explain案例解析

Explain信息中Extra字段解释

ACID的原理

mysql将数据存储到数据库之前都是先通过日志的方式来存储数据，因为日志的存储是顺序存储，可以通过偏移量来控制或者查找，而数据库的持久化存储，是见缝插针，这样可能最大化利用磁盘空间，存储完还需要记录数据的地址，所以相比日志存储比较慢。

    原子性的实现：通过`Redo log`和`Undo log`，重做和回滚。如果事务提交了，那么就会执行Redo log写到数据库，如果没有提交就会执行undo log。

日志

redo log和binlog区别 ：

redo log是属于innoDB层面，
binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

redo log是物理日志，记录该数据页更新的内容；
binlog是逻辑日志，记录的是这个更新语句的原始逻辑

redo log是循环写，日志空间大小固定；
binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

binlog可以作为恢复数据使用，主从复制搭建，
redo log作为异常宕机或者介质故障后的数据恢复使用。

redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

引擎

MySQL5.5版本后，MySQL的默认内置存储引擎已经从MyISAM变成InnoDB

InnoDB：

  • 支持事务;

  • 行级锁定(更新数据时一般指锁定当前行)：通过索引实现、全表扫描忍让时表锁、注意间隙所的影响;

  • 读写阻塞与事务的隔离级别相关；

  • 具有非常高的缓存特性(既能缓存索引、也能缓存数据)；

  • 这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；

  • 所有的辅助索引(secondary indexes)都会保存主键信息；

  • 支持分区、表空间类似与oracle 数据库；

  • 支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);

  • 相对MyISAM而言、对硬件的要求比较高

MyISAM特性

  • 不支持事务

  • 表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。

  • 读写互相阻塞：不仅会在写入的时候阻塞读取，myisam还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

  • 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少产品IO，但是这个缓存区只会缓存索引，而不会缓存数据。

  • 读取速度较快，占用资源相对少。

  • 不支持外键约束，但支持全文索引。

Memory

慢SQL

查看慢SQL是否启用，查看命令：show variables like 'log_slow_queries';
如果结果为ON则是开启了，如果为OFF则表示禁用了。

  1. 开启慢查询命令：set global log_slow_queries = on;
  2. 查看是否开启：show variables like 'log_slow_queries';
  3. 查看慢查询参数，即设置超过多少秒的查询归为了慢查询。参数为：long_query_time，查询命令： show global variables like ‘long_query_time’;
  4. mysql默认时间为10秒，即10秒及以上的查询被归为了慢查询。我们的实际项目中根本就不可能这么包容你，所以得提供查询效率优化sql，让程序更快的执行。
  5. 这里设置时间为1秒，即超过1秒就会被认为慢查询。设置命令：set global long_query_time =1;用命令设置的，会立即生效，不用重启mysql服务。但重启mysql服务后就会失效
  6. 查看设置的时间， show global variables like ‘long_query_time’;即可看到现在已经变为1秒了
  7. 查看慢查询存放日志，命令： show variables like 'slow_query_log_file';

可以使用mysql自带的mysqldumpslow解析慢日志，示例返回内容：

查询语句为：

SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;

mysqldumpslow 返回内容为 ：

Count: 1  Time=1.91s (1s)  Lock=0.00s (0s)  Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;

使用mysqldumpslow的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构;

pt-query-digest是用于分析mysql慢查询的一个工具

配置示例博客
慢sql日志解析

整合SpringBoot

整合博客

博客记录

mvcc解析