MySQL篇

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

MYSQL的存储引擎是InnoDB

4、默认隔离级别-RR

默认隔离级别：可重复读；

同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改；

可重复读是有可能出现幻读的，如果要保证绝对的安全只能把隔离级别设置成SERIALIZABLE；这样所有事务都只能顺序执行，自然不会因为并发有什么影响了，但是性能会下降许多。

第二种方式，使用MVCC解决快照读幻读问题（如简单select），读取的不是最新的数据。维护一个字段作为version，这样可以控制到每次只能有一个人更新一个版本。

select id from table_xx where id = ? and version = Vupdate id from table_xx where id = ? and version = V+1

第三种方式，如果需要读最新的数据，可以通过GapLock+Next-KeyLock可以解决当前读幻读问题，

select id from table_xx where id > 100 for update;select id from table_xx where id > 100 lock in share mode;

5、RR和RC使用场景

事务隔离级别RC(read commit)和RR（repeatable read）两种事务隔离级别基于多版本并发控制MVCC(multi-version concurrency control）来实现。

6、行锁，表锁，意向锁

InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

InnoDB按照不同的分类的锁：

共享/排它锁(Shared and Exclusive Locks)：行级别锁，

意向锁(Intention Locks)，表级别锁

间隙锁(Gap Locks)，锁定一个区间

记录锁(Record Locks)，锁定一个行记录

表级锁：（串行化）

Mysql中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁：（RR、RC）

Mysql中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种：

记录锁（Record Lock）

对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

间隙锁（Gap Lock）

对索引项之间的“间隙”加锁，锁定记录的范围，不包含索引项本身，其他事务不能在锁范围内插入数据。

Next-key Lock

锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

共享锁（ shared lock, S 读锁）

锁允许持有锁读取行的事务。加锁时将自己和子节点全加S锁，父节点直到表头全加IS锁

排他锁（ exclusive lock， X 写锁）

锁允许持有锁修改行的事务。加锁时将自己和子节点全加X锁，父节点直到表头全加IX锁

意向共享锁（intention shared lock, IS）

事务有意向对表中的某些行加共享锁（S锁）

IS锁，表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（intention exclusive lock, IX）

事务有意向对表中的某些行加排他锁（X锁）

IX锁，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

【扩展：为什么需要意向锁呢？】
有这么一个场景，当一个事务想要给一张表加上表锁，其前提是没有其他任何事务已经锁定了这张表的任意一行数据。
也就是说，需要去全表扫描，看是否有哪一行数据被其他事务锁定了，但是这非常低效。
因此引入了意向锁，意向锁相当于一个标识，表示是否有其他事务锁定该表的其他行数据。

7、MVCC多版本并发控制

MVCC是一种多版本并发控制机制，通过事务的可见性看到自己预期的数据，能降低其系统开销.（RC和RR级别工作）

InnoDB的MVCC,是通过在每行记录后面保存系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的，防止幻读的产生。

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

3.简单的select快照度不会加锁，删改及select for update等需要当前读的场景会加锁

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。客观上，mysql使用的是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

版本链

在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

trx_id

这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer

每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

8.索引

索引的分类

1.按功能划分

普通索引
唯一性索引
主键索引
全文索引

普通索引就是最最基础的索引，这种索引没有任何的约束作用，它存在的主要意义就是提高查询效率。普通索引创建方式如下：

CREATE TABLE `user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(64) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

name 字段就是一个普通索引（括号外面的是索引名，里边的是索引的字段）。

唯一性索引则在普通索引的基础上增加了数据唯一性的约束，一张表中可以同时存在多个唯一性索引，唯一性索引创建方式如下：

CREATE TABLE `user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(64) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

name 字段就是唯一性索引。

主键索引则是在唯一性索引的基础上又增加了不为空的约束（换言之，添加了唯一性索引的字段，是可以包含 NULL 值得），即 NOT NULL+UNIQUE，一张表里最多只有一个主键索引，当然一个主键索引中可以包含多个字段。

全文索引其实我们很少在 MySQL 中用，如果项目中有做全文索引的需求，一般可以通过 Elasticsearch 或者 Solr 来做，目前比较流行的就是 Elasticsearch 了

全文索引的创建方式如下：

CREATE TABLE `user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(64) DEFAULT NULL,  PRIMARY KEY (`id`),  FULLTEXT KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

name 字段就是全文索引。

2. 按照物理实现划分

聚簇索引
非聚簇索引

聚集索引在存储的时候，可以按照主键（不是必须，看情况）来排序存储数据，B+Tree 的叶子结点就是完整的数据行，查找的时候，找到了主键也就找到了完整的数据行。如下图，在聚集索引中，叶子结点保存了每一行的数据。

1、Innodb和Myisam引擎

Myisam：支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件

Innodb：支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件

2、哈希索引

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能

3、B+树索引

优点：

B+树的磁盘读写代价低，更少的查询次数，查询效率更加稳定，有利于对数据库的扫描

B+树是B树的升级版，B+树只有叶节点存放数据，其余节点用来索引。索引节点可以全部加入内存，增加查询效率，叶子节点可以做双向链表，从而提高范围查找的效率，增加的索引的范围

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树与B+树可以有多个子女，从几十到上千，可以降低树的高度。

磁盘预读原理：将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

4、创建索引

CREATE  [UNIQUE | FULLTEXT]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；说明：UNIQUE:可选。表示索引为唯一性索引。FULLTEXT:可选。表示索引为全文索引。INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。索引名:可选。给创建的索引取一个新名称。字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。注：索引方法默认使用B+TREE。

5、聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（主键索引）

主键（ PRIMARY KEY ）在创建是若未指定需要定义为非聚簇索引则，默认为聚簇索引，改索引和主键在表中都只能有一个

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（辅助索引）

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

6、最左前缀问题

最左前缀原则主要使用在联合索引中，联合索引的B+Tree是按照第一个关键字进行索引排列的。

联合索引的底层是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

1.采用（>、<、between、like）等进行匹配都会导致后面的列无法走索引，因为通过以上方式匹配到的数据是不可知的。

比如：建立联合索引（a,b,c,d），如果查询a=3 and b=4 and c>5 and d=6，d是用不到做索引的，但是如果建立（a,b,d,c），则都可以用到，abd的顺序可以任意调整

2.= 和 in 可以乱序

比如：a = 1 and b = 2 and c=3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

SQL查询

1、SQL语句的执行过程

查询语句：

select * from student  A where A.age='18' and A.name='张三';

结合上面的说明，我们分析下这个语句的执行流程：

①通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限

②Mysql8.0之前开看是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；

③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id='1'

④查询优化器生成执行计划。根据索引看看是否可以优化

⑤查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

2、回表查询和覆盖索引

普通索引（唯一索引+联合索引+全文索引）需要扫描两遍索引树

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引：主键索引==聚簇索引==覆盖索引

如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。

实现覆盖索引：常见的方法是，将被查询的字段，建立到联合索引里去。

3、Explain及优化

参考：https://www.jianshu.com/p/8fab76bbf448

mysql> explain select * from staff;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | staff | ALL  | NULL          | 索引  | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set

索引优化：

①最左前缀索引：like只用于'string%'，语句中的=和in会动态调整顺序

②唯一索引：唯一键区分度在0.1以上

③无法使用索引：!= 、is null 、 or、>< 、（5.7以后根据数量自动判定）in 、not in

④联合索引：避免select * ，查询列使用覆盖索引

SELECT uid From user Where gid = 2 order by ctime asc limit 10ALTER TABLE user add index idx_gid_ctime_uid(gid,ctime,uid) #创建联合覆盖索引，避免回表查询

语句优化：

①char固定长度查询效率高，varchar第一个字节记录数据长度

②应该针对Explain中Rows增加索引

③group/order by字段均会涉及索引

④Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决

select * from mytbl order by id limit 100000,10  改进后的SQL语句如下：select * from mytbl where id >= ( select id from mytbl order by id limit 100000,1 ) limit 10select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

⑤count会进行全表扫描，如果估算可以使用explain

⑥delete删除表时会增加大量undo和redo日志，确定删除可使用trancate

表结构优化：

①单库不超过200张表

②单表不超过500w数据

③单表不超过40列

④单表索引不超过5个

数据库范式 ：

①第一范式（1NF）列不可分割（每一列都具有原子性）

②第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]（其他列都于主键有关）

③第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]（其他列都于主键直接相关）

配置优化：

配置连接数、禁用Swap、增加内存、升级SSD硬盘

4、JOIN查询

left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录

inner join(等值连接) 只返回两个表中关联字段相等的行

集群

1、主从复制过程

MySQl主从复制：

原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
优点：
- 作为备用数据库，并且不影响业务
- 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

binlog记录格式：statement、row、mixed

基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题。

大部分互联网的业务都是“读多写少”的场景，数据库层面，读性能往往成为瓶颈。业界通常采用“一主多从，读写分离，冗余多个读库”的数据库架构来提升数据库的读性能。