mysql基础

基础使用

操作数据库

创建数据库

1	CREATE DATABASE [IF NOT EXISTS] 数据库名;

删除数据库

1	DROP DATABASE [IF EXIST] 数据库名字;

使用数据库
1
use 数据库名;
查看数据库
1
show 数据库名；

数据库的列类型

数值：

数据类型	描述	大小
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数，一般用于金融计算

字符串

数据类型	描述	大小
char	字符串固定大小	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8-1
text	文本串	2^16-1

时间日期

数据类型	描述	格式
date	日期格式	YYYY-MM-DD
time	时间格式	HH：mm：ss
datetime	最常用的时间格式	YYYY-MM-DD HH：mm：ss
timestamp	时间戳，1970.1.1到现在的毫秒数
year	年份表示

数据库的字段属性

UnSigned
1. 无符号的
2. 声明了该列不能为负数
ZEROFILL
1. 0填充的
2. 不足位数的用0来填充
Auto_InCrement
1. 通常理解为自增，自动在上一条记录的基础上默认＋1
2. 通常用来设计唯一的主键，必须是整数类型
NULL和NOT NULL
1. 默认为NULL，即没有插入是该列的值
2. 若设置为NOT NULL，则该列必须有值
DEFAULT
1. 用于设置默认值

拓展：每一个表，都最好存在以下五个字段：

名称	描述
id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

创建字符串库表

example:

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意点：

表名和字段尽量使用``括起来
AUTO_INCREMENT 代表自增
所有的语句后面加逗号，最后一个不加
字符串使用单引号括起来
主键的声明一般放在最后，便于查看
不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

修改数据库

修改表名 :

1	ALTER TABLE 旧表名 RENAME AS 新表名;

添加字段 :

1	ALTER TABLE 表名 ADD字段名列属性[属性];

修改字段 :

1	ALTER TABLE 表名 MODIFY 字段名列类型[属性];

1	ALTER TABLE 表名 CHANGE 旧字段名新字段名列属性[属性];

删除字段 :

1	ALTER TABLE 表名 DROP 字段名;

DML操作语言

Data Manipulation Language：数据操作语言

添加 insert

语法：

1	INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

修改 update

语法：

1	UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

删除 delete

语法：

1	DELETE FROM 表名 [WHERE 条件];

DQL 查询语言

Data Query Language 数据查询语言

基本查询

SELECT 列1, 列2, 列3 FROM <表名>
wHERE <条件表达式>
ORDER BY <列名> (DESC)
LIMIT 3 OFFSET 0;         #从第0个开始，查询3个

聚合查询

example:

1	SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

结果：

class_id	num
1	4
2	3
3	3

聚合函数：

函数	说明
COUNT	计算所有列的行数
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

连接查询

内连接：

example:

-- 选出所有学生，同时返回班级名称:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

注意INNER JOIN查询的写法是：

先确定主表，仍然使用FROM <表1>的语法；
再确定需要连接的表，使用INNER JOIN <表2>的语法；
然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
可选：加上WHERE子句、ORDER BY等子句。

外连接：

-- 使用OUTER JOIN:
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：

INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

优化

mysql中，如何定位慢查询

我们当时在做压力测试时发现有些接口响应时间非常慢，超过了2秒。因为我们的系统部署了运维监控系统Skywalking，在它的报表展示中可以看到哪个接口慢，并且能分析出接口中哪部分耗时较多，包括具体的SQL执行时间，这样就能定位到出现问题的SQL。

如果没有这种监控系统，MySQL本身也提供了慢查询日志功能。可以在MySQLd的系统配置文件中开启慢查询日志，并设置SQL执行时间超过多少就记录到日志文件，比如我们之前项目设置的是2秒，超过这个时间的SQL就会记录在日志文件中，我们就可以在那里找到执行慢的SQL。

如果一条sql执行很慢，如何分析

我们通常会使用MySQL的EXPLAIN命令来分析这条SQL的执行情况。通过key和key_len可以检查是否命中了索引，如果已经添加了索引，也可以判断索引是否有效。通过type字段可以查看SQL是否有优化空间，比如是否存在全索引扫描或全表扫描。通过extra建议可以判断是否出现回表情况，如果出现，可以尝试添加索引或修改返回字段来优化。

1）没有索引或者索引失效

2）单表数据量过多，导致查询瓶颈

3）网络原因或者机器负载过高

4）热点数据导致单点负载不均衡

什么是索引

索引在项目中非常常见，它是一种帮助MySQL高效获取数据的数据结构，主要用来提高数据检索效率，降低数据库的I/O成本。同时，索引列可以对数据进行排序，降低数据排序的成本，也能减少CPU的消耗。

索引的底层数据结构是什么

MySQL的默认存储引擎InnoDB使用的是B+树作为索引的存储结构。选择B+树的原因包括：节点可以有更多子节点，路径更短；磁盘读写代价更低，非叶子节点只存储键值和指针，叶子节点存储数据；B+树适合范围查询和扫描，因为叶子节点形成了一个双向链表。

B树和B+树的区别是什么

B树的非叶子节点和叶子节点都存放数据，而B+树的所有数据只出现在叶子节点，这使得B+树在查询时效率更稳定。
B+树在进行范围查询时效率更高，因为所有数据都在叶子节点，并且叶子节点之间形成了双向链表。

什么是聚簇索引什么是非聚簇索引

聚簇索引是指数据与索引放在一起，B+树的叶子节点保存了整行数据，通常只有一个聚簇索引，一般是由主键构成。

非聚簇索引则是数据与索引分开存储，B+树的叶子节点保存的是主键值，可以有多个非聚簇索引，通常我们自定义的索引都是非聚簇索引。

什么是回表查询

回表查询是指通过二级索引找到对应的主键值，然后再通过主键值查询聚簇索引中对应的整行数据的过程

什么叫覆盖索引

覆盖索引是指在SELECT查询中，返回的列全部能在索引中找到，避免了回表查询，提高了性能。使用覆盖索引可以减少对主键索引的查询次数，提高查询效率。

MySQL超大分页怎么处理

超大分页通常发生在数据量大的情况下，使用LIMIT分页查询且需要排序时效率较低。可以通过覆盖索引和子查询来解决。首先查询数据的ID字段进行分页，然后根据ID列表用子查询来过滤只查询这些ID的数据，因为查询ID时使用的是覆盖索引，所以效率可以提升。

索引创建原则有哪些

创建索引的原则包括：

表中的数据量超过10万以上时考虑创建索引。
选择查询频繁的字段作为索引，如查询条件、排序字段或分组字段。
尽量使用复合索引，覆盖SQL的返回值。
如果字段区分度不高，可以将其放在组合索引的后面。
对于内容较长的字段，考虑使用前缀索引。
控制索引数量，因为索引虽然可以提高查询速度，但也会影响插入、更新的速度。

什么情况下索引会失效

索引可能在以下情况下失效：

没有遵循最左匹配原则。
使用了模糊查询且%号在前面。
在索引字段上进行了运算或类型转换。
使用了复合索引但在中间使用了范围查询，导致右边的条件索引失效。

SQL的优化经验有哪些

建表时选择合适的字段类型，比如数值类型选择TINYINT、INT、BIGINT等，字符串类型选择CHAR、VARCHAR或TEXT。
使用索引，遵循创建索引的原则。确保索引字段是查询频繁的，使用复合索引覆盖SQL返回值，避免在索引字段上进行运算或类型转换，以及控制索引数量。
编写高效的SQL语句，包括指明字段名称而不是使用SELECT *，避免造成索引失效的写法，聚合查询时使用UNION ALL代替UNION，表关联时优先使用INNER JOIN，以及在必须使用LEFT JOIN或RIGHT JOIN时，确保小表作为驱动表。
采用主从复制和读写分离提高性能。
在数据量大时考虑分库分表。

数据库连接池有什么用，它有哪些关键参数

数据库连接池是一种池化技术，其核心思想是实现资源的复用，避免资源重复创建和销毁的开销。在数据库的应用场景中，应用程序每次向数据库发起CRUD操作的时候，都需要创建连接，而在数据库访问较大的情况下，频繁地创建连接造成极大的系统开销，而连接池的核心思想就是在应用程序启动的时候提前初始化一部分连接到连接池里面，当需要连接的时候，直接从连接池中获取一个已经建立好的连接。

连接池的关键参数：

1）初始化连接数，表示启动的时候初始化多少连接并保存到连接池里面

2）最大连接数，表示同时最多能支持多少连接

3）最大空闲连接数，表示没有请求的时候，连接池中要保留的最大空闲连接数

4）最小空闲连接数，当连接数小于这个值的时候，连接池需要再创建连接来达到这个值

事务

事务的特性是什么

事务的特性是ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。例如，A向B转账500元，这个操作要么都成功，要么都失败，体现了原子性。转账过程中数据要保持一致，A扣除了500元，B必须增加500元。隔离性体现在A向B转账时，不受其他事务干扰。持久性体现在事务提交后，数据要被持久化存储。

并发事务带来哪些问题

并发事务可能导致脏读、不可重复读和幻读。脏读是指一个事务读到了另一个事务未提交的“脏数据”。不可重复读是指在一个事务内多次读取同一数据，由于其他事务的修改导致数据不一致。幻读是指一个事务读取到了其他事务插入的“幻行”。

怎么解决这些问题呢？MySQL的默认隔离级别是？

解决这些问题的方法是使用事务隔离。MySQL支持四种隔离级别：

未提交读（READ UNCOMMITTED）：解决不了所有问题。
读已提交（READ COMMITTED）：能解决脏读，但不能解决不可重复读和幻读。
可重复读（REPEATABLE READ）：能解决脏读和不可重复读，但不能解决幻读，这也是MySQL的默认隔离级别。
串行化（SERIALIZABLE）：可以解决所有问题，但性能较低。

Mysql事务的实现原理

Mysql中的事务满足ACID特性，所以mysql的事务实现原理就是InnoDB是如何保证ACID特性：

原子性：失败就意味着要对原本成功的数据进行回滚，所以InnoDB设计了一个UNDO_LOG表，在事务执行的过程中，把修改之前的数据快照保存到undo_log里面，一旦出现错误，就直接从UNDO_LOG中读取数据进行反向操作。
一致性：表示数据的完整性约束没有被破坏，这个更多是依赖于业务层面的保证，数据库本身提供的约束有主键的唯一余数，字段长度和类型的保证等。
隔离性：也就是多个并行事务对同一个数据进行操作的时候，如何避免多个事务的干扰造成事务混乱的问题。而InnoDB实现了SQL92的标准，提供了4种隔离级别的实现。
持久性：也就是只要事务提交成功，那么对于这个数据的结果的影响一定是持久的，不能因为宕机或者其他原因导致数据变更失效。

理论上来说，只要事务提交之后直接把数据持久化到硬盘即可，但是因为随机硬盘I/O的效率确实很低，所以InnoDB设计了Buffer Pool缓存区来优化，也就是数据发生变更的时候先更新内存缓存区，然后在合适的实际再持久化到硬盘。在持久化的过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性。所以InnoDB引入了redo_log文件，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作的时候，除了修改内存缓存区里的数据，还会把本次修改的值追加到redo_log里。当提交事务的时候，直接把redo_log日志刷到磁盘上持久化，一旦数据库出现宕机，在mysql重启后可以直接将redo_log里面保存的重写日志读取出来，再执行一遍，从而保证持久性。

undo_log和redo_log的区别是什么

redo log记录的是数据页的物理变化，用于服务宕机后的恢复，保证事务的持久性。而undo log记录的是逻辑日志，用于事务回滚时恢复原始数据，保证事务的原子性和一致性。

事务中的隔离性是如何保证的呢？（解释一下MVCC）

事务的隔离性通过锁和多版本并发控制（MVCC）来保证。MVCC通过维护数据的多个版本来避免读写冲突。底层实现包括隐藏字段、undo log和read view。隐藏字段包括trx_id和roll_pointer。undo log记录了不同版本的数据，通过roll_pointer形成版本链。read view定义了不同隔离级别下的快照读，决定了事务访问哪个版本的数据。

MySQL主从同步原理是什么？

MySQL主从复制的核心是二进制日志（Binlog）。步骤如下：

主库在事务提交时记录数据变更到Binlog。
从库读取主库的Binlog并写入中继日志（Relay Log）。
从库重做中继日志中的事件，反映到自己的数据中。

杂

Mysql中的数据排序是怎么实现的

排序过程中，如果排序字段命中索引，则利用索引排序

反之，使用文件排序

文件排序中，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序

如果数据大则利用磁盘文件进行外部排序，一般使用归并排序

拓展：

双路排序

有一个叫max_length_for_sort_data参数，默认是4096字节，如果select列的数据长度超过它，则采用row_id排序，即把row_id（有主键就是主键）+排序字段放置到sort_buffer中排序。

比如现在要执行：
1
select a,b,c from t1 where a = "面试" order by b
假设select列的数据长度超过了max_length_for_sort_data，为了节省排序占用的空间，此时sort_buffer只会放置id和b来排序。

排序后，再通过id回表查询得到a、b、c，最终将最后的结果集返回给客户端

所以排序需要多个回表的过程，等于需要两次查询，也叫双路排序
单路排序

假设select列的数据没有超过max_length_for_sort_data，则可以进行单路排序，就是将select的字段都放置到sort_buffer中。

排序后直接得到结果集，返回给客户端即可，相比双路排序它减少了回表的动作，因此效率更高

MySQL的Change Buffer是什么？它有什么作用？

Change BUffer是MySQL InnoDB存储引擎中的一个机制，用于暂存堆二级索引的插入和更新操作的变更，而不立即执行这些操作，随后，当InnoDB进行合适的条件时（如页被读取或Flush操作），会将这些变更写入到二级索引中。

作用：

提高写入性能：通过将对二级索引的变更暂存，可以减少对磁盘的频繁写入，提高插入和更新操作的性能。
批量处理：Change BUffer 可以在后续的操作中批量处理这些变更，减少了随机写入的开销。

详细描述一条SQL语句在MySQL中的执行过程

先通过连接器校验权限
利用分析器进行SQL语句的此法分析和语法分析，构建解析树
使用优化器选择合适的索引和表连接顺序，最终选择一个最佳的执行计划
利用执行器，调用引擎层查询数据

详细：

我们以一条SQL为例: select * from user where id=1;

在这条查询中，大体可以分为两个层面，Server 层和存储引擎层

Server 层涉及到一系列的业务的组件，而存储引擎层则是负责存储发送过来的数据、提供读写接口等功能。（MySQL中的存储引擎是插件式的，一个数据库里面的不同表可以使用不同的数据引擎:InnoDB、MyISAM等）

具体流程如下：

当客户端的SQL发送到MYSQL时，首先到达的是Server层的连接器，连接器会对你此次发送的请求进行权限校验，以此来获取你这个账号的权限。当你的账号或密码不正确时，会报Access denied for user错误。连接成果后如果没有任何后续操作。到达一定实践后它便会断开来你接，这个时间一般是8小时，是由wait_timeout参数控制的。
分析器进行语法分析、词法分析：判断操作语句是什么类型的，提取要查询的表名id等参数，然后判断语句的语法是否有误
优化器：会对语句进行优化判断，比如若表中由多个索引，优化器会选择使用哪个索引；若使用了join多表连接，优化器会帮你调整表的连接顺序。explain返回的其实就是MySQL的优化决定策略。
执行器：根据存储引擎提供的接口进行数据查询。

MySQL的存储引擎有哪些？他们有什么区别

主要存储引擎：

InnoDB

支持事务、行级锁和外键

提供高并发，适用于高负载的应用

数据以聚簇索引的方式存储，提高检索效率
MyISAM：

不支持事务和外键，使用表级锁

适合读取多、更新少的场景

具有较高的读取性能和较快的表级锁定

MySQL的索引类型有哪些

从数据结构的角度看：

B+树索引
哈希索引
倒排索引（即全文索引Full-Text）

从常见的基于InnoDB B+树索引角度来看，可以分为:

聚簇索引
非聚簇索引

从索引性质的角度：

普通索引（二级索引）
主键索引
联合索引
唯一索引
全文索引
空间索引

Mybatis

Mybatis执行流程？

MyBatis的执行流程如下：

读取MyBatis配置文件mybatis-config.xml。
构造会话工厂SqlSessionFactory。
会话工厂创建SqlSession对象。
操作数据库的接口，Executor执行器。
Executor执行方法中的MappedStatement参数。
输入参数映射。
输出结果映射。

MyBatis 的执行原理基于其核心设计思想:通过映射文件(XML 或注解)将 SQL 语句与 Java 对象进行绑定整个执行流程可以分为以下几个步骤:

SalSessionFactory 的创建:MvBatis 的执行过程从 sqlsessionfactory开始。sqlsesionfactory是一个工厂类，负责创建sqsession实例，sglsesion是 MyBatis 与数据库交互的核心对象。 sqlsesionfactory 是通过 sqlsesionfactorypuilder 构建的(通常是通过读取 MyBatis 配置文件 mybatis-config.xml来初始化)。
SqlSession 的获取: sqlsesionFactory 通过 opensesion()方法获取一个 sqlsesion 对象，sqlsession 是操作数据库的主要入口，用户通过它执行SQL 语句、提交事务等。
执行映射语句:当调用 sqlsesion 的方法(例如 selectone、selectlist、insert、update、delete 等)时,MyBatis 会根据传入的 SQL 映射语句的ID，去寻找对应的 SQL 语句执行。
命名空间和映射语句的查找:SQL 映射语句通过映射文件中的 namespace 和 id 进行定位。MyBatis 会将映射文件解析成一个 mappedstatement 对象Mappedstatement 保存了 SQL 语句、参数类型、返回类型等信息,
参数封装和 SQL 语句执行:在 SQL 执行前，MyBatis 会根据映射文件中配置的 parameterype 类型，将传入的参数封装成适当的对象(例如，使用JavaBean、Map、XML格式等)。然后、MBatis 会根据不同的执行环境(如 MSQL、Oracle 等数据库)，将 SQL 语句执行到数据库中，并将查询结果通过映射文件中配置的 resultType 类型返回。
返回结果的映射:MyBatis 会将查询结果根据 resultType或 resultap进行转换，将查询结果转换成 Java 对象(如 List 、ap 或指定的 POJO 类)
事务管理:MBatis 的事务管理通过 sqisession 来处理，sqlsession 提供了事务的提交和回滚方法。当调用 sqlsession.comit()时，SQL 执行的结果会被提交到数据库;若发生异常，调用SqlSession.rollback()，事务会被回滚。
最后关闭 SqlSession:在操作完成后，sqlsession 会通过 close()方法关闭，释放数据库连接和资源。