MySql入门：MySQL数据类型与表设计

MySQL数据类型与表设计

在数据库设计中，选择合适的数据类型和设计良好的表结构是构建高性能应用的基石。今天，我们将深入探讨MySQL的数据类型选择策略、表设计原则以及索引优化技巧，帮助你构建既高效又可维护的数据库结构。

1. 数据类型深度解析

数值类型：整型、浮点型、定点数的选择策略

整型数据类型对比：

类型	存储空间	有符号范围	无符号范围	适用场景
TINYINT	1字节	-128 到 127	0 到 255	状态标志、年龄、小范围计数
SMALLINT	2字节	-32,768 到 32,767	0 到 65,535	端口号、中等范围计数
MEDIUMINT	3字节	-8,388,608 到 8,388,607	0 到 16,777,215	用户ID、文章ID
INT	4字节	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295	订单ID、大范围计数
BIGINT	8字节	-2^63 到 2^63-1	0 到 2^64-1	分布式ID、极大范围计数

数值类型选择实战：

-- 用户表 - 合理的数值类型选择
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    age TINYINT UNSIGNED COMMENT '年龄',
    status TINYINT DEFAULT 1 COMMENT '状态:1正常,0禁用',
    login_count INT UNSIGNED DEFAULT 0 COMMENT '登录次数',
    balance DECIMAL(10,2) UNSIGNED DEFAULT 0.00 COMMENT '账户余额'
) COMMENT='用户表';

-- 订单表 - 货币相关使用DECIMAL
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
    tax_amount DECIMAL(10,2) NOT NULL COMMENT '税费',
    discount_amount DECIMAL(8,2) DEFAULT 0.00 COMMENT '折扣金额',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='订单表';

浮点数与定点数选择：

-- 科学计算 - 使用浮点数
CREATE TABLE sensor_data (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT COMMENT '温度，允许精度损失',
    pressure DOUBLE COMMENT '压力，更高精度',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 金融计算 - 必须使用DECIMAL
CREATE TABLE financial_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_amount DECIMAL(15,4) NOT NULL, -- 精确计算
    exchange_rate DECIMAL(10,6) NOT NULL,      -- 汇率需要高精度
    calculated_amount DECIMAL(15,4) AS (transaction_amount * exchange_rate)
);

字符串类型：CHAR、VARCHAR、TEXT的应用场景

字符串类型对比分析：

类型	最大长度	存储特点	适用场景	性能影响
CHAR(N)	255字符	定长，不足补空格	固定长度数据(MD5、UUID)	读取快，可能浪费空间
VARCHAR(N)	65,535字节	变长，额外1-2字节存储长度	用户名、邮箱、地址	空间效率高，读取稍慢
TINYTEXT	255字节	变长，不支持默认值	短文本描述	类似VARCHAR
TEXT	65,535字节	变长，存储较大文本	文章内容、评论	可能产生临时表
MEDIUMTEXT	16MB	变长，大文本存储	大型文档、日志	影响查询性能
LONGTEXT	4GB	变长，极大文本存储	二进制数据、历史记录	谨慎使用

字符串类型实战应用：

-- 用户表 - 合理的字符串类型选择
CREATE TABLE user_profiles (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(255) NOT NULL UNIQUE COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号，固定11位',
    id_card CHAR(18) COMMENT '身份证号，固定18位',
    avatar_url VARCHAR(500) COMMENT '头像URL',
    bio TEXT COMMENT '个人简介，可变长文本',
    
    -- 索引优化
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) COMMENT='用户档案表';

-- 文章表 - 大文本处理
CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL COMMENT '文章标题',
    summary VARCHAR(500) COMMENT '文章摘要',
    content LONGTEXT NOT NULL COMMENT '文章内容',
    tags VARCHAR(300) COMMENT '标签，逗号分隔',
    
    -- 全文索引
    FULLTEXT idx_content (title, summary, content),
    INDEX idx_created (created_at)
) COMMENT='文章表';

日期时间类型：DATETIME、TIMESTAMP、DATE的差异

日期时间类型深度对比：

类型	存储空间	范围	时区处理	自动更新	适用场景
DATE	3字节	1000-01-01 到 9999-12-31	无	不支持	生日、日期
TIME	3字节	-838:59:59 到 838:59:59	无	不支持	持续时间
DATETIME	8字节	1000-01-01 00:00:00 到 9999-12-31 23:59:59	无	不支持	创建时间、日志时间
TIMESTAMP	4字节	1970-01-01 00:00:01 到 2038-01-19 03:14:07 UTC	自动转换	支持	更新时间、系统时间
YEAR	1字节	1901 到 2155	无	不支持	年份

日期时间类型实战：

-- 时间字段设计最佳实践
CREATE TABLE time_demo (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 创建时间 - 使用DATETIME，不涉及时区转换
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    
    -- 更新时间 - 使用TIMESTAMP，自动更新
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                 ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
    
    -- 业务时间 - 使用DATE
    birth_date DATE COMMENT '出生日期',
    event_date DATE COMMENT '事件日期',
    
    -- 时间范围 - 使用TIME
    start_time TIME COMMENT '开始时间',
    end_time TIME COMMENT '结束时间',
    
    -- 索引优化
    INDEX idx_created (created_at),
    INDEX idx_updated (updated_at),
    INDEX idx_event_date (event_date)
);

-- 时间查询优化示例
SELECT * FROM time_demo 
WHERE created_at >= '2023-01-01 00:00:00' 
  AND created_at < '2023-02-01 00:00:00';
  
SELECT * FROM time_demo 
WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 时间函数使用
SELECT 
    id,
    created_at,
    DATE(created_at) as create_date,
    HOUR(created_at) as create_hour,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') as formatted_time
FROM time_demo;

JSON数据类型：现代应用的数据存储方案

JSON类型优势与应用场景：

-- 动态schema数据存储
CREATE TABLE product_catalog (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    basic_info JSON NOT NULL COMMENT '基础信息',
    specifications JSON COMMENT '规格参数',
    metadata JSON COMMENT '元数据',
    
    -- 生成列 + 索引
    product_name VARCHAR(200) 
        GENERATED ALWAYS AS (basic_info->>'$.name') VIRTUAL,
    price DECIMAL(10,2)
        GENERATED ALWAYS AS (JSON_UNQUOTE(basic_info->'$.price')) VIRTUAL,
    
    -- 索引
    INDEX idx_sku (sku),
    INDEX idx_product_name (product_name),
    INDEX idx_price (price)
) COMMENT='商品目录表';

-- JSON数据插入示例
INSERT INTO product_catalog (sku, basic_info, specifications) 
VALUES (
    'IPHONE14-128-BLACK',
    '{
        "name": "iPhone 14",
        "brand": "Apple",
        "price": 5999.00,
        "color": "黑色",
        "weight": 172
    }',
    '{
        "screen": {"size": 6.1, "type": "OLED"},
        "storage": 128,
        "camera": {"main": "48MP", "front": "12MP"},
        "battery": 3279
    }'
);

-- JSON查询操作
SELECT 
    sku,
    basic_info->>'$.name' as product_name,
    JSON_UNQUOTE(basic_info->'$.brand') as brand,
    specifications->'$.screen.size' as screen_size,
    
    -- JSON路径查询
    JSON_EXTRACT(basic_info, '$.price') as price,
    
    -- JSON包含检查
    JSON_CONTAINS_PATH(basic_info, 'one', '$.color') as has_color,
    
    -- JSON数组操作
    JSON_LENGTH(COALESCE(metadata->'$.tags', '[]')) as tag_count
    
FROM product_catalog
WHERE basic_info->>'$.brand' = 'Apple'
  AND specifications->'$.screen.size' > 6.0;

-- JSON更新操作
UPDATE product_catalog 
SET basic_info = JSON_SET(
    basic_info,
    '$.price', 5799.00,
    '$.discount', true
)
WHERE sku = 'IPHONE14-128-BLACK';

-- JSON索引优化（MySQL 8.0+）
CREATE INDEX idx_brand ON product_catalog((basic_info->>'$.brand'));
CREATE INDEX idx_screen_size ON product_catalog(
    (CAST(specifications->'$.screen.size' AS UNSIGNED))
);

空间数据类型：GIS应用支持

空间数据类型应用：

-- 地理位置数据存储
CREATE TABLE locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    
    -- 空间数据类型
    point_coord POINT NOT NULL COMMENT '点坐标',
    area_boundary POLYGON COMMENT '区域边界',
    route_path LINESTRING COMMENT '路线路径',
    
    -- 空间索引
    SPATIAL INDEX idx_point (point_coord),
    SPATIAL INDEX idx_area (area_boundary),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='地理位置表';

-- 空间数据插入
INSERT INTO locations (name, point_coord, area_boundary) 
VALUES (
    '公司总部',
    ST_GeomFromText('POINT(116.3974 39.9093)'),
    ST_GeomFromText('POLYGON((116.396 39.908, 116.398 39.908, 116.398 39.910, 116.396 39.910, 116.396 39.908))')
);

-- 空间查询
SELECT 
    name,
    ST_AsText(point_coord) as coordinates,
    ST_Distance_Sphere(
        point_coord, 
        ST_GeomFromText('POINT(116.4074 39.9042)')
    ) as distance_meters
FROM locations
WHERE ST_Contains(
    area_boundary, 
    ST_GeomFromText('POINT(116.3974 39.9093)')
);

-- 附近查询优化
SELECT 
    name,
    ST_Distance_Sphere(point_coord, @user_point) as distance
FROM locations
WHERE ST_Distance_Sphere(point_coord, @user_point) < 5000  -- 5公里内
ORDER BY distance ASC
LIMIT 10;

2. 表设计与规范化

数据库设计三大范式实战

第一范式（1NF） - 原子性：

-- 违反1NF的设计
CREATE TABLE bad_design (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(100),
    phone_numbers VARCHAR(500) -- 存储多个电话号码，用逗号分隔
);

-- 符合1NF的设计
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL
);

CREATE TABLE user_phones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    phone_type ENUM('mobile', 'home', 'work') NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    UNIQUE KEY unique_user_phone (user_id, phone_number)
);

第二范式（2NF） - 完全依赖：

-- 违反2NF的设计（订单明细包含产品信息）
CREATE TABLE order_details_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖于product_id，而不是完全依赖于主键
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- 符合2NF的设计
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATETIME,
    customer_id INT,
    total_amount DECIMAL(12,2)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    unit_price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL, -- 下单时的价格
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三范式（3NF） - 无传递依赖：

-- 违反3NF的设计
CREATE TABLE employees_bad (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    dept_name VARCHAR(100),  -- 传递依赖于emp_id，通过dept_id
    manager_name VARCHAR(100)
);

-- 符合3NF的设计
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    manager_id INT
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

反范式设计的适用场景

读写分离场景的反范式优化：

-- 报表查询优化 - 反范式设计
CREATE TABLE user_statistics (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(100),
    total_orders INT DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    last_order_date DATETIME,
    favorite_category VARCHAR(50),
    
    -- 定期更新的统计字段
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_total_amount (total_amount),
    INDEX idx_last_order (last_order_date)
) COMMENT='用户统计表（反范式设计）';

-- 订单列表查询优化
CREATE TABLE order_summary (
    order_id INT PRIMARY KEY,
    order_number VARCHAR(50),
    customer_id INT,
    customer_name VARCHAR(100),  -- 反范式：冗余存储
    total_amount DECIMAL(12,2),
    status ENUM('pending', 'paid', 'shipped', 'completed'),
    created_at DATETIME,
    
    -- 复合索引支持多种查询
    INDEX idx_customer_status (customer_id, status),
    INDEX idx_created_status (created_at, status),
    INDEX idx_customer_created (customer_id, created_at)
) COMMENT='订单汇总表（反范式设计）';

计数器场景的优化：

-- 高频更新计数器的优化设计
CREATE TABLE post_counters (
    post_id INT PRIMARY KEY,
    view_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    share_count INT DEFAULT 0,
    
    -- 定期同步到主表，减少主表更新压力
    last_sync_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 计数更新（高频操作）
UPDATE post_counters 
SET view_count = view_count + 1 
WHERE post_id = 1234;

-- 定期同步到文章主表
UPDATE posts p
JOIN post_counters pc ON p.id = pc.post_id
SET p.view_count = pc.view_count,
    p.like_count = pc.like_count
WHERE pc.last_sync_at < NOW() - INTERVAL 1 HOUR;

表关系设计：一对一、一对多、多对多

一对一关系设计：

-- 用户基础信息与详细信息的垂直分表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='用户基础表';

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    full_name VARCHAR(100),
    birth_date DATE,
    gender ENUM('M', 'F', 'O'),
    avatar_url VARCHAR(500),
    bio TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) COMMENT='用户详情表（一对一）';

一对多关系设计：

-- 用户与订单的一对多关系
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='客户表';

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'),
    
    -- 外键约束
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    
    -- 查询优化索引
    INDEX idx_customer_date (customer_id, order_date),
    INDEX idx_status_date (status, order_date)
) COMMENT='订单表（一对多）';

多对多关系设计：

-- 文章与标签的多对多关系
CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    author_id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    published_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_author_status (author_id, status),
    INDEX idx_published (published_at)
) COMMENT='文章表';

CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(50) UNIQUE NOT NULL,
    tag_slug VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='标签表';

CREATE TABLE article_tags (
    article_id INT NOT NULL,
    tag_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 复合主键
    PRIMARY KEY (article_id, tag_id),
    
    -- 外键约束
    FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE,
    
    -- 双向查询优化
    INDEX idx_tag_article (tag_id, article_id)
) COMMENT='文章标签关联表（多对多）';

字段选择与数据类型优化

枚举与集合类型的选择：

-- 使用ENUM替代字符串
CREATE TABLE tasks (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    
    -- ENUM存储为数字，查询效率高
    INDEX idx_priority_status (priority, status)
);

-- 使用SET存储多选项
CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    notification_types SET('email', 'sms', 'push', 'in_app') DEFAULT 'email',
    language SET('zh_CN', 'en_US', 'ja_JP') DEFAULT 'zh_CN',
    
    -- SET查询示例
    CHECK (JSON_LENGTH(language) > 0) -- 至少选择一种语言
);

-- SET查询技巧
SELECT * FROM user_preferences 
WHERE FIND_IN_SET('sms', notification_types) > 0;

SELECT * FROM user_preferences 
WHERE notification_types = 'email,sms'; -- 精确匹配

默认值与约束优化：

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    
    -- 合理的默认值
    status TINYINT DEFAULT 1,
    stock_quantity INT DEFAULT 0,
    min_stock_level INT DEFAULT 10,
    
    -- 检查约束（MySQL 8.0.16+）
    price DECIMAL(10,2) CHECK (price >= 0),
    weight_kg DECIMAL(8,3) CHECK (weight_kg > 0),
    
    -- 时间戳默认值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 计算列（MySQL 5.7+）
    need_reorder BOOLEAN GENERATED ALWAYS AS (stock_quantity <= min_stock_level),
    
    -- 索引优化
    INDEX idx_sku_status (sku, status),
    INDEX idx_stock (stock_quantity),
    INDEX idx_need_reorder (need_reorder)
);

3. 索引设计原理与优化

B+Tree索引原理深度解析

B+Tree结构特点：

B+Tree结构：
├── 根节点 (Root Node)
├── 内部节点 (Internal Nodes)
└── 叶子节点 (Leaf Nodes)
    ├── 数据页指针
    ├── 键值对（有序）
    └── 相邻叶子节点指针

B+Tree优势分析：

• 平衡树结构：所有叶子节点在同一层，查询稳定
• 顺序访问：叶子节点链表支持范围查询
• 高扇出：减少树高度，提高查询效率
• 数据集中：数据只存储在叶子节点

聚簇索引与非聚簇索引

聚簇索引（InnoDB）：

-- InnoDB表的聚簇索引（通常是主键）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,          -- 聚簇索引
    emp_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    
    -- 数据按emp_id物理排序存储
    INDEX idx_department (department_id)  -- 非聚簇索引
);

-- 没有主键时，InnoDB的处理
CREATE TABLE logs (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    log_message TEXT,
    created_at TIMESTAMP,
    
    -- 如果没有主键，InnoDB会：
    -- 1. 找第一个UNIQUE NOT NULL列
    -- 2. 否则创建隐藏的_rowid列作为聚簇索引
    UNIQUE KEY uk_id (id)
);

非聚簇索引（二级索引）结构：

-- 二级索引包含主键值
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,           -- 聚簇索引键
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12,2),
    
    -- 二级索引存储(customer_id, order_id)
    INDEX idx_customer_date (customer_id, order_date),
    
    -- 覆盖索引示例
    INDEX idx_customer_amount (customer_id, total_amount)
);

-- 二级索引查询过程
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1234 AND order_date = '2023-01-01';
-- 1. 在idx_customer_date找到(order_id)
-- 2. 用order_id回表查询完整数据

复合索引设计与最左前缀原则

复合索引设计原则：

-- 用户行为日志表 - 复合索引设计
CREATE TABLE user_actions (
    user_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    action_time DATETIME NOT NULL,
    device_type ENUM('web', 'ios', 'android'),
    page_url VARCHAR(500),
    
    -- 复合索引设计
    PRIMARY KEY (user_id, action_time, action_type),
    INDEX idx_time_type (action_time, action_type),
    INDEX idx_type_time (action_type, action_time),
    INDEX idx_user_type_time (user_id, action_type, action_time)
);

-- 最左前缀原则验证
EXPLAIN SELECT * FROM user_actions 
WHERE user_id = 1234;  -- ✅ 使用索引

EXPLAIN SELECT * FROM user_actions 
WHERE user_id = 1234 AND action_time > '2023-01-01';  -- ✅ 使用索引

EXPLAIN SELECT * FROM user_actions 
WHERE action_time > '2023-01-01';  -- ❌ 无法使用主键索引

EXPLAIN SELECT * FROM user_actions 
WHERE user_id = 1234 AND action_type = 'login';  -- ✅ 使用索引

索引选择性优化：

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT user_id) as distinct_users,
    COUNT(*) as total_records,
    ROUND(COUNT(DISTINCT user_id) / COUNT(*), 4) as selectivity
FROM user_actions;

-- 低选择性索引示例（不推荐）
CREATE TABLE low_selectivity_demo (
    gender ENUM('M', 'F'),           -- 选择性差
    status TINYINT DEFAULT 1,        -- 选择性差
    created_date DATE,               -- 选择性随时间变差
    
    -- 不推荐的索引
    INDEX idx_gender (gender),
    
    -- 推荐的复合索引
    INDEX idx_status_date (status, created_date),
    INDEX idx_gender_date (gender, created_date)
);

覆盖索引与索引下推优化

覆盖索引优化：

-- 覆盖索引设计
CREATE TABLE sales (
    sale_id BIGINT PRIMARY KEY,
    product_id BIGINT NOT NULL,
    sale_date DATE NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    customer_id BIGINT NOT NULL,
    
    -- 覆盖索引：包含查询所需的所有列
    INDEX idx_product_date (product_id, sale_date),
    INDEX idx_customer_product (customer_id, product_id, quantity, unit_price),
    INDEX idx_date_customer (sale_date, customer_id, product_id)
);

-- 覆盖索引查询示例
EXPLAIN 
SELECT product_id, SUM(quantity) as total_quantity
FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY product_id;
-- ✅ 使用idx_date_customer，不需要回表

EXPLAIN
SELECT customer_id, product_id, quantity, unit_price
FROM sales 
WHERE customer_id = 1234 AND product_id IN (1, 2, 3);
-- ✅ 使用idx_customer_product，不需要回表

索引下推（ICP）优化：

-- 索引下推示例
CREATE TABLE orders_icp (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status ENUM('pending', 'paid', 'shipped') NOT NULL,
    total_amount DECIMAL(12,2),
    created_at DATETIME,
    
    INDEX idx_customer_status (customer_id, status)
);

-- 没有ICP的查询（旧版本）
SELECT * FROM orders_icp 
WHERE customer_id = 1234 AND status = 'paid';
-- 1. 通过customer_id找到所有记录
-- 2. 回表读取完整数据
-- 3. 在Server层过滤status

-- 有ICP的查询（MySQL 5.6+）
SELECT * FROM orders_icp 
WHERE customer_id = 1234 AND status = 'paid';
-- 1. 在存储引擎层直接过滤customer_id和status
-- 2. 只回表符合条件的记录

索引维护与重建策略

索引监控与维护：

-- 索引使用情况监控
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_READ DESC;

-- 索引统计信息
ANALYZE TABLE sales;  -- 更新统计信息

SHOW INDEX FROM sales;
-- 关注Cardinality（基数），值越接近记录数越好

-- 索引碎片整理
OPTIMIZE TABLE sales;  -- 重建表，整理碎片

-- 在线DDL（MySQL 5.6+）
ALTER TABLE sales 
DROP INDEX idx_old_index,
ADD INDEX idx_new_index (customer_id, sale_date),
ALGORITHM=INPLACE, LOCK=NONE;

索引设计检查清单：

-- 1. 为WHERE条件中的列创建索引
-- 2. 为JOIN条件的列创建索引  
-- 3. 为ORDER BY、GROUP BY的列创建索引
-- 4. 考虑覆盖索引，避免回表
-- 5. 使用复合索引，注意最左前缀
-- 6. 避免在索引列上使用函数
-- 7. 定期监控索引使用情况

-- 索引使用分析
EXPLAIN FORMAT=JSON
SELECT o.order_id, c.customer_name, SUM(oi.quantity) as total_items
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2023-01-01'
  AND o.status = 'completed'
GROUP BY o.order_id, c.customer_name
HAVING total_items > 5
ORDER BY o.created_at DESC;

总结

通过本篇的深入学习，我们掌握了MySQL数据类型选择和表设计的核心知识：

1. 数据类型选择：根据业务需求选择最合适的类型，平衡存储空间和查询性能
2. 规范化设计：理解三大范式，知道何时应该反范式化优化性能
3. 关系设计：掌握一对一、一对多、多对多关系的实现方式
4. 索引原理：深入理解B+Tree、聚簇索引、覆盖索引的工作原理
5. 索引优化：掌握复合索引设计、最左前缀原则、索引下推等高级技巧

关键实践要点：

• 字符串类型：固定长度用CHAR，变长用VARCHAR，大文本用TEXT
• 数值类型：根据范围选择最小合适的类型，金融计算用DECIMAL
• 时间类型：业务时间用DATETIME，系统时间用TIMESTAMP
• 索引设计：遵循最左前缀，考虑覆盖索引，监控索引使用情况

动手练习：

1. 为你当前的项目重新设计表结构，应用学到的数据类型和索引原则
2. 分析现有表的索引使用情况，优化低效索引
3. 尝试使用JSON类型存储半结构化数据
4. 设计一个符合范式要求的数据库schema，并考虑性能优化

欢迎在评论区分享你的表设计经验和遇到的问题！