Mysql概念

kill-java

已于 2022-10-10 14:19:24 修改

阅读量232

点赞数

分类专栏：概念篇文章标签： mysql

于 2022-09-06 15:44:59 首次发布

本文链接：https://blog.csdn.net/zq6269/article/details/126709248

版权

概念篇专栏收录该内容

22 篇文章 0 订阅

订阅专栏

索引

存储结构区分
- B-Tree
- B+Tree
- Hash索引
应用层区分
- 普通索引
- 唯一索引
- 主键索引
- 复合索引
- 空间索引
- FULLTEXT全文索引

存储引擎

InnoDB
- 默认存储引擎
- 支持自增长列（auto_increment）
- 支持外键（foreign key）
- 索引使用的是B+Tree
- 支持MVCC的行级锁
- 提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全
- 优点
  - 支持事务，崩溃修复能力，并发控制
- 缺点
  - 读写效率差，占用数据空间相对较大
- 场景：既有读，写也频繁，不知道如何选择适合时。
MyISAM
- 不支持事务
- 不支持行锁
- 支持并发插入表锁
- 主要用于高负载的select
- 优点
  - 占用空间小，处理速度快
- 缺点
  - 不支持事务的完整性和并发
- 场景：表中绝大多数都只是读查询（一般R/W > 100:1且update相对较少），可以考虑 MyISAM。
MEMORY
- 数据存储在内存中
- 默认使用哈希（HASH）索引，其速度比B+Tree要快，如果希望使用B树型，可在创建表的时候使用
- 优点
  - 数据快速访问和处理
- 缺点
  - 一旦发生异常，重启或关闭机器，数据都会丢失。
- 场景：适合用于查询的临时表。
Archive 归档
- 只支持insert和select
- 插入快，查询需要执行全表扫描
- 优点
  - 高压缩，快速插入。
- 缺点
  - 不适合频繁查询。
- 场景：适合存储大量独立的、作为历史记录的数据。
其它引擎
- CSV存储引擎
- BLACKHOLE存储引擎（黑洞引擎）
- PERFORMANCE_SCHEMA存储引擎
- Federated存储引擎
- MERGE存储引擎

事务

事务特性

A 原子性
- 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
C 一致性
- 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
I 隔离性
- 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
D 持久性
- 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

mysql事务处理方法

用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
用SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交

脏读，幻读，不可重复读

脏读
- 事务A将某一值修改但是还未提交，然后事务B读取该值，然后使用了这个数据
幻读
- 事务A 按照一定条件进行数据读取，期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。
不可重复读
- 在一个事务内根据同一个条件对数据进行多次查询，但是结果却不一致，原因是其他事务对该数据进行了修改。

隔离级别

未提交读（READ UNCOMMITTED）
- READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。
提交读（READ COMMITTED）<oracle默认>
- READ COMMITTED 隔离级别的安全性比 REPEATABLE READ 隔离级别的安全性要差。处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。
可重复读（REPEATABLE READ）<mysql默认>
- 在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。
序列化（SERIALIZABLE）
- 如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

锁

锁粒度
- 表锁
  - 使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
  - 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行锁
  - 使用行级锁定的主要是InnoDB存储引擎。
  - 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页锁
  - 使用页级锁定的主要是BerkeleyDB存储引擎。
  - 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
加锁机制
- 乐观锁
- 悲观锁
兼容性
- 共享(s)锁
  - select * from table_name where … lock in share mode (加共享锁)
- 排它(x)锁
  - 对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句，会自动给涉及数据集加排它锁（X）。
  - 对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁或者 X锁，需要我们手动加锁。
  - select * from table_name where … for update (加排它锁)
- 读锁会阻塞写(X)，但是不会堵塞读(S)。而写锁则会把读(S)和写(X)都堵塞
加锁模式
- 记录锁
  - 对表中的记录加锁，叫做记录锁，简称行锁
    - SELECT * FROM test WHERE id = 1 FOR UPDATE;
    - 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。
    - id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
    - 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁
    - 在通过主键索引与唯一索引对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：
    - UPDATE SET age = 50 WHERE id = 1;
  - 记录锁是锁住记录，锁住索引记录，而不是真正的数据记录.
  - 记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。
- 间隙(gap Locks)锁
  - 间隙锁是Innodb在RR(可重复读) 隔离级别下为了解决幻读问题时引入的锁机制。
  - 间隙锁是innodb中行锁的一种。
  - 使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
    - SELECT * FROM emp WHERE empid > 100 FOR UPDATE
    - 当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
    - 这个时候如果你插入empid等于102的数据的，如果那边事物还没有提交，那你就会处于等待状态，无法插入数据。
- 临键(next-key)锁
  - 可以理解为一种特殊的间隙锁。
  - Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。
  - 通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。
  - InnoDB 中行级锁是基于索引实现的。
  - 临键锁只与非唯一索引列有关，在唯一索引列包括主键列上不存在临键锁。
  记录锁、间隙锁、临键锁总结
  1. InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
  2. 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
  3. 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
  4. 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。
- 意向锁
  - 意向锁又分为意向共享锁（IS）和意向排他锁（IX）
  - 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
  - 意向共享锁（IS）和意向排他锁（IX）都是表锁。
  - 意向锁是一种不与行级锁冲突的表级锁
  - 意向锁是 InnoDB 自动加的，不需用户干预。
  - 意向锁是在 InnoDB 下存在的内部锁，对于MyISAM 而言没有意向锁之说。
  - 意向锁存在的目的就是为了让 InnoDB 中的行锁和表锁更高效的共存。
- 插入意向锁
  - 插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。
  - 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
  - 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待
  - 插入意向锁是行锁
  - InnoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。

优化 Explain 性能分析

语法

EXPLAIN  (SQL语句)

执行计划包含的信息

id
- id是用来顺序标识整个查询中SELECT语句的编号，在嵌套查询中id越大的语句越先执行。该值可能为NULL，那么这一行用来说明的是其他行的联合结果。
select_type
- simple
  - 简单子查询，查询中不包含子查询和union
- primary
  - 包含union或者子查询，最外层的部分标记为primary
- subquery
  - 在select或者where列表中包含了子查询
- derived
  - 派生表，该临时表是从子查询中派生出来的，位于form中的子查询
- union
  - 位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary，如果是union位于from中则标记为derived
- union result
  - 用来从匿名临时表里检索结果的select被标记为union result
- dependent union
  - 首先需要满足union的条件，及union中第二个以及后面的select语句，同时该语句依赖外部的查询
- dependent subquery
  - 和dependent union相对union一样
table
- 对应行正在访问哪一个表，表名或者别名。
partitions
- 匹配的分区，如果查询是基于分区表的话,会显示查询将访问的分区
type
- type显示的是访问类型，是较为重要的一个指标
- 结果值从好到坏依次是：
  - system
    - 表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
  - const
    - 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
  - eq_ref
    - 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  - ref
    - 非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而，它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体
  - fulltext
    - 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
  - ref_or_null
    - 与ref方法类似，只是增加了null值的比较。实际用的不多。
  - index_merge
    - 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
  - unique_subquery
    - 用于where中的in形式子查询，子查询返回不重复值唯一值
  - index_subquery
    - 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
  - range
    - 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般是在where语句中出现between、<、>、in等的查询中，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点不用做扫描全部索引
  - index
    - index和all的区别为index类型只遍历索引树，这通常比all要快，因为索引文件通常比数据文件小，也就是说all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的
  - all
    - 最坏的情况，将遍历全表以找到匹配的行
possible_keys
- 显示查询使用了哪些索引，表示该索引可以进行高效的查找，但是列出来的索引对于后续优化过程可能是没有用的。
key
- key列显示了MySQL优化器实际上所决定使用的键（索引），如果没有选择索引，键是NULL。要想MySQL使用或者忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示地值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref
- 显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或者常量被用于查找索引列上的值。
rows
- rows列显示MySQL认为它执行查询时必须检查的行数，注意这是一个预估值。
filtered
- MySQL 5.7缺省就会输出filtered，它指返回结果的行占需要读到的行(rows列的值)的百分比
Extra
- Extra是Explain输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型	说明
Using filesoft	MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesoft说明MySQL使用了后者，但注意虽然叫filesoft但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是order by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
Using temporary	用临时表保存中间结果，常用于group by和order by操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Using where	使用了where从句来限制哪些行将于下一张表匹配或者返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用where条件过滤
Using join buffer	使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接；Batched Key Access，连接算法是批量索引连接
Using index	表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using index condition	这是MySQL 5.6出来的新特性，叫做"索引条件推送"。简单说就是MySQL原来在索引上是不能执行如like这样的模糊匹配操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
using_union	表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
using intersect	表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
using sort_union	与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
sing sort_intersection	同上
no tables used	不带from字句的查询或者From dual查询使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
Not exists	MySQL优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在每有group by子句的情况下，基于索引优化min/max操作，或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	在select部分使用了distinc关键字

mysql主从延时解决方案

产生原因
- 随机重放
  - Mysql 主库中写 binlog 的操作是顺序写的，磁盘的顺序读写速度是很快的。同样的，从库中的 I/O 线程操作日志的速度效率也是很高的。但是SQL 线程重放的过程是随机写盘的，某一时刻 relay log 里的数据来不及重放进从库，就会产生主从延迟的情况。
- 锁等待
  - 对于 SQL 单线程来说，当遇到阻塞时就会一直等待，直到执行成功才会继续进行。如果某一时刻从库因为查询产生了锁等待的情况，此时只有当前的操作执行完成后才会进行下面的操作，同理也就产生了主从延迟的情况。
- 大事务
  - 当主库执行大事务时，例如大表DDL，一个事务操作很多行时，也会导致从库出现主备延时。
- 硬件问题
解决方案
- 并行复制
  - MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
- 降低主库并发
  - 合理使用队列和redis等，对高并发请求做限流等处理
- 读主库
  - 对于一些实时性极高的数据，可以直接读主库，但这样就失去了读写分离的意义
- 尽量保持主备库的硬件配置一致，甚至从库的服务器配置略高于主库。
- .避免大事务
  - 大表ddl操作，建议将大事务拆分为多个小事务进行执行
- 半同步复制
  - 主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认
    - 全同步复制(介绍)
      - 指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
  - 半同步复制可能遇到的问题
    - 主库已经将事务 commit 到了存储引擎层，应用已经可以看到数据发生了变化，只是在等待返回而已。如果此时主库宕机，可能从库还没写入 Relay Log，就会发生主从库数据不一致 (如下图)
    - MySQL 5.7 引入了增强半同步复制。“Waiting Slave dump” 被调整到了 “Storage Commit” 之前，即主库写数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay Log 后，并将数据落盘，然后返回给主库 ACK，通知主库可以执行 commit 操作，然后主库再将事务提交到事务引擎层，应用此时才可以看到数据发生了变化。