关于mysql:MySQL灵魂拷问36题带你面试通关

大家好，我是大彬~

明天给大家分享MySQL 常考的面试题，看看你们能答对多少。

本期 MySQL 面试题的目录如下：

事务的四大个性？
事务隔离级别有哪些？
索引
- 什么是索引？
- 索引的优缺点？
- 索引的作用？
- 什么状况下须要建索引？
- 什么状况下不建索引？
- 索引的数据结构
- Hash 索引和 B + 树索引的区别？
- 为什么 B + 树比 B 树更适宜实现数据库索引？
- 索引有什么分类？
- 什么是最左匹配准则？
- 什么是汇集索引？
- 什么是笼罩索引？
- 索引的设计准则？
- 索引什么时候会生效？
- 什么是前缀索引？
常见的存储引擎有哪些？
MyISAM 和 InnoDB 的区别？
MVCC 实现原理？
快照读和以后读
共享锁和排他锁
大表怎么优化？
bin log/redo log/undo log
bin log 和 redo log 有什么区别？
讲一下 MySQL 架构？
分库分表
什么是分区表？
分区表类型
查问语句执行流程？
更新语句执行过程？
exist 和 in 的区别？
truncate、delete 与 drop 区别？
having 和 where 的区别？
什么是 MySQL 主从同步？
为什么要做主从同步？
乐观锁和乐观锁是什么？
用过 processlist 吗？

事务个性 ACID：原子性 （Atomicity）、 一致性 （Consistency）、 隔离性 （Isolation）、 持久性（Durability）。

原子性 是指事务蕴含的所有操作要么全副胜利，要么全副失败回滚。
一致性 是指一个事务执行之前和执行之后都必须处于一致性状态。比方 a 与 b 账户共有 1000 块，两人之间转账之后无论胜利还是失败，它们的账户总和还是 1000。
隔离性。跟隔离级别相干，如read committed，一个事务只能读到曾经提交的批改。
持久性 是指一个事务一旦被提交了，那么对数据库中的数据的扭转就是永久性的，即使是在数据库系统遇到故障的状况下也不会失落提交事务的操作。

先理解下几个概念：脏读、不可反复读、幻读。

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可反复读 是指在对于数据库中的某行记录，一个事务范畴内屡次查问却返回了不同的数据值，这是因为在查问距离，另一个事务批改了数据并提交了。
幻读是当某个事务在读取某个范畴内的记录时，另外一个事务又在该范畴内插入了新的记录，当之前的事务再次读取该范畴的记录时，会产生幻行，就像产生幻觉一样，这就是产生了幻读。

不可反复读和脏读的区别 是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可反复读则是读取了前一事务提交的数据。

幻读和不可反复读都是读取了另一条曾经提交的事务，不同的是不可反复读的重点是批改，幻读的重点在于新增或者删除。

事务隔离就是为了解决下面提到的脏读、不可反复读、幻读这几个问题。

MySQL 数据库为咱们提供的四种隔离级别：

Serializable (串行化)：通过强制事务排序，使之不可能互相抵触，从而解决幻读问题。
Repeatable read (可反复读)：MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可反复读的问题。
Read committed (读已提交)：一个事务只能看见曾经提交事务所做的扭转。可防止脏读的产生。
Read uncommitted (读未提交)：所有事务都能够看到其余未提交事务的执行后果。

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

索引是存储引擎用于进步数据库表的访问速度的一种 数据结构。

长处：

放慢数据查找的速度
为用来排序或者是分组的字段增加索引，能够放慢分组和排序的速度
放慢表与表之间的连贯

毛病：

建设索引须要 占用物理空间
会升高表的增删改的效率，因为每次对表记录进行增删改，须要进行 动静保护索引，导致增删改工夫变长

数据是存储在磁盘上的，查问数据时，如果没有索引，会加载所有的数据到内存，顺次进行检索，读取磁盘次数较多。有了索引，就不须要加载所有数据，因为 B + 树的高度个别在 2 - 4 层，最多只须要读取 2 - 4 次磁盘，查问速度大大晋升。

常常用于查问的字段
常常用于连贯的字段建设索引，能够放慢连贯的速度
常常须要排序的字段建设索引，因为索引曾经排好序，能够放慢排序查问速度

where条件中用不到的字段不适宜建设索引
表记录较少
须要常常增删改
参加列计算 的列不适宜建索引
区分度不高 的字段不适宜建设索引，如性别等

索引的数据结构次要有 B + 树和哈希表，对应的索引别离为 B + 树索引和哈希索引。InnoDB 引擎的索引类型有 B + 树索引和哈希索引，默认的索引类型为 B + 树索引。

B+ 树索引

B+ 树是基于 B 树和叶子节点程序拜访指针进行实现，它具备 B 树的平衡性，并且通过程序拜访指针来进步区间查问的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 别离是 key_i 和 key_i+1，则该指针指向节点的所有 key 大于等于 key_i 且小于等于 key_i+1。

进行查找操作时，首先在根节点进行二分查找，找到 key 所在的指针，而后递归地在指针所指向的节点进行查找。直到查找到叶子节点，而后在叶子节点上进行二分查找，找出 key 所对应的数据项。

MySQL 数据库应用最多的索引类型是 BTREE 索引，底层基于 B + 树数据结构来实现。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算失去哈希码，并且哈希算法要尽量保障不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的 key 值，将指向数据行的指针作为哈希表的 value 值。这样查找一个数据的工夫复杂度就是 O(1)，个别多用于准确查找。

哈希索引 不反对排序，因为哈希表是无序的。
哈希索引 不反对范畴查找。
哈希索引 不反对含糊查问 及多列索引的最左前缀匹配。
因为哈希表中会 存在哈希抵触，所以哈希索引的性能是不稳固的，而 B + 树索引的性能是绝对稳固的，每次查问都是从根节点到叶子节点。

因为 B + 树的数据都存储在叶子结点中，叶子结点均为索引，不便扫库，只须要扫一遍叶子结点即可，然而 B 树因为其分支结点同样存储着数据，咱们要找到具体的数据，须要进行一次中序遍历按序来扫，所以 B + 树更加适宜在区间查问的状况，而在数据库中基于范畴的查问是十分频繁的，所以通常 B + 树用于数据库索引。
B+ 树的节点只存储索引 key 值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中能够寄存更多的节点。缩小更多的 I / O 收入。
B+ 树的查问效率更加稳固，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

1、主键索引：名为 primary 的惟一非空索引，不容许有空值。

2、惟一索引 ：索引列中的值必须是惟一的，然而容许为空值。惟一索引和主键索引的区别是：惟一束缚的列能够为null 且能够存在多个 null 值。惟一索引的用处：惟一标识数据库表中的每条记录，次要是用来避免数据反复插入。创立惟一索引的 SQL 语句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、组合索引：在表中的多个字段组合上创立的索引，只有在查问条件中应用了这些字段的右边字段时，索引才会被应用，应用组合索引时需遵循最左前缀准则。

4、全文索引 ：只有在MyISAM 引擎上能力应用，只能在 CHAR、VARCHAR 和TEXT类型字段上应用全文索引。

如果 SQL 语句中用到了组合索引中的最右边的索引，那么这条 SQL 语句就能够利用这个组合索引去进行匹配。当遇到范畴查问 (>、<、between、like) 就会进行匹配，前面的字段不会用到索引。

对 (a,b,c) 建设索引，查问条件应用 a/ab/abc 会走索引，应用 bc 不会走索引。如果查问条件为a = 1 and b > 2 and c = 3，那么 a、b 个字两段能用到索引，而 c 无奈应用索引，因为 b 字段是范畴查问，导致前面的字段无奈应用索引。

如下图，对(a, b) 建设索引，a 在索引树中是全局有序的，而 b 是全局无序，部分有序（当 a 相等时，会依据 b 进行排序）。

当 a 的值确定的时候，b 是有序的。例如 a = 1 时，b 值为 1，2 是有序的状态。当执行 a = 1 and b = 2 时 a 和 b 字段能用到索引。而对于查问条件 a < 4 and b = 2 时，a 字段能用到索引，b 字段则用不到索引。因为 a 的值此时是一个范畴，不是固定的，在这个范畴内 b 的值不是有序的，因而 b 字段无奈应用索引。

InnoDB 应用表的主键结构主键索引树，同时叶子节点中寄存的即为整张表的记录数据。汇集索引叶子节点的存储是逻辑上间断的，应用双向链表连贯，叶子节点依照主键的程序排序，因而对于主键的排序查找和范畴查找速度比拟快。

汇集索引的叶子节点就是整张表的行记录。InnoDB 主键应用的是聚簇索引。汇集索引要比非汇集索引查问效率高很多。

对于 InnoDB 来说，汇集索引个别是表中的主键索引，如果表中没有显示指定主键，则会抉择表中的第一个不容许为 NULL 的惟一索引。如果没有主键也没有适合的惟一索引，那么 InnoDB 外部会生成一个暗藏的主键作为汇集索引，这个暗藏的主键长度为 6 个字节，它的值会随着数据的插入自增。

select的数据列只用从索引中就可能获得，不须要回表进行二次查问，也就是说查问列要被所应用的索引笼罩。对于 innodb 表的二级索引，如果索引能笼罩到查问的列，那么就能够防止对主键索引的二次查问。

不是所有类型的索引都能够成为笼罩索引。笼罩索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 应用 b + 树索引做笼罩索引。

对于应用了笼罩索引的查问，在查问后面应用explain，输入的 extra 列会显示为using index。

比方 user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id 和blog_id都不为null。

explain select blog_id from user_like where user_id = 13;

explain后果的 Extra 列为 Using index，查问的列被索引笼罩，并且 where 筛选条件合乎最左前缀准则，通过 索引查找 就能间接找到符合条件的数据，不须要回表查问数据。

explain select user_id from user_like where blog_id = 1;

explain后果的 Extra 列为 Using where; Using index，查问的列被索引笼罩，where 筛选条件不合乎最左前缀准则，无奈通过索引查找找到符合条件的数据，但能够通过 索引扫描 找到符合条件的数据，也不须要回表查问数据。

索引列的 区分度越高，索引的成果越好。比方应用性别这种区分度很低的列作为索引，成果就会很差。
尽量应用 短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引波及到的磁盘 I / O 较少，查问速度更快。
索引不是越多越好，每个索引都须要额定的物理空间，保护也须要破费工夫。
利用 最左前缀准则。

导致索引生效的状况：

对于组合索引，不是应用组合索引最右边的字段，则不会应用索引
以 % 结尾的 like 查问如%abc，无奈应用索引；非 % 结尾的 like 查问如abc%，相当于范畴查问，会应用索引
查问条件中列类型是字符串，没有应用引号，可能会因为类型不同产生隐式转换，使索引生效
判断索引列是否不等于某个值时
对索引列进行运算
查问条件应用 or 连贯，也会导致索引生效

有时须要在很长的字符列上创立索引，这会造成索引特地大且慢。应用前缀索引能够防止这个问题。

前缀索引是指对文本或者字符串的前几个字符建设索引，这样索引的长度更短，查问速度更快。

创立前缀索引的关键在于抉择足够长的前缀以 保障较高的索引选择性。索引选择性越高查问效率就越高，因为选择性高的索引能够让 MySQL 在查找时过滤掉更多的数据行。

建设前缀索引的形式：

// email 列创立前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

MySQL 中罕用的四种存储引擎别离是：MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5 版本后默认的存储引擎为InnoDB。

InnoDB 存储引擎

InnoDB 是 MySQL默认的事务型存储引擎，应用最宽泛，基于聚簇索引建设的。InnoDB 外部做了很多优化，如可能主动在内存中创立自适应 hash 索引，以减速读操作。

长处：反对事务和解体修复能力；引入了行级锁和外键束缚。

毛病：占用的数据空间绝对较大。

实用场景：须要事务反对，并且有较高的并发读写频率。

MyISAM 存储引擎

数据以严密格局存储。对于只读数据，或者表比拟小、能够容忍修复操作，能够应用 MyISAM 引擎。MyISAM 会将表存储在两个文件中，数据文件 .MYD 和索引文件.MYI。

长处：访问速度快。

毛病：MyISAM 不反对事务和行级锁，不反对解体后的平安复原，也不反对外键。

实用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY 存储引擎

MEMORY 引擎将数据全副放在内存中，访问速度较快，然而一旦零碎奔溃的话，数据都会失落。

MEMORY 引擎默认应用哈希索引，将键的哈希值和指向数据行的指针保留在哈希索引中。

长处：访问速度较快。

毛病：

哈希索引数据不是依照索引值顺序存储，无奈用于排序。
不反对局部索引匹配查找，因为哈希索引是应用索引列的全部内容来计算哈希值的。
只反对等值比拟，不反对范畴查问。
当呈现哈希抵触时，存储引擎须要遍历链表中所有的行指针，逐行进行比拟，直到找到符合条件的行。

ARCHIVE 存储引擎

ARCHIVE 存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE 提供了压缩性能，领有高效的插入速度，然而这种引擎不反对索引，所以查问性能较差。

是否反对行级锁 : MyISAM 只有表级锁，而InnoDB 反对行级锁和表级锁，默认为行级锁。
是否反对事务和解体后的平安复原 ：MyISAM 不提供事务反对。而InnoDB 提供事务反对，具备事务、回滚和解体修复能力。
是否反对外键： MyISAM不反对，而 InnoDB 反对。
是否反对 MVCC：MyISAM不反对，InnoDB反对。应答高并发事务，MVCC 比单纯的加锁更高效。
MyISAM不反对汇集索引，InnoDB反对汇集索引。

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种形式，进而实现并发管制。在查问的时候，通过 read view 和版本链找到对应版本的数据。

作用：晋升并发性能。对于高并发场景，MVCC 比行级锁开销更小。

MVCC 实现原理如下：

MVCC 的实现依赖于版本链，版本链是通过表的三个暗藏字段实现。

DB_TRX_ID：以后事务 id，通过事务 id 的大小判断事务的工夫程序。
DB_ROLL_PRT：回滚指针，指向以后行记录的上一个版本，通过这个指针将数据的多个版本连贯在一起形成 undo log 版本链。
DB_ROLL_ID：主键，如果数据表没有主键，InnoDB 会主动生成主键。

每条表记录大略是这样的：

应用事务更新行记录的时候，就会生成版本链，执行过程如下：

用排他锁锁住该行；
将该行本来的值拷贝到undo log，作为旧版本用于回滚；
批改以后行的值，生成一个新版本，更新事务 id，使回滚指针指向旧版本的记录，这样就造成一条版本链。

上面举个例子不便大家了解。

1、初始数据如下，其中 DB_ROW_ID 和DB_ROLL_PTR为空。

2、事务 A 对该行数据做了批改，将 age 批改为 12，成果如下：

3、之后事务 B 也对该行记录做了批改，将 age 批改为 8，成果如下：

4、此时 undo log 有两行记录，并且通过回滚指针连在一起。

接下来理解下 read view 的概念。

read view能够了解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻 t 的数据时，到 t 工夫点拍的“照片”上取数据。

在 read view 外部保护一个沉闷事务链表，示意生成 read view 的时候还在沉闷的事务。这个链表蕴含在创立 read view 之前还未提交的事务，不蕴含创立 read view 之后提交的事务。

不同隔离级别创立 read view 的机会不同。

read committed：每次执行 select 都会创立新的 read_view，保障能读取到其余事务曾经提交的批改。
repeatable read：在一个事务范畴内，第一次 select 时更新这个 read_view，当前不会再更新，后续所有的 select 都是复用之前的 read_view。这样能够保障事务范畴内每次读取的内容都一样，即可反复读。

read view 的记录筛选形式

前提：DATA_TRX_ID 示意每个数据行的最新的事务 ID；up_limit_id 示意以后快照中的最先开始的事务；low_limit_id示意以后快照中的最慢开始的事务，即最初一个事务。

如果 DATA_TRX_ID < up_limit_id：阐明在创立read view 时，批改该数据行的事务已提交，该版本的记录可被以后事务读取到。
如果 DATA_TRX_ID >= low_limit_id：阐明以后版本的记录的事务是在创立read view 之后生成的，该版本的数据行不能够被以后事务拜访。此时须要通过版本链找到上一个版本，而后从新判断该版本的记录对以后事务的可见性。
如果up_limit_id <= DATA_TRX_ID < low_limit_i：
1. 须要在沉闷事务链表中查找是否存在 ID 为 DATA_TRX_ID 的值的事务。
2. 如果存在，因为在沉闷事务链表中的事务是未提交的，所以该记录是不可见的。此时须要通过版本链找到上一个版本，而后从新判断该版本的可见性。
3. 如果不存在，阐明事务 trx_id 曾经提交了，这行记录是可见的。

总结：InnoDB 的MVCC 是通过 read view 和版本链实现的，版本链保留有历史版本记录，通过read view 判断以后版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，持续进行判断，直到找到一个可见的版本。

表记录有两种读取形式。

快照读：读取的是快照版本。一般的 SELECT 就是快照读。通过 mvcc 来进行并发管制的，不必加锁。
以后读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是以后读。

快照读状况下，InnoDB 通过 mvcc 机制防止了幻读景象。而 mvcc 机制无奈防止以后读状况下呈现的幻读景象。因为以后读每次读取的都是最新数据，这时如果两次查问两头有其它事务插入数据，就会产生幻读。

上面举个例子阐明下：

1、首先，user 表只有两条记录，具体如下：

2、事务 a 和事务 b 同时开启事务start transaction；

3、事务 a 插入数据而后提交；

insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);

4、事务 b 执行全表的 update；

update user set user_name = 'a';

5、事务 b 而后执行查问，查到了事务 a 中插入的数据。（下图右边是事务 b，左边是事务 a。事务开始之前只有两条记录，事务 a 插入一条数据之后，事务 b 查问进去是三条数据）

以上就是以后读呈现的幻读景象。

那么 MySQL 是如何防止幻读？

在快照读状况下，MySQL 通过 mvcc 来防止幻读。
在以后读状况下，MySQL 通过 next-key 来防止幻读（加行锁和间隙锁来实现的）。

next-key 包含两局部：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

Serializable隔离级别也能够防止幻读，会锁住整张表，并发性极低，个别不会应用。

SELECT 的读取锁定次要分为两种形式：共享锁和排他锁。

select * from table where id<6 lock in share mode;-- 共享锁 select * from table where id<6 for update;-- 排他锁

这两种形式次要的不同在于 LOCK IN SHARE MODE 多个事务同时更新同一个表单时很容易造成死锁。

申请排他锁的前提是，没有线程对该后果集的任何行数据应用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL 会对查问后果集的每行数据增加排它锁，其余线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被 commit 语句或 rollback 语句完结为止。

SELECT... FOR UPDATE 应用注意事项：

for update 仅实用于 innodb，且必须在事务范畴内能力失效。
依据主键进行查问，查问条件为 like 或者不等于，主键字段产生表锁。
依据非索引字段进行查问，会产生表锁。

某个表有近千万数据，查问比较慢，如何优化？

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

限定数据的范畴。比方：用户在查问历史信息的时候，能够管制在一个月的工夫范畴内；
读写拆散：经典的数据库拆分计划，主库负责写，从库负责读；
通过分库分表的形式进行优化，次要有垂直拆分和程度拆分。

MySQL 日志次要包含查问日志、慢查问日志、事务日志、谬误日志、二进制日志等。其中比拟重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log是 MySQL 数据库级别的文件，记录对 MySQL 数据库执行批改的所有操作，不会记录 select 和 show 语句，次要用于复原数据库和同步数据库。

redo log

redo log是 innodb 引擎级别，用来记录 innodb 存储引擎的事务日志，不论事务是否提交都会记录下来，用于数据恢复。当数据库产生故障，innoDB 存储引擎会应用 redo log 复原到产生故障前的时刻，以此来保证数据的完整性。将参数 innodb_flush_log_at_tx_commit 设置为 1，那么在执行 commit 时会将 redo log 同步写到磁盘。

undo log

除了记录 redo log 外，当进行数据批改时还会记录 undo log，undo log 用于数据的撤回操作，它保留了记录批改前的内容。通过 undo log 能够实现事务回滚，并且能够依据 undo log 回溯到某个特定的版本的数据，实现 MVCC。

bin log会记录所有日志记录，包含 InnoDB、MyISAM 等存储引擎的日志；redo log只记录 innoDB 本身的事务日志。
bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有 redo log 一直写入磁盘。
bin log是逻辑日志，记录的是 SQL 语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么批改。

MySQL 次要分为 Server 层和存储引擎层：

Server 层：次要包含连接器、查问缓存、分析器、优化器、执行器等，所有跨存储引擎的性能都在这一层实现，比方存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：次要负责数据的存储和读取。server 层通过 api 与存储引擎进行通信。

Server 层根本组件

连接器： 当客户端连贯 MySQL 时，server 层会对其进行身份认证和权限校验。
查问缓存: 执行查问语句的时候，会先查问缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会间接返回给客户端，如果没有命中，就会执行后续的操作。
分析器: 没有命中缓存的话，SQL 语句就会通过分析器，次要分为两步，词法剖析和语法分析，先看 SQL 语句要做什么，再查看 SQL 语句语法是否正确。
优化器： 优化器对查问进行优化，包含重写查问、决定表的读写程序以及抉择适合的索引等，生成执行打算。
执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会依据执行打算去调用引擎的接口，返回后果。

当单表的数据量达到 1000W 或 100G 当前，优化索引、增加从库等可能对数据库性能晋升成果不显著，此时就要思考对其进行切分了。切分的目标就在于缩小数据库的累赘，缩短查问的工夫。

数据切分能够分为两种形式：垂直划分和程度划分。

垂直划分

垂直划分数据库是依据业务进行划分，例如购物场景，能够将库中波及商品、订单、用户的表别离划分出成一个库，通过升高单库的大小来进步性能。同样的，分表的状况就是将一个大表依据业务性能拆分成一个个子表，例如商品根本信息和商品形容，商品根本信息个别会展现在商品列表，商品形容在商品详情页，能够将商品根本信息和商品形容拆分成两张表。

长处：行记录变小，数据页能够寄存更多记录，在查问时缩小 I / O 次数。

毛病：

主键呈现冗余，须要治理冗余列；
会引起表连贯 JOIN 操作，能够通过在业务服务器上进行 join 来缩小数据库压力；
仍然存在单表数据量过大的问题。

程度划分

程度划分是依据肯定规定，例如工夫或 id 序列值等进行数据的拆分。比方依据年份来拆分不同的数据库。每个数据库构造统一，然而数据得以拆分，从而晋升性能。

长处：单库（表）的数据量得以缩小，进步性能；切分出的表构造雷同，程序改变较少。

毛病：

分片事务一致性难以解决
跨节点 join 性能差，逻辑简单
数据分片在扩容时须要迁徙

分区表是一个独立的逻辑表，然而底层由多个物理子表组成。

当查问条件的数据分布在某一个分区的时候，查问引擎只会去某一个分区查问，而不是遍历整个表。在治理层面，如果须要删除某一个分区的数据，只须要删除对应的分区即可。

依照范畴分区。

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN (TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN (TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN (TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN (TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN (TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN (TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN (TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN (TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN (TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN (TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN (TO_DAYS('20190101') )
   );

在 /var/lib/mysql/data/ 能够找到对应的数据文件，每个分区表都有一个应用 #分隔命名的表文件：

   -rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
   -rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list 分区

对于 List 分区，分区字段必须是已知的，如果插入的字段不在分区时枚举值中，将无奈插入。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash 分区

能够将数据平均地散布到事后定义的分区中。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

查问语句的执行流程如下：权限校验、查问缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查问语句如下：

select * from user where id > 1 and name = '大彬';

首先查看权限，没有权限则返回谬误；
MySQL8.0 以前会查问缓存，缓存命中则间接返回，没有则执行下一步；
词法剖析和语法分析。提取表名、查问条件，查看语法是否有谬误；
两种执行计划，先查 id > 1 还是 name = '大彬'，优化器依据本人的优化算法抉择执行效率最好的计划；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行后果。

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '大彬' where id = 1;

先查问到 id 为 1 的记录，有缓存会应用缓存。
拿到查问后果，将 name 更新为大彬，而后调用引擎接口，写入更新数据，innodb 引擎将数据保留在内存中，同时记录 redo log，此时redo log 进入 prepare状态。
执行器收到告诉后记录 binlog，而后调用引擎接口，提交redo log 为commit状态。
更新实现。

为什么记录完 redo log，不间接提交，而是先进入prepare 状态？

假如先写 redo log 间接提交，而后写 binlog，写完redo log 后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过 redo log 复原数据，然而这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会失落这一条数据，同时主从同步也会失落这一条数据。

exists用于对表面记录做筛选。exists会遍历表面，将外查问表的每一行，代入内查问进行判断。当 exists 里的条件语句可能返回记录行时，条件就为真，返回表面以后记录。反之如果 exists 里的条件语句不能返回记录行，条件为假，则表面以后记录被抛弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到长期表中，而后遍历长期表，将长期表的每一行，代入外查问去查找。

select * from Awhere id in(select id from B)

子查问的表比拟大的时候 ，应用exists 能够无效缩小总的循环次数来晋升速度；当外查问的表比拟大的时候 ，应用in 能够无效缩小对外查问表循环遍从来晋升速度。

相同点：

truncate和不带 where 子句的 delete、以及drop 都会删除表内的数据。
drop、truncate都是 DDL 语句（数据定义语言），执行后会主动提交。

不同点：

truncate 和 delete 只删除数据不删除表的构造；drop 语句将删除表的构造被依赖的束缚、触发器、索引；
一般来说，执行速度: drop > truncate > delete。

二者作用的对象不同，where子句作用于表和视图，having作用于组。
where在数据分组前进行过滤，having在数据分组后进行过滤。

主从同步使得数据能够从一个数据库服务器复制到其余服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不须要始终连贯着主服务器，从服务器甚至能够通过拨号断断续续地连贯主服务器。通过配置文件，能够指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

读写拆散，使数据库能撑持更大的并发。
在主服务器上生成实时数据，而在从服务器上剖析这些数据，从而进步主服务器的性能。
数据备份，保证数据的平安。

数据库中的并发管制是确保在多个事务同时存取数据库中同一数据时不毁坏事务的隔离性和统一性以及数据库的统一性。乐观锁和乐观锁是并发管制次要采纳的技术手段。

乐观锁：假设会产生并发抵触，在查问完数据的时候就把事务锁起来，直到提交事务。实现形式：应用数据库中的锁机制。
乐观锁：假如不会产生并发抵触，只在提交操作时查看是否数据是否被批改过。给表减少 version 字段，在批改提交之前查看 version 与原来取到的 version 值是否相等，若相等，示意数据没有被批改，能够更新，否则，数据为脏数据，不能更新。实现形式：乐观锁个别应用版本号机制或 CAS 算法实现。

show processlist 或 show full processlist 能够查看以后 MySQL 是否有压力，正在运行的 SQL，有没有慢SQL 正在执行。返回参数如下：

id：线程 ID，能够用 kill id 杀死某个线程
db：数据库名称
user：数据库用户
host：数据库实例的 IP
command：以后执行的命令，比方 Sleep，Query，Connect 等
time：耗费工夫，单位秒
state：执行状态，次要有以下状态：
- Sleep，线程正在期待客户端发送新的申请
- Locked，线程正在期待锁
- Sending data，正在解决 SELECT 查问的记录，同时把后果发送给客户端
- Kill，正在执行 kill 语句，杀死指定线程
- Connect，一个从节点连上了主节点
- Quit，线程正在退出
- Sorting for group，正在为 GROUP BY 做排序
- Sorting for order，正在为 ORDER BY 做排序
info：正在执行的 SQL 语句

关于mysql:MySQL灵魂拷问36题带你面试通关

事务的四大个性？

事务隔离级别有哪些？

索引

什么是索引？

索引的优缺点？

索引的作用？

什么状况下须要建索引？

什么状况下不建索引？

索引的数据结构

Hash 索引和 B + 树索引的区别？

为什么 B + 树比 B 树更适宜实现数据库索引？

索引有什么分类？

什么是最左匹配准则？

什么是汇集索引？

什么是笼罩索引？

索引的设计准则？

索引什么时候会生效？

什么是前缀索引？

常见的存储引擎有哪些？

MyISAM 和 InnoDB 的区别？

MVCC 实现原理？

快照读和以后读

共享锁和排他锁

大表怎么优化？

bin log/redo log/undo log

bin log 和 redo log 有什么区别？

讲一下 MySQL 架构？

分库分表

什么是分区表？

分区表类型

查问语句执行流程？

更新语句执行过程？

exist 和 in 的区别？

truncate、delete 与 drop 区别？

having 和 where 的区别？

什么是 MySQL 主从同步？

为什么要做主从同步？

乐观锁和乐观锁是什么？

用过 processlist 吗？

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）