SQL篇-Mysql的SQL性能调优

服务器优化：
扩大buffer pool 默认为128M 理论上为内存的3/4或者4/5
innodb_buffer_pool_size = 750M

SQL设计层面的优化

1 分页查询的调优（当limit的偏移量越大时，性能越差）
优化前：
select 字段 from table limit 3000,10
优化后：(利用右表查询出来的主键关联上左边，得到其他的字段信息）
select 字段 from table as t1 righit join (select 主键 from table ) as t2 on t1.主键=t2.主键

2、导入大量数据的优化
针对MyISAM的存储引擎：
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开和关闭MyISAM表非唯一索引的更新

针对INNODB表来说：(两种方案）
1、先给表创建主键
2、导入数据前先关闭唯一性校验（SET UNIQUE_CHECKS=0） 导入结束在开启（SET UNIQUE_CHECKS=1）
3、关闭自动提交（SET AUTOCOMMIT=0） 导入结束在开启（SET AUTOCOMMIT=1）
4、 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE
5、利用join比子查询效率更佳，子查询需要在内存中生成一张临时表
例如：
优化前：
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID
FROM salesinfo
优化后：（CustomerID 有索引，效率更高）
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL

关联查询优化：
left join :小表在前（驱动表）
子查询优化：尽量不要使用not in 或not exists 应该使用left outer join xxx is null代替

3、复制表的操作
建议先将数据导出成文件，在导入到目标表
insert … select … 会造成表锁
我们推荐使用select…into outfile 和 load data infile 的组合来实现，这样是不会对纪录进
行锁定的。

4、 开始profile
show variables lile ‘%profile’;
set profiling = 1 (开启)
查看执行的sql
show profiles
查询对应的sql语句的执行情况
show prifile for query sqlID

5、count(*) 找普通索引，找到最小的那棵树遍历，包含空值
count(1) 忽略字段，包含空值 （最优，也是找到最小的树遍历，主键树肯定是最小的树 ）
count(字段) 走缓存，不包含空值

在系统架构层面：
二进制之类的图片视频文件是不适合存放到mysql中的，数据库空间资源消耗大，存储这些数据IO消耗大
将常用的数据加载进缓存中，避免频繁访问数据库
SQL及索引优化：
目的是为了减少中间结果集，降低物理IO

问题SQL的筛选步骤
1检查慢查询的日志路径
show variables like ‘%slow_query_log%’;
2开启慢日志：
set global slow_query_log=on;
3慢查询判断标准（默认查询时间大于10S的sql语句）
show variables like ‘long_query_time’;

mysqldumpslow -v /var/lib/mysql/xx-slow-log按序查看消耗时间最长的sql
第三方工具分析慢查询日志： pt-query-disgest

Explain执行计划里返回的参数解读
id: 执行计划的步骤，数字越大越先执行
select_type:指的是简单查询还是复杂查询
partitions:分区
types:连接关系 All代表全表扫描（性能最差） 性能从高到低： System>constant>eq_ref>ref>range>index>all
possible_keys :可能使用到的索引
key：真正用的索引
key_length:索引的长度，越小越好
ref:代表索引具体在哪一列上
rows:扫描的行数
filtered:返回给客户端的数据，是个百分数

永远是小表驱动大表，可以极大降低IO，因为如果小表只有20条，那么只需要在最外层循环20次，内层循环只要匹配就可以退出。

优化策略：
尽可能在索引中完成排序
不要 select *
仅使用最有效的过滤条件
尽可能避免复杂的join和子查询
小心使用order by,group by,distinct语句
保证join语句中被驱动表上join条件字段有索引