-- 創(chuàng)建索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 以下查詢無法使用索引
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 優(yōu)化后的查詢，可以使用索引
SELECT * FROM orders 
WHERE create_time >= '2023-01-01 00:00:00' 
  AND create_time < '2024-01-01 00:00:00';

-- 創(chuàng)建表和索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    INDEX idx_phone (phone)
);

-- 以下查詢可能無法使用索引
SELECT * FROM users WHERE phone = 13800138000;

-- 正確的查詢方式，可以使用索引
SELECT * FROM users WHERE phone = '13800138000';

-- 創(chuàng)建索引
CREATE INDEX idx_status ON orders(status);

-- 以下查詢可能無法有效利用索引
SELECT * FROM orders WHERE status != 'completed';
SELECT * FROM orders WHERE status NOT IN ('completed', 'shipped');

-- 優(yōu)化后的查詢
SELECT * FROM orders 
WHERE status IN ('pending', 'processing', 'cancelled');

-- 創(chuàng)建單列索引
CREATE INDEX idx_name ON customers(name);
CREATE INDEX idx_email ON customers(email);

-- 以下查詢可能無法充分利用索引
SELECT * FROM customers 
WHERE name = 'John' OR email = 'john@example.com';

-- 使用UNION優(yōu)化
SELECT * FROM customers WHERE name = 'John'
UNION
SELECT * FROM customers WHERE email = 'john@example.com';

-- 在MySQL 5.6及以上版本，可能會使用索引合并
-- 也可以創(chuàng)建覆蓋索引
CREATE INDEX idx_name_email ON customers(name, email);

-- 創(chuàng)建索引
CREATE INDEX idx_product_name ON products(product_name);

-- 以下查詢無法使用索引
SELECT * FROM products WHERE product_name LIKE '%phone%';

-- 可以使用索引的查詢
SELECT * FROM products WHERE product_name LIKE 'phone%';

-- 創(chuàng)建全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name(product_name);

-- 使用全文索引查詢
SELECT * FROM products 
WHERE MATCH(product_name) AGAINST('phone' IN BOOLEAN MODE);

-- 創(chuàng)建索引
CREATE INDEX idx_price ON products(price);

-- 以下查詢無法使用索引
SELECT * FROM products WHERE price + 100 > 500;

-- 優(yōu)化后的查詢，可以使用索引
SELECT * FROM products WHERE price > 500 - 100;

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 以下查詢無法充分利用索引
SELECT * FROM users WHERE age = 25 AND city = 'New York' AND name = 'John';

-- 優(yōu)化后的查詢，更容易使用索引
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'New York';

-- 創(chuàng)建索引
CREATE INDEX idx_deleted_at ON users(deleted_at);

-- 以下查詢可能無法使用索引
SELECT * FROM users WHERE deleted_at IS NULL;

-- 創(chuàng)建表時使用NOT NULL約束和默認(rèn)值
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1
);

EXPLAIN SELECT * FROM users WHERE deleted_at IS NULL;

-- 創(chuàng)建索引
CREATE INDEX idx_gender ON users(gender);

-- 假設(shè)性別比例接近1:1，以下查詢可能不使用索引
SELECT * FROM users WHERE gender = 'male';

-- 添加更多條件縮小結(jié)果集
SELECT * FROM users 
WHERE gender = 'male' AND age BETWEEN 25 AND 35;

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_gender_age_name ON users(gender, age, name);

-- 查詢僅需要索引中包含的列
SELECT gender, age, name FROM users WHERE gender = 'male';

-- 創(chuàng)建索引
CREATE INDEX idx_status ON orders(status);

-- 假設(shè)status只有3個值：'pending', 'processing', 'completed'
-- 以下查詢可能不使用索引
SELECT * FROM orders WHERE status = 'completed';

-- 創(chuàng)建復(fù)合索引，將高選擇性的user_id放在前面
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 查詢同時使用user_id和status
SELECT * FROM orders 
WHERE user_id = 10001 AND status = 'completed';

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_age_salary ON employees(age, salary);

-- 以下查詢只能使用索引的age部分，salary部分無法使用
SELECT * FROM employees WHERE age > 30 AND salary > 50000;

-- 調(diào)整索引順序
CREATE INDEX idx_salary_age ON employees(salary, age);

-- 如果條件中salary是等值查詢，age是范圍查詢
SELECT * FROM employees WHERE salary = 50000 AND age > 30;

-- 為不同的查詢模式創(chuàng)建不同的索引
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);

-- 創(chuàng)建索引
CREATE INDEX idx_name ON users(name);

-- 以下查詢無法使用索引排序，會產(chǎn)生filesort
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

-- 創(chuàng)建包含排序列的復(fù)合索引
CREATE INDEX idx_name_create_time ON users(name, create_time);

-- 現(xiàn)在可以使用索引排序
SELECT * FROM users WHERE name = 'John' ORDER BY create_time;

-- 創(chuàng)建包含混合排序方向的索引
CREATE INDEX idx_name_time_score ON users(name ASC, create_time DESC, score ASC);

-- 可以高效使用索引
SELECT * FROM users 
WHERE name = 'John' 
ORDER BY create_time DESC, score ASC;

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'completed';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 設(shè)置慢查詢閾值為1秒

-- 查看查詢性能統(tǒng)計
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

SET profiling = 1;
SELECT * FROM users WHERE email LIKE '%@example.com';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;