SQL

关系数据库概述

数据模型

数据库一共有三种模型：

  • 层次模型

    层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树

  • 网状模型

    网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网

  • 关系模型

    关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表

    image-20240725165200125

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等

名称 类型 说明
INT 整型 4字节整数类型，范围约+/-21亿
BIGINT 长整型 8字节整数类型，范围约+/-922亿亿
REAL 浮点型 4字节浮点数，范围约+/-1038
DOUBLE 浮点型 8字节浮点数，范围约+/-10308
DECIMAL(M,N) 高精度小数 由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N) 定长字符串 存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N) 变长字符串 存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN 布尔类型 存储True或者False
DATE 日期类型 存储日期，例如，2018-06-22
TIME 时间类型 存储时间，例如，12:20:59
DATETIME 日期和时间类型 存储日期+时间，例如，2018-06-22 12:20:59

主流关系数据库

目前，主流的关系数据库主要分为以下几类：

  1. 商用数据库，例如：Oracle，SQL Server，DB2等；
  2. 开源数据库，例如：MySQL，PostgreSQL等；
  3. 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
  4. 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

MySQL

概述

MySQL是目前应用最广泛的开源关系数据库。MySQL最早是由瑞典的MySQL AB公司开发，该公司在2008年被SUN公司收购，紧接着，SUN公司在2009年被Oracle公司收购，所以MySQL最终就变成了Oracle旗下的产品。

MySQL安装

要安装MySQL，可以从MySQL官方网站下载最新的MySQL Community Server版本：

https://dev.mysql.com/downloads/mysql/

安装教程：MySQL ：： MySQL 5.7 参考手册 ：： 2.3.3.1 MySQL安装程序初始设置

使用Docker运行MySQL

  1. docker安装最新版mysql

    1
    docker pull mysql

  2. 启动服务

    1
    2
    3
    4
    5
    6
    7
    docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -v /Users/liaoxuefeng/mysql-data:/var/lib/mysql mysql
    # -d：表示在后台执行；
    # --name mysql：表示容器的名字，不输入Docker会自动选择一个名字；
    # -p 3306:3306：表示把容器的端口3306映射到本机，这样可以在本机通过3306端口连接MySQL；
    # -e MYSQL_ROOT_PASSWORD=password：表示传入一个环境变量，作为root的口令，这里设置的口令是password，不输入此项则会自动生成一个口令，需要查看日志才能知道口令；
    # -v /Users/liaoxuefeng/mysql-data:/var/lib/mysql：表示将本地目录映射到容器目录/var/lib/mysql作为MySQL数据库存放的位置，需要将/Users/liaoxuefeng/mysql-data改为你的电脑上的实际目录；
    # mysql：最后一个参数是Docker镜像的名称。

  3. 可以在Docker Desktop的管理窗口中选择Containers，看到正在运行的MySQL

关系模型

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

主键

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

  1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  2. 全局唯一GUID类型：也称UUID，使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

在定义表结构时，表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型

id s_id username age score
1 1 小明 18 90
2 1 小红 19 95

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键

外键

classes表

id name
1 一班
2 二班

students表

id class_id name
1 1 小明
2 1 小红
5 2 小白

外键定义

1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

students表

d class_id name gender score
1 1 小明 M 90
2 1 小红 F 95
3 1 小军 M 88

如果要经常根据score列进行查询，就可以对score列创建索引：

1
2
3
4
5
ALTER TABLE students
ADD INDEX idx_score (score);
# 使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

唯一索引

通过UNIQUE关键字添加唯一索引。

1
2
3
4
5
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
# 也可以只对某一列添加一个唯一约束而不创建唯一索引，这种情况下，name列没有索引，但仍然具有唯一性保证
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

数据库操作

创建数据库

1
CREATE DATABASE new_database;

查看所有数据库名

1
show DATABASES;

使用数据库

1
use <database_name>; # database_name 为数据库名字

数据库数据导入导出

1
2
$ mysqldump -u username -p old_database > dump.sql  
$ mysql -u username -p new_database < dump.sql

删除数据库

1
DROP DATABASE old_database;

表操作

创建数据表

1
2
3
4
5
6
CREATE TABLE table_name (  
    id INT PRIMARY KEY,  
    name VARCHAR(50) NOT NULL,  
    age INT,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);
  • PRIMARY KEY：将 id 字段设置为主键，用于唯一标识每一行数据。
  • NOT NULL：表示 name 字段不允许为空值。
  • INT 和 VARCHAR(50) 是数据类型，分别表示整数和字符串，并且可以设置长度。
  • DEFAULT CURRENT_TIMESTAMP：将 created_at 字段默认值设为当前时间戳。

除了上述示例中提到的属性外，还有许多其他可用的字段属性，如自增（AUTO_INCREMENT）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）等。可以根据需要来选择相应的字段属性进行设置。

查看数据库中所有的表

1
show tables;

查看表结构

1
DESCRIBE table_name;

image-20240726180159047

修改表名

1
RENAME TABLE employees TO staff;

修改表结构

添加新列
1
2
ALTER TABLE table_name  
ADD email VARCHAR(50);
修改表某一列的属性
1
2
ALTER TABLE table_name  
MODIFY age BIGINT; # int -> BIGINT
删除某一列
1
2
ALTER TABLE table_name  
DROP COLUMN age;
删除主键并修改主键
1
2
3
4
ALTER TABLE table_name  
DROP PRIMARY KEY,  # 删除原有主键
MODIFY id INT AUTO_INCREMENT,  # id自增
ADD PRIMARY KEY (id);  # 将id设置新主键
添加外键约束
1
2
ALTER TABLE table_name  
ADD CONSTRAINT fk_example FOREIGN KEY (parent_id) REFERENCES parent_table(id);

查询数据

基本查询

查询students表的所有行的所有数据

1
SELECT * FROM students
条件查询

查询students表成绩( score)大于等于80的所有行的所有数据

1
SELECT * FROM students WHERE score >= 80;

查询students表成绩( score)大于等于80且姓’李’的所有行的所有数据

1
SELECT * FROM students WHERE score >= 80 AND name='李%';
条件查询关键字
IN关键字

IN 关键字用于判断某个字段的值是否在指定的集合中

1
2
3
SELECT * | 字段名1 | 字段名2
FROM 表名 
where 字段名 [NOT] IN(元素1,元素2)
BETTEEN ADN 关键字

BETWEEN ADN 用于判断某个字段的值是否在指定的范围之内

1
select * from student WHERE stuID BETWEEN 1 and  3
空值查询

在数据表中,某些列的值可能为空(NULL), 空值不同于0,也不同于空字符串.在MYSQL中,使用is null 来判断字段的值是否为空

1
SELECT * FROM student WHERE stuName IS NOT NULL
DISTINCT 关键字

把查询的数据去掉重复的值

1
SELECT DISTINCT stuGender FROM student
LIKE 关键字

对某些字符串进行模糊搜索的查询

1
2
3
SELECT * | 字段名1 | 字段名2
FROM 表名 
WHERE 字段名  [NOT] LIKE '匹配字符串'
百分号(%)通配符

匹配任意长度的字符串,包括空字符串

1
SELECT *  FROM student WHERE stuName LIKE '黄%'
下划线(_)通配符

下划线通配符,只匹配单个字符

1
SELECT *  FROM student WHERE stuName LIKE '黄__'
AND关键字

使用多个查询条件,且同时满足

1
2
3
SELECT * | 字段名1 | 字段名2
FROM 表名 
WHERE 表达式1 AND 表达式2
OR关键字

使用多个查询条件,满足其中一个即可

1
2
3
SELECT * | 字段名1 | 字段名2
FROM 表名 
WHERE 表达式1 ORDER 表达式2
投影查询 - 过滤字段

查询students表的所有行的id, score, name

1
SELECT id, score, name FROM students;
排序

查询students表的所有行的id, score, name 并以score升序进行排列 (默认ASC)

1
SELECT id, name, score FROM students ORDER BY score;

查询students表的所有行的id, score, name 并以score倒序排列

1
SELECT id, name, score FROM students ORDER BY score DESC;

查询students表的所有行的id, score,gender, name 并以score升序进行排列，如果有相同的再按gender升序排序

1
SELECT id, name, gender, score FROM students ORDER BY score ,gender;
聚合查询
1
2
SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students; # 将字段输出命名为num
函数 说明
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值
COUNT 计算内容条数
FLOOR 向下取整
CEILING 向上取整
CONCAT 将两个字符串连接在一起
SUBSTRING(str,6,5) 提取字符串 - 切片
UPPER 将字符串转换为大写
NOW 返回当前日期和时间
DATE_FORMAT(NOW(), ‘%Y-%m-%d’) 格式化日期
IF(10 > 5, ‘True’, ‘False’) 根据条件返回不同的值
RAND() 生成随机数
分组查询

在SQL中，分组查询是一种非常常用的查询方式，它允许我们对查询结果进行分组并对每个分组应用聚合函数来获取汇总信息。在实际应用中，我们经常需要统计某个字段在不同分组下的总数、平均数、最大值、最小值等信息。

假设我们有一个存储员工信息的表Employee，表结构如下：

Employee表结构：

emp_id emp_name department salary
1 Alice Sales 5000
2 Bob HR 6000
3 Carol Sales 5500
4 David HR 7000
  1. 现在我们想要按部门统计每个部门的员工数量和平均工资，我们可以使用分组查询来实现：
1
2
3
SELECT department, COUNT(emp_id) as num_employees, AVG(salary) as avg_salary  
FROM Employee  
GROUP BY department;

运行以上查询后，会得到如下结果：

department num_employees avg_salary
Sales 2 5250
HR 2 6500
查询结果去重
  1. DISTINCT关键字 - 用关键字给字段添加去重效果
1
2
3
SELECT DISTINCT column1, column2, ...  
FROM table_name  
WHERE conditions;
  1. GROUP BY子句

使用GROUP BY子句可以将查询结果根据一个或多个列进行分组，并对每个组进行聚合计算。同时，GROUP BY子句会自动去重，因为它只返回每个分组的数据。

1
2
3
4
SELECT column1, column2, ...  
FROM table_name  
WHERE conditions  
GROUP BY column1, column2, ...;
分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

1
2
3
4
5
6
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 100 OFFSET 0; 
# 每页数据100条，查询第1页；注意SQL记录集的索引从0开始
# OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

多表查询

返回两个表中所有行的组合

1
2
3
4
5
6
7
8
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
连接查询

INNER JOIN - 内连接

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

OUTER JOIN - 外连接

当外表中没有与内表匹配的数据时，外连接会在结果集中填充NULL值

RIGHT OUTER JOIN - 右外连接

RIGHT OUTER JOIN（右外连接）则会返回右表中的所有行以及左表中与右表匹配的行，如果左表中没有匹配的行，则会在结果集中为左表的列填充NULL值。

1
2
3
4
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;

LEFT OUTER JOIN - 左外连接

LEFT OUTER JOIN（左外连接）会返回左表中的所有行以及右表中与左表匹配的行，如果右表中没有匹配的行，则会在结果集中为右表的列填充NULL值。

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;

FULL OUTER JOIN - 全外连接

FULL OUTER JOIN（全外连接）则会返回左表和右表中的所有行，并在结果集中为没有匹配的行填充NULL值。

1
2
3
4
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

image-20240726173438999

子查询

假设有两个表customers和orders，我们想要查询每个客户的订单总金额，如果不使用JOIN语法，我们可以使用子查询来代替

1
2
3
SELECT customer_id,   
       (SELECT SUM(total_amount) FROM orders WHERE customer_id = customers.customer_id) AS total_order_amount  
FROM customers;

修改数据

插入数据

当我们需要向数据库表中插入一条新记录时，使用INSERT语句。

  • INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
1
2
3
4
5
6
7
8
INSERT INTO students (class_id, name, gender, score) VALUES (2, '老大', 'M', 80);
# 插入多条数据
INSERT INTO students (class_id, name, gender, score) VALUES
  (3, '老二', 'M', 87),
  (4, '老三', 'M', 81);
# 可以使用不写字段的方式直接插入数据，需要注意字段个数和排序位置
INSERT INTO table_name  
VALUES (value1, value2, value3, ...);

更新数据

更新数据库表中的记录，使用UPDATE语句

  • UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
1
2
UPDATE students SET name='大牛', score=66 WHERE id=1;
UPDATE students SET score=score+10 WHERE score<80;

删除数据

删除数据库表中的记录，我们可以使用DELETE语句。

  • DELETE FROM <表名> WHERE …;
1
DELETE FROM students WHERE id=1;

临时表

临时表是在查询中临时创建和使用的表，用于存储中间结果或者临时数据，在查询结束后会自动销毁，不保留数据。使用临时表可以帮助简化复杂查询、提高性能和降低查询的复杂度。

创建临时表

在SQL语句中，可以使用CREATE TEMPORARY TABLE语句来创建临时表。临时表只在当前会话中存在，并且在会话结束时自动销毁。临时表的结构和普通表一样，可以像普通表一样进行数据插入、查询、更新和删除操作。

1
2
3
4
5
CREATE TEMPORARY TABLE temp_table (  
    column1 datatype,  
    column2 datatype,  
    ...  
);

填充临时表

创建了临时表后，可以使用INSERT INTO语句将数据插入临时表中。可以使用SELECT语句来从其他表中选择数据插入到临时表中。

1
2
3
4
INSERT INTO temp_table  
SELECT column1, column2, ...  
FROM source_table  
WHERE condition;

查询临时表

1
2
SELECT *  
FROM temp_table;

使用临时表进行多表查询

临时表通常用于处理复杂的查询需求，例如多表关联查询。可以使用临时表来存储需要联合查询的表数据，然后再基于临时表进行进一步的查询操作。

1
2
3
4
5
6
7
8
CREATE TEMPORARY TABLE temp_orders AS   
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_order_amount  
FROM orders  
GROUP BY customer_id;  

SELECT c.customer_id, c.customer_name, t.order_count, t.total_order_amount  
FROM customers c  
JOIN temp_orders t ON c.customer_id = t.customer_id;

视图

SQL视图是虚拟表，它是从一个或多个基本表中导出的表，用户可以像使用表一样对其进行查询和操作。视图可以基于一个表或多个表，还可以基于其他视图。

SQL视图使用的主要作用有：

  1. 简化复杂查询：视图可以隐藏复杂的SQL查询逻辑，将其封装为一个简单的表结构，方便用户查询和理解。
  2. 数据安全性控制：通过视图，可以限制用户只能访问特定的列或行，从而保护敏感数据。可以通过视图来隐藏敏感数据，并且只提供给用户可见的数据。
  3. 数据逻辑封装：视图可以将多个表的关联操作封装成一个视图，从而简化数据库操作和维护。当基础表发生变化时，只需要重新定义视图，而不需要修改查询逻辑。
  4. 简化数据操作：通过视图，可以将复杂的数据操作逻辑封装为一个视图，用户只需要对视图进行增删改查操作，而不需要了解底层的数据结构和操作方式。

创建视图 - 单表结果

将Employee 表的emp_id, emp_name, department, salary 字段数据作为新创建的视图表EmployeeView 的数据

1
2
3
CREATE VIEW EmployeeView AS  
SELECT emp_id, emp_name, department, salary  
FROM Employee;

创建视图 - 多表结果

1
2
3
4
CREATE VIEW EmployeeDepartmentView AS  
SELECT emp_id, emp_name, Employee.department_id, salary, department_name  
FROM Employee  
JOIN Department ON Employee.department_id = Department.department_id;

视图和临时表的区别

视图用于简化数据查询和操作，提高数据的安全性和可维护性，而临时表用于存储临时数据或中间结果，辅助进行复杂的数据处理操作。

  1. 视图（View）：
  • 视图是基于表或其他视图的查询结果集，是一个虚拟的表，不存储实际数据，只存储查询定义。
  • 视图可以隐藏复杂的数据结构和查询逻辑，简化用户对数据的访问。
  • 视图可以用于限制用户对数据的访问权限，只允许用户访问视图中指定的列或行。
  • 视图可以提高数据的安全性，通过视图可以隐藏敏感数据，只暴露需要的数据给用户。
  • 视图的数据是动态的，当基础表的数据发生变化时，视图的数据也会相应地更新。
  1. 临时表（Temporary Table）：
  • 临时表是一个临时存储数据的表，它可以在会话级别或事务级别存在，当会话结束或事务结束后数据会被销毁。
  • 临时表常用于存储中间结果或临时数据，用于辅助复杂的数据处理操作。
  • 临时表可以提高查询性能，当需要多次查询同一组数据时，可以将结果存储在临时表中，避免重复计算。
  • 临时表的作用范围有限，只在当前会话或当前事务中有效，不会对其他用户产生影响。
  • 临时表不会和永久表共享相同的表空间，占用的内存和磁盘资源较小。

索引

索引是一种用于提高数据库查询性能的数据结构。它类似于书籍中的目录，可以加快数据库的查询速度。索引存储特定列（或多列）的值，以便在查询中快速定位到所需的行。

索引类型

MySQL支持多种类型的索引，常见的索引类型包括：

  1. B-Tree索引：B-Tree（平衡树）索引是MySQL中最常用的索引类型。它适用于等值查询、范围查询和排序操作。B-Tree索引适用于大多数场景，包括单列索引、组合索引和唯一索引。
  2. 哈希索引：哈希索引适用于等值查询，但不支持范围查询和排序操作。哈希索引将索引列的值通过哈希函数映射到一个哈希表中，可以快速定位到具体的数据行。但是，哈希索引不适用于范围查询和模糊查询。
  3. 全文索引：全文索引用于全文搜索，适用于对文本内容进行关键字搜索的场景。全文索引可以提供更高级的搜索功能，如关键字匹配、模糊搜索和排序。
  4. 空间索引：空间索引用于处理地理空间数据，如地理位置坐标。它支持空间数据类型和空间函数，可以进行空间范围查询和距离计算。
  5. 前缀索引：前缀索引是指只对索引列的前缀部分进行索引，而不是整个列。它可以减少索引的存储空间，但可能会影响查询的性能。
  6. 其他特殊索引：MySQL还支持其他一些特殊类型的索引，如全文空间索引、JSON索引等，用于特定的数据类型和查询需求。

根据具体的业务需求和查询模式，选择合适的索引类型非常重要。通常，使用B-Tree索引是最常见和通用的选择，但在特定场景下，其他类型的索引也可以提供更好的性能和功能。

MySQL索引原理

MySQL索引是一种数据结构，用于加快数据库查询操作的速度。索引基于B-Tree（平衡树）或哈希表等数据结构实现，它们允许快速定位和访问存储在数据库表中的数据。

MySQL索引的原理如下：

  1. B-Tree索引原理：

    • B-Tree是一种平衡树结构，它具有多个层级，每个层级都有多个节点。根节点位于最上层，叶子节点位于最下层。

    • 每个节点包含多个键值对，其中键是索引列的值，值是指向对应数据行的指针。

    • B-Tree索引按照键的顺序进行排序，使得查询时可以使用二分查找的方式快速定位到目标数据。

    • B-Tree索引支持等值查询、范围查询和排序操作，它可以有效地减少查询的数据访问量。

  2. 哈希索引原理：

    • 哈希索引使用哈希函数将索引列的值映射到一个哈希表中的槽位。

    • 哈希表是一个数组，每个槽位存储一个指针，指向对应数据行的位置。

    • 哈希索引通过哈希函数的计算，可以快速定位到目标数据行，具有O(1)的查询时间复杂度。

    • 哈希索引适用于等值查询，但不支持范围查询和排序操作。

MySQL索引的工作原理如下：

  1. 创建索引：当在表的列上创建索引时，MySQL会根据索引类型（如B-Tree或哈希）创建相应的数据结构，并将索引列的值和对应的指针存储在索引结构中。
  2. 查询优化器：当执行查询语句时，MySQL的查询优化器会分析查询语句和表的结构，决定使用哪个索引来执行查询。优化器会考虑索引的选择性、列的顺序、查询条件等因素，选择最优的索引。
  3. 索引扫描：当使用索引执行查询时，MySQL会根据索引的数据结构进行索引扫描。对于B-Tree索引，MySQL会根据查询条件的范围进行二分查找，定位到目标数据行。对于哈希索引，MySQL会通过哈希函数计算，直接定位到目标数据行。
  4. 数据访问：一旦定位到目标数据行，MySQL会使用指针获取对应的数据，并返回给查询结果。

需要注意的是，索引的创建和维护会带来一定的开销，包括存储空间和写操作的性能。因此，需要根据具体的业务需求和查询模式，合理选择和使用索引，以平衡查询性能和存储开销。

MySQL索引是如何支持百万级别查询的

MySQL索引是基于B+树的，B+树是类似与跳表的一种数据结构，查询效率为log(N)

创建索引的方法

在SQL中，可以使用以下语法为表添加索引：

1
2
CREATE INDEX index_name ON table_name (column_name);  
# 创建了一个针对table_name表中column_name列的索引。这样可以加快对column_name列的查询速度。
  • index_name：指定索引的名称。
  • table_name：指定要添加索引的表名。
  • column_name：指定要添加索引的列名。

使用场景

索引适合用于以下情况：

  • 频繁用于查询的列：对于经常用于WHERE子句、JOIN子句或ORDER BY子句的列，创建索引可以提高查询性能。
  • 字段的取值范围广泛：如果某列的取值范围很大，例如性别字段或状态字段，可以考虑为该列创建索引。
  • 经常需要排序的列：对于经常需要按特定列排序的查询，创建索引可以加快排序的速度。
  • 经常用于连接的列：对于经常用于JOIN操作的列，创建索引可以提高连接的效率。

索引使用注意事项

  • 不要滥用索引：创建过多的索引可能会降低数据插入、更新和删除操作的性能，因此应谨慎选择需要创建索引的列。
  • 对于小数据量表：一些小表可能不需要额外的索引，因为全表扫描的成本可能更低。
  • 定期维护索引：随着数据的变化，索引可能会失效或不再有效，因此应定期进行索引重建或重新组织操作。

MySQL索引为什么不能过多也不能太少

MySQL索引的数量需要适度，既不能过多也不能太少，这是为了平衡查询性能和存储空间的考虑。

如果索引过多，会导致以下问题：

  1. 存储空间占用：每个索引都需要占用额外的存储空间。如果索引过多，会增加数据库的存储需求，占用更多的磁盘空间。
  2. 写操作性能下降：每次对表进行插入、更新或删除操作时，都需要更新索引。如果索引过多，写操作的性能会下降，因为需要更新更多的索引。
  3. 查询优化器的选择困难：查询优化器在决定使用哪个索引来执行查询时，需要考虑多个索引的选择。如果索引过多，查询优化器的选择过程会变得复杂，可能导致性能下降。

另一方面，如果索引太少，会导致以下问题：

  1. 查询性能下降：没有足够的索引可能导致查询的性能下降。查询需要扫描更多的数据行，而不是直接使用索引进行快速定位，从而增加了查询的时间复杂度。
  2. 数据库负载增加：缺乏索引可能导致数据库的负载增加。查询需要扫描更多的数据行，消耗更多的CPU和内存资源，降低整体的数据库性能。

因此，为了平衡查询性能和存储空间的需求，需要根据具体的业务场景和查询模式来合理选择和创建索引。需要根据经验和性能测试来评估索引的效果，并根据实际情况进行调整和优化。

数据库优化思路

数据库优化是提高数据库性能和效率的过程。以下是一些常见的数据库优化思路：

  1. 索引优化：合理创建和使用索引可以提高查询性能。分析常用的查询语句，确定需要的索引类型和字段，并避免创建过多或不必要的索引。定期检查索引的使用情况，进行索引重建或删除不需要的索引。
  2. 查询优化：优化查询语句可以减少数据库的负载和提高查询性能。使用合适的查询语法和操作符，避免使用全表扫描和不必要的连接操作。使用EXPLAIN语句分析查询计划，优化查询的执行路径。
  3. 看数据库设计优化：合理的数据库设计可以提高查询和操作的效率。使用适当的数据类型和字段长度，避免冗余和重复数据。规范化和反规范化的选择要根据具体的业务需求和查询模式进行权衡。
  4. 缓存优化：使用缓存技术可以减少对数据库的访问，提高响应速度。常见的缓存技术包括数据库查询缓存、应用程序级缓存、分布式缓存等。根据业务需求和数据更新频率，选择合适的缓存策略和缓存工具。
  5. 硬件优化：合理配置和优化数据库服务器的硬件资源可以提高数据库性能。增加内存容量，优化磁盘和网络配置，调整数据库参数等都可以对性能产生影响。
  6. 定期维护和监控：定期进行数据库维护和监控是保持数据库性能的关键。包括备份和恢复、数据清理、统计信息收集、日志管理等。监控数据库的性能指标，如CPU利用率、内存使用、磁盘IO等，及时发现和解决性能问题。
  7. 并发控制和事务管理：合理管理并发访问和事务操作可以提高数据库的并发性和数据一致性。使用适当的锁机制、事务隔离级别和并发控制策略，避免死锁和数据冲突。

数据库优化是一个持续的过程，需要根据实际情况进行分析、调整和优化。通过综合考虑索引、查询、设计、缓存、硬件、维护和并发控制等方面的优化，可以提高数据库的性能和效率。

慢查询如何优化

  1. 使用合适的索引：
    • 确保数据库表的相关字段上建立了索引，这样可以加快查询速度。
    • 分析查询语句中的条件，确定哪些条件经常被用来检索数据，为这些字段添加索引。
  2. 优化查询语句：
    • 确保查询语句写法正确，避免使用复杂的子查询和联合查询。
    • 避免使用SELECT *，只选择需要的字段。
    • 使用EXPLAIN语句来分析查询执行计划，查看是否有不必要的全表扫描或索引未命中。
  3. 优化表结构：
    • 避免使用过多的JOIN操作，考虑是否需要将表拆分或者进行冗余设计。
    • 对于大量数据的表，考虑进行分区或分表操作，以减少查询范围。
  4. 限制返回数据量：
    • 如果只需要一部分数据，可以使用LIMIT进行限制返回数据的数量。
    • 考虑是否需要分页查询，以避免一次性返回大量数据。
  5. 缓存：
    • 对于静态数据或者不经常变动的数据，可以考虑使用缓存来减少数据库查询次数。
  6. 定期优化数据库：
    • 定期分析数据库性能，清理无用数据、优化表结构、重新调整索引等。
  7. 数据库参数调优：
    • 根据数据库类型和版本，调整数据库参数来提高性能，如优化缓冲区大小、连接数等

事务

把多条sql语句作为一个整体进行操作的功能，被称为数据库事务

数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

数据库事务具有ACID这4个特性

  • A：Atomicity，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C：Consistency，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D：Durability，持久性，即事务完成后，对数据库数据的修改被持久化存储。

隐式事务

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

显式事务

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务

1
2
3
4
5
6
BEGIN;
    BEGIN TRANSACTION; -- 开启事务
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
# COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。
1
2
3
4
5
6
BEGIN;
    BEGIN TRANSACTION; -- 开启事务
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
# 有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

什么是分布式事务

分布式事务是指涉及多个独立的计算机系统或数据库的事务操作。在分布式系统中，每个计算机系统或数据库都可以独立执行事务，但这些事务可能需要保持一致性和原子性。

为了实现分布式事务，通常使用一些协调机制和协议，如两阶段提交（Two-Phase Commit，2PC）、三阶段提交（Three-Phase Commit，3PC）、补偿事务（Compensating Transaction）等。这些机制和协议用于确保所有参与的系统或数据库在事务执行期间保持一致，并在需要时进行回滚或恢复。

分布式事务的设计和实现需要考虑网络延迟、故障恢复、并发访问等因素，以确保事务的正确性和可靠性

分布式节点的接入和一致性

  1. 分布式节点的接入：当新的节点加入分布式系统时，需要确保其能够正确地与其他节点进行通信和协作。以下是一些常见的方法和技术：

    • 注册和发现：新节点可以通过注册自身的信息，如IP地址、端口号等，使其他节点能够发现和连接它。常见的注册和发现机制包括服务注册中心、DNS等。

    • 节点间通信：节点之间需要建立可靠的通信渠道，以便进行消息传递和协作。常见的通信协议包括HTTP、TCP/IP、消息队列等。

    • 负载均衡：为了平衡节点的负载和提高系统的可扩展性，可以使用负载均衡技术，将请求分发到不同的节点上。常见的负载均衡策略包括轮询、最少连接等。

  2. 分布式节点的一致性：在分布式系统中，节点之间的数据一致性是一个重要的问题。当多个节点同时对数据进行读写操作时，需要确保数据的一致性。以下是一些常见的方法和技术：

    • 一致性协议：使用一致性协议来确保节点之间的数据一致性。常见的一致性协议包括两阶段提交（2PC）、三阶段提交（3PC）、Paxos、Raft等。

    • 数据复制：将数据复制到多个节点上，以提高数据的可用性和容错性。常见的数据复制策略包括主从复制、多主复制等。

    • 时钟同步：节点之间的时钟同步是保证分布式系统一致性的关键。通过使用时钟同步协议，可以确保节点之间的时间一致性，以便进行正确的时间戳和顺序处理。

    • 冲突解决：当多个节点同时对数据进行写操作时，可能会发生冲突。使用冲突解决策略，如乐观锁、悲观锁、版本控制等，可以解决冲突并保持数据的一致性。

存储过程

存储过程是一组预定义的SQL语句集合，可以被保存在数据库中并被多次调用。存储过程使得数据库操作更加模块化、可重用，并可以提高性能。类似于编程语言的方法函数

存储过程特点

  • 存储过程是一段被编译、存储在数据库中并可供重复使用的SQL代码块。
  • 存储过程可以接受参数作为输入和输出。
  • 存储过程可以包含流程控制语句、循环语句、条件语句等，使得复杂的逻辑操作可以在数据库层面完成。
  • 存储过程可以提高数据库的性能，减少网络通信开销，并减少客户端与数据库之间的交互次数。

创建存储过程的方法

在SQL中，可以使用以下语法创建存储过程：

1
2
3
4
5
CREATE PROCEDURE procedure_name  
AS  
BEGIN  
    -- SQL 语句 
END;
  • procedure_name：指定存储过程的名称。
  • AS BEGIN 和 END 之间是存储过程的SQL代码逻辑

使用存储过程的方法

  • 调用存储过程：使用 EXECUTE 或 CALL 关键字来执行存储过程。例如：EXECUTE procedure_name;
  • 传递参数：存储过程可以接受参数，参数可以是输入参数、输出参数或输入输出参数。在创建存储过程时定义参数，并在调用存储过程时传递参数。
  • 修改存储过程：通过 ALTER PROCEDURE 命令来修改存储过程的定义。
  • 删除存储过程：使用 DROP PROCEDURE 命令来删除存储过程。

输入输出参数

输入输出参数结合了输入参数和输出参数的功能，允许存储过程接受参数值并将计算结果返回给调用者。在存储过程定义中，通过在参数名称前面加上 INOUT 或 IN OUTPUT 关键字来声明输入输出参数。

1
2
3
4
5
6
7
8
CREATE PROCEDURE sp_UpdateEmployeeSalary  
    @employee_id INT,  
    @new_salary DECIMAL(10,2) INOUT  
AS  
BEGIN  
    UPDATE Employee SET salary = @new_salary WHERE employee_id = @employee_id;  
    SET @new_salary = @new_salary * 1.1; -- 增加10%薪资  
END;

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE PROCEDURE sp_UpdateAccountBalance  
    @account_number INT,  
    @amount DECIMAL(10, 2),  
    @transaction_type VARCHAR(50)  
AS  
BEGIN  
    BEGIN TRANSACTION; -- 开启事务  
    
    -- 更新客户账户余额  
    UPDATE Account SET balance = balance + @amount WHERE account_number = @account_number;  
    
    -- 记录交易日志  
    INSERT INTO TransactionLog (account_number, transaction_type, amount, transaction_date)   
    VALUES (@account_number, @transaction_type, @amount, GETDATE());  
    
    -- 检查是否余额为负，如果为负则回滚事务  
    IF (SELECT balance FROM Account WHERE account_number = @account_number) < 0  
    BEGIN  
        ROLLBACK; -- 回滚事务  
        RAISERROR('Insufficient funds to complete transaction.', 16, 1); -- 抛出错误消息
        RETURN -1; -- 返回 -1 表示余额不足
    END  
    ELSE  
    BEGIN  
        COMMIT; -- 提交事务
        RETURN 0; -- 返回 0 表示成功  
    END  
END;

调用存储过程

使用 EXEC 或 EXECUTE 关键字来执行该存储过程并传递参数

1
2
3
4
-- 假设要更新账户号为12345的客户账户余额，金额为100，交易类型为'Deposit'  
DECLARE @result INT;  -- 使用 DECLARE @result INT; 声明一个 INT 类型的变量，接受返回值
EXEC @result = sp_UpdateAccountBalance @account_number = 12345, @amount = 100.00, @transaction_type = 'Deposit';  
SELECT @result; -- 显示存储过程的返回值