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;
优化,调优思路:
- 数据库设计与规划:以后再修该很麻烦,估计数据量,使用什么存储引擎
- 数据的应用:怎样取数据,sql语句的优化
- mysql服务优化:内存的使用,磁盘的使用
- 操作系统的优化:内核、tcp连接数量
- 升级硬件设备
数据库最好在设计之初就把一切都想好,比如部署时就打开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';