-- 查看当前连接
SHOW PROCESSLIST;

-- 查看连接数配置
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'max_user_connections';

-- 查看当前连接状态
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Threads_connected';

-- 查看支持的存储引擎
SHOW ENGINES;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'table_name';

-- 修改表的存储引擎
ALTER TABLE table_name ENGINE = InnoDB;

-- InnoDB状态查看
SHOW ENGINE INNODB STATUS;

-- 关键配置参数
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 查看缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

-- 查看缓冲池状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 建议配置（物理内存的70-80%）
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB

-- 重做日志配置
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 建议配置
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1  -- 最高安全性

-- 查看撤销日志配置
SHOW VARIABLES LIKE 'innodb_undo_logs';
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';

-- MySQL 8.0配置
SET GLOBAL innodb_undo_log_truncate = ON;
SET GLOBAL innodb_max_undo_log_size = 1073741824; -- 1GB

-- 启用二进制日志
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

-- 查看二进制日志状态
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 用户表设计
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 无符号整数作为主键
    age TINYINT UNSIGNED,                        -- 年龄：0-255足够
    score DECIMAL(5,2),                          -- 分数：xxx.xx格式
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00, -- 余额：精确到分
    login_count BIGINT UNSIGNED DEFAULT 0        -- 登录次数：大数值
);

-- 性能对比测试
SELECT 
    BENCHMARK(10000000, id + 1) as int_calc,
    BENCHMARK(10000000, balance + 1.00) as decimal_calc
FROM users LIMIT 1;

-- 用户信息表
CREATE TABLE user_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,          -- 用户名：变长，50字符足够
    phone CHAR(11),                                -- 手机号：固定11位
    email VARCHAR(100),                            -- 邮箱：变长，最长100字符
    address VARCHAR(500),                          -- 地址：变长，较长
    bio TEXT,                                      -- 个人简介：大文本
    avatar_data MEDIUMBLOB,                        -- 头像数据：二进制
    settings JSON,                                 -- 用户设置：JSON格式
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 性能测试：CHAR vs VARCHAR
-- CHAR(50) vs VARCHAR(50) 在不同数据长度下的存储对比
SELECT 
    LENGTH('test') as actual_length,
    CHAR_LENGTH(CHAR_FIELD) as char_storage,
    CHAR_LENGTH(VARCHAR_FIELD) as varchar_storage
FROM test_table;

-- 订单表设计
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,                      -- 订单日期
    order_time TIME,                               -- 下单时间点
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP,        -- 自动更新时间
    delivery_year YEAR,                            -- 发货年份
    
    INDEX idx_order_date (order_date),
    INDEX idx_created_at (created_at)
);

-- 日期时间查询示例
SELECT 
    id,
    order_date,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') as formatted_time,
    YEAR(created_at) as order_year,
    MONTH(created_at) as order_month,
    DAYOFWEEK(created_at) as weekday
FROM orders 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 时区处理
SELECT 
    created_at,
    CONVERT_TZ(created_at, '+00:00', '+08:00') as beijing_time
FROM orders;

-- 用户配置表
CREATE TABLE user_configs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    config JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id)
);

-- 插入JSON数据
INSERT INTO user_configs (user_id, config) VALUES 
(1, '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "sms": false}}'),
(2, '{"theme": "light", "language": "en-US", "notifications": {"email": false, "sms": true}}');

-- JSON查询和操作
SELECT 
    user_id,
    JSON_EXTRACT(config, '$.theme') as theme,
    config->'$.language' as language,               -- 简化语法
    config->>'$.notifications.email' as email_notify, -- 去除引号
    JSON_KEYS(config->'$.notifications') as notify_keys
FROM user_configs;

-- JSON修改
UPDATE user_configs 
SET config = JSON_SET(config, '$.theme', 'auto', '$.version', '1.0')
WHERE user_id = 1;

-- JSON数组操作
SELECT 
    JSON_ARRAY('apple', 'banana', 'orange') as fruits,
    JSON_OBJECT('name', 'John', 'age', 30) as person;

-- 创建虚拟列索引提高JSON查询性能
ALTER TABLE user_configs 
ADD COLUMN theme_virtual VARCHAR(20) 
GENERATED ALWAYS AS (config->>'$.theme') VIRTUAL,
ADD INDEX idx_theme (theme_virtual);

-- 错误设计：违反1NF
CREATE TABLE orders_bad (
    id INT PRIMARY KEY,
    customer_info VARCHAR(500)  -- 包含姓名、电话、地址等多个信息
);

-- 正确设计：符合1NF
CREATE TABLE orders_good (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_phone CHAR(11),
    customer_address VARCHAR(300)
);

-- 错误设计：违反2NF
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),    -- 依赖于product_id，不依赖于组合主键
    product_price DECIMAL(10,2),  -- 依赖于product_id，不依赖于组合主键
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 正确设计：符合2NF，拆分表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 优秀的表设计示例
CREATE TABLE users (
    -- 主键设计
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 基本信息：选择合适的数据类型
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    phone CHAR(11) COMMENT '手机号',
    
    -- 状态字段：使用枚举或小整数
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态：1-正常，2-禁用，3-删除',
    gender ENUM('M', 'F', 'U') DEFAULT 'U' COMMENT '性别：M-男，F-女，U-未知',
    
    -- 时间字段：标准化
    birthday DATE COMMENT '生日',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 索引设计
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 策略1：自增整数主键（推荐）
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 简单、高效、有序
    order_no VARCHAR(32) NOT NULL UNIQUE,        -- 业务编号作为唯一索引
    -- 其他字段...
);

-- 策略2：UUID主键（分布式场景）
CREATE TABLE distributed_orders (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),    -- UUID保证全局唯一
    -- 其他字段...
);

-- 策略3：雪花算法ID（推荐分布式场景）
CREATE TABLE snowflake_orders (
    id BIGINT UNSIGNED PRIMARY KEY,              -- 雪花算法生成的ID
    -- 其他字段...
);

-- 使用ENUM类型
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') 
           DEFAULT 'pending' COMMENT '订单状态',
    -- 其他字段...
);

-- 使用TINYINT + 常量定义（推荐）
CREATE TABLE orders_v2 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '状态：1-待付款，2-已付款，3-已发货，4-已送达，5-已取消',
    INDEX idx_status (status)
);

CREATE TABLE financial_records (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(15,2) NOT NULL COMMENT '金额，精确到分',
    currency CHAR(3) DEFAULT 'CNY' COMMENT '货币代码',
    -- 避免使用FLOAT或DOUBLE存储金额
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 开发环境：使用外键保证数据一致性
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 生产环境：通常不使用外键，在应用层保证一致性
CREATE TABLE orders_prod (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    INDEX idx_user_id (user_id)
    -- 不使用外键，在应用代码中保证数据一致性
);

-- 查看当前字符集配置
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 创建数据库时指定字符集
CREATE DATABASE myapp 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci;

-- 创建表时指定字符集
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;

-- 修改已存在表的字符集
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 不同排序规则的比较
CREATE TABLE test_collation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_unicode VARCHAR(100) COLLATE utf8mb4_unicode_ci,
    name_general VARCHAR(100) COLLATE utf8mb4_general_ci,
    name_bin VARCHAR(100) COLLATE utf8mb4_bin
);

INSERT INTO test_collation (name_unicode, name_general, name_bin) 
VALUES ('Apple', 'Apple', 'Apple'), ('apple', 'apple', 'apple');

-- 查询结果对比
SELECT * FROM test_collation WHERE name_unicode = 'apple';  -- 找到两条
SELECT * FROM test_collation WHERE name_bin = 'apple';      -- 只找到一条

-- 1. 备份数据
mysqldump --default-character-set=utf8mb4 mydb > backup.sql

-- 2. 检查当前字符集
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mydb';

-- 3. 转换数据库字符集
ALTER DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 4. 转换表字符集
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 5. 验证转换结果
SHOW FULL COLUMNS FROM users;

-- 示例：查找 id = 35 的记录
SELECT * FROM users WHERE id = 35;

-- 示例：查找 25 <= id <= 45 的记录
SELECT * FROM users WHERE id BETWEEN 25 AND 45;

-- 创建测试表和索引
CREATE TABLE test_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_age (age),
    INDEX idx_name_age (name, age)
);

-- 插入测试数据
INSERT INTO test_index (name, age, city) 
SELECT 
    CONCAT('user_', i), 
    20 + (i % 50), 
    CASE (i % 3) 
        WHEN 0 THEN 'Beijing' 
        WHEN 1 THEN 'Shanghai' 
        ELSE 'Guangzhou' 
    END
FROM (
    SELECT @i := @i + 1 as i 
    FROM information_schema.tables t1, information_schema.tables t2, 
         (SELECT @i := 0) r 
    LIMIT 100000
) nums;

-- 分析索引使用情况
EXPLAIN SELECT * FROM test_index WHERE age = 25;
EXPLAIN SELECT * FROM test_index WHERE age BETWEEN 25 AND 30;
EXPLAIN SELECT * FROM test_index WHERE name = 'user_1000';

-- 回表查询：先查二级索引，再查主键索引
SELECT * FROM users WHERE name = 'John';

-- 覆盖索引：无需回表，直接从索引获取所需数据
SELECT id, name FROM users WHERE name = 'John';

-- 分析是否发生回表
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John';

-- 创建普通索引
CREATE INDEX idx_age ON users(age);
ALTER TABLE users ADD INDEX idx_age (age);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
ALTER TABLE users ADD UNIQUE KEY idx_email (email);

-- 删除索引
DROP INDEX idx_age ON users;
ALTER TABLE users DROP INDEX idx_age;

-- 1. 等值查询
SELECT * FROM users WHERE age = 25;

-- 2. 范围查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE age > 25;

-- 3. 排序操作
SELECT * FROM users ORDER BY age;

-- 4. 分组操作
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 等价写法
ALTER TABLE users ADD INDEX idx_name_age_city (name, age, city);

-- 创建复合索引 (name, age, city)
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- ✅ 可以使用索引的查询
SELECT * FROM users WHERE name = 'John';                          -- 使用索引：name
SELECT * FROM users WHERE name = 'John' AND age = 25;             -- 使用索引：name, age
SELECT * FROM users WHERE name = 'John' AND age = 25 AND city = 'Beijing'; -- 使用索引：name, age, city
SELECT * FROM users WHERE name = 'John' AND city = 'Beijing';     -- 使用索引：name

-- ❌ 无法使用索引的查询
SELECT * FROM users WHERE age = 25;                               -- 不能使用索引
SELECT * FROM users WHERE city = 'Beijing';                       -- 不能使用索引
SELECT * FROM users WHERE age = 25 AND city = 'Beijing';          -- 不能使用索引

-- 设计示例：订单查询索引
-- 查询场景：按用户查订单、按时间范围查订单、按状态查订单

-- 方案1：按查询频率设计
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 方案2：按区分度设计  
CREATE INDEX idx_user_time_status ON orders(user_id, created_at, status);

-- 分析不同查询的索引使用情况
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND created_at > '2023-01-01';
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1 AND created_at > '2023-01-01';

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- ✅ 覆盖索引查询：查询字段都在索引中
SELECT name, age FROM users WHERE name = 'John';
SELECT name FROM users WHERE name = 'John';
SELECT age FROM users WHERE name = 'John';

-- ❌ 非覆盖索引查询：需要回表获取city字段
SELECT name, age, city FROM users WHERE name = 'John';

-- 验证是否使用覆盖索引
EXPLAIN SELECT name, age FROM users WHERE name = 'John';
-- 查看Extra字段，如果显示"Using index"表示使用覆盖索引

-- 场景：用户列表页面，显示用户名、年龄、注册时间
-- 频繁查询：按注册时间排序，按年龄范围筛选

-- 传统索引设计
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_created_at ON users(created_at);

-- 覆盖索引设计：将查询需要的字段都包含进来
CREATE INDEX idx_age_created_name ON users(age, created_at, name);

-- 比较查询性能
-- 传统索引：需要回表查询
EXPLAIN SELECT name, age, created_at FROM users WHERE age BETWEEN 20 AND 30 ORDER BY created_at;

-- 覆盖索引：无需回表
EXPLAIN SELECT name, age, created_at FROM users WHERE age BETWEEN 20 AND 30 ORDER BY created_at;

-- 分析字符串字段的区分度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) as prefix_15,
    COUNT(DISTINCT email) / COUNT(*) as full_column
FROM users;

-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 比较存储空间
SELECT 
    index_name,
    index_length,
    cardinality 
FROM information_schema.statistics 
WHERE table_name = 'users' AND column_name = 'email';

-- 分析不同前缀长度的区分度
SELECT 
    prefix_length,
    COUNT(DISTINCT LEFT(url, prefix_length)) as distinct_count,
    COUNT(DISTINCT LEFT(url, prefix_length)) / COUNT(*) as selectivity
FROM 
    urls,
    (SELECT 5 as prefix_length UNION SELECT 10 UNION SELECT 15 UNION SELECT 20) as lengths
GROUP BY prefix_length
ORDER BY prefix_length;

-- 选择区分度达到95%以上的最短前缀长度

-- 复合索引：(name, age, city)
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- ❌ 索引失效：跳过了最左字段name
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = 'Beijing';
SELECT * FROM users WHERE age = 25 AND city = 'Beijing';

-- ✅ 索引有效
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name = 'John' AND age = 25;

-- ❌ 索引失效：对索引字段使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';
SELECT * FROM users WHERE age + 1 = 26;

-- ✅ 索引有效：避免对索引字段使用函数
SELECT * FROM users WHERE name = 'JOHN';  -- 或在应用层转换
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';
SELECT * FROM users WHERE age = 25;

-- 假设phone字段是VARCHAR类型
-- ❌ 索引失效：发生隐式类型转换
SELECT * FROM users WHERE phone = 13812345678;  -- 数字类型

-- ✅ 索引有效：使用正确的类型
SELECT * FROM users WHERE phone = '13812345678';  -- 字符串类型

-- ❌ 索引失效：负向条件通常不能使用索引
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;
SELECT * FROM users WHERE NOT age = 25;

-- ✅ 可能使用索引：正向条件
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age IN (20, 25, 30);

-- ❌ 索引失效：前导通配符
SELECT * FROM users WHERE name LIKE '%john';
SELECT * FROM users WHERE name LIKE '%john%';

-- ✅ 索引有效：后置通配符
SELECT * FROM users WHERE name LIKE 'john%';

-- 解决方案：全文索引
ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('john');

-- ❌ 可能索引失效：OR条件中有字段没有索引
SELECT * FROM users WHERE name = 'John' OR city = 'Beijing';  -- city没有索引

-- ✅ 索引有效：OR条件中所有字段都有索引
SELECT * FROM users WHERE name = 'John' OR email = '[email protected]';

-- 或者使用UNION改写
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE city = 'Beijing';

-- 分析表中各字段的选择性
SELECT 
    'id' as column_name,
    COUNT(DISTINCT id) as distinct_count,
    COUNT(*) as total_count,
    COUNT(DISTINCT id) / COUNT(*) as selectivity
FROM users
UNION ALL
SELECT 
    'name',
    COUNT(DISTINCT name),
    COUNT(*),
    COUNT(DISTINCT name) / COUNT(*)
FROM users
UNION ALL
SELECT 
    'age',
    COUNT(DISTINCT age),
    COUNT(*),
    COUNT(DISTINCT age) / COUNT(*)
FROM users
UNION ALL
SELECT 
    'gender',
    COUNT(DISTINCT gender),
    COUNT(*),
    COUNT(DISTINCT gender) / COUNT(*)
FROM users;

-- 高选择性字段（0.8+）：适合建立索引
-- id: 1.0（主键）
-- email: 0.95+（邮箱通常唯一）
-- phone: 0.90+（手机号通常唯一）

-- 中等选择性字段（0.1-0.8）：可以考虑建立索引
-- name: 0.3-0.7（姓名有一定重复）
-- age: 0.05-0.1（年龄值有限）

-- 低选择性字段（<0.1）：不适合单独建立索引
-- gender: 0.5（男/女两个值）
-- status: 0.1（状态值有限）

-- 复合索引设计：将低选择性字段与高选择性字段组合
CREATE INDEX idx_gender_age_name ON users(gender, age, name);

-- 查看索引使用统计
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    PAGES,
    COMMENT
FROM information_schema.statistics 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME;

-- 查看从未使用的索引（MySQL 5.7+）
SELECT 
    s.table_schema,
    s.table_name,
    s.index_name
FROM information_schema.statistics s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
    ON s.table_schema = p.object_schema
    AND s.table_name = p.object_name
    AND s.index_name = p.index_name
WHERE p.index_name IS NULL
    AND s.table_schema = 'your_database'
    AND s.index_name != 'PRIMARY';

-- 检查重复索引
SELECT 
    table_schema,
    table_name,
    GROUP_CONCAT(index_name) as duplicate_indexes,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) as columns
FROM information_schema.statistics
WHERE table_schema = 'your_database'
GROUP BY table_schema, table_name, 
         GROUP_CONCAT(column_name ORDER BY seq_in_index)
HAVING COUNT(*) > 1;

-- 重建索引（清理索引碎片）
ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name (name);

-- 在线DDL（MySQL 5.6+）
ALTER TABLE users DROP INDEX idx_name, ADD INDEX idx_name (name), ALGORITHM=INPLACE, LOCK=NONE;

-- 分析表统计信息
ANALYZE TABLE users;

-- 检查表和索引
CHECK TABLE users;

-- 优化表（整理碎片）
OPTIMIZE TABLE users;

-- 监控索引效率
SHOW STATUS LIKE 'Handler_read%';

-- Handler_read_first: 读取索引第一个条目的次数
-- Handler_read_key: 通过索引读取的次数
-- Handler_read_next: 顺序读取下一行的次数
-- Handler_read_rnd: 随机读取的次数

-- 索引使用率 = Handler_read_key / (Handler_read_key + Handler_read_rnd)

-- 优秀的执行计划特征
-- type: const, eq_ref, ref
-- key: 使用了合适的索引
-- rows: 扫描行数少
-- Extra: Using index (覆盖索引)

-- 需要优化的执行计划
-- type: ALL, index (全表扫描)
-- key: NULL (没有使用索引)
-- rows: 大量行扫描
-- Extra: Using filesort, Using temporary

-- 开启优化器追踪
SET optimizer_trace='enabled=on';

-- 执行查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 查看优化器决策过程
SELECT * FROM information_schema.optimizer_trace;

-- 关闭追踪
SET optimizer_trace='enabled=off';

-- ✅ 优化后：使用索引
SELECT * FROM users WHERE user_id = 1001;
SELECT * FROM users WHERE status = 1 AND created_at > '2023-01-01';

-- ❌ 优化前：无法使用索引
SELECT * FROM users WHERE YEAR(created_at) = 2023;
SELECT * FROM users WHERE age + 1 = 26;

-- 复合索引：(status, user_id, created_at)
-- ✅ 优化：按索引顺序组织条件
SELECT * FROM orders 
WHERE status = 1 
  AND user_id = 1001 
  AND created_at > '2023-01-01';

-- 1. 小表驱动大表
-- ✅ 优化：用户表(小)驱动订单表(大)
SELECT o.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 2. 确保JOIN字段有索引
-- ✅ 优化：在关联字段建立索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON users(status);

-- 3. 避免笛卡尔积
-- ❌ 危险：缺少关联条件
SELECT * FROM users u, orders o WHERE u.status = 1;
-- ✅ 安全：明确关联条件
SELECT * FROM users u, orders o WHERE u.id = o.user_id AND u.status = 1;

-- ❌ 性能差：大偏移量
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;

-- ✅ 优化方案1：使用主键范围
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 20;

-- ✅ 优化方案2：延迟关联
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 20
) t ON u.id = t.id;

-- ❌ 低效：逐条插入
INSERT INTO users (name, email) VALUES ('user1', '[email protected]');
INSERT INTO users (name, email) VALUES ('user2', '[email protected]');

-- ✅ 高效：批量插入
INSERT INTO users (name, email) VALUES 
('user1', '[email protected]'),
('user2', '[email protected]'),
('user3', '[email protected]');

-- ✅ 大量数据：LOAD DATA
LOAD DATA INFILE 'users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

-- ✅ 使用索引条件
UPDATE users SET status = 1 WHERE id = 1001;

-- ✅ 批量更新
UPDATE users SET status = 1 WHERE id IN (1001, 1002, 1003);

-- ✅ 避免全表更新
UPDATE users SET updated_at = NOW() WHERE status = 0 LIMIT 1000;

-- ❌ 性能差：相关子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 1
);

-- ✅ 优化：转换为JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 1;

-- 大表 EXISTS 小表：使用 EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM user_profiles p WHERE p.user_id = u.id
);

-- 小表 IN 大表：使用 IN
SELECT * FROM users
WHERE id IN (SELECT user_id FROM user_profiles);

-- 开始事务
START TRANSACTION;

-- 执行一系列操作
INSERT INTO orders (user_id, status, created_at) VALUES (1, 'pending', NOW());
UPDATE users SET status = 1 WHERE id = 1;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;

-- 执行一系列操作
INSERT INTO orders (user_id, status, created_at) VALUES (2, 'pending', NOW());
UPDATE users SET status = 1 WHERE id = 2;

-- 回滚到保存点
ROLLBACK TO sp1;

-- 事务A
START TRANSACTION;
INSERT INTO orders (user_id, status, created_at) VALUES (1, 'pending', NOW());

-- 事务B
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 1;  -- 读取到未提交的数据

-- 事务A
COMMIT;

-- 事务B
COMMIT;  -- 提交后，事务A的插入操作对事务B可见

-- 事务A
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 1;  -- 读取到数据

-- 事务B
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE user_id = 1;  -- 修改数据

-- 事务A
COMMIT;

-- 事务B
COMMIT;  -- 提交后，事务A的读取操作读取到修改后的数据

-- 事务A
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 1;  -- 读取到数据

-- 事务B
START TRANSACTION;
INSERT INTO orders (user_id, status, created_at) VALUES (1, 'paid', NOW());  -- 插入新数据

-- 事务A
COMMIT;

-- 事务B
COMMIT;  -- 提交后，事务A的读取操作读取到插入的新数据

-- 事务A
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE user_id = 1;  -- 更新数据

-- 事务B
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE user_id = 1;  -- 更新数据

-- 事务A
COMMIT;

-- 事务B
COMMIT;  -- 提交后，事务A的更新操作被事务B覆盖

-- 记录锁：锁定具体的索引记录
BEGIN;
SELECT * FROM users WHERE id = 100 FOR UPDATE;  -- 锁定id=100这一行
-- 其他事务无法修改id=100的记录，但可以插入其他记录

-- 间隙锁：锁定索引记录之间的间隙，防止幻读
BEGIN;
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- 锁定(10,20)区间，防止在此区间插入新记录
-- 注意：间隙锁与间隙锁兼容，不会相互阻塞

-- 临键锁：记录锁+间隙锁，锁定记录及其前面的间隙
-- 这是InnoDB默认的行锁算法
BEGIN;
SELECT * FROM users WHERE age >= 25 FOR UPDATE;
-- 锁定age>=25的所有记录，以及(24,25]的间隙

-- 创建测试表
CREATE TABLE lock_demo (
    id INT PRIMARY KEY,
    age INT,
    name VARCHAR(50),
    KEY idx_age (age)
);

INSERT INTO lock_demo VALUES 
(1, 10, 'Alice'),
(5, 20, 'Bob'),
(10, 30, 'Charlie'),
(15, 40, 'David');

-- 测试1：唯一索引等值查询（记录存在）
-- 会话1
BEGIN;
SELECT * FROM lock_demo WHERE id = 5 FOR UPDATE;  -- 只锁定id=5

-- 会话2
INSERT INTO lock_demo VALUES (3, 15, 'Eve');      -- 成功
INSERT INTO lock_demo VALUES (5, 25, 'Frank');    -- 阻塞，主键冲突
UPDATE lock_demo SET name = 'Bob2' WHERE id = 5;  -- 阻塞，记录被锁

-- 测试2：普通索引等值查询
-- 会话1  
BEGIN;
SELECT * FROM lock_demo WHERE age = 20 FOR UPDATE;  -- Next-Key锁

-- 会话2
INSERT INTO lock_demo VALUES (3, 15, 'Eve');        -- 成功
INSERT INTO lock_demo VALUES (6, 20, 'Frank');      -- 阻塞，age=20被锁
INSERT INTO lock_demo VALUES (7, 25, 'Grace');      -- 阻塞，(20,30)间隙被锁

-- 意向锁解决表锁与行锁的冲突检测问题

-- 场景：事务A持有行级锁，事务B申请表级锁
-- 会话A：获取行级锁时，自动获取意向锁
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 自动获取IX锁

-- 会话B：申请表级锁时，检查意向锁
LOCK TABLES users WRITE;  -- 检查到IX锁存在，等待

-- 如果没有意向锁，会话B需要检查所有行锁，效率极低

-- 转账场景死锁
-- 会话A：A账户转给B账户
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';  -- 锁定A账户
-- 模拟处理时间
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';  -- 等待B账户

-- 会话B：B账户转给A账户  
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 'B';   -- 锁定B账户
-- 模拟处理时间
UPDATE accounts SET balance = balance + 50 WHERE account_id = 'A';   -- 等待A账户

-- 结果：形成死锁，InnoDB自动检测并回滚其中一个事务

-- 预防策略1：按ID顺序加锁
BEGIN;
-- 总是按照account_id字典序加锁
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

-- 预防策略2：快速事务
BEGIN;
-- 一次性获取所有需要的锁
SELECT * FROM accounts WHERE account_id IN ('A', 'B') FOR UPDATE;
-- 快速完成所有操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

-- 预防策略3：降低隔离级别（适用场景有限）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS;

-- 启用死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 监控死锁频率
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 监控锁等待情况
SHOW STATUS LIKE 'Innodb_lock_wait%';

-- 原始数据
id | name  | age | DB_TRX_ID | DB_ROLL_PTR
1  | Alice | 25  | 100       | ptr1

-- 事务101修改后
id | name  | age | DB_TRX_ID | DB_ROLL_PTR
1  | Bob   | 25  | 101       | ptr2
                     ↓
              [Undo Log]
              name=Alice, age=25, trx_id=100

-- 事务102再次修改
id | name  | age | DB_TRX_ID | DB_ROLL_PTR  
1  | Charlie| 30 | 102       | ptr3
                     ↓
              [Undo Log]
              name=Bob, age=25, trx_id=101 → ptr2
                                    ↓
                            [Undo Log]
                            name=Alice, age=25, trx_id=100

-- Read View结构
ReadView {
    m_low_limit_id,     -- 最大事务ID+1
    m_up_limit_id,      -- 最小活跃事务ID  
    m_creator_trx_id,   -- 创建该ReadView的事务ID
    m_ids              -- 活跃事务ID列表
}

-- 可见性判断规则
-- 1. trx_id < m_up_limit_id：已提交事务，可见
-- 2. trx_id >= m_low_limit_id：未来事务，不可见
-- 3. m_up_limit_id <= trx_id < m_low_limit_id：
--    if trx_id in m_ids：活跃事务，不可见
--    else：已提交事务，可见

-- READ COMMITTED：每次查询创建新ReadView
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM users WHERE id = 1;  -- ReadView1
-- 其他事务提交修改
SELECT * FROM users WHERE id = 1;  -- ReadView2，可能看到新值

-- REPEATABLE READ：事务开始时创建ReadView
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;  
SELECT * FROM users WHERE id = 1;  -- ReadView固定
-- 其他事务提交修改
SELECT * FROM users WHERE id = 1;  -- 使用相同ReadView，保证一致性

-- 快照读：使用MVCC，读取历史版本
SELECT * FROM users WHERE id = 1;

-- 当前读：加锁读取最新版本
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;         -- 排他锁
UPDATE users SET name = 'New' WHERE id = 1;          -- 排他锁
DELETE FROM users WHERE id = 1;                      -- 排他锁

# CPU监控
top -p $(pidof mysqld)
iostat -x 1

# 内存监控
free -h
cat /proc/meminfo | grep -E "MemTotal|MemFree|MemAvailable|Buffers|Cached"

# 磁盘监控
df -h
iostat -d 1

# 网络监控
iftop
nethogs

-- 连接监控
SHOW STATUS LIKE 'Connections';              -- 总连接数
SHOW STATUS LIKE 'Threads_connected';        -- 当前连接数
SHOW STATUS LIKE 'Threads_running';          -- 活跃连接数
SHOW STATUS LIKE 'Max_used_connections';     -- 历史最大连接数

-- QPS/TPS监控
SHOW STATUS LIKE 'Questions';                -- 总查询数
SHOW STATUS LIKE 'Queries';                  -- 总语句数
SHOW STATUS LIKE 'Com_select';               -- SELECT数量
SHOW STATUS LIKE 'Com_insert';               -- INSERT数量  
SHOW STATUS LIKE 'Com_update';               -- UPDATE数量
SHOW STATUS LIKE 'Com_delete';               -- DELETE数量

-- 缓冲池监控
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 锁监控
SHOW STATUS LIKE 'Innodb_lock_wait%';
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 临时表监控
SHOW STATUS LIKE 'Created_tmp%';

#!/bin/bash
# mysql_monitor.sh

# 计算QPS
mysql -e "
SELECT 
    ROUND(Questions/Uptime, 2) AS QPS,
    ROUND((Com_select + Com_insert + Com_update + Com_delete)/Uptime, 2) AS TPS,
    ROUND(Connections/Uptime, 2) AS CPS
FROM (
    SELECT 
        VARIABLE_VALUE AS Questions
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Questions'
) q,
(
    SELECT 
        VARIABLE_VALUE AS Uptime
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Uptime'  
) u,
(
    SELECT 
        SUM(VARIABLE_VALUE) AS Com_select
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Com_select'
) cs,
-- ... 其他指标
"

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1秒以上的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW STATUS LIKE 'Slow_queries';

-- 实时监控正在执行的查询
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as QUERY
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' 
ORDER BY TIME DESC;

-- 使用EXPLAIN分析查询
EXPLAIN FORMAT=JSON 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id;

-- 使用SHOW PROFILE分析查询耗时
SET profiling = 1;
SELECT COUNT(*) FROM large_table WHERE status = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE '%long_query_time%';

-- 配置慢查询日志
SET GLOBAL slow_query_log = ON;                    -- 启用慢查询日志
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  -- 日志文件路径
SET GLOBAL long_query_time = 2;                    -- 2秒阈值
SET GLOBAL log_queries_not_using_indexes = ON;     -- 记录未使用索引的查询
SET GLOBAL log_slow_admin_statements = ON;         -- 记录管理语句
SET GLOBAL min_examined_row_limit = 1000;          -- 最小扫描行数