23-MYSQL数据库(二)

视图

视图：VIEW,虚表，保存有实表的查询结果
创建方法：
    CREATE VIEW view_name [(column_list)]
        AS select_statement
        [WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义：SHOW CREATE VIEW view_name
删除视图：
    DROP VIEW [IF EXISTS]
        view_name [, view_name] ...
        [RESTRICT | CASCADE]
视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；
其修改操作受基表限制  

-- 创建视图
create view view_test as select st.name,co.course,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.CourseID;
-- 查询是否为视图
show talbe status like 'view_test'\G
-- 删除视图
drop view view_test

函数

函数：系统函数和自定义函数
    系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summaryref.html
自定义函数 (user-defined function UDF)
    保存在mysql.proc表中
    创建UDF
        CREATE [AGGREGATE] FUNCTION function_name(parameter_name
        type,[parameter_name type,...])
        RETURNS {STRING|INTEGER|REAL}
        runtime_body
    说明：
    参数可以有多个,也可以没有参数
    必须有且只有一个返回值  

自定义函数

创建函数
    示例：无参UDF
    CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN “Hello World!”;
    查看函数列表：
    SHOW FUNCTION STATUS;
    查看函数定义
    SHOW CREATE FUNCTION function_name
    删除UDF:
    DROP FUNCTION function_name
    调用自定义函数语法:
    SELECT function_name(parameter_value,...)
    
示例：有参数UDF
    DELIMITER //
    CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS
    VARCHAR(20)
    BEGIN
    DELETE FROM students WHERE stuid = uid;
    RETURN (SELECT COUNT(stuid) FROM students);
    END//
    DELIMITER ; 
    
自定义函数中定义局部变量语法
    DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
示例:
    DELIMITER //
    CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
    RETURNS SMALLINT
    BEGIN
    DECLARE a, b SMALLINT UNSIGNED;
    SET a = x, b = y;
    RETURN a+b;
    END//
    DELIMITER ;
    
自定义函数
    为变量赋值语法
    SET parameter_name = value[,parameter_name = value...]
    SELECT INTO parameter_name
示例:
    ...
    DECLARE x int;
    SELECT COUNT(id) FROM tdb_name INTO x;
    RETURN x;
    END//
    
存储过程
存储过程优势
    存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
    提高了运行速度
    同时降低网络数据传输量
存储过程与自定义函数的区别
    存储过程实现的过程要复杂一些,而函数的针对性较强
    存储过程可以有多个返回值,而自定义函数只有一个返回值
    存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
    
存储过程：存储过程保存在mysql.proc表中
创建存储过程
    CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
    routime_body
    proc_parameter : [IN|OUT|INOUT] parameter_name type
    其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
    param_name表示参数名称；type表示参数的类型
查看存储过程列表
    SHOW PROCEDURE STATUS;
查看存储过程定义
    SHOW CREATE PROCEDURE sp_name
调用存储过程
    CALL sp_name ([ proc_parameter [,proc_parameter ...]])
    CALL sp_name
    说明:当无参时,可以省略"()",当有参数时,不可省略"()"
存储过程修改
    ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改
    存储过程体,所以要修改存储过程,方法就是删除重建
删除存储过程
    DROP PROCEDURE [IF EXISTS] sp_name
    
存储过程示例
创建无参存储过程
    delimiter //
    CREATE PROCEDURE showTime()
    BEGIN
    SELECT now();
    END//
    delimiter ;
    CALL showTime;  
创建含参存储过程：只有一个IN参数
    delimiter //
    CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
    BEGIN
    SELECT * FROM students WHERE stuid = uid;
    END//
    delimiter ;
    call selectById(2);
示例
    delimiter //
    CREATE PROCEDURE dorepeat(n INT)
    BEGIN
    SET @i = 0;
    SET @sum = 0;
    REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
    UNTIL @i > n END REPEAT;
    END//
    delimiter ;
    CALL dorepeat(100);
    SELECT @sum;
创建含参存储过程:包含IN参数和OUT参数
    delimiter //
    CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
    BEGIN
    DELETE FROM students WHERE stuid >= uid;
    SELECT row_count() into num;
    END//
    delimiter ;
    call deleteById(2,@Line);
    SELECT @Line;
说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删
除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

select row_count()  --记录上一条命令更改的行数

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行
流程控制：
    IF：用来进行条件判断。根据是否满足条件，执行不同语句
    CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
    LOOP：重复执行特定的语句，实现一个简单的循环
    LEAVE：用于跳出循环控制
    ITERATE：跳出本次循环，然后直接进入下一次循环
    REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
    WHILE：有条件控制的循环语句  

触发器

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
创建触发器
    CREATE
        [DEFINER = { user | CURRENT_USER }]
        TRIGGER trigger_name
        trigger_time trigger_event
        ON tbl_name FOR EACH ROW
        trigger_body
    说明：
        trigger_name：触发器的名称
        trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
        trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
        tbl_name：该触发器作用在表名
        
触发器示例
    CREATE TABLE student_info (
        stu_id INT(11) NOT NULL AUTO_INCREMENT,
        stu_name VARCHAR(255) DEFAULT NULL,
        PRIMARY KEY (stu_id)
    );
    CREATE TABLE student_count (
        student_count INT(11) DEFAULT 0
    );
    INSERT INTO student_count VALUES(0);
示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
    CREATE TRIGGER trigger_student_count_insert
    AFTER INSERT
    ON student_info FOR EACH ROW
    UPDATE student_count SET student_count=student_count+1;
    CREATE TRIGGER trigger_student_count_delete
    AFTER DELETE
    ON student_info FOR EACH ROW
    UPDATE student_count SET student_count=student_count-1;
    
查看触发器
    SHOW TRIGGERS
    查询系统表information_schema.triggers的方式指定查询条件，查看指定的 触发器信息。
        mysql> USE information_schema;
        Database changed
        mysql> SELECT * FROM triggers WHERE
            trigger_name='trigger_student_count_insert';
删除触发器
    DROP TRIGGER trigger_name;

MySQL用户和权限管理

元数据数据库：mysql
    系统授权表：
        db, host, user
        columns_priv, tables_priv, procs_priv, proxies_priv
用户账号：
    'USERNAME'@'HOST'
        @'HOST':
        主机名
        IP地址或Network
        通配符： % _
    示例：172.16.%.%  

用户管理

创建用户：CREATE USER
    CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
    默认权限：USAGE
用户重命名：RENAME USER
    RENAME USER old_user_name TO new_user_name;
删除用户：
    DROP USER 'USERNAME'@'HOST'
    示例：删除默认的空用户
    DROP USER ''@'localhost';   

修改密码：
    mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD('password');
    mysql>UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
        此方法需要执行下面指令才能生效：
        mysql> FLUSH PRIVILEGES;
    #mysqladmin -u root -poldpass password 'newpass'
忘记管理员密码的解决办法：
    启动mysqld进程时，为其使用如下选项：
        --skip-grant-tables --skip-networking
    使用UPDATE命令修改管理员密码
    关闭mysqld进程，移除上述两个选项，重启mysqld  

小笔记

create user test@'192.168.37.%' identified by 'centos';
select user,host,password,authentication_string from user;
-- 客户端连接
mysql -utest -pcentos -h192.168.37.7
-- 删除空用户
drop user ''@'localhost';
drop user ''@'centos7.localdomain';
-- 修改密码
set password for root@'localhost'=password('centos');   -- 立即生效
set password for root@'127.0.0.1'=password('centos');
update user set password=password('centos') where host='centos7.localdomain';   -- 不会立即生效
flush privileges;
-- 修改反向解析
show variables like 'skip%';    #查询
skip_name_resolve   OFF
vim /etc/mysql.cnf
    [mysqld]
    skip_name_resolve
:wq
systemctl mariadb restart
-- 密码忘记处理
vim /etc/my.cnf
    skip_grant_tables
    skip_networking
:wq
mysql
--执行修改密码命令后，把配置文件改回来即可

MySQL权限管理

权限类别：
    管理类
    程序类
    数据库级别
    表级别
    字段级别
管理类：
    CREATE TEMPORARY TABLES
    CREATE USER
    FILE
    SUPER
    SHOW DATABASES
    RELOAD
    SHUTDOWN
    REPLICATION SLAVE
    REPLICATION CLIENT
    LOCK TABLES
    PROCESS
程序类： FUNCTION、 PROCEDURE、 TRIGGER
    CREATE
    ALTER
    DROP
    EXCUTE
库和表级别：DATABASE、 TABLE
    ALTER
    CREATE
    CREATE VIEW
    DROP
    INDEX
    SHOW VIEW
    GRANT OPTION：能将自己获得的权限转赠给其他用户   
数据操作
    SELECT
    INSERT
    DELETE
    UPDATE
字段级别
    SELECT(col1,col2,...)
    UPDATE(col1,col2,...)
    INSERT(col1,col2,...)
所有权限
    ALL PRIVILEGES 或 ALL    

授权

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
    priv_type: ALL [PRIVILEGES]
    object_type:TABLE | FUNCTION | PROCEDURE
    priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
    with_option: GRANT OPTION
        | MAX_QUERIES_PER_HOUR count
        | MAX_UPDATES_PER_HOUR count
        | MAX_CONNECTIONS_PER_HOUR count
        | MAX_USER_CONNECTIONS count
    示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO
    'someuser'@'somehost';
回收授权
    REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
    [object_type] priv_level FROM user [, user] ...
    示例：REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
查看指定用户获得的授权
    Help SHOW GRANTS
    SHOW GRANTS FOR 'user'@'host';
    SHOW GRANTS FOR CURRENT_USER[()];
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
    (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进
    程通常会自动重读授权表，使之生效
    (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程
    重读授权表：mysql> FLUSH PRIVILEGES;   

小笔记

grant all on hellodb.* to test@'192.168.37.%' identified by 'centos';   -- 授权
show grants for test@'192.168.37.%';        -- 查询授权情况
revoke delete on hellodb.* from test@'192.168.37.%';    -- 取消授权
revoke all on hellodb.* from test@'192.168.37.%';       -- 取消全部授权

MySQL架构

image.png

存储引擎

image.png

MyISAM引擎特点
    不支持事务
    表级锁定
    读写相互阻塞，写入不能读，读时不能写
    只缓存索引
    不支持外键约束
    不支持聚簇索引
    读取数据较快，占用资源较少
    不支持MVCC（多版本并发控制机制）高并发
    崩溃恢复性较差
    MySQL5.5.5前默认的数据库引擎 

MyISAM存储引擎适用场景
    只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件
    tbl_name.frm 表格式定义
    tbl_name.MYD 数据文件
    tbl_name.MYI 索引文件

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

InnoDB数据库文件
    所有InnoDB表的数据和索引放置于同一个表空间中
        表空间文件：datadir定义的目录下
        数据文件：ibddata1, ibddata2, ...
    每个表单独使用一个表空间存储表的数据和索引
        启用：innodb_file_per_table=ON
        参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table    ON (>= MariaDB 5.5)
        两类文件放在数据库独立目录中
            数据文件(存储数据和索引)：tb_name.ibd
            表格式定义：tb_name.frm   

​ 其它存储引擎
​ Performance_Schema：Performance_Schema数据库使用
​ Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
​ MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large DataBase)环境，如数据仓库
​ Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
​ Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
​ BDB：可替代InnoDB的事务引擎，支持COMMIT、 ROLLBACK和其他事务特性
​ Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
​ CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
​ BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
​ example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
​ MariaDB支持的其它存储引擎：
​ OQGraph
​ SphinxSE
​ TokuDB
​ Cassandra
​ CONNECT
​ SQUENCE

管理存储引擎

查看mysql支持的存储引擎
    show engines;
查看当前默认的存储引擎
    show variables like '%storage_engine%';
设置默认的存储引擎
vim /etc/my.conf
    [mysqld]
    default_storage_engine= InnoDB
查看库中所有表使用的存储引擎
    show table status from db_name;
查看库中指定表的存储引擎
    show table status like ' tb_name ';
    show create table tb_name;
设置表的存储引擎：
    CREATE TABLE tb_name(... ) ENGINE=InnoDB;
    ALTER TABLE tb_name ENGINE=InnoDB;

MySQL中的系统数据库

mysql数据库
    是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据    库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
performance_schema数据库
    MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的 存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema数据库
    MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

服务器配置

mysqld选项，服务器系统变量和服务器状态变量
    https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
    https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/
注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置 

获取mysqld的可用选项列表：
    mysqld --help --verbose
    mysqld --print-defaults 获取默认设置
设置服务器选项方法：
    在命令行中设置
    shell> ./mysqld_safe --skip-name-resolve=1
在配置文件my.cnf中设置
    skip_name_resolve=1

服务器端设置

服务器系统变量：分全局和会话两种
获取系统变量
    mysql> SHOW GLOBAL VARIABLES;
    mysql> SHOW [SESSION] VARIABLES;
    mysql> SELECT @@VARIABLES;
修改服务器变量的值：
    mysql> help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
    mysql> SET GLOBAL system_var_name=value;
    mysql> SET @@global.system_var_name=value;
修改会话变量：
    mysql> SET [SESSION] system_var_name=value;
    mysql> SET @@[session.]system_var_name=value; 
服务器状态变量：
    分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
    mysql> SHOW GLOBAL STATUS;
    mysql> SHOW [SESSION] STATUS;    

-- 查询最大连接数
show variables like '%connection%';
max_connections     |   151
set global max_connections=3000;        -- 临时修改最大连接数

服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/
常见MODE:
    NO_AUTO_CREATE_USER
        禁止GRANT创建密码为空的用户
    NO_ZERO_DATE
        在严格模式，不允许使用‘0000-00-00’ 的时间
    ONLY_FULL_GROUP_BY
        对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么
        将认为这个SQL是不合法的
    NO_BACKSLASH_ESCAPES
        反斜杠“\” 作为普通字符而非转义字符
    PIPES_AS_CONCAT
        将"||"视为连接操作符而非“或运算符”         

小笔记

mysql> set sql_mode='traditional';      -- 传统模式
show warnings;      -- 查询报警信息
show variables like 'sql_mode';

查询的执行路径

image.png

查询缓存

查询缓存（ Query Cache ）原理
    缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写优缺点
    不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率查询缓存的使用，会增加检查和清理Query Cache中记录集的开销 
    
哪些查询可能不会被缓存
    查询语句中加了SQL_NO_CACHE参数
    查询语句中含有获得值的函数，包含自定义函数，如：NOW()、CURDATE()、 GET_LOCK()、 RAND()、 CONVERT_TZ()等
    对系统数据库的查询：mysql、 information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量
    查询语句中使用了LOCK IN SHARE MODE、 FOR UPDATE的语句，查询语句中类似SELECT …INTO 导出数据的语句
    对临时表的查询操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句
    事务隔离级别为Serializable时，所有查询语句都不能缓存
    
查询缓存相关的服务器变量
    query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较
小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导
致碎片过多，内存不足
    query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结
果过大而无法缓存的语句，建议使用SQL_NO_CACHE
    query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024
的整数倍，最小值40KB，低于此值有警报
    query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以
从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景
中继续从缓存返回数据；ON则表示不允许
    query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
    
SELECT语句的缓存控制
    SQL_CACHE：显式指定存储查询结果于缓存之中
    SQL_NO_CACHE：显式查询结果不予缓存
query_cache_type参数变量
    query_cache_type的值为OFF或0时，查询缓存功能关闭
    query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
    query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
    参看：https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type
          https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html   

优化查询缓存

image.png

查询缓存

查询缓存相关的状态变量：SHOW GLOBAL STATUS LIKE 'Qcache%';
    Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
    Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
    Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
    Qcache_hits：Query Cache 命中次数
    Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
    Qcache_lowmem_prunes：记录因为内存不足而被移除出查询缓存的查询数
    Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL以及由于 query_cache_type 设置的不会被 Cache 的 SQL语句
    Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量  

命中率和内存使用率估算

查询缓存中内存块的最小分配单位query_cache_min_res_unit ：
    (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 ：Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
查询缓存内存使用率：(query_cache_size – qcache_free_memory) / query_cache_size * 100% 

索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现
优点：
    索引可以降低服务需要扫描的数据量，减少了IO次数
    索引可以帮助服务器避免排序和使用临时表
    索引可以帮助将随机I/O转为顺序I/O
缺点：
    占用额外空间，影响插入速度
索引类型：
    B+ TREE、 HASH、 R TREE
    聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
    主键索引、二级（辅助）索引
    稠密索引、稀疏索引：是否索引了每一个数据项
    简单索引、组合索引
        左前缀索引：取前面的字符做索引
        覆盖索引：从索引中即可取出要查询的数据，性能高 

二叉树

image.png

红黑树

image.png

B-TREE索引

image.png
image.png
image.png

B+TREE索引

B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
可以使用B+Tree索引的查询类型：
    全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
    匹配最左前缀：即只使用索引的第一列，如：姓wang
    匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
    匹配范围值：如：姓ma和姓wang之间
    精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
    只访问索引的查询  
B+Tree索引的限制：
    如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
    不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
特别提示：
    索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
    为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
    不适用于顺序查询：索引存储顺序的不是值的顺序
    不支持模糊匹配
    不支持范围查询
    不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效  

索引

空间数据索引R-Tree（ Geospatial indexing ）
    MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访
    问，常用于做地理数据存储，使用不多
    InnoDB从MySQL5.7之后也开始支持
全文索引(FULLTEXT)
    在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
    InnoDB从MySQL 5.6之后也开始支持

聚簇和非聚簇索引

image.png

聚簇和非聚簇索引，主键和二级索引

image.png

索引

冗余和重复索引：
    冗余索引：（A），（A，B）
    重复索引：已经有索引，再次建立索引
索引优化策略：
    独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
    左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估索引选择性：不重复的索引值和数据表的记录总数的比值
    多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
    选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧 

索引优化建议

● 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
● 尽量使用短索引，如果可以，应该制定一个前缀长度
● 对于经常在where子句使用的列，最好设置索引
● 对于有多个列where或者order by子句，应该建立复合索引
● 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
● 尽量不要在列上进行运算（函数操作和表达式操作）
● 尽量不要使用not in和<>操作  

SQL语句性能优化

● 查询时，能不要*就不用*，尽量写全字段名
● 大部分情况连接效率远大于子查询
● 多表连接时，尽量小表驱动大表，即小表 join 大表
● 在有大量记录的表分页时使用limit
● 对于经常使用的查询，可以开启缓存
● 多使用explain和profile分析查询语句
● 查看慢查询日志，找出执行时间长的sql语句优化  

管理索引

创建索引：
    CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
    ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
    help CREATE INDEX;
删除索引：
    DROP INDEX index_name ON tbl_name;
    ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
查看索引：
    SHOW INDEXES FROM [db_name.]tbl_name;
优化表空间：
    OPTIMIZE TABLE tb_name;
查看索引的使用
    SET GLOBAL userstat=1;
    SHOW INDEX_STATISTICS;  

小笔记

show index from students\G      -- 查索引
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

explain select * from students where age=20;    -- 查看是否使用索引(key)
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

--单索引
create index idx_age on students(age);          -- 建立stuednet.age索引
explain select * from students where age=20;
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | students | ref  | idx_age       | idx_age | 1       | const |    2 |       |
+------+-------------+----------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

create index idx_name on students(name(10));    -- 建立studnets.name索引，长度为10个字符
explain select * from students where name like 's%';
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | students | range | idx_name      | idx_name | 32      | NULL |    4 | Using where |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain select * from students where name like 'x%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | idx_name      | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

drop index idx_name on students;                --删除索引
drop index idx_age on students;
-- 复合索引
create index idx_name_age on students(name,age);
explain select * from students where name like 's%';        -- 利用索引
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | idx_name_age  | idx_name_age | 152     | NULL |    4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

explain select * from students where age=20;                -- 跳过name，索引不利用
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain select * from students where name like 's%' and age=20;     --可利用索引
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | idx_name_age  | idx_name_age | 153     | NULL |    4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

explain select * from students where stuid > 10;            --主键可利用索引
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | students | range | PRIMARY       | PRIMARY | 4       | NULL |   15 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain select * from students where stuid + 10 > 20;       --主键运算不能利用索引
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

-- 索引利用记录
vim /etc/my.cnf
    [mysqld]
    userstat    #开启索引利用记录
:wq
systemctl mariadb restart 
mysql> show variables like 'userstat';      -- 查看是否打开索引利用记录
mysql> show index_statistics;               -- 查看索引利用记录情况

-- 导入数据库做实验：
-- 导入
mysql hellodb < testlog.sql
mysql> call sp_testlog;
-- 创建索引
create index idx_name on testlog(name);
explain select * from testlog where name='wang99999';   -- 索引查询

-- 唯一键索引
create unique index uni_age on testlog(age);
show indexes from testlog;
drop index uni_age on testlog;

EXPLAIN(索引)

通过EXPLAIN来分析索引的有效性
EXPLAIN SELECT clause
    获取查询执行计划信息，用来查看查询优化器如何执行查询
输出信息说明：
    参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
id: 当前查询语句中，每个SELECT语句的编号
    复杂类型的查询有三种：
        简单子查询
        用于FROM中的子查询
        联合查询：UNION
    注意：UNION查询的分析结果会出现一个额外匿名临时表
    
select_type：
    简单查询为SIMPLE
    复杂查询：
        SUBQUERY 简单子查询
        PRIMARY 最外面的SELECT
        DERIVED 用于FROM中的子查询
        UNION UNION语句的第一个之后的SELECT语句
        UNION RESULT 匿名临时表
table：SELECT语句关联到的表 

type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
    ALL: 全表扫描
    index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”,表示了使用覆盖索引，而非全表扫描
    range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
    ref: 根据索引返回表中匹配某单个值的所有行
    eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
    const, system: 直接返回单个行
possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数

ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数
Extra：额外信息
    Using index：MySQL将会使用覆盖索引，以避免访问表
    Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
    Using temporary：MySQL对结果排序时会使用临时表
    Using filesort：对结果使用一个外部索引排序

并发控制

锁粒度：
    表级锁
    行级锁
锁：
    读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞
    写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和它锁
实现
    存储引擎：自行实现其锁策略和锁粒度
    服务器级：实现了锁，表级锁，用户可显式请求
分类：
    隐式锁：由存储引擎自动施加锁
    显式锁：用户手动请求
    
-- 表锁
lock tables students read;
lock tables students write;
-- 查看进程
show processlist;
-- 结束进程
kill 6;
-- 全局读锁，备份用
flush tables with read lock;

锁策略：在锁粒度及数据安全性寻求的平衡机制
显式使用锁
    LOCK TABLES 加锁
            tbl_name [[AS] alias] lock_type
            [, tbl_name [[AS] alias] lock_type] ...
            lock_type: READ ， WRITE
        UNLOCK TABLES 解锁
    FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
        关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
    SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
        查询时加写或读锁

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
    A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
    C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
    I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
    D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中  

Transaction生命周期

image.png

启动事务：
    BEGIN
    BEGIN WORK
    START TRANSACTION
结束事务：
    COMMIT：提交
    ROLLBACK: 回滚
    注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
    建议：显式请求和提交事务，而不要使用“自动提交”功能
事务支持保存点：savepoint
    SAVEPOINT identifier
    ROLLBACK [WORK] TO [SAVEPOINT] identifier
    RELEASE SAVEPOINT identifier
    
-- 关闭自动提交
show variables like 'autocommit';
set autocommit=0;       --临时关闭
--永久关闭
vim /etc/my.cnf
    [mysqld]
    autocommit=OFF
:wq
systemctl restart mariadb;
--关闭后需要输入：commit 才能提交(oracle默认状态)
delete form students where stuid=25;
rollback;           -- 回滚，commit前可后悔
commit;             -- 提交

-- 事务方式导入数据，必须关闭autocommit(速度比正常快)
begin;
call sp_testlog;
commit;
-- rollback
insert update delete    -- DML可以撤销
create drop alter       -- DDL不可以撤销

-- 单条回滚
begin;
insert students (name,age,gender) values ('c','23','m');
savepoint c_tran;   -- 保存点
insert students (name,age,gender) values ('d','22','m');
savepoint d_tran;
insert students (name,age,gender) values ('e','17','m');
savepoint e_tran;
rollback to d_scan;
release e_tran identifier;      -- 删除保存点

事务隔离级别

事务隔离级别：从上至下更加严格
    READ UNCOMMITTED 可读取到未提交数据，产生脏读
    READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
    REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
    SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
MVCC: 多版本并发控制，和事务级别相关

# READ UNCOMMITTED
    A事务开始后，B可以看到A事务没有commit(提交)的数据
# READ COMMITTED
    A事务提交后B事务才可以看到数据，但每次读取的数据不一致
# REPEATABLE READ(备份时用)
    A事务提交后B事务才可以看到数据，每次读取的数据一致，只有B事务修改的才会变动，数据可能是过时的
# SERIALIZABILE
    未提交的读事务阻塞修改事务，未提交的修改事务阻塞读事务，但别人不能访问，并发性差

事务隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
读未提交（read-uncommitted）	是	是	是	否
不可重复读（read-committed）	否	是	是	否
可重复读（repeatable-read）	否	否	是	否
串行化（serializable）	否	否	否	是

事务

指定事务隔离级别：
    服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
    SET tx_isolation=''
        READ-UNCOMMITTED
        READ-COMMITTED
        REPEATABLE-READ     #默认值
        SERIALIZABLE
    服务器选项中指定
    vim /etc/my.cnf
        [mysqld]
        transaction-isolation=SERIALIZABLE  
    mysql> select @@tx_isolation    #查看变量值

并发控制

死锁：
    两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务日志：
    事务日志的写入类型为“追加”，因此其操作为“顺序IO” ；通常也被称为：预写式日志 write ahead logging
    事务日志文件： ib_logfile0， ib_logfile1  

日志

日志
事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log  

事务日志

事务日志：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 ./ 事务文件路径
        innodb_flush_log_at_trx_commit 默认为1
        
innodb_flush_log_at_trx_commit
    说明：设置为1，同时sync_binlog = 1表示最高级别的容错
    innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
    1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
    0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
    2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
    3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

#小笔记
#事务日志分离
#新的硬盘创建新分区，格式化挂载/data/logs
mount /dev/sdb1 /mnt/data/logs
chown mysql.mysql /data/logs
vim /etc/my.cnf
    innodb_log_group_home_dir=/data/logs
systemctl restart mariadb   

事务日志优化

image.png

错误日志

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

通用日志

通用日志：记录对数据库的通用操作，包括错误的SQL语句
    文件：file，默认值
    表：table
通用日志相关设置
    general_log=ON|OFF
    general_log_file=HOSTNAME.log
    log_output=TABLE|FILE|NONE 
    
-- 通用日志放在数据库中
vim /etc/my.cnf
    general_log=ON
    log_output=TABLE
systemctl restart mariadb;
mysql> select * from general_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
    上述查询类型且查询时长超过long_query_time，则记录日志
    log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录  #推荐打开，默认文件名centos7-slow.log
    log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
    log_slow_verbosity= Query_plan,explain 记录内容
    log_slow_queries = OFF 同slow_query_log 新版已废弃  
    
    show variables like 'profiling';    
    set profiling=on;                   --开启慢语句记录
    select sleep(1) from teachers;
    show profiles;                      --查看哪条是慢语句
    show profile for query 2;

-- 开启慢查询日志(默认关闭)
vim /etc/my.cnf
    slow_query_log=ON
    long_query_time=3       --默认10秒
    log_queries_not_using_indexes=ON    --记录不用索引语句,默认OFF
    
-- 数据库表整理优化(释放空间)
optimize table testlog;
-- mysql客户端日志
~/.mysql_history

二进制日志

二进制日志
    记录导致数据改变或潜在导致数据改变的SQL语句
    记录已提交的日志
    不依赖于存储引擎类型
    功能：通过“重放”日志文件中的事件来生成数据副本
        注意：建议二进制日志和数据文件分开存放
中继日志：relay log
    主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件  

二进制日志记录格式
    二进制日志记录三种格式
        基于“语句”记录：statement，记录语句，默认模式
        基于“行”记录：row，记录数据，日志量较大
        混合模式：mixed, 让系统自行判定该基于哪种方式进行
    格式配置
        show variables like 'binlog_format';
二进制日志文件的构成
有两类文件
    日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
        如： mariadb-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(记录语句)，ROW每一行修改都记录
    max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G。说明：文件达到上限时的大小未必为指定的精确值
    sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
    expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除
    
二进制日志相关配置
    查看mariadb自行管理使用中的二进制日志文件列表，及大小
        SHOW {BINARY | MASTER} LOGS
    查看使用中的二进制日志文件
        SHOW MASTER STATUS
    查看二进制文件中的指定内容
        SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
        show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

日志

mysqlbinlog：二进制日志的客户端命令工具
命令格式：
    mysqlbinlog [OPTIONS] log_file…
    --start-position=# 指定开始位置
    --stop-position=#
    --start-datetime=
    --stop-datetime=
    时间格式：YYYY-MM-DD hh:mm:ss
    --base64-output[=name]
    -v -vvv
    示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
        mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop
        datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
        
二进制日志事件的格式：
    # at 328
    #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，mysql5.6以mariadb10以上版本专属属性：GTID
    
清除指定二进制日志：
        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;    

备份和恢复

为什么要备份
    灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
备份注意要点
    能容忍最多丢失多少数据
    恢复数据需要在多长时间内完成
    需要恢复哪些数据
还原要点
    做还原测试，用于测试备份的可用性
    还原演练
    
备份类型：
    完全备份，部分备份
        完全备份：整个数据集
        部分备份：只备份数据子集，如部分库或表
    完全备份、增量备份、差异备份
        增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
        差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
    注意：二进制日志文件不应该与数据文件放在同一磁盘
    
冷、温、热备份
    冷备：读写操作均不可进行
    温备：读操作可执行；但写操作不可执行
    热备：读写操作均可执行
        MyISAM：温备，不支持热备
        InnoDB：都支持
物理和逻辑备份
    物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
    逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
    
备份时需要考虑的因素
    温备的持锁多久
    备份产生的负载
    备份过程的时长
    恢复过程的时长
备份什么
    数据
    二进制日志、 InnoDB的事务日志
    程序代码（存储过程、函数、触发器、事件调度器）
    服务器的配置文件
    
备份工具
    cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
    LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
    mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
    xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
    MariaDB Backup： 从MariaDB 10.1.26开始集成，基于PerconaXtraBackup 2.3.8实现
    mysqlbackup：热备份， MySQL Enterprise Edition组件
    mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、 FLUSH TABLES和cp或scp来快速备份数据库
    
基于LVM的备份
    (1) 请求锁定所有表
        mysql> FLUSH TABLES WITH READ LOCK;
    (2) 记录二进制日志文件及事件位置
        mysql> FLUSH LOGS;
        mysql> SHOW MASTER STATUS;
        mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
    (3) 创建快照
        lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
    (4) 释放锁
        mysql> UNLOCK TABLES;
    (5) 挂载快照卷，执行数据备份
    (6) 备份完成后，删除快照卷
    (7) 制定好策略，通过原卷备份二进制日志   

小笔记：备份和恢复应用

-- 冷备份(需要停服务)
systemctl stop mariadb
mkdir backup
cd backup
tar -Jcf all.bak.tar.xz /var/lib/mysql       -- 数据库
tar -Jcf login.tar.xz /data/logbin           -- 二进制日志
cp -p /etc/my.cnf .                         -- 配置文件
-- 找一台机器测试
yum install mariadb -y                      -- 版本最好一样
cp -p backup/my.cnf /etc/my.cnf
mkdir /data/logbin
chown mysql.mysql /data/logbin
tar -xf login.tar.xz -C /
tar -xf all.bak.tar.xz -C /
systemctl start mariadb

备份和恢复

逻辑备份工具：mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
    mysqldump [OPTIONS] database [tables]
    mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
    mysqldump [OPTIONS] –A [OPTIONS]
mysqldump参考：
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
    
mysqldump常见选项：
    -A， --all-databases 备份所有数据库，含create database
    -B , --databases db_name… 指定备份的数据库，包括create database语句
    -E, --events：备份相关的所有event scheduler
    -R, --routines：备份所有存储过程和自定义函数
    --triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
    --default-character-set=utf8 指定字符集
    --master-data[=#]： 此选项须启用二进制日志
        1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
        2：记录为注释的CHANGE MASTER TO语句
        此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除
        非开启--single-transaction）
    
    -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的
二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。 建议在同一时刻
执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使
用实现，此时只刷新一次日志
    --compact 去掉注释，适合调试，生产不使用
    -d, --no-data 只备份表结构
    -t, --no-create-info 只备份数据,不备份create table
    -n,--no-create-db 不备份create database，可被-A或-B覆盖
    --flush-privileges 备份mysql或相关时需要使用
    -f, --force 忽略SQL错误，继续执行
    --hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
    -q, --quick 不缓存查询，直接输出，加快备份速度    
    
MyISAM备份选项：
    支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
    锁定方法如下：
        -x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能
        注意：数据量大时，可能会导致长时间无法并发访问数据库
        -l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
    注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
    
InnoDB备份选项： 支持热备，可用温备但不建议用
    --single-transaction
    此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
    此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
    此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
    备份大型表时，建议将--single-transaction选项和--quick结合一起使用    

mysqldump工具

#开启二进制日志
vim /etc/my.cnf
    [mysqld]
    log_bin=/data/logbin
    binlog_format=row
:wq

1、第一种备份方法(不推荐，不备份库名)
#备份数据库
mysqldump hellodb > /data/hellodb.sql
mysql> drop database hellodb; 
mysql> show databases;
#还原数据库
mysql -e 'create database hello'
mysql hello < /data/hellodb.sql

2、第二种备份方法(推荐，带库名)
mysqldump -B hello > /data/hello.sql    #单数据库备份
mysqldump -B hello mysql > /data/hello.sql  #多数据库备份

3、第三种备份方法
#备份
mysqldump -A --default-character-set=utf8mb4 > /data/all.sql            #全部备份
mysqldump -A --master-data=2 > /data/all2.sql                       #备份文件默认注释二进制日志标志位
mysqldump -A --master-data=1 > /data/all3.sql                       #备份文件默认不注释二进制日志标志位

#模拟故障，恢复数据库
rm -rf "/usr/lib/mysql/*"
systemctl restart mariadb
vim /etc/my.cnf
    [mysql]
    skip_grant_tables
    skip_networking
:wq
systemctl restart mariadb
mysql 
    set sql_log_bin=off;    #暂时关闭二进制日志
    show master logs;
    exit
grep -i "master_log_pos" /data/all2.sql
#查到master_log_file='mysql-bin.00003',master_log_pos=245；
mysqlbinlog --start-position=710 mysql-bin.00003 > /data/inc.sql
mysqlbinlog mysql-bin.00004 >> /data/inc.sql
mysqlbinlog mysql-bin.00005 >> /data/inc.sql
mysql -uroot
    source /data/all2.sql
    source /data/inc.sql    #导入数据
    use hello;
    select * from students;
    set sql_log_bin=on;
    exit
vim /etc/my.cnf
    [mysql]
    #skip_grant_tables
    #skip_networking
:wq
systemctl restart mariadb
    
----------------分割线------------------
    
#误删除表，恢复表操作
mysqldump -A --master-data=2 > /data/all_`data +%F`.sql
mysql -uroot
    use hello;
    insert students (name,age) values ('c',30);
    insert students (name,age) values ('d',40);
    drop table students;
    insert teachers (name,age) values ('mage',30);
    insert teachers (name,age) values ('wang',18);

#还原
grep -i "master_log_pos" /data/all_2019-7-10.sql
#查到master_log_file='mysql-bin.00006',master_log_pos=245；
mysql -e "flush tables whit read lock;"
mysqlbinlog --start-position=245 mysql-bin.00006 > /data/inc.sql
vim /data/inc.sql   #注释掉DROP TABLE开头的语句
rm -rf /usr/lib/mysql
systemctl restart mariadb
mysql -uroot
    set sql_log_bin=off;
    source /data/inc.sql
    select * from students;
    select * from teachers;
    set sql_log_bin=on;


mysqldump -A -F     #滚动日志
mysqldump -A -F --single--transaction --master-data=2 > /data/all.sql   #滚动日志，开启新的日志
pruse binary logs to 'mysql-bin.000016'     #清除多余日志，16之前的

备份和恢复

MyISAM备份选项：
    支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
    锁定方法如下：
    -x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--single
    transaction或--lock-tables选项会关闭此选项功能
    注意：数据量大时，可能会导致长时间无法并发访问数据库
    -l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
    注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用  
    
InnoDB备份选项： 支持热备，可用温备但不建议用
    --single-transaction
    此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
    此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的
    表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
    此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
    备份大型表时，建议将--single-transaction选项和--quick结合一起使用  

生产环境实战备份策略

InnoDB建议备份策略
    mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
MyISAM建议备份策略
    mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql  

分库备份

#查看有什么数据库
DATABASE=mysql -e 'show databases' | grep -Eiv '^(information_schema|performance.*|database)$'
for DB in $DATABASE;do mysqldump -B $DB --single-transaction --master-datg=2 > /data/$DB.sql;done
#备份带压缩
for DB in $DATABASE;do mysqldump -B $DB --single-transaction --master-datg=2 | gzip > /data/$DB.sql.gz ;done

mysql -e 'show databases' | grep -Eiv '^(information_schema|performance.*|database)$' | sed -nr 's@(.*)@mysqldump -B \1 --single-transaction --master-datg=2 | gzip > /data/\1.sql.gz@p' | bash

xtrabackup

Percona
    官网：www.percona.com
    percona-server
    InnoDB --> XtraDB
Xtrabackup
    percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
    手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：
    备份还原过程快速、可靠
    备份过程不会打断正在执行的事务
    能够基于压缩等功能节约磁盘空间和流量
    自动实现备份检验
    开源，免费
    
Xtrabackup2.2版之前包括4个可执行文件：
    innobackupex: Perl 脚本
    xtrabackup: C/C++ 编译的二进制
    xbcrypt: 加解密
    xbstream: 支持并发写的流文件格式
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在xtrabackup 之上做了一层封装实现的

Xtrabackup备份过程

image.png

xtrabackup的新版变化

xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：
    innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary程序，另
    外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现
    在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过
    xtrabackup替换innobackupex
xtrabackup安装：
    yum install percona-xtrabackup 在EPEL源中
    最新版本下载安装：
    https://www.percona.com/downloads/XtraBackup/LATEST/  

xtrabackup用法

备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
    --user：该选项表示备份账号
    --password：该选项表示备份的密码
    --host：该选项表示备份数据库的地址
    --databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
    --defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
    --incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir
    --incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
    --incremental-dir：该选项表示还原时增量备份的目录
    --include=name：指定表名，格式：databasename.tablename 

Prepare：innobackupex --apply-log [option] BACKUP-DIR
    选项说明：
    --apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
    --use-memory：和--apply-log选项一起使用，当prepare 备份时，做crashrecovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
    --export：表示开启可导出单独的表之后再导入其他Mysql中
    --redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并    

还原：innobackupex --copy-back [选项] BACKUP-DIR
    innobackupex --move-back [选项] [--defaults-group=GROUP-NAME]
    BACKUP-DIR
选项说明：
    --copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
    --move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
    
还原注意事项：
    1.datadir 目录必须为空。除非指定innobackupex --force-non-emptydirectorires选项指定，否则--copy-back选项不会覆盖
    2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
    3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户
        chown -R mysql:mysql /data/mysql
        以上需要在用户调用innobackupex之前完成
        --force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败 

备份生成的相关文件

● 使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、 MERGE、 CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
    (1)xtrabackup_info：innobackupex工具执行时的相关信息，包括版本，备份选项，
备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
    (2)xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为
prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序
列号LSN。 LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面
最近是如何发生改变的
    (3)xtrabackup_binlog_info：MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
    (4)backup-my.cnf：备份命令用到的配置选项信息
    (5)xtrabackup_logfile：备份生成的日志文件  

小笔记：

#需要epl源支持
yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
#完全备份
xtrabackup --backup --target-dir=/backup/
#还原(需要停止服务)
scp -r /backup/* 192.168.37.17:/backup
    #37.17上操作
    yum install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
    xtrabackup --prepare --target-dir=/backup/      #预处理
    xtrabackup --copy-back --target-dir=/backup/    #拷贝回去
    chown -R mysql:mysql /var/lib/mysql
    systemctl start mariadb  

#增量备份
xtrabackup --backup --target-dir=/backup/base       #完整备份
mysql -uroot
    use hello
    insert teachers (name,age) values ('wang',20);
exit
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base    #增量备份
mysql -uroot
    use hello
    insert teachers (name,age) values ('wang',30);
exit
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1    #增量备份

#恢复(需要停止服务)
systemctl stop mariadb
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/base
systemctl start mariadb 

示例：新版xtrabackup完全备份及还原

1 在原主机做完全备份到/backups
    xtrabackup --backup --target-dir=/backup/
    scp -r /backup/* 目标主机:/backup
2 在目标主机上
    1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
    xtrabackup --prepare --target-dir=/backup/
    2）复制到数据库目录
    注意：数据库目录必须为空，MySQL服务不能启动
    xtrabackup --copy-back --target-dir=/backup/
    3）还原属性
    chown -R mysql:mysql /var/lib/mysql
    4）启动服务
    systemctl start mariadb  

示例：新版xtrabackup完全，增量备份及还原

1 备份过程
    1）完全备份：xtrabackup --backup --target-dir=/backup/base
    2）第一次修改数据
    3）第一次增量备份
    xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
    4）第二次修改数据
    5）第二次增量
    xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
    6）scp -r /backup/* 目标主机:/backup/
备份过程生成三个备份目录
    /backup/{base，inc1，inc2} 
2 还原过程
    1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
    xtrabackup --prepare --apply-log-only --target-dir=/backup/base
    2）合并第1次增量备份到完全备份，
    xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
    3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
    xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
    4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
    xtrabackup --copy-back --target-dir=/backup/base
    5）还原属性：chown -R mysql:mysql /var/lib/mysql
    6）启动服务：systemctl start mariadb