mysql

Mysql调优手段总结(持续更新)

考虑优化的层面

  1. 服务器层(优化器,缓存)
  2. 存储引擎
  3. schema设计(索引/表)
  4. 查询优化

服务器层

  1. 待续…

存储引擎 （详见第二章笔记）

  1. 尽量使用innodb，以便使用它的行级锁；Myisam是表级锁，锁竞争更频繁。
  2. 根据场景选择：
    场景:
    全文索引: InnoDB + Sphinx
    在线热备份: InnoDB
    记录日志: MyISAM,Archive
    10TB以下: InnoDB
    10TB以上: InfoBright.
    大部分情况优先考虑: Innodb
  3. 待续…

schema设计

表设计(详见第四章笔记)

  1. 数据类型：
    1
    2
    3
    4
    (1) 更小的通常更好,
    int 好于 char (如可以用int存储ip);
    date/time/datetime 好于 char.
    (2) 尽量Not Null; // 因为NULL导致索引需要额外字节.// 不过这个影响不大
  2. 考虑使用缓存表，汇总表，计数器表；
  3. 表设计原则:
    1
    2
    3
    1. 避免太多的列; 服务器层需要缓冲存储引擎层的行,变长的行会引入转换开销.
    2. 避免太多关联;
    3. 恰当使用NULL. 大多数时候避免NULL列,有时候可以使用.(当异常值不好定义时)
  4. 适当程度使用范式,不迷信三范式.
    特定需求可以单独定制相应的缓存表和汇总表,而不改变原有的设计结构.
  5. 待续…

索引设计（详见第五章笔记）

  1. 不同类型索引适用场景：
    1
    2
    3
    4
    1. 哈希索引：（全键值查询，Memory引擎）
    数据仓库的查找表。（精确匹配O(1)复杂度，星形,多表连接）
    2. B-Tree索引：
    大部分场景。

三星系统
索引与查询匹配程度的评价：三星系统：

  1. 一星： 将查询相关记录放在了一起；（顺序IO,范围查询能利用到索引）
  2. 二星： 数据顺序与查找顺序一致；（避免排序,让排序也能利用到索引）
  3. 三星： 索引列包含查询的所有列。(避免二次访问磁盘)

上述评价的对象是(索引,某种查询). 因此并没有一个索引能够完美适应所有查询.只能说尽量适应当前场景下的高频查询.

索引适用场景：

  1. 小表： 直接扫全表即可；
  2. 中表： 索引；
  3. 大表： 分区。索引开销太大。

作为过滤条件的列需要考察的3个属性，按重要性如下：

  1. 查询频率；
  2. 相关查询是否是范围查询，或者列的取值是否可枚举。
  3. 选择性。

设计索引的原则：

  1. 查询频率高，则应该加入索引；
  2. 如果是范围查询，则如果作为联合索引的一列，应该放在后面；（因为最左前缀无法利用范围查询后的列）
    如果是可枚举列，则查询语句中可以使用In (‘male’,’female’)绕过列顺序限制，让Mysql始终能够应用相关索引；
  3. 满足前两个条件后，应该把选择性高的列放在联合索引的前面。

查询优化(详见第六章)

优化思路

  1. 尽量减少数据量(提前使用limit);
  2. 检查响应时间组成;
  3. 索引相关优化;
  4. 重写分解复杂查询;
  5. 如果是日志系统，开启异步写入数据的参数；
  6. 检查关联表的顺序;
  • 相关命令
    1
    2
    3
    explain extended <sql>;
    show warnings;
    -- 可以得到重构出的查询。

  1. 检查是内排还是外排(max_length_for_sort_data控制);

  2. 排序优化:
    关联查询排序时候，尽量把Order by的列提到第一张表中。
    ( 如果Order by的所有列都来自关联的第1个表，Mysql在关联处理第一个表的时候就会进行排序。)

  3. 网络传输;
    （1）缓冲区大小(SQL_BUFFER_RESULT);
    （2）先释放表锁，再发送结果：SQL_BUFFER_RESULT。

  4. 待续…

索引相关

  1. 范围查询转化成枚举，充分利用索引；
    如索引为(dt,city)，查询为：

    1
    2
    3
    4
    5
    6
    7
    select dt,city from t
    where dt>='2018-01-01' and dt<='2018-01-02'
    and city='beijing'
    -- 转化为=>
    select dt,city from t
    where dt in ('2018-01-01','2018-01-02'）
    and city='beijing'

  2. 延迟关联+覆盖索引；

  3. 检查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: 扫全表

  4. 检查索引字段是否正确转换了类型:

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

  5. 待续…

查询中锁的使用

  1. 尽量使用读锁，避免使用写锁；
  2. 降低写锁使用时间（先抢注后处理+定时清理占用标志），消除select ... for update的使用；(避免显式加锁,优先考虑隐式加锁)
  3. Innodb不同索引对应的锁：
    (1) 主键索引： 排他锁（写锁）；
    (2) 二级索引： 共享锁（读锁）。
  4. 待续…

推荐文章