mysql

mysql调优小记

  • 摘要

    mysql表出现慢查询,单表数据量500W700W条. 每天23W条.
    措施:

    1. 优化查询语句;
    2. 更改存储引擎.
    3. 优化索引.

详情

同事去塞班岛玩前留了个大坑,导致网站报表卡死刷不开.

STEP 1

进入后台:

1
show processlist

发现Query很多,show full processlist找出查询语句,发现多表join的时候没有利用到索引.
查询语句模式如下:

1
explain select ...

发现没有用上索引,更改查询语句。

  • explain查询计划中的type:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    const(system): 根据PRI或Unique key,只取出确定的(一行)数据,常量优化. 

    eq_ref: JOIN条件包括了所有索引,并且索引是Unique key. 
    ref: JOIN条件包括部分索引或不是Unique key.
    ref_or_null: WHERE col=exp or col is null

    index_merge: Where id=xx or userid=xxx. 索引合并优化.

    unique_subquery: where col in( select id from xxx). 这里id唯一.
    index_subquery: 同上,只是id不唯一.

    range: 索引在某个范围内.
    all: 扫全表

  • TODO:
    研究除了子查询以外的方式使用索引.

此外, 索引对于数据类型敏感, 查询中存在字符串和date类型相等的时候, 无法利用索引,
需要将date类型转成字符串.

1
dt = date_format(date_sub(current_date, interval 1 day), '%Y-%m-%d')

STEP 2

检查后台,发现许多连接状态都是:
waiting for table level lock.
进一步发现这几张表的存储引擎是MYISAM,而不是默认的innodb.
由于myisam引擎只有表级锁,不符合我们的使用要求.于是我把涉及到的几张表引擎都改为innodb.

STEP 3

修改后,查询不会卡死(毕竟不扫全表了ORZ),降低到40s,但还是太慢了.
进一步检查explain结果,发现key_len太长了.
于是重新设计索引, 把筛选度高的放前面(利用最左前缀), 并且根据具体业务\语义,尽量缩短索引字段的长度,
实在无法缩短的则取其中一部分. 最后查询缩短到0.04sec,几个报表都是秒出.

推荐文章