[TOC]

MySQL优化

目前我所掌握的手段只有
1. 查看MySQL运行状态
2. 查看MySQL未命中索引的语句
3. 查看MySQL慢查询语句

我的开发配置

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

# 记录全局日志
log_output=file
general_log=ON
general_log_file=/tmp/db_general.log


# 慢查询日志
long_query_time = 0.3               # 超过多少秒记录
slow_query_log = ON                 # 慢查询日志开关
slow_query_log_file = /tmp/db_slow.log # 记录保存位置
min_examined_row_limit=0            # 扫描行数小于该值的查询，不记入慢查询日志
log_slow_admin_statements=OFF       # 记录执行缓慢的OPTIMIZE、ANALYZE、ALTER和其他管理命令语句

# 错误日志
log-error = /tmp/db_error.log



# binlog相关
## 指定保存的位置
log-bin = /tmp/db_bin
## 设置server_id，一般设置为IP,注意要唯一
server_id=100
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
#log-bin=edu-mysql-bin
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
## 对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
## binlog日志格式，可选值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默认为“STATEMENT”，5.6之后默认为“MIXED”；因为“STATEMENT”方式在处理一
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

运行状态

• 查看当前mysql的连接进程 show full processlist;

• 查看MySQL锁状态 SHOW STATUS LIKE 'table%';

mysql> SHOW  STATUS  LIKE  'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 26492 |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

说明：

Innodb_row_lock_current_waits：当前锁等待的数量

Innodb_row_lock_time：自系统启动到现在，锁定的总时间，单位：毫秒 ms。

Innodb_row_lock_time_avg：平均锁定的时间，单位：毫秒 ms。

Innodb_row_lock_time_max：最大锁定时间，单位：毫秒 ms。

Innodb_row_lock_waits：自系统启动到现在，锁等待次数，即锁定的总次数。

分析：

针对如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，说明可能存在锁争用的情况，针对 Innodb 类型的表，可以通过设置InnoDB Monitors来进一步观察发生锁争用的表、数据行等，并分析锁争用的原因，

日志注解

MySQL日志：
主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志；
这里的操作,使用SET GLOBAL xxx=xxx在MySQL重启后就失效了.
如果要永久性,需要修改MySQL配置文件my.cnf
判断配置文件所在地可以
mysql --help | grep 'my.cnf'
这些就是mysql默认会搜寻my.cnf的目录，顺序排前的优先。

查看日志相关的全局变量：
SHOW GLOBAL VARIABLES LIKE '%log%'

修改相关变量：
    1.对于开关某个功能，使用SET GLOBAL xxx=xxx即可。
    2.涉及到文件的操作，只能修改配置文件，添加内容到配置文件并重启服务后生效。
    
在MySQL 5.7.2 新增了 log_timestamps 这个参数，该参数主要是控制 error log、genera log，等等记录日志的显示时间参数
且默认安装后error_log,slow_log 日志时间戳默认为UTC，因此会造成与系统时间不一致，与北京时间相差8个小时
SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+---------------------+-------------------+
|Variable_name  |              Value   |
+---------------------+-------------------+
|log_timestamps |              UTC     |
+---------------------+-------------------+

因为log_timestamps  是一个GLOBAL的全局参数,所以直接在登录后去set全局参数,重启后就会直接失效

因此需要在mysql的配置文件中[mysqld]中增加一条log_timestamps的配置
有两种选择,按照系统SYSTEM和设置为默认的UTC
log_timestamps=SYSTEM

    

MySQL错误日志

show variables like '%log_err%';
| log_error           | /usr/local/var/mysql/liuhaodeMacBook-Pro.local.err |
| log_error_verbosity | 3
第一个为错误日志的保存路径
第二个为错误日志的全局动态变量，默认3，范围：1～3
表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息。

[mysqld]
log-error = /tmp/db_error.log

记录binlog

binlog 就是binary log，二进制日志文件
这个文件记录了mysql所有的dml操作。
通过binlog日志我们可以做数据恢复，做主住复制和主从复制等等.

SHOW GLOBAL VARIABLES LIKE '%log_bin%';

| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
三个参数来指定，
第一个参数是打开binlog日志
第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件
第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目

[mysqld]
# 设置server_id，一般设置为IP,注意要唯一
server_id=100  
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql  
# 指定binlog 保存的位置,注意,以后的文件名会是db_bin.000001等
log-bin=/tmp/db_bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M 
# 主从复制的格式（mixed,statement,row，默认格式是statement）
# 对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
# binlog日志格式，可选值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默认为“STATEMENT”，5.6之后默认为“MIXED”；因为“STATEMENT”方式在处理一些“不确定”性的方法时会造成数据不一致问题，我们建议使用“MIXED”或者“ROW”
binlog_format=mixed  
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7  
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062


查看binlog日志的大小
mysql>show master status 或 show binary logs;

查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
mysql>show master status;

刷新binlog日志文件
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
mysql>flush logs #刷新之后会新建一个新的Binlog日志


显示从库的状态
mysql>show slave status

清空日志文件,重置(清空)所有binlog日志
mysql>reset master

查看binlog日志文件
binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
mysqlbinlog mysql-bin.000002

上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
        IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
        FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
        LIMIT [offset,] 偏移量(不指定就是0)
        row_count       查询总条数(不指定就是所有行)

截取部分查询结果：
     *************************** 20. row ***************************
        Log_name: mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名
             Pos: 11197 ----------------------------------------------------------> pos起始点:
      Event_type: Query ----------------------------------------------------------> 事件类型：Query
       Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
     End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
            Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
     *************************** 21. row ***************************
        Log_name: mysql-bin.000021
             Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
      Event_type: Query
       Server_id: 1
     End_log_pos: 11417
            Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
     *************************** 22. row ***************************
     
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
    A.查询第一个(最早)的binlog日志：
    mysql> show binlog events\G; 
    
    B.指定查询 mysql-bin.000021 这个文件：
    mysql> show binlog events in 'mysql-bin.000021'\G;
    
    C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
    mysql> show binlog events in 'mysql-bin.000021' from 8224\G;
    
    D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;
    
    E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
    mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;

记录所有SQL日志

所有的SQL操作都会被记录
所有日志的系统变量:
	log_output=[none|file|table|file,table] #通用查询日志输出格式,5.7以下版本要加引号.如: 'table'
	general_log=[on|off]      #是否启用通用查询日志
	general_log_file=[filename]    #通用查询日志位置及名字
如果将日志放在数据库中则保存在mysql.general_log表中.
如果将日志保存在文件中,需要指定文件路径.


案例:
    MySQL中:
    不加单引号,5.7以下版本会报错
	set global log_output='file';
	set global general_log_file='/usr/local/var/log/general.log';
	set global general_log=on;
	配置文件中:
	[mysqld]
	general_log=ON
	log_output=file
	general_log_file=/usr/local/var/log/general.log


select * from mysql.general_log;

未命中索引的查询

MySQL未命中索引查询日志:
保存位置和慢查询日志相同

# 记录没有使用索引的查询，而不考虑执行时间的长短：
# 记录那些执行时没有从任何索引中受益的查询
# 默认值：OFF
log_queries_not_using_indexes=OFF

# 限制每分钟记录的不使用索引的查询数：
# 每分钟记录最多这么多条“不使用索引”的警告至慢查询日志
# 任何更多的警告都将被压缩概括为一行。值为0则禁用该限制
# 除非设置了log-queries-not-using-indexes，否则该项不生效
# 默认值：0
log_throttle_queries_not_using_indexes=#

案例:
    # 该案例为记录所有未命中索引的查询
    show variables like '%quer%';
    set global log_queries_not_using_indexes=on;

慢查询

Mysql慢查询日志:
 
# 查看慢查询日志是否开启  通过slow_query_log 是否是off判断
show variables like '%quer%';

#  通过配置文件的方式进行设置  # 设置慢查询后 MySQL重启报错 Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
# /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。 
long_query_time = 1 				# 超过多少秒记录
slow_query_log = ON                 # 慢查询日志开关
slow_query_log_file = /tmp/slow.log # 记录保存位置
min_examined_row_limit=0            # 扫描行数小于该值的查询，不记入慢查询日志
log_slow_admin_statements=OFF       # 记录执行缓慢的OPTIMIZE、ANALYZE、ALTER和其他管理命令语句


# 两者区别  未验证
slow_query_log    ：  指定是否开启慢查询日志
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log 

# 通过命令行方式进行设置 按照先开启日志在设置时间的顺序, 下次打开窗口即生效
mysql> set global slow_query_log='ON';      # 启用慢查询, 加上global，不然会报错的。
mysql> set global long_query_time=2;	#设置成2秒，加上global,下次进mysql已然生效

# 将MySQL慢查询日志写入表,随后在mysql.slow_log表中可以看得到
set global log_output='TABLE';

# 通过一下方式测试慢查询
SELECT SLEEP(10);
# 查看慢查询一共执行了几次
show global status like '%slow%';

# 查看刚才慢查询的日志
select * from mysql.slow_log;

慢查询日志格式:
聚合方式, 按照出现频率, 每次多久,排序,去重.

慢查询日志案例

-- 打开sql 执行记录功能
set global log_output='TABLE';                             -- 输出到表
set global log=ON;                                                -- 打开所有命令
执行记录功能general_log, 所有语句: 成功和未成功的.
set global log_slow_queries=ON;                        -- 打开慢查询 sql 记录
slow_log, 执行成功的: 慢查询语句和未使用索引的语句
set global long_query_time=0.1;                          -- 慢查询时间限制(秒)
set global log_queries_not_using_indexes=ON;  -- 记录未使用索引的sql 语句
-- 查询sql 执行记录
select * from mysql.slow_log order by 1;           -- 执行成功的：慢查询语句,和未
使用索引的语句
select * from mysql.general_log order by 1;      -- 所有语句：  成功和未成功的.-- 关闭sql 执行记录

sql执行时间查询

-开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表  
--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭  
select * from information_schema.profiling limit 10\G;  

#开启profile
set profiling=1;
# 运行SQL语句
select * from user;
# 查看结果列表
show profiles;

# 查看详细信息,这里的1是 表ID
show profile for query 1;

# 查看CPU开销
show profile cpu for query 1;

# 查看MEMORY部分的开销 
show profile memory for query 1;

# 同时查看不同资源开销 
show profile block io,cpu for query 1;


--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列  
set @query_id=2;  
SELECT STATE, SUM(DURATION) AS Total_R,  
   ROUND(  
        100 * SUM(DURATION) /  
           (SELECT SUM(DURATION)  
            FROM INFORMATION_SCHEMA.PROFILING  
            WHERE QUERY_ID = @query_id  
        ), 2) AS Pct_R,  
     COUNT(*) AS Calls,  
     SUM(DURATION) / COUNT(*) AS "R/Call"  
  FROM INFORMATION_SCHEMA.PROFILING  
  WHERE QUERY_ID = @query_id  
  GROUP BY STATE  
  ORDER BY Total_R DESC;