八股文四（sql篇）

Mysql篇

索引分为哪几大类：

索引分为三大类：B+树索引、Hash索引、全文索引

MySQL有哪些索引 *

主键索引：一张表只能有一个主键索引，主键索引列不能有空值和重复值
唯一索引：唯一索引不能有相同值，但允许为空
普通索引：允许出现重复值
组合索引：对多个字段建立一个联合索引，需要遵循最左匹配原则
全文索引：myisam引擎支持，也就是倒排索，广泛用于搜索引擎

MyIsAm和InnoDB的区别 *

InnoDB有三大特性，分别是事务、外键、行级锁，这些都是MyIsAm不支持的；
MyIsAm的数据和索引是分开存储的，InnoDB的数据文件本身就是索引文件；
MyIsAm的访问速度一般InnoDB快，因为没有回表现象出现，还有行级锁。

聚簇索引和非聚簇索引的区别：

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了完整的行数据，非叶子节点存储记录到主键和页号。
优点：访问快，对主键的范围查询快；缺点：插入速度依赖插入顺序，否则有页分裂；

非聚簇索引：（辅助索引）将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置，不是完整的行数据;
也就是叶子节点存主键id，在根据主键id查聚簇索引找到行数据。

一个表中没有创建索引，那么还会创建B+树吗？

会的，如果有主键，会根据主键创建聚簇索引，如果没有主键，也会创建聚簇索引，也就是隐式索引（rowid）

二叉树、平衡二叉树、红黑树、B树、B+树的区别？

二叉树：分为左边和右边，容易形成单链表，因为不平衡；
不平衡二叉树：来保证高度差的绝对值不超过1，来保持平衡，每个节点都保存一个数据；缺点会导致树特别高，数据越来越多，自选时间越长；
红黑树：是一种自平衡的二叉查找树，因不平衡二叉树在新增修改，为了保持平衡频繁调整，所以有了红黑树；在jdk8的hashmap存储用到；
B树：每个页都有该行的数据，数据存在该页中；
B+树：数据是存在叶子节点上的，每页只存主键key，或者其他索引的值；

一个b+树中大概存放多少条索引记录？

默认的页可以存16k（已经很大了），3层大概有16*1600*1600 =4096,0000

什么是自适应的哈希索引

快速访问数据库中数据的索引结构，这个是InnoDB引擎的功能，当某个索引使用频繁，底层会自动创建一个哈希索引，我们都知道hash查询速度快，时间复杂度o1。

为什么官方建议使用自增主键作为索引（字符串索引与自增索引区别）

1 自增主键，底层在某个区域的磁盘中，是顺序插入，顺序读都非常快，我们买机械磁盘是分顺序读写速度和随机读写速度是不一样的；
2 读取由B+树的二分查找定位，查找范围更快；像uuid这种字符串就是，在磁盘中随机写，也随机读。

使用int自增主键最大id是10，删除9,10，那么再添加一条记录，最后的id是？

id是11，如果重启Mysql的话，是9，没有重启是11；如果重启，取目前表最大的id值；

索引的缺点：

索引过多，每创建索引都会创建B+树结构，索引过多，储存空间过多；如果新增修改频繁，也会有时间消耗，更新索引结构。

如果是大段文本内容，如何优化？

方式1：分表存储，就是拆出一张表只存内容；把一个大字段拆分2个字段；开头精确，后面模糊匹配；
方式2：使用ES作为创建索引；

非聚簇索引为什么不存数据地址而存主键？

因为当某个页存满，会创建新的页，之前的页有些数据删除掉，再重新写入，地址可能发生偏移。
在非聚簇索引只存主键，就不用担心该主键是否发移动；

什么是回表操作 *

就是先通过非聚簇索引查询得到一些主键值，再跟进这些主键值查询聚簇索引，得到具体的完整数据；

什么是覆盖索引 *

覆盖索引不会进行回表操作，就是想要查询到字段值在此索引中有；
例子：select name from 表 where name = "abc";

为什么要回表查询，直接存储数据不可以吗？

我们底层从B树变成B+树，改变的只有叶子节点存完成数据，这样的好处让树的高度更矮，存更多的数据，减少自循环的比例；
因为聚簇索引已存完成数据，如果非聚簇索引也存，浪费磁盘空间；如果其中一条数据改变，只改变聚簇索引里的值就可以，如果涉及非聚簇索引，改变其中影响的索引就好；避免更新数据所消耗时间更久；

如果我们表的主键id删除了，没有主键，那如何回表?

我们底层会有个隐式索引（rowid），如果没有主键，会根据rowid进行回表查询

什么是联合索引、组合索引、复合索引

多个字段创建的索引，按照当前列的顺序生成B+树索引，遵从最左原则；

什么是唯一索引，会影响性能嘛

唯一索引，必须唯一，但允许有空值
alter table 表 add primary key 表(字段);  -- 主键索引
CREATE unique index 索引名 on 表(字段);    -- 唯一性索引
CREATE index 索引名 on 表(字段);           -- 普通索引
CREATE index 索引名 on 表(字段1,字段2);     -- 复合索引
会影响性能，但影响到新增的速度可以忽略不计；如果没有，根据墨菲定律，必有脏数据产生。如果使用先查后查的方式，必须有事务，想避免脏数据产生，就需要锁表，并发数量就少。

什么时候适合创建索引，什么时候不适合创建索引 *

适合：
1.频繁操作where条件语句查询，数据量过多
2.关键字段、主键、外键
3.排序字段、统计字段
不适合：
1.频繁更改的字段
2.表数据量少
3.有使用函数的字段
4.索引数量尽量不超过5个

什么是索引下推

目的就是提升索引的利用率，就是多使用复合索引，减少回表的次数。

哪些情况会导致索引失效 *

1. 计算函数
2. like查询以%开始
3. 不等于，<> != 范围查询
4. is not null 和 is null 是否走索引，取决null数据占的比例
可使用explain 查询SQL执行计划，看是否走索引

为什么like查询以%开始索引会失效

并不一定会失效，如果name字段设置索引，select name from 表 where name like “%xxx”; 这种使用覆盖索引，不需要回表查询完整数据，这样索引会生效。

如何查看一个表的索引

1 show index from 表；
2 也可以使用explain 查询SQL执行计划查看

能否查看索引选择的逻辑

可以，需要开启这个权限，然后执行SQL语句，再使用optimizer_trace进行观察；
SET （session) OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
执行要观察的SQL语句...
SET （session) optimizer_trace="enabled=off";
就可以看到json格式的逻辑

多个索引优先级是如何匹配？

1.主键索引
2.单值索引
3.最左前缀匹配（组合索引）
4.范围匹配
5.索引扫描
6.全表扫描

什么是单路排序和双路排序 *

单路排序：一次性取出所有字段进行排序，内存不够采用磁盘
双路排序：取出排序字段进行排序，排序完再回表查询想要的数据；
单路排序更快，但消耗内存资源更多，双路排序慢，会产生回表；
当我们max_lengh_for_sort_data的单行所有字段限制，超过设置的数，启用双路排序；

如果表中有字段为null，又经常查询需要建索引吗？

如果经常被查询，需要建索引，null也是一种值，只是在索引树下有很多相同的值，也就是范围匹配 range

有字段为null，索引会失效吗

不一定，可以看SQL执行计划观察，看null的占比比例，最好我们给字段设置默认值

Mysql内部支持缓存查询吗？

5.7之前是支持的，但在8之后废弃了。
之前的缓存就有一个hashMap，key是查询的SQL语句，value是具体的值；如果第二次查询，根据同一个key可以很快查询出对应的value，查询快；
缺点就是命中的概率低，还有缓存需要大量的缓存区存储，浪费内存空间。
Mysql内部缓存不够灵活，然后我们在应用层有Redis和ehcached来代替。

Mysql内部有哪些核心模块组成 *

1. 第一部分是连接层，客户端进行连接Mysql，这里有连接池，负责连接；
2. 第二部分是解析和优化层，拿到该SQL语句（字符串），先判断有没有缓存，但在8版本之后取消缓存。然后拿SQL语句进行语法解析，判断要干嘛，该一步也进行语法验证；
然后语法正确后，进行优化操作，判断使用哪些索引；
3. 第三部就是存储引擎，判断是什么存储引擎，进行去磁盘中读取数据，进行返回给客户端。

一条SQL语句执行的过程

1. 首先客户端连接上Mysql，然后通过网络端口，把SQL语句发送给Mysql服务器；
2. 然后Mysql判断有没有权限
3. 有权限后，查询缓存，如果有缓存，直接返回数据，但在8版本之后取消缓存
4. 没有缓存直接进入解析和优化层，先判断SQL语句语法是否正确，（会生成树语句结构）正确之后，进行优化操作，判断使用哪些索引；
5. 然后再进入存储引擎，判断是什么存储引擎，进行去磁盘中读取数据，进行返回给客户端。

mysql支持哪些存储引擎

不同版本的存储引擎是不同的，默认是InnoDB，我们可以通过show engines;命令去查看

mysql有哪些存储引擎

InnoDB：   Mysql默认的存储引擎，支持事务、外键、行级锁
MyISAM：   不支持事务、外键、行级锁，优势：查询快，缺点就是一旦崩溃数据找不回来
Archive：  不常见，适合存压缩包
Blackhole：它适合记录日志，比如，一个SQL语句查询，它只记录该SQL语句，不记录SQL语句查询到结果
CSV：用于数据交换，如excel的读取
Federated：用于跨库关联查询，但我们也不用，跨库我们会使用中间件

能否为一张表设计单独的存储引擎

可以，
设置全表的存储引擎：SET DEFAULT_STORAGE_ENGINE=MyISAM; (或者通过配置文件设置)
设置单独表的存储引擎
CREATE TABLE 表名(
  建表语句;
) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称; 

mysql事务特性 *

原子性：一个事务内的操作统一成功或失败
一致性：事务前后的数据总量不变
隔离性：事务与事务之间相互不影响
持久性：事务一旦提交发生的改变不可逆

并发事务所产生的一些问题有哪些 *

脏读：脏读是指一个事务读取到其他事务未提交的数据
不可重复读：不可重复读是指在同一次事务中前后查询不一致的问题。
例子（一个A事务查询结果，另一个B事务修改了对应的值，提交事务B，事务A又查询结果，与上次查询结果不一致）
幻读：幻读跟不可重复读相似，2次查询，查询条数不一致。
例子（一个A事务查询结果，另一个B事务新增一条数据，提交事务B，事务A又查询结果，与上次查询结果不一致）

事务的隔离级别 *

在高并发情况下，并发事务会产生脏读、不可重复读、幻读问题，这时需要用隔离级别来控制。
读未提交： 允许一个事务读取另一个事务已提交的数据，可能出现不可重复读，幻读。
读已提交： 只允许事务读取另一个事务没有提交的数据可能出现不可重复读，幻读。
可重复读： 确保同一字段多次读取结果一致，可能出现欢幻读。
可串行化： 所有事务逐次执行，没有并发
InnoDB 默认隔离级别是 可重复读级别，分为快照度和当前读，通过间隙锁解决了幻读问题。

Mysql事务的隔离是如何实现的

Mysql默认隔离级别是 可重复读，然后InnoDB支持读写锁，有MVCC版本控制。

什么是一致性非锁定读和锁定读

锁定读：就是读写锁，多个事务同时更新数据，我们需要加锁，
	有行锁：解决对多个事务更新一行数据
	有间隙锁：解决对多个事务更新多行数据
	分别有：insert、update、delete、 select... for update;
非锁定读:就是MVCC解决，通过多版本控制，不需要加锁	

MVCC是什么*

MVCC是多版本并发控制，为每次事务生成一个新版本数据，每个事务都由自己的版本，解决每个事务进行读写不冲突。
MVCC主要依赖：
隐藏字段：
	有一个字段是标记删除字段，记录创建这条记录或者最后一次修改该记录的事务id，并不是真的物理上的操作；
	有一个字段是回滚指针；
	有一个字段是唯一的索引，就是行id，如果有主键索引会使用主键索引；
undolog日志：
    用来回滚数据的，还可以不加锁的读，就是快照读，用于读老版本的数据，就是一个事务还没提交，读之前版本的数据。
readView：
	不同的事务会对数据有变更，在不同的事务隔离级别，来解决哪些事务是可见和不可见的；每个事务的操作都会有版本号，它会标记好，访问对应的版本号数据。

Mysql有哪些日志 *

undolog日志：
	1.用于回滚，Mysql的原子性，一致性，通过该日志实现的
	2.用于MVCC读老版本的数据
redolog日志：
	1.Mysql的持久性，通过该日志实现的；如果我们电脑重启，可以通过该日志恢复数据，该日志是记录在磁盘中
	2.该日志记录操作之后的数据，用于MVCC读当前版本的数据
binlog日志：
	1.做主从复制的日志
	2.每次创建表、索引都会记录，增删改的日志，可以数据库的恢复
error日志：错误日志，一般Mysql启动错误写入该日志中
慢查询日志：一般慢SQL写入该日志中，在配置文件中配置long_query_time,超过该时间会记录该日志中；

表级锁和行级锁的区别：

行级锁：是锁行，该行上锁
表级锁：是锁表，隔离级别是串行化的时候是表级锁

什么是共享锁和排它锁

共享锁（读锁）（S锁），
排它锁（写锁）（独占锁），一个事务有排它锁，其他事务不能再有该锁，就是同一行。

InnoDB支持哪些锁

表锁、行锁、间隙锁

当前读和快照读的区别

当前读：就代表当前事务加了锁，读最新版本的数据；
快照读：不加锁的读，就是快照读，可重复读，读的是老版本的数据；

是否使用过select for update *

一般我们查询语句，不会上锁，但必须在事务中使用，select for update是在查询中上锁，避免超卖现象出现，
例子：一个事务中，先查询库存100个，然后再修改库存 -1操作，如果其他事务也查询库存100个，直接修改减库存-100，很容易减多；
select for update，就是在查询的时候上锁，避免超卖现象；
使用：
select * from 表 for update; 会等行锁释放，在返回结果；
select * from 表 for update nowait; 不等待，直接返回锁冲突，不返回结果；
select * from 表 for update wait 5; 等待5秒，若没释放行锁直接返回锁冲突，不返回结果；
select * from 表 for update skip locked; 返回结果，忽略有行锁的记录；

mysql的死锁的处理 *

避免：
1.上线前的压力测试
2.拆分SQL，禁止大事务
3.利用索引,优化索引，减少锁的范围
# 查询innodb锁的状态
show status LIKE 'innodb_row_lock%'
	结果：
	Innodb_row_lock_current_waits 当前正在等待锁定的数量
	Innodb_row_lock_time 从系统启动到现在锁定总时间 （单位毫秒）
	Innodb_row_lock_time_avg 每次等待所花平均时间
	Innodb_row_lock_time_max 从系统启动到现在等待最长的一次时间
	Innodb_row_lock_waits 从系统启动到现在总共等待次数

# 查询是否锁表
show OPEN TABLES ;
show OPEN TABLES where In_use > 0;
# 解锁表
unlock tables;
# 正在运行的任务
show full PROCESSLIST;
# 杀死进程，（一般这样就解锁）
kill id;
# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM performance_schema.data_locks; （8版本之后是该命令）
# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM performance_schema.data_lock_waits;（8版本之后是该命令）

# XA模式，如果事务 try_mysql_thread_id 为0，需要手动回滚事务，不能通过kill 删除该id
xa recover;
# 回滚XA事务格式
xa rollback 'left(data,gtrid_length)', 'substr(data,gtrid_length+1,bqual_length)', formatID;
xa rollback '192.168.0.102:8091:7935837828044361737', '-7935837828044361738', 9752;

Mysql集群同步时为什么选择binlog？

如果同步二进制文件，一旦同步过程中有中断，不好缺点哪些数据同步失败和成功，
优点就是如果中断了，还可以继续，支持增量同步；
binlog是Mysql日志，没有存储引擎限制；

Mysql是如何存储文件

如：ppt
1.当我们文件大小比较小，查询频率比较高，后期维护性不高（如：原先4k，现在2K，对之前数据维护），我们可以存在数据库中；可以使用blob数据类型，二进制的字符类型；
2.一般我们上传到云平台，数据库只记录地址
上传过程的问题：
1.SQL过大，需要调整
2.主从同步慢
3.占用网络带宽
4.使用二进制，浏览器很难使用到缓存

如何存储ip地址

1.可以使用字符串，但缺点范围查询，需要like影响性能
2.整型，支持范围查询某个段的查询，可使用inet_aton()、inet_ntoa()
-- 插入
INSERT INTO 表 (ID,IP) VALUES (1 , inet_aton('192.168.2.12'));
-- 查询
select id,inet_ntoa(ip) as ip from 表;

日期和时间如何存储

TIMESTAMP：跟时区有关，字节较小
DATETIME：字节较大
不建议使用字符串，因为不好范围查询

char和varchar区别

char是固定长度，varchar弹性的长度；
char对于更新频繁操作的字段，读取速度更快，但缺点就是浪费空间；

浮点类型如何选择

如果有计算保持精度使用decimal
如果只展示可以使用字符串、float、double

预编译的SQL好处

预编译的SQL会被缓存下来，防止SQL注入
例子：select * from 表 where 条件=？； 该？只是参数，带进来，整个SQL语句会缓存下来，只有？参数不同。

join与子查询哪个效率高

join的效率高，因为子查询在底层会创建临时表，然后再删除临时表，多了一步操作；
建议join查询不超过3张表，嵌套越多，占用内存空间越大

优化join查询 *

1.可以设置冗余字段，避免关联查询
2.小表驱动大表
3.关键字段需要加索引

是否有过Mysql调优

分为：
1.SQL调优
  根据SQL的执行计划来分析SQL，是否增加索引，进行索引优化；
  减少函数的使用；
2.表结构优化
   表结构优化 - 尽量字段使用最小空间进行存储元素；
   根据业务考虑，是否有冗余字段，快速查询，减少关联查询；
   根据业务考虑，对于大数据，是否冷热数据分离；
3.数据库参数调优
   从硬件考虑 - 硬盘读写速度优先考虑，尽量磁盘单独给数据库使用，避免使用Docker部署，把磁盘分给其他服务；
   网络方面 - 提升带宽；
   更改数据库的配置文件，加大内存缓存大小，方便快速排序，是有单路排序；
   事务优化，避免大事务。尽量行锁，不用升级到表锁；
   对应数据安全 - 数据集群化配置；

索引是如何分析和调优 *

我们可以通过explan查询SQL执行计划，进行分析：
如果id相同，从上往下走，id不同，从大往小走；
然后查询type字段，避免是all，进行全表扫码；
然后key，目前使用到的索引，然后旁边还有该表有哪些索引；
然后row，影响的行数，旁边还有百分比，比如行100，百分比是10%，100行有10%是有用的

Mysql数据库CPU飙升如何处理 *

首先我们先排除是不是其他因素造成的，因为我们现在几乎部署都是云环境下，先看一下容器有没有问题；再看一下当前系统有没有跑其他的程序；
然后再看Mysql，一般都是大SQL造成，使用show full PROCESSLIST;进行分析，但这种如果并发量比较大，需要不断的刷新，很难看出；
我们可以使用pidstat -u 查看一下CPU使用率 例子：pidstat -u 1 （可以看到pid 线程id）
pidstat -t -p 24319（线程id）（可以看到cpu高的tid）
然后再去SQL命令行执行
SELECT * FROM performance_schema.threads WHERE THREAD_OS_ID = 63938 （tid） OR THREAD_OS_ID = 63939 （tid）

有没有进行过分库分表 *

一般我们先不建议分库分表，如果进行了，SQL复杂度也上升；能不分就不分，看看是否使用其他中间件可以解决不；
优先：1.查看SQL性能很难再优化再考虑，
	 2.细化业务，增加缓存解决
	 3.读写分离进行解决
	 4.先分库
	 5.再分表，我们先进行冷热数据分离
垂直分库：我们把一个数据库，拆分多个数据库，按业务化分；
水平分表：把一张大表拆分，多个表，例：a表存id前100数据，b表存id200-300数据，c表存id300后数据

实现分库分表的思路

我们可以使用中间件，如：Mycat，一个伪装成Mysql服务，做一个代理，然后通过配置Mycat路由的规则，再访问对应的数据库；
优点：使用方便，开发者连接数据库，只连接Mycat就可以，效率没那么高。
然后可以通过代码的形式，如AOP进行拦截处理，框架有：Sharding-Sidecar、Sharding-JDBC

分库分表引发的问题 *

1.执行效率，原先是一个库，现在是多个库的查询
2.表结构很难调整，如每次调整，可能造成数据迁移
3.分布式id问题
4.产生跨库join，效率会底
5.如果使用中间件如Mycat，大家都请求这台机器，这个机器的性能，带宽问题	

为什么使用视图

视图就是虚拟表，并不会提示性能；会提升代码可读性，SQL复用性会提高，

什么是存储过程

就是通过SQL编写复杂逻辑，它优点：提升SQL执行效率，一般的SQL执行一次编译一次，存储过程的SQL，它只编译一次，效率会高；
但现在很少有公司写存储过程了，缺点：不利于维护，不方便做数据迁移，存储过程调试不方便，当有并发量下会给数据库增加性能瓶颈；
一般什么情况会使用，就是核心代码，比如核心业务逻辑，这个开发人只管理该业务逻辑，写存储过程；其他开发人员写代码访问数据库，没有权限看到核心业务逻辑的存储过程，只有使用权，有一定的安全性。但这种问题不好调试，就是存储过程改动一点，这些开发人员并不知情，导致有问题想不到是存储过程引起的。

什么是外键

就是一个表和另一个表进行关联字段
现在不建议使用外键，因为不适合做分布式，高并发集群，每次插入影响速度，因为要级联，耦合度高；
如单机，并发低的外键比较适合

where和having的区别

having是使用聚合函数进行过滤的，where是正常的过滤

MySQL插入百万级的数据如何优化？ *

1 可以通过存储过程，先去掉索引，再导入数据，再创建索引（不推荐，如果有集群不推荐使用存储过程）
2 分批提交，一次性提交一部分数据
3 可启用多线程进行导入

有个表有千万数据，查询慢如何优化

前端优化:
	1.避免无效的刷新页面（就是如果是机器人刷新，需要限制）
	2.减少网络交互，避免重复查询，减少查询
增加缓存：
	1.热点数据放入Redis中
	2.高频查询（双写一致性是否考虑）
数据库优化:
	1.字段类型是否合适，如字符串char还是varchar
	2.可以查看SQL执行计划，有没有用到索引，进行优化SQL
	3.然后SQL优化不好了，进行优化业务，是否可以分段查询，先干什么再干什么，例子：第一步操作，第二部操作...
      如果join表过多，是否允许有冗余字段
      然后冷热数据分离，再细化分微微服务，再考虑读写分离、分表分库

count（列名）和count（*）区别

count(*)会统计null的数据，count(列名)会过滤掉

如果超大分页如何处理

因为分页是limit，比如查询1000,10  并不是跳过1000，还需要查1000条；
1 可以通过延迟关联,就是先通过分页找出对应主键id，然后再关联查询完整数据
  例子：select * from 表 a,（select id from 表 where 1000,10 ）b where b.id = a.id
2 可以通过排序字段优化，一般都是先排序在进行limit分页
一般大数据，前几页都是从缓存查询出来，后几页进行查询数据

mysql服务器无规律异常重启解决

排查问题
1.运行内容占用率，是不是OOM问题，解决：增加物理内存
2.查看mysql的buffer，调整合适的值
3.查看mysql的连接数，是否控制连接数
如果出现该问题，先看日志，在看相关配置参数，是否改变配置参数，万能解决这些都是通过花钱能解决的，加内存。

mysql上线修改表结构有哪些风险

产生问题，可能会锁表，导致表的读写问题；
一般我们会创建新表，然后导入数据，再重命名；

mysql的多实例部署

就是一台机器，部署多台服务，这样会压榨服务器的性能；缺点就是互相影响；
现在我们一般都是云部署，使用容器，多个沙盒。