MySQL 其他

SQL执行

SQL执行顺序

from确定查询的表，jion连接表、on对from进行过滤，where进行条件过滤，

group by和having进行分组后过滤，

select进行指定的列，distinct去重，排序，限制行数

定表过滤-分组过滤-选列过滤

基础

mysql8.0有哪些新功能

窗口函数，公有表达式。

公有表达式：定义一个临时结果集，可以想表一样应用。FORM(子查询),可能需要反复扫描子查询表,导致性能问题。

窗口函数：

储存引擎

为什么使用InnoDB储存引擎？

InnoDB 是事务型数据库的首选引擎，支持事务安全表 (ACID)，支持行锁定和外键。

Memory存储引擎使用HASH。

MySQL5.5之后，InnoDB 作为默认存储引擎

关系数据库和非关系型数据的区别

关系型数据库，容易理解，因为它采用了关系模型来组织数据，支持丰富的复杂查询。

非关系型数据库，基于键值对，读写性能很高，可以支持储存多种类型的数据。

数据备份和恢复

如果数据库误操作, 如何执行数据恢复?

数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

数据备份和恢复

使用mysqldump进行备份恢复，通过Mysql事件每天更新。

使用Navcat工具备份

视图

视图：定义的sql查询语句。

我一般不在视图上做复杂计算。

因为每次查询数据的视图会执行视图的sql。

约束

外键约束

储存过程

存储过程：是一组为了完成某项特定功能的 SQL 语句集合

触发器

触发器：他在特定的数据库活动发生时自动执行，和表上的insert,update,delete操作关联。

MySQL备份

备份远程数据库

#!/bin/bash

# MySQL 远程连接信息
REMOTE_HOST="your_remote_host"
PORT="3306"
USERNAME="your_username"
PASSWORD="your_password"
DATABASE="your_database"

# 备份保存路径
BACKUP_DIR="/path/to/backup_directory"

# 当前日期
CURRENT_DATE=$(date +%Y%m%d)

# 备份文件名
BACKUP_FILE="${BACKUP_DIR}/backup_${CURRENT_DATE}.sql"

# mysqldump 备份命令
mysqldump -h $REMOTE_HOST -P $PORT -u $USERNAME -p$PASSWORD $DATABASE > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Database backup successfully completed."
else
    echo "Error: Database backup failed."
    exit 1
fi

# 删除十天前的备份数据
OLD_BACKUP_DATE=$(date -d "10 days ago" +%Y%m%d)
OLD_BACKUP_FILE="${BACKUP_DIR}/backup_${OLD_BACKUP_DATE}.sql"

if [ -f $OLD_BACKUP_FILE ]; then
    rm $OLD_BACKUP_FILE
    echo "Deleted backup from ${OLD_BACKUP_DATE}."
else
    echo "No backup found from ${OLD_BACKUP_DATE}."
fi

exit 0

Docker中的MySQL定时备份

创建目录
mkdir /home/tests/wy-mysqlbackups

步骤 1: 创建 Shell 脚本文件
vim /home/tests/wy-mysqlbackups/wy-mysqlbackup.sh

步骤 2: 输入脚本内容
#!/bin/bash

# 容器名称
DOCKER_NAME="wy-mysql"
# 数据库名称
DATABASE_NAME="gva"
# 数据库密码
DATABASE_PASSWORD="-"
# 当前日期和时间，用于文件命名
DATE=$(date +%Y%m%d%H%M)
# MySQL 备份文件的存储目录
BACKUP_DIR="/home/tests/wy-mysqlbackups"
# 检查备份目录是否存在，如果不存在就创建
if [ ! -d "${BACKUP_DIR}" ]; then
    mkdir -p "${BACKUP_DIR}"
fi
# 完整的备份文件路径
BACKUP_PATH="${BACKUP_DIR}/${DATABASE_NAME}_${DATE}.sql"
# 执行 mysqldump 命令备份 MySQL 数据库
docker exec -e MYSQL_PWD=${DATABASE_PASSWORD} ${DOCKER_NAME} /usr/bin/mysqldump -u root ${DATABASE_NAME} > "${BACKUP_PATH}"
# 删除超过10天的旧备份文件
find "${BACKUP_DIR}" -name "${DATABASE_NAME}_*.sql" -mtime +10 -exec rm {} \;

步骤 3: 保存并关闭编辑器
如果您使用的是 vim，按 Esc，然后输入 :wq 并按 Enter 来保存并退出。

步骤 4: 赋予执行权限
chmod +x /home/wwq/shell-script/mysql_backup.sh

步骤 5: 运行脚本
. /home/wwq/shell-script/mysql_backup.sh

步骤 6: 添加到定时任务中
打开当前用户的 cron 任务列表编辑界面：
crontab -e

添加一行以定义何时执行备份脚本。例如，每天凌晨 2 点执行，您可以添加：
0 2 * * * /home/tests/wy-mysqlbackups/wy-mysqlbackup.sh

报错：-bash: ./mysql_backup.sh: /bin/bash^M: bad interpreter: No such file or directory

原因：Windows下编辑过。

解决：vim编写。

SHOW PROCESSLIST;

KILL 137;

数据备份和恢复

使用mysqldump进行备份恢复，通过Mysql事件每天更新。

使用Navcat工具备份

备份
mysqldump -u root -p gva >D:\gva.sql

恢复
mysql -u root -p gva <D:\gva.sql

 分表

是什么

将表按照某种规则拆分成多个表。

为什么

当数据量超大的时候，B-Tree索引就无法起作用了。

怎么用

垂直分区

切分原则： 把不常用或存储内容比较多的字段分到新的表中可使表存储更多数据。

Innodb主索引叶子节点存储着当前行的所有信息，所以减少字段可使内存加载更多行数据。

水平分区

切分原则： 增量区间或散列或其他业务逻辑。

使用哪种切分方法要根据实际业务逻辑判断。

范围分区：

比如对表的访问多是近期产生的新数据，历史数据访问较少，可以考虑根据时间增量把数据按照一定时间段（比如每年）切分。

Hash分区：

如果对表的访问较均匀，没有明显的热点区域，则可以考虑用范围（比如每500w一个表）或普通Hash或一致性Hash来切分。

分库

是什么

将数据库的表分到多个数据库中

为什么

当单台数据库性能不满足需求时，数据库磁盘IO瓶颈和网络IO瓶颈。

怎么用

分库的原则 

是根据业务紧密程度拆分，因为跨数据库无法联表查询。

 分区

是什么

分区表是一个独立的逻辑表，但是底层由多个物理子表实现。

为什么

当数据量超大的时候，B-Tree索引就无法起作用了。

MySQL帮我们实现了传统分表后的SQL中间件。

对于原表分区后，对于应用层来说可以不做变化。

怎么用

分区方式

哈希分区：使用哈希函数将数据均匀分布到不同的分区。

按范围分区：按照时间，地点分区等。

分区的使用场景

对数据的操作只涉及一部分数据，而不是所有数据 

分区怎么用 

-- 创建分区表
CREATE TABLE sales (
    id INT,
    date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2012),
    PARTITION p3 VALUES LESS THAN (2013),
    PARTITION p4 VALUES LESS THAN (2014),
    PARTITION p5 VALUES LESS THAN (2015),
    PARTITION p6 VALUES LESS THAN (2016),
    PARTITION p7 VALUES LESS THAN (2017),
    PARTITION p8 VALUES LESS THAN (2018),
    PARTITION p9 VALUES LESS THAN (2019),
    PARTITION p10 VALUES LESS THAN MAXVALUE
);

-- 插入数据
INSERT INTO sales VALUES (1, '2018-01-01', 100.00);
INSERT INTO sales VALUES (2, '2018-02-01', 200.00);
INSERT INTO sales VALUES (3, '2019-01-01', 150.00);
INSERT INTO sales VALUES (4, '2019-02-01', 250.00);


-- 查询2018年的销售总额
SELECT SUM(amount) FROM sales PARTITION (p9);

-- 查询2019年的销售总额
SELECT SUM(amount) FROM sales PARTITION (p10);