2. 首页 > MariaDB教程 > 正文

MariaDB教程FG056-MariaDB SQL语言与开发最佳实践

教程整理：风哥教程  |  更新时间：2026-01-02  |  教程分类：MariaDB教程  |  文档学习：27

内容简介：本文主要介绍MariaDB SQL语言与开发最佳实践，包括SQL语言的基本概念、SQL语言的分类、开发最佳实践的基本概念等内容。通过实际案例讲解SQL语句优化、索引设计和存储过程开发，帮助读者掌握SQL开发的技能。风哥教程参考MariaDB官方文档SQL Statements、Optimization等相关内容。

Part01-基础概念与理论知识

1.1 SQL语言的基本概念

SQL（Structured Query Language）是一种用于管理关系型数据库的标准化语言。它用于执行各种数据库操作，如查询、插入、更新和删除数据。

SQL语言的主要特点：

  • 标准化：SQL是一种标准语言，被大多数关系型数据库支持
  • 简单易学：SQL语法简洁明了，易于学习和使用
  • 功能强大：可以执行复杂的数据库操作
  • 非过程化：只需要指定做什么，不需要指定怎么做

1.2 SQL语言的分类

SQL语言可以分为以下几类：

  • DDL（Data Definition Language）：数据定义语言，用于创建、修改和删除数据库对象，如CREATE、ALTER、DROP等
  • DML（Data Manipulation Language）：数据操作语言，用于查询、插入、更新和删除数据，如SELECT、INSERT、UPDATE、DELETE等
  • DCL（Data Control Language）：数据控制语言，用于控制数据访问权限，如GRANT、REVOKE等
  • TCL（Transaction Control Language）：事务控制语言，用于管理事务，如BEGIN、COMMIT、ROLLBACK等

1.3 开发最佳实践的基本概念

开发最佳实践是指在SQL开发过程中遵循的一系列规范和技巧，以提高代码的质量、性能和可维护性。

开发最佳实践的主要内容：

  • SQL语句优化
  • 索引设计与优化
  • 存储过程与函数开发
  • 事务管理
  • 安全开发
更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 SQL开发规范

SQL开发规范建议：

  • 命名规范：使用有意义的表名、列名和索引名
  • 缩进规范：使用一致的缩进，提高代码可读性
  • 注释规范：添加适当的注释，解释复杂的SQL语句
  • 代码风格：使用一致的代码风格，如关键字大写、操作符前后空格等

2.2 性能优化建议

性能优化建议：

  • 使用索引：为经常查询的列创建索引
  • 避免全表扫描：使用WHERE子句过滤数据
  • 优化JOIN操作：使用合适的JOIN类型，避免笛卡尔积
  • 使用 LIMIT：限制查询结果的数量
  • 避免使用SELECT *：只查询需要的列

2.3 安全开发建议

安全开发建议：

  • 使用参数化查询：避免SQL注入攻击
  • 最小权限原则：为用户分配最小必要的权限
  • 数据加密：对敏感数据进行加密
  • 审计日志：记录重要的数据库操作
学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 SQL语句优化

更多学习教程公众号风哥教程itpux_com

# SQL语句优化
MariaDB [(none)]> # 1. 避免使用SELECT *
# 不好的写法
SELECT * FROM fgedu_users;
# 好的写法
SELECT id, username, email FROM fgedu_users;
# 2. 使用WHERE子句过滤数据
# 不好的写法
SELECT id, username FROM fgedu_users ORDER BY id LIMIT 10;
# 好的写法
SELECT id, username FROM fgedu_users WHERE id <= 10;
# 3. 优化JOIN操作
# 不好的写法
SELECT * FROM fgedu_users, fgedu_articles WHERE fgedu_users.id = fgedu_articles.user_id;
# 好的写法
SELECT u.id, u.username, a.title FROM fgedu_users u INNER JOIN fgedu_articles a ON u.id = a.user_id;
# 4. 使用索引
CREATE INDEX idx_username ON fgedu_users(username);
# 5. 避免使用函数
# 不好的写法
SELECT id, username FROM fgedu_users WHERE YEAR(created_at) = 2023;
# 好的写法
SELECT id, username FROM fgedu_users WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;

3.2 索引设计与优化

# 索引设计与优化
MariaDB [(none)]> # 1. 为经常查询的列创建索引
CREATE INDEX idx_email ON fgedu_users(email);
# 2. 为经常排序的列创建索引
CREATE INDEX idx_created_at ON fgedu_users(created_at);
# 3. 为经常JOIN的列创建索引
CREATE INDEX idx_user_id ON fgedu_articles(user_id);
# 4. 复合索引
CREATE INDEX idx_username_email ON fgedu_users(username, email);
# 5. 查看索引使用情况
EXPLAIN SELECT id, username FROM fgedu_users WHERE username = ‘admin’;
# 6. 优化索引
ALTER TABLE fgedu_users ADD INDEX idx_username (username);
ALTER TABLE fgedu_users DROP INDEX idx_username;

3.3 存储过程与函数开发

# 存储过程与函数开发
MariaDB [(none)]> # 1. 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
SELECT id, username, email FROM fgedu_users WHERE id = user_id;
END //
DELIMITER;
# 2. 调用存储过程
CALL get_user(1);
# 3. 创建函数
DELIMITER //
CREATE FUNCTION get_user_count() RETURNS INT
BEGIN
DECLARE count INT;
SELECT COUNT(*) INTO count FROM fgedu_users;
RETURN count;
END //
DELIMITER;
# 4. 调用函数
SELECT get_user_count();
# 5. 查看存储过程和函数
SHOW PROCEDURE STATUS WHERE Db = ‘fgedudb’;
SHOW FUNCTION STATUS WHERE Db = ‘fgedudb’;
学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 SQL语句优化案例

场景描述：优化一个慢查询SQL语句。

# 原始SQL语句
MariaDB [(none)]> SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’ ORDER BY id DESC;
# 执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’ ORDER BY id DESC;
+—-+————-+————+——+—————+——+———+——+——+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——+——+—————————–+
| 1 | SIMPLE | fgedu_users | ALL | NULL | NULL | NULL | NULL | 1000 | Using where;
Using filesort |
+—-+————-+————+——+—————+——+———+——+——+—————————–+
# 优化方案：为created_at列创建索引
CREATE INDEX idx_created_at ON fgedu_users(created_at);
# 再次执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’ ORDER BY id DESC;
+—-+————-+————+——-+—————+—————-+———+——+——+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+—————+—————-+———+——+——+—————————–+
| 1 | SIMPLE | fgedu_users | range | idx_created_at | idx_created_at | 5 | NULL | 500 | Using where;
Using filesort |
+—-+————-+————+——-+—————+—————-+———+——+——+—————————–+
# 进一步优化：使用复合索引
CREATE INDEX idx_created_at_id ON fgedu_users(created_at, id);
# 再次执行计划
EXPLAIN SELECT * FROM fgedu_users WHERE created_at > ‘2023-01-01’ ORDER BY id DESC;
+—-+————-+————+——-+—————+——————-+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+—————+——————-+———+——+——+————————–+
| 1 | SIMPLE | fgedu_users | range | idx_created_at,idx_created_at_id | idx_created_at_id | 5 | NULL | 500 | Using where;
Using index |
+—-+————-+————+——-+—————+——————-+———+——+——+————————–+

执行结果：

# 原始SQL执行时间
1.234 seconds
# 创建索引后执行时间
0.345 seconds
# 使用复合索引后执行时间
0.123 seconds

4.2 索引设计案例

场景描述：为一个电商网站的订单表设计索引。

# 创建订单表
MariaDB [(none)]> CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
order_no VARCHAR(50),
total_amount DECIMAL(10,2),
status VARCHAR(20),
created_at DATETIME,
updated_at DATETIME
) ENGINE=InnoDB;
# 为经常查询的列创建索引
# 1. 为user_id创建索引（用于查询用户的所有订单）
CREATE INDEX idx_user_id ON fgedu_orders(user_id);
# 2. 为order_no创建索引（用于根据订单号查询）
CREATE INDEX idx_order_no ON fgedu_orders(order_no);
# 3. 为status创建索引（用于根据状态查询）
CREATE INDEX idx_status ON fgedu_orders(status);
# 4. 为created_at创建索引（用于按时间查询）
CREATE INDEX idx_created_at ON fgedu_orders(created_at);
# 5. 为经常组合查询的列创建复合索引
CREATE INDEX idx_user_id_status ON fgedu_orders(user_id, status);
# 查看索引
SHOW INDEX FROM fgedu_orders;

执行结果：

+————+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+————+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+
| fgedu_orders | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
| fgedu_orders | 1 | idx_user_id | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | |
| fgedu_orders | 1 | idx_order_no | 1 | order_no | A | 0 | NULL | NULL | YES | BTREE | |
| fgedu_orders | 1 | idx_status | 1 | status | A | 0 | NULL | NULL | YES | BTREE | |
| fgedu_orders | 1 | idx_created_at | 1 | created_at | A | 0 | NULL | NULL | YES | BTREE | |
| fgedu_orders | 1 | idx_user_id_status | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | |
| fgedu_orders | 1 | idx_user_id_status | 2 | status | A | 0 | NULL | NULL | YES | BTREE | |
+————+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+

4.3 存储过程开发案例

场景描述：开发一个存储过程，用于统计用户订单数量和总金额。

# 创建存储过程
MariaDB [(none)]> DELIMITER //
CREATE PROCEDURE get_user_order_stats(IN user_id INT, OUT order_count INT, OUT total_amount DECIMAL(10,2))
BEGIN
SELECT COUNT(*), SUM(total_amount) INTO order_count, total_amount FROM fgedu_orders WHERE user_id = user_id;
END //
DELIMITER;
# 调用存储过程
CALL get_user_order_stats(1, @order_count, @total_amount);
SELECT @order_count AS order_count, @total_amount AS total_amount;
# 创建存储过程，用于插入订单
DELIMITER //
CREATE PROCEDURE insert_order(IN p_user_id INT, IN p_order_no VARCHAR(50), IN p_total_amount DECIMAL(10,2), IN p_status VARCHAR(20))
BEGIN
INSERT INTO fgedu_orders(user_id, order_no, total_amount, status, created_at, updated_at)
VALUES(p_user_id, p_order_no, p_total_amount, p_status, NOW(), NOW());
END //
DELIMITER;
# 调用存储过程插入订单
CALL insert_order(1, ‘ORD20230101001’, 100.00, ‘pending’);
# 验证插入结果
SELECT * FROM fgedu_orders WHERE order_no = ‘ORD20230101001’;

执行结果：

# 调用存储过程获取订单统计
+————+————–+
| order_count | total_amount |
+————+————–+
| 5 | 500.00 |
+————+————–+
# 调用存储过程插入订单
Query OK, 1 row affected (0.00 sec)
# 验证插入结果
+—-+———+—————+————–+———+———————+———————+
| id | user_id | order_no | total_amount | status | created_at | updated_at |
+—-+———+—————+————–+———+———————+———————+
| 6 | 1 | ORD20230101001 | 100.00 | pending | 2023-01-01 10:00:00 | 2023-01-01 10:00:00 |
+—-+———+—————+————–+———+———————+———————+
风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 SQL开发最佳实践

风哥提示：在SQL开发过程中，应遵循最佳实践，提高代码的质量、性能和可维护性。
  • 使用参数化查询：避免SQL注入攻击
  • 优化SQL语句：使用EXPLAIN分析执行计划，优化查询语句
  • 合理使用索引：为经常查询的列创建索引，避免过度索引
  • 使用存储过程：将复杂的业务逻辑封装在存储过程中
  • 事务管理：合理使用事务，确保数据一致性

5.2 性能优化技巧

  • 使用合适的存储引擎：根据业务需求选择合适的存储引擎
  • 优化表结构：合理设计表结构，避免冗余字段
  • 分区表：对于大表，使用分区表提高查询性能
  • 使用缓存：使用查询缓存或应用层缓存
  • 定期维护：定期执行OPTIMIZE TABLE和ANALYZE TABLE

5.3 常见问题与解决方案

  • 慢查询：使用EXPLAIN分析执行计划，优化SQL语句和索引
  • 死锁：合理设计事务，避免长时间持有锁
  • 索引失效：避免在索引列上使用函数，使用合适的索引类型
  • 内存不足：优化配置参数，增加内存
  • 并发问题：使用合适的事务隔离级别，优化并发控制
# SQL开发规范示例
— 创建表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
— 创建索引
CREATE INDEX idx_username ON fgedu_users(username);
CREATE INDEX idx_email ON fgedu_users(email);
— 优化查询
SELECT id, username, email
FROM fgedu_users
WHERE created_at BETWEEN ‘2023-01-01’ AND ‘2023-12-31’
ORDER BY created_at DESC
LIMIT 10;
— 存储过程
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
SELECT id, username, email
FROM fgedu_users
WHERE id = p_id;
END //
DELIMITER;

通过以上措施，可以有效提高MariaDB SQL开发的质量和性能，确保系统稳定运行。

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息