MySQL 知识点

MySQL基础

什么是数据库，SQL，MySQL

• 数据库

  数据库是一种有效地管理大量的、安全的、并发的、关联的、一致的数据工具；而文件保存数据会存在安全问题，不利于查询和对数据的管理，也不利于存放海量数据；因此，引入了数据库的概念，数据库更便于我们去操纵和管理数据，做一些我们想要的改变。

• SQL

  一种用于操作数据库的查询语言

• MySQL

  MySQL 是一种关系型数据库，DBMS（DataBaseManagementSystem）数据库管理系统

  MySQL是开放源代码的。默认端口号是3306。

超键、候选键、主键、外键的概念？

• 超键：只要在关系中含有能唯一标识元组属性的集合就叫做超键

• 候选键：不含有多余属性，只含有能唯一标识元组的属性就叫做候选键

• 主键：用户从众多候选键中选出来的一个键就是主键

• 外键：相对于两个表或多张表而言。如果表R中属性K是其他表的主键，那么K在表R中称为外键。

三大范式

• 第一范式（1NF）：

  数据库表中的字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式。

• 第二范式（2NF）：

  在第一范式的基础上，要求实体的属性完全依赖于主关键字。即不能存在仅依赖主关键字一部分的属性。

• 第三范式：

  在第二范式的基础上，要求非主键列必须直接依赖于主键，不能存在传递依赖。比如一个表中有<学号，姓名，课程号，成绩>，其中成绩是由学号+课程号决定的，因此将表分为两个表<学号，姓名，课程号>，<学号，课程号，成绩>。

有关权限的表

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

binlog录入格式有哪些？

• Statement：只记录每条修改数据的SQL执行语句；但还必须记录每条语句在执行的时候的一些相关信息，确保所有语句能在slave得到和在master端执行时候相同的结果。但一些函数功能不能被复制。

• row：不记录SQL的上下语句信息，只记录那一条记录被修改成什么了；解决了Statement的问题，但单语句更新（删除）表的行数过多，会导致形成大量binlog。

• Mixedlevel：前两个的混合使用，一般的语句修改用Statement，一些函数复制等用row，根据执行的语句来选择使用哪种方式。

MySQL有哪些常见的数据类型？

• 整数：smallint（2字节，0 ~ 2^16 -1）、int（4字节，0 ~ 2^32-1）;
• 浮点数：float（4字节）、double（8字节）；
• 定点数：decimal(m，d)（M+2字节）；
• 日期类型：date（YYYY-MM-DD）、time（HH:MM:SS）、year（YYYY）、datetime（YYYY-MM-DD HH:MM:SS）、timestamp（YYYYMMDD HHMMSS）
• 字符型：char（0 ~ 255）、varchar（0 ~ 255）、text（0 ~ 65535(2^16-1)）、longtext（0 ~ 2^32-1）

事务相关

什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

事物的四大特性(ACID)介绍一下？

image

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

• 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来哪些问题？

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

• **脏读（Dirty read）: **当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

• **不可重复读（Unrepeatableread）: **指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• **幻读（Phantom read）: **幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

**事务隔离级别有哪些?MySQL的默认隔离级别

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看

mysql> SELECT @@tx_isolation;

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

索引相关

索引使用场景

不需要索引

当表记录较少、经常增删改的表或数据重复且分布平均的表字段不需要使用索引，用了反而占存储空间；

需要建立索引

1.主键自动建立唯一索引；

2.频繁作为查询的条件的字段应该创建索引；

3.查询中与其他表关联的字段，使用外键关系建立索引；

4.查询中排序的字段，通过索引可以大大提高排序的速度；

5.查询中统计或者分组字段；

索引有哪些类型？

1. 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一
2. 聚集索引：定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
3. 非聚集索引：唯一索引、普通索引、主键索引、全文索引
4. UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
5. INDEX(普通索引)：允许出现相同的索引内容
6. PROMARY KEY(主键索引)：不允许出现相同的值
7. fulltext index(全文索引)：可以针对值中的某个单词，但效率不是很好

创建索引的原则？

1. 在经常用作过滤器的字段上建立索引；

2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；

3. 在不同值较少的字段上不必要建立索引，如性别字段，性别字段无非男女两种值，区分度不好，建立索引效果不好，要选择区分度高的字段；

4. 对于经常存取的列避免建立索引；

5. 用于联接的列（主健/外健）上建立索引；

索引是怎么优化查询的？/索引为什么用B+树？

1. B+树更有利于对数据库的扫描 。B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题，而 B+ 树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的 range query，B+树有着更高的性能。
2. B+树的磁盘读写代价更低 。B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字 数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 I/O 读写次数也就降低了。
3. B+树的查询效率更加稳定 。由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+树特点

1. 所有叶子节点中的关键字按大小顺序排序；

2. 相邻的叶子节点按顺序链接（相当于构成一个顺序链表）；

3. B+树的内部节点只存放键，不存放值，叶子节点同时存放键和值；

B树的特点

1. B树的内部节点和叶子节点都存放键和值；

2. B树的叶子节点各自独立

B树与B+树的区别

1. 内部节点中，关键字的个数与其子树的个数相同，不像 B 树种，子树的个数总比关键字个数多 1 个 ；

2. 所有指向文件的关键字及其指针都在叶子节点中，不像 B 树，有的指向文件的关 键字是在内部节点中。换句话说，B+树中，内部节点仅仅起到索引的作用；

3. B+在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支，B+为了找到这个关键字的指针；

为什么索引能提高查询速度

先从 MySQL 的基本存储结构说起

MySQL的基本存储结构是页 (记录都存在页里边) ：

image image

• 各个数据页可以组成一个双向链表

• 每个数据页中的记录又可以组成一个单向链表

- 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

所以说，如果我们写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页

2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

索引做了些什么可以让我们查询加快速度呢？****其实就是将无序的数据变成有序(相对)：

image

要找到id为8的记录简要步骤：

image

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

最左匹配原则最左前缀原则？

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQLS.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

Mysql如何为表字段添加索引？

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

存储引擎

存储引擎MyISAM与InnoDB区别？

大多数时候我们使用的都是 InnoDB 存储引擎，
但是在某些情况下使用 MyISAM 也是合适的比如你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题, 读密集的情况下。（如果你不介意 MyISAM 崩溃回复问题的话）。

1. InnoDN（5.5版本之后）支持事务处理、外键与行级锁，而MyISAM（5.1版本之前）不支持；

2. 是否支持行级锁 :

   MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

3. 是否支持事务和崩溃后的安全恢复：

   MyISAM 强调的是性能，每次查询具有原子性,其执行比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

4. 是否支持外键：

   MyISAM不支持，而InnoDB支持。

5. 是否支持MVCC ：

   仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

6. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎，该引擎是为了处理大数据容量的数据库系统；

7. MyISAM的读性能比InnoDB好，但修改性能比InnoDB差；

8. MyISAM可被压缩，存储空间较小，InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引；

9. MyISAM支持全文类型索引，而InnoDB不支持；

InnoDB引擎的4大特性

1. 插入缓冲（insert buffer)
2. 二次写(double write)
3. 自适应哈希索引(ahi)
4. 预读(read ahead)

常用命令

查看MySQL提供的所有存储引擎

mysql> show engines;

image

从上图我们可以查看出 MySQL 当前默认的存储引擎是InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

查看MySQL当前默认的存储引擎

我们也可以通过下面的命令查看默认的存储引擎。

mysql> show variables like '%storage_engine%';

查看表的存储引擎

show table status like "table_name" ;

image

锁

为了防止数据库中的数据被多个用户共享访问，而影响到数据的一致性和有效性，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁。因此引入了锁机制，用于管理对共享资源的并发访问。

有两种锁，一种是Latch闩(shuan)锁（轻量级的锁），它要求锁定的时间非常短，若持续时间长，则其应用性能会很差。在InnoDB引擎中，Latch分为rwlock（读写锁）和mutex（互斥锁），其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

还有一种是Lock，它的对象是事务，用来锁定数据库中的对象，如表、页、行。并且lock的对象commit或rollback后进行释放（不同事物隔离级别释放的时间可能不同）。
在MySQL中的引擎中主要是Lock。

锁的粒度

Lock锁根据粒度主要分为表锁、行锁、页锁，不同的存储引擎拥有的锁粒度都不同。

1. 表锁：表级锁就是一次会将整个表进行锁定，是个存储引擎中最大颗粒度的锁机制；它的特点是实现逻辑简单，资源消耗较少，获取锁和释放锁的速度很快，很好的解决了死锁问题。使用表锁的主要是MyISAM、MEMORY，CSV等一些非事务性存储引擎。

2. 行锁：MySQL中锁定粒度最小的锁机制，特点是发生锁定资源争用的概率小，能给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能，但资源消耗较大。使用行锁的主要是InnoDB引擎。

3. 页锁：它的锁定颗粒度介于表锁和行锁之间，它的并发能力与资源开销也是介于两者之间，另外，它与行锁一样，会发生死锁。使用页锁的主要是BerkeleyDB存储引擎。

锁的类别

从类别上锁分为：共享锁（S Lock）和排它锁（X Lock）。

• 共享锁、读锁

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

• 排他锁、写锁

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

当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

锁的粒度具体取决于存储引擎，InnoDB实现了行锁，页锁，表锁；他们的资源开销从大到小，并发性能也从大到小。

乐观锁与悲观锁的区别

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

乐观锁常见的两种实现方式

乐观锁一般会使用版本号机制或CAS算法实现。

1. 版本号机制

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

举一个简单的例子： 假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 $100 。

1. 操作员 A 此时将其读出（ version=1 ），并从其帐户余额中扣除 100-$50 ）。

2. 在操作员 A 操作的过程中，操作员B 也读入此用户信息（ version=1 ），并从其帐户余额中扣除 100-$20 ）。

3. 操作员 A 完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=$50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 。

4. 操作员 B 完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=$80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 提交版本必须大于记录当前版本才能执行更新 “ 的乐观锁策略，因此，操作员 B 的提交被驳回。

这样，就避免了操作员 B 用基于 version=1 的旧数据修改的结果覆盖操作员A 的操作结果的可能。

2. CAS算法

即compare and swap（比较与交换），是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。CAS算法涉及到三个操作数

• 需要读写的内存值 V

• 进行比较的值 A

• 拟写入的新值 B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

乐观锁的缺点

ABA 问题是乐观锁一个常见的问题

1 ABA 问题

如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。

JDK 1.5 以后的 AtomicStampedReference 类就提供了此种能力，其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用，并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值。

2 循环时间长开销大

自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。 如果JVM能支持处理器提供的pause指令那么效率会有一定的提升，pause指令有两个作用，第一它可以延迟流水线执行指令（de-pipeline）,使CPU不会消耗过多的执行资源，延迟的时间取决于具体实现的版本，在一些处理器上延迟时间是零。第二它可以避免在退出循环的时候因内存顺序冲突（memory order violation）而引起CPU流水线被清空（CPU pipeline flush），从而提高CPU的执行效率。

3 只能保证一个共享变量的原子操作

CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

锁机制与InnoDB锁算法

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

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

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

表级锁和行级锁对比：

• 表级锁： Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB引擎的行锁是怎么实现的？

InnoDB是通过索引来实现行锁的。

例如：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 锁定一个范围，包含记录本身

死锁怎么解决？

1. 对于事物之间由于资源访问顺序导致的死锁，则可以约定以相同的顺序访问表；

2. 对于并发修改同一记录导致的死锁，可以使用乐观锁或悲观锁进行控制；

3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

4. 避免多个事务同时执行表级锁，对于有全表扫描的SQL语句，建立相应的索引进行优化。

相关知识点：

• innodb对于行的查询使用next-key lock
• Next-locking keying为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将next-key lock降级为record key
• Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
• 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

视图

视图（View）是从一个或多个基本表（或视图）导出的表，它与基本表不同，是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据；当基本表中的数据发生变化，从视图中查询出的数据也就随之改变；单表视图一般用于查询和修改，会改变基本表的数据；多表视图一般用于查询，不会改变基本表的数据。

视图的作用

1. 简化用户的操作；

2. 使用户能以多种角度看待同一数据；

3. 对重构数据库提供了一定程度的逻辑独立性；

4. 能够对机密数据提供安全保护；

当需要多次重复输入相同的语句、需要不同的表字段聚合，进行信息重组、安全需要或需要兼容老的表等情况，就可以利用视图来简化sql查询，提高开发效率。

不过事物皆有两面性，视图也存在它的缺点。视图的性能差，数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间；视图也存在着修改限制，当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

存储过程与函数的区别？

存储过程是用户定义的一系列SQL语句的集合，涉及特定表或其他对象（表、视图、索引、序列、目录、同义词、数据库用户、存储过程、函数、触发器等）的任务，用户可以调用存储过程；而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

1. 存储过程功能复杂强大，可以执行包括修改表等一系列数据库操作；而函数实现的功能针对性比较强；

2. 对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类；

3. 存储过程可以使用非确定函数，而不允许用户在定义函数非确定函数；

4. 存储过程一般作为一个独立的部分来执行（EXECUTE语句执行），而函数可以作为查询语句的一部分来调用（SELECT调用）；

触发器

触发器（trigger）是一种特殊的存储过程，它可以在对一个表上进行INSERT、UPDATE和DELETE操作中的任一种或几种操作时被自动调用执行。它SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法。

当需要实现多个表的级联更改或实时监控表中字段的更改作出处理时就可以使用触发器来完成。

例如在数据库中有两个表，一个用户信息表，一个账号表，账号表用来账号的注册，密码的修改等操作，信息表用来保存用户 的信息（姓名、年龄等）；当账号在账号表中注册成功后，如何在用户表中同时添加用户的相关信息以保证数据的准确性和实时性呢。通常我们在注册成功时可以insert用户的信息到用户信息表中，但如果用户数量过多，SQL语句则过于繁琐，怎么办。这时我们就可以使用触发器。

MySQL中的触发器种类

Before Insert、After Insert、Before Update、After Update、Before Delete、After Delete

大表优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

当只有一行数据时使用limit 1。查询时如果已知只会得到一条数据，则加上limit 1会增加性能。因为mysql数据库引擎会在找到一条结果后停止搜索，而不是继续查询下一条或直至所有记录查询完毕。

2. 选择正确的数据库引擎

MySQL中都MyISAM和InnoDB都各有利弊，因此选择正确的引擎很重要。MyISAM适用于读多写少的应用，而且它对于select count(*)类似操作速度非常快；InnoDB是一个复杂的存储引擎，它在写多的应用中占优势，并且它支持很多高级应用（事务等）。

3. 操作符

• 用not exists代替not in

  Not exists用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。Not in是最慢的方式要同每条记录比较，在数据量比较大的操作中不建议使用这种方式。

• 尽量不采用不利于索引的操作符。如：in、not in、is null、is not null、<> 等

4. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

在数据库并发大的情况下，最好的做法就是进行横向扩展，增加机器，以提升抗并发能力，而且还兼有数据备份功能。

5. 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。

• 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

• 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

image

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

• 中间件代理： 在应用和数据中间加了一个代理层。****分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

Reference

[1] 程序猿DD
https://mp.weixin.qq.com/s__biz=MzAxODcyNjEzNQ==&mid=2247487869&idx=1&sn=394772e8b625d14234c8b8db42325100&chksm=9bd0bce5aca735f3c13b205ef509b570f15fe5a07a843051a29d9957a51921200f74b59cbebd&scene=0&xtrack=1&key=fe55a52768475489de36012b36160a32b2891c91f3dc73ab21751b983316809ecc21d3184c1d5f779f18549cfdc87081edd2f21642b0c9f9f69d3486f87e34373505ca0e9e26668d88e6ff569d8b508a&ascene=1&uin=MjgwMTEwNDQxNg%3D%3D&devicetype=Windows-QQBrowser&version=6103000b&lang=zh_CN&pass_ticket=frEhJY3v9NTTpw3DipCMvKWL8ExMeyX1CTVBogmdI1qPvg7CVAKpX%2BL5%2BI%2FPMr7o

[2] 盘点面试必备知识点——数据库