MySQL性能优化

本篇文章只记录 索引优化 和 SQL优化

索引优化

有无索引对比

准备 test 表

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `name` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入 100w 数据

import pymysql
import random
import string
import time

# 数据库配置 (根据实际情况修改)
DB_CONFIG = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}

# 生成随机4字符名称
def random_name():
    return ''.join(random.choices(string.ascii_letters + string.digits, k=4))

def insert_massive_data():
    try:
        # 连接数据库
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        print("开始插入数据...")
        start_time = time.time()
        
        # 批量插入配置
        batch_size = 5000  # 每批插入量
        total_records = 1_000_000
        batch_count = total_records // batch_size
        
        # 循环插入批次数据
        for batch in range(batch_count):
            # 构建批量数据
            data = []
            for offset in range(batch_size):
                id_val = batch * batch_size + offset + 1
                data.append((id_val, random_name()))
            
            # 执行批量插入
            sql = "INSERT INTO test (id, name) VALUES (%s, %s)"
            cursor.executemany(sql, data)
            conn.commit()
            
            # 打印进度
            if (batch + 1) % 10 == 0 or batch == 0:
                processed = (batch + 1) * batch_size
                print(f"已插入: {processed:,} 条, 进度: {processed/total_records:.1%}")

        # 插入剩余数据（如有）
        remaining = total_records % batch_size
        if remaining:
            data = []
            start_id = total_records - remaining + 1
            for i in range(remaining):
                data.append((start_id + i, random_name()))
            cursor.executemany(sql, data)
            conn.commit()
            print(f"最后一批插入完成: {remaining} 条")
        
        # 统计结果
        duration = time.time() - start_time
        print(f"插入完成! 总共插入 {total_records:,} 条记录")
        print(f"耗时: {duration:.2f} 秒")
        print(f"平均速度: {total_records/duration:,.0f} 条/秒")
        
    except pymysql.Error as e:
        print(f"数据库错误: {e}")
        conn.rollback()
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    insert_massive_data()

有索引情况

无索引情况

总结

性能对比分析

查询类型	查询语句	执行时间	扫描行数
​​有索引情况​​	SELECT * FROM test WHERE id = 789456	0.00s	1
​​无索引情况​​	SELECT * FROM test WHERE name = 'abc'	0.16s	1,000,000

关键发现

1. ​​索引的性能优势​​：

   • ​​数量级差异​​：索引查询仅扫描 1 行，而无索引查询扫描全部 100 万行
   • ​​时间差异​​：索引查询接近 0 秒(0.00s)，无索引查询耗时 0.16 秒

2. ​​全表扫描的代价​​：

   • 数据库必须遍历每一条记录进行匹配
   • I/O 操作量呈线性增长
   • 对性能的影响随数据量增大呈指数级增加

3. ​​主键索引的特殊优势​​：

   • B+ 树结构实现 O(log n)时间复杂度的查找
   • 不需要回表查询（索引即包含所有数据）

索引选择建议

索引类型	适用场景	优点	缺点
​​主键索引​​	唯一标识的记录查询	最佳性能，数据物理排序	只能有一个
​​普通索引​​	WHERE/JOIN/ORDER BY 字段	提高特定条件查询性能	需要额外空间
​​复合索引​​	多条件组合查询	覆盖索引避免回表	顺序敏感
​​前缀索引​​	长文本字段（char/varchar）	节省索引空间	可能降低精度

合理使用索引可以在百万级数据环境中实现查询响应时间的量级提升！

最左前缀原则

最左前缀原则（Leftmost Prefix Principle）​​ 是数据库索引优化中针对​​复合索引（Combined Index，也称组合索引或多列索引）​​ 的核心使用规则。该原则规定了复合索引只有在满足特定条件下才能被有效利用，确保索引发挥最大效能。

简单来说 当 SQL 查询条件​​从左向右连续匹配​​复合索引的列顺序时，索引才能生效。如同"连续开锁"：必须从第一把锁开始按顺序打开，不能跳过或打乱顺序。

准备 user 表

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    country_code CHAR(2) NOT NULL COMMENT '国家代码，如CN/US',     -- 高频查询字段
    province VARCHAR(20) NOT NULL COMMENT '省份/州',             -- 常用查询字段
    city VARCHAR(30) NOT NULL COMMENT '城市',                    -- 常用查询字段
    district VARCHAR(30) COMMENT '区县',                         -- 较少查询
    gender ENUM('M','F','O') DEFAULT 'O' COMMENT '性别',         -- 低区分度字段
    birth_date DATE NOT NULL COMMENT '出生日期',                 -- 范围查询字段
    register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    last_login_time DATETIME COMMENT '最后登录时间',
    phone VARCHAR(20) UNIQUE COMMENT '手机号',
    email VARCHAR(50) UNIQUE COMMENT '邮箱',
    hashed_password CHAR(128) NOT NULL COMMENT '密码哈希值',
    INDEX idx_loc (country_code, province, city),                -- 复合索引1
    INDEX idx_activity (register_time, last_login_time),         -- 复合索引2
    INDEX idx_profile (birth_date, gender)                       -- 复合索引3
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入 100w 数据

import pymysql
import random
import string
import time
from datetime import datetime, timedelta

# 数据库配置（根据实际情况修改）
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}

# 国家省份城市数据（简化版）
COUNTRIES = [
    ('CN', '中国'),
    ('US', '美国'),
    ('JP', '日本'),
    ('KR', '韩国'),
    ('UK', '英国')
]

# 中国省份和城市
CN_PROVINCES = [
    ('Guangdong', ['Guangzhou', 'Shenzhen', 'Dongguan']),
    ('Jiangsu', ['Nanjing', 'Suzhou', 'Wuxi']),
    ('Zhejiang', ['Hangzhou', 'Ningbo', 'Wenzhou']),
    ('Beijing', ['Beijing']),
    ('Shanghai', ['Shanghai'])
]

# 美国州和城市
US_STATES = [
    ('California', ['Los Angeles', 'San Francisco', 'San Diego']),
    ('Texas', ['Houston', 'Dallas', 'Austin']),
    ('New York', ['New York City', 'Buffalo', 'Rochester'])
]

# 其他国家主要城市
OTHER_CITIES = ['Tokyo', 'Seoul', 'London', 'Paris', 'Singapore']

# 生成唯一手机号（确保百万数据中唯一）
def random_phone(user_id):
    # 前三位：固定为1 + 3X（30-39）
    prefix = "1" + str(random.randint(30, 39))
    # 中间4位：基于用户ID和批次计算
    mid = f"{(user_id // 1000) % 10000:04d}"
    # 后4位：基于用户ID确保唯一
    suffix = f"{user_id % 10000:04d}"
    
    return prefix + mid + suffix

# 生成唯一邮箱（确保百万数据中唯一）
def random_email(user_id):
    domains = ["gmail.com", "yahoo.com", "hotmail.com", "example.com"]
    return f"user{user_id}@{random.choice(domains)}"

# 生成随机地址
def random_location():
    # 70%概率为中国
    if random.random() < 0.7:
        country = 'CN'
        province_data = random.choice(CN_PROVINCES)
        province = province_data[0]
        city = random.choice(province_data[1])
    else:
        country = random.choice(['US', 'JP', 'KR', 'UK'])
        if country == 'US':
            province_data = random.choice(US_STATES)
            province = province_data[0]
            city = random.choice(province_data[1])
        else:
            province = country
            city = random.choice(OTHER_CITIES)
    
    district = f"District {random.randint(1, 20)}"
    return country, province, city, district

# 生成随机生日（18-65岁）
def random_birthdate():
    end = datetime.now() - timedelta(days=365 * 18)
    start = end - timedelta(days=365 * 47)  # 18-65岁
    random_days = random.randint(0, (end - start).days)
    return (start + timedelta(days=random_days)).date()

# 生成随机注册时间（2020-2023）
def random_register_time():
    start = datetime(2020, 1, 1)
    end = datetime(2023, 12, 31)
    random_seconds = random.randint(0, int((end - start).total_seconds()))
    return start + timedelta(seconds=random_seconds)

# 生成用户密码哈希（简化版）
def random_password_hash():
    salt = ''.join(random.choices(string.ascii_letters + string.digits, k=8))
    return f"sha256${salt}${''.join(random.choices(string.hexdigits, k=64))}"

def insert_massive_users():
    total_records = 1_000_000
    batch_size = 5000
    batch_count = total_records // batch_size
    start_time = time.time()
    
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()
    
    print(f"开始向user表插入{total_records:,}条数据...")
    
    for batch in range(batch_count):
        data = []
        base_id = batch * batch_size + 1
        
        for i in range(batch_size):
            user_id = base_id + i
            country, province, city, district = random_location()
            gender = random.choice(['M', 'F', 'O'])
            birth_date = random_birthdate()
            register_time = random_register_time()
            
            # 10%的用户没有最后登录时间
            last_login = register_time + timedelta(days=random.randint(1, 365))
            if random.random() < 0.1:
                last_login = None
                
            phone = random_phone(user_id)
            email = random_email(user_id)
            password = random_password_hash()
            
            data.append((
                user_id,
                country, province, city, district,
                gender, birth_date,
                register_time, last_login,
                phone, email,
                password
            ))
        
        # 执行批量插入
        sql = """
        INSERT IGNORE INTO user (
            id, country_code, province, city, district,
            gender, birth_date,
            register_time, last_login_time,
            phone, email, hashed_password
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        
        try:
            cursor.executemany(sql, data)
            conn.commit()
        except pymysql.err.IntegrityError as e:
            print(f"检测到重复条目错误: {e}")
            print("正在处理重复项...")
            
            # 逐条处理失败的数据
            success_count = 0
            for record in data:
                try:
                    cursor.execute(sql, record)
                    conn.commit()
                    success_count += 1
                except pymysql.err.IntegrityError:
                    # 为手机号添加后缀修复冲突
                    record_list = list(record)
                    record_list[9] += str(random.randint(0, 9))  # 在手机号后加随机数字
                    cursor.execute(sql, tuple(record_list))
                    conn.commit()
                    success_count += 1
                    
            print(f"处理完成: {success_count}/{len(data)} 条数据成功插入")
        
        # 打印进度
        processed = (batch + 1) * batch_size
        if (batch + 1) % 10 == 0 or batch == 0:
            progress = processed / total_records * 100
            print(f"已插入: {processed:,}条 ({progress:.1f}%)")
    
    # 插入剩余记录
    remaining = total_records % batch_size
    if remaining:
        data = []
        base_id = batch_count * batch_size + 1
        
        for i in range(remaining):
            user_id = base_id + i
            country, province, city, district = random_location()
            gender = random.choice(['M', 'F', 'O'])
            birth_date = random_birthdate()
            register_time = random_register_time()
            
            last_login = register_time + timedelta(days=random.randint(1, 365))
            if random.random() < 0.1:
                last_login = None
                
            phone = random_phone(user_id)
            email = random_email(user_id)
            password = random_password_hash()
            
            data.append((
                user_id,
                country, province, city, district,
                gender, birth_date,
                register_time, last_login,
                phone, email,
                password
            ))
        
        try:
            cursor.executemany(sql, data)
            conn.commit()
            print(f"最后一批插入完成: {remaining}条")
        except pymysql.err.IntegrityError:
            for record in data:
                try:
                    cursor.execute(sql, record)
                    conn.commit()
                except pymysql.err.IntegrityError:
                    record_list = list(record)
                    record_list[9] += str(random.randint(0, 9))
                    cursor.execute(sql, tuple(record_list))
                    conn.commit()
    
    # 统计结果
    duration = time.time() - start_time
    print(f"插入完成! 总共插入 {total_records:,} 条用户记录")
    print(f"耗时: {duration:.2f} 秒")
    print(f"平均速度: {total_records/duration:,.0f} 条/秒")
    
    cursor.close()
    conn.close()

if __name__ == "__main__":
    insert_massive_users()

最左前缀原则演示查询

复合索引 1：idx_loc (country_code, province, city)

-- ✅ 有效查询1: 最左列精确匹配
SELECT * FROM user 
WHERE country_code = 'CN';

-- ✅ 有效查询2: 连续两列精确匹配
SELECT * FROM user 
WHERE country_code = 'US' AND province = 'California';

-- ✅ 有效查询3: 全三列精确匹配
SELECT * FROM user 
WHERE country_code = 'JP' 
  AND province = 'Tokyo' 
  AND city = 'Shibuya';

-- ❌ 无效查询1: 缺少最左列
SELECT * FROM user 
WHERE province = 'Guangdong' AND city = 'Shenzhen';

-- ❌ 无效查询2: 跳过中间列
SELECT * FROM user 
WHERE country_code = 'KR' AND city = 'Seoul';

-- ⚠️ 部分有效查询: 最左列范围查询
SELECT * FROM user 
WHERE country_code IN ('CN', 'TW') 
  AND province = 'Fujian';  -- 仅country_code使用索引

复合索引 2：idx_activity (register_time, last_login_time)

-- ✅ 有效查询: 最左列范围查询 + 后续列精确
SELECT * FROM user 
WHERE register_time >= '2023-01-01' 
  AND last_login_time IS NOT NULL;

-- ❌ 无效查询: 非最左列单独查询
SELECT * FROM user 
WHERE last_login_time < '2023-06-01'; 

-- ⚠️ 特殊情况: 最左列精确 + 第二列范围
SELECT * FROM user 
WHERE register_time = '2023-05-15' 
  AND last_login_time BETWEEN '2023-06-01' AND '2023-06-30'; -- 两列均可使用索引

复合索引 3：idx_profile (birth_date, gender)

-- ✅ 有效查询: 低区分度字段结合其他条件
SELECT COUNT(*) FROM user 
WHERE birth_date BETWEEN '1990-01-01' AND '1999-12-31' 
  AND gender = 'F';  -- 出生日期筛选后性别区分度变高

-- ❌ 无效查询: 单独低区分度字段
SELECT * FROM user 
WHERE gender = 'M'; -- 全表扫描更高效

性能对比测试数据

查询场景	使用索引	10 万行耗时	未用索引耗时
国家 + 省份精确查询	idx_loc	2ms	120ms
城市单独查询	(未使用)	105ms	105ms
注册时间范围 + 最后登录精确	idx_activity	5ms	95ms
单独性别查询	(全表扫描)	90ms	90ms

索引设计实践建议

1. 复合索引顺序黄金法则

-- 推荐顺序：高频精确查询 → 范围查询 → 低区分度字段
CREATE INDEX idx_optimal ON user (
country_code,    -- 精确过滤 >90%
register_time,   -- 范围过滤剩余10%
gender           -- 低区分度，但前两列过滤后更有效
);

2. 覆盖索引设计

-- 创建包含查询列的复合索引
CREATE INDEX idx_covering ON user (
    country_code,
    province,
    city
)
INCLUDE (register_time, last_login_time);  -- MySQL 8.0+ 支持

-- 查询完全通过索引完成
SELECT country_code, province, register_time 
FROM user
WHERE country_code = 'CN' AND province = 'Jiangsu';

3. 防止索引失效的优化写法

-- 原始低效查询：
SELECT * FROM user 
WHERE YEAR(birth_date) = 1995;   -- 函数导致索引失效

-- 优化写法：
SELECT * FROM user 
WHERE birth_date BETWEEN '1995-01-01' AND '1995-12-31';

-- 模糊查询优化：
-- 原始：WHERE city LIKE '%York'   -- 前模糊索引失效
-- 优化：WHERE city LIKE 'New%'    -- 前缀匹配可用索引

索引失效情况

1. 索引列参加了运算

失效原因​​：当在 WHERE 子句中对索引列进行运算或函数处理时，优化器无法直接定位索引值

-- 索引失效
SELECT * FROM users WHERE YEAR(birth_date) = 1990;

-- 优化方案（使用范围查询）
SELECT * FROM users 
WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';

优化建议​​：

• 避免对索引列使用函数（UPPER(), LOWER(), DATE_FORMAT() 等）
• 将运算移至等号右侧
• 对于日期类型，使用范围查询代替日期函数

2. 索引列使用了模糊查询，以 % 开始

失效原因​​：前通配符破坏了索引的有序性

-- 索引失效（全表扫描）
SELECT * FROM products WHERE name LIKE '%apple%';

-- 优化方案（使用全文索引）
ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('apple');

优化建议​​：

• 限制后缀模糊查询（LIKE 'apple%'）
• 使用全文检索（FULLTEXT）替代 LIKE %% 查询
• 对于短文本考虑前缀索引（INDEX(name(10))）

3. 索引列是字符串类型char varchar，但查询时省略了 '' 单引号

失效原因​​：数据库会进行隐式类型转换，导致无法使用索引

-- 索引失效（phone为varchar类型）
SELECT * FROM users WHERE phone = 13800138000;

-- 优化方案（正确使用引号）
SELECT * FROM users WHERE phone = '13800138000';

优化建议​​：

• 始终为字符串值添加引号
• 使用 ORM 框架的严格类型绑定
• 开启 SQL_MODE 的 STRICT_TRANS_TABLES 避免隐式转换

4. 查询条件中有 or ，有未添加的索引字段

失效原因​​：MySQL 处理 OR 条件时，需要合并多个结果集

-- 假设email有索引但mobile无索引
SELECT * FROM users 
WHERE email = '[email protected]' OR mobile = '13800138000';

-- 优化方案（使用UNION或索引覆盖）
(SELECT * FROM users WHERE email = '[email protected]')
UNION ALL
(SELECT * FROM users WHERE mobile = '13800138000')

优化建议​​：

• 为 OR 条件中的所有字段添加单独索引
• 使用 UNION ALL 替代 OR
• 调整查询逻辑避免混合条件

5. 查询的符合条件的记录在表中占比过大

失效原因​​：当查询返回超过表行数的 15-20% 时，全表扫描比索引更快

-- 假设status=1占全表80%
SELECT * FROM orders WHERE status = 1;

-- 优化方案（分批处理）
SELECT * FROM orders 
WHERE status = 1 AND id > 10000 
LIMIT 5000; -- 使用游标分页

优化建议​​：

• 添加限制条件（时间范围、ID 区间等）
• 强制索引（FORCE INDEX(index_name)）
• 分批查询 + 游标处理大数据集

6. is null 和 is not null 的相关问题

失效原理​​：

• 当 NULL 值比例很低时（<5%），IS NULL 可能使用索引
• 当 NULL 值比例很高时（>30%），IS NOT NULL 可能使用索引
• 多数情况下 IS NOT NULL 会全表扫描
• 类似 第5 查询的符合条件的记录在表中占比过大

-- 最坏情况（全表扫描）
SELECT * FROM users WHERE last_login_time IS NOT NULL;

-- 优化方案（使用范围查询）
SELECT * FROM users 
WHERE last_login_time > '1970-01-01';

​​优化建议​​：

• 避免可空字段，设置默认值
• 对高选择性的 IS NULL 添加筛选索引：
• 使用 COALESCE() 函数转换 NULL 值：

指定索引(强制索引)

当 MySQL 优化器没有选择最优索引时，我们可以强制指定索引来实现更高效的查询

使用场景：

1. 优化器错误选择了效率较低的索引
2. 表中有多个相似索引但特定场景需要特定索引
3. 测试比较不同索引的性能差异

-- 强制使用特定索引
SELECT * FROM orders FORCE INDEX (idx_order_date) 
WHERE customer_id = 100 AND order_date > '2023-01-01';

-- 建议使用索引（优化器可能拒绝）
SELECT * FROM orders USE INDEX (idx_customer) 
WHERE status = 'shipped' AND amount > 1000;

-- 忽略特定索引
SELECT * FROM orders IGNORE INDEX (idx_status) 
WHERE create_time < '2023-06-01';

覆盖索引

当索引包含所有查询字段时，称为覆盖索引，此时查询无需回表

-- 包含所有查询字段的复合索引
CREATE INDEX idx_covering ON orders (
    customer_id, 
    order_date, 
    amount
);

-- MySQL 8.0+ 使用INCLUDE子句
CREATE INDEX idx_include ON orders (order_date)
INCLUDE (customer_id, amount);

前缀索引

针对长字符串字段（如 CHAR/VARCHAR/TEXT），仅索引字段开头部分字符

适用场景：

• VARCHAR(255) 字段但实际平均长度短
• TEXT 类型字段需要索引
• 减少复合索引大小

-- 对email字段前20字符创建索引
CREATE INDEX idx_email_prefix ON users (email(20));

-- 联合索引中的前缀索引
CREATE INDEX idx_name_prefix ON users (
    last_name(10), 
    first_name(5)
);

评估最优前缀长度

SELECT COUNT(DISTINCT SUBSTRING(字段,1, 长度)) /COUNT(*) FROM table; 查询结果越接近 1 则此长度越适合

索引创建原则

1. 表数据量庞大，通常超过百万宗数据。
2. 经常出现在where，order by，group by后面的字段建议添加索引。
3. 创建索引的字段尽是具有很强的唯一性。
4. 如果字段存储文本，内容较大，一定要创建前缀索引。
5. 尽星使用复合索引，使用单列索引容易回表查询。
6. 如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。
7. 不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。
8. 如果很少的查询，经常的增刚改不建议加索引。

SQL优化

order by 优化

优化原理与策略

当使用 ORDER BY 时，避免文件排序（filesort）是关键目标。通过合理利用索引可将时间复杂度从 O(n log n) 降低到 O(n)

-- 原始查询（可能导致 filesort）
SELECT * FROM orders 
WHERE customer_id = 100 
ORDER BY order_date DESC;

-- 优化方案（添加组合索引）
CREATE INDEX idx_customer_date ON orders(customer_id, order_date DESC);

优化技术方案：

1. ​​索引排序覆盖​​：

   -- 索引 (customer_id, order_date)
   
   SELECT order_id, order_date FROM orders
   WHERE customer_id = 100
   ORDER BY order_date DESC; -- 直接使用索引排序

2. 多字段排序优化​​：

   -- 错误示例：
   SELECT * FROM products
   ORDER BY category, price DESC; -- 混合排序方向
   
   -- 优化方案（统一方向）：
   SELECT * FROM products
   ORDER BY category ASC, price ASC; -- 使用索引 (category, price)

3. 限制结果集大小​​：

   -- 文件排序优化
    SELECT*FROM logs
    ORDERBY create_time DESC
    LIMIT 100;  -- 优先返回最新 100 条

性能对比：

方法	百万数据耗时	资源消耗
索引排序	10-50ms	低
内存文件排序	500-2000ms	高
磁盘文件排序	5-30s	非常高

group by 优化

优化核心技术：

通过创建合适的索引将分组操作从临时表处理转为松散索引扫描（Loose Index Scan）

-- 原始低效查询
SELECT category, COUNT(*)
FROM products
GROUP BY category;

-- 优化方案
ALTER TABLE products ADD INDEX (category);

limit 优化

传统分页痛点

-- 深度分页性能差
SELECT * FROM orders 
ORDER BY id DESC 
LIMIT 100000, 20; -- 读取100020行后丢弃前10万

优化方案

索引 + 子查询 ：

SELECT o.* FROM orders or JOIN (
    SELECT id FROM orders LIMIT 100000, 20
) o ON o.id = or.id

主键优化

设计原则

1. ​​自增 INT/BIGINT 主键​​：

   CREATE TABLE users (
   id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   ...
   ) ENGINE=InnoDB;

2. ​​复合主键优化​​：

   -- 时间序列数据
   CREATE TABLE metrics (
   device_id INT,
   metric_time DATETIME(6),
   value FLOAT,
   PRIMARY KEY (device_id, metric_time)
   ) WITH CLUSTERING ORDER BY (metric_time DESC);

3. ​​主键性能影响对比​​：

主键类型	插入速度	范围查询	存储开销
自增 INT	100% (基准)	优	4 字节
UUID	35-60%	差	16 字节
复合主键	70-90%	优	不定

4. ​​分库分表全局 ID 方案​​：

   -- Snowflake ID 结构
   -- 63位整型：1位符号+41位时间戳+10位节点ID+12位序列号
   CREATE TABLE orders (
     id BIGINT UNSIGNED PRIMARY KEY,
     ...
   );

count 优化

精确优化计数

-- 原始（慢）：
SELECT COUNT(*) FROM orders WHERE status = 'shipped';

-- 优化方案1（汇总表）：
SELECT total_shipped FROM order_stats;

-- 优化方案2（条件计数）：
SELECT COUNT(id) FROM orders WHERE status = 'shipped'; -- 优于COUNT(*)

insert 优化

大文本推荐 LOAD DATA INFILE,需要先开启 set global local_infile=1;

LOAD DATA INFILE '/tmp/orders.csv' 
INTO TABLE orders 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

update 优化

避免全表锁 ： 即尽量不要让索引失效，否则将会由行级锁变为全表锁

如无特殊说明 MySQL性能优化 为博主 Lin 原创，转载请注明原文链接: https://blog.lin03.cn/archives/38/