Mysql 查询优化

优化流程：先找出sql语句慢的原因，然后explain sql找出慢的原因，最后进行优化

1. 开启查询日志

Linux下修改mysql配置文件

1. 找到mysql安装目录

which mysql

2. 查找配置文件目录

/usr/bin/mysql --verbose --help | grep -A 1 'Default options'

输出：

fault options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 

这个信息的意思是： 
服务器首先读取的是/etc/mysql/my.cnf文件，如果前一个文件不存在则继续读/etc/my.cnf文件，如若还不存在便会去读~/.my.cnf文件

打开 /etc/mysql/my.cnf 

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

 成功找到配置文件目录为 /etc/mysql/mysql.conf.d/mysqld.cnf

 mysql数据文件和日志默认储存位置 /var/lib/mysql

 

开启查询日志

• 普通查询日志：

　　在mysql命令行或者客户端管理工具中执行：SHOW VARIABLES LIKE "general_log%";

结果：

    general_log OFF
    general_log_file /var/lib/mysql/localhost.log

　　OFF说明没有开启日志记录

　　分别执行开启日志以及日志路径和日志文件名

   SET GLOBAL general_log_file = '/var/lib/mysql/localhost.log';
   SET GLOBAL general_log = 'ON';　　

注意

　　此时为动态配置，重启后失效

永久开启日志记录：

打开mysql配置文件，找到并启用下面配置

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

 

 

• 开启开启慢查询 ： 

　　　 查看慢查询开启状态

show variables like "%slow%"

+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /var/lib/mysql/ubuntu-slow.log |
+---------------------------+--------------------------------+

　　　 slow-query-log=1 --开启慢查询

            slow_query_log_file="103-54-slow.log" --慢查询日志文件名，默认跟data目录相同路径
            long_query_time=1 --超过1s认为是慢查询

• 开启未使用索引SQL过滤配置：

            log_queries_not_using_indexes 

　　直接在配置文件中添加这一行就行或者：

            log_queries_not_using_indexes =1

　　重启mysql  /etc/init.d/mysql restart

2 . show profiles 使用分析sql 性能

mysql> set profiling=1; //开启临时表

mysql> show profiles;    //显示临时表

mysql> show profile for query 2;    //显示某条sql具体执行时间

---

优化点

• 避免获取过多的行和列
• 重复查询相同的数据，可是使用缓存
• 合理的使用索引
•  将耗时长的查询语句分解成多次执行，缓解服务器压力，例如删除1000万条数据，可以一次删除1000条
• 将一条关联语句分解成多条SQL执行
  • 提高缓存效率
  • 减少mysql表的竞争
• 关联查询优化
  • 确保 on 后面字段有建立索引
• 尽量避免使用子查询