MYSQL---关于MYSQL优化
1. 慢查询,规定查询超过2秒的为慢查询(救火)
1)mysql> show full processlist;
2)用explain查看查询语句是否经过索引,如果没有就要建立索引
long_query_time =2
3)对需要建立索引的条件建立索引,大于300万的记录不能建立索引
2. 日常记录文件
[root@MySQL ~]# grep gene /data/3306/my.cnf
general_log = on
general_log_file = /data/3306/data/MySQL_oldboy.log
general_log = on
general_log_file = /data/3306/data/MySQL_oldboy.log
临时生效:
mysql> set global general_log_file = "/data/3306/data/MySQL_oldboy.log";
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like 'general_log%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | ON |
| general_log_file | /data/3306/data/MySQL_oldboy.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log = on;
Query OK, 0 rows affected (0.03 sec)
mysql> set global general_log_file = "/data/3306/data/MySQL_oldboy.log";
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like 'general_log%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | ON |
| general_log_file | /data/3306/data/MySQL_oldboy.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
mysql> set global general_log = on;
Query OK, 0 rows affected (0.03 sec)
[root@oldboy ~]# cat /data/3306/data/MySQL_oldboy.log
/application/mysql-5.5.32/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/3306/mysql.sock
Time Id Command Argument
160828 19:09:22 15 Query show databases
160828 19:09:25 15 Query explain select * from test where state=1
160828 19:09:28 15 Query show full processlist
160828 19:09:36 15 Query select * from test
15 Query select * from test
160828 19:09:37 15 Query select * from test
160828 19:09:39 15 Quit
/application/mysql-5.5.32/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/3306/mysql.sock
Time Id Command Argument
160828 19:09:22 15 Query show databases
160828 19:09:25 15 Query explain select * from test where state=1
160828 19:09:28 15 Query show full processlist
160828 19:09:36 15 Query select * from test
15 Query select * from test
160828 19:09:37 15 Query select * from test
160828 19:09:39 15 Quit
删除binlog日志方法
1、设置参数自动删除
expire_logs_days = 7 #<==删除7天前的日志
2、从头删到指定的文件位置
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.03 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 126 8月 21 16:31 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql 126 8月 21 16:32 /data/3306/mysql-bin.000005
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 84 8月 28 19:26 /data/3306/mysql-bin.index
3、按照时间删除
mysql> PURGE MASTER LOGS BEFORE '2016-08-28 13:00:00';
Query OK, 0 rows affected (0.02 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 28 8月 28 19:28 /data/3306/mysql-bin.index
1、设置参数自动删除
expire_logs_days = 7 #<==删除7天前的日志
2、从头删到指定的文件位置
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.03 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 126 8月 21 16:31 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql 126 8月 21 16:32 /data/3306/mysql-bin.000005
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 84 8月 28 19:26 /data/3306/mysql-bin.index
3、按照时间删除
mysql> PURGE MASTER LOGS BEFORE '2016-08-28 13:00:00';
Query OK, 0 rows affected (0.02 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 28 8月 28 19:28 /data/3306/mysql-bin.index
mysql> PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 3 DAY);
Query OK, 0 rows affected (0.00 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 28 8月 28 19:28 /data/3306/mysql-bin.index
Query OK, 0 rows affected (0.00 sec)
mysql> system ls -l /data/3306/mysql-bin*
-rw-rw---- 1 mysql mysql 15881 8月 28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 28 8月 28 19:28 /data/3306/mysql-bin.index
1、硬件层面优化
1.1 数据库物理机采购:
1.2 服务器硬件配置调整
1.2.1 服务器BIOS调整:
1.2.2 阵列卡调整:
2、软件层优化
2.1 操作系统层面优化
2.1.1 操作系统及MySQL实例选择
2.1.2 文件系统层优化
2.1.3 linux内核参数优化
2.2 mysql数据库层面优化
2.2.1 my.cnf参数优化
2.2.2 关于库表的设计规范
2.2.3 SQL语句的优化
3、网站集群架构上的优化
4、流程,制度,安全优化
1.1 数据库物理机采购:
1.2 服务器硬件配置调整
1.2.1 服务器BIOS调整:
1.2.2 阵列卡调整:
2、软件层优化
2.1 操作系统层面优化
2.1.1 操作系统及MySQL实例选择
2.1.2 文件系统层优化
2.1.3 linux内核参数优化
2.2 mysql数据库层面优化
2.2.1 my.cnf参数优化
2.2.2 关于库表的设计规范
2.2.3 SQL语句的优化
3、网站集群架构上的优化
4、流程,制度,安全优化