目录

1.基础优化策略

2.索引优化案例

3.查询重写案例

4.JOIN优化案例

5.子查询优化案例

6.分页优化案例

7.统计聚合优化

8.大数据量处理

9.数据库设计优化

10.实战综合案例

一、基础优化策略案例1：SELECT * 问题

-- 原SQLSELECT * FROM orders WHERE user_id = 1000;

-- 优化后SELECT order_id, order_date, amount FROM orders WHERE user_id = 1000;

案例2：LIMIT滥用

-- 原SQLSELECT * FROM products LIMIT 1000;

-- 优化后SELECT product_id, name, price FROM products WHERE is_active = 1 LIMIT 100;

案例3：避免在WHERE中使用函数

-- 原SQLSELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';

-- 优化后SELECT * FROM orders WHERE order_date >= '2024-01-01'

AND order_date < '2024-01-02';

二、索引优化案例案例4：最左前缀原则

-- 表结构CREATE TABLE orders (

order_id INT PRIMARY KEY,

user_id INT,

status VARCHAR(20),

order_date DATE,

INDEX idx_user_status_date (user_id, status, order_date));

-- 原SQL：无法使用完整索引SELECT * FROM orders WHERE status = 'shipped';

-- 优化SQL1：使用索引最左列SELECT * FROM orders WHERE user_id = 1000 AND status = 'shipped';

-- 优化SQL2：调整索引顺序CREATE INDEX idx_status_user_date ON orders(status, user_id, order_date);

案例5：覆盖索引优化

-- 原SQL：需要回表SELECT order_id, user_id, order_date FROM orders WHERE status = 'pending' ORDER BY order_date DESC;

-- 优化：创建覆盖索引CREATE INDEX idx_status_date_covering ON orders(status, order_date, user_id, order_id);

案例6：索引列顺序优化

-- 低效索引CREATE INDEX idx_date_user ON orders(order_date, user_id);

-- 高效索引（基于查询模式）CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- 查询优化SELECT * FROM orders WHERE user_id = 1000

AND order_date BETWEEN '2024-01-01' AND '2024-12-31';

案例7：索引选择性优化

-- 低选择性索引（只有3种状态）CREATE INDEX idx_status ON orders(status);

-- 高选择性索引组合CREATE INDEX idx_status_date ON orders(status, order_date);

-- 更优：选择性更高的列在前CREATE INDEX idx_date_status ON orders(order_date, status);

案例8：函数索引（MySQL 8.0+）

-- 原SQL：无法使用索引SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 创建函数索引CREATE INDEX idx_email_lower ON users((LOWER(email)));

-- 优化后查询SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

案例9：前缀索引优化

-- 大文本字段索引优化CREATE TABLE logs (

id INT PRIMARY KEY,

url VARCHAR(500),

INDEX idx_url (url(100)) -- 前100字符索引);

-- 计算合适的前缀长度SELECT

COUNT(DISTINCT LEFT(url, 50)) / COUNT(*) as selectivity_50,

COUNT(DISTINCT LEFT(url, 100)) / COUNT(*) as selectivity_100,

COUNT(DISTINCT LEFT(url, 200)) / COUNT(*) as selectivity_200FROM logs;

案例10：删除冗余索引

-- 冗余索引检测SHOW INDEX FROM orders;

-- 删除冗余索引DROP INDEX idx_user_id ON orders; -- 如果已有复合索引(user_id, status)

-- 使用复合索引代替多个单列索引CREATE INDEX idx_user_status_date ON orders(user_id, status, order_date);

三、查询重写案例案例11：OR优化

-- 原SQL：可能导致全表扫描SELECT * FROM products WHERE category_id = 1

OR category_id = 2

OR price > 100;

-- 优化1：使用UNION ALLSELECT * FROM products WHERE category_id = 1UNION ALLSELECT * FROM products WHERE category_id = 2UNION ALLSELECT * FROM products WHERE price > 100

AND category_id NOT IN (1, 2);

-- 优化2：使用INSELECT * FROM products WHERE category_id IN (1, 2)

OR price > 100;

案例12：IN vs EXISTS

-- 小表驱动大表用EXISTSSELECT * FROM orders oWHERE EXISTS (

SELECT 1 FROM users u

WHERE u.user_id = o.user_id

AND u.is_vip = 1);

-- 大表驱动小表用INSELECT * FROM orders WHERE user_id IN (

SELECT user_id FROM vip_users);

案例13：DISTINCT优化

-- 原SQL：使用DISTINCTSELECT DISTINCT u.user_id, u.nameFROM users uJOIN orders o ON u.user_id = o.user_id;

-- 优化1：使用EXISTSSELECT u.user_id, u.nameFROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

-- 优化2：使用GROUP BYSELECT u.user_id, u.nameFROM users uJOIN orders o ON u.user_id = o.user_idGROUP BY u.user_id, u.name;

案例14：LIKE优化

-- 原SQL：前导通配符无法使用索引SELECT * FROM products WHERE name LIKE '%apple%';

-- 优化1：后通配符可以使用索引SELECT * FROM products WHERE name LIKE 'apple%';

-- 优化2：全文索引CREATE FULLTEXT INDEX idx_name_ft ON products(name);SELECT * FROM products WHERE MATCH(name) AGAINST('apple' IN NATURAL LANGUAGE MODE);

-- 优化3：使用覆盖索引CREATE INDEX idx_name_covering ON products(name, product_id, price);SELECT product_id, price FROM products WHERE name LIKE 'apple%';

四、JOIN优化案例案例15：JOIN顺序优化

-- 原SQL：大表在前SELECT * FROM large_table lJOIN small_table s ON l.small_id = s.id;

-- 优化后：小表在前SELECT /*+ STRAIGHT_JOIN */ FROM small_table sJOIN large_table l ON s.id = l.small_id;

案例16：避免笛卡尔积

-- 原SQL：隐式笛卡尔积SELECT * FROM table1, table2;

-- 优化后：显式JOINSELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id;

案例17：JOIN条件优化

-- 原SQL：JOIN条件包含计算SELECT * FROM orders oJOIN products p ON o.product_id = p.id

AND DATE(o.order_date) = DATE(p.created_at);

-- 优化后：避免函数计算SELECT * FROM orders oJOIN products p ON o.product_id = p.id

AND o.order_date >= DATE(p.created_at)

AND o.order_date < DATE(p.created_at) + INTERVAL 1 DAY;

案例18：LEFT JOIN优化

-- 原SQL：在JOIN条件外过滤SELECT o.*, u.nameFROM orders oLEFT JOIN users u ON o.user_id = u.user_idWHERE u.is_active = 1; -- 这会将LEFT JOIN转为INNER JOIN

-- 优化后：过滤条件放在ON中SELECT o.*, u.nameFROM orders oLEFT JOIN users u ON o.user_id = u.user_id AND u.is_active = 1;

案例19：多重JOIN优化

-- 原SQL：复杂多重JOINSELECT *FROM orders oJOIN order_items oi ON o.order_id = oi.order_idJOIN products p ON oi.product_id = p.idJOIN categories c ON p.category_id = c.idJOIN users u ON o.user_id = u.user_id;

-- 优化：减少JOIN数量WITH order_details AS (

SELECT o.*, oi.product_id

FROM orders o

JOIN order_items oi ON o.order_id = oi.order_id)SELECT od.*, p.name, u.usernameFROM order_details odJOIN products p ON od.product_id = p.idJOIN users u ON od.user_id = u.user_id;

五、子查询优化案例案例20：相关子查询优化

-- 原SQL：相关子查询SELECT u.name,

(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) as order_countFROM users u;

-- 优化1：使用LEFT JOINSELECT u.name, COUNT(o.order_id) as order_countFROM users uLEFT JOIN orders o ON u.user_id = o.user_idGROUP BY u.user_id, u.name;

-- 优化2：使用派生表SELECT u.name, IFNULL(t.order_count, 0) as order_countFROM users uLEFT JOIN (

SELECT user_id, COUNT(*) as order_count

FROM orders

GROUP BY user_id) t ON u.user_id = t.user_id;

案例21：IN子查询优化

-- 原SQL：IN子查询SELECT * FROM productsWHERE category_id IN (

SELECT category_id FROM categories WHERE is_active = 1);

-- 优化1：使用JOINSELECT p.*FROM products pJOIN categories c ON p.category_id = c.category_idWHERE c.is_active = 1;

-- 优化2：使用EXISTSSELECT * FROM products pWHERE EXISTS (

SELECT 1 FROM categories c

WHERE c.category_id = p.category_id

AND c.is_active = 1);

案例22：多层子查询优化

-- 原SQL：多层嵌套SELECT * FROM ordersWHERE user_id IN (

SELECT user_id FROM users

WHERE city_id IN (

SELECT city_id FROM cities WHERE country = 'China'

));

-- 优化：扁平化查询SELECT o.*FROM orders oJOIN users u ON o.user_id = u.user_idJOIN cities c ON u.city_id = c.city_idWHERE c.country = 'China';

六、分页优化案例案例23：深度分页优化

-- 原SQL：LIMIT深度分页SELECT * FROM orders ORDER BY order_date DESC LIMIT 1000000, 20; -- 非常慢！

-- 优化1：使用覆盖索引+JOINSELECT o.*FROM orders oJOIN (

SELECT order_id

FROM orders

ORDER BY order_date DESC

LIMIT 1000000, 20) t ON o.order_id = t.order_id;

-- 优化2：使用游标分页SELECT * FROM orders WHERE order_date < '2024-01-01' -- 上次最后一条的时间ORDER BY order_date DESC LIMIT 20;

案例24：分页+JOIN优化

-- 原SQL：分页包含JOINSELECT o.*, u.nameFROM orders oJOIN users u ON o.user_id = u.user_idORDER BY o.order_date DESCLIMIT 0, 20;

-- 优化：先分页再JOINSELECT o.*, u.nameFROM (

SELECT * FROM orders

ORDER BY order_date DESC

LIMIT 0, 20) oJOIN users u ON o.user_id = u.user_id;

七、统计聚合优化案例25：COUNT优化

-- 原SQL：COUNT(*)SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- 优化1：使用覆盖索引CREATE INDEX idx_status ON orders(status);

-- 优化2：定期统计表CREATE TABLE order_stats (

stat_date DATE PRIMARY KEY,

pending_count INT,

shipped_count INT);

案例26：GROUP BY优化

-- 原SQL：GROUP BY多个字段SELECT category_id, status, COUNT(*)FROM ordersGROUP BY category_id, status;

-- 优化：使用复合索引CREATE INDEX idx_category_status ON orders(category_id, status);

-- 使用ROLLUPSELECT category_id, status, COUNT(*)FROM ordersGROUP BY category_id, status WITH ROLLUP;

案例27：窗口函数优化

-- 原SQL：复杂窗口函数SELECT

user_id,

order_date,

amount,

SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) as running_total,

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rnFROM orders;

-- 优化：减少窗口函数使用WITH ranked_orders AS (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn

FROM orders)SELECT user_id, order_date, amountFROM ranked_ordersWHERE rn <= 10; -- 每个用户最近10个订单

八、大数据量处理案例28：批量插入优化

-- 原SQL：单条插入INSERT INTO logs (message, created_at) VALUES ('log1', NOW());INSERT INTO logs (message, created_at) VALUES ('log2', NOW());

-- 优化：批量插入INSERT INTO logs (message, created_at) VALUES ('log1', NOW()),('log2', NOW()),('log3', NOW());

-- 大批量数据优化LOAD DATA INFILE '/path/to/data.csv'INTO TABLE logsFIELDS TERMINATED BY ','LINES TERMINATED BY '\n';

案例29：分区表优化

-- 创建分区表CREATE TABLE orders_partitioned (

order_id INT PRIMARY KEY,

user_id INT,

order_date DATE,

amount DECIMAL(10,2))PARTITION BY RANGE (YEAR(order_date)) (

PARTITION p2022 VALUES LESS THAN (2023),

PARTITION p2023 VALUES LESS THAN (2024),

PARTITION p2024 VALUES LESS THAN (2025));

-- 查询指定分区SELECT * FROM orders_partitioned WHERE order_date >= '2024-01-01'PARTITION (p2024);

案例30：归档历史数据

-- 1. 创建归档表CREATE TABLE orders_archive LIKE orders;

-- 2. 迁移历史数据INSERT INTO orders_archiveSELECT * FROM orders WHERE order_date < '2023-01-01';

-- 3. 删除原表数据DELETE FROM orders WHERE order_date < '2023-01-01';

-- 4. 定期优化表OPTIMIZE TABLE orders;

九、数据库设计优化案例31：范式化vs反范式化

-- 范式化设计（3NF）CREATE TABLE orders (

order_id INT PRIMARY KEY,

user_id INT,

order_date DATE);

CREATE TABLE order_items (

item_id INT PRIMARY KEY,

order_id INT,

product_id INT,

quantity INT,

price DECIMAL(10,2));

-- 反范式化设计（查询优化）CREATE TABLE orders_denormalized (

order_id INT PRIMARY KEY,

user_id INT,

user_name VARCHAR(100), -- 冗余用户信息

order_date DATE,

total_amount DECIMAL(10,2), -- 预计算总和

item_count INT -- 预计算计数);

案例32：枚举类型优化

-- 原设计：VARCHAR存储状态CREATE TABLE orders (

status VARCHAR(20) -- 'pending', 'shipped', 'delivered');

-- 优化1：使用ENUMCREATE TABLE orders (

status ENUM('pending', 'shipped', 'delivered', 'cancelled'));

-- 优化2：使用TINYINTCREATE TABLE orders (

status TINYINT, -- 1:pending, 2:shipped, 3:delivered, 4:cancelled

INDEX idx_status (status));

-- 使用JOIN表维护状态映射CREATE TABLE order_statuses (

status_id TINYINT PRIMARY KEY,

status_name VARCHAR(20));

案例33：JSON字段优化

-- 原设计：JSON存储动态字段CREATE TABLE products (

product_id INT PRIMARY KEY,

attributes JSON -- 包含各种动态属性);

-- 优化：提取常用字段CREATE TABLE products (

product_id INT PRIMARY KEY,

price DECIMAL(10,2),

stock INT,

attributes JSON, -- 存储不常用字段

INDEX idx_price (price),

INDEX idx_stock (stock));

-- 使用虚拟列+索引ALTER TABLE products ADD COLUMN weight DECIMAL(10,2)

GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.weight'))) STORED,ADD INDEX idx_weight (weight);

案例34：时间字段优化

-- 原设计：字符串存储时间CREATE TABLE logs (

log_time VARCHAR(19) -- '2024-01-01 12:00:00');

-- 优化1：使用TIMESTAMPCREATE TABLE logs (

log_time TIMESTAMP,

INDEX idx_log_time (log_time));

-- 优化2：分区+索引CREATE TABLE logs_partitioned (

log_time TIMESTAMP,

message TEXT)PARTITION BY RANGE (UNIX_TIMESTAMP(log_time)) (

PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),

PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')));

-- 使用DATE类型单独存储日期ALTER TABLE logs ADD COLUMN log_date DATE

GENERATED ALWAYS AS (DATE(log_time)) STORED,ADD INDEX idx_log_date (log_date);

十、实战综合案例案例35：电商订单查询优化

-- 原复杂查询SELECT

u.user_id,

u.name,

COUNT(DISTINCT o.order_id) as order_count,

SUM(oi.quantity * oi.price) as total_spent,

MAX(o.order_date) as last_order_dateFROM users uLEFT JOIN orders o ON u.user_id = o.user_idLEFT JOIN order_items oi ON o.order_id = oi.order_idWHERE u.register_date >= '2023-01-01'

AND u.is_active = 1

AND o.status IN ('completed', 'shipped')GROUP BY u.user_id, u.nameHAVING total_spent > 1000ORDER BY total_spent DESCLIMIT 100;

-- 优化方案-- 1. 创建索引CREATE INDEX idx_users_active_date ON users(is_active, register_date);CREATE INDEX idx_orders_user_status ON orders(user_id, status);CREATE INDEX idx_order_items_order ON order_items(order_id);

-- 2. 使用派生表分步查询WITH user_orders AS (

SELECT

o.user_id,

o.order_id,

o.order_date

FROM orders o

WHERE o.status IN ('completed', 'shipped')),

order_totals AS (

SELECT

order_id,

SUM(quantity * price) as order_amount

FROM order_items

GROUP BY order_id),

user_summary AS (

SELECT

u.user_id,

u.name,

COUNT(DISTINCT uo.order_id) as order_count,

SUM(ot.order_amount) as total_spent,

MAX(uo.order_date) as last_order_date

FROM users u

JOIN user_orders uo ON u.user_id = uo.user_id

JOIN order_totals ot ON uo.order_id = ot.order_id

WHERE u.register_date >= '2023-01-01'

AND u.is_active = 1

GROUP BY u.user_id, u.name

HAVING SUM(ot.order_amount) > 1000)SELECT * FROM user_summaryORDER BY total_spent DESCLIMIT 100;

案例36：实时数据分析优化

-- 原实时聚合查询SELECT

DATE(created_at) as date,

product_id,

COUNT(*) as view_count,

COUNT(DISTINCT user_id) as unique_usersFROM product_viewsWHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)GROUP BY DATE(created_at), product_idORDER BY date DESC, view_count DESC;

-- 优化：使用物化视图/汇总表CREATE TABLE product_view_summary (

summary_date DATE,

product_id INT,

view_count INT DEFAULT 0,

unique_users INT DEFAULT 0,

PRIMARY KEY (summary_date, product_id),

INDEX idx_date_count (summary_date, view_count DESC)) ENGINE=InnoDB;

-- 每小时更新汇总表INSERT INTO product_view_summary (summary_date, product_id, view_count, unique_users)SELECT

DATE(created_at) as summary_date,

product_id,

COUNT(*) as view_count,

COUNT(DISTINCT user_id) as unique_usersFROM product_viewsWHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)GROUP BY DATE(created_at), product_idON DUPLICATE KEY UPDATE

view_count = view_count + VALUES(view_count),

unique_users = unique_users + VALUES(unique_users);

-- 查询优化后SELECT

summary_date as date,

product_id,

view_count,

unique_usersFROM product_view_summaryWHERE summary_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)ORDER BY summary_date DESC, view_count DESC;

案例37：消息系统查询优化

-- 原消息查询SELECT

m.*,

u1.username as sender_name,

u2.username as receiver_nameFROM messages mJOIN users u1 ON m.sender_id = u1.user_idJOIN users u2 ON m.receiver_id = u2.user_idWHERE (m.sender_id = 1000 OR m.receiver_id = 1000)

AND m.is_deleted = 0ORDER BY m.created_at DESCLIMIT 0, 50;

-- 优化方案-- 1. 创建复合索引CREATE INDEX idx_user_conversation ON messages(

GREATEST(sender_id, receiver_id),

LEAST(sender_id, receiver_id),

created_at DESC);

-- 2. 使用UNION优化OR条件(SELECT m.*

FROM messages m

WHERE m.sender_id = 1000 AND m.is_deleted = 0

ORDER BY m.created_at DESC

LIMIT 50)UNION ALL(SELECT m.*

FROM messages m

WHERE m.receiver_id = 1000 AND m.is_deleted = 0

ORDER BY m.created_at DESC

LIMIT 50)ORDER BY created_at DESCLIMIT 50;

-- 3. 最终优化查询SELECT

m.*,

u1.username as sender_name,

u2.username as receiver_nameFROM (

SELECT message_id

FROM messages

WHERE (sender_id = 1000 OR receiver_id = 1000)

AND is_deleted = 0

ORDER BY created_at DESC

LIMIT 0, 50) recent_idsJOIN messages m ON recent_ids.message_id = m.message_idJOIN users u1 ON m.sender_id = u1.user_idJOIN users u2 ON m.receiver_id = u2.user_idORDER BY m.created_at DESC;

案例38：社交网络好友关系查询

-- 原查询：查找共同好友SELECT

u.user_id,

u.usernameFROM users uJOIN friendships f1 ON u.user_id = f1.friend_idJOIN friendships f2 ON u.user_id = f2.friend_idWHERE f1.user_id = 1000 -- 用户A

AND f2.user_id = 2000 -- 用户B

AND f1.status = 'accepted'

AND f2.status = 'accepted';

-- 优化方案-- 1. 创建索引CREATE INDEX idx_friendships_user_friend ON friendships(user_id, friend_id, status);

-- 2. 使用INTERSECT替代（如果支持）SELECT friend_idFROM friendshipsWHERE user_id = 1000 AND status = 'accepted'INTERSECTSELECT friend_idFROM friendshipsWHERE user_id = 2000 AND status = 'accepted';

-- 3. MySQL中使用JOIN优化SELECT f1.friend_idFROM friendships f1JOIN friendships f2 ON f1.friend_id = f2.friend_idWHERE f1.user_id = 1000

AND f2.user_id = 2000

AND f1.status = 'accepted'

AND f2.status = 'accepted';

-- 4. 使用EXISTS优化SELECT u.user_id, u.usernameFROM users uWHERE EXISTS (

SELECT 1 FROM friendships f1

WHERE f1.user_id = 1000

AND f1.friend_id = u.user_id

AND f1.status = 'accepted') AND EXISTS (

SELECT 1 FROM friendships f2

WHERE f2.user_id = 2000

AND f2.friend_id = u.user_id

AND f2.status = 'accepted');

案例39：地理空间查询优化

-- 原查询：查找附近地点SELECT

place_id,

name,

address,

(6371 * ACOS(

COS(RADIANS(31.2304)) * COS(RADIANS(latitude)) *

COS(RADIANS(longitude) - RADIANS(121.4737)) +

SIN(RADIANS(31.2304)) * SIN(RADIANS(latitude))

)) as distance_kmFROM placesWHERE category = 'restaurant'HAVING distance_km <= 5ORDER BY distance_kmLIMIT 20;

-- 优化：使用空间索引-- 1. 添加空间数据ALTER TABLE placesADD COLUMN location POINT NOT NULL,ADD SPATIAL INDEX idx_location (location);

UPDATE places SET location = POINT(longitude, latitude);

-- 2. 使用MBRContains优化SELECT

place_id,

name,

address,

ST_Distance_Sphere(

location,

ST_GeomFromText('POINT(121.4737 31.2304)')

) / 1000 as distance_kmFROM placesWHERE category = 'restaurant'

AND MBRContains(

ST_GeomFromText('Polygon((

121.4737 - 0.045 31.2304 - 0.045,

121.4737 + 0.045 31.2304 - 0.045,

121.4737 + 0.045 31.2304 + 0.045,

121.4737 - 0.045 31.2304 + 0.045,

121.4737 - 0.045 31.2304 - 0.045

))'),

location

)HAVING distance_km <= 5ORDER BY distance_kmLIMIT 20;

案例40：全文搜索优化

-- 原LIKE搜索SELECT * FROM articlesWHERE title LIKE '%数据库优化%'

OR content LIKE '%数据库优化%';

-- 优化1：使用全文索引CREATE FULLTEXT INDEX idx_article_search ON articles(title, content);

SELECT

*,

MATCH(title, content) AGAINST('数据库优化') as relevanceFROM articlesWHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE)ORDER BY relevance DESC;

-- 优化2：使用布尔模式SELECT * FROM articlesWHERE MATCH(title, content) AGAINST('+数据库 +优化' IN BOOLEAN MODE);

-- 优化3：使用扩展查询SELECT * FROM articlesWHERE MATCH(title, content) AGAINST('数据库优化' WITH QUERY EXPANSION);

-- 优化4：结合LIKE和全文索引SELECT * FROM articlesWHERE MATCH(title, content) AGAINST('数据库*' IN BOOLEAN MODE)

AND (title LIKE '%优化%' OR content LIKE '%优化%');

更多案例速览（41-100）案例41-50：性能监控与调优

-- 41. 慢查询日志分析SET GLOBAL slow_query_log = ON;SET GLOBAL long_query_time = 2;

-- 42. 查看执行计划EXPLAIN FORMAT=JSONSELECT * FROM orders WHERE user_id = 1000;

-- 43. 使用索引提示SELECT /*+ INDEX(orders idx_user_id) */ *FROM orders WHERE user_id = 1000;

-- 44. 强制连接顺序SELECT /*+ STRAIGHT_JOIN */ *FROM small_table sJOIN large_table l ON s.id = l.small_id;

-- 45. 优化器提示SELECT /*+ MAX_EXECUTION_TIME(1000) */ *FROM large_table;

-- 46. 查询缓存优化SELECT SQL_NO_CACHE * FROM orders;

-- 47. 临时表优化CREATE TEMPORARY TABLE temp_ordersENGINE=MemorySELECT * FROM orders WHERE order_date > '2024-01-01';

-- 48. 批量更新优化UPDATE large_tableSET status = 'inactive'WHERE last_active < DATE_SUB(NOW(), INTERVAL 1 YEAR)LIMIT 1000; -- 分批更新

-- 49. 死锁避免START TRANSACTION;SELECT * FROM accounts WHERE id = 1 FOR UPDATE;-- 按相同顺序访问表避免死锁

-- 50. 连接池优化SHOW STATUS LIKE 'Threads_connected';SET GLOBAL max_connections = 500;

案例51-60：数据类型优化

-- 51. INT类型选择TINYINT -- -128到127SMALLINT -- -32768到32767MEDIUMINT -- -8388608到8388607INT -- -2147483648到2147483647BIGINT -- 更大范围

-- 52. 字符串类型优化CHAR(10) -- 固定长度VARCHAR(10) -- 可变长度TEXT -- 大文本BLOB -- 二进制

-- 53. 时间类型选择TIMESTAMP -- 4字节，时区相关DATETIME -- 8字节，时区无关DATE -- 3字节，只存日期TIME -- 3字节，只存时间

-- 54. 小数类型优化DECIMAL(10,2) -- 精确小数FLOAT -- 4字节，近似值DOUBLE -- 8字节，近似值

-- 55. 枚举优化ENUM('small','medium','large') -- 存储为整数

案例61-70：事务优化

-- 61. 事务隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 62. 避免长事务SET innodb_lock_wait_timeout = 50;SET innodb_rollback_on_timeout = ON;

-- 63. 批量提交START TRANSACTION;-- 批量操作COMMIT;

-- 64. 锁优化SELECT * FROM table WHERE id = 1 LOCK IN SHARE MODE;SELECT * FROM table WHERE id = 1 FOR UPDATE;

-- 65. 避免间隙锁SELECT * FROM table WHERE index_column = 100

AND unique_column = 200 FOR UPDATE;

案例71-80：复制与高可用优化

-- 71. 主从复制优化SHOW SLAVE STATUS\G

-- 72. 读写分离-- 写操作INSERT INTO orders ...; -- 主库

-- 读操作SELECT * FROM orders ...; -- 从库

-- 73. 分库分表策略-- 按用户ID分片

shard_id = user_id % 64

-- 74. 全局唯一IDCREATE TABLE sequence (

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;

-- 75. 数据一致性检查

CHECKSUM TABLE orders;

案例81-90：备份与恢复优化

-- 81. 在线备份

mysqldump --single-transaction --routines dbname > backup.-- 82. 增量备份

mysqlbinlog mysql-bin.000001 > binlog.-- 83. 快速恢复

mysql -e "SET foreign_key_checks=0; source backup.sql"

-- 84. 分区表备份ALTER TABLE orders EXCHANGE PARTITION p2024 WITH TABLE orders_backup;

-- 85. 并行备份

mydumper -t 4 -B dbname -o /backup/

案例91-100：高级优化技巧

-- 91. 使用窗口函数优化SELECT *,

LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) as prev_amountFROM orders;

-- 92. CTE递归查询WITH RECURSIVE cte AS (

SELECT 1 as n

UNION ALL

SELECT n + 1 FROM cte WHERE n < 100)SELECT * FROM cte;

-- 93. JSON函数优化SELECT

JSON_EXTRACT(data, '$.price') as price,

JSON_CONTAINS_PATH(data, 'one', '$.discount') as has_discountFROM products;

-- 94. 生成列优化ALTER TABLE ordersADD COLUMN order_year YEAR

GENERATED ALWAYS AS (YEAR(order_date)) STORED;

-- 95. 使用GIS函数SELECT

ST_AsText(location),

ST_Distance_Sphere(location, @user_location) as distanceFROM places;

-- 96. 优化UNION查询(SELECT * FROM table1 WHERE ... LIMIT 10)UNION ALL(SELECT * FROM table2 WHERE ... LIMIT 10)LIMIT 10;

-- 97. 使用变量优化SET @row_number = 0;SELECT

(@row_number:=@row_number + 1) AS row_num,

user_id, nameFROM users;

-- 98. 避免触发器性能问题-- 使用应用程序逻辑替代触发器

-- 99. 存储过程优化

CREATE PROCEDURE process_orders()BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 批量处理逻辑END;

-- 100. 最终建议/*

1. 永远先看执行计划

2. 索引不是越多越好

3. 避免在数据库层做复杂计算

4. 定期分析表和索引

5. 监控慢查询日志

6. 合理使用缓存

7. 考虑数据归档

8. 测试、测试、再测试

*/

记住：没有最好的优化方案，只有最适合当前场景的方案。在实际应用中，应该根据具体情况进行测试和调整。

#sql# #数据库# #mysq# #mssql# #oracle# #数据库优化# #sql优化#