MySQL日志及引擎
MySQL日志文件
错误日志(error log):记录mysql服务器mysqld在启动/关闭或运行过程中遇到的错误信息。
查询日志(query log):
普通查询日志(general query log):记录客户端连接信息和执行的sql语句信息。
慢查询日志(slow query log):记录执行时间超出指定值(long_query_time)的sql语句。
二进制日志(binary log):记录数据被修改的相关信息。
在配置文件中进行配置各日志路径
log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3306/log/slow.log log-error = /data/mysql/mysql_3306/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
查看log相关及测试
mysql> show variables like '%_log%';
开启普通查询日志
mysql> set global general_log=ON; Query OK, 0 rows affected (0.07 sec)
测试查看效果
mysql> select user,host from mysql.user;
[root@localhost data]# cat localhost.log mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql_3306.sock Time Id Command Argument 181204 12:20:03 2 Query show variables like '%_log%' 181204 12:21:40 2 Query select user,host from mysql.user [root@localhost data]#
慢查询日志
# 慢查询参数 long_query_time=1 #多长时间记录 log-slow-queries=/data/slow.log log_queries_not_using_indexes
二进制日志
log-bin
sql-log-bin
binlog日志三种模式
1 Statement Level 模式
每一条修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
优点:不需要记录每一行数据的变化,较少bin-log日志量,节约IO,提高性能。
2 Row Level 模式
日志中会记录每一行数据被修改的形式,然后在slave端再对相同的数据行修改
优点:bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅需要记录那一条记录被修改了。
缺点:产生大量的日志内容。
3 Mixed 模式
前两种模式的结合。
模式调整
查看
mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+ 1 row in set (0.00 sec)
在线修改生效
运行时在线修改
set session binlog_format='ATATEMENT';
set session binlog_format='ROW';
set session binlog_format='MIXED';
全局生效
set global binlog_format='STATEMENT'; set global binlog_format='ROW'; set global binlog_format='MIXED';
MySQL服务引擎
对于用户和应用程序同样一张表,无论什么引擎来存储,用户看到的数据都是一样的,不同的存储引擎存取,引擎功能,占用空间大小,读取性能都有区别。
MySQL最常用存储引擎:MyISAM和InnoDB。
MyISAM引擎
每一个MyISAM表都对应硬盘上的三个文件,这三个文件都有一样的文件名,有不同的扩展名。.frm文件保存表的定义,这个文件不是MyISAM引擎的一部分,而是服务的一部分;.myd保存表的数据;.myi是表的索引文件。
[root@localhost mysql]# file user.frm user.frm: MySQL table definition file Version 9 [root@localhost mysql]# file user.MYD user.MYD: Hitachi SH big-endian COFF executable, not stripped [root@localhost mysql]# file user.MYI user.MYI: MySQL MISAM compressed data file Version 1
MyISAM引擎特点
1 不支持事务(事务时逻辑上的以组操作,组成这操作的各个单元,要么全成功,要么全失败。
事务的四大特性(ACID)
1.原子性(Atomicity),事务是一个不可分割的单位,事务中的所有sql等操作要么都发生,要么都不发生。
2.一致性(Consistercy),事务发生前和发生后,数据的完整性必须保持一致。
3.隔离性(Isolation),并发访问数据时,一个事务在执行完毕前,对于其它的会话不可见,多个并发事务之间的数据是相互隔离的。
4.持久性(Durability),一个事务一旦提交,对数据库中的数据改变时永久性的,出了错误,事务也不允许撤销,只能通过”补偿性事务“。
2 表级锁定(锁整个表)降低了并发性能。
3 读写互相阻塞,在写入的时候阻塞读取,在读取的时候阻塞写入,读本身不会阻塞另外的读。
4 只会缓存索引,可以通过key_buffer_size缓存索引,提高访问性能,减少磁盘IO,只会缓存索引,不缓存数据。
5 读取表快,占用资源少
6 不支持外键约束,支持全文索引。
生产业务场景
1 不需要事务支持的业务,一般为读数据比较多的网站业务。
2 并发相对较低的业务。
3 数据修改相对较少的业务。
4 以读为主的业务。
5 对数据一致性要求不是很高的业务。
6 中小站网站部分业务。
总结:单一对数据库的操作都可以用,单一就是尽量纯读或纯写。
引擎调优
1 设置合适的索引(缓存机制)。
2 调整读写优先级,根据实际需求确保重要操作更优先执行。
3 启用延迟插入改善大批量写入性能(降低写入频率,尽可能多条数据一次性写入)。
4 尽量顺序操作让insert数据都写入到尾部,减少阻塞。
5 分解大的操作,降低单个操作的阻塞时间。
6 降低并发数,某些高并发场景通过应用进行排队队列机制。
7 对于相对静态的数据库,利用query Cache或memcached缓存服务提高访问效率。
8 MyISAM的count只有在全表扫描的时候高效,带有其它条件的要进行实际的数据访问。
9 把主从同步的主库用InnoDB,从库用MyISAM引擎。
InnoDB引擎
InnoDB引擎包含在二进制安装包里,和其它的引擎相比,兼容ACID事务,以及参数的完整性。
InnoDB引擎特点
1 支持事务:支持4个事务隔离级别,支持多版本读。
2 行级锁定(更新时一般时锁当前行),通过索引实现,全表扫描仍然时表锁。
3 读写阻塞与事务隔离级别相关。
4 具有非常高效的缓存特性,能缓存索引,也能缓存数据。
5 整个表和主键以cluster方式存储,组成一颗平衡树。
6 所有Secondary Index都会保存主键信息。
7 支持分区、表空间。
8 支持外键约束。
InnoDB业务场景
1 需要事务支持(具有较好的事务特性)。
2 行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成。
3 数据更新较为频繁的场景。
4 数据一致性要求较高的业务。
5 硬件设备内存较大,利用InnoDB较好的缓存能力来提高内存利用率,减少磁盘IO。
InnoDB引擎调优
1 主键尽可能小,避免给Secondary index带来过大的空间负担。
2 避免全表扫描,因为会使用表锁。
3 尽可能缓存所有的索引和数据,提高相应速度,减少磁盘IO。
4 咋大批量小插入的时候,尽量自己控制事务不使用autocommit自动提交。
5 合适使用innodb_flush_log_at_trx_commit参数值,不要过度追求安全性。
6 避免主键更新,因为会带来大量的数据移动。
MySQL引擎
1 查看有那些引擎可用
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
MySQL引擎更改
1 MySQL命令语句修改
alter table talbe_name engine=InnoDB;
修改示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table t1 engine=MyISAM; Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
2 用sed对备份内容进行引擎更换。
3 mysql_convert_table_format命令修改。
mysql_convert_table_format --user=username --password=passwd --socket=sockpath --ENGINE=MyISAM dbname tbnama
4 创建表时指定引擎
mysql> create table t( -> id int, -> name varchar(10))ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (2.02 sec) mysql> show create table t; +-------+------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
问题:
1.MySQL有哪些存储引擎,各自有什么特点和区别?
2.生产场景如何选用MySQL的引擎?