数据库 之 存储引擎 索引 日志

数据库之存储引擎

什么是存储引擎

存储引擎就是一种数据存储方式，不同的存储引擎对应着不同的存储方式，索引方式，锁定水平，当然就提供不同的作用。

引擎粗解

MyISAM引擎特点

1.不支持事务
2.表级锁定
3.读写相互阻塞，写入不能读，读时不能写
4.只缓存索引
5.不支持外键约束
6.不支持聚簇索引
7.读取数据较快，占用资源较少
8.不支持MVCC（多版本并发控制机制）高并发
9.崩溃恢复性较差
10.MySQL5.5.5前默认的数据库引擎
适用场景：
只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件：
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件

InnoDB引擎特点（重点使用）

1.支持事务，适合处理大量短期事务 
2.行级锁 
3.读写阻塞与事务隔离级别相关 
4.可缓存数据和索引 
5.支持聚簇索引 
6.崩溃恢复性更好 
7.支持MVCC高并发 
8.从MySQL5.5后支持全文索引 
9.从MySQL5.5.5开始为默认的数据库引擎
10.InnoDB数据库文件 

InnoDB引擎文件：
所有InnoDB表的数据和索引放置于同一个表空间中
1.表空间文件：datadir定义的目录下
2.数据文件：ibddata1, ibddata2, …
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON
两类文件放在数据库独立目录中
3.数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

InnoDB引擎：MyISAM  你就是个弟弟
MyISAM引擎：  我  我 你说的对啊  

MyISAM与InnoDB的区别
　　InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
ISAM：

ISAM是一个定义明确且历经时间考验的数据表格管理方法，
在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。
因此，ISAM执行读取操作的速度很快，而且不占用大量的内存
和存储资源。ISAM的两个主要不足之处在于，它不 支持事务
处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就
无法恢复了。如果你正在把ISAM用在关键任务应用程序里，
那就必须经常备份你所有的实 时数据，通过其复制特性，
MYSQL能够支持这样的备份应用程序。

其他引擎

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

引擎支持一览表　
另附 给mysql引擎设置
查看mysql支持的存储引擎:
show engines;
查看当前默认的存储引擎:

show variables like ‘%storage_engine%’;
设置默认的存储引擎：

vim /etc/my.conf
[mysqld]
default_storage_engine= InnoDB;

数据库之索引

什么是索引

索引是特殊数据结构：定义在查找时作为查找条件的字段
优点
1.在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。

2.能提高数据的搜索及检索速度，符合数据库建立的初衷。

3.能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。

4.在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

索引类型

聚簇索引的索引顺序和表中记录的物理顺序是一致的，而非聚簇索引的索引顺序和记录的物理顺序是不一致的，一个表中只能有一个聚簇索引，通常为设为主键的列，非聚簇索引可以创建多个。
我们以下面这个表来举例了解聚簇和非聚簇

非聚簇索引

（MyISAM的是非聚簇索引）
我们以男女作为节点

data挂的是其他全部的东西

聚簇索引

（InnoDB使用的是聚簇索引）

1,2，3挂的都是序号 这样找直接能找到那一行

数据库之日志

事务日志：transaction log

事务型存储引擎自行管理和使用
redo log
undo log
Innodb事务日志相关配置：
show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径

错误日志：error log

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置
SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径：
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1

查询日志：query log

记录查询操作
文件：file，默认值
表：table
查询日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

慢查询日志：slow query log

记录执行查询时长超出指定时长的操作
slow_query_log=ON|OFF 开启或关闭慢查询
long_query_time=N 慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,
full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_queries_not_using_indexes=ON 不使用索引也没有达到慢查询阀	值的语句是否记录日志--
--默认OFF，即不记录
log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain 记录内容log_slow_queries = 	OFF 
同slow_query_log 新版已废弃

二进制日志：binary log

记录导致数据改变或潜在导致数据改变的SQL语句
功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放

二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
SHOW MASTER STATUS
查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in 'mariadb-bin.000001' from 6516 limit 2,3

二进制日志记录格式

二进制日志记录三种格式
基于“语句”记录：statement，记录语句，默认模式
基于“行”记录：row，记录数据，日志量较大
混合模式：mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like '%binlog_format%';

二进制日志文件的构成

有两类文件
日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mysql-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式

二进制日志相关的服务器变量：

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

mysqlbinlog：二进制日志的客户端命令工具

命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;

例
二进制日志事件的格式：
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use mydb/!/;
SET TIMESTAMP=1446712300/!/;
CREATE TABLE tb1 (id int, name char(30))
/!/;
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mys

清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO ‘log_name’ | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3前日志
PURGE BINARY LOGS BEFORE ‘2017-01-23’;
PURGE BINARY LOGS BEFORE ‘2017-03-22 09:25:30’;
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; 日志文件从#开始记数，默认从1开始，一般是master第一次启动时执行，MariaDB10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;

中继日志：reley log**

主从复制架构中，从服务器用于保存从主服务器的二进制
日志中读取到的事件