MySQL调优

fjian_fresh
200 阅读9分钟

日常工作中应该如何进行MySQL调优，以达到满意的查询效率？

我认为可以从存储定位、硬件系统、SQL优化三方面来说明。

存储定位对MySQL的影响

  • 不适合放进MySQL的数据

    • 二进制多媒体数据
    • 流水队列数据
    • 超大文本数据

  • 需要放进缓存的数据

    • 系统各种配置及规则数据
    • 活跃用户的基本信息数据
    • 活跃用户的个性化定制信息数据
    • 准实时的统计信息数据
    • 其他一些访问频繁但变更较少的数据

硬件系统

  • CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态

我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：

Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

SQL优化

使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。能让我们了解到：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

各字段解释

  • id（SELECT识别符。这是SELECT的查询序列号，是SQL执行的顺序的标识，SQL从大到小的执行）

    • id相同，执行顺序从上往下
    • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

  • select_type（查询中每个select子句的类型）

    • SIMPLE ：简单的select查询，查询中不包含子查询或UNION
    • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
    • SUBQUERY：在select或where列表中包含了子查询
    • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
    • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
    • UNION RESULT：从UNION表获取结果的select

  • table（显示这一行的数据是关于哪张表的）

  • type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

    • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
    • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
    • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
    • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
    • ALL：Full Table Scan，将遍历全表找到匹配的行

    tip: 一般来说，得保证查询至少达到range级别，最好到达ref

  • possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

  • key

    • 实际使用的索引，如果为NULL，则没有使用索引
    • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

  • key_len

    • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
    • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

  • ref （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

  • rows （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

  • Extra（包含不适合在其他列中显示但十分重要的额外信息）

    1. using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
    2. Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
    3. using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
    4. using where：使用了where过滤
    5. using join buffer：使用了连接缓存
    6. impossible where：where子句的值总是false，不能用来获取任何元祖
    7. select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
    8. distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

性能优化

索引优化

  1. 全值匹配我最爱
  2. 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
  3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少回表
  6. is null ,is not null 也无法使用索引
  7. like以通配符开头('%abc...')索引失效会变成全表扫描的操作，
  8. 字符串不加单引号索引失效
  9. 少用or，用它来连接时会索引失效
  10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
  • 少用Hint强制索引

查询优化

永远小表驱动大表（小的数据集驱动大的数据集）

slect * from A where id in (select id from B)
等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
等价于
select * from A
select * from B where B.id = A.id

当 A 表的数据集小于B表的数据集时，用 exists优于用 in

注意：A表与B表的ID字段应建立索引。

order by关键字优化

  • order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

  • MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；

  • ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列

  • 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀

  • 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序

    • 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
    • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序

  • 优化策略

    • 增大sort_buffer_size参数的设置
    • 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

  • group by实质是先排序后进行分组，遵照索引键的最佳左前缀
  • 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
  • where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

  • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

    简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。

  • 尽量避免NULL：通常情况下最好指定列为NOT NULL

avatar
文章
阅读
粉丝