-- name 上有二级索引
SELECT * FROM user WHERE name = '张三';

-- 建立联合索引 (name, age)
CREATE INDEX idx_name_age ON user(name, age);

-- 只查 name 和 age，索引里全都有，Extra 显示 Using index
SELECT name, age FROM user WHERE name = '张三';

CREATE TABLE `user` (
  `id`       bigint NOT NULL AUTO_INCREMENT,
  `name`     varchar(50)  NOT NULL,
  `phone`    varchar(20)  NOT NULL,
  `age`      int          NOT NULL,
  `city`     varchar(20)  NOT NULL,
  `status`   tinyint      NOT NULL,
  `create_time` datetime  NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_city` (`name`, `age`, `city`),
  KEY `idx_phone` (`phone`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ✅ 走索引，用到 name
SELECT * FROM user WHERE name = '张三';

-- ✅ 走索引，用到 name + age
SELECT * FROM user WHERE name = '张三' AND age = 28;

-- ✅ 走索引，三个都用上
SELECT * FROM user WHERE name = '张三' AND age = 28 AND city = '杭州';

-- ❌ 跳过 name 直接从 age 查，索引失效
SELECT * FROM user WHERE age = 28;

-- ❌ 跳过 age，name 之后的 city 用不上
SELECT * FROM user WHERE name = '张三' AND city = '杭州';

-- ❌ 对索引列做函数运算，B+Tree 的有序性被破坏
SELECT * FROM user WHERE YEAR(create_time) = 2026;

-- ✅ 改写成范围查询
SELECT * FROM user
WHERE create_time >= '2026-01-01 00:00:00'
  AND create_time <  '2027-01-01 00:00:00';

-- ❌
SELECT * FROM user WHERE SUBSTRING(phone, 1, 3) = '138';
-- ✅
SELECT * FROM user WHERE phone LIKE '138%';

-- ❌
SELECT * FROM user WHERE DATE(create_time) = '2026-08-31';
-- ✅
SELECT * FROM user
WHERE create_time >= '2026-08-31 00:00:00'
  AND create_time <  '2026-09-01 00:00:00';

-- ❌ age 参与了运算
SELECT * FROM user WHERE age + 1 = 29;

-- ✅ 把计算挪到右边
SELECT * FROM user WHERE age = 29 - 1;

-- phone 是 varchar，这里却用数字比较
-- ❌ MySQL 会对整列做 CAST 转换，等价于在索引列上加函数
SELECT * FROM user WHERE phone = 13800138000;

-- ✅ 类型保持一致
SELECT * FROM user WHERE phone = '13800138000';

-- ✅ 前缀匹配，能用到索引的有序性
SELECT * FROM user WHERE name LIKE '张%';

-- ❌ 前缀不确定，无法从根节点开始定位
SELECT * FROM user WHERE name LIKE '%三';

-- ❌ 同上
SELECT * FROM user WHERE name LIKE '%张%';

-- 建全文索引
ALTER TABLE user ADD FULLTEXT INDEX ft_name (name);

-- 使用全文检索
SELECT * FROM user WHERE MATCH(name) AGAINST('张三' IN BOOLEAN MODE);

-- status 没有索引，优化器只能全表扫（否则要扫两遍再合并）
-- ❌
SELECT * FROM user WHERE name = '张三' OR status = 1;

-- ✅ 方案一：给 status 也建索引
ALTER TABLE user ADD INDEX idx_status (status);

-- ✅ 方案二：拆成两个查询用 UNION
SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE status = 1;

-- ❌ 否定条件的匹配范围太广，优化器倾向全表
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE age NOT IN (20, 30);

-- ✅ 改写为正向范围，前提是能表达
SELECT * FROM user WHERE status IN (0, 2, 3);

-- 如果 name 列绝大部分是 NULL，这个查询命中极少，会走索引
SELECT * FROM user WHERE name IS NULL;

-- IS NOT NULL 命中比例高，通常全表扫
SELECT * FROM user WHERE name IS NOT NULL;

ALTER TABLE user
  MODIFY COLUMN name varchar(50) NOT NULL DEFAULT '';

-- 索引 (name, age, city)
-- age 用了范围，city 用不上，key_len 会暴露这一点
SELECT * FROM user WHERE name = '张三' AND age > 20 AND city = '杭州';

-- 调整索引顺序，让等值列在前
CREATE INDEX idx_name_city_age ON user(name, city, age);

-- 这样 name 和 city 都能精确定位，age 做范围
SELECT * FROM user WHERE name = '张三' AND city = '杭州' AND age > 20;

-- t1.phone 是 utf8mb4，t2.phone 是 utf8
-- ❌ 关联字段字符集不一致，索引失效
SELECT * FROM t1 JOIN t2 ON t1.phone = t2.phone;

-- ✅ 统一字符集
ALTER TABLE t2 MODIFY phone varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SHOW FULL COLUMNS FROM t1 LIKE 'phone';
SHOW FULL COLUMNS FROM t2 LIKE 'phone';

-- 假设 status=1 占了全表 90% 的行
-- 优化器判断：走索引要回表 90 万次，不如直接顺序扫全表
SELECT * FROM user WHERE status = 1;

CREATE INDEX idx_status_name ON user(status, name);
-- 只查索引内的字段，不回表，优化器就愿意走索引
SELECT name FROM user WHERE status = 1;

SELECT * FROM user FORCE INDEX (idx_status) WHERE status = 1;

-- 假设 gender 只有 男/女 两个值，10 万行里男女各 5 万
-- 这个索引几乎不会被引擎选中
CREATE INDEX idx_gender ON user(gender);

SELECT
  COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,
  COUNT(DISTINCT phone)  / COUNT(*) AS phone_selectivity,
  COUNT(DISTINCT name)   / COUNT(*) AS name_selectivity
FROM user;

EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 28;

EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = 28;

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看当前正在执行的慢查询
SELECT id, user, host, db, command, time, state, LEFT(info, 100) AS sql_text
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 2
ORDER BY time DESC;

-- 耗时 12 秒
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;

-- 耗时 0.3 秒
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) t ON o.id = t.id;

-- 已知上一页最后一条的 create_time 和 id
SELECT * FROM orders
WHERE create_time < '2026-08-01 10:00:00'
   OR (create_time = '2026-08-01 10:00:00' AND id < 9527)
ORDER BY create_time DESC, id DESC
LIMIT 20;

SELECT u.name, o.amount
FROM user u
JOIN orders o ON u.phone = o.phone
WHERE u.city = '杭州';

-- user.phone:   utf8mb4_general_ci
-- orders.phone: utf8_general_ci

ALTER TABLE orders
  MODIFY phone varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- Extra: Using where; Using filesort
SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 10;

-- 删除冗余的单列索引
DROP INDEX idx_user_id ON orders;
-- 建立联合索引，等值列在前、排序列在后
CREATE INDEX idx_user_create ON orders(user_id, create_time);