当前位置：首页 > news >正文

探索关系型数据库 MySQL

news 2025/4/26 16:23:49

引言

在当今数字化时代，数据是企业和组织的核心资产之一。如何高效地存储、管理和查询数据成为了至关重要的问题。关系型数据库作为一种广泛应用的数据管理解决方案，凭借其结构化的数据存储方式、强大的查询功能和数据完整性保证，在众多领域发挥着关键作用。MySQL 作为开源关系型数据库的代表，以其高性能、可靠性和易用性，深受开发者和企业的喜爱。本文将深入探讨 MySQL 的基本概念、安装配置、常用操作以及优化技巧，帮助读者全面了解和掌握这一强大的数据库工具

一.SQL的基本操作

1.数据库是什么? 什么是SQL？

按照数据结构来组织、存储和管理数据的仓库 ；是一个长期存

储在计算机内的、有组织的、可共享的、统一管理的大量数据

的集合；

1.1.OLTP

OLTP （ On-Line transaction processing ）翻译为联机事务处理；

主要对数据库增删改查；

OLTP 主要用来记录某类业务事件的发生；数据会以增删改的方

式在数据库中进行数据的更新处理操作，要求实时性高、稳定

性强、确保数据及时更新成功；

1.2.OLAP

OLAP （ On-Line Analytical Processing ）翻译为联机分析处理；

主要对数据库查询；

当数据积累到一定的程度，我们需要对过去发生的事情做一个

总结分析时，就需要把过去一段时间内产生的数据拿出来进行

统计分析，从中获取我们想要的信息，为公司做决策提供支

持，这时候就是在做 OLAP 了；

1.3.SQL

结构化查询语言 ( Structured Query Language ) 简称 SQL ，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统 。

SQL 是关系数据库系统的标准语言。

关系型数据库包括： MySQL , SQL Server , Oracle , Sybase , postgreSQL 以及 MS Acces 等；

SQL 命令包括： DQL 、 DML 、 DDL 、 DCL 以及 TCL ；

1.4.DQL

Data Query Language - 数据查询语言；

select ：从一个或者多个表中检索特定的记录；

1.5.DML

Data Manipulate Language - 数据操作语言；

insert ：插入记录；

update ：更新记录；

delete ：删除记录；

1.6.DDL

Data Define Languge - 数据定义语言；

create ：创建一个新的表、表的视图、或者在数据库中的对象；

alter ：修改现有的数据库对象，例如修改表的属性或者字段；

drop ：删除表、数据库对象或者视图；

1.7.DCL

Data Control Language - 数据控制语言；

grant ：授予用户权限；

revoke ：收回用户权限；

1.8.TCL

Transaction Control Language - 事务控制语言；

commit ：事务提交；

rollback ：事务回滚；

1.9.数据库术语

数据库：数据库是一些关联表的集合；

数据表：表是数据的矩阵；

列：一列包含相同类型的数据；

行：或者称为记录是一组相关的数据；

主键： 主键是唯一的；一个数据表只能包含一个主键 ；

外键：外键用来关联两个表，来保证参照完整性； MyISAM 存储

引擎本身并不支持外键，只起到注释作用；而 innoDB 完整支持外键；

复合键：或称组合键；将多个列作为一个索引键；

索引：用于快速访问数据表的数据；索引是对表中的一列或者

多列的值进行排序的一种结构；

2.MySQL体系结构

MySQL 由以下几部分组成：

连接池组件

管理服务和工具组件、 SQL 接口组件、查询分析器组件、优化器组件、缓冲组件、

插件式存储引擎、

物理文件。

2.1.连接者

不同语言的代码程序和 MySQL 的交互（ SQL 交互）；

2.2.MySQL 内部连接池

管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求；

网络处理流程： 主线程接收连接，接收连接交由连接池处理；

主要处理方式： IO多路复用 select + 阻塞的 io ；

需要理解： MySQL 命令处理是多线程并发处理的；

主线程负责接收客户端连接，然后为每个客户端 fd 分配一个连

接线程，负责处理该客户端的 sql 命令处理；

2.3.管理服务和工具组件

系统管理和控制工具，例如备份恢复、 MySQL 复制、集群等；

2.4.SQL接口

将 SQL 语句解析生成相应对象； DML ， DDL ，存储过程，视图，触发器等；

2.5.查询解析器

将 SQL 对象交由解析器验证和解析，并生成语法树；

2.6.查询优化器

SQL 语句执行前使用查询优化器进行优化；

2.7.缓冲组件

是一块内存区域，用来弥补磁盘速度较慢对数据库性能的影

响；在数据库进行读取页操作，首先将从磁盘读到的页存放在

缓冲池中，下一次再读相同的页时，首先判断该页是否在缓冲

池中，若在缓冲池命中，直接读取；否则读取磁盘中的页，说

明该页被 LRU 淘汰了；缓冲池中 LRU 采用最近最少使用算法来

进行管理；

缓冲池缓存的数据类型有：索引页、数据页、以及与存储引擎

缓存相关的数据（比如 innoDB 引擎： undo 页、插入缓冲、自

适应 hash 索引、 innoDB 相关锁信息、数据字典信息等）；

2.8.一条select查询语句的执行流程

①客户端向mysql服务器发起连接通过，mysql服务器接收连接并且分配到连接池中对应的连接线程中，并且校验用户的信息。

②查询缓存，如果缓存中有对应的数据则直接返回，在mysql8.0版本中该功能已被删除，毕竟有又快性能又高的redis

③分析器,对输入的 SELECT 语句进行词法和语法分析，将语句拆解成一个个单词（词法分析），并根据 MySQL 的语法规则检查语句是否合法，最终生成语法树，以此理解语句的结构和意图

④优化器，对于涉及多表连接的查询，确定表的连接顺序、选择合适的索引等，制定出执行成本最小的执行计划，以提升查询执行效率 。

⑤执行器,根据执行任务，从存储引擎中获取数据，并返回给客户端

3.数据库设计三范式

为了 建立冗余较小、结构合理的数据库 ，设计数据库时必须遵

循一定的规则。在关系型数据库中这种规则就称为范式。范式

是符合某一种设计要求的总结。要想设计一个结构合理的关系

型数据库，必须满足一定的范式。

3.1.范式一

确保每列保持原子性；数据库表中的所有字段都是不可分解的

原子值；

例如：某表中有一个地址字段，如果经常需要访问地址字段中

的城市属性，则需要将该字段拆分为多个字段，省份、城市、

详细地址等；

3.2.范式二

确保表中的每列都和主键相关，而不能只与主键的某一部分相

关（组合索引）；

3.3.范式三

确保每列都和主键直接相关，而不是间接相关；减少数据冗

余；

3.4.反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完

整性的麻烦；但是采用数据库范式化设计，可能导致数据库业

务涉及的表变多，并且造成更多的联表查询，将导致整个系统

的性能降低；因此基于性能考虑，可能需要进行反范式设计；

4.CRUD

4.1.执行过程

4.2创建数据库

CREATE DATABASE `数据库名` DEFAULT CHARACTER SET
utf8;

4.3删除数据库

CREATE DATABASE `数据库名` DEFAULT CHARACTER SET
utf8;

4.4删除数据库

DROP DATABASE `数据库名`;

4.5选择数据库

USE `数据库名`;

4.6创建表

CREATE TABLE `table_name` (column_name
column_type);
CREATE TABLE IF NOT EXISTS `0voice_tbl` (`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编
号',`course` VARCHAR(100) NOT NULL COMMENT '课程',`teacher` VARCHAR(40) NOT NULL COMMENT '讲师',`price` DECIMAL(8,2) NOT NULL COMMENT '价格',PRIMARY KEY ( `id` )
)ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课
程表';

4.7删除表

DROP TABLE `table_name`;

4.8清空数据表

TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至
少有两行数据），有自增索引的话，从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的
话，从之前值继续累加

4.9增

INSERT INTO `table_name`(`field1`, `field2`, ...,
`fieldn`) VALUES (value1, value2, ..., valuen);
INSERT INTO `0voice_tbl` (`course`, `teacher`,
`price`) VALUES ('C/C++Linux服务器开发/高级架构师',
'Mark', 7580.0);

4.10删

DELETE FROM `table_name` [WHERE Clause];
DELETE FROM `0voice_tbl` WHERE id = 3;

4.11改

UPDATE table_name SET field1=new_value1,
field2=new_value2 [, fieldn=new_valuen]
UPDATE `0voice_tbl` SET `teacher` = 'Mark' WHERE
id = 2;
-- 累加
UPDATE `0Voice_tbl` set `age` = `age` + 1 WHERE id
= 2;

4.12查

SELECT field1, field2,...fieldN FROM table_name
[WHERE Clause]

4.13 大约束

not null 非空约束：限定字段必须有值，不允许为空值，确保数据完整性，避免关键信息缺失。

auto_increment 自增约束：用于数值型字段，值按顺序自动递增，常作主键生成唯一标识，无需手动赋值。

unique 唯一约束：保证字段值在表内唯一，可出现一个空值（若字段允许空值），防止重复数据录入。

primary 主键约束：唯一标识表中记录，兼具非空和唯一性，一个表仅有一个主键，是数据定位和关联基础。

foreign 外键约束：构建表间关联，保证参照完整性，外键值需来自关联表主键或唯一键，维护数据逻辑关系。

4.14删除数据注意事项

drop（DDL）

快，删整表（结构、数据、索引等），不能回滚，释空间。

truncate（DDL）

较快，删表数据，留结构（auto_increment 置 1 ），不能回滚，以页删，释空间。

delete（DML）

慢，可部分或全部删数据，留结构，能回滚，逐行删，可条件删。

4.15去重

group by column

按指定列分组，每组留一条记录实现去重，常配合聚合函数，用于分组统计场景。

select distinct column

直接扫描指定列，去除重复值来实现去重，单纯获取列的唯一值

5.高级查询

5.1基础查询

-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `sname`, `class_id` FROM student;
-- 别名 列明 不要用关键字
SELECT `sname` AS '姓名' , `class_id` AS '班级ID'
FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `class_id` FROM student;

5.2条件查询

-- 查询姓名为 邓洋洋 的学生信息
SELECT * FROM `student` WHERE `name` = '邓洋洋';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND
`class_id`=2;

5.3范围查询

-- 查询班级id 1 到 3 的学生的信息
SELECT * FROM `student` WHERE `class_id` BETWEEN 1
AND 3;

5.4判空查询

# is null 判断造成索引失效
# 索引 B+ 树
SELECT * FROM `student` WHERE `class_id` IS NOT
NULL; #判断不为空
SELECT * FROM `student` WHERE `class_id` IS NULL;
#判断为空
SELECT * FROM `student` WHERE `gender` <> ''; 
#判断不为空字符串
SELECT * FROM `student` WHERE `gender` = ''; 
#判断为空字符串

5.5模糊查询

-- 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符
-- 查询名字为 m 开头的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '谢%';
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

5.6分页查询

-- 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询
一起使用
-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个
参数表示要显示的数目。表中默认第一条记录的参数为0。
-- 查询第二条到第三条内容
SELECT * FROM `student` LIMIT 1,2;

5.7查询后排序

-- 关键字：order by field, asc:升序, desc:降序
SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多个字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC,
`num` DESC;

5.8聚合查询

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

5.9分组查询

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM
`student` GROUP BY `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student`
GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student`
GROUP BY `gender` HAVING num > 6;

5.10分组聚合

分组的作用是去重

聚合的作用是合并重复的

举例子

-- 创建表
CREATE TABLE students (student_id INT PRIMARY KEY AUTO_INCREMENT,student_name VARCHAR(50),gender ENUM('男', '女')
);-- 插入数据
INSERT INTO students (student_name, gender)
VALUES
('张三', '男'),
('李四', '男'),
('王五', '女'),
('赵六', '女'),
('孙七', '男');SELECT gender, COUNT(student_id) AS student_count
FROM students
GROUP BY gender;

查询结果

6.联表查询

6.1 INNER JOIN

只取两张表有对应关系的记录

SELECTcid
FROM`course`
INNER JOIN `teacher` ON course.teacher_id =
teacher.tid;

6.2 LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录

SELECTcourse.cid
FROM`course`
LEFT JOIN `teacher` ON course.teacher_id =
teacher.tid;

6.3 RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录

SELECTcourse.cid
FROM`course`
RIGHT JOIN `teacher` ON course.teacher_id =
teacher.tid;

7 子查询/合并查询

7.1.单行子查询

select * from course where teacher_id = (select
tid from teacher where tname = '谢小二老师')

7.2多行子查询

多行子查询即返回多行记录的子查询

IN 关键字 ：运算符可以检测结果集中是否存在某个特定的值，

如果检测成功就执行外部的查询。

EXISTS 关键字 ：内层查询语句不返回查询的记录。而是返回一

个真假值。如果内层查询语句查询到满足条件的记录，就返回

一个真值（ true ），否则，将返回一个假值（ false ）。当返

回的值为 true 时，外层查询语句将进行查询；当返回的为

false 时，外层查询语句不进行查询或者查询不出任何记录。

ALL 关键字 ：表示满足所有条件。使用 ALL 关键字时，只有满

足内层查询语句返回的所有结果，才可以执行外层查询语句。

ANY 关键字 ：允许创建一个表达式，对子查询的返回值列表，

进行比较，只要满足内层子查询中的，任意一个比较条件，就

返回一个结果作为外层查询条件。

在 FROM 子句中使用子查询 ：子查询出现在 from 子句中，这

种情况下将子查询当做一个临时表使用。

select * from student where class_id in (select
cid from course where teacher_id = 2);
select * from student where exists(select cid
from course where cid = 5);
SELECTstudent_id,sname
FROM(SELECT * FROM score WHERE course_id = 1 OR
course_id = 2) AS ALEFT JOIN student ON A.student_id =
student.sid;

8.正则表达式

SELECT * FROM `teacher` WHERE `tname` REGEXP '^
谢';

9.视图

9.1定义

视图（ view ）是一种虚拟存在的表，是一个逻辑表，本身并不

包含数据。其内容由查询定义。

基表：用来创建视图的表叫做基表；

通过视图，可以展现基表的部分数据；

视图数据来自定义视图的查询中使用的表，使用视图动态生

成；

9.2优点

简单：使用视图的用户完全不需要关心后面对应的表的结构、

关联条件和筛选条件，对用户来说已经是过滤好的复合条件的

结果集。

安全：使用视图的用户只能访问他们被允许查询的结果集，对

表的权限管理并不能限制到某个行某个列，但是通过视图就可

以简单的实现。

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用

户的影响，源表增加列对视图没有影响；源表修改列名，则可

以通过修改视图来解决，不会造成对访问者的影响。

9.3语法

CREATE VIEW <视图名> AS <SELECT语句>

案例

CREATE VIEW view_test1 AS SELECT
A.student_id
FROM(SELECTstudent_id,numFROMscoreWHEREcourse_id = 1) AS A -- 12
LEFT JOIN (SELECTstudent_id,numFROMscoreWHEREcourse_id = 2
) AS B -- 11
ON A.student_id = B.student_id
WHEREA.num >
IF (isnull(B.num), 0, B.num);

作用：

可复用，减少重复语句书写；类似程序中函数的作用；

重构利器

逻辑更清晰，屏蔽查询细节，关注数据返回；

权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对

该表操作；

10.综合案例演示

-- 创建部门表
CREATE TABLE departments (department_id INT PRIMARY KEY AUTO_INCREMENT,department_name VARCHAR(50) NOT NULL
);-- 创建员工表
CREATE TABLE employees (employee_id INT PRIMARY KEY AUTO_INCREMENT,employee_name VARCHAR(50) NOT NULL,department_id INT,salary DECIMAL(10, 2),hire_date DATE,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);-- 创建订单表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,employee_id INT,order_date DATE,order_amount DECIMAL(10, 2),FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);-- 向部门表插入数据
INSERT INTO departments (department_name)
VALUES
('销售部'),
('研发部'),
('财务部');-- 向员工表插入数据
INSERT INTO employees (employee_name, department_id, salary, hire_date)
VALUES
('张三', 1, 5000.00, '2022-01-01'),
('李四', 1, 5500.00, '2022-02-15'),
('王五', 2, 7000.00, '2021-10-10'),
('赵六', 2, 7500.00, '2021-11-20'),
('孙七', 3, 6000.00, '2022-05-01');-- 向订单表插入数据
INSERT INTO orders (employee_id, order_date, order_amount)
VALUES
(1, '2023-03-01', 1000.00),
(1, '2023-04-10', 1500.00),
(2, '2023-05-20', 2000.00),
(3, '2023-06-15', 800.00),
(4, '2023-07-05', 1200.00);-- 1. 简单查询
-- 查询所有员工的姓名和工资
SELECT employee_name, salary FROM employees;-- 2. 条件查询
-- 查询工资大于 6000 的员工信息
SELECT * FROM employees WHERE salary > 6000;-- 查询 2022 年之后入职的销售部员工姓名
SELECT e.employee_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = '销售部' AND e.hire_date > '2022-12-31';-- 3. 联表查询
-- 内连接：查询每个员工的订单信息（包括员工姓名、订单日期和订单金额）
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id;-- 左连接：查询所有员工及其订单信息，没有订单的员工也显示
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id;-- 右连接：查询所有订单及其对应的员工信息，没有员工的订单也显示
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
RIGHT JOIN orders o ON e.employee_id = o.employee_id;-- 全连接（MySQL 没有直接的全连接关键字，通过左连接和右连接并集实现）
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id
UNION
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
RIGHT JOIN orders o ON e.employee_id = o.employee_id;-- 4. 子查询
-- 查询工资高于平均工资的员工信息
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);-- 查询在 2023 年有订单的员工所属部门名称
SELECT d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN orders o ON e.employee_id = o.employee_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';-- 5. 分组查询
-- 按部门统计员工数量
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;-- 按员工统计订单总金额，并筛选出订单总金额大于 1500 的员工
SELECT e.employee_name, SUM(o.order_amount) AS total_amount
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_name
HAVING SUM(o.order_amount) > 1500;-- 6. 排序查询
-- 按工资从高到低查询员工信息
SELECT * FROM employees ORDER BY salary DESC;-- 按入职日期升序，工资降序查询员工信息
SELECT * FROM employees ORDER BY hire_date ASC, salary DESC;-- 7. 聚合函数查询
-- 查询所有员工的平均工资
SELECT AVG(salary) AS average_salary FROM employees;-- 查询员工的最高工资和最低工资
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;

department表

employees表

order表

-- 1. 简单查询
-- 查询所有员工的姓名和工资
SELECT employee_name, salary FROM employees;

-- 2. 条件查询
-- 查询工资大于 6000 的员工信息
SELECT * FROM employees WHERE salary > 6000;

-- 查询 2022 年之后入职的销售部员工姓名
SELECT e.employee_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = '销售部' AND e.hire_date > '2022-12-31';

-- 3. 联表查询
-- 内连接：查询每个员工的订单信息（包括员工姓名、订单日期和订单金额）
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id;

-- 左连接：查询所有员工及其订单信息，没有订单的员工也显示
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id;

-- 右连接：查询所有订单及其对应的员工信息，没有员工的订单也显示
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
RIGHT JOIN orders o ON e.employee_id = o.employee_id;

-- 全连接（MySQL 没有直接的全连接关键字，通过左连接和右连接并集实现）
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
LEFT JOIN orders o ON e.employee_id = o.employee_id
UNION
SELECT e.employee_name, o.order_date, o.order_amount
FROM employees e
RIGHT JOIN orders o ON e.employee_id = o.employee_id;

-- 4. 子查询
-- 查询工资高于平均工资的员工信息
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询在 2023 年有订单的员工所属部门名称
SELECT d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN orders o ON e.employee_id = o.employee_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 5. 分组查询
-- 按部门统计员工数量
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

-- 按员工统计订单总金额，并筛选出订单总金额大于 1500 的员工
SELECT e.employee_name, SUM(o.order_amount) AS total_amount
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_name
HAVING SUM(o.order_amount) > 1500;

-- 6. 排序查询
-- 按工资从高到低查询员工信息
SELECT * FROM employees ORDER BY salary DESC;

-- 按入职日期升序，工资降序查询员工信息
SELECT * FROM employees ORDER BY hire_date ASC, salary DESC;

-- 7. 聚合函数查询
-- 查询所有员工的平均工资
SELECT AVG(salary) AS average_salary FROM employees;

-- 查询员工的最高工资和最低工资
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;

二.MySQL索引原理以及SQL优化

1.索引是什么？分为什么？

MySQL 索引是一种数据结构，用于提高数据库中数据的查询效率，就像书的目录一样，能让数据库快速定位到所需数据的位置。

1.1索引的分类

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（elasticsearch ）；

1.2主键索引

非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息；

PRIMARY KEY(key1, key2)

1.3唯一索引

不可以出现相同的值，可以有 NULL 值；

UNIQUE(key)

1.4普通索引

允许出现相同的索引内容；

INDEX(key)
-- OR
KEY(key[,...])

1.5组合索引

对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

1.6全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT;

在短字符串中用 LIKE % ；在全文索引中用 match 和 against；

1.7主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；

1. 如果显示设置 PRIMARY KEY ，则该设置的 key 为该表的主键；

2. 如果没有显示设置，则从非空唯一索引中选择；

1. 只有一个非空唯一索引，则选择该索引为主键；

2. 有多个非空唯一索引，则选择声明的第一个为主键；

3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

1.8约束

为了实现数据的完整性，对于 innodb ，提供了以下几种约束， primary key ， unique key ， foreign key ， default ， not null ；

1.9外键约束

外键用来关联两个表，来保证参照完整性； MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innodb 完整支持外键，并具备事务性；

create table parent (id int not null,primary key(id)
) engine=innodb;
create table child (id int,parent_id int,foreign key(parent_id) references parent(id)
ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb;
-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行
为发生时的操作可选择：
-- CASCADE   子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

1.10 约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

2.索引的实现原理

2.1索引存储

innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB （一个区由 64 个连续页构成）；页的默认值为 16k ；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K ；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；

2.2页

页是 innodb 磁盘管理的最小单位；默认 16K ，可通过 innodb_page_size 参数来修改；

B+ 树的一个节点的大小就是该页的值；

2.3B+树

全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K ， innodb 默认页大小为 16K；对页的访问是一次磁盘 IO ，缓存中会缓存常访问的页；

平衡二叉树（红黑树、 AVL 树）

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；

叶子节点之间互相连接，方便范围查询；

每个索引对应着一个 B+ 树；

B+ 树层高问题

B+ 树的一个节点对应一个数据页； B+ 树的层越高，那么要读取

到内存的数据页越多， IO 次数越多；

innodb 一个节点 16KB ；

假设： key 为 10 byte 且指针大小 6 byte ，假设一行记录的大小为 1KB ；

那么一个非叶子节点可存下 16 KB / 16 byte=1024 个（key+point ）；每个叶子节点可存储 1024 行数据；

结论：

2 层 B+ 树叶子节点 1024 个，可容纳最大记录数为： 1024 * 16 = 16384 ；

3 层 B+ 树叶子节点 1024 * 1024 ，可容纳最大记录数为： 1024 * 1024 * 16 = 16777216 ；

4 层 B+ 数叶子节点 1024 * 1024 * 1024 ，可容纳最大记录数为：1024 * 1024 * 1024 * 16 = 17179869184 ;

2.4关于自增 id

超过类型最大值会报错；

类型 bigint 范围： ;

假设采用 bigint ， 1 秒插入 1 亿条数据，大概需要 5849 年才会用完索引；

2.5聚集索引

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分

# table id name
select * from user where id >= 18 and id < 40;

2.6辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个 bookmark ；该书签存储了聚集索引的 key ；

-- 某个表 包含 id name lockyNum; id是主键，lockyNum
辅助索引;
-- KEY()
select * from user where lockyNum = 33;

2.7innodb 体系结构

2.8Buffer pool

Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数据；

2.9Change buffer

Change buffer 缓存辅助（二级）索引的数据变更（ DML 操作）这些数据并不在 buffer pool 中， Change buffer 中的数据将会异步 merge 到 buffer pool 中，当下次从磁盘当中读取非唯一索引的数据；同时会定期合并到索引页中。

free list 组织 buffer pool 中未使用的缓存页； flush list 组织 buffer pool 中脏页，也就是待刷盘的页； lru list 组织 buffer pool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的数据进行淘汰；

2.10最左匹配原则

对于组合索引，从左到右依次匹配，遇到就停止匹配；

2.11覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；较少磁盘 IO ；

2.12索引下推

为了减少回表次数，提升查询效率；在 MySQL 5.6 的版本开始推出；

MySQL 架构分为 server 层和存储引擎层；

没有索引下推机制之前， server 层向存储引擎层请求数据，在 server 层根据索引条件判断进行数据过滤；

有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇总返回给 server 层；

2.13索引失效

select ... where A and B 若 A 和 B 中有一个不包含索引，

则索引失效；

索引字段参与运算，则索引失效；例如： from_unixtime(idx)

= '2021 - 04 - 30' ; 改成 idx = unix_timestamp("2021 - 04 - 30")

索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某

个类型，实际等价于在索引列上作用了隐式转换函数；

LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select

* from user where name like '%Mark' ;

在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0

则修改为 idx > 0 or idx < 0 ；组合索引中，没使用第一列索引，索引失效；

2.14索引原则

查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；

使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如： smallint， tinyint ；

对于很长的动态字符串，考虑使用前缀索引；

于组合索引，考虑最左侧匹配原则和覆盖索引；

尽量选择区分度高的列作为索引；该列的值相同的越少越好

尽量扩展索引，在现有索引的基础上，添加复合索引；最多 6 个索引

不要 select * ；尽量只列出需要的列字段；方便使用覆盖索引；

索引列，列尽量设置为非空；

可选：开启自适应 hash 索引或者调整 change buffer ；

3.出现了sql比较慢怎么办

在面试的过程中如果遇到面试官拷问SQL比较慢应该怎么解决这个问题？

我们应该先找到是哪条哪些SQL语句导致了 SQL比较慢？

3.1.我们采用开启慢查询日志或者 show processlist 进行对SQL语句的搜寻

慢日志查询

开启

查看

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

设置

SET GLOBAL slow_query_log = ON;  -- on 开启 off
关闭
SET GLOBAL long_query_time = 4;   -- 单位秒；默认
10s；此时设置为4s

或者修改配置

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

mysqldumpslow

查找最近10条慢查询日志

mysqldumpslow -s t -t 10 -g 'select'
D:/mysql/mysql57-slow.log

SHOW PROFILE

# 查看是否开启
SELECT @@profiling;
# 设置开启
SET profiling = 1;
# 查看所有 profiles
show profiles;
# 查看query id 为 10 那条查询
show profile for query 10;
# 查看最后一条查询
show profile;
# 最后关闭
SET profiling = 0;

SHOW PROCESSLIST