mysql性能调优精简版

大家好，我是烤鸭：
    这是根据官方文档提炼出的mysql性能优化总结。

    想看完整翻译版的请看  https://blog.csdn.net/Angry_Mills/article/details/87720396

 

1. 成本优化

    成本包含： IO 和 CPU 从硬盘读取的花费
    模型包含： 全表扫描（IO成本：表中的pages * IO阻塞读取成本  CPU成本： 行 * 行计算成本） 和 范围索引扫描（IO成本：范围中的行 * IO阻塞读取成本 IO成本：范围中的行 * 行计算成本）

2. 利用工具监视sql

    MySQL Enterprise Monitor (MEM), Query Analyzer
    Performance schema 执行计划
        events_statements_history,events_statements_history_long
            大部分最近执行的statement
        events_statements_summary_by_digest
            总结相似操作（相同的statement合并）
        file_summary_by_event_name
            Interesting event: wait/io/file/innodb/innodb_data_file
        table_io_waits_summary_by_table
        table_io_waits_summary_by_index_usage
         统计存储引擎的每个表和索引

EXPLAIN/EXPLAIN FORMAT=JSON

   调试查询计划：

SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE <filename> LINES TERMINATED BY '';
SET optimizer_trace="enabled=off";

3.数据访问和索引优化

    使用索引/添加索引/复合索引/索引合并
        注意低选择性的索引!(字段长度或者变量比较少,比如sex int 1)

 

4.连接优化

    找到最好的连接顺序
        使用 STRAIGHT_JOIN
    优化连接顺序
        对非索引列进行过滤
        部分表强制提前处理无需暗示
        将派生表合并到外部查询中
        无临时表

 

5.子查询优化

    IN 转换为 EXISTS
    例子：

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM actor WHERE name=“Bullock”)
=> SELECT title FROM filmWHERE EXISTS (SELECT 1 FROM actor WHERE name=“Bullock” AND film.film_id = actor.film_id)

     优势：子查询将计算更少的记录

     Semi-join 半连接
         将子查询转换为内连接,但需要一些方法去重
              匹配优先（等价于IN—>EXISTS的方式）
              懒扫描（索引扫描，跳过重复）
              实体化  MatLookup(像子查询实体化)，MatScan（实体化表在连接顺序的第一位）
              去重（用唯一索引将半连接的行插入临时表；重复列将会被拒绝。无论连接顺序）
        优势：有更多优化"连接顺序"的选择
        限制：如果子查询包含union（并集）或者aggregation（聚合）不能使用半连接

    子查询实例化：
        执行一次子查询并在临时表中存结果,表有唯一索引可以快速查找并去重
        执行外部查询并检查临时表中的匹配项

    派生表：
    例子：

SELECT AVG(o_totalprice) FROM ( SELECT * FROM orders ORDER BY o_totalprice DESC LIMIT 100000 ) td; 

        mysql 5.6 以前 : 单独执行并将结果存储在临时表中（实现）
        mysql 5.7 处理类似于视图的派生表：可以与外部查询块合并

        注意：用外部连接合并派生表,基于GROUP BY，DISTINCT，LIMIT或聚合函数的派生表将不会合并

    外部查询与派生表MERGE
        NO_MERGE: 可用于覆盖默认行为  

SELECT /*+ NO_MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;

      MERGE: 强制合并            

SELECT /*+ MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;

     也可以使用MERGE / NO_MERGE暗示查看视图  

SELECT /*+ NO_MERGE(v) */ * FROM t1 JOIN v ON t1.x = v.x;

 

6.排序

    通常的解决方案；"FileSort"
        在排序之前将查询结果存储在临时表中
        如果数据量很大，可能需要在磁盘上使用中间存储进行多次传递排序
    优化
        利用索引按排序顺序生成查询结果
        对于"LIMIT n查询，保留内存中n个顶级项的优先级队列而不是文件排序。

    执行计划
        可从执行计划中对每个语句进行排序
    优化方式
        增加排序缓冲区 SET sort_buffer_size = 8*1024*1024;
        使用索引避免排序
        重新研究案例

 

7.影响的优化器

    增加索引
        强制使用特定索引 使用索引，强制索引，忽略索引
    强制特定的关联顺序
        STRAIGHT_JOIN    
    调整会话变量 

set optimizer_switch="index_merge=off";set sort_buffer=8*1024*1024;set optimizer_search_depth = 10;

    mysql 5.7 优化
        (BKA) 批量key访问    (BNL) 阻塞嵌套循环算法
        (Multi-Range Read ) 多范围读取 (表的索引)
        连接和子查询（策略）
        命中索引的情况
        范围优化（索引） 
        阻塞查询

    未来 mysql 8.0 语法可能的优化：
        启用/禁用视图和派生表的合并
        MERGE() NO_MERGE() 
        连接顺序

        考虑添加的暗示：
            强制/忽略index_merge替代方案
            重新实现新语法中的索引暗示
            暂时为一个查询设置会话变量

    mysql 5.7 重写查询插件
        无需更改应用程序即可重写有问题的查询
            更新连接顺序... 更多
                向表中添加重写规则
         可调成本常量(不建议使用)