// Java中創(chuàng)建索引示例（Spring Data JPA）
@Entity
@Table(indexes = @Index(columnList = "username,email", name = "idx_user_identity"))
public class User {
    @Id
    private Long id;
    private String username; // 索引列
    private String email;    // 索引列
    private Integer age;
    // Getter/Setter省略
}

-- 創(chuàng)建聯(lián)合索引
CREATE INDEX idx_soldier ON army(squad, team, soldier);

-- 有效查詢 ?
SELECT * FROM army WHERE squad = 'A'; 
SELECT * FROM army WHERE squad = 'A' AND team = 2;

-- 索引罷工 ?
SELECT * FROM army WHERE team = 2;        -- 跳過(guò)squad
SELECT * FROM army WHERE soldier = 'Tom'; -- 跳過(guò)頭兩列

// Java代碼中常見(jiàn)的類型錯(cuò)誤
@Query("SELECT u FROM User u WHERE u.username = :name") // username是varchar
User findByUsername(@Param("name") Integer name); // 傳入Integer類型！

SELECT * FROM user WHERE username = 100; 
-- 類型轉(zhuǎn)換導(dǎo)致：username列索引失效！

-- 生日字段有索引
SELECT * FROM user WHERE YEAR(birthday) = 1990;  -- 索引失效 ?

-- 優(yōu)化方案 ?
SELECT * FROM user 
WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

CREATE INDEX idx_sales ON orders(region, amount, product);

-- 索引僅用到 region 和 amount ?
SELECT * FROM orders 
WHERE region = 'East' 
  AND amount > 1000 
  AND product = 'Phone';

-- 即使name和age都有獨(dú)立索引
SELECT * FROM user WHERE name = 'John' OR age = 30;
-- MySQL通常選擇全表掃描！

SELECT * FROM user WHERE name = 'John'
UNION ALL
SELECT * FROM user WHERE age = 30;

ALTER TABLE article ADD INDEX idx_title(title(10));

-- 建立覆蓋索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查詢只需索引列
SELECT user_id, status FROM orders WHERE user_id = 1001;

-- 每月檢查無(wú)用索引
SELECT * FROM sys.schema_unused_indexes;

// 分頁(yè)優(yōu)化Java實(shí)現(xiàn)
public Page<User> getUsers(Long lastId, int limit) {
    String sql = "SELECT * FROM user WHERE id > ? ORDER BY id ASC LIMIT ?";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(), lastId, limit);
}

// MyBatis防類型事故
@Param("userId") Long userId // 而非Integer

-- 每月執(zhí)行
ANALYZE TABLE orders; 
OPTIMIZE TABLE critical_data;