mysql基础-性能调优

Focus4149
157 阅读7分钟

这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

概述

性能调优往往是程序开发过程中不可避免的步骤，对于复杂和高并发的项目，性能调优变的尤其重要。

影响性能的因素

  1. 业务需求以及业务的复杂度
  2. 存储定位
不适合放进mysql的数据
1）二进制的多媒体数据
2）流水队列数据
3）超大文本数据

需要放进放进缓存的数据
1)系统的各项配置规则
2）活跃用户的基本信息
3）准实时的统计信息
4）其他一些访问频繁但是变更少的数据
  1. schema设计对系统的影响
  • 尽量减少对数据库访问的请求
  • 尽量减少无用数据的查询请求

性能分析

mysql常见瓶颈

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

性能下降、sql慢、执行时间长、等待时间长原因分析

  • 查询语句写的不合理
  • 索引失效
  • 关联查询太多
  • 服务器参数设置不合理

常见性能分析手段

慢日志查询

  • mysql提供一种日志御用记录mysql中响应的时间超过设定阈值的语句，具体是指将运行时间超过long_query_time值的收集起来，会记录到慢查询日志中
  • 默认情况下，mysql数据库没有开启慢查询日志，需要手动设置参数开启
  • 查看开启状态 show variables like '%slow_query_log%'
  • 开启慢日志查询
-- 临时设置
mysql> set global slow_query_log= 'ON';
mysql> set global slow_query_log_file='/path/to/slow.log';
mysql> set global long_query_time=2;

-- 永久配置，修改my.cnf或者my.ini
[mysqld]
slow_query_log = ON
slow_query_log_file = /path/to/slow.log
long_query_time = 3

explain 执行计划

概述

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句

可以做什么

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

explain各个字段的意义

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

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

查询类型 用于区分普通查询、联合查询、子查询等复杂查询

  • 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

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

  • system：表只有一行记录，是const类型的特例，一般不会出现
  • const：表示通过索引一次就找到了，const用于比较primary key或union索引，因为只要匹配一行数据，所以很快，如将主键至于where列表中，mysql就能将该查询转换为一个常量
  • eq_ref : 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
  • ref：非唯一索引扫描，范围匹配某个单独值的所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行
  • range：只检索给定返回的行，使用一个索引来选择行。key列显示使用哪个索引，一般是在where语句中出现between、<、>、in等的查询
  • index：full index scan ，index与all的区别为index类型值遍历索引树，通常比all快，因为索引文件通常比数据文件小。index和all都是全表扫描，但是index是从索引中读取，all是从磁盘中读取
  • all : Full table scan ，将遍历全表找到匹配行
possible keys

显示可以能应用在这个表中的索引，一个或者多个，查询设计到的字段，若存在索引，则该索引被列出，但实际上不一定用到。

key

实际使用的索引，若未null，则没有使用索引

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

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

rows

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

extra

表示一些额外的信息

性能分析

索引优化

  1. 全值匹配是最优选择
  2. 最佳左前缀法则
  3. 不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效转而进行全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引，减少select
  6. is null，is not null会导致索引失效，进而进行全表扫描
  7. like 'xxx%'可以使用索引，而like '%xxx%'则会导致索引失效，进而全表扫描
  8. 字符串不加单引号，会导致索引失效
  9. 少用or，用它来链接时会导致索引失效
  10. <=、>=、BETWEEN、IN可以使用到索引，<>、not in、!=则不行，会导致全表扫描

查询优化

永远小表驱动大表

-- 当表A > 表B，使用in
select * from A where id in (select id from B)

-- 当A表 < B表，使用exists
select * from A where exists (select 1 from B where B.id = A.id)

数据类型优化

  1. 更小的通常更好。一般情况，尽量使用可以正确存储数据的最小数据类型。
  2. 简单最好，简单的数据类型通常需要更少的CPU周期。例如:整形类型比字符串操作代价更低，因为字符串集合校对规则比整形要复杂
  3. 尽量避免使用null

order by 关键字优化

  1. mysql支持两种方式的排序：index和FileSort；index效率高（扫描索引本身完成排序），FileSort效率低
  2. order by子句尽量使用index方式排序，避免使用filesort方式排序
满足一下两种情况，会使用index排序
1）order by语句使用索引最左前列
2）使用where子句或者order by子句条件组合满足索引最左前列
  1. filesort方式排序有两种算法
双路排序：mysql4.1之前是用改排序，两次扫描磁盘，最终得到数据
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列进行输出，效率高于双路排序
  1. 优化策略
1）增大 sort_buffer_size的参数设置 
2）增大max_length_for_sort_data的参数设置

group by关键字优化

  1. group by实际是先排序后进行分组，遵照最左匹配原则
  2. 当无法使用索引时，增大max_length_for_sort_data和sort_buffer_size的参数设置
  3. where高于having，能在where限定的条件下就不要去having限定了。
avatar
文章
阅读
粉丝