Java Web 学习笔记（一） —— MySQL（3）

最新推荐文章于 2024-09-13 22:15:00 发布

夏木夕

最新推荐文章于 2024-09-13 22:15:00 发布

阅读量483

点赞数 3

分类专栏： Java Web Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/hu_wei123/article/details/138588190

版权

Java Web 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

Mysql

4 篇文章 0 订阅

订阅专栏

1 Mysql 函数

1.1 日期函数

在这里插入图片描述

1.2 判断函数

case when 语句，用于计算条件列表并返回多个可能结果表达式之一。（类似于java中的switch多分支语句）

CASE 具有两种格式：

简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
CASE 搜索函数计算一组布尔表达式以确定结果。

格式一：简单Case函数

CASE 表达式1 
         WHEN 表达式2 THEN 表达式3 
         WHEN 表达式4 THEN 表达式5
         ......
ELSE 表达式6 
END 

举例：
CASE sex 
         WHEN '1' THEN '男' 
         WHEN '2' THEN '女' 
ELSE '其他' 
END

格式二：CASE 搜索函数

CASE 
         WHEN 条件表达式1 THEN 表达式2 
         WHEN 条件表达式3 THEN 表达式4
         ......
ELSE 表达式5 
END 

举例：
CASE 
	WHEN sex = '1' THEN '男' 
	WHEN sex = '2' THEN '女' 
ELSE '其他'
END

注意：如果上述两种格式都不指定ELSE,并且都不满足条件则返回 null。

case…when可以和select语句一起使用

-- 简单Case函数 
select 字段1,字段2,
		CASE 字段3
		WHEN 值1 THEN 返回新值
		WHEN 值1 THEN 返回新值
		.....
		ELSE '其他'
		END as 给字段3重新命名即别名
from 表名
where ....
group by ...
order by...
limit ...

-- CASE 搜索函数
select 字段1,字段2,
		CASE
		WHEN 条件表达式 THEN 返回新值
		WHEN 条件表达式 THEN 返回新值
		.....
		ELSE '其他'
		END as 给要查询的字段重新命名即别名
from 表名
where ....
group by ...
order by...
limit ...

1.3 字符函数

在这里插入图片描述

1.4 数学函数

在这里插入图片描述

2 Mysql 性能

2.1 提高操作数据库性能

我们进入公司进行项目开发往往关注的是业务需求和功能的实现，但是随着项目运行的时间增加，数据量也就增加了，这时会影响到我们数据库的查询性能。所以我们要提高操作数据库的性能，有如下两种方式：

硬优化：就是软优化之后性能还很低，只能采取硬优化，最后的步骤了，就是公司花钱购买服务器。在硬件上进行优化。我们在这里不关注。我们关注的软优化。
软优化: 在操作和设计数据库方面上进行优化（表结构和 sql 语句），例如下面讲解的索引。

2.2 执行次数比较多的语句

执行次数比较多的语句分类

查询密集型（DQL）
修改密集型（DML）

查询累计插入和返回数据条数，即查看当前数据库属于查询密集型还是修改密集型。

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';

说明：Innodb数据一种存储引擎。后续会讲解。

在这里插入图片描述

在开发中，一般查询比较多。

2.3 sql语句的执行效率

我们想学习mysql的性能问题，这里需要准备千万条数据，这样才可以看出效果。

插入千万条记录

-- 1. 准备表
CREATE TABLE user(
	id INT,
	username VARCHAR(32),
	password VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法，插入千万条数据之后，在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO user VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
	COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程
CALL auto_insert();

在这里插入图片描述

说明：由于每个人的电脑配置不一样，所以插入千万条数据的时间也是不一样的，有的人是2分钟，有的人十几分钟或者半个小时。

需求：查询id是22的用户。

在这里插入图片描述

时间太长了。那么我们需要使用接下来讲解的索引进行优化。

3 Mysql 优化（***）

3.1 定位慢查询

在这里插入图片描述

如何定位慢查询？

在这里插入图片描述

面试官：MySQL中，如何定位慢查询?
候选人：嗯~，我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题。如果，项目中没有这种运维的监控系统，其实在MySQL中也提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中，我记得上一个项目配置的是2秒，只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了。

在这里插入图片描述

3.2 SQL执行计划

接着上个问题，定位了慢查询之后，那这个SQL语句执行的很慢，如何分析呢？

在这里插入图片描述

可以采用 EXPLAIN 或者 DESC 命令获取 Mysql 如何执行 SELECT 语句的信息

在这里插入图片描述

面试官：那这个SQL语句执行很慢, 如何分析呢？
候选人：如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

在这里插入图片描述

3.3 索引

3.3.1 索引介绍与分类

在这里插入图片描述

面试官：了解过索引吗？（什么是索引）

候选人：嗯，索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

索引分类

主键索引：设置主键后数据库默认建立索引，一个表只能有一个主键索引且不能为空。（主键约束+提高查询效率）
唯一索引：索引列的值必须唯一，但允许有空值，且只允许有一个空值。（唯一约束+提高查询效率）
普通索引：一个索引只包含单个列，一个表可以有多个普通索引（仅提高查询效率）
组合（联合）索引：多个字段组成索引。（联合主键索引、联合唯一索引、联合普通索引）
全文索引：主要用于查找文本中的关键字。Mysql全文索引使用较少，基本针对文档类数据会选择 solr 、es 等文档搜索类数据库。
hash索引：根据key-value等值查询效率非常高，但是不适合范围查询

我们创建表时就会指定主键和唯一约束，那么就相当于给表的字段添加了主键和唯一索引。

3.3.2 索引的使用

（1）创建索引

索引是创建在表中的某（些）列上的。

① 在已有表的字段上直接创建【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

注意：

如果在同一张表中创建多个索引，要保证索引名是不能重复的
上述创建索引的方式比较麻烦，还需要指定索引名
采用上述方式不能添加主键索引（因为主键索引默认在创建表时指定主键列就自动添加了）

② 在已有表的字段上修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

③ 创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

（2）查看索引

show index from 表名;

查看 student 表的索引信息

show index from student;

（3）删除索引

直接删除

-- 直接删除
drop index 索引名 on 表名;

修改表时删除

-- 修改表时删除
alter table 表名 drop index 索引名;

3.3.3 索引创建原则

在这里插入图片描述

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项。性别的识别度是50%。
经常使用where条件搜索的字段，例如user表的id name等字段。
经常使用表连接的字段（内连接、外连接），可以加快连接的速度。
经常排序的字段 order by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。

注意：那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的。创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。

3.3.4 索引的优缺点

优势

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。IO次数越多，效率越低。
索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间。
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

3.3.5 索引的数据结构

在这里插入图片描述

索引的本质：索引就是数据结构（B +Tree）

Mysql 索引的数据结构是 B+ 树， B+ 树结构的特点：划分了叶子节点和非叶子节点。

非叶子节点：只存储索引和指针，不存储数据，这样节省了内存，可以存储更多个非叶子节点（索引+指针），降低了树的高度，也意味着减少了数据查询所需的 io 次数，效率随之提高。千万条数据，B+Tree可以控制在小于等于3的高度。
叶子节点：存储索引、数据（占用存储空间最大），按照索引排好序，更好的支持范围查找，速度会很快。

为了提高数据库的区间访问能力，针对叶子结点维护了一个类似双向链表的结构，避免数据反复从根节点搜索带来的性能开销。

还有一点是 mysql 将根节点都加载到内存中，每张表有一个根节点，大小是16KB。那么这样的好处，如果是千万条数据，那么只有2次磁盘IO。这就是为什么我们加完索引之后瞬间查到数据的原因了。

面试官：索引的底层数据结构了解过嘛 ?
候选人：MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引，选择B+树的主要的原因是：第一阶数更多，路径更短，第二个磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，第三是B+树便于扫库和区间查询，叶子节点是一个双向链表
面试官：B树和B+树的区别是什么呢？
候选人：第一：在B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有的数据都会出现在叶子节点，在查询的时候，B+树查找效率更加稳定。
第二：在进行范围查询的时候，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表。

3.3.6 查询时索引的底层机制

只有书写在 where 条件中的字段才会激活索引，在进行 select 查询时，执行where条件时，会先检查字段上是否有建立索引

未建立有索引：直接去表中逐行查询
建立有索引：去B+树种查找

索引可以提高查询效率，但是会降低增删改的效率。

3.3.7 聚簇索引和非聚簇索引

在这里插入图片描述

面试官：什么是聚簇索引什么是非聚簇索引 ?
候选人：好的~，聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般情况下主键在作为聚簇索引的。非聚簇索引值的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引

面试官：知道什么是回表查询嘛 ?
候选人：嗯，其实跟刚才介绍的聚簇索引和非聚簇索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表【备注：如果面试官直接问回表，则需要先介绍聚簇索引和非聚簇索引】

3.3.7 覆盖索引

在这里插入图片描述

面试官：知道什么叫覆盖索引嘛 ?
候选人：嗯~，清楚的
覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用 select *，尽量在返回的列中都包含添加索引的字段。
面试官：MYSQL超大分页怎么处理 ?
候选人：嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了>因为查询id的时候，走的覆盖索引，所以效率可以提升很多

3.3.9 什么情况下索引失效

在这里插入图片描述

面试官：什么情况下索引会失效 ?
候选人：嗯，这个情况比较多，我说一些自己的经验，以前遇到过的。比如，索引在使用的时候没有遵循最左匹配法则，第二个是，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。我们之前还遇到过一个就是，如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析