MySql相关知识点(用户管理)

数据库

Mysql知识点(一)

MySQL 有关权限的表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

索引

Mysql 中聚簇索引和非聚簇索引的区别？

Innodb 的次索引指向对主建的引用 (聚簇索引)

MyISAM 的次索引和主索引都指向物理行(非聚簇索引)

  • 聚簇索引

​ 聚簇索引是对磁盘上实际数据重新组织以按指定的一个或者多个列的的值排序的算法.特点是存储数据的顺序和索引顺序一致, 一般情况下主键会默认创建聚簇索引 且一张表只允许存在一个聚簇索引(理由: 数据一旦存储,顺序只能有一种)

  • 聚簇索引和非聚簇索引的区别是:

    聚簇索引（innodb）的叶子节点就是数据节点 而非聚簇索引（myisam）的叶子节点仍然是索引文件，只是这个索引文件中包含指向对应数据块的指针。

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

InnoDB的二级索引的叶子节点存放的是KEY字段加主键值, 因此 通过二级索引首先查询到的是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。

MyISAM的二级索引叶子节点存放的还是列值和行号的组合, 叶子节点中保存的是数据的物理地址,所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键

InnoDB 的主索引文件上, 直接存放该行数据 称为聚簇索引 次索引指向对主键的引用

MyISAM中 主索引和次索引 都指向物理行(磁盘位置)

索引的使用场景

1). 应该创建索引的场景

  • 主键应该创建主键索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中需要与其他表进行关联的字段应该创建索引
  • 需要排序的字段应该创建索引
  • 需要统计或者分组的字段应该创建索引
  • 优先考虑创建复合索引

2). 不应该创建索引的场景

  • 数据记录较少的表
  • 经常需要增删改操作的字段
  • 数据记录重复较多且分布平均的字段

索引的选择性是指索引列中不同值的数目与表中记录总数的比

索引的选择性越接近于1，创建索引的价值就越高。反之就越低。

百万级别或者以上的数据如何删除

方法一:

索引是单独的文件, 增删改时,当存在索引,会消耗额外的 IO, 删除速度和索引数量成正比

  • 删除索引
  • 删除需要删除的数据
  • 重新建立索引

这个方法有一个明显的缺点,就是在正式环境这个表如果访问频率比较高的话,删除索引后有大量的SQL查询会导致IO和CPU特别高

方法二:

将删除任务拆分为一次删除1w条， 然后把删除任务重新压入的异步任务队列里面。

最左前缀原则

在 mysql 建立联合索引时会遵循最左前缀匹配的原则既最左优先 在检索数据时从联合索引的最左边开始匹配

1
KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

1
2
SELECT * FROM test WHERE col1="1" AND clo2="2"  AND clo4="4"

上面这个查询语句执行时会依照最左前缀匹配原则 检索时会使用索引(col1,col2)进行数据匹配

事务

数据库事务

​ 数据库事务可以保证多个数据库的操作 构成一个逻辑上的整体 构成这个逻辑上的整体的这些数据库操作遵循:要么全部执行成功, 要么全部不执行。

1
2
3
4
5
6
# 开启一个事务
START TRASACTION
# 多条 SQL语句
SQL1, SQL2
# 提交事务
COMMIT

事务的四大特性(ACID)

ACID 指 数据库事务正确执行的四个基本要素的缩写. 包含: 原子性(Atomicity), 一致性(Consistency),隔离性(lsolation),持久性(Durability)。一个支持事务的数据库必须具有这四种特性 否则在事务过程当中无法保证数据的正确性

脏读 幻读 不可重复读

  1. 脏读

    脏读是指在一个事务处理过程里读取了另一个未提交的数据

    当一个事务正在多次修改某个数据 而在这个事务中多次修改都还未提交 这时一个并发的事务访问该数据,就会造成两个事务得到的数据不一致,如下:

    1
    2
    update account set money=money+100 where name='B'; (此时A通知B)
    update account set money=money - 100 where name='A';

    当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

  2. 不可重复读 不可重复读是指在对数据库中的某个数据, 一个事务范围内多次查询却返回了不同的数据值, 这是由于在查询间隔,被另一个事务修改并提交了, 例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库, 事务T1再次读取该数据就得到了不同的结果 发生了不可重复读

​ 不可重复读和脏读的区别是 脏读是某一个事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。在某些情况下, 不可重复读并不是问题, 比如我们多次查询某个数据当然以最后查询得到的结果为主 但在另一些情况下就有可能发生问题. 例如对于同一个数据A 和 b 依次查询就可能不同 A和B 就可能打起来

  1. 虚读(幻读)

​ 幻读是事务非独立执行时发生的一种现象. 例如事务T1对一个表中所有的行的某个数据项从 1修改到2 的操作, 这时事务T2又对这个表中插入一行数据项 而这个数据项的数值还是为 1 并且提交给数据库 而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的, 就好像产生幻觉一样,这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体（比如数据的个数）。

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销

事务的隔离级别

Mysql 的事务隔离级别一共有四个 分别是读未提交, 读已提交 可重复读以及串行化。

MySQL的隔离级别的作用就是让事务之间互相隔离,互不影响 这样可以保证事务的一致性

隔离级别比较: 可串行化>可重复读>读已提交>读未提交

隔离级别对性能的影响比较: 可串行化>可重复读>读已提交>读未提交

由此看出, 隔离级别越高, 所需要消耗的MySQL 性能越大(如果事务并发严重性), 为了平衡二者, 一般建议设置的隔离级别为可重复读,MySQL默认的隔离级别也是可重复读.

隔离级别的实现原理

这里使用MySQL的默认隔离级别（可重复读）来进行说明 隔离级别的实现原理。

每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志undo log）。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作），可以回到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

  • 回滚操作日志（undo log）什么时候删除？

MySQL会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

  • 什么时候不需要了？

当系统里么有比这个回滚日志更早的read-view的时候。

分布式事务

分布式事务典型场景:银行跨行转账业务是一个典型分布式事务场景，假设A需要跨行转账给B，那么就涉及两个银行的数据，无法通过一个数据库的本地事务保证转账的正确性，只能够通过分布式事务来解决。

将服务拆分为微服务时, 遇见类似需要分布式事务的场景非常多,虽然微服务最佳实践建议尽量规避分布式事务，但是在很多业务场景，分布式事务是一个绕不开的技术问题。

分布式事务模式常见的有XA、TCC、SAGA、可靠消息。

  1. 两阶段提价/XA

XA是由X/Open组织提出的分布式事务的规范, XA规范主要定义了(全局)事务管理器(TM)和(局部)资源管理器(RM)之间的接口.本地的数据库如mysql在XA中扮演的是RM角色

XA 一共分为两阶段:

​ 第一阶段(prepare)： 即所有的参与者RM准备执行事务并锁住需要的资源. 参与者 ready后 向TM报告已准备就绪。

​ 第二阶段(commit//rollback): 当事务管理者(TM)确认所有参与者(RM)都ready后 向所有参与者发送 commit命令.

目前主流的数据库基本都支持XA事务, 包括Mysql oracle sqlserver postgre

  1. TCC事务方案

TCC方案其实是XA提交的一种改进, 其将整个业务逻辑的每个分支显式的分成Try Confirm Cancel 三个操作, Try部分完成业务的准备工作. confirm部分完成业务的提交, cancel部分完成事务的回滚。

  1. SAGA 事务方案

​ Saga和TCC一样, 也是最终一致性事务, 柔性事务。Saga的本质就是把一个长事务分隔成一个个小的事务,每个事务都包含一个执行模块和补偿模块. Saga 没有 Try 直接提交事务, 可能出现脏读的情况, 在某些对一致性要求较高的场景下, 是不可接受的。

在启动一个Saga事务时, 事务管理器会告诉第一个Saga参与者, 也就是子事务, 去执行本地事务。事务完成之后 Saga的会按照执行顺序调用Saga的下一个参与的子事务。这个过程会一直持续到Saga事务执行完毕

  1. 可靠消息

消息一致性方案是通过消息中间件保证上下游应用数据操作的一致性.基本思路是将本地操作和发送消息放在一个本地事务中, 保证本地操作和消息发送要么两者都成功或者都失败. 下游应用向消息系统订阅该消息 收到消息后执行相应的操作。

锁

读写锁

在处理并发读或者写时, 可以通过实现一个由两种类型组成的锁系统来解决问题.这两种类型的锁通常被称为共享锁和排他锁, 也叫读锁和写锁,读锁是共享的,相互不阻塞. 多个客户在同一时刻可以同时读取同一个资源而不互相干扰. 写锁则是排他的. 也就是说一个写锁会阻塞其他的写锁和读锁.确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL 会通过锁定防止其他用户读取同一数据。写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但是读锁不能插入到写锁前面。

隔离级别锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

锁机制与InnoDB锁算法

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

行级锁，表级锁和页级锁对比

行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

MySQL 中 InnoDB 引擎的行锁

InnoDB是基于索引来完成行锁

1
select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

InnoDB存储引擎的锁的算法有三种

  • Record lock：单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 锁定一个范围，包含记录本身

死锁

死锁是指多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的现象。当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。

为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。

死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型系统这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。