SQL

数据库分页

数据库分页语法

在MySQL中，SELECT语句默认返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，以实现分页查询。

  1. -- 这样查询可以查到前5条
  2. SELECT * from user limit 5
  3. -- 从第9条记录开始查询5条
  4. select * from user 9,5

总之，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

优化limit分页

在偏移量非常大的时候，例如 LIMIT 10000,20 这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面的10000条记录都将被抛弃，这样的代价是非常高的。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

要优化这样的分页查询，最简单的方法就是尽可能的使用索引覆盖扫描，不要查询所有的列。

-- 对于下面的查询，就可以进行优化
SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

SELECT film.film_id,film.description 
FROM sakila.film
INNER JOIN (
    SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5
) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。

表跟表是怎么关联的

表语法上看：关联方式有内连接和外连接。
内连接：

  • 内连接通过INNER JOIN来实现，它将返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来。

外连接：

  • 左外连接：可以简称为左连接（LEFT JOIN），它会返回左表中的所有记录和右表中满足连接条件的记录。
  • 右外连接：可以简称为右连接（RIGHT JOIN），它会返回右表中的所有记录和左表中满足连接条件的记录。

实际上，外连接还有一种形式：完全外连接（FULL OUTER JOIN），但MySQL不支持这种形式。

表关系来看：分为一对多关联，多对多关联，自关联

  • 一对多关联：这种关联形式最为常见，一般是两张表具有主从关系，并且以主表的主键关联从表的外键来实现这种关联关系。另外，以从表的角度来看，它们是具有多对一关系的，所以不再赘述多对一关联了。
  • 多对多关联：这种关联关系比较复杂，如果两张表具有多对多的关系，那么它们之间需要有一张中间表来作为衔接，以实现这种关联关系。这个中间表要设计两列，分别存储那两张表的主键。因此，这两张表中的任何一方，都与中间表形成了一对多关系，从而在这个中间表上建立起了多对多关系。
  • 自关联：自关联就是一张表自己与自己相关联，为了避免表名的冲突，需要在关联时通过别名将它们当做两张表来看待。一般在表中数据具有层级（树状）时，可以采用自关联一次性查询出多层级的数据。

SQL注入

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作。

如何解决SQL注入

  1. 严格的参数校验参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可。
  2. SQL预编译。当将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。我们常用的JDBC就有预编译功能，不仅提升性能，而且防止SQL注入。

将一张表的部分数据更新到另一张表，该如何操作

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。

update b set b.col=a.col from a,b where a.id=b.id;
update b set col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

WHERE和HAVING有什么区别

where是一个约束声明，使用where约束来自数据库的数据，where在结果返回之前起作用，不能使用聚合函数。
having是过滤声明，在查询结果集以后对查询结果进行过滤操作，在having中可以使用聚合函数，另一方面，having字句中不能使用除了分组字段和聚合函数之外的其他字段。
从性能角度来说，having字句中如果使用了分组字段作为过滤条件，应该替换成where字句，因为where可以在执行分组操作和聚合函数之前过滤掉不需要的数据，性能会更好。

索引

说一说对索引的理解

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速的找出在某个或多个列中某一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且也不是每种存储引擎都支持所有的索引类型。
MySQl中索引的存储类型有两种，即BTREE和HASH。但是MyISAM和InnoDB存储引擎都只支持BTREE索引。

索引的优点

  1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
  4. 在使用分组和排序字句进行数据查询时，也可以显著减少查询中分组和排序的时间。

创建索引的缺点

主要是体现在（时间，空间，速度）这三方面。

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  2. 索引需要占用磁盘空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果有大量的索引，索引文件可能比数据文件还要大。
  3. 当对表中的数据进行增加、删除合并修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引有哪几种

MySQL的索引可以分为以下几类：

  1. 普通索引和唯一索引
  • 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
  • 唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引是一种特殊的唯一索引，不允许有空值。
  1. 单列索引和组合索引
  • 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
  • 组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
  1. 全文索引

在MySQL中只有MyISAM存储引擎支持全文索引。

  1. 空间索引

空间索引只能在存储引擎为MyISAM的表中创建。

如何创建及保存MySQL的索引

  1. 在创建表的时候创建索引。

使用create创建表的时候，定义各种约束（如主键约束，外键约束，唯一性约束），不论创建何种约束，在定义约束的同时相当于在指定的列上创建了一个索引。

  1. 在已经存在的表上创建索引。

可以使用alter table语句，或者使用create index语句。

alter table book add unique index UniqidIdx(bookId);

create unique index UniqidIdx on book (bookId);

MySQL 怎么判断要不要加索引

  1. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需要能确保定义的列的数据完整性，以提高查询速度。
  2. 在频繁的进行排序或分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

不适合创建索引的情况

  1. 频繁更新的字段不适合建立索引
  2. where条件中用不到的字段不适合建立索引
  3. 数据比较少的表不需要建立索引
  4. 数据重复且分布比较均匀的字段不适合建索引，例如性别、真假值
  5. 参与列计算的列不适合建立索引

建立了索引就一定走索引吗

不一定，

  1. 使用LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。
  2. 在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。
  3. 使用OR关键字的查询语句，只有OR前后的两个条件都是索引时，查询才会使用索引。

如何判断数据的索引有没有生效

使用explain命令

索引的实现原理

不同的存储引擎对索引的实现方式是不同的。

MyISAM索引实现：

MyISAM使用B+TREE作为索引结构，叶节点的data域存放的是数据记录的地址。
因此，MyISAM中索引检索的算法为首先按照B+TREE搜索算法搜索索引，如果指定的Key存在，则取出data域的值，然后以data域的值为地址，读取相应数据记录。

如果我们为其他字段建立一个辅助索引，则索引的结构也是一颗B+TREE，data域中也是保存数据记录的地址。

InnoDB索引实现

虽然InnoDB也是用B+TREE作为索引结构，但具体的实现方式却与MyISAM截然不同。MyISAM索引文件和数据文件是分离的，索引文件仅仅存储保存数据记录的地址。
在InnoDB中，数据文件本身就是索引文件，表数据本身就是按照B+TREE组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按照主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则自动生成一个隐含字段作为主键，这个字段长度为6字节，类型为长整型。

为其他字段建立一个辅助索引，辅助索引存储相应记录主键的值而不是地址。InnoDB的所有辅助索引都引用主键作为data域，所以使用辅助索引搜索需要走两遍索引，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

所以，知道了InnoDB索引的实现，就很容易明白为什么不建议使用过长的字段作为主键，因为所有的辅助索引都会引用主索引，过长的主索引会使辅助索引变得过大。而且，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据本身就是一棵B+TREE，非单调的主键会造成在插入新记录时B+TREE频繁分裂调整，十分低效，使用自增主键作为主键则是一个很好地选择。

数据库索引重建

什么时候需要重建索引呢

  1. 表上频繁发生update，delete操作。
  2. 表上发生 alter table ..move操作。

怎么判断索引是否需要重建

  1. 一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析，可以使用analyze index index_name validata structure;来分析。

重建索引

不要先drop原索引然后创建索引，这种方法相当耗时。
可以直接重建索引：

alter index indexname rebuild;
alter index indexname rebuild online;

如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。

MySQL的索引为什么用B+树

B+TREE是由B树和索引顺序访问方法发展而来，专门为磁盘或其他直接存取辅助设备设计的一种平衡查找树。

MySQL的Hash索引和B树索引有什么区别

hash索引底层是hash表，进行查找的时候，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
B+TREE底层实现是多路平衡查找树，对于每一次查询都是从根节点出发，查找到叶子节点才开始获得查找的键值，然后再根据查询判断是否需要回表查询数据。

  • hash索引进行等值查询更快，但是无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+TREE的所有节点皆遵循（左节点小于父节点，右节点大于父节点），天然支持范围。
  • hash索引不支持使用索引进行排序
  • hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。
  • hash索引不稳定，性能不可预测。

因此，大多数情况下，直接选择B+TREE索引就可以获得稳定且较好的查询速度，而不需要使用hash索引。

什么是联合索引

联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建方法一样，不同之处仅在于有多个索引列。本质上讲，联合索引还是一棵B+TREE，不同的是键值数量不是1，而是大于等于2。联合所以遵循最左前缀集合。

select in语句中如何使用索引

索引是否起作用，主要取决于字段类型

  • 如果字段类型为字符串，需要给in查询中的数值与字符串都需要添加引号，索引才能起作用。
  • 如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。

聚集索引和非聚集索引有什么区别

在InnoDB中，可以将B+树的索引分为聚集索引和辅助索引（非聚集索引），无论何种索引，每个页的大小都是16KB，且不能更改。
聚集索引是根据主键创建的一棵B+树，聚集索引的叶子节点存放了表中所有记录。辅助索引是根据索引键创建的一棵B+树，其叶子结点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子结点以后，很有可能还需要根据主键值查找聚集索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含记录的有数据，这就意味着每页可以存放更多的键值，因为其高度一般都要小于聚集索引。

事务

说一说你对数据库事务的了解

事务需要遵循ACID四个特性；
A：原子性。指事务是不可分割的工作单位。
C：一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。
I：隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常使用锁来实现。
D：持久性。事务一旦提交，结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。

ACID的特性是怎么实现的

  • 原子性：实现原子性主要是靠事务的回滚。事务回滚的时候会撤销之前所有已经成功执行的语句。InnoDB实现回滚靠的是undo log。undo log属于逻辑日志，它记录的是SQL执行相关的信息。
  • 持久性：
    • 数据是存在磁盘中的，但是如果每次读写都经过磁盘IO，效率会很低。所以，InnoDB提供了缓存（Buffer Pool），Buffer Pool会用作访问数据库的缓冲，数据库读取数据时，会先从Buffer pool中读取数据，如果Buffer Pool中没有数据，则从磁盘中读取后放入Buffer Pool，当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称之为刷脏）。
    • Buffer Pool的使用大大提高了读写效率。但是带来了新的问题，那就是如果MySQL宕机，Buffer Pool中的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
    • 所以，redo log被引入用来解决这个问题。当数据被修改时，除了在Buffer Pool中修改数据，还要在redo log中记录这次操作。如果数据库宕机，重启时可以读取redo log中的数据，从而对数据库进行恢复。
  • 隔离性：隔离性追求的是并发情形下事务之间互相不干扰。
    • 一方面，一个事务写操作对另一个事务写操作的影响。使用锁机制来保证隔离性。
    • 另一方面，MVCC保证隔离性。MVCC（多版本并发控制协议），它最大的优点就是不加锁，因此读写不冲突，并发性能好。
  • 一致性：保证原子性，持久性，隔离性，如果这些特性没法保证，事务的一致性也没办法保证。

事务可以分为几种类型

  • 扁平事务：扁平事务是事务类型中最简单的一种，也是实际生产环境中使用最频繁的事务。在扁平事务中，所有操作都属于同一层次，由BEGIN WORK开始，由COMMIT WORK或者ROLLBACK WORK结束。处于之间的操作都是原子的，要么都执行，要么都回滚。
  • 带有保存点的扁平事务：允许事务在执行过程中回滚到同一事务中较早的一个状态。
  • 链事务：可以当做保存点模式的一个变种。链事务的思想是在提交一个事务的时候，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务。提交事务和开始下一个事务合并为一个原子操作。所以下一个事务将看到上一个事务的结果，就好像是在一个事务中进行的。
  • 分布式事务：在一个分布式环境下运行的扁平事务。

对于MySQL的InnoDB引擎来说，就只支持以上三种事务。

事务可以嵌套吗

可以，嵌套事务也是众多事务分类的一种。一个顶层事务控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务，它控制着每一个局部的变换。

需要注意的是，MySQL不支持嵌套事务。

事务的隔离级别

SQL定义了4中隔离级别，为了解决脏读，幻读，不可重复读的问题。
分别是读未提交、读已提交、可重复度、可串行化。InnoDB引擎实现的可重复度。

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 可能 可能 可能
READ COMMITTED 不可能 可能 可能
REPEATABLE READ 不可能 不可能 可能
SERIALIZABLE 不可能 不可能 不可能

并发情况下，读操作会存在的三种问题：
脏读：一个事务读取到了另一个事务未提交的数据（脏数据）。
不可重复度：一个事务先后两次查询数据库，两次读取的数据不一样。
幻读：一个事务先后两次查询数据库，两次读取的结果行数不一样。

脏读与不可重复读的区别是：脏读读到了其他事务未提交的数据，不可重复读读到的是其他事务已经提交的数据。
幻读与不可重复读的区别是：幻读是表的行数变了，不可重复读是表的数据变了

MySQL的事务隔离级别是怎么实现的

读未提交：没有加任何锁，可以说是没有隔离。
可串行化：读的时候加共享锁，其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

为了解决不可重复读，MySQL采用了MVCC的方式。我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。
如下图，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。
image.png

读已提交和可重复读都是创建快照，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读已提交是每次执行语句的时候都重新生成一次快照，两者的区别主要就是在快照的创建上。
快照能读到哪些版本数据，要遵循以下规则：

  • 当前事务内的更新，可以读到
  • 版本未提交，不能读到
  • 版本已提交，但是是在快照创建后提交的，不能读到。
  • 版本已提交，并且是在快照创建之前提交的，可以读到。

可重复读的隔离级别下解决幻读是使用的间隙锁。

image.png
在事务A提交之前，事务B的插入操作只能等待，就是间隙锁起的作用。

事务的回滚

使用ROLLBACK语句。

锁

InnoDB引擎实现了两种标准的行级锁

InnoDB存储引擎实现了如下两种标准的行级锁：

  • 共享锁（S Lock），事务可以读不能写
  • 排他锁（X Lock），其他事务不能读也不能写。

还有意向锁，也就是表级锁:意向锁是相互兼容的，因为IX IS只是表明申请更低层别的元素
IS 意向共享锁:
IX 意向排它锁:

死锁

解决死锁问题最简单的方法是超时，即当两个事务争夺资源发生死锁的时候，当一个等待时间超过设置的某一个阈值的时候，其中一个事务回滚，释放资源，另一个等待的事务就可以继续进行。
除了超时机制，当前数据库还都普遍采用等待图的方式来进行死锁检测。图中如果存在回路，就代表存在死锁。

间隙锁

间隙锁用来锁定一个范围，但不包含记录本身，它的作用是为了阻止多个事务将记录插入到同一范围内，这会导致幻读的产生。

InnoDB的行级锁是怎么实现的

InnoDB的行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表级锁。

优化

说一说对数据库优化的理解

MySQL的优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。

针对查询，我们可以通过使用索引，使用连接代替子查询的方式来提高查询速度。
针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询得原因，从而有针对性的进行优化。
针对插入，可以通过禁用索引，禁用检查等方式俩提高插入速度，在插入之后再启用索引和检查。
针对数据库结构，我们可以通过将字段很多的表拆分成多张表，增加中间表，增加冗余字段等方式进行优化。

如何优化MySQL的查询

  1. 使用索引
    1. 使用索引需要注意并不是所有情况都会走索引
  2. 优化子查询
    1. 在MySQL中，可以使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中再使用索引，性能会更好。

怎样插入数据才更高效

对于InnoDB的表

  1. 禁用唯一性检查
  2. 兼用外键检查
  3. 禁用自动提交

表中包含几千万条数据该怎么办

  1. 优化SQL和索引
  2. 增加缓存，如memcached,redis
  3. 读写分离，可以采用主从复制，也可以采用主主复制
  4. 使用 Mysql 自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的
  5. 做垂直拆分，根据模块的耦合度，将一个大的系统分为多个小的系统
  6. 做水平拆分。

Mysql慢查询优化

MySQL中慢查询默认是关闭的，可以通过配置文件打开。

其他

说一说你对redo log、undo log、binlog的了解

binlog（Binary Log）：
二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。
默认情况下，二进制日志功能是开启的，启动时可以重新配置—log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。
redo log：
重做日志用来实现事务的持久性，即事务ACID中的D。它由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），它是持久的。
InnoDB是事务的存储引擎，它通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成，即redo log和undo log。
redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。
undo log：
重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。
redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段（segment）中，这个段称为undo段（undo segment），undo段位于共享表空间内。

MySQL主从同步是如何实现的

复制（replication）是mysql数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作原理分为三个步骤：

  1. 主服务器把数据更改记录到二进制日志（binlog）中
  2. 从服务器把主服务器的二进制日志复制到自己的中继日志中
  3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，已达到数据的最终一致性

image.png