Raven's Wiki

清风皓月任我飞~~~

0%

MySQL 性能优化

发表于 更新于 分类于

1. 1. Table 结构优化

  1. 表优化
    控制单表的长度, 使用 3 大范式拆分表.

  2. 字段优化

    1. 选用合适的字段类型, 如整数类型的处理比字符串处理开销更小
    2. 选用何时的字段长度, 固定长度的表会更快
    3. 尽可能使用not null定义字段

  3. 索引优化
    对于索引而言, 字段长度越小越好, 因为数据库的存储单位是页, 一页中能存下的数据越多越好; 字段离散度越大越好.
    离散度可以通过 select count(distinct column_name) from table_name 查询. count 值越大, 离散度也越大.

    1. 对于频繁查询的列建立索引, 频繁查询的列是指出现在 where, group by, order by, on 中的列

  4. 其他

    1. 对于会有删除操作的大表, 要定期清理存储空间

2. 2. CRUD SQL 优化

  1. 使用连接 (join) 代替子查询
  2. 大的查询可以拆分成多个小的查询, 以便利用查询缓存
  3. 通过延迟关联优化分页查询
    如: 
    1
    2
    3
    4
    select film_id, description from sakila.film order by title limit 50, 5;
    -- 可优化成:
    select film.film_id, film.description from sakila.film 
    inner join (select film_id from sakila.film order by title limit 50, 5) as lim using(film_id);

3. 3. 命令分析

  1. 查看数据库状态

通过类似于 show [session|global] status like @status_name; 命令查询各种状态.
其中 session 是查询当前会话的状态, 默认值; global 是从 MySQL 启动到现在的状态.

status_name 有:

  • com_select: 查询次数
  • com_insert: 插入次数
  • com_update: 更新次数
  • com_delete: 删除次数
  • connections: 登录次数
  • uptime: 运行时长
  • slow_queries: 慢查询次数
  • handler_read%: 索引使用情况. handler_read_key: 这个值越高越好, 越高表示使用索引查询到的次数. handler_read_rnd_next: 这个值越高, 说明查询低效.
  1. 查看数据库参数

通过类似于 show variables like @variables_name; 命令查询各种状态.

  1. 查看 SQL 执行时间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
show variables like 'PROFILING';
set profiling ='ON';
show profiles;
set profiling ='OFF';

set @query_id=1;
select
    state,
    sum(duration) as total_r,
    ROUND(
        100 * sum(duration) / (
            select sum(duration)
            from information_schema.profiling
            where query_id = @quer_id
        ), 2
    ) as pct_r,
    count(*) as calls,
    sum(duration) / count(*) as "r/call"
from information_schema.profiling
where query_id = @query_id
group by state
order by total_r desc;
  1. 表结构优化建议

以下 SQL 语句会给出每一列的优化建议.

1
SELECT column_name FROM table_name procedure analyse();
  1. 定期回收闲置的数据库空间
1
2
3
optimize table table_name;
alter table table_name engine = innodb;
alter table table_name force;

optimize table的支持表类型: innodb,myisam, archive, ndb; 会重组表数据和索引的物理页, 对于减少所占空间和在访问表时优化io有效果.

对于MyISAM表, 当表上的数据行被删除时, 所占据的磁盘空间并没有立即被回收, 使用命令后这些空间将被回收, 并且对磁盘上的数据行进行重排(注意: 是磁盘上, 而非数据库).

在InnoDB表中等价 ALTER TABLE … FORCE, ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引;

只需在批量删除数据行之后, 或定期(每周一次或每月一次)进行一次数据表优化操作即可, 只对那些特定的表运行.

需要有足够的空间才能进行OPTIMIZE TABLE.如果没有磁盘空间, MySQL将不能进行优化, 表也无法使用.

  1. 查看慢查询
1
2
3
4
5
6
7
8
9
10
11
12
-- 查看慢查询是否开始, 存储文件位置
show variables like 'slow_query%';
-- 查询超过多少秒才记录
show variables like 'long_query_time';

-- 开启慢查询
set global slow_query_log='ON'; 
-- 设置慢查询时间, 默认为 10 秒
set long_query_time = 1;

-- 测试慢查询, 之后可以在慢查询日志文件中找到
select sleep(2)

也可以使用 pt-query-digest 工具分析慢查询日志.
pt-query-digest是用于分析mysql慢查询的一个工具, 它可以分析binlog, General log, slowlog, 也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析.
可以把分析结果输出到文件中, 分析过程是先对查询语句的条件进行参数化, 然后对参数化以后的查询进行分组统计, 统计出各查询的执行时间, 次数, 占比等, 可以借助分析结果找出问题进行优化.

  1. EXPLAIN 分析查询 SQL
  • table: 表名
  • type: 连接的类型
    • all: 全表扫描
    • index: 索引的扫描
      如果在 Extra 列看到 Using index, 表示正在使用覆盖索引, 只宿醉索引中的数据, 并不按索引扫描整个表.
    • range: 索引的范围查找
    • ref: 索引查找
      ref_or_null 是 ref 的变体, 表示需要在初次筛选结果中再次查找出 NULL 条目.
    • eq_ref: 主键或唯一索引的等值查找, 只会返回一条记录
    • const, system: 主键, 索引
  • possible_keys: 可能用到的索引
  • key: 实际使用的索引
  • key_len: 索引的长度, 越短越好
  • ref: 索引的哪一列被使用了, 常数较好
  • rows: mysql认为必须检查的用来返回请求数据的行数
  • extra:
    • Using index: 使用覆盖索引
    • Using where: MySQL 服务器将在存储引擎检索后再进行过滤
      不是所有 where 条件都会显示该值.
      有时候该值表示查询将受益于不同的索引
    • Using filesort: 需要额外排序
      无法按索引次序从表里读出行, 需要在内存或磁盘上进行排序
    • Using temporary: 需要使用临时表
      会使用内存或磁盘上幅度临时表, 通常用于排序
    • Range checked for each record (index map: N): 没有好的索引
  1. 查看实时 sql 记录

这个文件会随着访问的增加而不断变大, 所以生产环境建议临时开启, 用完及时关闭.

1
2
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';