关于java:MySQL重要知识点mysql面试题总结下

为什么索引能进步查问速度

先从 MySQL 的根本存储构造说起
MySQL 的根本存储构造是页(记录都存在页里边)：

• 各个数据页能够组成一个双向链表
• 每个数据页中的记录又能够组成一个单向链表

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

2. 以其余列 (非主键) 作为搜寻条件：只能从最小记录开始顺次遍历单链表中的每条记录。

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

• 定位到记录所在的页：须要遍历双向链表，找到所在的页
• 从所在的页内中查找相应的记录：因为不是依据主键查问，只能遍历所在页的单链表了

很显著，在数据量很大的状况下这样查找会很慢！这样的工夫复杂度为 O（n）。

索引做了些什么能够让咱们查问加快速度呢？其实就是将无序的数据变成有序(绝对)：

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

很显著的是：没有用索引咱们是须要遍历双向链表来定位对应的页，当初通过“目录”就能够很快地定位到对应的页上了！（二分查找，工夫复杂度近似为 O(logn)）

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

什么是最左前缀准则？

MySQL 中的索引能够以肯定程序援用多列，这种索引叫作联结索引。如 User 表的 name 和 city 加联结索引就是(name,city)，而最左前缀准则指的是，如果查问的时候查问条件准确匹配索引的右边间断一列或几列，则此列就能够被用到。如下：

select * from user where name=xx and city=xx ; //能够命中索引
select * from user where name=xx ; // 能够命中索引
select * from user where 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 )

存储引擎

一些常用命令

查看 MySQL 提供的所有存储引擎
mysql> show engines;

从上图咱们能够查看出 MySQL 以后默认的存储引擎是 InnoDB, 并且在 5.7 版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 反对事务。

查看 MySQL 以后默认的存储引擎

咱们也能够通过上面的命令查看默认的存储引擎。

mysql> show variables like ‘%storage_engine%’;

查看表的存储引擎

show table status like “table_name” ;

MyISAM 和 InnoDB 区别

MyISAM 是 MySQL 的默认数据库引擎（5.5 版之前）。尽管性能极佳，而且提供了大量的个性，包含全文索引、压缩、空间函数等，但 MyISAM 不反对事务和行级锁，而且最大的缺点就是解体后无奈平安复原。不过，5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。

大多数时候咱们应用的都是 InnoDB 存储引擎，然而在某些状况下应用 MyISAM 也是适合的比方读密集的状况下。（如果你不介意 MyISAM 解体回复问题的话）。

两者的比照：

1. 是否反对行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 反对行级锁(row-level locking) 和表级锁, 默认为行级锁。
2. 是否反对事务和解体后的平安复原：MyISAM 强调的是性能，每次查问具备原子性, 其执行数度比 InnoDB 类型更快，然而不提供事务反对。然而 InnoDB 提供事务反对事务，内部键等高级数据库性能。具备事务 (commit)、回滚(rollback) 和解体修复能力 (crash recovery capabilities) 的事务平安 (transaction-safe (ACID compliant)) 型表。
3. 是否反对外键：MyISAM 不反对，而 InnoDB 反对。
4. 是否反对 MVCC：仅 InnoDB 反对。应答高并发事务, MVCC 比单纯的加锁更高效;MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC 能够应用 乐观 (optimistic) 锁 和 乐观 (pessimistic) 锁来实现; 各数据库中 MVCC 实现并不对立。举荐浏览：MySQL-InnoDB-MVCC 多版本并发管制
5. ……
   《MySQL 高性能》下面有一句话这样写到:

不要轻易置信“MyISAM 比 InnoDB 快”之类的经验之谈，这个论断往往不是相对的。在很多咱们已知场景中，InnoDB 的速度都能够让 MyISAM 可望不可即，尤其是用到了聚簇索引，或者须要拜访的数据都能够放入内存的利用。
个别状况下咱们抉择 InnoDB 都是没有问题的，然而某事状况下你并不在乎可扩大能力和并发能力，也不须要事务反对，也不在乎解体后的平安复原问题的话，抉择 MyISAM 也是一个不错的抉择。然而个别状况下，咱们都是须要思考到这些问题的。

乐观锁与乐观锁的区别

乐观锁
总是假如最坏的状况，每次去拿数据的时候都认为他人会批改，所以每次在拿数据的时候都会上锁，这样他人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程应用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比方行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。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。

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

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

操作员 A 实现了批改工作，将数据版本号加一（version=2），连同帐户扣除后余额（balance=$50），提交至数据库更新，此时因为提交数据版本大于数据库记录以后版本，数据被更新，数据库记录 version 更新为 2。

操作员 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 中锁定 粒度最小 的一种锁，只针对以后操作的行进行加锁。行级锁能大大减少数据库操作的抵触。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会呈现死锁。
  具体内容能够参考：Mysql 锁机制简略理解一下

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

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

相干知识点：

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

大表优化

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

1. 限定数据的范畴
务必禁止不带任何限度数据范畴条件的查问语句。比方：咱们当用户在查问订单历史的时候，咱们能够管制在一个月的范畴内；

2. 读 / 写拆散
经典的数据库拆分计划，主库负责写，从库负责读；

3. 垂直分区
依据数据库外面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的根本信息，能够将用户表拆分成两个独自的表，甚至放到独自的库做分库。

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

• 垂直拆分的长处：能够使得列数据变小，在查问时缩小读取的 Block 数，缩小 I / O 次数。此外，垂直分区能够简化表的构造，易于保护。
• 垂直拆分的毛病：主键会呈现冗余，须要治理冗余列，并会引起 Join 操作，能够通过在应用层进行 Join 来解决。此外，垂直分区会让事务变得更加简单；

4. 程度分区
放弃数据表构造不变，通过某种策略存储数据分片。这样每一片数据扩散到不同的表或者库中，达到了分布式的目标。程度拆分能够撑持十分大的数据量。

程度拆分是指数据表行的拆分，表的行数超过 200 万行时，就会变慢，这时能够把一张的表的数据拆成多张表来寄存。举个例子：咱们能够将用户信息表拆分成多个用户信息表，这样就能够防止繁多表数据量过大对性能造成影响。

程度拆分能够反对十分大的数据量。须要留神的一点是：分表仅仅是解决了繁多表数据过大的问题，但因为表的数据还是在同一台机器上，其实对于晋升 MySQL 并发能力没有什么意义，所以 程度拆分最好分库。

程度拆分可能 反对十分大的数据量存储，利用端革新也少，但 分片事务难以解决，跨节点 Join 性能较差，逻辑简单。《Java 工程师修炼之道》的作者举荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度，个别的数据表在优化切当的状况下撑持千万以下的数据量是没有太大问题的。如果切实要分片，尽量抉择客户端分片架构，这样能够缩小一次和中间件的网络 I /O。

上面补充一下数据库分片的两种常见计划：

• 客户端代理：分片逻辑在利用端，封装在 jar 包中，通过批改或者封装 JDBC 层来实现。当当网的 Sharding-JDBC、阿里的 TDDL 是两种比拟罕用的实现。
• 中间件代理：在利用和数据两头加了一个代理层。分片逻辑对立保护在中间件服务中。咱们当初谈的 Mycat、360 的 Atlas、网易的 DDB 等等都是这种架构的实现。