解析PHP底层开发原理：数据库优化和查询性能提升方法概要

<?php
// 创建主键索引
$sql = "ALTER TABLE users ADD PRIMARY KEY (id)";

// 创建唯一索引
$sql = "CREATE UNIQUE INDEX username_unique ON users(username)";

// 创建复合索引
$sql = "CREATE INDEX
    idx_name
ON
    users (last_name, first_name)";

// 创建聚簇索引
$sql = "CREATE CLUSTERED INDEX
    idx_name
ON
    users (age)";
?>

<?php
// 避免使用SELECT *
$sql = "SELECT column1, column2 FROM users";

// 使用JOIN
$sql = "SELECT users.username, orders.order_id
    FROM users
    INNER JOIN orders ON users.id = orders.user_id";

// 子查询优化
$sql = "SELECT column1, column2 FROM users 
    WHERE column1 IN (
        SELECT column3 FROM table2
        WHERE column2 = 'value'
    )";

// LIMIT优化
$sql = "SELECT column1, column2 FROM users LIMIT 10";
?>

<?php
// 批量插入
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ss", $username, $email);
foreach ($data as $row) {
    $username = $row['username'];
    $email = $row['email'];
    $stmt->execute();
}

// 批量更新
$sql = "UPDATE users SET status = ? WHERE id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ii", $status, $id);
foreach ($data as $row) {
    $status = $row['status'];
    $id = $row['id'];
    $stmt->execute();
}

// 批量删除
$sql = "DELETE FROM users WHERE id IN (1, 2, 3)";
$stmt = $mysqli->prepare($sql);
$stmt->execute();
?>