MySQL 数据库教程：从建库到索引优化

FreeGuideOnline 最新 2026-06-13

MySQL 数据库教程：从建库到索引优化

欢迎来到免费在线教程的MySQL实战页面。本教程将带你从零开始，掌握关系型数据库的核心操作，包括建库、建表、数据增删改查、多表查询以及最关键的索引优化技巧。无论你是开发新手还是准备面试，跟随示例动手实践，就能快速上手。

1. 环境准备与连接数据库

在开始之前，请确保你的电脑上已经安装了 MySQL 服务端（例如 MySQL Community Server 或集成的 XAMPP/WAMP 环境）。推荐使用命令行终端操作，也可以使用图形化工具如 Navicat、DBeaver 或 phpMyAdmin。

使用以下命令连接到本地 MySQL 服务（-u 后跟用户名，-p 提示输入密码）：

mysql -u root -p

连接成功后，你会看到 mysql> 提示符，现在就可以输入SQL语句了。所有SQL语句以分号 ; 结尾。

2. 数据库（Database）基本操作

数据库就像一个大容器，存放着所有的数据表。

2.1 创建数据库

CREATE DATABASE my_shop;

创建一个名为 my_shop 的数据库。如果担心数据库已存在导致报错，可以使用 IF NOT EXISTS：

CREATE DATABASE IF NOT EXISTS my_shop;

2.2 查看与选择数据库

SHOW DATABASES;               -- 查看所有数据库
USE my_shop;                  -- 切换到 my_shop 数据库

一旦执行 USE，之后的所有操作都在该数据库中生效。

2.3 删除数据库

DROP DATABASE my_shop;        -- 慎用，会永久删除所有数据

3. 数据表（Table）设计与创建

表是数据库中实际存储数据的结构。设计表时需要考虑列名、数据类型和约束。

3.1 常用数据类型

  • INT：整数，如用户ID
  • VARCHAR(n)：可变长度字符串，n为最大字符数
  • TEXT：长文本，如文章内容
  • DATE：日期 YYYY-MM-DD
  • DATETIME：日期时间 YYYY-MM-DD HH:MM:SS
  • DECIMAL(m,d)：精确小数，m总位数，d小数位数，适合存储金额

3.2 建表示例：用户表与订单表

-- 用户表
CREATE TABLE users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL,
    age         INT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 订单表，通过 user_id 关联 users 表
CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT NOT NULL,
    product     VARCHAR(100) NOT NULL,
    price       DECIMAL(10,2),
    order_date  DATE
);

约束解释：

  • PRIMARY KEY：主键，唯一标识每一行，自动创建索引
  • AUTO_INCREMENT：自增整数，常用于主键
  • NOT NULL：列值不能为空
  • UNIQUE：值必须唯一
  • DEFAULT：设置默认值

3.3 查看表结构

SHOW TABLES;          -- 查看当前数据库所有表
DESCRIBE users;       -- 查看 users 表的结构

4. 数据操作：增删改查（CRUD）

4.1 插入数据（INSERT）

-- 插入单行
INSERT INTO users (username, email, age) 
VALUES ('张三', 'zhangsan@example.com', 25);

-- 插入多行
INSERT INTO users (username, email, age) VALUES 
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);

4.2 查询数据（SELECT）

-- 查询所有列（生产环境避免使用 *）
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 条件查询（WHERE）
SELECT * FROM users WHERE age > 25;

-- 模糊查询（LIKE）
SELECT * FROM users WHERE username LIKE '张%';   -- 以“张”开头

-- 排序（ORDER BY）与限制数量（LIMIT）
SELECT * FROM users ORDER BY age DESC LIMIT 3;

4.3 更新数据（UPDATE）

UPDATE users SET age = 26 WHERE username = '张三';

务必带上 WHERE 条件，否则会修改表中所有行。

4.4 删除数据（DELETE）

DELETE FROM users WHERE id = 3;

同样必须使用 WHERE 精确删除，否则表会被清空。若想快速清空整张表并重置自增ID，可用 TRUNCATE TABLE users;。

5. 查询进阶：聚合与多表关联

5.1 聚合函数与分组

-- 统计用户总数
SELECT COUNT(*) AS total_users FROM users;

-- 按年龄分组统计人数
SELECT age, COUNT(*) AS num FROM users GROUP BY age;

-- 分组后过滤（HAVING）
SELECT age, COUNT(*) AS num 
FROM users 
GROUP BY age 
HAVING num > 1;

5.2 多表连接（JOIN）

连接是关系型数据库的核心力量，能够通过主键-外键关系组合多张表的数据。

INNER JOIN（内连接）：只返回两个表中匹配的行

SELECT u.username, o.product, o.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左连接）：返回左表所有行，即使右表没有匹配

SELECT u.username, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

如果用户没有订单，product 列显示为 NULL。

RIGHT JOIN 同理，保留右表所有行。

5.3 子查询

在查询中嵌套另一个查询：

SELECT * FROM orders 
WHERE user_id IN (
  SELECT id FROM users WHERE age > 25
);

6. 索引优化：让查询快百倍

当数据量达到几万、几十万行时，不加索引的查询会进行全表扫描，性能极差。索引就像书的目录，能极大加速数据查找。

6.1 理解索引

  • MySQL 常见索引类型：B+Tree 索引（默认）、哈希索引（Memory引擎）、全文索引等。
  • 主键会自动创建聚簇索引，数据按照主键顺序物理存储。
  • 普通索引和唯一索引为二级索引，叶子节点存放主键值。

6.2 创建索引

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引（避免重复）
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引（多列联合）
CREATE INDEX idx_user_age ON users(username, age);

-- 在建表时直接定义
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    INDEX idx_name_price (name, price)
);

最左前缀原则：复合索引 (username, age) 可用于查询中只包含 username 的条件，但不能跳过 username 直接查 age。

6.3 查看索引

SHOW INDEX FROM users;

6.4 使用 EXPLAIN 分析查询

在 SELECT 语句前加上 EXPLAIN，观察执行计划，尤其是 type、key、rows 等字段：

EXPLAIN SELECT * FROM users WHERE username = '张三';
  • type 最好是 const、eq_ref 或 ref，避免 ALL（全表扫描）。
  • key 显示实际使用的索引，若为 NULL 则未使用索引。

6.5 索引优化黄金法则

  1. 为 WHERE、ORDER BY、JOIN 的列建立索引，尤其是频繁作为查询条件的列。
  2. 避免在索引列上使用函数或计算，例如 WHERE YEAR(created_at) = 2025 会导致索引失效，应改为 WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'。
  3. 避免使用前导模糊查询，LIKE '%张三' 无法使用索引，而 LIKE '张三%' 可以。
  4. 选择性高的列适合建索引，区分度越大越好（比如用户ID优于性别）。
  5. 复合索引注意列顺序，将使用最频繁、区分度高的列放在前面。
  6. 不要创建过多的索引，索引会占用磁盘空间，并降低写操作（INSERT/UPDATE/DELETE）的速度。
  7. 使用覆盖索引：如果查询的所有列都包含在索引中，MySQL 可以直接从索引返回数据，无需回表，性能极高。

真实场景优化示例：

-- 慢查询
SELECT * FROM orders WHERE order_date > '2025-01-01' AND price > 100;

-- 先建复合索引
CREATE INDEX idx_date_price ON orders(order_date, price);

-- 再用 EXPLAIN 验证，type 应为 range
EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01' AND price > 100;

7. 总结与后续学习

本篇教程覆盖了从数据库创建、表结构设计、数据操作、多表查询到索引优化的完整链路。掌握这些知识后，你已经具备独立开发中小型应用数据库的能力。

加快成长的建议：

  • 动手在本地搭建一个 MySQL 环境，把每个示例都敲一遍。
  • 尝试模拟一个电商场景（用户、商品、订单、分类），编写各种查询。
  • 学习数据库设计范式和慢查询日志分析。
  • 深入了解 InnoDB 存储引擎的事务、锁机制，以及 MySQL 8.0 的新特性。

持续练习，你很快就能成为 SQL 高手。如果你喜欢本教程，欢迎收藏并分享给需要的小伙伴。免费在线教程，与你一起进步！