EXPLAIN SELECT o.order_id, c.name 
FROM orders o
JOIN customers c ON o.cust_id = c.id
WHERE o.status = 'PAID'
  AND o.create_time > '2023-01-01';

-- 關(guān)鍵指標(biāo)解讀
/*
+----+-------------+-------+------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref               | rows   | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------------------+--------+-------------+
| 1  | SIMPLE      | o     | ref  | idx_status    | idx_status | 82     | const             | 156892 | Using where |
| 1  | SIMPLE      | c     | eq_ref| PRIMARY       | PRIMARY  | 4       | db.o.cust_id      | 1      | NULL        |
+----+-------------+-------+------+---------------+---------+---------+-------------------+--------+-------------+
*/

SET profiling = 1;
-- 執(zhí)行目標(biāo)SQL
SELECT /*+ 測試SQL */ ...;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 7;

/* 典型問題輸出
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | Block_ops  |
+----------------------+----------+----------+------------+
| starting             | 0.000065 | 0.000000 | 0          |
| checking permissions | 0.000007 | 0.000000 | 0          |
| Opening tables       | 0.000023 | 0.000000 | 0          |
| Sorting result       | 2.134567 | 1.982342 | 1245       | <-- 排序耗時嚴(yán)重
| Sending data         | 0.000045 | 0.000000 | 0          |
+----------------------+----------+----------+------------+
*/

-- 查看最耗資源的SQL
SELECT sql_text, 
       SUM_TIMER_WAIT/1e12 AS total_sec,
       SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE 'SELECT%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

-- 1. 隱式類型轉(zhuǎn)換
SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar類型

-- 2. 索引列參與運(yùn)算
SELECT * FROM logs WHERE YEAR(create_time) = 2023;

-- 3. 前導(dǎo)通配符查詢
SELECT * FROM products WHERE name LIKE '%Pro%';

-- 4. OR條件混合使用
SELECT * FROM orders WHERE status = 'PAID' OR amount > 1000;

-- 5. 違反最左前綴原則
INDEX idx_a_b_c (a,b,c)
WHERE b=1 AND c=2  -- 無法使用索引

-- 6. 使用否定條件
SELECT * FROM users WHERE status != 'ACTIVE';

-- 7. 索引列使用函數(shù)
SELECT * FROM orders WHERE UPPER(order_no) = 'ABC123';

-- 原始寫法（掃描100100行）
SELECT * FROM orders 
ORDER BY id 
LIMIT 100000, 100;

-- 優(yōu)化寫法（掃描100行）
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 100;

-- 低效嵌套查詢
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE amount > 1000
);
-- 優(yōu)化為JOIN
SELECT u.* 
FROM users u
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 1000;

SELECT * FROM orders 
FORCE INDEX(idx_status_create_time) 
WHERE status = 'SHIPPED' 
  AND create_time > '2023-06-01';

SELECT /*+ MAX_EXECUTION_TIME(1000) */ ...
FROM large_table
WHERE ...;

SELECT /*+ MRR(buf_size=16M) */ ...
FROM sales 
WHERE sale_date BETWEEN ...;

# InnoDB配置優(yōu)化
innodb_buffer_pool_size = 物理內(nèi)存的70-80%
innodb_flush_log_at_trx_commit = 2  # 非關(guān)鍵業(yè)務(wù)
innodb_io_capacity = 2000          # SSD配置

# 查詢緩存優(yōu)化
query_cache_type = 0               # 8.0+版本已移除

SELECT COUNT(*) 
FROM user_behavior 
WHERE create_time BETWEEN '2023-01-01' AND '2023-06-30';
-- 執(zhí)行時間：12.8秒

-- 優(yōu)化步驟：
1. 創(chuàng)建函數(shù)索引：ALTER TABLE ADD INDEX idx_ymd ((DATE_FORMAT(create_time,'%Y%m%d')))
2. 分批統(tǒng)計后匯總：
   SELECT SUM(cnt) FROM (
     SELECT COUNT(*) cnt FROM user_behavior_202301
     UNION ALL
     SELECT COUNT(*) FROM user_behavior_202302
     ...
   ) tmp;
-- 優(yōu)化后時間：0.9秒

SELECT product_id, 
       AVG(rating),
       COUNT(DISTINCT user_id)
FROM reviews
GROUP BY product_id
HAVING COUNT(*) > 100;
-- 執(zhí)行時間：7.2秒

-- 優(yōu)化方案：
1. 創(chuàng)建匯總表：
   CREATE TABLE product_stats (
     product_id INT PRIMARY KEY,
     total_reviews INT,
     avg_rating DECIMAL(3,2),
     unique_users INT
   );
2. 使用觸發(fā)器實時更新
-- 查詢時間降至0.03秒

-- 字段類型為VARCHAR(32)
SELECT * FROM devices WHERE imei = 123456789012345; -- 全表掃描
SELECT * FROM devices WHERE imei = '123456789012345'; -- 走索引

-- 錯誤的長事務(wù)
BEGIN;
SELECT * FROM products; -- 耗時查詢
UPDATE inventory SET ...; 
COMMIT;

-- 優(yōu)化為：
START TRANSACTION READ ONLY;
SELECT * FROM products;
COMMIT;

BEGIN;
UPDATE inventory SET ...;
COMMIT;