MYSQL性能优化之SQL查询优化

SQL查询优化目的：减少查询所消耗的时间，加快查询的相应速度

获取有性能问题的SQL

满查询日志开销比较低

• 磁盘IO （顺序存储） 开销忽略不计
• 存储日志大小所需要的磁盘空间 （依赖）

控制sql配置

- slow_query_log(默认关闭，启动on,运行系统启动满查询 set global)
- 定时，避免占用过多的磁盘空间（使用脚本来控制）
- slow_query_log_file(默认情况下，保存在mysql的数据目录中)
- 建议日志存储和数据存储分开，磁盘分区更好
- long_query_time单位秒(默认10s),最小微妙
- 记录所有符合条件的SQL
- 包括查询语句，数据修改语句，已经回滚的SQL
- 通常改为0.001秒（1毫秒）比较合适

- 使用用户信息，线程ID号（慢查可以代替审计日志）
- 查询时间和锁时间
- 返回行数和扫描行数
- sql执行时间
- sql

mysqldumpslow -s r -t 10 slow-mysql.log-t top 指定取前几条做为结束输出

测试

• 汇总查询信息（总查询时间，总锁定时间，发送行数，检查行数，接受查询大小）
• SQL统计信息，ID是sql去掉空格和文本值转成小写，hash值
• Query1 性能最差的查询，次数，并发次数，查询id
• Query 查询计划（很棒）

实时获取有性能问题的SQL

利用information_schema数据库来获取PROCESSLIST表
使用脚本周期性执行

SELECT id,`user`,`host`,DB,command,`time`,state,info FROM information_schema.PROCESSLIST WHERE TIME>=60

慢查询产生的原因

sql执行过程

第一阶段

缓存查找是利用对大小写敏感的哈希查找来实现的
Hash查找只能进行全值查找（sql完全一致）
如果缓存命中，检查用户权限，如果权限允许，直接返回
查询不被解析，也不会生成查询计划

• 每次缓存中的表中的内容更新，缓存中的所有内容都会跟着刷新
• 缓存查询加锁，对于一个读写频繁的系统使用查询，缓存很可能会降低查询处理的效率，不要使用查询缓存

配置参数

- query_cache_type 设置查询缓存是否可用
- ON,OFF,DEMAND
- DEMAND使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存
- 如果缓存很大，在查询中可以加上SQL_NO_CACHE提交效率

第二阶段

将msql转换成执行计划和存储引擎进行交互

• 一条查询可以有多种查询方式，查询优化器会对每一种查询方式的（存储引擎）统计信息进行比较，找到成本最低的查询方式
  
  • 这也是缩影不能越来越多的原因

Mysql优化器可优化的SQL类型

查询优化器：对查询进行优化并查询mysql认为的成本最低的执行计划

为了生成最优的执行计划，查询优化器会对一些查询进行改写

• 在一定情况下，外连接等价于内连接。比如 查询 a left join b where 对B表进行过滤
• 利用索引列是否为空对聚合函数进行优化（count…），比如B树索引，顺序存储，所以如果要找到最小值，只需要查询B树索引最左边的记录就行了
• 等价变换规则：比如当发现索引中的列包含所有的查询中所需要的信息时，会使用覆盖索引
• mysql对in（）列表中的数据先进行排序，通过二分查找的方式来确定列表中的值是否满足条件
  当优化过查询会，生成查询计划，根据查询计划，调用存储引擎的API,通过存储引擎调用所需的数据

查询处理各个阶段所需要的时间

使用profile

• set profile = 1; 启动profile,这是一个session级的配制
• 执行查询
• show profiles; 查询每一个查询所消耗的总时间的信息
• show profiles for query N; 查询的每个阶段所消耗的时间

show profiles for query 1;

影响sending data的因素很大：不同服务器的活动：关联时搜索匹配的行记录。通常可以使用索引来优化。

查询cpu的信息show profile cpu for query 1 ;

performance_schema

performance_schema是5.5引入的一个性能分析引擎（5.5版本时期开销比较大）

启动监控和历史记录表

use performance_schema;

performance_schema分析数据库中每一个sql中各个阶段执行消耗时间的信息

特定SQL的查询优化

大表数据修改（分批修改，暂停几秒（减少对主从复制的压力））

大表表结构的修改

一

利用主从复制，先对从服务器进入修改，然后主从切换

二（推荐）

• 添加一个新表（修改后的结构）
• 老表数据导入新表
• 老表建立触发器，修改数据同步到新表
• 老表加一个排它锁（重命名）
• 新表重命名
• 删除老表

手工到工具（pt-online-schema-change）

修改语句这个样子alter table sbtest4 modify c varchar(150) not null default ''()

优化not in 和 <> 查询

子查询改写为关联查询

使用汇总表优化查询

汇总表就是提前以要统计的数据进行汇总并记录到表中以备后续的查询使用
创建一个统计表，利用覆盖索引进行优化
product_comment_cnt每隔一段时间进行对前天所有数据的汇总
product_comment 当天的评论数