MySQL

leng_yimo

已于 2023-06-17 15:13:58 修改

阅读量254

点赞数

分类专栏： Java知识点汇总文章标签： mysql 数据库 sql Powered by 金山文档

于 2023-03-07 23:16:04 首次发布

本文链接：https://blog.csdn.net/leng_yimo/article/details/129392613

版权

Java知识点汇总专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、存储引擎

1、简单描述一个MySQL的内部结构？

MySQL的基本架构示意图：

大体来说，MySQL可以分为server层和存储引擎层两部分。

① server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能

② 存储引擎层：存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎

连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。

查询缓存：连接建立完成后，你就可以执行select语句了，此时会先进行查询缓存(缓存是key-value格式；key是sql语句，value是sql语句的查询结果)。

分析器：

1、词法分析： MySQL需要识别出里面的字符串分别是什么，代表什么。

2、语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器：调用存储引擎接口，执行sql语句，得到结果

2、MySQL中char和varchar有什么区别？

char是一种固定长度的类型，varchar是一种可变长度的类型

char列的长度固定为创建表时的长度，其范围为0~255。当保存为char类型时，在它们右边填充空格以达到指定的长度。当检索到char值时，尾部的空格被删除掉

varchar列中的值为可变长字符串，其范围为0~65535

char会造成空间浪费，但是有速度优势；而varchar节省了空间，但是速度就不如char。

3、MySQL中float和double的区别是什么？

在内存中占有的字节数不同：单精度内存占4个字节, 双精度内存占8个字节

有效数字位数不同(尾数) ：单精度小数点后有效位数7位, 双精度小数点后有效位数16位

数值取值范围不同：根据IEEE标准来计算!

在程序中处理速度不同：一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快

4、MySQL中date和datetime类型的区别？

date类型可用于需要一个日期而不需要时间的部分

-格式为'YYYY-MM-DD' 范围是'1000-01-01' 到'9999-12-31'

datetime类型可用于需要同时包含日期和时间的信息的值

-格式为YYYY-MM-DD HH:mm:ss 范围是'1000-01-0100:00:00' 到 '9999-12-3123:59:59'

5、MySQL中sql语句执行的顺序？（查询语句）

6、开发中用的什么数据库，group by 和order by 都是啥意思？

数据库：mysql | oracle

group by：分组，将具有相同属性的记录放在一起

order by ：排序

7、Inner join、left join和right join 的区别？(高频)

inner join:内连接

只展示2个表有关联关系的数据，如果没有关联关系不展示；

left join:左外

左表的数据要完全展示，右表只展示和左表有关联关系的数据，

如果左表有一些数据和右边没有关系，右边用NULL代替；

right join:右外

右表的数据要完全展示，左表只展示和右表有关联关系的数据，

如果右表有一些数据和左边没有关系，左边用NULL代替；

左外和右外是相对的

8、什么是聚合函数？

概念：聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型：

AVG()

SUM()

MAX()

MIN()

COUNT()

9、a、b两表字段相同，写一条sql将a表数据拷贝到b表中？

将旧表的数据添加到新表中

insert into 已有的新表 (列名) select 原表列名 from 原表名

10、MySQL中如何获取当前数据库版本？

1.登录到mysql中以后：select version()

2.进入mysql的bin目录：mysql --version | -V

11、MySQL如何实现分页？

limit start ,size

-- start=(页码-1) * size

-- size=页大小

12、MySQL中往两个字段中间添加一个新字段，sql语句如何实现？

ALTER TABLE table_name ADD 字段名称 char(4) after 已存在字段名称

(alter table table_name add 字段名称 char(4) after 已存在字段名称 )

13、MySQL行列转换，sql语句如何实现？

group by配合case表达式

14、MySQL中常用的几种约束？

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性

主键

默认值

唯一

外键

非空

15、MySQL体系架构

连接层：负责客户端的连接，认证，鉴权

服务层：sql的接口(DML,DDL,DQL,存储过程，触发器)，解析器，sql优化器，缓存

存储引擎

InnoDB

MyISAM

Memory

Merge

存储层

数据文件

日志文件

16、MySQL数据库的三大范式是什么？

针对数据表中的列，列要具备原子性，不可再拆分

任何一个非主键字段，都依赖于主键字段。每个表只描述一件事情（拆分解决）

任何2个非主键字段数值之间不存在函数依赖

17、MySQL数据库如何实现主从复制？如果主库出现问题了，从库怎么办？

mysql主从复制

Mysql的主从复制中主要有三个线程master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件

主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。

从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。

从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题，就是假设主库挂了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

1.全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端写入操作成功，但是很显然这个方式的话性能会受到严重影响。

2.半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

18、数据库连接池原理是什么？

连接池的工作原理主要由三部分组成，分别为连接池的建立、连接池中连接的使用管理、连接池的关闭

连接池的创建。

一般在应用启动后，连接池会根据应用配置配置创建，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销

连接池中连接的使用管理

连接池中连接的使用管理是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。

当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户端使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。这样避免频繁的创建、释放连接所带来的系统资源开销。

连接池的关闭

当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

19、常用的高性能连接池有哪些？

Druid

BoneCP

HikariCP：SpringBoot官方推荐

20、存储引擎的介绍

存储引擎是决定了表中数据如何存储，查询，更新及索引如何存储。

默认的存储引擎：show engines

创建表的时候指定存储引擎

create table ...(
)engine=innodb

create table ...(
)engine=memory

21、数据库存储引擎有哪些？(高频)

MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

MySQL5.0支持的存储引擎包含： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

22、如何选择存储引擎？

如果没有特别的需求，实际开发全部都采用InnoDB。

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

MyISAM：以读为主的应用程序，比如博客系统、新闻门户网站（MongoDB代替MyISAM）。

Memory：若需要一个用于查询的临时表，可以选择Memory

23、存储引擎的特点

InnoDB

1. 兼顾高可用性和高性能的存储引擎

支持【事务】及ACID模型

【外键】

【行锁】

2. mysql8.0 每个InnoDB的表对应一个*.idb文件包含表结构，数据和索引

MyISAM

1.不支持事务

2.不支持外键

3.不支持行锁

4.每个MyISAM的表对应三个文件myi(索引),myd(数据),sdi(表结构)

Memory

1.数据存储在内存中，断电丢失，经常作为临时表或者缓存

2.访问速度快

3.每个Memory存储引擎的表就一个文件sdi(表结构)

24、InnoDB与MyISAM的区别？(高频)

1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2、InnoDB支持外键，而MyISAM不支持。

3、InnoDB是支持表锁和行级锁，MyISAM只支持表锁

25、存储引擎常用命令？

show engines; 查看MySQL提供的所有存储引擎

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

show variables like '%storage_engine%';  查看mysql默认的存储引擎

show table status like "table_name"\G     查看表的存储引擎

26、对MySQL存储过程的认识？

经过事先编译存储在数据库的一段SQL语句的集合，类似于java中的方法。一般可以把一些比较耗时的操作编写到存储过程中，然后使用java程序调用，减少数据在数据库和应用服务器之间的传输，从而提高了数据处理效率。

27、MySQL中存储过程和存储函数的区别？

返回值

存储过程可以没有返回值，也可以通过out参数返回多个值

存储函数必须通过return返回一个值；

调用

存储过程通过call来调用

存储函数通过select来调用

28、对触发器的理解？

触发器是一种与表绑定的一种数据库对象

作用是：

监听表中记录变化。（insert update,delete），当表中数据产生的增删改的操作，触发器就会被执行；

分类

1.粒度

行级：执行一条insert update delete的sql影响表中每一条记录，触发器都会执行

语句级：执行一条insert update delete的sql无论影响表中多少条记录，只触发一次

2.操作类型

监听insert的触发器

监听update的触发器

监听delete的触发器

二、索引

29、什么是索引？(高频)

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效查询数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

如下面的示意图所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

导入资料中提供的sql脚本文件，已经准备了1000W条数据。

A. 根据ID查询

select * from tb_sku where id = 1999\G ;

查询速度很快，接近0s ，主要的原因是因为id为主键，有索引；

查看执行计划：

B. 根据 name进行精确查询

select * from tb_sku where name = '华为Meta1999'\G ;

查询速度太慢了，几乎使用了9s才完成数据的查询。

查看执行计划：

如果没有索引

如果有索引，索引假如是二叉搜索树

注意：索引真正的数据结构不是二叉搜索树

优缺点

1.提高查询效率，减少IO成本

2.提高排序效率

3.降低DML语句效率

4.索引的存储需要占用磁盘空间

30、什么是聚簇索引和非聚簇索引？(高频)

聚簇索引：将数据和索引放在一起，并且是按照一定顺序组织的，找到索引也就找到了数据。一般情况下主键就是默认的聚簇索引。

优缺点：使用聚簇索引进行行数据查询效率较高，更新数据的效率较低，同时会占用的存储空间较大。

非聚簇索引：叶子结点不存储数据，存储的是行的物理地址，在进行行数据查询的时候，需要根据物理地址值从数据库表中再次进行查询【回表】

优缺点：使用非聚簇索引进行行数据查询效率较低，更新数据的效率较高，同时占用的存储空间较小。

31、MySQL中如何给字段创建索引？(高频)

为了提升上述查询效率，可以对name字段创建索引。创建索引有两种方式：

1、方式一：在创建表的时候创建索引

-- 语法结构
-- [使用]：
create table tableName( id int not null, 列名 列的类型, [索引类型] index [索引名] (列名,...););

示例：

-- 示例代码
CREATE TABLE `index1` ( 
  id int(11) DEFAULT NULL, 
  name varchar(20) DEFAULT NULL, 
  sex tinyint(1) DEFAULT NULL, 
  KEY index1_id (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、方式二：使用create index语句进行索引创建

语法：

-- [使用]：
create [unique|fulltext] index 索引名 on 表名 (列名);
-- [注意]：unique 唯一性索引、fulltext 全文索引
-- 如果指定的列的名称是多个，那么这个索引我们将其称之为复合索引

示例：

-- 创建普通索引 INDEX(普通索引)
create index idx_duration on examination_info (duration);
 
-- 创建唯一索引 UNIQUE(唯一索引)
create unique index uniq_idx_exam_id on examination_info (exam_id);
 
-- 创建全文索引 FULLTEXT(全文索引)
create fulltext index full_idx_tag on examination_info (tag);

再次进行查询：

通过explain ，查看执行计划，执行SQL时使用了刚才创建的索引

3、方式三：修改表的方式创建索引

语法：

-- [使用]：
alter table 表名 add [索引类型] index 索引名(列名);
-- [注意]：索引类型：普通索引、唯一性索引、全文索引

示例：

 -- 创建普通索引 INDEX(普通索引)
alter table examination_info add index idx_duration (duration);

-- 创建唯一索引 UNIQUE(唯一索引)
alter table examination_info add unique index uniq_idx_exam_id (exam_id);

-- 创建全文索引 FULLTEXT(全文索引)
alter table examination_info add fulltext index full_idx_tag (tag);

32、MySQL中创建索引越多越好吗？为什么？

不是。

索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

33、常见的索引约束有哪些？(高频)

1、UNIQUE：唯一索引

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为UNIQUE。

2、FULLTEXT：全文索引

表示全文搜索，在检索长文本的时候，效果最好，短文本建议使用普通索引,但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

3、SPATIAL：空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。如果没有指定索引约束，此时创建的索引就是普通索引。而一般情况下只需要创建普通索引。

4、普通索引：如果没有指定索引约束，此时创建的索引就是普通索引。而一般情况下只需要创建普通索引。

34、常见的索引类型有哪些？(高频)

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。也就是，不同的存储引擎数据结构不同。

MySQL目前提供了以下4种索引：

B+Tree索引：最常见的索引类型，大部分索引都支持 B 树索引。

HASH索引：只有Memory引擎支持，使用场景简单。

R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理位置空间数据类型，通常使用较少，不做特别介绍。

Full-text （全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

各种存储引擎对索引的支持：

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。

35、怎么看到为表格定义的所有索引？

语法：

show index  from  table_name;

示例：查看tb_sku表中的索引信息；

show index from tb_sku ;

注意：主键自动创建索引

36、唯一索引比普通索引快吗, 为什么？

唯一索引不一定比普通索引快, 还可能慢。

1、查询时, 在未使用 limit 1 的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微。

2、更新时, 这个情况就比较复杂了. 普通索引将记录放到 change buffer 中语句就执行完毕了。而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作。

对于写多读少的情况 , 普通索引利用 change buffer 有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引。

37、索引的优缺点？

1、优点

提高数据检索的效率，降低数据库的 IO 成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

2、缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

38、什么情况下设置了索引但无法使用？(高频)

环境准备

建表语句：

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;

初始化数据sql：

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

创建索引：

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

全职匹配查询：对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

① 违背了最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

违法最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

② 范围查询: 范围查询右边的列，不能使用索引。

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件address 没有用到索引。

>= 不走索引
> 走索引

③ 列运算：不要在索引列上进行运算操作，索引将失效。

④ 字符串：字符串不加单引号，造成索引失效。

由于，在查询时没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

⑤ 模糊查询：以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：

通过覆盖索引来解决

总结：MySQL索引命中与失效

39、在建立索引的时候,都有哪些需要考虑的因素呢？

① 建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合。

② 如果需要建立联合索引的话,还需要考虑联合索引中的顺序。

③ 此外也要考虑其他方面,比如防止过多的索引对表造成太大的压力

40、创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因？

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,

其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度。

41、二叉搜索树

二叉树(度为2的树)，所有的节点最多2个子树

根节点大于所有左子树，小于右子树

顺序插入会成为一个链表，影响插入，删除，查询的时间复杂度0(n)，如下图所示：

如果数据量比较大，树的高度比较高

42、平衡二叉搜索树

顺序插入会成为一个链表，影响插入，删除，查询的时间复杂度0(n)

AVL

红黑树，如下图所示：

43、如果数据量比较大，树的高度比较高

B树：属于一种多叉树

1.一个节点可以存储多个数据

2.一个节点可以有多个子树

3.子树的范围[m/2] ~ m

4.数据范围[m/2]-1 ~ m-1

5.根节点：

1~m-1

2~m

B+树

1.基于B树一种数据结构

2.所有数据都会存入叶子：

3.叶子形成一个单向链表

4.非叶子只是用来索引

MySQL B+树

Hash

eg:对name这一列创建hash索引，对name这一列计算hash值，取模hash表的长度，将数据[name及记录的hash值]存入hash表指定位置

1.缺点：

只能进行等值运算，无法进行返回运算

无法利用索引进行排序

效率要比B+tree高

为什么InnoDB存储引擎采用B+tree

1.为什么不使用二叉搜索树？

如果数据量比较大，导致树的高度|深度比较深；

2.为什么不使用B树？

B树中每个节点都会存储索引key和数据

MySQL的一个节点最大16K,一个索引数据大小8B+1K

最大的B树就是16阶

3.B+树

只有叶子节点存储索引key和数据，非叶子节点只存储索引的key

最大的B+树 16K / 8B 约等于1000多阶

4.为什么没有使用hash索引？

hash表中数据无序，无法进行范围搜索

索引的分类

1.主键索引：只能有一个

2.唯一索引

3.常规索引

4.全文索引

5.聚集索引

将索引和行数据存储在一起，在索引数据结构的叶子节点中保存了行数据

一张表必须有聚集索引

什么索引是聚集索引：

主键

第一个唯一索引

既无主键又无唯一索引，用一个自动生成虚拟主键，最终利用虚拟主键生成聚集索引

6.二级索引

将索引和行数据分开存储，在索引数据结构的叶子节点中保存了主键

7.如果基于name字段查询，走哪个索引？

哪个性能高

第一个的sql,走聚簇索引直接可以获取行数据

第二个的sql,走二级索引，先获取id，再通过聚簇索引获取行数据

Innodb存储引擎聚簇索引存储数据个数

Innodb存储引擎的B+tree节点存储在页中，一页大小16k

2. 非叶子：主键8个字节，指向子树的指针6个字节

3. 8*n + 6 * (n+1) = 16 * 1024：1171指针

4. 叶子中存储行数据大概1k：大概存储16个数据

三、SQL优化

44、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？(高频)

在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么?

① 是查询条件没有命中索引?

② 是load了不需要的数据列?

③ 还是数据量太大?

所以优化也是针对这三个方向来的：

1、分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。

2、分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

3、如果是表中的数据量是否太大导致查询慢，可以进行横向或者纵向的分表.

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

慢查询的配置：

# 是否开启慢查询日志，1表示开启，0表示关闭
slow_query_log=1

# 旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
log_slow_queries=/var/lib/mysql/mysql_slow.log

# 新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow_query_log_file=/var/lib/mysql/mysql_slow.log

# 慢查询阈值，当查询时间大于设定的阈值时，记录日志。
long_query_time = 1

# 未使用索引的查询也被记录到慢查询日志中（可选项）。
log_queries_not_using_indexes=0

# 日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
log_output='FILE,TABLE'

添加如上配置重启服务，产生慢查询日志：

慢查询日志文件内容：

执行如下sql语句模拟慢查询：

-- 不会记录到慢查询日志中
select sleep(0.2) ;

-- 会记录到慢查询日志中
select sleep(2) ;

45、数据库表结构设计字段怎么优化？

表方面

1.核心字段且常用字段，应该建立建立成定长，比如说int ,char等定长，并且这些定长的字段放在一张表中

2.常用字段和不常用字段要分离（垂直分表）

3.在1对多的情况下，需要在关联统计的表上添加冗余字段

字段类型优先级

整型 > date,time > enum,char>varchar > blob,text

原则

1.够用就行,不要慷慨

原因: 大的字段浪费内存,影响速度

以年龄为例 tinyint unsigned not null ,可以存储255岁,足够。用int浪费了3个字节

如果varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存

2.尽量避免用NULL

NULL不利于索引

3.性别

char(1)3个字长字节

enum('男','女')内部转成数字来存,多了一个转换过程

tinyint：定长1个字节.

46、SQL性能分析工具

MySQL四种SQL性能分析工具

47、如何优化SQL语句？(高频)

⑴ SQL语句中in包含的值不应过多

MySQL对于in做了相应的优化，即将in中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。

例如：

select id from table_name where num in(1,2,3)

对于连续的数值，能用between 就不要用in了。

⑵ select语句务必指明字段名称，尽量避免使用select *

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

⑶ 如果排序字段没有用到索引，就尽量少排序

⑷ 如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

or查询：

(1) or两边放联合索引，不触发索引(如果两边是单列索引另算)

(2) or两边是单列索引，查询走索引

(3) or两边只要有一个不是索引就不启用索引查询

单例索引演示：

复合索引演示：

(4) or两边一个是联合索引的最左索引一个是单例索引才生效，否则失效

示例：

-- 创建单列索引
 create index idx_nickname on tb_seller(nickname) ;

使用索引：

索引失效：

⑸ 不建议使用%前缀模糊查询：例如like “%name”或者like “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用like“name%”。

⑹ union-all 代替union

⑺ 不使用not in和like语句

⑻ 为经常用来查询的字段添加索引

⑼ 在查询语句select前面加上explain

⑽ 多使用limit

48、超大分页怎么处理？(高频)

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 1000000 , 10，此时需要MySQL排序前1000010 记录，仅仅返回1000000 - 1000010 的记录，其他记录丢弃，查询排序的代价非常大。

示例：

explain select * from tb_sku limit 1000000 , 10 ;

执行查询耗时：

优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

示例：

explain select * from tb_sku s , (select id from tb_sku order by id limit 1000000 , 10 ) t where t.id = s.id ;

执行查询耗时：

优化思路二：该方案适用于主键自增的表，可以把limit 查询转换成某个位置的查询。

示例：

 explain select * from tb_sku where id > 1000000 limit 10 ;

执行查询耗时：

49、MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？(高频)

1、设计良好的数据库结构：

选择合适的存储引擎，设计合理的表结构、选择恰当的数据类型、避免使用过多的关联查询等，可以减少不必要的查询和计算量，提高数据库性能。

2、查询优化，书写高效率的SQL：

尽可能使用简单的查询语句，避免使用子查询、联合查询等复杂语句。同时建议使用EXPLAIN命令来查看查询执行计划，以便发现潜在的性能问题。

3、从功能方面优化：

可以对索引优化，采用缓存缓解数据库压力，分库分表

4、从架构方面优化：

可以采用主从复制，读写分离，负载均衡。

5、不经常改动的页面，生成静态页面。

6、定期维护和优化：

定期进行数据库备份、日志清理、碎片整理等操作，保持数据库的健康状态，减少性能下降和故障风险

顺序优化：

1.SQL语句及索引的优化

2.数据库表结构的优化

3.系统配置的优化

4.硬件的优化

四、事务

50、什么是事务？(高频)

概述：由多个操作组成的一个逻辑单元，组成这个逻辑单元的多个操作要么都成功，要么都失败。

举例：转账

51、ACID是什么？可以详细说一下吗？(高频)

A=Atomicity原子性：就是上面说的,要么全部成功,要么全部失败，不可能只执行一部分操作。

C=Consistency一致性：系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态。

I=Isolation隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况。

D=Durability持久性：一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。

52、说一下MySQL数据库事务的三个安全性问题？

脏读：指的是一个事务读到另一个事务未提交的数据。

不可重复读：指的是一个事务读到了另一个事务已经提交的 update的数据, 导致多次查询结果不一致，导致在一个事务中两次读取数据不一致。

虚读/幻读：指的是一个事务读到了另一个事务已经提交的 insert的数据,导致多次查询结果不一致。

53、说一下MySQL数据库事务的隔离级别？

read uncommitted：脏读、不可重复读、虚读/幻读都可能发生。

read committed：避免脏读, 但是不可重复读和虚读/幻读是有可能发生的.

repeatable read：避免脏读, 不可重复读. 但是虚读/幻读是有可能发生的.

serializable(串行化的)：避免脏读, 不可重复读, 虚读/幻读

安全性：read uncommitted < read committed < repeatable read< Serializable

效率：read uncommitted > read committed > repeatable read > Serializable

一般数据库设置：read committed 和 repeatable read

-MySQL默认：repeatable read

-Oracle默认：read committed

54、并发事务带来哪些问题？(高频)

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（Lost to modify）：指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读（Phantom read）：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

55、怎么解决这些问题呢？MySQL的默认隔离级别是？(高频)

解决方案：对事务进行隔离

MySQL的四种隔离级别如下:

未提交读(READ UNCOMMITTED)：这个隔离级别下,其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚)。这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用。

sql演示：

# 插入数据
insert into goods_innodb(name) values('华为');
insert into goods_innodb(name) values('小米');

# 会话一
set session transaction isolation level read uncommitted ;        # 设置事务的隔离级别为read uncommitted
start transaction ;                                                # 开启事务
select * from goods_innodb ;                                    # 查询数据

# 会话二
set session transaction isolation level read uncommitted ;        # 设置事务的隔离级别为read uncommitted
start transaction ;                                                # 开启事务
update goods_innodb set name = '中兴' where id = 10 ;               # 修改数据

# 会话一
select * from goods_innodb ;                                    # 查询数据

已提交读(READ COMMITTED)：其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改。

sql演示：

# 会话一
set session transaction isolation level read committed ;        # 设置事务的隔离级别为read committed
start transaction ;                                                # 开启事务
select * from goods_innodb ;                                    # 查询数据

# 会话二
set session transaction isolation level read committed ;        # 设置事务的隔离级别为read committed
start transaction ;                                                # 开启事务
update goods_innodb set name = '中兴' where id = 1 ;               # 修改数据

# 会话一
select * from goods_innodb ;                                    # 查询数据

# 会话二
commit;                                                            # 提交事务

# 会话一
select * from goods_innodb ;                                    # 查询数据

REPEATABLE READ(可重复读)：可重复读隔离级别解决了上面不可重复读的问题(看名字也知道)，但是不能完全解决幻读。

MySql默认的事务隔离级别，就是：REPEATABLE READ

select @@tx_isolation;

sql演示(解决不可重复读)：

# 会话一
start transaction ;                                                # 开启事务
select * from goods_innodb ;                                    # 查询数据

# 会话二
start transaction ;                                                # 开启事务
update goods_innodb set name = '荣耀' where id = 1 ;               # 修改数据

# 会话一
select * from goods_innodb ;                                    # 查询数据

# 会话二
commit;                                                            # 提交事务

# 会话一
select * from goods_innodb ;                                    # 查询数据

sql演示(测试不会出现幻读的情况)：

# 会话一
start transaction ;                                                # 开启事务
select * from goods_innodb ;                                    # 查询数据

# 会话二
start transaction ;                                                # 开启事务
insert into goods_innodb(name) values('小米');                      # 插入数据
commit;                                                            # 提交事务

# 会话一
select * from goods_innodb ;

sql演示(测试出现幻读的情况)：

# 表结构进行修改
ALTER TABLE goods_innodb ADD version int(10) NULL ;

# 会话一
start transaction ;                                                # 开启事务
select * from goods_innodb where version = 1;                    # 查询一条不满足条件的数据

# 会话二
start transaction ;                                                # 开启事务
insert into goods_innodb(name, version) values('vivo', 1);        # 插入一条满足条件的数据 
commit;                                                            # 提交事务

# 会话一
update goods_innodb set name = '金立' where version = 1;            # 将version为1的数据更改为'金立'
select * from goods_innodb where version = 1;                    # 查询一条不满足条件的数据

SERIALIZABLE(可串行化)：这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。

五、锁

56、MySQL中有哪几种锁？

从对数据操作的粒度分：

1）表锁：操作时，会锁定整个表。

2）行锁：操作时，会锁定当前操作行。

3）页面锁：会锁定一部分的数据

Mysql的行锁和表锁

从对数据操作的类型分：

1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这2种锁的特性可大致归纳如下：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；
解锁:     unlock tables;

MyISAM锁：读锁案例

准备环境

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');

客户端一：

1）获得tb_book 表的读锁

lock table tb_book read;

2）执行查询操作

select * from tb_book;

可以正常执行，查询出数据。

客户端二：

3）执行查询操作

select * from tb_book;

客户端一：

4）查询未锁定的表

select name from tb_seller;

在一个session里面lock table锁表，你只能访问你锁的那张表；访问其他表，就报这个异常。

客户端二：

5）查询未锁定的表

select name from tb_seller;

可以正常查询出未锁定的表；

客户端一：

6）执行插入操作

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

执行插入，直接报错，由于当前tb_book 获得的是读锁，不能执行更新操作。

客户端二：

7）执行插入操作

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 inesrt 语句，立即执行；

写锁案例

客户端一 :

1）获得tb_book 表的写锁

lock table tb_book write ;

2）执行查询操作

select * from tb_book ;

查询操作执行成功；

3）执行更新操作

客户端一 :

update tb_book set name = 'java编程思想（第二版）' where id = 1;

更新操作执行成功；

客户端二 :

4）执行查询操作

select * from tb_book ;

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 select 语句，立即执行；

结论：就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集(行)加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

准备环境：

create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

Session-1	Session-2
关闭自动提交功能	关闭自动提交功能
可以正常的查询出全部的数据	可以正常的查询出全部的数据
查询id 为3的数据；	获取id为3的数据；
更新id为3的数据，但是不提交；	更新id为3 的数据，出于等待状态
通过commit，提交事务	解除阻塞，更新正常进行
以上，操作的都是同一行的数据，接下来，演示不同行的数据：
更新id为3数据，正常的获取到行锁，执行更新；	由于与Session-1 操作不是同一行，获取当前行锁，执行更新；