Mysql相关

2020-05-12  本文已影响0人  万福来

Mysql相关

Mysql并发控制-锁

共享锁

共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源，互不干扰。

排他锁

排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

锁策略

table lock（表锁）

    update table set columnA = "a" where columnB = "b";

如果上述sql语句中columnB字段不存在索引或者不是组合索引前缀，会进行锁表操作。
如果columnB字段创建索引后，那么会锁住满足where条件的行（行锁）。

row lock （行锁）

行锁可以最大限度的支持并发处理，当然也带来了最大开销，加锁慢，行锁的粒度在每一行数据。

InnoDB 间隙锁

当我们用范围条件而不是相等检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但是并不存在的记录，叫做“间隙（GAP）”,InnoDB也会对这个间隙加锁，这种锁机制就是间隙锁(Next-Key锁）。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值得并发插入，造成严重的锁等等。所以应尽量使用相等条件访问更新数据。
间隙锁的目的：

显式锁 与 隐式锁
隐式锁:我们上文说的锁都属于不需要额外语句加锁的隐式锁。
显示锁：

SELECT ... LOCK IN SHARE MODE(加共享锁);
SELECT ... FOR UPDATE(加排他锁);

Mysql并发控制-事务

事务的特性ACID

事务的隔离级别（依靠锁来实现）

Mysql多版本并发控制(MVCC)

MVCC是个行级锁变种，他在普通读情况下避免了加锁操作，因此开销更低。实现了非阻塞读，对于写操作只锁定必要的行。

select无锁操作与维护版本号

死锁，就是产生了循环等待链条，我等待你释放锁，你却等待我释放锁，我们都相互等待，谁也不释放自己的锁，导致无限等待下去。比如：

//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;

innodb_lock_wait_timeout 等待锁超时回滚事务：
直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。
wait-for graph算法来主动进行死锁检测：
innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。

如何避免死锁

Mysql - 索引原理

索引目的

索引的目的在于提高查询效率，可以类比一本字典，如果要查询某个单词，可以先根据单词首字母查询字典目录，然后找到对应的页码，直接翻到指定页码在进行二分查找，提高查询效率。

磁盘IO与预读

磁盘IO是非常高昂的操作，计算机系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也会读取到内存缓冲区，这样可以方便计算机很快访问相邻的数据，这就是磁盘预读。每一次IO读取的数据称之为一页（page）,page大小和操作系统有关，一般为4k或8k。

索引数据结构 B+Tree

为了每次查询数据的时候把磁盘IO次数控制在一个很小的数量级，我们需要一个高度可控的多路搜索树。这就是B+Tree。


image.png

浅蓝色为一个磁盘块，每个磁盘块包含几个数据项（深蓝色）和指针(黄色）,如磁盘块1包含数据项17和35，包含指针P1、P2、P3表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块，真实的数据只存在于叶子节点。非叶子节点只存储指引搜索方向的数据项，而不存真实的数据。如17、35并不真实存在数据表中。

B+Tree的查找过程

比如要查找数据项29，需要先把磁盘块1加载到内存，发生一次IO，在内存中二分查找29在17和35之间，找到磁盘块1的P2指针；然后在通过P2指针在加载磁盘块3到内存，发生第二次IO，内存中二分查找29在26和30之间，锁定磁盘块3的P2指针；通过指针加载磁盘块8到内存，发生第三次IO，同时二分查找到29，结束查询，总计发生三次IO。

B+Tree性质

联合索引数据结构

image.png

一个SQL只能利用到联合索引中的其中一列进行范围查找，因为B+Tree的每个叶子节点有一个指针指向下一个节点，把某一索引列的所有叶子节点串在了一起，只能根据单列的叶子节点进行范围查询。

Mysql - 索引原则

Mysql - 索引类型

索引主要分为两大类，聚簇索引和非聚簇索引

聚簇索引 (clustered index)

聚簇索引的叶子节点存储行记录，InnoDB必须要有且只有一个聚簇索引：

  1. 如果表定义了主键，则主键索引就是聚簇索引；
  2. 如果没有定义主键，则第一个非空的唯一索引列是聚簇索引；
  3. 如果没有唯一索引，则创建一个隐藏的row-id列作为聚簇索引。主键索引查询非常快，可以直接定位行记录。

非聚簇索引 （secondary index)

InnoDB非聚簇索引的叶子节点存储的是行记录的主键值，而MyISAM叶子节点存储的是行指针。
通常情况下，需要先遍历非聚簇索引获得聚簇索引的主键ID，然后在遍历聚簇索引获取对应行记录。
非聚簇索引需要扫描两遍索引树：又叫回表查询
1、先扫描非聚簇索引根据where条件定位到主键值id=9;
2、再根据主键ID扫描聚簇索引定位到行记录。

回表查询

回表查询就是先定位主键值，在根据主键值定位行记录，需要扫描两遍索引。
解决方案：
只需要在一颗索引树上能够获取SQL所需要的所有列数据，则无需回表查询，速度更快。
常见方法：
将要查询的字段，建立到联合索引里去，这就是索引覆盖。
查询sql在进行explain解析时，Extra字段为Using Index时，则触发索引覆盖。
没有触发索引覆盖，发生了回表查询时，Extra字段为Using Index condition.

索引覆盖进行回表查询优化场景

select id, name, sex from table where name = 'tom'; #单列索引（name）联合索引(name,sex)，可避免回表
select id, name, sex from table order by name limit 5 ,10 # 单列索引（name）联合索引(name,sex)，可避免回表

索引条件下推 （Index Condition Pushdown)

mysql5.6引入了索引下推优化，默认开启，使用 SET optimizer_switch = 'index_condition_pushdown=off'; 可以关闭。
索引下推示例
数据库表中创建了联合索引 （a,b,c)
select * from table where a = 'a' and b like '%b%' and c like '%c%';
没有索引下推技术：
由于b和c使用了like，将导致索引匹配失效，所以只有a字段使用了索引，在索引中通过字段a查询到所有的数据，然后返回服务端，然后在服务端基于模糊匹配条件进行数据过滤。
使用索引下推技术：
可以在索引中首先通过字段a进行查询，然后在根据索引上的b和c判断是否符合模糊匹配条件，如果符合条件则根据该索引来定位对应数据，不符合则直接拒绝，有了索引下推技术，可以在有like条件的情况下减少回表次数，提高查询性能。

主从复制过程

  1. 主库把数据更改记录到二进制日志（Binary Log)中；
  2. 从库通过IO线程将主库上的二进制日志复制到自己的中继日志中（Relay Log)；

  3. 从库通过sql线程读取中继日志中的事件，将其执行到从库之上。


    image.png

主从复制模式

异步复制模式 （mysql async-mode) -默认模式

Mysql增删改查操作全部记录在binLog中，当salve节点连接master节点时，会主动从master节点处获取最新的binLog，并把binLog中的sql进行重新执行。
缺点：会造成主从复制延迟


image.png

半同步模式 （mysql semi-sync）

master需要接受到其中一台salve的确认信息，才会commit然后返回给用户；否则要等直到超时时间然后切换成异步模式再提交。可以使得主从复制延迟缩小，提高数据安全性，但是影响性能，响应时间变长。可以确保了事务提交后，binlog至少传输到一个slave，但不保证slave将此事务更新到db。


image.png

全同步模式

全同步模式是指master和salve节点全部执行了commit并确认才会向客户端返回成功。

Mysql - 主从复制方式

mysql复制主要有三种方式：基于SQL语句的复制（statement-based replication,SBR），基于行的复制(row-based replication,RBR),混合模式复制（mixed-based replication,MBR）。分别对应了binlog的三种格式：
STATEMENT,ROW,MIXED。

基于GTID复制实现的工作原理

Mysql - Explain

explain + 查询sql可以生成一个执行计划，可以通过执行计划来进行sql优化。

mysql> explain select * from table;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (0.03 sec)

id

表示sql执行顺序标识，sql从大到小按顺序执行。

select_type

table

显示这一行的数据是关于哪张表的，有时不是真实的表名，看到的是derived+数字

type

访问类型：ALL index range ref eq_ref const system NULL 从左到右边，性能越好。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Mysql - 日志文件

Mysql中一共有6种日志文件

  1. 重做日志 - redo log
  2. 回滚日志 - undo log
  3. 二进制日志 - bin log
  4. 中继日志 - relay log
  5. 错误日志 - error log
  6. 慢查询日志 - slow query log

重做日志 - redo log

回滚日志 - undo log

二进制日志 - bin log

中继日志 - relay log

错误日志 - error log

慢查询日志 - slow query log

上一篇 下一篇

猜你喜欢

热点阅读