mysqldba面试 mysql面试基础知识

史上最全Mysql基础面试知识总结

• 1. 几种连接的种类和区别

• 1.1 全连接
• 1.2 左连接
• 1.3 右连接
• 1.4 内连接
• 1.5 下面是用这四种连接组成的一些常用操作

• 1.5.1 左表有右表没有的全部数据
• 1.5.2 右表有左表没有的全部数据
• 1.5.3 全连接，查询所有记录
• 1.5.4 并集去交集

• 2. 范式的种类和区别

• 2.1 第一范式（1NF）
• 2.2 第二范式（2NF）
• 2.3 第三范式（3NF）

• 2.Sql函数有哪些
• 3.char 和varchar的区别
• 4. 数据库事务管理（ACID）
• 5. 事务的隔离性

• 5.1 脏读、幻读、不可重复读
• 5.2 读未提交、读以提交、可重复读、可序列化读（串行化读）

• 6. Mysql 的复制原理以及流程

• 6.1 mysql 复试概述
• 6.2 mysql复制能解决的问题

• 6.2.1 高可用和故障切换
• 6.2.2 负载均衡
• 6.2.3 数据备份
• 6.2.4 业务模块化

• 6.3 主从复制的步骤

• 6.3.1 涉及到的三个线程
• 6.3.2 主从复制的具体步骤
• 6.3.3 GTID是什么

• 7. Mysql的锁机制

• 7.1 锁的分类

• 7.1.1 行锁
• 7.1.2 表锁

• 7.2 InnoDB中的行锁算法

• 7.2.1 记录锁（Record Locks）
• 7.2.2 间隙锁（Gap Locks）
• 7.2.3 临隙锁（Next-key Locks）

• 7.3 Mysql遇到的死锁问题、如何排查与解决

• 10. 存储引擎InnoDB与MyISAM的区别，优缺点以及使用场景
• 11. InnoDB引擎的四大特性

• 11.1.插入缓冲（insert buffer)
• 11.2.二次写(double write)
• 11.3.自适应哈希索引(ahi)
• 11.4.预读(read ahead)

• 12. InnoDB引擎的行锁是通过加在什么上面完成的，为什么是这个样子
• 13. 存储过程与触发器的区别
• 14. MVCC的实现原理
• 15. 如何防范sql注入
• 16. 说说数据库优化的思路
• 17. 索引工作原理以及种类

• 17.1 mysql的索引分为哪几种
• 17.2为什么用B+树作为mysql的索引

• 18. 聚集索引与非聚集索引的区别
• 19. 覆盖索引、联合索引、索引下推、最左前缀原则
• 20. 遇到过索引失效的情况吗，什么时候可能会出现，如何解决
• 21. limit 20000 加载很慢如何解决
• 22. 说说分库与分表设计

• 22.1 分表概述
• 22.2 分库概述

• 23. 分库与分表带来的分布式困境与应对 之策（如何解决分布式下的分库分表，全局表？）

1. 几种连接的种类和区别

生产环境中，为了获取更好的性能，以及更大的伸缩性，我们常需要把数据存储在多个表中，检索时通常需要跨若干个表来进行检索；所以，join是sql中一种重要的机制。

常用的连接才做有：

• 内连接
• 全连接（全外连接）
• 左连接（左外连接）
• 右连接（右外连接）

下面我们创建一个简单的数据库来简单模拟一下这些连接的应用：

CREATE TABLE `a`  (
  `a_id` int(11) NOT NULL AUTO_INCREMENT,
  `a_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `a_age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`a_id`) USING BTREE
)

CREATE TABLE `b`  (
  `b_id` int(11) NOT NULL AUTO_INCREMENT,
  `b_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `b_age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`b_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

1.1 全连接

首先，不加任何限定条件的就是全连接操作，返回的是两个表的笛卡尔积。

SELECT * FROM a JOIN b

1.2 左连接

两个表相关联，左表数据全部保留，右表无法关联的用null表示

SELECT * FROM a LEFT JOIN b ON A.a_id = B.b_id

1.3 右连接

两个表相关联，右表数据全部保留，左表无法关联的用null表示

SELECT * FROM a RIGHT JOIN b ON A.a_id = B.b_id

1.4 内连接

连接两个表集中相交的数据

SELECT * FROM a INNER JOIN b ON A.a_age = B.b_age

1.5 下面是用这四种连接组成的一些常用操作

1.5.1 左表有右表没有的全部数据

SELECT * FROM a LEFT JOIN b ON A.a_age = B.b_age WHERE b.b_id is NULL

1.5.2 右表有左表没有的全部数据

SELECT * FROM a RIGHT JOIN b ON A.a_age = B.b_age WHERE a.a_id is NULL

1.5.3 全连接，查询所有记录

mysql 中不提供full join功能，所以我们用union (合并返回结果集)来实现

(SELECT * FROM a LEFT JOIN b ON A.a_id = B.b_id) union (SELECT * FROM a RIGHT JOIN b ON A.a_id = B.b_id)

1.5.4 并集去交集

(SELECT * FROM a LEFT JOIN b ON A.a_id = B.b_id WHERE b.b_id = NULL) union (SELECT * FROM a RIGHT JOIN b ON A.a_id = B.b_id WHERE a.a_id = NULL)

2. 范式的种类和区别

关系型数据库总共有6大范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

2.1 第一范式（1NF）

第一范式具有原子性，每一列都保持它的原子特性，也就是列不可再分割。

学号	课号	姓名	系名	分数	系主任

可以看出这张表就存在很严重的函数依赖和数据冗余。

2.2 第二范式（2NF）

第二范式消除了第一范式中的部份依赖。所以，我们把上表抽取出候选码，生成新的关系。

可以看出每个关系只有一个候选键，不存在部份依赖，即2nf

2.3 第三范式（3NF）

**传递依赖：**满足二范式基础上，如果一个属性依赖于其它非主键属性，而非主键属性又依赖于主键，那么这个属性间接依赖主键，造成了传递依赖。

第三范式消除了传递依赖。

2.Sql函数有哪些

下面我们列举了sql中一些常用的内置函数：

• avg(): 计算某个字段的平均值
• COUNT(column_name)： 函数返回指定列的值的数目
• FIRST() 函数返回指定的字段中第一个记录的值。
• LAST() 函数返回指定的字段中最后一个记录的值。
• MAX() 函数返回一列中的最大值。NULL 值不包括在计算中。
• MIN() 函数返回一列中的最小值。NULL 值不包括在计算中。
• SUM() 函数返回数值列的总数（总额）。
• UCASE() 函数把字段的值转换为大写。
• LCASE() 函数把字段的值转换为小写。
• MID() 函数用于从文本字段中提取字符。
• LEN() 函数返回文本字段中值的长度。
• ROUND() 函数用于把数值字段舍入为指定的小数位数。
• NOW() 函数返回当前的日期和时间。
• **FORMAT() ** 函数用于对字段的显示进行格式化。

3.char 和varchar的区别

在MYSQL中，char和varchar都是我们日常经常使用的，二者都是用于存储字符串的，并且在使用中基本没有什么区别。但他们的保存和检索方式相差很大。

char和varchar在声明类型时表示用户想保存的最大字符数，其中char(M)定义的列的长度是固定的，M的取值可以在0 ~ 255之间。如果存入的数据长度小于M，Mysql就会在它的右边以空格填充，而检索时，会删掉微博的空格。在存储和检索过程中不进行大小写的转换，所以检索速度很快。

varchar(M) 定义的是可边长度字符串，与char相比，varchar值保存时只保存需要的字符数，另外加一个字节来记录长度（超过255时要加两个字节）。但varchar存储时虽然保存保留实际长度的内存串，但加载到内存中时，同样会在末尾添加字符串，占用固定长度的内存值。具体而言，就是节约了硬盘存储空间。

二者相比，char固定长度，所以在处理速度上明显快于varchar，但会浪费很多的存储空间。所以，一些固定长度的信息，比如手机号、UUID主键、门牌号，使用char类型更合适。对于MyISAM表，也尽量用char。

4. 数据库事务管理（ACID）

MySql 是一个支持事务的数据库，所以肯定具备ACID四大特性：

1. 原子性（Atumicity）: 事物的原子性指事务所包含的操作要么全部成功，要么全部失败回滚
2. 一致性（Consistency）: 一致性是指事务必须使数据库从一个一致性状态，转换到另一个一致性状态，也就是说一个事务执行之前和执行之后必须处于一致性状态。（例：如果用户A和用户B二者一共有5000块钱，那么不管AB如何相互转账，事务结束后一共还应该有5000块）
3. 隔离性（Isolation）：当多个事务并发访问数据库时，每个事务要相互隔离，不能被其它事务干扰，多个并发事务之间要相互隔离。
4. 持久性（Durability）：值事务一旦提交，即永久性改变。即使遇到故障也不会丢失事务操作。

5. 事务的隔离性

事务的隔离性是指多个事务并发访问数据库时，各个数据库要相互独立，一个事务不能被其它事务干扰，多个并发事务之间要相互隔离。
如果不考虑事务的隔离性，可能会发生如下几种问题：

5.1 脏读、幻读、不可重复读

1. 脏读：是指一个事务处理过程中读取了另一个未提交的事务中的数据。（一个事务正在多次修改某个数据，而这个事务的多次修改还未提交。同时，一个并发的事务来读取该数据，造成两个事务得到的数据不一致。例如：用户A向用户B转账100元主要由两部操作，先把B的账户加100元，这是B访问数据库查询余额，发现转账已完成。而此时A未提交第二步，事务回滚。当B再次查看账户时发现钱并没有转）
2. 不可重复读：不可重复读指在对于数据库中某个数据，一个事务范围多次查询却返回了不同的数据值。这是由于查询的时候，被另一个事务修改并提交了。（和脏读的区别是脏读读取了另一事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据）。
3. 虚读（幻读）：幻读是事务非独立执行的一种现象。例如当事务T1对一个表中所有的行的某个数据项做了从“1”修改为”2“的操作，这是事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为”1“并提交给数据库。而T1用户如果再次查看刚修改的数据，发现刚从T2事务中添加的那条没有修改，即发生了幻读。

5.2 读未提交、读以提交、可重复读、可序列化读（串行化读）

为了杜绝上述这些情况，Mysql为我们提供了四种隔离级别：

1. Serializable(串行化)：可避免脏读、不可重复读、幻读的发生。
2. Repeatedtable read(可重复读)：可避免脏读、不可重复读的发生。
3. Read committed(读已提交)：可避免脏读的发生。
4. Read uncommitted(读未提交)：最低级别，任何情况都无法保证。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，级别越高，执行效率越低。像Serializable这种级别，就是以锁表的方式，使其它事务都在锁外等待。在应用中，应该根据情况选择合适的隔离方式。

隔离界别	脏读	不可重复读	幻读	概念
READ UNCOMMITED	√	√	√	事务能够看到其他事务没有提交的修改，当另一个事务又回滚了修改后的情况，又被称为脏读dirty read
READ COMMITTED	×	√	√	事务能够看到其他事务提交后的修改，这时会出现一个事务内两次读取数据可能因为其他事务提交的修改导致不一致的情况，称为不可重复读
REPEATABLE READ	×	×	√	事务在两次读取时读取到的数据的状态是一致的
SERIALIZABLE	×	×	×	可重复读中可能出现第二次读读到第一次没有读到的数据，也就是被其他事务插入的数据，这种情况称为幻读phantom read, 该级别中不能出现幻读

6. Mysql 的复制原理以及流程

6.1 mysql 复试概述

mysql复制时为了保证主服务器（Master）和从服务器(Slave)的数据是一致性的，向Master插入数据后，Slave 会自动从Master把修改后的数据同步过来（有一定延迟），通过这种方式来保证数据的一致性，即Mysql 复制。一个主服务器可以同步到多台备用服务器上，备用服务器也可以作为另一台服务器的服务器。二者之间有多种不同的组合方式。mysql从3.23版本开始提供复制功能，复制是将主库的DDL和DML操作通过二进制日志传递到复制服务器（从库）上，然后从库对这些日志重新执行（重做），从而使得主库和从库保持数据一致。

6.2 mysql复制能解决的问题

6.2.1 高可用和故障切换

复试能够帮助避免Mysql单点失败。因为数据都是相同的，所以当Master挂掉后，可以指定一台Slave充当Master继续保证服务运行，因为数据是一致的。

6.2.2 负载均衡

读写分离也算是负载均衡的一种。因为有多台Slave，可以将读操作指定到Slave服务器上，然后用负载均衡来选择哪台Slave来提供服务，同时把一些大量的计算查询指定到某台Slave上，就不会影响Master的写入以及其它查询方式

6.2.3 数据备份

基本我们都会做数据备份，可能是写定时任务，也有可能是手动备份。所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据安全。

6.2.4 业务模块化

可以一个业务模块读取一个Slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择不同的存储引擎。

6.3 主从复制的步骤

6.3.1 涉及到的三个线程

Mysql 主从复试涉及到三个线程，一个运行在主节点，其余两个运行在从节点。
主节点 binary log dump 线程： 当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log 的内容。读取big-log中的操作时，会对主节点上的bin-log加锁。读取完成，发送给从节点之前，锁解除。
**从节点I/O线程：**当从节点执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新bin-log，I/O线程收到主节点binlog dump进行发来的更新之后，保存在本地relay-log中。
从节点SQL线程：sql线程读取relay log(中继日志)中的内容，解析成具体的操作并执行，保证主从数据的一致性。

Bin Log:是mysql服务层产生的日志，常用来进行数据恢复、数据库复制，常见的mysql主从架构，就是采用slave同步master的binlog实现的

Redo Log:记录了数据操作在物理层面的修改，mysql中使用了大量缓存，修改操作时会直接修改内存，而不是立刻修改磁盘，事务进行中时会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中。当数据库或主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。

6.3.2 主从复制的具体步骤

1. 从服务器上接收到start slave命令开启主从复制开关，开始进行主从复制。
2. 此时，Slave服务器上的I/O线程连接Master服务器，请求从执行binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master 命令指定的)之后开始发送binlog内容。
3. Master 服务器接收到来自Slabe服务器的IO线程请求后，主节点IO线程会根据Slave服务器请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息；然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4. 当Slave 服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件以及位置点后，会将binlog日志依次写道Slave端自身的Relay Log(中继日志)文件的最末端。并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5. Slave 服务器端的SQL线程会实时检测本地的Relay Log 中IO线程新增的日志内容，然后及时解析成SQL语句，并按顺序执行sql语句，然后在relay-log.info中记录当前应用中继日志的文件名和位置点。

6.3.3 GTID是什么

从Mysql5.6.5中开始新增了一种基于GTID的复制方式，通过GTID保证了每个在主库提交的事务在集群中有一个唯一的ID，强化了数据库的主库一致性，故障恢复以及容错能力。GTID相当于主库上的一个标志位，从库会自动识别这个标志位，找到自己需要复制的点。

在原来的基于二进制文件的府志中，从库需要告诉主库从哪个偏移量进行增量同步，如果指定错误会造成数据遗漏，破坏一致性。借助GTID，在发生主备切换的情况下，MySql的其它从库可以自动在新主库上找到正确的位置，大大简化了复杂拓扑下集群的维护，减少数据不一致的风险。

GTID由UUID+TID组成，其中UUID是一个mysql示例的唯一标识，TID代表该实例已提交的事务数量，随着事务提交递增。

7. Mysql的锁机制

Mysql 大致七种类型的锁：

• 共享/排它锁（Shared and Exclusive Locks）
• 意向锁（Intention Locks）
• 记录锁（Record Locks）
• 间隙锁（Gap Locks）
• 临隙锁（Next-key Locks）
• 插入意向锁（Insert Intention Locks）
• 自增锁（Auto-inc Locks）

7.1 锁的分类

1. 按照锁的互斥程度来划分，可以分为共享锁（S锁、IS锁）、排它锁。
2. 按照锁的粒度来划分，可以分为：表锁（意向锁（IS锁，IX锁），自增锁）；行锁：记录锁、临建锁、插入意向锁；页锁

7.1.1 行锁

行锁是Mysql中粒度最细的一种锁，只针对当前操作的行进行加锁。能大大减小数据库操作的重读，但开销页最大，会发生死锁情况。
共享锁（S锁）： 事务T对数据A加S锁，事务T只可以读A但不能修改A 其它事务只能对A加S锁，而不能加X锁，直到T释放A上的S锁。保证了其它事务可以读A。

select ... lock in share mode;

排他锁（X锁）： 事务T对数据A加X锁，事务T可读也可修改A，但其它事务不能再对A加任何锁。保证了其它事务在T释放A锁时，不能读取或修改A。

select ... for update

7.1.2 表锁

表级锁是对当前操作的整张表加锁，资源开销比行锁小，不会出现死锁，但冲突概率很大。MyISAM和InnoDB都支持表级锁，InnoDB默认的锁是行锁，MyISAM默认表锁。

共享锁用法：

LOCK TABLE table_name [ AS alias_name ] READ

**排它锁用法：**表级锁，主要

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

意向锁： 表级锁，主要为了在下一事务中揭示下一行将要被请求锁的类型。是InnoDB自动加的。主要分为意向共享锁（IS）和意向排他锁（IX）。
例如：

trx1：
1. BEGIN;
2. 对表T1 加X锁，修改表结构。
trx2：
1. BEGIN;
2. 对表T1 的一行记录加S或X锁（事务被阻塞)。

因trx1锁住了整个表，所以trx2就不能再对T1的单条记录加X或S锁，去读取或修这条记录。为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁。

trx1:
1. BEGIN
2. trx1 给 T1 加X锁，修改表结构。
trx2:
1. BEGIN
2. trx2 给 T1 加IX锁（事务被阻塞，等待加锁成功)
3. trx2 给 T1 的一行记录加S或X锁.

自增长锁
是一种特殊的表锁机制，提升并发插入性能。
Mysql5.7之后，自增长锁有如下三种模式的配置：
innodbautoinclock_mode 0 , 1 , 2

• 0 连续模式： 传统表锁
• 1 传统模式：对于插入的时候可以确定行数的使用互斥量其余使用表锁（默认）
• 交错模式：全都使用互斥量，可能批量插入自增量不连续。

7.2 InnoDB中的行锁算法

7.2.1 记录锁（Record Locks）

单挑索引上加锁，Record Locks永远锁住的是索引，而非数据本身。如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住这个索引。

7.2.2 间隙锁（Gap Locks）

间隙锁，锁定一个范围，但不包括记录本上，gap锁是RR隔离级别下防止幻读的主要原因。

7.2.3 临隙锁（Next-key Locks）

这个锁机制其实就是前面两个锁相结合的机制，行锁就是采用这个锁的机制用来防止幻读！锁定一个范围，并且锁定记录本身

7.3 Mysql遇到的死锁问题、如何排查与解决

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。（不过现在一般都是InnoDB引擎，关于MyISAM不做考虑）

在InnoDB中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

例子：

这种情况很好理解，首先session1获得 id=1的锁 session2获得id=5的锁，然后session想要获取id=5的锁 等待，session2想要获取id=1的锁 ，也等待！

这种情况需要我们了解数据的索引的检索顺序原理简单说下：普通索引上面保存了主键索引，当我们使用普通索引检索数据时，如果所需的信息不够，那么会继续遍历主键索引。

假设默认情况是RR隔离级别，针对session 1 从name索引出发，检索到的是（hdc,1）（hdc,6）不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10] 这个顺序是因为B+树结构的有序性。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。
避免死锁，这里只介绍常见的三种

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

10. 存储引擎InnoDB与MyISAM的区别，优缺点以及使用场景

1. 默认存储引擎变迁
在MySQL 5.5之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。

2. MyISAM与InnoDB存储引擎的主要特点
（1）MyISAM存储殷勤的特点是：表级锁、不支持事务和和支持全文索引，适合一些CMS内容管理系统作为后台数据库使用。但大并发、重负荷系统上，显得力不从心。
（2）InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
注意： InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表(锁完以后，判断不符合条件的会逐步解锁)，
例如 update table set num=1 where name like “a%”。
两种类型最主要的差别就是InnoDB支持事务处理与外键和行级锁。而MyISAM不支持。所以MyISAM往往就容易被人认为只适合在小项目中使用。

3. MyISAM与InnoDB的性能测试
随着CPU增加，InnoDB的吞吐量反而越好，而MyISAM，其吞吐量几乎没有什么变化。因为MyISAM的表锁定机制降低了读和写的吞吐量。

4. 事务支持与否
MyISAM是一种非事务性的引擎，使得MyISAM引擎的MySQL可以提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用；
InnoDB是事务安全的；事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5. MyISAM与InnoDB构成上的区别
1.每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型，.frm文件存储表定义。 第二个文件是数据文件，其扩展名为.MYD (MYData)。 第三个文件是索引文件，其扩展名是.MYI (MYIndex)。

6.MyISAM与InnoDB表锁和行锁的解释
MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。
InnoDB行锁是通过索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的
时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决。

7.是否保存数据库中表的具体行数
InnoDB不保存具体行数，执行count时，需要遍历一遍整个表。但MyISAM只要读取保存好的行数。

8. 如何进行选择

适合MyISAM的情况：

（1）做很多count的计算。
（2）插入不频繁，查询非常频繁。
（3）没有事务。

适合InnoDB的情况：
（1）可靠性比较高，要求事务
（2）表更新和查询相当频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
（3）数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；
（4）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；
（5） LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

9. 如何进行选择
1). 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

2). DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

3). LOAD TABLE FROMMASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

4). 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

5). 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

6). InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。

要注意，创建每个表格的代码是相同的，除了最后的 TYPE参数，这一参数用来指定数据引擎。

11. InnoDB引擎的四大特性

11.1.插入缓冲（insert buffer)

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

11.2.二次写(double write)

Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入

11.3.自适应哈希索引(ahi)

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

11.4.预读(read ahead)

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

12. InnoDB引擎的行锁是通过加在什么上面完成的，为什么是这个样子

Innodb的行锁是加在索引实现的；

原因：innodb是将primary key index和相关的行数据共同放在B+树的叶节点；innodb一定会有一个primary key，secondary index查找的时候，也是通过找到对应的primary，再找对应的数据行；

13. 存储过程与触发器的区别

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

触发器（trigger）是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

区别： 触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。

触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。

触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

14. MVCC的实现原理

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

参考博客：https://www.jianshu.com/p/f692d4f8a53e

15. 如何防范sql注入

首先什么是SQL注入：

sql注入_百度百科：
所谓SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意）的SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。[1] 比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击．
SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

sql注入的总体思路：

1. 寻找到SQL注入的位置
2. 判断服务器类型和后台数据类型
3. 针对不同的服务器和数据库特点进行SQL注入攻击

应对办法：
1.参数化语句，使用PreparedStatement。只把输入串当作数据处理，不再对sql语句进行解析
2. 加强输入验证
3. 普通用户和管理员做严格的权限区分

16. 说说数据库优化的思路

参考博客：https://shafish.cn/1072.html

1. 数据库中索引优化，应尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引
2. 对sql语句尽可能的优化 开启慢查询日志，对效率过低的sql语句进行优化
3. 数据库字段优化，索引优化
4. 加缓存，redis/memcache等，减少对mysql数据库的IO操作。
5. 对表进行水平划分和垂直划分
   代码中sql语句优化

第一优化你的sql和索引；

第二加缓存，memcached,redis；

第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；

第四如果以上都做了还是慢，不要想着去做切分，mysql自带分区表，先试试这个，对你的应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了；

第五如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；第六才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

17. 索引工作原理以及种类

17.1 mysql的索引分为哪几种

• 按存储结构分类：聚簇索引和非聚簇索引
• 按数据结构分类：B+tree索引，hash索引，全文索引，R-Tree索引
• 按列表属性分类：单列索引，联合索引
• 从逻辑角度：主键索引、普通索引、全文索引、唯一索引

17.2为什么用B+树作为mysql的索引

树形结构如B树、B+树、二叉查找树都是有序的，查询效率很高。可以用O(logn)的时间复杂度找到目标数据。 而哈希表虽然能够在O(1)查找到目标数据，不过如果进行模糊查找的话，却只能遍历所有数据，不适合复杂的查找条件。而和二叉查找树比较而言，B+树能有效降低磁盘寻址加载次数。和B树比较起来，B+树所有数据都在叶子节点，并且用指针串起来，这样遍历叶子节点就可以获得全部数据，就可以进行区间访问。

18. 聚集索引与非聚集索引的区别

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

1. 对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的地址。
2. 对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。

聚集索引的优点：
3. 聚簇索引适合范围查找
4. 聚簇索引查找目标比非聚簇索引理论上要快。

聚集索引的缺点：

1. 插入速度依赖于插入顺序
2. 更新主键的代价很高
3. 二级索引需要两次索引查找
4. 插入新值较慢

19. 覆盖索引、联合索引、索引下推、最左前缀原则

20. 遇到过索引失效的情况吗，什么时候可能会出现，如何解决

列和列对比： 某个表中两列都建立了单独索引，列和列对比不会走索引

select * from test where id=c_id;

存在NULL值条件 设计数据库时应该避免NULL值存在，尽可能给默认值。

select * from test where id is not null;

存在NOT条件 当查询条件为非时，索引定位困难，更倾向全表扫描。这类查询有<>、NOT、in、not exists

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

LIKE通配符 使用模糊查询时，尽量使用后置通配符。前匹配的情况更倾向全表扫描。

select * from test where name like 张||'%';

条件上括号函数 查询条件尽量不要对索引使用函数，因为索引建立和计算后有可能不同，无法定位所用。

select * from test where upper(name)='SUNYANG';

条件中有or 如果or中有列没加索引，那么就不会使用索引检索

21. limit 20000 加载很慢如何解决

limit是我们在分页中常用的基本操作之一。基本应用位select * from table limit m,n 其中m指记录开始的index，表示每次开始的索引，默认从0开始；n表示从第m+1条开始，取n条。

当数据量小的时候可以正常完成分页，但数据量达到百万级别时，需要进行性能优化。

**limit20000 , 20的意思是扫描满足条件的20020行，然后抛弃前面的20000行，取最后的20行。**但这样扫面前面的20000行，会造成时间的浪费。

SELECT * FROM a LIMIT 10,10 执行时间为0.024秒

SELECT * FROM a LIMIT 440000,10 执行时间为0.112秒（2百万数据集）

优化方案1：利用自增id
当存在自增主键时，就可以利用where操作进行优化，非常简单粗暴
SELECT * FROM a WHERE a_id >= 440000 LIMIT 10 耗时0.026秒
缺点也很明显：id必须连续，同时不能有where语句对数据进行过滤破坏连续性

优化方案2：利用子查询

因为子查询是建立在索引上的，一般索引文件比数据文件要小很多，所以可以利用子查询先定位出要访问的数据，然后再进行查询。
SELECT * FROM a WHERE a_id >= (SELECT a_id FROM a ORDER BY a_id LIMIT 440000,1) LIMIT 10 耗时0.088秒

优化方案3：利用覆盖索引优化
当mysql查询完全命中索引的时候，成为覆盖索引，是非常快的。因为查询只需要在索引上进行查找，之后就可以直接返回。所以，我们可以先查出索引的id，然后根据id查数据。
SELECT * FROM (SELECT a_id FROM a limit 440000,10) t1 LEFT JOIN a t2 on t1.a_id = t2.a_id; 耗时0.086秒

22. 说说分库与分表设计

22.1 分表概述

随着用户数和数据量不断增加，会使单表压力越来越大，查询一次的时间也会变长，如果有联合查询情况下，会造成很大的瓶颈。

通过分表，可以减少数据库的单表负担，把压力分散到不同的表上，提高查询性能，缩短查询时间的作用。

分表策略可归纳为垂直拆分和水平拆分。
垂直拆分：把表的字段进行拆分，把一张字段比较多的表拆为多张表，使行数据变小。
水平拆分：把表的行进行拆分。因为表的行数超过几百万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

22.2 分库概述

库内分表，仅仅解决单表数据过大的问题，但没有把单表的数据分散到不同的物理机上，因此不能减轻MySQL的压力。

分库策略也可分为垂直分库和水平分库

垂直拆分，按照业务和功能划分，把数据分别放到不同的数据库中。举个例子，可以划分资讯库、百科库等。

水平拆分，**把一张表的数据划分到不同的数据库，两个数据库的表结构一样。**实际上，水平分库与水平分表类似，水平拆分有许多策略，例如，取模分库，自定义 Hash 分库等，在不同策略分库情况下，根据各自的策略写入与读取。举个例子，随着业务的增长，资讯库的单表数据过大，此时采取水平拆分策略，根据取模分库。

参考博客：http://blog.720ui.com/2017/mysql_core_08_multi_db_table/

23. 分库与分表带来的分布式困境与应对 之策（如何解决分布式下的分库分表，全局表？）

数据迁移与扩容问题 、表关联问题、分页与排序问题、分布式事务问题、分布式全局唯一性ID

参考博客：http://blog.720ui.com/2017/mysql_core_09_multi_db_table2/