数据库 面试题

概念和专有名词解释

客户端与服务端

存储引擎

字符集

一些常用字符集

mysql中的utf和uft8mb4

utf_general_ci是Mysql默认的比较规则

MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别、列级别。

mysql - InnoDB数据页结构

地址

记录在页中的存储

I/O成本、CPU成本

事务

临时表与普通表

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

通过实例通俗理解脏读、不可重复读、幻读

脏写：如果一个事务修改了另一个未提交事务修改过的数据， 就意味着发生了脏写现象

脏读的严格解释:也就是T1先修改了数据项x的值，然后T2 又读取了未提交事务T1针对数据项x 修改后的值，之后T1中止而T2 提交,这就意味着T2 读到了一个根本不存在的值

不可重复读的严格解释:也就是T1先读取了数据项x的值，然后T2又修改了未提交事务T1读取的数据项x的值,之后T2提交，然后T1再次读取数据项x的值时会得到与第一次读取时不同的值。 （T1重复读了两次,但结果不一致）

幻读的严格解释:也就是T1先读取符合搜索条件Р的记录，然后T2写入了符合搜索条件Р的记录。之后T1再读取符合搜索条件P的记录时，会发现两次读取的记录是不一样的。

知识点

一些知识点的截图

位于不同主机上的客户端和服务端是如何通信的

深入理解内连接、外连接、on的出现

表t1,t2

通俗理解hash索引

地址

书上所讲数据结构一般都由管理信息和具体数据组成

mysql命令行授权

地址

面试题

题目汇总地址

mysql架构

地址

词法分析和语法分析

简单来说， 如果将程序比作一个英语句子：

词法分析就是对程序的逐个字符进行分析，看看字符写没写对， 是不是符合规范，如语句@int a=3中，@这个字符错了，因此在这一步会报错。

语法分析就是把字符组合成单词， 然后看看单词是不是拼写对了， 如语句：int1 a=3;中，int拼错了，因此在这一步会报错

句法分析则是看看这些单词组成的句子是不是很通顺， 如语句: int int a=3; int int 不符合规范，因此在这一步会报错。

对应中文里的字->成语->句子

何为事务

设计数据库的大叔为了方便起见，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为事务(transaction)。

事务的四大特性（ACID原则）

原子性（Atomicity）:在现实世界中，转账操作是一个不可分割的操作。也就是说，要么压根儿就没转，要么转账成功;不能存在中间的状态，也就是转了一半的这种情况。设计数据库的大叔把这种“要么全做，要么全不做”的规则称为原子性。

一致性（Consistency）：数据库世界只是现实世界的映射，现实世界中存在的约束当然也要在数据库世界中有所体现。如果数据库中的数据全部符合现实世界中的约束，我们就说这些数据就是一致的，或者说符合十致性的。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

隔离性（Isolation）:保证其他的状态转换不会影响到本次状态转换，这个规则称为隔离性。例如

持久性（Durability）:当现实世界中的一个状态转换完成后，这个转换的结果将永久保留，这个规则被设计数据库的大叔称为持久性。比如，狗哥向猫爷转账，ATM机提示转账成功时，就意味着这次账户的状态转换完成了，狗哥就可以拔卡走人了，转账后的状态会保留下来。
当把现实世界中的状态转换映射到数据库世界时，持久性意味着该次转换对应的数据库操作所修改的数据都应该在磁盘中保留下来，无论之后发生了什么事故，本次转换造成的影响都不应该损失。

数据库的三大范式

第一范式(1NF):强调的是列的原子性，即列不能够再分成其他几列。
考虑这样一个表：【联系人】（姓名，性别，电话）
如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。

第二范式(2NF):首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

第三范式(3NF):第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。
其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。

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

通过实例通俗理解脏读、不可重复读、幻读

事务的隔离级别

通俗易懂的地址
前文介绍了在并发事务执行过程中可能会遇到的一些现象，这些现象可能会对事务的一致性产生不同程度的影响。我们按照可能导致一致性问题的严重性给这些现象排一下序:
脏写>脏读>不可重复读>幻读
前文所说的“舍弃一部分隔离性来换取一部分性能”在这里就体现为:设立一些隔离级别,隔离级别越低，就越可能发生越严重的问题。有一帮人制定了一个SOL标准，在标准中设立了4个隔离级别。
READ UNCOMMITTED:读未提交
READ COMMITTED:读已提交。
REPEATABLE READ:可重复读。
SERIALIZABLE:可串行化。

也就是说:

• 在 READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读现象;
• 在 READ COMMITTED隔离级别下，不可能发生脏读,可能发生不可重复读和幻读现象;
• 在 REPEATABLE READ隔离级别下，不可能发生脏读和不可重复读的现象，可能发生幻读现象;
• 在 SERIALIZABLE隔离级别下，上述各种现象都不可能发生。

脏写是怎么回事儿?怎么上面都没提到呢?这是因为脏写这个现象对一致性影响太严重了，无论是哪种隔离级别，都不允许脏写的情况发生。

聚簇索引、二级索引、联合索引

通俗理解B+树索引结构（详解）？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息,我们可以先来看一看在mysql中具体的B+树索引

其实我们可以把索引理解成在我们查字典时的目录,为什么这么说呢，我们先来看看我们是如何通过拼音来查一个【帮】字的

1.打开总目录，然后找到汉语拼音音节索引所在的起始页p10

2.翻到汉语拼音音节索引(它的名字也叫索引,这样就更好理解了呀)，然后找到bang拼音的起始页p14

3. 然后我们在p14页顺序搜索找到【帮】字

此时我们再回过头来看前面B+树的那个实例结构图,是不是感觉到了惊人的相似,那个页33不就是字典里的总目录嘛，页30和页32不就是对应着汉语拼音音节索引和部首检字表的起始页，页10、页28…这样存放用户记录的的叶子节点，不就对应着字典中像p14这样存放真正数据的页面嘛，而一条条的用户记录不就是那一个个的字嘛，可能有差异的无非是在mysql中根据主键的大小给用户记录进行排序的，而字典中的排序是按照拼音的排序规则来进行排序的。

InnoDB表的主键生成策略

这里需要提一下InnoDB表的主键生成策略:优先使用用户自定义的主键作为主键;如果用户没有定义主键，则选取一个不允许存储NULL值的UNIQUE键作为主键;如果表中连不允许存储NULL值的UNIQUE键都没有定义，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

索引的优缺点？

索引的优点：
① 建立索引的列可以保证行的唯一性，生成唯一的rowId（）

② 建立索引可以有效缩短数据的检索时间

③ 建立索引可以加快表与表之间的连接（比如 select A.ID,B.ID,因为这个时候虽然需要访问表里所有的数据，但是可以直接用索引的数据，进行关联，因为只需要返回两个表的ID字段的值。而相对于表来说，索引中只包含了ID字段的值，所以占用的磁盘空间，相对于表来说，会小很多，所以扫描整个索引所需要的时间，远小于扫描全表的时间，本质上就是访问的数据页数少了，IO次数少了，所需要的时间就少）

④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

索引的缺点：

① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

什么是最左匹配原则？

地址

实例讲解

explain执行计划参数详解

什么情况下需要建索引?什么情况下不建索引

哪些情况需要创建索引：
1.主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3.查询与其它表关联的字段，外键关系建立索引（多表关联查询）
4.单键、组合索引的选择问题，组合索引性价比更高（mysql自动选择最优）
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段（（分组order by )包含(排序group by））

哪些情况不要创建索引：
1.表记录太少
2.经常增删改的表或者字段（例如电商项目的用户余额）
3.Where条件里用不到的字段不创建索引
4.过滤性不好的不适合建索引（手机号、身份证号之类过滤性好）

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

(1) Hash索引不能进行范围查询，而B+树可以。
这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
(2)Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。
对于联合索引来说，Hash索引在计算Hash值的时候基将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或多个索引时，联合索引无法被利用。
(3)Hash索引不支持Order BY排序，而B+树支持。
因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段Order By排序优化的作用。
(4)Hash索引无法进行模糊查询。而B+树使用LIKE进行模糊查询的时候，LIKE后面前模糊查询(比如%开头)的话可以起到优化的作用。
(5) Hash索引在等值查询上比B+树效率更高。

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

B树

B+树

B+ 树和 B 树的区别？

1.B树非叶子结点和叶子结点都存储数据,因此查询数据时，时间复杂度最好为O(1),最坏为O(log n)。

B+树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为O(log n)。

2.B+树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

1. B+树更加适应磁盘的特性，相比B树减少了I/O读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机I/O读取次数相对就减少了。
   2.B+树的查询效率相比B树更加稳定，由于数据只存在在叶子结点上，所以查找效率固定为O(log n)。
   3.B+树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+树的效率更高(而我们一般的查询语句都是范围查询或者有序遍历)。

索引有什么分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引、联合索引。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

覆盖索引

索引中已经包含所有需要读取的列的查询方式称为覆盖索引

索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的？

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文
检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

**8．小表不应建立索引；包含大量的列并且不需要搜索非空值的时候可以考虑不建索引
**

索引什么时候会失效

1.模糊查询以 "%"开头索引失效，因为不确定 % 的值
2.使用 or 的时候会失效，如果使用 or 要求条件两边都要有索引，才会使用索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。
3.使用复合索引的时候，假如创建一个（a,b)的联合索引，由于最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引，所以当我们查询a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。
4.在where当中索引列参加了运算，索引失效

5.在where列中使用了函数

6.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

什么是前缀索引

常见的存储引擎有哪些

InnoDB 引擎(MySQL5.5以后默认使用)，MySQL 5.5 及以后版本中的默认存储引擎，他的优点如下：
1.最主要是支持事务
2.灾难恢复性好
3.使用行级锁
4.支持外键关联
5.实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
6.支持热备份
7.对于InnoDB引擎中的表，主键索引和数据是放在一起的，即索引即数据,数据即索引

2.MyISAM引擎
特性如下：
1.不支持事务
2.主机宕机后，MyISAM表易损坏，灾难恢复性不佳
3.使用表级锁，并发性差
4.MyISAM不支持外键
5.只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
6.数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
7.MyISAM存储引擎的表会把索引信息单独存储到一个索引文件中，将表中的记录按照记录的插入顺序单独存储在一个数据文件中，而且MyISAM索引的叶子节点中存储的不是完整的用户记录，而是主键值与行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录!这就意味着二MyISAM中建立的索引相当于全部都是二级索引

MVCC 实现原理

READ COMMITTED和REPEATABLE READ隔离级别下，生成Readview的时机不同。
1.READ COMMITTED
每次读取数据前都生成一个ReadView
2.REPEATABLE READ
在第一次读取数据时生成一个ReadView,也就是说在REPEATABLE READ隔离级别下，事务的两次查询得到的结果是一样的，记录的name列值都是’刘备’。这就是可重复读的含义。如果我们之后再把事务id为200的记录进行提交，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，得到的结果还是’刘备’。(这段话需要看课本p403)

快照读和当前读

当前读：每次读取的都是当前最新的数据，但是读的时候不允许写，写的时候也不允许读，共享锁+排他锁+Next-Key Lock实现的。
快照读：就是读取快照数据，也就是说当某个数据正在被修改的时候，也可以进行读取该数据，保证读写不冲突，MVCC+undolog实现的
隔离级别Repeatable Read下（默认隔离级别）：有可能读取的不是最新的数据
Read Committed隔离级别下：快照读和当前读读取的数据是一样的，都是最新的。

共享锁和排他锁

共享锁（S锁）：共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

• 如果事务T仅对数据A进行读取，那么会对数据A加上共享锁，之后则其他事务如果要读取数据A的话可以对其继续加共享锁，但是不能加排他锁（也就是无法修改数据）。获准共享锁的事务只能读数据，不能修改数据。

排他锁（X锁）：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

• 如果事务T对数据A要进行修改，则需要对其添加排它锁，加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

大表如何优化

一、数据库设计及索引优化
1.时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率
2.建议字段定义not null，null值很难查询优化且占用额外的索引空间
3.使用TINYINT类型代替枚举ENUM
4.字段长度严重根据业务需求来,不要设置过大
5.优化sql和索引,比如在创建复合索引时，根据最左匹配原则和业务需求，where子句中使用最频繁的一列放在最左边

二、加缓存

三、就做主从复制或主主复制，读写分离

四、根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统

五、针对数据量大的表，进行拆分

Mysql执行计划

执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。MySQL 为我们提供了 EXPLAIN 语句，来获取执行计划的相关信息。需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

redo log --说过的话一定要做到

redo：重做

undo log --后悔了怎么办

undo：取消

bin log

Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，，比如有insert、update、delete、create table、alter table等操作，不记录select、show，因为这些操作不会产生任何更改。不过就算一个update未产生数据变化，也是会被记录进去的。

bin log和redo log有什么区别

Redo Log和 Binlog 区别

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的Server层实现的，所有引擎都可以使用。
• redo log 是物理日志，binlog 是逻辑日志，逻辑日志：可以简单理解为记录的就是sql语句(bin：binary，二进制为01，春纯纯的数字逻辑)。 物理日志：因为 mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更
• Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
• Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制。Binlog没有自动crash-safe能力。 总结：redo log配合uodo log可以用来支持事务，biglog无法支持事务
  (crash-safe(crash-宕机)即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的持久性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力依赖的就是redo log和unod log两个日志。)

可能有人会问，为什么会有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM是不支持事务得的，也没有崩溃恢复(crash-safe)的能力，binlog日志只能用于归档。那么既然InnoDB是需要支持事务的，那么就必须要有崩溃恢复(crash-safe)能力，所以就使用另外一套自己的日志系统，也就是基于redo log 来实现 crash-safe 能力。

分库分表

通俗易懂的地址

缺点：
1、联合查询困难

联合查询不仅困难，而且可以说是不可能，因为两个相关联的表可能会分布在不同的数据库，不同的服务器中。

2、需要支持事务

分库分表后，就需要支持分布式事务了。数据库本身为我们提供了事务管理功能，但是分库分表之后就不适用了。如果我们自己编程协调事务，代码方面就又开始了麻烦。

3、跨库join困难

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 我们可以使用全局表，所有库都拷贝一份。

4、结果合并麻烦

比如我们购买了商品，订单表可能进行了拆分等等，此时结果合并就比较困难。

什么是分区表

表分区分为水平分区和垂直分区
水平分区将表分为多个表。每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月或几个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。

而垂直分区则是将原始表分成多个只包含较少列的表
考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。
因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

mysql中select和update语句执行过程

地址

exit和in的区别

in 语句只执行一次
确定给定的值是否与子查询或列表中的值相匹配。
in 在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。
所以相对内表比较小的时候，in 的速度较快

exists 语句：执行n次（外表行数）
指定一个子查询，检测行的存在。
遍历循环外表，检查外表中的记录有没有和内表的的数据一致的。
匹配得上就放入结果集。

in 和 exists 的区别： 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in， 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用 exists。

其实我们区分 in 和 exists 主要是造成了驱动顺序的改变（这是性能变化的关键），如果是 exists，那么以外层表为驱动表，先被访问，如果是 in ，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系 ，另外 in 是不对 NULL 进行处理。
in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。一直以来认为 exists 比 in 效率高的说法是不准确的。

MySQL中int(10)和char(10)的区别

varchar(m),char(n)里面的m或n代表的是字符的个数，但是，在varchar中，因为是变长，所以需要1-2个字节来标识这一列的长度。如果varchar字段定义中带有default null允许列空,则需要需要1bit来标识，每8个bits的标识组成一个字段。一张表中存在N个varchar字段，那么需要（N+7）/8 （取整）bytes存储所有的NULL标识位。

整型跟char、varchar不同，整型分为tinyint，smallint，mediumint，bigint
原来，在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。
int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。
说白了，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的
根据上面的结论int(11)和int(1)表示的数字的范围是一样的，那么设置int(M)中的M的意义是什么呢？其实设置M得和zerofill结合起来才会生效

truncate、delete与drop区别

delete，drop，truncate 都有删除表的作用，区别在于：

• 1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
• 2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
• 3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

having和where的区别

地址

MySQL主从同步

随着业务的增长，一台数据服务器已经满足不了需求了，负载过重。这个时候就需要减压了，实现负载均衡读写分离，一主一丛或一主多从。
主服务器只负责写，而从服务器只负责读，从而提高了效率减轻压力。

主从复制可以分为：

• 主从同步：当用户写数据主服务器必须和从服务器同步了才告诉用户写入成功，等待时间比较长。
• 主从异步：只要用户访问写数据主服务器，立即返回给用户。
• 主从半同步：当用户访问写数据主服务器写入并同步其中一个从服务器就返回给用户成功。

为什么需要主从同步

1.读写分离，缓解数据库压力(主数据库用于数据写入，从数据库用于数据读取)。
2.发扬不同表引擎的优点
目前Myisam表的查询速度比innodb略快，而写入并发innodb比myIsam要好。那么，我们可以使用innodb作为master，处理高并发写入，使用Myisam作为slave，接受查询。或在myisam slave中建立全文索引，解决innodb无全文索引的弱点。
3.一主多从，系统可扩展性和可用性高。
4.数据备份容灾，异地双活，保证主库异常随时切换，提高系统容错能力。

乐观锁和悲观锁

地址

processlist

地址

关系模型

关系(表)只是元组(行)的集合

索引下堆

地址

SQL优化

地址
原文太长且注意点太多，所以在此按自己的经验和思路总结一些常用的方法
总结在SQL优化中，就三点:

• 最大化利用索引；
• 尽可能避免全表扫描；
• 减少无效数据的查询；

首先我们需要先建立一张表，所以我们肯定要在表结构上做一些文章

1. 在表中建立索引，优先考虑where、order by使用到的字段。
   2.尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

既然我们在表中建立索引，肯定需要最大化的利用索引的，但下面这些操作会导致索引失效，所以我们应该尽量避免

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)`

优化方式：如果是连续数值，可以用between代替。如下

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0` 

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描
可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9`

6.使用复合索引的时候，假如创建一个（a,b)的联合索引，由于最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引，所以当我们查询a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

7.order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;

-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

然后我们就需要对写的SQL语句进行优化了

1. 避免出现select *
2. 多表关联查询时，小表在前，大表在后。
   在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
   例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，你品，那就先去吃个饭再说吧是吧。
3. 用where字句替换HAVING字句
   避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。
   where和having的区别：where后面不能使用组函数
4. 调整Where字句中的连接顺序
   MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

接下来我们可以会通过命令来修改表中的数据和结构

1. 大批量插入数据
   如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。
   方法一：

insert into T values(1,2); 

insert into T values(1,3); 

insert into T values(1,4);

方法二：

Insert into T values(1,2),(1,3),(1,4);

选择后一种方法的原因有三。

减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
在特定场景可以减少对DB连接次数
SQL语句较短，可以减少网络传输的IO。

2. 适当使用commit
   适当使用commit可以释放事务占用的资源而减少消耗

联合索引详解

ABC联合索引生效问题
我们对a,b,c建立联合索引，创建如下表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` varchar(20) NOT NULL DEFAULT '' COMMENT 'aid',
  `bid` varchar(20) NOT NULL DEFAULT '' COMMENT 'bid',
  `cid` varchar(20) NOT NULL DEFAULT '' COMMENT 'cid',
  PRIMARY KEY (`id`),
  KEY `abc` (`aid`,`bid`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert into test values(1,'a1','b1','c1');
insert into test values(2,'a2','b2','c2');
insert into test values(3,'a3','b3','c3');

explains参数讲解

当我们指行下列语句时

explain select * from test where aid='a1'  and bid='b1';

ref里面有两个const，说明a和b的索引都被用上了
当我们执行如下语句时,

explain select * from test where aid='a1'  and cid='c1';

我们发现ref里面只有一个const，说明这里只用到了a的索引，b的索引和c的索引都没有用到，自我理解为b没有条件限制，就相当于全表扫描了，所以没用到索引。

计算数字的时候除了float，怎样更精确的表示