MySQL调优思路

MySQL优化

查看引擎： 
mysql> show engines; 
mysql> SHOW variables like '%storage_engine%'; 
mysql> show create table t1; mysql> show table status like 't1';

临时指定引擎： 
mysql> create table innodb1(id int)engine=innodb;

修改默认引擎： 
[root@xiaobai] vim /etc/my.cnf 
[mysqld] 
default-storage-engine=INNODB    #引擎

修改已经存在的表的引擎： 
mysql> alter table t2 engine=myisam; 

---

优化,调优思路：

1. 数据库设计与规划：以后再修该很麻烦，估计数据量，使用什么存储引擎
2. 数据的应用：怎样取数据，sql语句的优化
3. mysql服务优化：内存的使用，磁盘的使用
4. 操作系统的优化：内核、tcp连接数量
5. 升级硬件设备

数据库最好在设计之初就把一切都想好，比如部署时就打开binlog日志等
也可以直接通过增加物理服务器来优化性能
还可以通过索引来优化，比如查询时，给经常要查询的字段增加一个索引，用where查询就会快很多

---

如果用的是innodb引擎，只对于innodb引擎
如果你的分区满了并且是非lvm的形式，那么可以用物理分区（不提议这种方式，一般都用lvm扩容）
[root@xiaobai] mkdir /data   #把这个目录挂载到一个新的磁盘下
[root@xiaobai] chown mysql.mysql /data 
mysql> create table t1(id int,name char(20)) data directory='/data/';   #sql语句，指定存储表的内容目录，会存储到新的硬盘里
表结构存储的文件是.frm结尾的文件不能指定。 
如果创建不成功给data目录增加权限。

MySQL服务优化

#如果看着不方便，把后面的分号换成\G这时结尾不需要加;
mysql> show status;   #看系统的资源 ---各种状态参数1
mysql> status;   #看系统的资源 ---各种状态参数2
mysql> show variables;   #看变量，在my.cnf配置文件里定义的 
mysql> show warnings;   #查看最近一个sql语句产生的错误警告，看其他的需要看.err日志 
mysql> show processlist;   #显示系统中正在运行的所有进程
mysql> show errors;   #查看err日志

字符集设置

临时：
mysql> create database db1 CHARACTER SET = utf8; 
mysql> create table t1(id int(10)) CHARACTER SET = utf8;

也可以在mysql主配置文件里加#5.7/ 5.5版本设置
[mysqld] 
character_set_server = utf8

#更改配置文件后要重启mysql才能生效

慢查询

#查看mysql的相关配置信息
mysql> show variables like '%query%';
+------------------------------+----------------------------------+
| Variable_name                | Value                            |
+------------------------------+----------------------------------+
| binlog_rows_query_log_events | OFF                              |
| ft_query_expansion_limit     | 20                               |
| have_query_cache             | YES                              |
| long_query_time              | 10.000000                        |
| query_alloc_block_size       | 8192                             |
| query_cache_limit            | 1048576                          |
| query_cache_min_res_unit     | 4096                             |
| query_cache_size             | 33554432                         |
| query_cache_type             | ON                               |
| query_cache_wlock_invalidate | OFF                              |
| query_prealloc_size          | 8192                             |
| slow_query_log               | ON                               |
| slow_query_log_file          |/*slow.log |
+------------------------------+----------------------------------+

当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。 
这是查询数据库当前设置的最大连接数 
mysql> show variables like '%max_connections%';

查看超时时间 
mysql> show global variables like '%timeout%';

强制限制mysql资源设置(主配置文件中改):
[root@xiaobai] vim /etc/my.cnf max_connections = 1024   #并发连接数，根据实际情况设置连接数。 
[root@xiaobai] connect_timeout= 5   #单位秒，超时时间，默认30秒

---

安全模式

在配置文件中加入sql_safe_updates = 1

当 sql_safe_updates 设置为 1 时。update 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 使用 limit；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患

---

innodb引擎

主配置文件中
#innodb-buffer-pool-size=缓存InnoDB数据和索引的内存缓冲区的大小值,这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置 这个参数达机器物理内存大小的80%。 
[root@xiaobai] vim /etc/my.cnf 
innodb-buffer-pool-size=2G 
wait_timeout=10    #终止空闲时间超过10秒的链接，避免长连接 
max_connect_errors=10   #10次连接失败就锁定，使用flush hosts解锁
#保存并退出

mysql> flush hosts;   #解锁 
 
mysql> lock tables table_name read;   #锁表 锁定数据表，避免在备份过程中，表被更新 
mysql> lock tables table_name write;   #为表增加一个写锁定
  
mysql> show open tables where in_use >0;   #查询是否锁表
mysql> unlock tables;   #解锁 

慢日志调优

默认情况下MySQL不启动慢查询日志，需要我们手动开启，一般不建议启动该参数，因为开启慢查询日志会影响一定的性能。慢查询日志支持将日志记录写入文件，也支持将日志写入数据库表。写入慢日志语句的密码由服务器重写，不是纯文本形式的

配置参数

属性	值
命令行格式	–slow-query-log
系统变量	slow_query_log
范围	全局
动态	是
类型	布尔
默认值	OFF

设置slow_query_log为0或者OFF时禁用日志，设置为1或者ON时启用日志

---

定义时间阈值
long_query_time

属性	值
命令行格式	–long-query-time=#
系统变量	long_query_time
范围	全局
动态	是
类型	数字
默认值	10
最低值	0

如果查询花费时间大于设置的秒数，服务器会递增slow_queries状态的变量，如果启用了慢查询日志，这个查询记录会记录到慢查询日志中

---

设置慢日志（慢查询日志）文件名称

属性	值
命令行格式	–slow-query-log-file=file_name
系统变量	slow_query_log_file
范围	全局
动态	是
类型	文件名
默认值	host_name-slow.log

如果没有给慢查询日志文件制定名称，则默认名为host_name-slow.log，除非给出绝对路径名来指定其它目录，否则服务器在数据库目录中创建改默认文件

---

系统变量 log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项，开启这个参数使用full index scan的sql语句会被记录到慢查询日志

系统变量 log_slow_admin_statements 表示是否将慢管理语句例如analyze table和alter table等记入慢查询日志

配置慢日志

[mysqld]
slow_query_log=1   #开启慢查询日志
long_query_time=0.5   #设置阈值0.5秒
log_queries_not_using_indexes=ON
slow_query_log_file="/var/log/mysql/mysql_slow.log"
#保存并退出

[root@xiaobai] mkdir /var/log/mysql   #创建相对应的目录
[root@xiaobai] chown mysql.mysql /var/log/mysql   #修改权限


处理慢日志的大小
刷新日志时，服务器会创建新的二进制日志文件。但是，它只是关闭并重新打开常规和慢速查询日志文件。
要想创建新文件，在刷新之前重命名当前日志文件。
[root@xiaobai] cd /var/log/mysql
[root@xiaobai] mv mysql.log mysql.old
[root@xiaobai] mv mysql-slow.log mysql-slow.old
[root@xiaobai] mysqladmin flush-logs


假如有些情况下服务器不允许重新启动，但是有此情况下又需要对日志重新命名，以便继续备份或者日志切割。
那就可以使用如下方案
1.先禁用日志功能
mysql> set globalg general_log = 'OFF';
mysql> set globalg slow_query_log = 'OFF';
2.在外部重命名日志文件; 例如，从命令行
3.然后再次启用日志，并刷新日志
mysql> set globalg general_log = 'ON';
mysql> set globalg slow_query_log = 'ON';

文章总览分类目录 @小白