mysql提高效率方法

-- 示例：优化前的查询
SELECT * FROM orders WHERE customer_id = 123;

-- 优化后的查询
SELECT order_id, order_date FROM orders WHERE customer_id = 123;

-- 示例：创建单列索引
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 示例：创建复合索引
CREATE INDEX idx_customer_order ON orders(customer_id, order_date);

-- 示例：调整innodb_buffer_pool_size
[mysqld]
innodb_buffer_pool_size = 2G

-- 示例：按日期分区
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 示例：配置主从复制
-- 主库配置
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb

-- 从库配置
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1