如何针对高负载应用程序优化 MySQL 性能？

<code>-- 使用char(2)代替varchar(255)存储国家代码
create table countries (
    country_code char(2), -- 固定长度，效率更高
    name varchar(100)
);</code>

<code>-- 规范化设计示例
create table authors (
    author_id int auto_increment primary key,
    name varchar(100)
);

create table books (
    book_id int auto_increment primary key,
    title varchar(100),
    author_id int,
    foreign key (author_id) references authors(author_id)
);</code>

<code>-- 反规范化设计示例，提升读取速度
create table book_details (
    book_id int,
    title varchar(100),
    author_name varchar(100)
);</code>

<code>-- 为频繁查询的列创建索引
create index idx_author_name on authors (name);</code>

<code>-- 多列查询的复合索引
create index idx_book_author on books (title, author_id);</code>

<code>explain select * from books where title = 'optimization guide';</code>

<code>-- 避免使用SELECT *
select * from books;  -- 不推荐

-- 推荐使用
select title, author_id from books;</code>

<code>select title from books limit 10;</code>

<code>-- 为连接列创建索引
create index idx_author_id on books (author_id);</code>

<code>-- 使用INNER JOIN
select books.title, authors.name
from books
inner join authors on books.author_id = authors.author_id;</code>

<code>set global query_cache_size = 1048576; -- 设置缓存大小
set global query_cache_type = 1; -- 启用查询缓存</code>

<code class="python"># 使用Redis缓存的Python示例
import redis

r = redis.StrictRedis(host='localhost', port=6379, decode_responses=True)
query_key = 'books_all'

if not r.exists(query_key):
    # 从MySQL获取数据
    books = fetch_books_from_mysql()
    r.set(query_key, books, ex=3600)  # 缓存1小时
else:
    books = r.get(query_key)</code>

<code class="sql">-- 按范围分区示例
create table sales (
    sale_id int,
    sale_date date,
    amount decimal(10, 2)
)
partition by range (year(sale_date)) (
    partition p0 values less than (2000),
    partition p1 values less than (2010),
    partition p2 values less than maxvalue
);</code>

<code class="sql">set global slow_query_log = 'on';
set global long_query_time = 2; -- 记录执行时间超过2秒的查询</code>

<code class="sql">SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;</code>