MySQL索引与性能优化入门：让查询提速的秘密武器【MySQL系列】

-- 普通索引
CREATE INDEX idx_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_mobile ON users(mobile);

-- 组合索引
CREATE INDEX idx_multi ON orders(user_id, status);

DROP INDEX idx_email ON users;

SHOW INDEX FROM users;

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

CREATE INDEX idx_user_status ON orders(user_id, status);

-- 命中索引
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

-- 未命中组合索引
SELECT * FROM orders WHERE status = 'paid';

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

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

-- 查询使用覆盖索引
SELECT name, age FROM users WHERE name = 'Tom';

-- 不走索引
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

-- 优化后
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

-- 分页慢
SELECT * FROM users ORDER BY id LIMIT 10000, 10;

-- 延迟关联优化
SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 10000, 1) LIMIT 10;

DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;

SELECT * FROM users WHERE email = 'abc@example.com';

CREATE INDEX idx_email ON users(email);

EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com';
-- type: ref, key: idx_email, rows: 1

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(100) NOT NULL,
  email VARCHAR(100),
  status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
);

CREATE TABLE permissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

CREATE TABLE user_role (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE role_permission (
  role_id INT,
  permission_id INT,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

CREATE TABLE login_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  ip_address VARCHAR(45),
  login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');

INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');

-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');

-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

START TRANSACTION;

INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色

COMMIT;

START TRANSACTION;

-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);

-- 失败时回滚
ROLLBACK;

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;