MySQL面试(1)

MySQL(1)

---

1.$跟#的区别

#相当于对数据 加上 双引号，$相当于直接显示数据。

2.MySQL的事务隔离级别有哪些？默认隔离级别是？

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

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

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

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

脏读：一个事务读取另外一个事务还没有提交的数据。

不可重复读：事务 T1 读到某行；事务 T2 修改或删除这行，提交事务；T1 重新读取发现这行数据已经被修改或删除。

幻读：事务 T1 读取了 N 行；事务 T2 在事务 T1 读取的条件范围内生成了一行或多行数据；T1 重新读取获得与之前不同集合的行数据。

3.explain语句结果各个字段分别表示什么

Column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
Extra	额外信息

• id: SQL查询中的序列号。

• select_type: 查询的类型，可以是下表的任何一种类型：

  select_type	类型说明
  SIMPLE	简单SELECT(不使用UNION或子查询)
  PRIMARY	最外层的SELECT
  UNION	UNION中第二个或之后的SELECT语句
  DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
  UNION RESULT	UNION的结果
  SUBQUERY	子查询中的第一个SELECT
  DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
  DERIVED	衍生表(FROM子句中的子查询)
  MATERIALIZED	物化子查询
  UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
  UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

• table 表名或者表的别名。

• partitions 分区信息，非分区表为null。

• type 访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：

  • NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
  • system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
  • eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
  • ref：多表查询时，根据非唯一非空索引进行查询的情况。
  • range：在一个索引上进行范围查找。
  • index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
  • ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。

• possible_keys 表示mysql此次查询中可能使用的索引。

• key 表示mysql实际在此次查询中使用的索引。

• key_len 表示mysql使用的索引的长度。该值越小越好。

• ref如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

• rows 也是一个重要的字段。 这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

• extra（重要）

  explain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

  • distinct：在select部分使用了distinc关键字
  • Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  • Using index
    “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary
    查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

4.什么是覆盖索引？

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

5.最左前缀原则是什么

当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照a,b.c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

6.lnnodb是如何实现事务的

Innodb通过Buffer Pool，LogBuffer，Redo Log, Undo Log来实现事务，以一个update语句为例:

• Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中
• 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
• 针对update语句生成一个RedoLog对象，并存入LogBuffer中
• 针对update语句生成undolog日志，用于事务回滚
• 如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中6．如果事务回滚，则利用undolog日志进行回滚

7.B树和B+树的区别，为什么Mysql使用B+树

B树的特点:

1．节点排序
2.一个节点了可以存多个元素，多个元素也排序了

B+树的特点:

1．拥有B树的特点
2．叶子节点之间有指针
3．非叶子节点上的元素在叶子节点上都冗余了，也就是叶子节点中存储了所有的元素，并且排好顺序

Mysql索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得B+树的高度不会太高，在Mysql中一个Innodb页就是一个B+树节点，一个Innodb页默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句。

为什么索引采用B+树

1.索引用来加快查询速度，B+树对数据排序可以加快查询速度

2.一个节点存储多个元素，B+树不会很高，一个innodb页是一个节点，两层B+树可以存储200万条数据

3.B+树叶子节点有指针，可以支持全表扫描，范围查找等SQL语句

8.Mysql锁有哪些，如何理解

按锁粒度分类:

• 行锁:锁某行数据，锁粒度最小，并发度高
• 表锁:锁整张表，锁粒度最大，并发度低
• 间隙锁:锁的是一个区间

还可以分为:

• 共享锁:也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写-
• 排它锁:也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写

还可以分为:

• 乐观锁:并不会真正的去锁某行记录，而是通过一个版本号来实现的
• 悲观锁:上面所的行锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利用锁来解决幻读

9.Mysql慢查询该如何优化?

• 检查是否走了索引，如果没有则优化SQL利用索引
• 检查所利用的索引，是否是最优索引
• 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
• 检查表中数据是否过多，是否应该进行分库分表了
• 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

10.mysql有哪些引擎

所谓的存储引擎是指用于存储、处理和保护数据的核心服务。也就是存储引擎是数据库的底层软件组织。在 MySQL 中可以使用“show engines”来查询数据库的所有存储引擎

InnoDB 存储引擎

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

特点

(1) 支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空，且值必须唯一。MySQL中规定自增列必须为主键。
(2) 支持外键，保证数据的完整性和正确性。外键所在表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。
(3) DML(数据库操作)操作遵循ACID模型，支持事务。
(4) 行级锁 ，提高并发访问性能。

MyISAM 存储引擎

MyISAM是MySQL早期的默认存储引擎。

特点

(1) 不支持事务，不支持外键
(2) 支持表锁，不支持行锁
(3) 占用空间小，访问速度快

Memory 存储引擎

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

(1) 内存存放
(2) hash索引（默认）

11.索引的作用

创建索引可以大大提高系统的性能。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

12.索引的类型

存储方式区分

1. B-树索引

目前大多数索引都是采用B-树来存储，其包含组件有：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

2. 哈希索引

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH 索引只支持等值比较，如”=” “IN()”或”<=>”。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分

1. 普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。允许重复值和空值。

关键字是 INDEX 或 KEY。

2. 唯一索引

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

关键字是 UNIQUE。

3. 主键索引

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

关键字是 PRIMARY KEY。

4. 空间索引

空间索引是对空间数据类型的字段建立的索引，不允许空值，只能在存储引擎为 MyISAM 的表中创建。

关键字是 SPATIAL。

5. 全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。只有 MyISAM 存储引擎支持，允许重复值和空值。

关键字是 FULLTEXT。

实际使用区分

1. 单列索引

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

2. 组合索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

查询时，字段顺序需与索引顺序一致；LIKE时，首字符不能是 ‘%’，否则会影响索引使用。

13.Mysql事务是什么，介绍一下

事务：

一个最小的不可再分的单元；可以理解为一个事务对应的是一组完整的业务，并且在这个事务中所作的一切操作要么全部成功，要么全部失败，只要有一个操作没成功，整个事务都将回滚到事务开始前。

事务的四大特征：

原子性：每一个事务都是一个不可再分的工作单位，事务中包括的操作要么都做，要么 都不做。

一致性：对于数据的操作从一个一致的状态转变成另一个一致转态。

隔离性：指一个事务的执行不能被其他事务干扰，即一个事务内部的操作对并发的其他事务是具有隔离的，并发执行的各个事务之间不能互相干扰。

持久性：一个事务一旦提交，他对数据库中的数据的改变就应该是永久性的。提交后的其他操作或故障不会对其具有任何影响。

14.Mysql主从了解吗，说说过程和原理?

MySQL 主从复制概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

为什么需要主从复制？

• 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
• 做数据的热备，当主数据库有问题，可以切换从数据库
• 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL主从复制原理

从上图可以看出，MySQL主从同步实现主要有以下三个过程：
1、当有数据更改语句执行时，MySQL主库要在更新数据的同时，写二进制日志，将数据修改的内容记录进入日志中。
2、MySQL从库上运行这一些I/O进程，这个进程会监视MySQL主库上的二进制日志，当发现修改时，会立即同步到自身的中继日志。
3、MySQL从库上还会运行一个SQL进程，该进程用于监视自身的中继日志，当发现自身的中继日志发生改变时，立即将该中继日志改变对应的数据更改操作写入自身的数据库。

MySQL主从复制模式

异步复制

在异步复制（async replication）中，Master不用关心Slave是否接收到二进制日志，所以Master与Slave没有任何的依赖关系。你可以认为Master和Slave是分别独自工作的两台服务器，数据最终会通过二进制日志达到一致。

异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread需要读取大量二进制日志文件。

如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统。

半同步复制

主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟。
半同步复制的出现，就是为了保证在任何时刻主备数据一致的问题。相对于异步复制，半同步复制要求执行的每一个事务，都要求至少有一个备库成功接收后，才返回给用户。实现原理也很简单，主库本地执行完毕后，等待备库的响应消息（包含最新备库接收到的binlog（file，pos）），接收到备库响应消息后，再返回给用户，这样一个事务才算真正完成。在主库实例上，有一个专门的线程（ack_receiver）接收备库的响应消息，并以通知机制告知主库备库已经接收的日志，可以继续执行。

增强半同步复制

解决半同步复制中途数据不一致问题

半同步的问题是因为等待ACK的点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据，当Binlog还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。

增强半同步将等待ACK的点放在提交Commit之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

全同步复制

当主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

15.char和varchar区别

1、最大长度：

char最大长度是255字符，varchar最大长度是65535个字节。

2、定长：

char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。

3、空间使用：

char会浪费空间，varchar会更加节省空间。

4、查找效率：

char查找效率会很高，varchar查找效率会更低。

5、尾部空格：

char插入时可省略，vaechar插入时不会省略，查找时省略。

ps(int 长度是11位)