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;
修改示例
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的引擎？
