数据库常见知识点

数据库

Mysql的金额用什么数据类型表示

在mysql中，金额用“DECIMAL”类型。DECIMAL类型是专门为财务相关问题而设计的数据类型，能够解决数据的范围和精度的问题，常用于货币数据，如价格，工资，帐户余额等；它实际上是以字符串的形式存放的，可在定义时划定整数部分以及小数部分的位数，语法“DECIMAL(M,D)”，参数M是数字的最大数（精度），参数D是小数点右侧数字的数目（标度）。

嵌套查询（子查询）怎么实现

嵌套查询的意思是，一个查询语句(select-from-where)查询语句块可以嵌套在另外一个查询块的where子句中，称为嵌套查询。其中外层查询也称为父查询，主查询。内层查询也称子查询，从查询。
子查询：
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，为了让读者更加清楚子查询的概念。
子查询返回结果
子查询可以返回的数据类型一共分为四种：
–查询公司之中工资最低的雇员的完整信息
SELECT * FROM emp e WHERE e.sal=(SELECT MIN(sal) FROM emp);

事务

事务的作用

事务的提出主要是为了解决并发情况下保持数据一致性的问题（类似于多线程）
事务（Transaction）是并发控制的基本单位。所谓的事务，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行,在关系数据库中,一个事务可以是一条SQL语句、一组SQL语句或整个程序。 。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务的使用场景

有个买东西的网站，你下单买了某个货品，那么数据库中的订单表会插入一条你下单的记录，同时货物表需要把你所购买货品的数量进行更新，候新增订单和更新货品数量必须是一致的，不应该出现有人下了单但是货品数量不变，也不应该货品数量减少了而订单却没有新增这种情况发生
那么插入订单信息和更新货品数量就是一个完整的事务，要么都执行成功，如果两个操作中有任意一个操作失败，则整个流程就应该是失败的，已经执行的操作就应当回滚
在数据库中使用事务，可以保证多个数据库操作的一致性
这就是一个简单的事务的使用场景

数据库事务包含哪些语句

什么是事务？
事务是由一个或多个sql语句组成的一个整体，如果所有语句执行成功那么修改将会全部生效，如果一条sql语句将销量+1，下一条再+1，倘若第二条失败，那么销量将撤销第一条sql语句的+1操作，只有在该事务中所有的语句都执行成功才会将修改加入数据库中。

除了普通的SQL语句之外，还有

事务控制语句：
commit； 提交（确认操作，写到硬盘上）
rollback； 回滚（回退）
savepoint； 保存点名
rollback to ； 回滚（回退）到某个点

事务的基本要素（ACID）四大特性

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的ACID属性是如何实现的？（原子性通过回滚日志undo log实现；持久性通过重做日志redo log实现；隔离性通过锁和MVCC实现；而一致性则是通过原子性、隔离性、持久性来实现，只有满足这三个特性，才能实现事务的一致性）

原子性

1. 定义
   原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
2. 实现原理：undo log

持久性

1. 定义
   持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
2. 实现原理：redo log

隔离性

• 定义
  与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响；隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明)，那么隔离性的探讨，主要可以分为两个方面：

• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

2. 锁机制
首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。
锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。
行锁与表锁
按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

事务的并发问题（脏读、不可重复度、幻读）

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
幻读
幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

例子：
在事务1中，查询User表id为1的是用户否存在，如果不存在则插入一条id为1的数据。
在事务1查询结束后，事务2往User表中插入了一条id为1的数据。
此时，由于事务1查询到id为1的用户不存在，因此插入1条id为1的数据。
但是由于事务2已经插入了1条id为1的数据，因此此时会报主键冲突，对于事务1 的业务来说是执行失败的，这里事务1 就是发生了幻读，因为事务1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。这里要灵活的理解读取的意思，第一次select是读取，第二次的insert其实也属于隐式的读取，只不过是在mysql的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

mysql默认的事务隔离级别是什么？如何理解可重复读？

mysql默认的事务隔离级别为repeatable-read（可重复读）

1、事务隔离级别为读提交时，写数据只会锁住相应的行
2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。可以避免脏度，不可重复度，不可避免幻读
3、事务隔离级别为串行化时，读写数据都会锁住整张表
4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

为什么可重复读隔离级别也可以解决幻读？（通过Next-Key Lock可以消除幻读）**

首先说结论，在RR的隔离级别下，Innodb使用MVCC和next-key locks解决幻读，MVCC解决的是普通读（快照读）的幻读，next-key locks解决的是当前读情况下的幻读。
Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题
MVCC
多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别的实现。
实现(隔离级别为可重复读)
在说到如何实现前先引入两个概念：
系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。
在MySQL中，会在表中每一条数据后面添加两个字段：
创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

当前读
所谓当前读，指的是加锁的select(S或者X), update, delete等语句。在RR的事务隔离级别下，数据库会使用next-key locks来锁住本条记录以及索引区间。
拿上面那个例子来说，在RR的情况下，假设使用的是当前读，加锁了的读
select * from table where id>3 锁住的就是id=3这条记录以及id>3这个区间范围，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录。
普通读
因为普通读是不会加锁的读，故不会有next-key locks的使用，解决幻读的手段是MVCC
MVCC会给每行元组加一些辅助字段，记录创建版本号和删除版本号。
而每一个事务在启动的时候，都有一个唯一的递增的版本号。每开启一个新事务，事务的版本号就会递增。
默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

• SELECT 读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的
• INSERT 将当前事务的版本号保存至行的创建版本号
• UPDATE 新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
• DELETE 将当前事务的版本号保存至行的删除版本号

如何理解Mysql默认的事务隔离级别可重复读？

在SQL标准中，前三种隔离级别分别解决了幻象读、不可重复读和脏读的问题。那么，为什么MySQL使用可重复读作为默认隔离级别呢？
这个是有历史原因的，要从主从复制开始讲起了！
1.主从复制，是基于什么复制的？
是基于binlog复制的
Mysql binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)
2.binlog有几种格式？
statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合
那Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！
接下来，就要说说当binlog为STATEMENT格式，且隔离级别为读已提交(Read Commited)时，有什么bug呢？如下图所示，在主(master)上执行如下事务:
此时在主库中查询：

select * from t;
输出结果：
+---+---+
| c1 |c2
+---+---+
| 2 | 2
+---+---+
1 row in set
从库中查询：
select * from t;
输出结果：
Empty set

这里出现了主从不一致性的问题！原因其实很简单，就是在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致！
如何解决？
解决方案有两种！
(1)隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住！
(2)将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题！奈何这个格式在mysql5.1版本开始才引入。
因此由于历史原因，mysql将默认的隔离级别设为可重复读(Repeatable Read)，保证主从复制不出问题。

数据库三大范式

第一范式：第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式：满足第二范式（2NF）必须先满足第一范式（1NF）
第二范式要求实体中每一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。
若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式：
满足第三范式必须先满足第二范式。
第三范式要求：实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。
如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

常用SQL语句

数据库的左联、右联、内联、全联

MySQL联合查询效率较高，以下例子来说明联合查询（内联、左联、右联、全联）的好处：

内联（ inner join ）

首先取出a表中所有数据,然后再加上与a,b匹配的的数据
如果想把用户信息、积分、等级都列出来，那么一般会这样写：

• select * from T1, T3 where T1.userid = T3.userid

这样的结果等同于：

• select * from T1 inner join T3 on T1.userid=T3.userid

把两个表中都存在userid的行拼成一行（即内联），但后者的效率会比前者高很多，建议用后者（内联）的写法。

SQL语句：

• select * from T1 inner join T2 on T1.userid = T2.userid

运行结果

左（外）联（ left [outer] join ）

左联就是以左边的表为基准，把右表符合条件的表接入到左表中，左边的表的数据不变，如果右边没有对应的数据，则展示NULL
显示左表T1中的所有行，并把右表T2中符合条件加到左表T1中；
右表T2中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：

• select * from T1 left join T2 on T1.userid = T2.userid

运行结果

• T1.userid username password T2.userid jifen dengji 1
  jack jackpwd 1 20 3 2 owen owenpwd NULL NULL
  NULL
  

右（外）联（ right [outer] join ）

右联就是以右边的表为基准，把左表符合条件的表接入到右表中，右边的表的数据不变，如果左边没有对应的数据，则展示NULL
显示右表T2中的所有行，并把左表T1中符合条件加到右表T2中；
左表T1中不符合条件，就不用加入结果表中，并且NULL表示。

SQL语句：

• select * from T1 right join T2 on T1.userid = T2.userid

运行结果

全联（ full join ）

全联就是把左表和右表的结果记录行数相加，其实就是左联+右联
显示左表T1、右表T2两边中的所有行，即把左联结果表 + 右联结果表组合在一起，然后过滤掉重复的。

SQL语句：

• select * from T1 full join T2 on T1.userid = T2.userid

运行结果

Having的作用，一般和什么一起配合使用

sql中的having语句是在使用group by的时候使用的。
通常where语句是在group by之前做数据筛选的，而having语句是对group by之后的结果进行筛选的。
group_by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组，分组后返回每个组的第一条记录。

MySQL实现分页查询

一般情况下，客户端通过传递 pageNo（页码）、pageSize（每页条数）两个参数去分页查询数据库中的数据，在数据量较小（元组百/千级）时使用 MySQL自带的 limit 来解决这个问题：

收到客户端{pageNo:1,pagesize:10}
select * from table limit (pageNo-1)*pageSize, pageSize;

收到客户端{pageNo:5,pageSize:30}
select * from table limit (pageNo-1)*pageSize,pageSize;

Limit接受一个或两个数字参数。参数必须是一个整数常量。

一个参数：它表示返回最大的记录数。从第一行开始算。

两个参数：参数1是指定第一个返回的记录行的索引+1，参数2是最后返回最大的记录数
有两个参数的时候，代表从第一个参数所记录行的索引+1开始，参数2是返回的记录数，也就是从第一个参数+1开始，到第一个参数+第二个参数

//检索前 5 个记录行
SELECT * FROM table LIMIT 5;

//检索记录行6-15
SELECT * FROM table LIMIT 5,10;

MySql常见函数

聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

数学函数

数学函数是MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等

函数名称	作用
ABS(x)	返回x的绝对值
BIN(x)	返回x的二进制
CEILING(x)	返回大于x的最小整数值
EXP(x)	返回值e（自然对数的底）的x次方
FLOOR(x)	返回小于x的最大整数值

行转列函数

PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
即将原本同一列下多行的不同内容作为多个字段，输出对应内容
完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

先来看一下转换后的结果：

UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

空值赋初值函数
oracle 通过 nvl( )函数sql 查询时为 空值 赋默认值

如何获取 MYSQL 插入数据的自增 ID

使用@@identity

方法一：select @@IDENTITY

select @@IDENTITY

@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定 义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据 后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。

方法二:是使用getGeneratedKeys()

Connection conn = ;
Serializable ret = null;
PreparedStatement state = .;
ResultSet rs=null;
try {
    state.executeUpdate();
    rs = state.getGeneratedKeys();
    if (rs.next()) {
        ret = (Serializable) rs.getObject(1);
    }
} catch (SQLException e) {
}
return ret;

总结一下，在mysql中做完插入之后获取id在高并发的时候是很容易出错的。另外last_insert_id虽然是基于session的但是不知道为什么没有测试成功。

Mysql的存储引擎有几种，默认是什么

InnoDB存储引擎（默认）
MyISAM存储引擎
MEMORY存储引擎
ARCHIVE存储引擎
CSV存储引擎

Myisam和InnoDB存储引擎的区别？

Myisam不支持外键也不支持事务，支持的是表锁，当执行select操作时，自动给涉及的表加表锁，当执行增删改操作，自动给涉及的表加写锁；InnoDB支持外键也支持事务，支持的是行锁，当执行select操作时，不加任何锁，当执行增删改操作，自动给涉及的行加写锁

索引

Mysql的索引有哪几种

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索
索引的最左原则（左前缀原则）
如（c1,c2,c3,c4…cN）的联合索引，where 条件按照索引建立的字段顺序来使用（不代表and条件必须按照顺序来写），如果中间某列没有条件，或使用（遇到范围查询）like会导致后面的列不能使用索引。
命名规则：表名_字段名
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则
https://segmentfault/q/1010000003984016/a-1020000003984281
联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

两个或更多个列上的索引被称作复合索引。
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。
复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。
如果您知 道姓，电话簿将非常有用；
如果您知道姓和名，电话簿则更为有用，
但如果您只知道名不姓，电话簿将没有用处。

联合索引在不满足最左原则、遇到范围查询(>、<、between、like)就会停止匹配、中间列没有条件的情况下都会失效

PRIMARY KEY(主键索引)创建语句为：

-- 在创建表时创建索引
CREATE TABLE <表名>(
 PRIMARY KEY(字段名)
);

-- 用ALTER TABLE语句传创建
ALTER TABLE <表名>
ADD PRIMARY KEY(字段名);

UNIQUE(唯一性索引)创建语句为：

-- 在创建表时创建索引
CREATE TABLE <表名>(
 UNIQUE[INDEX][<索引名>](字段名)
);

-- 用ALTER TABLE 语句传创建
ALTER TABLE <表名>
ADD UNIQUE(字段名);

-- 用SQL语句创建
CREATE UNIQUE INDEX <索引名>
ON <表名>(字段名);

INDEX(普通索引)创建语句为：

-- 在创建表时创建索引
CREATE TABLE <表名>(
 INDEX[<索引名>](字段名)
);

-- 用ALTER TABLE 语句传创建
ALTER TABLE <表名>
ADD INDEX(字段名);

-- 用SQL语句创建
CREATE INDEX <索引名>
ON <表名>(字段名);

查看索引语句为：

SHOW INDEX FROM <表名>;

删除索引语句为：

DROP INDEX <索引名> ON <表名>;

聚簇索引、非聚簇索引、回表查询、覆盖索引

聚簇索引
聚簇索引是指索引的叶子节点中存储了整行数据,因此在查询时可以直接通过该索引一次得到该行的所有数据；一般聚簇索引的前提是:

• 字段为该表主键
• 一个表中只有一个聚簇索引(但可以通过覆盖索引的方式来模拟聚簇索引的特性)
• 索引的存储结构只能为B+Tree

非聚簇索引（辅助索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行

回表查询
一个表中除了聚簇索引外都是非聚簇索引(好像有点废话),其在查询时一般会通过索引得到主键的值,然后再拿主键的值去查询聚簇索引得到最终的数据行,这样的查询叫二级索引,或者也叫回表查询；但是也有例外,如下面的覆盖查询中的情况就不会进行回表

覆盖索引
覆盖索引是指,在表中有一个联合索引,若查询时,查询时select的字段为该索引的一个项,且where的也是该索引的一个项,此时就可以通过索引一次性拿到查询结果
例如 Student表中有字段number和name,且这两个字段组成联合索引key(number,name),则使用如下查询将使用覆盖索引查询:
select name from Student where number=1
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低

索引失效的情况？（违反最左前缀法则、范围查询右边的列索引失效、字符串不加单引号、对索引列进行运算、头部模糊匹配、使用不等于！=或者<>）**

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子句也遵循此规则。

sql中where和having的区别

用的地方不一样
where可以用于select、update、delete和insert into values(select * from table where …)语句中。
having只能用于select语句中
执行的顺序不一样
where的搜索条件是在执行语句进行分组之前应用
having的搜索条件是在分组条件后执行的
即如果where和having一起用时，where会先执行，having后执行
子句有区别
where子句中的条件表达式having都可以跟，而having子句中的有些表达式where不可以跟；having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以。
总结
1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
2.GROUP BY 子句用来分组 WHERE 子句的输出。
3.HAVING 子句用来从分组的结果中筛选行

什么字段适合建立索引

表的主键、外键必须有索引；
数据量超过300的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where或者Having子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

数据库调优

说说数据库性能优化有哪些方法？

答：使用explain进行优化，查看sql是否充分使用索引。避免使用in,用exist替代，字段值尽可能使用更小的值，任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。使用连接查询(join)代替子查询。
在表的多列字段上建立一个索引，但只有在查询这些字段的第一个字段时，索引才会被使用。

explain分析执行计划、SQL语句的优化

使用explain 分析sql 语句，能够显示mysql的执行计划，了解mysql是如何执行sql。为我们如何进行sql优化提供重要的参考信息
explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
使用方法
在需要执行的sql语句前加上explain，执行

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

Id：代表执行select子句或操作表的顺序
Select_type：查询的类型,主要用于区别普通查询,联合查询,子查询等复杂查询
Table：显示一行的数据时关于哪张表的
Type：查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到ref
Possible_keys：显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的
Key：实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。
Key_len：表示索引中使用的字节数,而通过该列计算查询中使用的 索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的
Ref：显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值
Rows：根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数
Extra：包含不适合在其他列中显式但十分重要的额外信息

数据库调优的几种主要入手方向：

一.创建索引

要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

二.避免在索引上使用计算

在where字句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表查询,函数属于计算的一种,同时在in和exists中通常情况下使用EXISTS，因为in不走索引。

效率低：

    select * from user where salary*22>11000(salary是索引列)

效率高：

    select * from user where salary>11000/22(salary是索引列)        

三.尽量将多条SQL语句压缩到一句SQL中

每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

四：用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

五：使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

六：架构优化：数据库读写分离，主库写，从库读；

七：使用连接查询(join)代替子查询

在表的多列字段上建立一个索引，但只有在查询这些字段的第一个字段时，索引才会被使用。

数据库中主库和从库的关系

主从数据库的建立一般基于以下三个方面考虑：
1、容灾：备库在异地，主库不存在了，备库可以立即接管，无须恢复时间
2、负载均衡：主库做增删改，备库做查询，这样很多查询业务不占用主库资源
3、数据集中和分发：此种模式主要用于数据从分公司集中到总公司，或从总公司分发到分公司，前提是公司需要同步的数据很少，另外各公司间业务系统不是同一家公司开发的
同步功能主要通过数据库同步软件实现的，象ORACLE的DATAGUARD、QUEST的SHAREPLEX、沃信科技的PAC、ORACLE的GOLDEN GATE、迪思杰的REALSYNC
但是建议可以用沃信科技的产品，因为只有他们一家产品是安装到主备之外的第三台机器上的，不站用主库资源，其他产品必须安装到主库机器上，大家试用起来很不方便

需要注意的是：修改主库中的表，从库中的表也会跟着修改，而修改从库时，主库不会跟着修改

什么是主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

主从复制的作用（好处，或者说为什么要做主从）重点
1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

主从复制的原理（重中之重，面试必问）：

1.数据库有个bin-log二进制文件，记录了所有sql语句。

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

4.下面的主从配置就是围绕这个原理配置

5.具体需要三个线程来操作：

1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。

在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。
步骤一：主库db的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库
步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.
步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db.

mysql数据库备份命令：mysqldump

查看主从复制状态命令：show slave status

间隙锁是什么？行锁升级为表锁的情况？

间隙锁（Gap Lock）是Innodb在Tex parse error！提交下为了解决幻读问题时引入的锁机制，（下面的所有案例没有特意强调都使用可重复读隔离级别）幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的解决的幻读

Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

加锁规则有以下特性，我们会在后面的案例中逐一解释：

1. 加锁的基本单位是（next-key lock）,他是前开后闭原则
2. 插叙过程中访问的对象会增加锁
3. 索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
4. 索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止 间隙锁是一个在索引记录之间的间隙上的锁。 间隙锁的作用

保证某个间隙内的数据在锁定情况下不会发生任何变化。比如mysql默认隔离级别下的可重复读（RR）
当使用唯一索引来搜索唯一行的语句时，不需要间隙锁定。如下面语句的id列有唯一索引，此时只会对id值为10的行使用记录锁。
select * from t where id = 10 for update;// 注意：普通查询是快照读，不需要加锁
如果，上面语句中id列没有建立索引或者是非唯一索引时，则语句会产生间隙锁。
**行锁和表锁：**在上一章节中我们学习了数据库的事务及其事务的隔离级别，但是数据库是怎样隔离事务的呢？这时候就牵连到了数据库锁。当插入数据时，就锁定表，这叫做”锁表”；当更新数据时，就锁定行，这叫做”锁行”。
行锁
顾名思义，行锁就是一锁锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引，如下图所示：

如上图所示，数据库表中有一个主键索引和一个普通索引，Sql语句基于索引查询，命中两条记录。此时行锁一锁就锁定两条记录，当其他事务访问数据库同一张表时，被锁定的记录不能被访问，其他的记录都可以访问到。
行锁的特征：锁冲突概率低，并发性高，但是会有死锁的情况出现。
我们使用代码演示一下，看看行锁的表现：我们还是使用上一篇文章中使用的数据库，打开两个窗口，我们在窗口A中根据id更新一条记录，然后在窗口B中也执行相同的SQL语句看看

可以看到，窗口A先修改了id为3的用户信息后，还没有提交事务，此时窗口B再更新同一条记录，然后就提示Lock wait timeout exceeded; try restarting transaction ，由于窗口A迟迟没有提交事务，导致锁一直没有释放，就出现了锁冲突，而窗口B一直在等待锁，所以出现了超过锁定超时的警告了。
但是，此时我们如果去更新id为3它旁边的记录看看会出现怎样的情况，我们新打开一个窗口更新id为2的记录看看

可以看到，在窗口B中更新id为3的记录报错，但是在窗口C中我们可以更新id为2的记录，这说明此时锁定了id为3的记录但是并没有锁定它旁边的记录。
就是在没有使用索引的情况下InnoDB就会使用表级锁(共享锁不会有这个情况)
索引列数据重复过多情况下，会导致索引失效，行锁变表锁
就是在没有使用索引的情况下InnoDB就会使用表级锁

表锁
顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录。

由于表锁每次都是锁一整张表，所以表锁的锁冲突几率特别高，表锁不会出现死锁的情况。
和上面一样，我们通过代码演示一下，看看表锁的表现，我们打开两个窗口，在窗口A中更新一条记录，条件为非索引字段，不提交事务，然后在窗口B中任意再更新一条记录，我们看看会出现怎样的现象：

上面，我们分别验证了一下mysq的行锁和表锁，我们可以看到，当更新数据库数据时，如果没有触发索引，则会锁表，锁表后再对表做任何变更操作都会导致锁冲突，所以表锁的锁冲突概率较高。

InnoDB的行锁有哪些？锁住的是行还是索引？（Record Lock、Gap Lock、Next-Key Lock；锁住的是索引，而不是行）**

(1) 记录锁(Record Locks)
针对主键和唯一索引，对某一行数据加锁；
(2) 间隙锁(Gap Locks)
针对普通索引，对某一段数据加锁；
(2) 临键锁(Next-Key Locks)
针对普通索引，且必须命中索引的情况下，将索引所在的段和后面一段同时加锁

mysql索引的底层实现，为什么用B+树不用B树？（B+树IO次数更少、更适合范围查询、查询效率更加稳定）**

作为B树的加强版，B+树与B树的差异在于

• 有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）。
• 所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接。
• 非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。
• 重点：
  B+树的查找过程，与B树类似，只不过查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。（理解核心）

B+树的特性如下：

• 所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的。
• 不可能非叶子节点命中返回。
• 非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
• 更适合文件索引系统。

MySQL为什么使用B树（B+树）

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。刚好B+数的IO次数比B树少。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

哈希表查找速度不是更快吗，为什么不直接使用哈希表来做索引的底层数据结构？（哈希表不支持范围查找）

哈希表只能处理=的情况，不支持范围查询

• hash表只能匹配是否相等，不能实现范围查找
• 当需要按照索引进行order by时，hash值没办法支持排序
• 组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了阿和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引
• 当数据量很大时，hash冲突的概率也会非常大
• B+树作为索引时，非叶子节点只保存索引，叶子节点才会保存数据，这样方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

数据库的分区、分片、分表、分库

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

垂直分表
垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段。
分表后的总表是没有数据的，数据都在分表上
它带来的提升是：

1. 为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响
2. 充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。

通常我们按以下原则进行垂直拆分:
把不常用的字段单独放在一张表;
把text，blob等大字段拆分出来放在附表中;
经常组合查询的列放在一张表中;

垂直分库：
垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。
例如把原有的SELLER_DB(卖家库)，分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)，并把这两个库分散到不同服务器。由于商品信息与商品描述业务耦合度较高，因此一起被存放在PRODUCT_DB(商品库)；而店铺信息相对独立，因此单独被存放在STORE_DB(店铺库)

它带来的提升是：
解决业务层面的耦合，业务清晰
能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈
垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是依然没有解决单表数据量过大的问题。

水平分库：
水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

垂直分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构

例如将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。
也就是说，要操作某条数据，先分析这条数据所属的店铺ID。如果店铺ID为双数，将此操作映射至RRODUCT_DB1(商品库1)；如果店铺ID为单数，将操作映射至RRODUCT_DB2(商品库2)。此操作要访问数据库名称的表达式为RRODUCT_DB[店铺ID%2 + 1] 。

它带来的提升是：
解决了单库大数据，高并发的性能瓶颈。
提高了系统的稳定性及可用性。

稳定性体现在IO冲突减少，锁定减少，可用性指某个库出问题，部分可用

水平分表：
水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

它带来的提升是：
优化单一表数据量过大而产生的性能问题
避免IO争抢并减少锁表的几率
库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。

总结：
垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。

垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。

水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。

水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

参考文章

分表和分区的区别：
实现方式上
分表：mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件
分区：分区就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上。分区跟分表不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。

数据处理上
分表：分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子：

select * from alluser where id='12'

表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。

分区：分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由表自己来完成。

提高性能上
分表：分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同 的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD（数据）文件现在也分摊到各个小表的.MYD中去了。

分区：mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。
在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

实现的难易度上
分表：分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

分区：分区实现是比较简单的，在表结构中加入partition。建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

mysql分表和分区有什么联系呢

1. 都能提高mysql的性高，在高并发状态下都有一个良好的表面。
2. 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

分区和分片的区别：
分片：Sharding不是一个某个特定数据库软件附属的功能，而是在具体技术细节之上的抽象处理，是水平扩展(Scale Out，亦或横向扩展、向外扩展)的解决方案，其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。
Sharding与数据库分区(Partition)的区别
　　有的时候，Sharding 也被近似等同于水平分区(Horizontal Partitioning)，网上很多地方也用水平分区来指代 Sharding，但二者之间实际上还是有区别的。的确，Sharding 的思想是从分区的思想而来，但数据库分区基本上是数据对象级别的处理，比如表和索引的分区，每个子数据集上能够有不同的物理存储属性，还是单个数据库范围内的操作，而 Sharding 是能够跨数据库，甚至跨越物理机器的。

JDBC连接数据库并处理数据有哪些操作步骤

1)加载Driver驱动

2)创建数据库连接对象Connection

3)创建一个可向数据库发送SQL命令并返回结果的传送对象Statement

4)执行SQL命令并处理返回结果

5)处理后，如果有返回结果集，关闭结果集对象ResultSet

6)关闭对应的 Statement 对象

7)关闭连接对象Connection

Mysql数据库连接池

什么是数据库连接池

官方：数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对连接池中的连接进行申请，使用，释放。

数据库连接池的基本思想是：为数据库连接建立一个缓冲池，预先在缓冲池中放入一定数量的连接，当需要建立连接的时候，只需从缓存池中取出一个，使用完后在放回去

理解：我们查询完数据库后，如果不关闭连接，而是暂时存放起来，当别人使用时，把这个连接给他们使用。就避免了一次建立数据库连接和断开的操作时间消耗。
我们知道，对于共享资源，有一个很著名的设计模式：资源池（resource pool）。该模式正是为了解决资源的频繁分配﹑释放所造成的问题。为解决上述问题，可以采用数据库连接池技术。数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接的数量﹑使用情况，为系统开发﹑测试及性能调整提供依据。

二. 传统的连接池机制与数据库连接池的运行机制区别

传统链接：一般来说，JAVA应用程序访问数据库的过程：

1） 装载数据库驱动程序；

2） 通过JDBC建立数据库连接；

3） 访问数据库，执行SQL语句；

4） 断开数据库连接；

使用了数据库连接池的机制：

1） 程序初始化时创建连接池；

2） 使用时向数据库申请可用连接；

3） 使用完毕，将连接返回给连接池；

4） 程序退出时，断开所有连接，并释放资源；

一. 为何要使用数据库连接池

假设网站一天有很大的访问量，数据库服务器就需要为每次连接创建一次数据库连接，极大的浪费数据库资源，并且极易造成数据库服务器内存溢出，拓机。

数据库连接池是一种关键的有限的昂贵的资源，这一点在多用户的网页程序中体现的尤为突出，对数据库连接的管理显著影响到整个应用程序的伸缩性和健壮性，影响程序的性能指标，数据库连接池正式针对这个问题提出的。数据库连接池负责分配，管理和释放数据库的连接，它允许应用程序重复使用一个现有的数据库连接，而不是重建一个。

数据库连接池在初始化时创建一定数量的数据库连接放到连接池中，这些数据在连接的数量上是由最小数据库连接数来设定的，无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量，连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数。当应用程序向连接池请求连接数量超过最大连接数量时，这些请求将被加入到等待队列中。

数据库连接池的最小连接数和最大连接数的设置要考虑以下几个因素：

1） 最小连接数：是连接池一直保持数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。

2） 最大连接数：是连接池能申请的最大连接数，如果数据库的连接请求超过次数，后面的数据库连接将被加入到等待队列中，这会影响以后的数据库操作。

3） 如果最小连接数与最大连接数相差很大，那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接，不过这些大于最小连接数的数据库连接在使用完不会马上被释放，它将放到连接池中等待重复使用或是时间超时后被释放。

二. 使用数据库连接池的关键点

1.并发问题

为了使连接池管理服务具有最大的通用性，必须考虑多线程环境，这个问题相对于好解决，因为各个语言自身提供了对于并发管理的支持，可以保证线程的同步。

2.事务处理

DB连接池必须要确保某一时间内一个connection只能分配给一个线程。不同conection的事务是相互独立的。

我们知道，事务具有原子性，此时要求对数据库的操作符合原子操作：要么不做，要么全做。

我们知道当2个线程共用一个连接Connection对象，而且各自都有自己的事务要处理的时候，对于连接池 是一个很头疼的问题，因为即使Connection类提供了相应的事务支持，可是我们仍然不能确定那个数据库操作是对应那个事务的，这是由于我们有两个线程都在进行事务操作而引起的，为此我们可以使用每一个事物独占一个连接来实现，虽然这种方法有点浪费连接池资源但是可以大大降低事物管理的复杂性。

3.连接池的分配与释放

连接池的分配与释放，对系统的性能有很大的影响。合理的分配与释放，可以提高连接的复用性，从而降低建立连接的开销，同时还可以加快用户的访问速度。

对于连接的管理可使用一个list，即把已经创建的连接都放入list中去同一管理。每当用户请求一个连接时，系统检查这个list中有没有可以分配的连接。如果有，就会把那个最合适的连接分配给他；如果没有就会抛出一个异常给用户，list中连接是否可以被分配有一个线程来专门管理稍后我会介绍这个线程的具体实现。

4.连接池的配置与维护

连接池到底该放置多少连接，才能使系统的性能最佳？系统可采取设置最小连接数和最大连接数等参数来控制连接池中的连接。

如何确保连接池中的最小连接数？有动态和静态两种策略。动态即每隔一段时间就对连接池进行检查，如果发现连接数量小于最小连接数，则补充相应数量的新连接，以保证连接池的正常运转。静态是发现空闲连接不够时再去检查。

三. 使用连接池的优势和其工作原理

1.连接池的优势

连接池用于创建和管理数据库连接的缓冲池技术，缓冲池中的连接可以被任何需要他们的线程使用。当一个线程需要用JDBC对一个数据库操作时，将从池中请求一个连接。当这个连接使用完毕后，将返回到连接池中，等待为其他线程服务。

连接池主要只有以下三个方面的优势：

• 减少创建连接的时间。连接池中的连接是已准备好的，可重复使用的，获取后可以直接访问数据库，因此减少了连接创建的次数和时间。
• 简化的变成方式。当使用连接池时，每一个单独的线程能够像创建一个自己的JDBC连接一样操作，允许用户直接使用的次数和时间。
• 控制资源的使用。 如果不使用连接池，每次访问数据库都需要创建一个连接，这样系统的稳定性受系统连接需求影响很大，很容易产生资源浪费和高负载异常。连接池能够使性能最大化，将资源利用控制在一定的水平下。连接池能控制池中的连接数量，增强了系统在大量用户应用时的稳定性。

2.连接池的工作原理

连接池技术的核心思想是连接复用，通过建立一个数据库连接池以及一套连接的使用，分配和管理策略，使得该连接池中的连接可以得到高效，安全的复用，避免数据库连接频繁建立，关闭的开销。

连接池的工作原理主要有三部分组成，分别为连接池的建立，连接池中连接的使用管理，连接池的关闭。

第一、连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中创建几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建与关闭。这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类可以方便的构建连接池，如Vector。Stack。

第二、 连接池的管理。 连接池管理策略施连接池机制的核心，连接池内连接的分配和释放都对系统性能有很大的影响。其管理策略是：

• 当用户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没有达到就重新创建一个连接给请求的用户；如果达到就设定最大等待时间进行等待，如果超出最大的等待时间，则抛出异常给用户。

• 当用户释放数据库连接时，先判断该连接的引用次数是否超出规定值，如果超出就从连接池中删除该连接，否则保留为其他客户服务。

该策略保证了数据库连接的有效复用，避免频繁的建立，释放连接所带来的系统资源开销。

第三、连接池关闭。当应用程序退出时，关闭连接池中的所有连接，释放连接池相关的资源，该过程正好与创建相反。

动态SQL:

mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼。动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询

if判断
用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态sql。
对查询条件进行判断，如果输入参数不为空才进行查询条件拼接。

foreach
向sql传递数组或List，mybatis使用foreach解析
需求
在用户查询列表和查询总数的statement中增加多个id输入查询

where语句和Choose(when,otherwise)
1.Where后面empno和ename为null，那where就不会出现在sql语句中。
where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉
2. choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

SQL片段：
有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
比如：假如我们需要经常根据用户名和性别来进行联合查询，那么我们就把这个代码抽取出来

trim
trim标记是一个格式化的标记，可以完成set或者是where标记的功能
可以通过 trim 标签去自定义where的这种处理规则

动态SQL和静态SQL两者的异同

静态SQL为直接嵌入到PL/SQL中的代码，而动态SQL在运行时，根据不同的情况产生不同的SQL语句。

静态SQL为在执行前编译，一次编译，多次运行。动态SQL同样在执行前编译，但每次执行需要重新编译。

静态SQL可以使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性。但缺乏灵活性

动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。

动态SQL容易产生SQL注入，为数据库安全带来隐患。

Mysql和Sql的区别

MySQL与sql的区别

MySQL与oracle，Microsoft access等一样，是关系型数据库，是一个用于存储的工具。而sql与java，Python一样，是一种数据库查询语言。通常关系型数据库都是支持用sql语言进行增删改查操作的。

SQL是一种用于操作数据库的语言

MySQL是市场上第一个可用的开源数据库之一

SQL用于访问，更新和操作数据库中的数据

MySQL是一种RDBMS，它允许保持数据库中存在的数据

SQL是结构化查询语言

MySQL是一个使用MYSQL存储，检索，修改和管理数据库的RDBMS

SQL是一种查询语言，而MYSQL是数据库软件

MySQL和Oracle的区别

(1) 对事务的提交
MySQL默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

(2) 分页查询
MySQL是直接在SQL语句中写"select… from …where…limit x, y",有limit就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询

(3) 事务隔离级别
MySQL是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的
读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session
查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块
MySQL没有类似Oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但
可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据

(4) 对事务的支持
MySQL在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务

(5) 保存数据的持久性
MySQL是在数据库更新或者重启，则会丢失数据，Oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复

(6) 并发性
MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。
虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。
Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并
发性的支持要好很多。

(7) 逻辑备份
MySQL逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用,Oracle逻辑备份时不锁定数据，且备份的数据是一致

(8) 复制
MySQL:复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据。且需要手工切换丛库到主库。
Oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出现问题是，可以自动切换备库到主库，但配置管理较复杂。

(9) 性能诊断
MySQL的诊断调优方法较少，主要有慢查询日志。
Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等

(10)权限与安全
MySQL的用户与主机有关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。
Oracle的权限与安全概念比较传统，中规中矩。

(11)分区表和分区索引
MySQL的分区表还不太成熟稳定。
Oracle的分区表和分区索引功能很成熟，可以提高用户访问db的体验。

(12)管理工具
MySQL管理工具较少，在linux下的管理工具的安装有时要安装额外的包（phpmyadmin， etc)，有一定复杂性。
Oracle有多种成熟的命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。

(13)最重要的区别
MySQL是轻量型数据库，并且免费，没有服务恢复数据。
Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务。

(13) 自动增长的数据类型处理
MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
(14) 单引号的处理
MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。
(15) 翻页的SQL语句的处理
MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；PHP里还可以用SEEK定位到结果集的位置。ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。

(16) 长字符串的处理
长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。

(17) 日期字段的处理
MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(‘<st1:chsdate isrocdate=“False” islunardate=“False” day=“1” month=“8” year=“2001”>2001-08-01</st1:chsdate>’,’YYYY-MM-DD’)年-月-日 24小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘<st1:chsdate isrocdate=“False” islunardate=“False” day=“1” month=“8” year=“2001”>2001-08-01</st1:chsdate>’,’YYYY-MM-DD HH24:MI:SS’)
日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME > SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到离当前时间7天用 DATE_FIELD_NAME >SYSDATE - 7;
(18) 空字符的处理
MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。
(19)字符串的模糊比较
MYSQL里用 字段名 like ‘%字符串%’,ORACLE里也可以用 字段名 like ‘%字符串%’ 但这种方法不能使用索引, 速度不快，用字符串比较函数 instr(字段名,‘字符串’)>0 会得到更精确的查找结果。
(20)程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。