Mysql

1.索引的种类?区别?特点?优点?缺点?

索引的本质是数据结构。你可以简单理解为“排好序的快速查找数据结构”。

常见的有两类：

(1)哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是O(1)；

(2)树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(log2N)；

注：InnoDB不支持哈希索引。虽然哈希索引是 O(1)，树索引是 O(log(n))，但 SQL 有很多“有序”需求，故数据库使用树型索引

普通二叉树

概念：

从根节点出发,每个节点最多拥有两个子节点
没有子节点的节点叫叶子节点
特点:左节点比根节点小,右节点比根几点大

优点:

有序
查找某个值每次都能淘汰掉50%

场景：

适合于范围查找
适合排序

缺点:

当数据过多的时候会变的很高,树会退化,搜索的效率越来越低

平衡二叉树

特点：

自平衡,自动重新选择根节点

优点:

拥有二叉树的全部优点
解决了二叉树的蜕化的问题.降低了树的高度,提升了查询的效率

缺点:

当数据量过大的时候,树的高度依然会很高

平衡树

每一个节点里面都存数据,指针,索引
特点：有序、自平衡、根节点下面的子节点有多少节点不受限制

B树

又称 平衡的多路搜索树 ，每一个节点

既存储数据
又存储指针
还存储索引

优点：

拥有（平衡）树的全部优点
解除了节点和子节点的数量的限制，
从而解决了数据量过大，树过高的问题
自带平衡

缺点：

每个节点既保存数据又保存索引and指针，导致每个节点存储的内容是有限的。
从也导致，每次查询数据库的时候，可能存在多次的磁盘IO

B+树

B树的衍生树。MySQL的InnoDB存储引擎的默认索引实现为：B+树索引

特点：

除了叶子结点之外的所有节点都只存储索引+ 指针
所有的数据都存储在叶子节点。

优点：

每个节点都可以存储更多的索引+ 指针
可以一次性将索引和指针读到内存中去
内存就可以把非叶子结点全部、一次读取到内存中（B+树更适合外部存储）

B树和B+树的区别

2.MySQL的逻辑架构几层?分别是做什么?

Mysql 主要分为Server层和引擎层，Server层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用。

controller(连接层):负责用户登录认证、鉴权、普通用户会话
service(服务层):管理SQL缓存(MySQL8取消)、检验SQL语法、解析SQL、生成执行计划(Profile)等....
mybatis(引擎层):管理mysql的存储引擎
mysql(存储层/持久层):负责管理硬盘上的存储数据。

引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory等。

3.SQL语句的解析顺序是怎样的?

查询语句的执行流程如下：

权限校验（如果命中缓存）---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎

更新语句执行流程如下：

分析器----》权限校验----》执行器---》引擎---redo log(prepare 状态---》binlog---》redo log(commit状态)

4.explain关键字常关注的列有哪些?

id: 选择标识符
select_type: 表示查询的类型
table: 输出结果集的表
partitions: 匹配的分区
type: 表示表的连接类型
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数(估算的行数)
filtered: 按表条件过滤的行百分比
Extra: 执行情况的描述和说明

5.mysql索引的种类?区别?特点?

普通/单列索引：即一个索引是只根据一个字段创建的，里面只包含单个列，一个表可以有多个单值索引（也叫单列索引）

CREATE INDEX idx_name ON table_name(filed_name)

联合索引/多值索引：即一个索引包含多个列（也叫多值索引、多列索引）

CREATE INDEX idx_name ON table_name(filed_name_1,filed_name_2);

唯一索引：索引列的值必须唯一，但允许有空值，空值可以有多个

CREATE UNIQUE INDEX idx_name ON table_name(filed_name_1,filed_name_2)；

主键索引：设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引 - ALTER TABLE table_name ADD PRIMARY KEY ( filed_name )
覆盖索引：索引包含所需要的值，不需要“回表”查询，比如查询两个字段，刚好是组合索引的两个字段
全文索引：对内容进行分词搜索，仅可用于Myisam，更多用ElasticSearch做搜索

ALTER TABLE table_name ADD FULLTEXT ( filed_name )

聚簇索引：可以一次直接找到数据

非聚簇索引：聚簇索引之上创建的索引称之为非聚簇索引；非聚簇索引访问数据总是需要二次查找。

回表：从非聚簇索引到聚簇索引的过程。

详解：在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 ID，所以当我们使用非聚簇索引进行查询时，首先会得到一个主键 ID，然后再使用主键 ID 去聚簇索引上找到真正的行数据，我们把这个过程称之为回表查询。

6.索引失效情况?

非最左匹配
错误模糊查询
- 模糊匹配后面任意字符：like '张%'(生效)
- 模糊匹配前面任意字符：like '%张'
- 模糊匹配前后任意字符：like '%张%'
列运算
- select * from student where id+1=2(失效)
使用函数
- 使用mysql任意的函数就会失效。
- 例如: select * from student where ifnull(id,0)=1 (失效)
类型转换
- 如果索引列在表中是字符型，搜索where条件后是整型，则存在类型转换
使用 is not null
- 当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的

最左原则?

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。
=和in可以被mysql优化调整到前面的位置(被命中)，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

什么叫索引的部分命中和完全命中?

全部命中:索引的列全部匹配从最左开始就是全部命中

部分命中:索引的列从最左开始只按顺序匹配了一两个，然后就从中间断了

7.存储引擎种类有哪些?

MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV。

innoDb

InnoDB的缓冲池缓存什么？有什么用？

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

大于等于5.5之后，默认采用InnoDB引擎。在mysql里面是默认引擎,拥有事务.行锁,支持高并发.可以缓存索引和真实数据.没有特别原因优先考虑innoDb引擎

MyISAM

5.5之前默认的存储引擎

有大量特性:全文索引、压缩、空间函数(GIS)等,不支持事务和行级锁本身是表锁

有个缺陷是崩溃后无法安全恢复

Archive

• Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

• Archive表适合日志和数据采集（档案）类应用

CSV

csv引擎可以将数据保存到csv里面非常做数据迁移

Memory

访问数据快,数据不会被修改,重启后丢失也没关系

innodb和MyIsam的区别

事务和外键
- InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作。
- MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
锁机制
- InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。[innodb默认是行锁，前提条件是建立在索引之上的。如果筛选条件没有建立索引，会降级到表锁。即如果where条件中的字段都加了索引，则加的是行锁；否则加的是表锁。]
- MyISAM支持表级锁，锁定整张表。
索引结构
- InnoDB使用聚簇索引，索引和记录在一起存储，既缓存索引，也缓存记录。可以一次直接找到数据
- MyISAM使用非聚簇索引，索引和记录分开。建立在聚簇索引之上，必须两次以上完成查找。
并发处理能力
- InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。
- MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
存储文件
- InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；
- MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

适用场景：

InnoDB

需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

MyISAM

不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少，以读为主
数据一致性要求不高

8.mysql数据库的隔离级别有哪些?默认是什么?特点是什么?

读未提交

读未提交: AB两个事务操作数据的时候,A事务可以读取到B事务未提交的操作
--->问题: 脏读不可重复读幻读

读已提交（oracle的默认级别）

读已提交: AB两个事务操作数据的时候,A事务只能读取到B提交的数据
避免脏读， --->问题: 不可重复读幻读

可重复读（mysql的默认级别）

可重复读(默认隔离级别): 同一个事务内多次读取同一份数据的内容是一样
避免了脏读，不可重复读， --->问题:很小概率幻读

串行化

避免所有问题！
读的时候加共享锁,其他事务可以并发读,但是不能写。
写的时候加排它锁,其他事务不能并发写，也不能并发读。
但因为执行效率低，所以真正使用的场景并不多。

9.脏、幻、不可重复读是什么意思?

脏读: A事务操作数据未提交,B事务读取到A事务操作的内容,A事务提交失败!

幻读/虚读: 同一个事务内,同一个查询多次查询到的结果不一样(新增)

不可重复读: 同一个事务内,对同一份数据多次查询到的结果不一样(修改)

怎么解决?

更改事务的隔离级别。

10.SQL怎么优化？

尽量选择较小的列

select子句中避免使用‘*’

当只需要一行数据的时候使用limit 1

（加上了limit 1,只要找到了一条对应的记录，就不会继续向下扫描了。效率高。 不加limit 1 是全表扫描。）

索引

将where中用的比较频繁的字段建立索引。

避免在索引列上使用计算、not in 和<>等操作。

避免改变索引列的类型。

子查询

尽量缩小子查询的结果

数据量多

保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain 来分析该语句具体的执行情况。

表顺序

在from子句中包含多个表的情况下，我们选择记录条数最少的表作为基础表。from字句中写在最后的表是基础表，这张表将被最先处理。这也是我们选择最有效的表名顺序的方案。

.乐观锁和悲观锁?

悲观锁（Pessimistic Concurrency Control，PCC）：比较悲观，假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。至于怎么加锁，加锁的范围也没讲。

乐观锁（Optimistic Concurrency Control，OCC）：比较乐观，假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。也没具体指定怎么检查。

毕竟乐观锁和悲观锁也不仅能用在数据库中，也能用在线程中。

悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好。

乐观锁不能解决脏读，加锁的时间要比悲观锁短（只是在执行sql时加了基本的锁保证隔离性级别），乐观锁可以用较大的锁粒度获得较好的并发访问性能。

但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象，那么当他完成了自己的更改进行提交时，数据库就会发现该对象已经变化了，这样，第二个用户不得不重新读取该对象并作出更改。

可见，乐观锁更适合解决冲突概率极小的情况；而悲观锁则适合解决并发竞争激烈的情况，尽量用行锁，缩小加锁粒度，以提高并发处理能力，即便加行锁的时间比加表锁的要长。

.默认主从集群数据同步的流程(异步复制)?

事务提交到master
master接收到应用事务提交请求后，更新内部的binlog日志，让mysql引擎执行事务操作，并返回给客户端执行结果信息。同时在master中会存在一个事件监听，其会一直监听着master中binlog日志文件的改变，一旦发现日志文件发生改变，触发dump线程
dump线程被触发后，通知slave中的IO线程现在有事务操作要进行同步
slave中IO线程接收到通知后，会从slave中relay-log.info文件中获取slave中的binlog日志文件和pos位置信息。接着会把这部分信息发送给master的dump线程
master的dump线程收到这些信息后，会根据slave发送的binlog日志文件和pos位置，将最新的binlog日志和pos位置后面的内容同步给slave的IO线程
slave的IO线程接收到这些信息后，会将这部分内容同步到slave中的relay-bin文件中
当relay-bin文件发生改变后，触发slave线程执行sql操作【异步】
当slave向relay-bin写入完成后，会向master返回一个ACK消息，同步成功。

对于这一系列的操作，可以发现master和slave在进行同步时是以异步的方式完成的，master写入完binlog后，会马上通过引擎进行事务提交并向客户端返回响应，对于与slave同步的操作，则是异步完成的。

优点:

效率高

缺点:

可能出现数据不一致(弱一致)

和半同步复制的区别?

主体流程相似或者说数据的同步的过程一模一样
半同步复制主节点完成数据存储,同时保证从节点也完成了数据同步以后,才返回给用户结果(强一致)
异步复制只需要主节点完成数据存储就返回给用户结果(弱一致)

什么叫主主级联模式?

主主级联架构:

所有从服务都丢给主服务1完成数据同步主服务1不对外提供操作

只有主服务2挂掉了主服务1才会顶上去

单点问题:靠主主级联架构解决

通过这种架构不仅可以解决master单点的问题，也可以解决slave延迟的问题。

级联复制：

12.什么叫读写分离?

主从服务:

主服务作为写服务
从服务作为读服务

问题:从服务越多,越会影响主服务的性能,因为主服务需要分配大量的资源去完成数据同步

主从级联架构:

主服务下面的从服务还有很多从服务

这样能减少主从服务的从服务数量,让主服务尽量的保证最大的性能给用户做写操作,少量性能作为数据同步

问题:主服务单点主服务蹦了就只能做读操作了

.mysql的分片策略有哪些?区别?特点?

取模分片:

当一个数据表中的数据量非常大时，就需要考虑对表内数据进行分片，拆分的规则有很多种，比较简单的一种就是，通过对id进行取模，完成数据分片

问题分析

- 散列不均匀，出现数据倾斜

- 每张表中的数据量差距较大

- 动态扩容时，存在重新计算，出现数据丢失

- 动态扩容后新增表时，需要对模数修改时有可能就会造成当查询某个分片时，在该分片中找不到对应数据

全局id分片:

当进行数据切分后，数据会存放在多张表中，如果仍然通过数据库自增id的方式，就会出现ID重复的问题，造成数据错乱。所以当拆分完数据后，需要让每一条数据都有自己的ID，并且在多表中不能出现重复。比较常见的会使用雪花算法来生成分布式id。

优点：

- 是基于本地文件，本地加载，读取速度较快

缺点：

- MyCAT重新发布后，配置文件中的sequence会初始化

- 生成的id没有意义

- MyCat如果存在多个，会出现id重复冲突

使用过什么分片的工具?

MyCat和ShardingSphere根据具体场景来做。

15.mysql的最小存储单元是什么?默认大小是多少?

mysql的最小存储单元是页，每页的默认大小为16kb（可以更改）2^14b
每个指针的大小是6kb，每个索引的大小是8kb。一对（索引+指针）=14b（计算方便视为 16b --> 2^4 b）
每一页可以存储 2^14b/2^4b = 2^10（2^14b/14b）个索引+指针。

3层高的B+树可以存储1kb的数据多少条?

假设这个 b+ 树是n阶的，总共有 n^2 个叶子节点
一个叶子节点，每个容量也是一页 = 16kb，每条数据是1kb，能存16条。（2^4条）
n^2个节点可以存储 n^2*2^4条数据。 ---- >1024个节点，就能存 2^24条数据
真实的条数 > 2^24= 16777216 （这里实际索引指针大小是14b，意味着每页能存更多条数据，实际数据当然要大于这里的2^24条）实际工作中，这里的数据大小100-200b每条，应该是这列1024的5-10倍。

MySql-高频面试问题