sql效率优化

查找分析查询慢的原因：

记录慢查询日志

设置方法
方法一：全局变量设置
将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON'; 

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

mysql> set global long_query_time=1;

方法二：配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

四、测试

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

如果日志存在，MySQL开启慢查询设置成功！

 

 

2.set profiling=1;分析单条语句，看看为啥慢

查看时间花费在了什么地方

show profile for query 1;

 

 

3.开始优化

查询不需要的记录，使用limit解决

多表关联返回全部列，指定A.id,A.name,B.age

总是取出全部列，SELECT*会让优化器无法完成索引覆盖扫描的优化

把大的查询分为多个小的相同的查询

一次性删除1000万条要比一次删除一万条，暂停一会的方案更加损耗服务器开销。

可以将一条关联分解成多条sql来执行，让缓存效率更高，执行单个查询可以减少锁的竞争

在应用层做关联可以更容易对数据库进行拆分

 

优化count()查询

count(*)中的*会忽略所有的列，直接统计所有列数，因此不要使用count(列名)

MyISAM中，没有任何where条件的count(*)非常快

当有where时，MyISAM的统计不一定比其他引擎快

可以增加汇总表

对汇总信息做缓存

 

优化关联查询

确认ON获取USING子句列上有没有索引，确认GROUP BY 和ORDER BY中只有一个表的列，这样MySQL才有可能使用索引

 

优化子查询

尽可能使用关联查询来替代

 

优化GROUP BY和DISTINCT这两种查询均可使用索引来优化，是最有效的方法

关联查询中，使用标识列进行分组效率更高。

如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL,MySQL不会再进行文件排序。

 

优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低

可以查询上次查询的最大ID，下次查询时直接根据该ID来查询。

 

UNION查询优化

UNION ALL效率大于UNION