linux进阶之mariadb（二）索引、日志

mariadb的索引

索引：提取索引的创建在的表上字段中的数据，构建出一个独特的数据结构；

索引的作用：加速查询操作；副作用：降低写操作性能；

表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
某个字段或某些字段：WHERE子句中用到的字段     

索引类型：B+ TREE，HASH

B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；
    适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；
    全值匹配：精确匹配某个值；
        WHERE COLUMN = 'value';
    匹配最左前缀：只精确匹配起头的部分；
            WEHRE COLUMN LIKE 'PREFIX%';                        
    匹配范围值：
        精确匹配某一列，范围匹配另一列；
        只用访问索引的查询：覆盖索引；
            index(Name)
            SELECT Name FROM students WHERE Name LIKE 'L%';                     
    不适用B+ TREE索引：
        如果查询条件不是从最左侧列开始，索引无效；
            index(age,Fname), WHERE Fname='Jerry';    , WHERE age>30 AND Fname='Smith';
        不能跳过索引中的某列；
            index(name,age,gender)
                WHERE name='black' and age > 30;
                WHERE name='black' AND gender='F';
        如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；
                WHERE age>30 AND Fname='Smith';

Hash索引：基于哈希表实现，特别适用于值的精确匹配查询；

    适用场景：
        只支持等值比较查询，例如=, IN(), <=>
    不用场景：
        所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引；

索引优点：

降低需要扫描的数据量，减少IO次数；
可以帮助避免排序操作，避免使用临时表； 
帮助将随机IO转为顺序IO；

高性能索引策略：

(1) 在WHERE中独立使用列，尽量避免其参与运算；
    WHERE age+2 > 32 ; 
(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
    索引选择性：不重复的索引值和数据表的记录总数的比值；
(3) 多列索引：
    AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
(4) 选择合适的索引列次序：选择性最高的放左侧；

EXPLAIN来分析索引有效性：

EXPLAIN [explain_type] SELECT select_options
    explain_type:
        EXTENDED
        | PARTITIONS    
MariaDB [mysql]> EXPLAIN  SELECT user from user;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | user  | index | NULL          | PRIMARY | 228     | NULL |    3 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

id：当前查询语句中，第个SELECT语句的编号；

- 复杂的查询的类型主要三种：
    - 简单子查询
    - 用于FROM中的子查询
    - 联合查询
    - 注意：联合查询的分析结果会出现一个额外的匿名临时表；

select_type：查询类型：

- 简单查询：SIMPLE
- 复杂查询：
- 简单子查询：SUBQUERY
    - 用于FROM中的子查询：DERIVED
    - 联合查询中的第一个查询：PRIMARY
    - 联合查询中的第一个查询之后的其它查询：UNION
    - 联合查询生成的临时表：UNION RESULT

table：查询针对的表；
type：关联类型，或称为访问类型，即MySQL如何去查询表中的行

- ALL：全表扫描；
- index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
- range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
- ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
- eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；
- const，system：与某个常数比较，且只返回一行；

possiable_keys：查询中可能会用到的索引；
key：查询中使用的索引；
key_len：查询中用到的索引长度；
ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；
rows：MySQL估计出的为找到所有的目标项而需要读取的行数；
Extra：额外信息

- Using index：使用了覆盖索引进行的查询；
- Using where：拿到数据后还要再次进行过滤； 
- Using temporary：使用了临时表以完成查询；
- Using filesort：对结果使用了一个外部索引排序；

日志

查询日志：general_log

查询日志
记录查询语句，日志存储位置：
    文件：file
    表：table (mysql.general_log)
        general_log={ON|OFF}是否记录所有语句的日志信息与一般查询日志文件（general_log_file）
        general_log_file=HOSTNAME.log 
        log_output={FILE|TABLE|NONE}

慢查询日志：log_slow_queries

慢查询：运行时间超出指定时长的查询；
    long_query_time
    存储位置：
        文件：FILE
        表：TABLE，mysql.slog_log
        log_slow_queries={ON|OFF}是否记录慢查询日志
        slow_query_log={ON|OFF}
        slow_query_log_file= 定义慢查询日志的保存文件
        log_output={FILE|TABLE|NONE}
        log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk不记录慢查询日志的动作或者事务
        log_slow_rate_limit 定义慢查询的时间限制
        log_slow_verbosity

错误日志：log_error， log_warnings

记录如下几类信息：
    (1) mysqld启动和关闭过程中输出的信息； 
    (2) mysqld运行中产生的错误信息； 
    (3) event scheduler运行时产生的信息；
    (4) 主从复制架构中，从服务器复制线程启动时产生的日志；
        log_error=/var/log/mariadb/mariadb.log|OFF
        log_warnings={ON|OFF} 

二进制日志：binlog

用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合；
功用：“重放”
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：语句；
ROW：行；
MIXED：混编；
查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件：
SHOW MASTER STATUS；
查看二进制 日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服务器变量：
log_bin=/PATH/TO/BIN_LOG_FILE
只读变量；
session.sql_log_bin={ON|OFF}
控制某会话中的“写”操作语句是否会被记录于日志文件中；
max_binlog_size=1073741824
sync_binlog={1|0}
mysqlbinlog：
YYYY-MM-DD hh:mm:ss
--start-datetime=
--stop-datetime=
-j, --start-position=#
--stop-position=#
--user, --host, --password

常用的二进制日志选项：

log_bin = {ON|OFF},也可以是一个文件路径
log_bin_trust_function_creators 不阻止任何存储函数
sql_log_bin = {ON|OFF} 当前会话是否将修改记入到二进制文件中
sql_log_off 是否将一般查询记入到查询日志中
sync_binlog 同步缓冲区二进制到应到的时间，0表示不急于时间同步，旨在时间提交时同步
binlog_format={statement|row|mixed}
max_binlog_cache_size MariaDB二进制日志的缓存区大小，仅用于缓存事务类的语句
max_binlog_stmt_cache_size 语句缓存区大小，即事务类和非事务类公用的大小 
max_binlog_size 二进制日志文件的上限，单位为字节
建议：切勿将二进制日志与数据文件放在同一设备上
可以临时通过sql_log_bin来控制未禁止的写入

二进制日志事件格式：

[root@test1 mysql]# mysqlbinlog maste-log.000003 > /test.log
[root@test1 mysql]# cat /test.log 
# at 480
#170916 16:24:14 server id 1  end_log_pos 563   Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1505549921/*!*/;

对上解释：
事件的起始位置：# at 480
事件发生的日期时间：#170916 16:24:14
事件发生的服务器id：server id 1
事件的结束位置：end_log_pos 563
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID： thread_id=2
语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
错误代码：error_code=0
设定事件发生时的时间戳：SET TIMESTAMP=1505549921/!/;
事件内容：（这里是会有不同的执行动作）

中继日志：

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
对于非从服务器中继日志没有启用
       relay_log_purge = {ON|OFF} 是否自动清理不在需要的中继日志
       relay_log_space_limit 中继大小是否限制

事务日志：

事务型存储引擎innodb用于保证事务特性的日志文件：     
    redo log 
    undo log 

清除日志：

  PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr（某个时间点之前） }