1、总结mysql常见的存储引擎以及特点。
MyISAM 存储引擎
引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入是不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5 前默认的数据库引擎
适用场景
- 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
InnoDB 引擎
引擎特点
- 行级锁
- 支持事务，适合处理大量短期的事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
其他存储引擎
- Performance_Schema：Performance_Schema数据库使用
- Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
- MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
- Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
- Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
- BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
- Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
- CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
- BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
- example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
2、MySQL查询缓存优化总结。
概述
mysql查询缓存在数据库优化可以起到很大的作用
一、缓存条件，原理
MySQL Query Cache是用来缓存我们所执行的SELECT语句以及该语句的结果集，MySql在实现Query Cache的具体技术细节上类似典型的KV存储，就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。当客户端发起SQL查询时，Query Cache的查找逻辑是，先对SQL进行相应的权限验证，接着就通过Query Cache来查找结果（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同，即使完全相同的SQL，如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存）。它不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生同任何存储引擎的交互，减少了大量的磁盘IO和CPU运 算，所以有时候效率非常高。
查询缓存的工作流程如下:
1：命中条件
缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key.在判断是否命中前,MySQL不会解析SQL,而是直接使用SQL去查询缓存,SQL任何字符上的不同,如空格,注释,都会导致缓存不命中.
如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的
2：工作流程
- 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)
- 如果找到了缓存,则直接返回缓存(性能提升)
- 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.
- 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)
二、相关SQL语句
2.1、查看SQL缓存参数：
show variables like '%query_cache%';
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
其中各个参数的意义如下：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 Qcache_free_memory：缓存中的空闲内存。 Qcache_hits：每次查询在缓存中命中时就增大 Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。 Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况) Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。 Qcache_total_blocks：缓存中块的数量。
2.2、开启SQL缓存：set global query_cache_type = 1;
2.3、关闭SQL缓存：set global query_cache_type = 0;
2.4、设置缓存空间：set global query_cache_size = 1024102464 （64M）
2.5、固定SQL语句声明不适用缓存：select sql_no_cache * from 表名
注意：改变SQL语句的大小写或者数据表有数据改动，则不会调用缓存。
2.6、配置查询缓存
vim /etc/mysql/mysql.conf.d/mysqld
query_cache_size=300M
query_cache_type=1
mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 314572800 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.00 sec)
三、清除缓存
mysql的FLUSH句法(清除缓存）
FLUSH flush_option [,flush_option]
如果你想要清除一些MySQL使用内部缓存，你应该使用FLUSH命令。为了执行FLUSH，你必须有reload权限。
flush_option可以是下列任何东西：
HOSTS 这个用的最多，经常碰见。主要是用来清空主机缓存表。如果你的某些主机改变IP数字，或如果你得到错误消息Host ... isblocked，你应该清空主机表。当在连接MySQL服务器时，对一台给定的主机有多于 max_connect_errors个错误连续不断地发生，MySQL为了安全的需要将会阻止该主机进一步的连接请求。清空主机表允许主机再尝试连接。 LOGS 关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1。 PRIVILEGES 这个也是经常使用的，每当重新赋权后，为了以防万一，让新权限立即生效，一般都执行一把，目地是从数据库授权表中重新装载权限到缓存中。 TABLES 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。 FLUSH TABLES WITH READ LOCK 关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。 STATUS 重置大多数状态变量到0。 MASTER 删除所有的二进制日志索引文件中的二进制日志文件，重置二进制日志文件的索引文件为空，创建一个新的二进制日志文件,不过这个已经不推荐使用，改成reset master 了。可以想象，以前自己是多土啊，本来一条简单的命令就可以搞定的，却要好几条命令来，以前的做法是先查出来当前的二进制日志文件名，再用purge 操作。 QUERY CACHE 重整查询缓存，消除其中的碎片，提高性能，但是并不影响查询缓存中现有的数据，这点和Flush table 和Reset Query Cache（将会清空查询缓存的内容）不一样的。 SLAVE 类似于重置复制吧，让从数据库忘记主数据库的复制位置，同时也会删除已经下载下来的relay log,与Master一样，已经不推荐使用，改成Reset Slave了。这个也很有用的。
一般来讲，Flush操作都会记录在二进制日志文件中，但是FLUSH LOGS、FLUSH MASTER、FLUSH SLAVE、FLUSH TABLES WITH READ LOCK不会记录，因此上述操作如果记录在二进制日志文件中话，会对从数据库造成影响。注意：Reset操作其实扮演的是一个Flush操作的增强版的角色。
四、缓存的内存管理
缓存会在内存中开辟一块内存(query_cache_size)来维护缓存数据,其中有大概40K的空间是用来维护缓存的元数据的,例如空间内存,数据表和查询结果的映射,SQL和查询结果的映射等.
MySQL将这个大内存块分为小的内存块(query_cache_min_res_unit),每个小块中存储自身的类型,大小和查询结果数据,还有指向前后内存块的指针.
MySQL需要设置单个小存储块的大小,在SQL查询开始(还未得到结果)时就去申请一块空间,所以即使你的缓存数据没有达到这个大小,也需要用这 个大小的数据块去存(这点跟Linux文件系统的Block一样).如果结果超出这个内存块的大小,则需要再去申请一个内存块.当查询完成发现申请的内存 块有富余,则会将富余的空间释放掉,这就会造成内存碎片问题
在查询开始时申请分配内存Block需要锁住整个空闲内存区,所以分配内存块是非常消耗资源的.注意这里所说的分配内存是在MySQL初始化时就开辟的那块内存上分配的.
五、缓存的使用时机 & 性能
衡量打开缓存是否对系统有性能提升是一个很难的话题
- 通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次数 (Com_select)
- 通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)
- 通过命中-写入率 判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能MySQL中称之为比较能反映性能提升的指数,一般来说达到3:1则算是查询缓存有效,而最好能够达到10:1
任何事情过犹不及，尤其对于某些写频繁的系统，开启Query Cache功能可能并不能让系统性能有提升，有时反而会有下降。原因是MySql为了保证Query Cache缓存的内容和实际数据绝对一致，当某个数据表发生了更新、删除及插入操作，MySql都会强制使所有引用到该表的查询SQL的Query Cache失效。对于密集写操作，启用查询缓存后很可能造成频繁的缓存失效，间接引发内存激增及CPU飙升，对已经非常忙碌的数据库系统这是一种极大的负担。
**六、查询缓存问题分析 **
分析和配置查询缓存
总结
这里要注意Query Cache因MySql的存储引擎不同而实现略有差异，比如MyISAM，缓存的结果集存储在OS Cache中，而最流行的InnoDB则放在Buffer Pool中。
3、MySQL各类日志总结。
- 日志类型：
错误日志(error log)
常规日志(general log)
二进制日志(bin log)
中继日志(relay log)
慢日志(slow log)
InnoDB引擎的redo log
错误日志
log-error = error.log
不加存储路径存储到datadir下，如果不指定名字是hostname.err
记录MySQL启动关闭，运行中的异常或是重要提示信息
在生产环境中，监控这个日志
全量日志(常规日志)
general_log = OFF|ON
general_log_file = general.log
不指定路径存储到datadir下，如果不指定名字是hostname.log
开启后，记录client和数据库的所有请求
二进行日志
最大文件数为2的32次方个
log-bin = /data/mysql/mysql3306/logs/mysql-bin
设置二进制日志的存储位置及prefix名字
用记录数据库写入操作的日志，可以用于备份或是master/slave的复制，必须有server-id
如果没有可以通过修改内存里的值加上，但是有风险
不能太快产生，尽量大些，5分钟左右产生一个日志
log_bin = /data/mysql/mysql3306/log/mysql-bin 是否开启binlog
log_bin_index = /data/mysql/mysql3306/log/mysql-bin.index
指定binlog的一个索引文件，默认是在datadir形成log_bin的prefix名index
binlog_do_db = thunder
用于指定只记录那个库的二进制日志(建议不要用)
max_binlog_size = 500M
指定binlog的大小为500M一个文件，默认是1G
expire-logs-days = 5 指定保留binlog的天数
binlog_format = row
指定binlog的日志格式，支持statement,row,mixed格式
statement格式记录原生的sql
row格式里没有，但5.6后增加了一个query event可以看到原来的sql
binlog_rows_query_log_events 默认是off
mixed格式是两种形式的混合体，DDL语句记录statement格式，DML语句记录row格式
推荐使用row格式
binlog_row_image = full
控制日志中binlog的详细相关的程度，支持full(default),minimal,nobolb
minimal:只记录变化的行和唯一标识列
nobolb:除了blob,text其它列都记
binlog_error_action = abort_server
当遇到mysql不能写binlog时，报出异常，默认是ignore error不报错
binlog_direct_non_transactional_updates = on
对于非事务引擎表，直接走日志，不走2pc提交，默认是不支持
binlog_order_commit = on
按顺序写入日志
binlog_cache_size=1M 已经很大,此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和bin_cache_disk_use 来帮助测试
binlog_cache_use:使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
binlog_checksum (5.6.2引入)在高版本上修改
mysql5.6.6后默认是crc32，之前版本是none
在启用这个参数之前对于日志的完整校验就是通过对比长度，引入新的方式后，利用新方法对内容进行校验
binlog_rows_query_log_events (5.6.2引入)
只作用于RBR格式，默认不启用
如果启用，会把用户写直的原生态DML操作记录到binlog中
log_bin_use_v1_row_events (5.6.6引入)
默认是0，如果使用1是使用Version1的格式，mysql5.5可以认出来的形式，如果0是5.6.6后使用的version2格式
sync_binlog = 1|0
这个参数对性能影响严重，数据一致性条件要求高调整为1，性能差别大概为10倍
当事务提交后，mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，面让filesystem自行决定什么时候来同步，这个是性能最好的。
sync_binlog=n 在进行n次事务提交以后，mysql将执行一次fsync之类的磁盘同步指令，同步文件系统将binlog文件缓存刷新到磁盘。
mysql中默认的设置是sync_binlog=0,即不作任何强制性的磁盘刷新指令，这是性能是最好的，但风险也是最大的。一旦系统crash，在文件系统缓存中的所有binlog信息都会丢失
中继日志
relay-log = relay-bin
设置中继日志的名称前缀，不指定路径默认在datadir下
log_thread从master就读到的日志写到中继日志中，供sql_thread执行，以完成复制
慢日志
slow-query-log-file = slow.log
slow-query-log
long_query_time=1
是怎么计时的，
ddl语句 exec时间
dml语句 select从等待锁开始计时，insert只记录执行时间
利用slow-query-log这个参数打开慢日志，slow-query-log-file指定慢日志的名称，不指定路径默认在datadir下
建议每天一个文件，时间长可能会很大，做定时任务
Innodb的redo-log
事务处理日志
innodb_log_group_home_dir=/data/mysql/mysql3317/logs 默认在datadir下
SSD磁盘中，日志文件不要放在SSD磁盘中，普通硬盘即可
innodb_log_file_size = 200M 512M左右即可
innodb_log_files_in_group = 3 个数
指定redo log的存储位置及大小，文件个数
Innodb事务操作不可缺少的一个环节
