大家好，我是大彬~

明天给大家分享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次磁盘，查问速度大大晋升。

什么状况下须要建索引？

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

什么状况下不建索引？

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

索引的数据结构

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

B+树索引

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

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 别离是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+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)，个别多用于准确查找。

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

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

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

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

索引有什么分类？

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

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

ALTER TABLE table_nameADD 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引擎默认应用哈希索引，将键的哈希值和指向数据行的指针保留在哈希索引中。

长处：访问速度较快。

毛病：

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

ARCHIVE存储引擎

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

MyISAM和InnoDB的区别？

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

MVCC 实现原理？

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

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

MVCC 实现原理如下：

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

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

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

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

  1. 用排他锁锁住该行；
  2. 将该行本来的值拷贝到undo log，作为旧版本用于回滚；
  3. 批改以后行的值，生成一个新版本，更新事务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 应用注意事项：

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

大表怎么优化？

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

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

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

bin log/redo log/undo log

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和redo log有什么区别？

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

讲一下MySQL架构？

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

更新语句执行过程？

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

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

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

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

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

exist和in的区别？

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、delete与drop区别？

相同点：

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

不同点：

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

having和where的区别？

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

什么是MySQL主从同步？

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

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

为什么要做主从同步？

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

乐观锁和乐观锁是什么？

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

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

用过processlist吗？

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

  1. id：线程ID，能够用kill id杀死某个线程
  2. db：数据库名称
  3. user：数据库用户
  4. host：数据库实例的IP
  5. command：以后执行的命令，比方Sleep，Query，Connect 等
  6. time：耗费工夫，单位秒

  7. state：执行状态，次要有以下状态：

    • Sleep，线程正在期待客户端发送新的申请
    • Locked，线程正在期待锁
    • Sending data，正在解决SELECT查问的记录，同时把后果发送给客户端
    • Kill，正在执行kill语句，杀死指定线程
    • Connect，一个从节点连上了主节点
    • Quit，线程正在退出
    • Sorting for group，正在为GROUP BY做排序
    • Sorting for order，正在为ORDER BY做排序
  8. info：正在执行的SQL语句