MySql

---

一.Mysql中char和varchar有什么区别？

char是一种固定长度的类型，varchar是一种可变长度的类型
char列的长度固定为创建表时的长度，其范围为0~255。当保存为char类型时，在它们右边填充空格以达到指定的长度。当检索到char值时，尾部的空格被删除掉
varchar列中的值为可变长字符串，其范围为0~65535
char会造成空间浪费，但是有速度优势；而varchar节省了空间，但是速度就不如char。

二.Mysql中float和double的区别是什么？

在内存中占有的字节数不同,
单精度内存占4个字节, 双精度内存占8个字节(和java一样)
有效数字位数不同(尾数)
单精度小数点后有效位数7位, 双精度小数点后有效位数16位
数值取值范围不同
根据IEEE标准来计算!
在程序中处理速度不同,
一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快

三.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'

四.Mysql中sql语句执⾏的顺序？（查询语句）

五.说一下Mysql常用的存储引擎？

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

脏读：

指的是一个事务读到另一个事务未提交的数据.

不可重复读：

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

幻读：

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

七.说一下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

八.谈谈对Mysql存储过程的认识？

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

九.Mysql中存储过程和存储函数的区别？

返回值

存储过程可以没有返回值，也可以通过out参数返回多个值
存储函数必须通过return返回一个值；

调用

存储过程通过call来调用
存储函数通过select来调用

十.说一说你对触发器的理解？

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

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

分类

粒度

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

操作类型

监听insert的触发器
监听update的触发器
监听delete的触发器

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

mysql | oracle

group by

分组->将具有相同属性的记录放在一起

order by

排序

十二.Inner join、left join和right join 的区别？(高频)

inner join:内连接

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

left join:左外

左表的数据要完全展示，右表只展示和左表有关联关系的数据，
如果左表有一些数据和右边没有关系，右边用NULL代替；

right join:右外

右表的数据要完全展示，左表只展示和右表有关联关系的数据，
如果右表有一些数据和左边没有关系，左边用NULL代替；

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

INSERT INTO 目标表 SELECT * FROM 来源表;

十三.Mysql数据库的三大范式是什么？

第一范式:列要具备原子性，不可再拆分*

第二范式:任何一个非主键字段，都依赖于主键字段 -> 每个表只描述一件事情（拆分解决）

第三范式 : 任何2个非主键字段数值之间不存在函数依赖关系

十四.Mysql中如何获取当前数据库版本？

登录到mysql中以后

select VERSION()

进入mysql的bin目录

mysql --version|-V

十五.Mysql如何实现分页？

limit 起始页码 ,每页大小

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

十六.Mysql中往两个字段中间添加一个新字段，sql语句如何实现？

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

十七.Mysql行列转换，sql语句如何实现？

group by配合case表达式

十八.Mysql中如何给字段创建索引？

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON tbl_name(index_col_name,…)

十九.Mysql中创建索引越多越好吗？为什么？

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

二十.说一说你们公司数据库都是如何优化的？

尽量避免使用select *
union-all 代替union
不使用NOT IN和like语句
为经常用来查询的字段添加索引
在查询语句select前面加上explain
利用查询缓存优化数据库查询
多使用limit
分库分表
定时任务
读写分离

二十一.说一下Mysql的表锁和行锁？

零.全局锁:锁定MySQL某个数据库中所有表

典型的应用场景，数据库的备份

一.表锁:锁定表中所有数据

1.表锁:

2.元数据锁(MDL)

维护表元数据的结构一致性,为了避免DMl(Data Manipulation Language)数据操纵语言与DDl(Data Definition Language)数据定义语言冲突,保证读写一致性。
由mysql自动控制,在访问一张表的时候自动加上;
MDL读锁(共享锁)
MDL写锁(排他锁)

3.意向锁

当A线程加一个行锁的时候，同时加一个意向锁；
这时,其他线程加表锁，就可以快速知道。这张表是有锁的；

意向锁之间不会排斥。

二.行级锁:每次加锁操作，锁住的是数据行，粒度小，并发度高。只有InnoDB存储引擎支持

1.行锁

InnoDB实现了一下两种行锁:


常见Sql语句的加的锁:

2.间隙锁

3.临键锁

锁总结:

临键锁是行锁和间隙锁的组合。

二十二.数据库表结构设计字段怎么优化？

表方面

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

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

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

字段类型使用优先级 整型 > date,time > enum,char>varchar > blob,text

原则
够用就行,不要慷慨
原因: 大的字段浪费内存,影响速度
以年龄为例 tinyint unsigned not null ,可以存储255岁,足够。用int浪费了3个字节
如果varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存,尽量避免用NULL
NULL不利于索引
性别
char(1)3个字长字节
enum(‘男’,‘女’)内部转成数字来存,多了一个转换过程
tinyint定长1个字节.

二十三.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默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

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

半同步复制

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

二十四.数据库连接池原理是什么？

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

第一、连接池的创建。

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

第二、连接池中连接的使用管理

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

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

三、连接池的关闭。

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

二十五.常用的高性能连接池有哪些？

Druid
BoneCP
HikariCP->springboot官方推荐

二十六.Mysql中常用的几种约束？

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

主键
默认值
唯一
外键
非空

二十七.mysql体系架构

连接层：

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

服务层

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

引擎层

存储引擎

InnoDB
MyISAM
Memory

Merge

存储层

数据文件
日志文件

二十八.存储引擎的介绍

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

默认的存储引擎
show engines
创建表的时候指定存储引擎
create table …(
)engine=innodb
create table …(
)engine=memory

二十九.存储引擎的特点

InnoDB

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

支持【事务】及ACID模型
【外键】
【行锁】

mysql8.0 每个InnoDB的表对应一个*.idb文件包含表结构，数据和索引
mysql5.0有两个文件,一个存储结构.frm,一个存储内容

MyISAM(MongoDB)

不支持事务
不支持外键
不支持行锁

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

Memory(Redis替换它)

数据存储在内存中，断电丢失，经常作为临时表或者缓存
访问速度快
每个Memory存储引擎的表就一个文件sdi(表结构)

三十一.索引的数据结构

索引是一种高效查询数据的数据结构,
优缺点
1.提高查询效率，减少IO成本
2.提高排序效率
缺点
1.索引占用空间
2.降低DML语句效率

三十二.索引的数据结构是什么

不同的存储引擎数据结构不同

B+Tree索引

全部支持

Hash索引

仅是Memory

R-tree索引

存储地理位置信息
MyISAM

Full-text全文索引

InnoDB & MyISAM

三十三.二叉搜索树

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

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

顺序插入会成为一个链表，影响插入，删除，查询的时间复杂度0(n)
如果数据量比较大，树的高度比较高

三十四.平衡二叉搜索树

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

AVL

左右子树高度之差不大于1

红黑树

五大条件:

节点只有红和黑
根节点一定是黑
不会出现连续的红
从任意节点到叶子节点的黑色节点数量相同
叶子节点一定是黑的

三十五.如果二叉树的数据量比较大，树的高度比较高

1.B树(m是介)

属于一种多叉树

一个节点可以存储多个数据
一个节点可以有多个子树

子树的范围 [m/2] ~ m
数据范围 [m/2]-1 ~ m-1

根节点

存储数据范围 : 1~m-1
介树 : 2~m

2.B+树

基于B树一种数据结构
所有数据都会存入叶子

叶子形成一个单向链表,非叶子只是用来索引

MySQL B+树

3.Hash

eg:对name这一列创建hash索引
对name这一列计算hash值
取模hash表的长度
将数据[name及记录的hash值]存入hash表指定位置
缺点：只能进行等值运算，无法进行返回运算
无法利用索引进行排序,
但是效率要比B+tree高

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

为什么不使用二叉搜索树

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

为什么不使用B树

B树中每个节点都会存储索引key和数据
MySQL的一个节点最大16K,一个索引数据大小8B+1K
最大的B树就是16介

B+树

只有叶子节点存储索引key和数据，非叶子节点只存储索引的key
最大的B+树 16K / 8B 约等于1000多介

为什么没有使用hash索引

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

三十六.索引的分类

0.索引语法:

1.主键索引 Primary

只能有一个

2.唯一索引 Unique

唯一约束创建唯一索引

3.常规索引 : 正常索引

4.全文索引 FullTest

es中的倒排索引

按索引存储形式分:

1.聚集索引

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

一张表必须有聚集索引,且只有一个

什么是聚集索引

如果一张表有主键,那主键索引就是聚集索引

如果没有主键,将使用第一个唯一索引作为聚集索引

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

B+树

row代表这一行下面的数据。

2.二级索引 -> 辅助索引 -> 非聚集索引

其他字段建的索引,就是二级索引

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

可以有多个

7.例子 : 如果基于name字段查询，走那个索引,那个性能高

回表查询

8.哪个索引性能高?

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

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

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

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

非叶子：主键8个字节，指向子树的指针6个字节 8*n + 6 * (n+1) = 16 * 1024 1171指针

叶子中存储行数据大概1k,大概存储16个数据

三十七.事务

1.ACID

Atomic 原子性

Atomic依赖 undo_log

Consistency 一致性

一致性 undo_log + redo_log

isolation 隔离性

隔离性：锁+MVCC

durablity 持久性

持久性依赖redo_log,缓冲区中的脏页写入磁盘，如果出现问题，可以基于redo_log重新操作日志进行恢复

2.MVCC(multi version concurrent contro)多版本并发控制

redo_log:保证事务持久性;
undo_log:保证了原子性和一致性。

依赖于undo_log 和 表2个隐藏字段 DB_TRX_ID DB_RLL_PTR 和 ReadView

1.在undo_log日志表中记录之前的数据

2.修改数据，DB_TRX_ID + 1

3.DB_ROLL_PTR执行数据

4.形成undo_log日志链

5.ReadView控制快照读

RC情况下，事务中每次读取都会产生ReadView
RR情况下，依据第一次的快照度ReadView

ReadView控制快照度的原理

三十八.SQL性能分析工具

1.sql的执行频率

查看服务器insert update delete select那个为主
show global status like ‘Com_______’,数据库服务器产生的

2.慢查询日志 看谁比较耗时

记录了所有查询时间超过设置了long_query_time参数的sql

使用流程
查看是否开启 : show variables like ‘slow_query_log’
在配置文件中开启
long_query_time=1
slow_query_log=1
一旦产生了超过1s的慢sql，就会记录到慢查询日志中
show variables like ‘%slow_query_log_file%’;data目录中

3.profile 详情

查看当前会话的sql的耗时详情
set global profiling = 1; 开启profile
select global @@profiling；2.查看是否开启profiling
show profiles;

全局变量的作用域

针对所有的会话有效，但是不能跨重启。如果需要跨重启，需要修改服务器的配置文件。

会话变量的作用域

仅仅针对当前的会话（连接）有效。

4.explain 解释工具

1.explain id

展示sql的执行顺序，主要针对子查询，多表关联查询
数字越大，越先执行
数字相同，上面的先执行

2.explain 查询的类型

①Simple，简单查询

②子查询

from后面的子查询，即将查询的结果看成一张表Derived（衍生表）set session optimizer_switch=‘derived_merge=off’

select及where后面的子查询SubQuery
主查询:Primary

③Union基于联合查询

3.explain : type

4.explain possible_key

该sql可以使用到的索引

5.explain key

使用的的索引
如果mysql认为走索引比全部扫描还慢，该值就会为null

6.explain rows

该sql可以要扫描的记录数

7.key_len

通过这个可以知道命中联合索引那几列

8.extra列

提供了额外的信息

三十九.索引的正确使用(索引原则)

1.最左前缀法则

组合索引要想命中，查询条件中必须包含最左边的索引列；
如果跳过中间的字段，导致部分索引失效
如果跳过中间的字段，使用><导致后面索引字段失效

2.注意索引失效的场景

①索引列上进行运算

②字符串类型不加 ’ ’

③模糊查询前面%，索引失效

④如果使用or进行查询

左边有索引，右边没有索引；索引失效
两边都有索引，不会失效

⑤数据分布影响

如果走索引比全表扫描还慢，不使用索引

例如:范围查询中查询范围小的批量数据导致索引失效;
is null & is not null
is null 查询的数据太多
is not null 查询的数据太多

3.手动sql提示索引

如果某列可以使用多个索引;

表名后
use index：建议mysql使用那个索引
ignore index：建议mysql不使用那个索引
force index: 强制mysql使用那个索引

4.前缀索引

字段类型为text类型时，如果要创建索引，导致索引列非常的大；可以取一部分前缀作为索引；大大降低索引大小。

create index index_name on 表(字段(n))

前缀长度取几

	选择度
	该列不重复的数据 占 总数据的比值
	选择度为1说明该列是唯一的,选择度越高越好

5. 单列和联合索引结构也是一种索引的使用原则

对于单列索引和联合索引来说,我们更推荐联合索引

6. 覆盖索引(指查询的时候覆盖索引,简称覆盖索引)

尽量使用覆盖索引，避免select *
查询的字段仅仅包含索引字段
二级索引中已经包含查询的所有字段，不需要回表查询

索引设计原则:

四十.Sql优化

1.插入优化

批量插入

insert into xx values()()

手动提交事务

不要插入一条提交一次

顺序插入>乱序插入

大批量数据插入

2.主键优化

顺序插入要比乱序插入速度快？

在Innodb存储引擎中，行数据是存储在聚簇索引中；

默认情况下，主键索引就是聚簇索引;
所以，行数据是在叶子节点中按照主键排序存储

InnoDB存储引擎中节点是存储在页（黄色）中，每页存储一个节点，节点大小；大小16K，叶子节点的页上存储的是行数据

顺序插入不会导致页分裂

顺序插入，一个页满了，再创建一个新页；把行数据存入新页

乱序插入导致页分裂

如果当前2个页都写满了，添加一个
创建一个新的数据页
将第一个页中数据按照%50比例进行分裂,将数据添加的数据存入新页中

删除导致页合并

当页中数据被删除了50%，Innodb存储引擎就会找相邻的页进行行数据的合并。

3.order by

using index
通过索引直接获取的数据就是有序（B+tree叶子节点就是有序）
using filesort
将满足条件的数据在缓冲区中进行排序
排序尽量优化为using index
可以在创建索引的时候指定，B+树，叶子节点排序方式

4.group by

满足最左前缀法则
建立合适的索引

5.limit

覆盖索引+子查询
1.先根据覆盖索引找id
2.再通过id查找数据
用id优化
select * from user where id>1000000 limit 100.

6.count

7.update

更新的时候，条件如果基于非索引的字段，会导致行锁变成表锁
更新的时候基于索引字段进行更新

8.优化

1.排序字段也要建立索引。如果是组合索引也要符合最左前缀法则
2.尽量使用覆盖索引
3.多字段进行排序，可以在创建索引的时候指定规则
4.如果无法避免要进行file_sort,可以适当提高sort_buffer_size

四十一.语言

数据定义语言(DDL)define
数据查询语言（DQL）query
数据操纵语言（DML) Manipulation
数据控制语言（DCL）control