整理:熟悉MySQL的使用和运行原理,掌握索引、事务、锁等机制。了解存储引擎、读写分离、分库分表。

发布于:2025-02-10 ⋅ 阅读:(36) ⋅ 点赞:(0)

系列博客目录



1.MySQL的运行原理

MySQL 是一个基于客户端-服务器架构的关系型数据库管理系统,通过 SQL 查询处理客户端请求。其核心包括查询解析、优化、执行和数据存储管理。MySQL 使用存储引擎(如 InnoDB、MyISAM)来管理数据,并通过索引、事务管理和锁机制来优化性能和保证数据一致性。同时,MySQL 通过日志机制支持数据恢复和复制,提高系统的可靠性和扩展性。

2.MySQL的使用

好的,接下来我会带你进行一个小的实战项目,创建一个简单的数据库和表,进行基本的增、查、改、删操作,并且创建索引和视图。假设我们在开发一个在线商店的数据库。

步骤 1:创建数据库

首先,我们需要创建一个数据库来存储商店相关的数据:

CREATE DATABASE online_store;

使用 USE 语句来选择我们刚才创建的数据库:

USE online_store;

步骤 2:创建表

我们会创建两张表:

  1. users:存储用户信息。
  2. products:存储商品信息。

2.1 创建 users

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • id:自动递增且为主键。
  • name:用户名,不能为空。
  • email:用户邮箱,必须唯一。
  • created_at:记录创建时间。

2.2 创建 products

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • id:自动递增且为主键。
  • name:商品名称,不能为空。
  • description:商品描述。
  • price:商品价格。
  • stock:商品库存,默认为 0。
  • created_at:记录创建时间。

步骤 3:插入数据

我们向这两张表中插入一些数据。

3.1 向 users 表插入数据

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');

3.2 向 products 表插入数据

INSERT INTO products (name, description, price, stock) VALUES
('Laptop', 'High-performance laptop', 1200.00, 10),
('Smartphone', 'Latest model smartphone', 800.00, 50),
('Headphones', 'Noise-canceling headphones', 150.00, 100);

步骤 4:查询数据

我们可以执行一些简单的查询,查看数据是否插入成功。

4.1 查询所有用户

SELECT * FROM users;

4.2 查询所有商品

SELECT * FROM products;

4.3 查询特定用户(比如查询邮箱为 bob@example.com 的用户)

SELECT * FROM users WHERE email = 'bob@example.com';

4.4 查询库存大于 20 的商品

SELECT * FROM products WHERE stock > 20;

步骤 5:更新数据

假设用户想要更新某个商品的价格,或者修改某个用户的信息。

5.1 更新商品价格

将商品 Laptop 的价格更新为 1100.00:

UPDATE products
SET price = 1100.00
WHERE name = 'Laptop';

5.2 更新用户邮箱

Bob 的邮箱修改为 bob.new@example.com

UPDATE users
SET email = 'bob.new@example.com'
WHERE name = 'Bob';

步骤 6:删除数据

如果我们要删除某些数据,比如删除一个用户或商品,可以使用 DELETE 语句。

6.1 删除某个商品(删除 Headphones 商品)

DELETE FROM products WHERE name = 'Headphones';

6.2 删除某个用户(删除 Charlie 用户)

DELETE FROM users WHERE name = 'Charlie';

步骤 7:创建索引

为了提高查询性能,我们可以为 products 表的 price 列和 users 表的 email 列创建索引,尤其是当这些列经常用于查询时。

7.1 为 products 表的 price 列创建索引

CREATE INDEX idx_price ON products (price);

7.2 为 users 表的 email 列创建唯一索引

CREATE UNIQUE INDEX idx_email ON users (email);

步骤 8:创建视图

假设我们想要快速查看那些价格在 500 以上且库存大于 10 的商品,可以创建一个视图来简化查询。

CREATE VIEW expensive_products AS
SELECT id, name, price, stock
FROM products
WHERE price > 500 AND stock > 10;

查询该视图:

SELECT * FROM expensive_products;

步骤 9:删除数据、表和数据库

当我们不再需要某些数据或表时,可以删除它们。

9.1 删除某个表(例如删除 products 表)

DROP TABLE products;

9.2 删除数据库

如果删除整个数据库及其所有表,可以使用:

DROP DATABASE online_store;

小结

通过这个小的实战,我们完成了:

  1. 创建数据库和表。
  2. 插入、查询、更新和删除数据。
  3. 创建索引以提高查询效率。
  4. 创建视图简化查询。
  5. 删除数据和表,管理数据库。

这些是 MySQL 中最常用的操作,掌握了这些后,你就能处理大部分数据库操作了。

3.索引

1.1 索引概述

索引是数据库中一种用于提高查询速度的数据结构。它可以加速数据的查找过程,类似于书
籍中的目录,可以快速找到目标数据。

1.2 索引类型

B-Tree 索引:MySQL 中最常见的索引类型,尤其是在 InnoDB 存储引擎中。B-Tree 索引是一个平衡树结构,适合范围查询、排序和唯一性检查。
Hash 索引:基于哈希表实现的索引,适用于精确匹配查询。其查询时间复杂度为 O(1),但不支持范围查询。
Full-Text 索引:全文索引用于加速文本字段中的关键词搜索。
R-Tree 索引:主要用于空间数据的存储,例如地理信息系统 (GIS) 中的坐标数据。

1.3 索引的底层原理

B+树:MySQL 的 B-Tree 索引实际上是 B+树的实现。B+树是一种多路平衡查找树,非叶子节点只存储键值,不存储数据,而数据存储在叶子节点中。叶子节点通过链表连接,支持高效的范围查询和排序操作。

1.4 索引的优缺点

优点:提高查询速度,特别是在大表中。通过唯一性索引来防止数据重复。
缺点:占用存储空间,尤其是多列组合索引。插入、更新、删除操作时需要维护索引,增加了开销。

4.

5.

6.

7.

8.

9.

10.


网站公告

今日签到

点亮在社区的每一天
去签到