不灬赖

自律>>自由>>自信
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
MySQL

 

转载

前言

对于Web三大模块的后端数据库模块，只要你是做过项目都会有涉及与了解，无论你是简单的增删改查，对于所写SQL语句都需要去查询才能够完善，亦或是对SQL语句都熟烂于心，无论是单表的查询操作，或是多表的组合查询都能够信手拈来，但这些也都是SQL语句部分，对于SQL数据库的基础真的是太多太多，面试时候遇到的每一家公司问到的也都很少出现重复，但是主要的知识点也就摆在哪里。

在下面会详细总结出来自己面试时候遇到过的SQL数据库问题，和查阅别人的面经整理出来的SQL复习信息，主要分为基础，索引，锁，和SQL语句等相关，也是尽力来网罗目前的SQL面试的大部分题目来增添自己的知识储备。

基础

数据库的三大范式

第一范式：每个列都不可以再拆分

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

数据库存储引擎的分类与区别

回答这个问题时候，首先说明MySQL在什么时候使用到的是那个存储引擎，然后再回答两者之间的区别，也不需要将那么多的区别都回答出来，只需要回答出几个更改比较大的点

答： 对于MySQL来说在5.5 版本之前使用到的是MyISAM，在5.5版本之后默认的存储引擎就是InnoDB。

区别：

 MyISAMInnnoDB
是否支持外键 不支持 支持
是否支持事务 不支持 支持
是否记录表格中行数 是 否
锁支持 表级锁定 行级锁，表级锁
哈希索引 不支持 支持
记录存储的顺序 按记录插入顺序保存 按主键大小有序插入
是否支持全文索引 支持 不支持

根据两个存储引擎可能出现的问题

  1. 有一个表中其ID是自增键，当插入了17条语句以后，删除了三条语句 15,16,17，此时重启MySQL数据库之后再插入一条语句，这个语句的id是15 还是18。

    当我们在使用到InnoDB做为存储引擎时候，若是说不进行重启的操作时候，这个id的值是18 但是若是进行了重新启动时候，对于InnoDB来说InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。所以这个时候id值就是15.

    但是我们使用到MyISAM时候，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

  2. 对于两个存储引擎，那个使用 select count(*) 更快

对于MyISAM来说 ，速度会更快一点，因为对于MyISAM来说，内部维持了一个计数器，所以进行这个操作时候，时间复杂度是O(1),但是对于 InnoDB来说，需要进行计算，所以时间复杂度是O(n)。

两个存储引擎的选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

数据库事务的四大特性以及实现原理

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

按照严格的标准，只有同时满足ACID特性才是事务；但是在各大数据库厂商的实现中，真正满足ACID的事务少之又少。例如MySQL的NDB Cluster事务不满足持久性和隔离性；InnoDB默认事务隔离级别是可重复读，不满足隔离性；Oracle默认的事务隔离级别为READ COMMITTED，不满足隔离性……因此与其说ACID是事务必须满足的条件，不如说它们是衡量事务的四个维度。

原子

概念：原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么全部做。如果对于一个事务来说其中的sql语句执行失败，则已经执行的语句也必须要回滚，数据库退回到事务之前的状态。

实现原理：对介绍原子性之前，对于Mysql的事务日志来说，InnoDB提供了两种事务日志： redo log(重做日志)和 undo log(回滚日志)。其中的重做日志保证的是事物的持久性，回滚日志保证的是事务的原子性和隔离性实现的基础。

下面来具体介绍对于 原子性实现的关键： 当事务出现回滚时候能够撤销所有已经成功执行的sql语句。
InnoDB能够实现回滚的主要原因就是靠undo log： 当事务对数据库进行修改的时候，InnoDB会生成对应的undo log ；如果此时事务执行失败或者调动了 rollback，导致事务出现回滚情况，可以利用undo log中的信息将数据回滚到修改前的样子

undo log 属于一个逻辑日志，它用来记录的是sql执行相关的信息。对于一个insert语句在回滚时候会执行delete，相反也是如此。例如对于一个update在执行的时候，其生成的undolog 中会包含被修改的主键（以便知道修改了哪些行，修改了哪些列）以便在回滚时候能够使用这些记录的信息将数据还原到执行之前。

一致

概念： 一致性就是MySQL 数据库最后追求的目标，数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如在完成一次的转账之后，无论是成功还是失败，总金额不能够发生变化，在对一个表完成一次操作之后，对于任意字段的类型属性不能够被修改。

隔离

概念：与原子性，持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。是指，事务内部的操作与其他的事务是隔离的，并发执行的各个事务之间是不能相互干扰。

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

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

持久

概念： 事务一旦提交，他对数据库的改变就是永久的，接下来的其他操作或是故障都不应该对其造成影响。

实现原理：前面提到了InnoDB实现了两个事务日志，首先我们来聊一下redo log 存在的背景。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：

如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。
  • 1

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

ACID的总结
  • 原子性： 语句要么都执行，要么都不是执行，是事务最核心的特性，事务本身来说就是以原子性历来定义的，实现主要是基于undo log
  • 持久性： 保证事务提交之后，不会因为宕机等其他的原因而导致数据的丢失，主要是基于 redo log实现
  • 隔离性： 保证事务与事务之间的执行是相互隔离的，事务的执行不会受到其他事务的影响。InnoDB存储引擎默认的数据库隔离级别是 RR ，RR又主要是基于锁机制，数据的隐藏列，undo log类 以及 next-key lock机制
  • 一致性： 事务追求的最终目标，一致性的实现即需要数据库层面的保障，也需要应用层面的保障。

redo log 与 binlog

我们知道，在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。
（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

  • 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
  • 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

数据库可能出现的问题：

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。此时读的是未提交。

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。读取到的行数不一样。

不可重复读： 在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。两次读取到的值不一样。

事务的隔离级别与MySQL 的默认隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别脏读幻读不可重复读
读未提交 √ √ √
读已提交 × √ √
可重复读 × × ×
可串行化 × × ×

读未提交： 顾名思义就是可以读取到未提交的数据，就会导致，脏读，幻读，和不可重复读。

读已提交：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

可重复读： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

可串行化： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

注意：对于MySQL来说默认的隔离级别是可重复度，对于Oracle 来说默认的隔离级别就是 读已提交。

MySQL 有哪些的数据类型

  1. 整数类型包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

    长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
    例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

  2. 实数类型 包括FLOAT、DOUBLE、DECIMAL。

    DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
    而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
    计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

  3. 字符串类型

    包括VARCHAR、CHAR、TEXT、BLOB
    VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
    VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
    VARCHAR存储的内容超出设置的长度时，内容会被截断。
    CHAR是定长的，根据定义的字符串长度分配足够的空间。
    CHAR会根据需要使用空格进行填充方便比较。
    CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
    CHAR存储的内容超出设置的长度时，内容同样会被截断。

  4. 枚举类型

    把不重复的数据存储为一个预定义的集合。
    有时可以使用ENUM代替常用的字符串类型。
    ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。
    ENUM在内部存储时，其实存的是整数。
    尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。
    排序是按照内部存储的整数

  5. 日期和时间类型

    尽量使用timestamp，空间效率高于datetime，
    用整数保存时间戳通常不方便处理。
    如果需要存储微妙，可以使用bigint存储。

索引

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

有哪几种索引？

主键索引: 数据列不允许出现重复字段，不允许为NULL值，并且对于一个表来说只能有一个主键。

唯一索引： 数据列不允许出现重复，但是允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引。
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引。

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引：

  • 通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引（目前只用MyISAM 支持全文索引）

怎么创建索引？

在 Create table时候进行创建。

可以创建其他类型的索引，但是不能够创建全文索引。

 create table text(
 id int auto_increment PRIMARY key,
 first_name varchar(20),
 last_name varchar(20),
 age int,
 address text,
 key name (first_name,last_name),// 联合索引
 fulltext key (address),// 全局索引
 UNIQUE key (id)// 唯一索引
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

image-20200721090547243

完成之后使用explain执行计划查看：发现 type类型为 ref：表示此时索引的使用类型为：使用非唯一索引或非唯一索引前缀进行的查找。

image-20200721091930782

在ALTER　TABLE 时候来创建索引

可以来创建普通，UNIQUE，或者是PRIMIARY KEY 索引

其中 table_name是要增加索引的表名，column_list表示是对哪些列进行创建索引

  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)
直接使用到CREATE INDEX 来创建索引

可以直接对表增加普通索引或者UNIQUE索引

  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

怎么删除索引？

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
  • ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

索引有哪些的优缺点

优点

  • 因为对于索引来说相当于我们的信息的目录，可以帮助我们快速的查询到我们的数据，不用在每一次的查询中，都需要对表进行遍历处理。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

  • 空间方面：索引需要占物理空间。

索引的数据结构

对于目前主流的数据结构就是B+树索引，与Hash索引，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

B+树索引原理与性质

对于现在MySQL索引基本上都是B+树，现在基本上所说的也都是B+树，一般都称作是B树，但是对于B树和B+树来说差别还是很大。

img

B+tree性质：

1.）n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.）B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5.）B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

可以看到其实对于B+树来说所有的信息都存储在叶子节点，这个时候在进行数据库查询时候，进行磁盘的查询，可以进行更快的查询，因为对于每次的查询的量是固定的，但是对于非叶子节点不存储信息，所有能够遍历更多的非叶子节点，并且所有的叶子节点的查询的路径的长度都是相同的。

为什么用B+树而不是B树

因为还是最开始的思想 减少磁盘的io的次数。因为尽可能的少减少磁盘的io的次数：
树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度

  1. b随机 b+支持随机和顺序
  2. B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
  3. B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
  4. B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
    增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

哈希索引

类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。
img

为什么要用B+树呢？

对于这个问题算是单独独立出来，来进行深刻的刨析以及底层的原理。

因为文件很大的时候，不可能将所有的信息记录都保存在内存中，需要一部分保存在磁盘上，但是当访问时候，访问磁盘需要进行查找，索引的目的就是能够快速的进行查找工作，索引索引的结构很关键，要尽量得减少在查找过程中磁盘I/O的存取次数，对于B+树来说 所有的数据域都保存在叶子节点上，并且没层的数据会比较多，为了就是能够分得更少的层次，在进行优化查找时候，每个节点到叶子节点的高度都是相同的 就是将所有的叶子节点使用指针串起来，这样就能够使用遍历叶子节点来获取数据。非叶子节点只保存索引，叶子节点才会保存数据。
为什么能够加快访访问速度

什么类型的sql语句不适合建立索引

  • 在查询中很少使用到的列不必要建立索引
  • 对于那些只有很少数值的列不适合建立索引
  • 对于那些定义为 text image 和 bit数据类型的列就不必要在增加索引，是因为这些列 要么数据量很列要么取值很小
  • 当修改性能远远超过检索性能时候 就没有必要再建立索引 是因为 修改性能和检索性能是相互矛盾的 增加索引会增阿基检索性能 但是降低修改性能。
    在那些情况下 索引会出现无法使用的情况：
  • 模糊查询时候 以 % 开头的like查询
  • 查询时候 数据类型出现隐式的转换
  • 在复合索引的情况下 查询条件不满足索引最左的原则。
  • 用 or分隔开的条件 or前条件有索引，但是or后的列没有索引。
    此时出现的情况就是 由于 Or后的列并没有索引的存在，所有在进行查询时候还是要进行全表的扫描，在存在全表扫描时候 就没有必要再进行一次索引扫描增加 IO访问。
  • 独立的列 索引不能是表达式的一部分，必须是独立的列。
  • 负向查询（not not in not like <> != !> !<） 等不能使用
  • 在估计全表扫描时候 比全表要快就可以不必要使用索引
  • 索引无法存储null值。

什么时候用索引

首先明白一点就是对于索引来说是为了给我们加快数据查询时候的速度，当我们在查询数据的时候难免会对数据进行检索操作，这个时候若是不走索引时候，where语句后面的检索就会对表进行遍历的处理，所以一般对于经常出现在where，等判断条件的字段上面添加索引。

例如如下操作：我们在没有创建索引时候，可以看到没有走索引信息，走的是全文查询 type的类型是ALL。

（具体的explain 执行计划会在下面讲到）

image-20200721100503119

现在为tags 来增添一个索引表示添加一个普通的索引：

image-20200721100059681

然后再来查询一遍，可以看到type的类型发生了改变，同时可能出现索引的key也发生了改变。

image-20200721100246980

当然在当前数据量比较小的情况下，暂时不会出现什么数据库访问压力问题，但是若是在数据库字段比较多时候查询就会出现查询时间过长的问题。

order by

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

join

对join语句匹配关系（on）涉及的字段建立索引能够提高效率
  • 1

对于这个问题，在面试时候会问到：“现在你已经为你表的某个字段创建了一个索引，如何来判断自己的这个索引有没有被使用到，或者说查看哪个字段能够判断索引的执行效率”

首先对我们需要执行的sql语句使用到explain执行计划，如下图所示。

image-20200721100246980

下面来具体刨析每个字段以及字段属性的具体值所代表的含义：

id：表示查询中各个子查询执行时候的执行顺序。

  • id相同执行顺序由上至下。
  • id不同，id值越大优先级越高，越先被执行。
  • id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type: 表示子查询的查询类型，下面列举常见的类型

idselect_type具体描述
1 SIMPLE 不包含任何子查询或union等查询
2 PRIMARY 包含子查询最外层查询就显示为 PRIMARY
3 SUBQUERY 在select或 where字句中包含的查询
4 DERIVED from字句中包含的查询
5 UNION 出现在union后的查询语句中

table： 表示在当前执行计划中走的是哪一个表。

type： 表示在此条sql语句中走的索引的类型，一般也都是看此字段，来判断此条sql语句执行的效率高不高。

  • ALL 扫描全表数据
  • index 遍历索引
  • range 索引范围查找
  • index_subquery 在子查询中使用 ref
  • unique_subquery 在子查询中使用 eq_ref
  • ref_or_null 对Null进行索引的优化的 ref
  • fulltext 使用全文索引
  • ref 使用非唯一索引查找数据

possible_keys: 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key : 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

key_length: 索引长度

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows： 返回估算的结果集数目，并不是一个准确的值。

extra 的信息非常丰富，常见的有：

  1. Using index 使用覆盖索引
  2. Using where 使用了用where子句来过滤结果集
  3. Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
注意：在上面的执行计划中我们查看完成之后主要是看**type**属性来判断当前的sql语句是否需要进行优化的处理。
至少要达到 range 级别，要求是ref级别，如果能够达到consts是最好的情况。 
说明： 
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。 
3） range 对索引进行范围检索。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

设计索引的原则

  1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  2. 基数较小的类，索引效果较差，没有必要在此列建立索引
  3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
  4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

索引创建时候的原则

  1. 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  2. 较频繁作为查询条件的字段才去创建索引
  3. 更新频繁字段不适合创建索引
  4. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
  5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  6. 定义有外键的数据列一定要建立索引。
  7. 对于定义为text、image和bit的数据类型的列不要建立索引。

使用索引一定能够提高性能？

通过建立索引并不一定一定能够对性能进行一个提升，在我们通常的情况下，在建立索引的基础上，一个查询走索引确实能够比走全表查询消耗更少的性能，但是我们也需要注意有些情况：

索引是真实存在，也需要消耗一定的存储空间，也需要进行定期的维护处理，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

  1. 对于范围的检索：且查询的结果集小于表中记录数的30%。
  2. 对于非唯一性索引的检索可以索引查询提高查询的效率。

尽管有索引的存在，但是什么时候会失效？

  1. 未使用最左匹配原则 例如就是说 组合索引 （A，B） 在where B=X 时候是不会使用到索引的
  2. like 未使用最左匹配原则
  3. 搜索一个索引时候 但是却在另外一个索引上做order by操作： where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；
  4. or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
  5. 如果列类型是字符串，要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；
  6. 在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

什么是聚簇索引与非聚簇索引

  • 聚簇索引： 并不是之前我们看到的一种单独的索引类型，而是一种数据存储的方式。具体的细节依赖于其实现方式。但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表中有聚簇索引时，它的数据行实际上存放在叶子页中（leaf page）。术语（聚簇）表示的是数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据存放在两个不同的地方，所以一个表中只能有一个聚簇索引。（不过覆盖引擎可以模拟多个聚簇索引的情况，在后面会进行介绍。
    聚簇索引的每一个叶子节点都包含了主键值，事务ID，用于事务和mvcc的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也使包含完整的主键列和剩下的其他列。
  • 非聚簇索引： 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

什么是联合索引，为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

什么是覆盖索引

mysql可以在使用索引来直接获取列的数据，这样就不需要来读取数据行，如果索引的叶子节点中以及包含了要查询的数据，那么还有什么必要再回表查询呢，如果一个索引包含（或者说是覆盖）所有需要查询的字段的值，我们就称为“覆盖索引”。
有什么好处：

  • 索引条目通常远小于数据行的大小，所以如果只需要读取索引，那Mysql就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。
  • 因为索引时按照列值顺序存储的（至少在当个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要小的多。
  • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键所以的二次查询。

什么是冗余索引与重复索引

重复索引指的是在相同列上按照相同的顺序创建的相同类型的索引。
举个例子

create table test(
ID int  NOT NULL primiary key,
A int not null,
B int not null,
unique(ID),
index(ID),
)engine=InnoDB;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

此时我们看到的是，一个用户想要创建一个主键，加上唯一限制，然后加上索引以供查询使用。事实上，MySql的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常没有这样做的理由，除非是在同一列上创建不同类型的索引来满足不同的查询需求。
冗余索引和重复索引有所不同，如果创建了索引（A,B),再创建索引 （A），就是冗余索引，因为这只是我们前面创建索引的一个前缀索引。因此索引（A,B）也可以当做索引（A）来使用，注意这里我们说的冗余只是对B-Tree来说的。但是如果再创建索引（B，A）却不能被视为冗余索引，同时索引（B）也不是，因为B不是索引（A，B）的最左前缀列。另外，其他不同类型的索引（例如哈希索引，或者是全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

在日常的工作中，冗余索引通常发生在为表添加新索引的时候，例如，有人可能会增加一个新的索引（A，B）而不是扩展已有的索引（A），还有一种情况就是将一个索引扩展为（A，ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中，所以这样的情况也是冗余的。

什么是回表查询

对于这个题目，切实在面试中遇到中，当时在问我索引的类型等，然后就谈论到“那你知道什么是回表查询？”

对于这个问题想要理解需要先明白对于Mysql索引大体分为两类：一个聚集索引，一个是普通索引：

InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。
1.如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
2.如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

就是说 当我们在使用非聚簇索引（对于那些非主键进行索引值时候）找到了记录的行，但是还是需要根据主键再次找到数据快里面对应的数据。
一个是一个表的ID是主键 我们查询
select * from stu where id=1;
是直接查询到的
但是还有一个name不是主键 有普通的索引
查询时候就需要进行一个回表的操作。

非聚簇索引来说一定会进行回表查询？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

锁

MyISAM 支持表锁，InnoDB 支持表锁和行级锁，默认是 行级锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发送锁冲突的概率比较高，并发处理效果较低。
行级锁： 开销大，加锁慢，会出现死锁，锁定粒度较大，发生锁冲突的概率会小一点，并发处理的效果高。

什么是数据库的死锁，怎么解决

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

什么是数据库的乐观锁与悲观锁，是如何实现

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

MySQL中InnoDB引擎的行锁是如何实现的

答：InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

一条SQL语句的执行流程

对于这个题目也是在面试中切实尽量过

例如我们有如下sql语句

select * from user where id=6
  • 1

就是查询一个id为6的用户的信息执行流程是

image-20200721150805998

客户端-》Server层-》 引擎层。

其中server层包括： 连接器，分析器，查询器，优化器，执行器
引擎层： 就是底层的引擎 主要负责是存储数据，提供读写的接口

连接

就是使用客户端或者命令行时候输入的用户名与密码进行的连接。在你连接成功以后，此用户操作数据的权限判断逻辑都将依赖查询到的权限，此时修改用户的权限也是无用，必须重新登录才行。这里说明一下对于客户端连接到的数据库而言，如果连接一直处于空闲的状态，那么到了一定的时间限制就会断开连接，这个连接的默认时间是8个小时。

查询缓存

对于连接成功以后，执行SQL时候会先行查询缓存，如果有相对应的数据就会直接返回给客户端进行显示，后面的步骤就不会再执行下去。
它的原理怎样的呢？请接着往下看，一条查询sql的首次执行完成后，会把sql语句作为key，把查询出来的数据作为value放入到缓存中，如果后面再有相同的查询，那么直接从缓存中取值便可。

看到这里也许你们会想缓存这么好用，那以后要多用缓存。别急，请接着往下看。查询缓存用起来确实好用，但是它有一个弊端，那就是当这个表做了更新操作时，那么此表的缓存将会全部清空。也许当你辛辛苦苦缓存起来的数据，还没来得及用时就可能被一条update语句给全部干掉。所以如果更新比较频繁的表是不适合使用缓存的，如果是某些配置表倒是比较适合缓存的使用。

分析器

当前面的不能够缓存命中的时候，sql就会开始真正执行流程，首先是分析器，包括语法分析，词法分析。
词法分析： 就是对sql语句中非每一个单词都逐个的做分析，比如 对于 select来说 就会识别出来时查询语句。user 就会分析出来时 表user 然后对应的信息一一对应。
语法分析就会分析： 写的这条语句是否满足sql的语法，若是你对于 where语句以后没有给定的限制信息就会报错，对于 select后来没有选中值也会报错等

优化器

对于 执行完分析器以后，就能够知道所执行的是什么类型的语句 是查询，还是插入等，进行优化。例如 使用表的哪个索引；或者是进行表的关联，决定关联的顺序等等问题。

执行器

当完成了优化以后，就表示此sql语句选择好了一个比较完拖的方案，可以进行执行阶段。首先要先检查你进行的操作是否有对应的表的权限。如果有了权限以后，执行器会根据定义的表的引擎，来执行引擎所对应的接口信息 mysql5.5之后存储引擎是 InnoDB。
其大致的流程如下：

  1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是6，如果不是则跳过，如果是则将这行存在结果集中；
  2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
    3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

如果是 id 是有索引的，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，基本和上面差不多。这些接口都是引擎中已经定义好的。至此 一条 sql 便执行完成。

一条SQL语句的生命周期

  1. 应用服务器与数据库服务器建立一个连接

  2. 数据库进程拿到请求sql

  3. 解析并生成执行计划，执行

  4. 读取数据到内存并进行逻辑处理

  5. 通过步骤一的连接，发送结果到客户端

  6. 关掉连接，释放资源

    image-20200721214245493

如何实现MySQL的主从复制

首先在实现主从复制之前，先对一些基础的术语有一个认识
image-20200721214732413
DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。

DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。
DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

主从复制

主从的区别：

因为对于MySQL来说性能是完完全全有限的，对于单点数据的读取的性能也是有很严重的上限要求。所以我们一般都是对主数据库进行写入的操作，对从数据库进行信息的读取。

基本过程

  1. Mysql会在主数据库发生变化的时候，将变化实时同步到从数据库上。
  2. 主从复制可以水平扩展数据库的负载能力，容错，高并发，数据备份。
  3. 不管是delete update insert 还是创建函数，存储过程都是在 master上进行，Slave会快速接受到这些操作，实现同步

用途和条件

1)、mysql主从复制用途
●实时灾备，用于故障切换
●读写分离，提供查询服务
●备份，避免影响业务
2)、主从部署必要条件：
●主库开启binlog日志（设置log-bin参数）
●主从server-id不同
●从库服务器能连通主库

粒度 原理与形式

实现的三种形式：
binlog 的记录模式：

  1. statement：
    ** 把对数据库的sql语句写到 binlog中 每一条修改数据的sql语句都会记录在 binlog日志中**记录的是sql语句不需要记录每一行的变化，只需要记录下来sql语句即可。

缺点： 就是 由于只是记录下来sql语句 为了能够让sql语句正常的在 Slave上运行，还必须记录每条语句在执行的过程中一些相关的新。以确保得到与 主数据库相同的结果

  1. row：

    把每一条数据的变化写入到binlog中，不记录sql语句的上下文信息，仅保存哪条记录被修改。 就是会清楚记录下来每一行的变化，但是这样回产生大量的日志，例如一个更改表的语句，就会涉及到很多条的语句都会进行修改，就会导致比较冗余

  2. mixed statement与row的混合。

    Mysql决定何时写statement格式的binlog, 何时写row格式的binlog。 将两则的混合起来，起到最少的记录行信息

实现的流程

首先看一张图片：
需要三个线程来完成的，在从端有两个线程，sql线程与 I/O线程。
主端有 一个 I/O线程。在实现主从复制的时候 ，首先会开启 Master端的 binLog记录功能 因为整个的复制流程就是 Slave从Master端获取到 binlog日志，然后再Slave上以相同的顺序执行获取到的binlog日志中的记录中的各种的SQL操作。
image-20200721153014075

  1. 在从端打开主从复制的开关，开始进行复制操作。
  2. 此时 对于 从的 I/O线程会通过 master上已经授权的赋值用户权限请求建立连接master服务。并请求从执行binlog日志的指定位置之后开始发送binlog日志的内从（注意这里的日志文件名和位置就是在配置主从服务质量执行 change master命令指定的）
  3. Mater 服务器接收到来自 Salve服务器的IO请求以后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后 返回给Slave端的IO线程，返回的除了基础的binlog日志内容以外，还有Master服务端记录的IO线程。返回的信息还有binlog中下一个指定更新的位置。
  4. 当slave 服务器的IO线程读取到 Master服务器上 IO线程发送过来的日志内容，日志文件，及位置以后，会将binlog日志内容依次写到Slave端自身的Relay Log （即中继日志）文件（Mysq-relay-bin.xxx）的最末端。并将新的binlog文件名和位置记录到 master-info文件中，以便能够在下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。
  5. Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

需要的条件

  1. 开启 binlog功能
  2. 主库要建立账号
  3. 从库要配置master.info (相当于配置密码文件的相关信息)
  4. start slave 开启复制的功能

小结：

主从复制是异步的逻辑的SQL语句级的复制
复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程
实现主从复制的必要条件是主库要开启记录binlog功能
作为复制的所有Mysql节点的server-id都不能相同
binlog文件只记录对数据库有更改的SQL语句（来自主库内容的变更），不记录任何查询（select，show）语句

从库同步延迟问题：

1）.
MySQL数据库主从同步延迟原理mysql主从同步原理：主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。
2) .
MySQL数据库主从同步延迟是怎么产生的？当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

什么是异步复制，全同步复制，与半同步复制

在面试过程中切实遇到。

异步复制：

逻辑上：
对于Mysql来说默认上就是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
技术上：
主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，而此时不会保证这些 Binlog 传到任何一个从库节点上。

同步复制：

逻辑上：
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
技术上：
当主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作。但缺点是，主库完成一个事务的时间会被拉长，性能降低。

半同步复制

逻辑上：
前面提到的 异步复制，是不查看从库是否已经完成了对于主库的赋值，全同步复制是，接收完所有的从库复制信息以后才会返回给客户端。但是对于 半同步复制来说，主库在执行完客户端提交的事务之后并不会立刻返回给客户端，而是等待至少一个从库接收到并写到Relay
olg中才返回给客户端，相当于一个简约版的异步复制，提高了一定的安全性。但是也造成了一定程度的延迟情况，这个延迟最少是一个TCP/IP 往返时间，所以来说 版同步复制最好在低延迟的网络中使用到。

SQL语句相关

SQL语句执行时各个关键字的顺序

  1. 对于order by来说 表示按照什么类进行分类的处理 例如对于学生的成绩而言，就可以直接查询然后利用到order by 进行一个升序的排列。
  2. 对于 group by 来说就是按照什么进行分类，常常和一些聚合的函数在一起来使用 count，avg max min 这里举一个列子如下图所示
    image-20200721152116411
select cno,count(sno) from stu group by cno;
  • 1

表示就是说 以cno进行分类处理 对于 cno来说有几个分类就列举出来几行 同时计算出 以cno为相同时候的count sno的值。

image-20200721214332945

  1. 就是表的相连接以及 聚合函数的使用 对于两个表时候，我们使用到直接逗号的方法进行连接即可 但是对于相同的字段要保持前缀的存在。
    举个栗子：

    查询选修了3门以上课程，且所有课程成绩都高于60分的学生学号及课程数

select sno,count(cno)
from stu
where grade>60
group by sno having count(cno)>3;
  • 1
  • 2
  • 3
  • 4

sql语句的相关联的连接查询

  • 交叉连接（CROSS JOIN）
  • 内连接（INNER JOIN）
  • 外连接（LEFT JOIN/RIGHT JOIN）
  • 联合查询（UNION与UNION ALL）
  • 全连接（FULL JOIN）

举个栗子

img

内连接

  • 等值连接：ON A.id=B.id

  • 不等值连接ON A.id > B.id

  • 自连接 就是说对于同一个表自己和自己相连接

select e.emp,d.emp from employ e 
inner join employ b where e.bossid==b.id;
  • 1
  • 2

外连接

  • 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
  • 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

表连接面试题

有两个表，一个是X表，其中有三个字段ABC，一个是Y表有两个字段CD,两张表之间只有c字段是共同的字段，来进行不同的操作，验证我们的各个连接的情况。如下图所示：

X表：

ABC
a1 b1 c1
a2 b2 c2
a3 b3 c3

Y表

CD
c1 d1
c2 d2
c4 d3

  1. 交叉连接（笛卡尔积）

    select x.*,y.* from r,s;// 最后应该是X表的行乘于Y表的行
    • 1
    ABCCD
    a1 b1 c1 c1 d1
    a2 a3        
    a1 b1 c1 c2 d2
    a2 a3        
    a1 b1 c1 c4 d3
    a2 a3        

  2. 内连接

    select x.*,y.* from x inner join y on x.c=y.c;// 要求要完完全全相等
    • 1
    ABCCD
    a1 b1 c1 c1 d1
    a2 b2 c2 c2 d2

  3. 左连接

select x.*,y.* from x left join y on x.c=y.c;// 对于左连接出现的情况是先将左边的表进行全查询处理，与右边表有相对应位置时候显示对应的值，若是没有相等的值，该列的值为空。
  • 1
ABCCD
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2
a3 b3 c3    

  1. 右连接

    select r.*,s.* from r right join s on r.c=s.c
    • 1
ABCCD
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2
      c4 d3
  1. 全连接(暂时情况是对于MySQL来说，不支持全连接的情况，全连接只能由Oracle支持)
select r.*,s.* from r full join s on r.c=s.c
  • 1
ABCCD
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2
a3 b3 c3    
      c4 d3

子查询

  1. 条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果
  2. 嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

Where 类型的子查询

  1. 子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符

    -- 查询工资最高的员工是谁？ 
select  * from employee where salary=(select max(salary) from employee);
    • 1
    • 2

  2. 子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符

    select * from employee where salary in(select salary from employee group by card_id)
    • 1
    • 2

  3. 子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表

    -- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

from 类型的子查询

在学习 from 子查询之前，需要理解一个概念：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

  1. 取出每个栏目下 goods_id 最大的商品信息：

首先执行下面这句语句以后，能得到一个结果，其中每个栏目的第一行都是 goods_id 最大的行：

select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc;
  • 1

假设存在这张表，表名叫 tmp ,那么：

select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;
  • 1

exists类子查询

exists 型子查询是指外层 sql 的结果，拿到内层 sql 去测试，如果内层 sql 成立，则该行取出。学习之前先建一张表：

create table category(
    -> cat_id int auto_increment primary key,
    -> cat_name varchar(20) not null default ''
    -> )engine myisam;
  • 1
  • 2
  • 3
  • 4

向里面插入原来 ecshop.ecs_category 中的部分数据：

insert into test.category select cat_id,cat_name from ecshop.ecs_category;
  • 1
  1. 取栏目表且只取出下面有商品的栏目表

那么假设某个栏目的 cat_id 为 N，则 select * from goods where cat_id = N如果能取出，则说明该栏目下有商品：

select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
  • 1

可以理解为，先把第一个 cat_id=1 取出来，带入到内层，那么内层就变成了：

select * from goods where goods.cat_id=1;
  • 1

查询发现没有对应的值，返回 empty ，那么category 中 cat_id=1 这一行就不要了。接下来看 cat_id=2，代入，发现可以取出货物 goods 来，那么 category 中 cat_id=2 这一行就取出来

in 和exist 区别

在前面讲到和学习到in 和exists都是在子查询时候来使用达到更高的效率，那两者之间的区别是什么呢？

  1. 如果查询的两个表大小相当，那么用in和exists差别不大。
  2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。(理解起来就是说想象一下对于 in来说，表示先将子表中的数据查询出来以后，然后在父表中进行筛选，所以说对于子表比较小的时候使用到in 能够提交效率。对于exists来说 表示存在于，对于父表较小的时候，子表中的查询也就小一些)

SQL 语句的优化

关于大表的优化处理 某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

首先就是说 对于数据比较多的情况下 crud 会导致比较低情况，这个时候就可以

  1. 限制数据的查询的范围 就是说 我们不允许出现不带任何限制条件的查询语句。 我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
  2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
  3. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

对于垂直分区

就是说 将一个表差分为两个表，同时拥有主键。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
在这里插入图片描述
优点就是说： 可以使得行数据变小，在查询的时候减少io的次数此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
在这里插入图片描述

水平分表

表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

可以结合explain 执行计划来具体查看

  1. 尽量避免使用非操作符号。在索引上使用 not，<>等非操作符，数据库管理系统时不会对此使用索引的，可以将查询语句转换成为可以使用索引的查询。
  2. 避免对查询的列的操作。任何对列的操作都可能导致全表的扫描，这个所谓的操作包括数据库函数，计算表达式等，查询时候要尽可能把操作移到等式的右边，甚至去掉函数。
  3. 避免不必要的类型转换。需要注意的是尽量避免潜在的数据类型转换。如将字符型数据域数值型数据比较，会自动将字符进行转换，从而导致全表的扫描。
  4. 适当增加查询的范围限制。
  5. 合理使用in与exists。例如有两个表有A与B分别有下面的情况：
  • 当我们想要显示表A中的数据，关系条件只有一个ID，对ID进行检
    索。
SELECT * FORM A WHERE ID IN(SELECT ID FROM B);
  • 1
  • 当我们在对表A对数据进行显示，但是条件不止有ID还会有其他的color时候，使用 esists
SELECT * FROM A WHERE ESISTS(SELECT 1 FROM B WHERE ID =A.ID AND COLOR= A.COLOR);
  • 1
  1. 去掉where 语句中的 is null 或者是is not null 将不会使用 索引而是使用全局搜索，就会降低查询的效率，在查询时候 使用分情况讨论会更好一点将其改为 > 0 或者是 >’ '。
  2. 避免使用前导模糊查询，使用模糊查询时候 是不能够使用 索引的。
  3. 在查询的过程中避免使用 * ，当我们在查询的过程中要是想要列出所用的column 时候，使用动态Sql列引用* 是一个方便的方法，但是不幸的是，这是一个非常低效的方法，在实际的过程中，* 会转换成为所有的列名，这个工作是通过查询数据字典完成的，就意味着消耗更过的时间。
  4. 规范使用 要么全部大写 要么全部小写，不要混用。
  5. 为经常出现在where字句中的列创建索引；为经常出现在 order by ，disinct后面的字段建立索引。如果有时候会建立的复合索引，此时索引的字段顺序要和这些关键字后面的字符段顺序一致；为经常作为表的连接条件的列上创建索引。
  6. 减少表与表之间的关联，尽量不使用全局查询，把数据量大的表排在前面。

其他

视图

什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

有哪些的使用场景

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

  1. 重用SQL语句；

  2. 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；

  3. 使用表的组成部分而不是整个表；

  4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；

  5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

  6. 限制数据访问。视图在本质上其实就是一条select语句，所以当在 访问视图的时候，只能访问到Select语句所对应的列，对基表中的其他列起到了保护的作用。

视图的优缺点

优点
  1. 查询简单化。视图能简化用户的操作
  2. 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
  3. 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性
缺点

  1. 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

  2. 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

什么是触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

  1. 可以通过数据库中的相关表实现级联更改。
  2. 实时监控某张表中的某个字段的更改而需要做出相应的处理。
  3. 例如可以生成某些业务的编号。
  4. 注意不要滥用，否则会造成数据库及应用程序的维护困难。
触发器的类型

在MySQL数据库中有如下六种触发器：

  • Before Insert
  • After Insert
  • Before Update
  • After Update
  • Before Delete
  • After Delete

基本类型长度问题

varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

mysql为什么这么设计

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

mysql中int(10)和char(10)以及varchar(10)的区别

int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

int(10) 10位的数据长度 9999999999，占32个字节，int型4位
char(10) 10位固定字符串，不足补空格 最多10个字符
varchar(10) 10位可变字符串，不足补空格 最多10个字符

char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间

varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

基础的MySQL练手

超过5名学生的课

有如下表：
在这里插入图片描述
最后输出：
在这里插入图片描述
SQL语句：

select class from courses group by class  
 having count( distinct student) >= 5;
  • 1
  • 2

部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
在这里插入图片描述
Department 表包含公司所有部门的信息。
在这里插入图片描述
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
在这里插入图片描述

SELECT
	Department.NAME AS Department,
	Employee.NAME AS Employee,
	Salary 
FROM
	Employee,
	Department 
WHERE
	Employee.DepartmentId = Department.Id 
	AND ( Employee.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

分数的排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
在这里插入图片描述
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
在这里插入图片描述

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as `Rank`
from Scores a
order by a.Score DESC
posted on 2020-08-07 16:35  不灬赖  阅读(432)  评论(0)  编辑  收藏  举报