mariadb的索引

索引：提取索引的創(chuàng)建在的表上字段中的數(shù)據(jù)白嘁，構(gòu)建出一個(gè)獨(dú)特的數(shù)據(jù)結(jié)構(gòu)坑鱼；

索引的作用：加速查詢操作；副作用：降低寫操作性能絮缅；

表中數(shù)據(jù)子集：把表中某個(gè)或某些字段的數(shù)據(jù)提取出來另存為一個(gè)特定數(shù)據(jù)結(jié)構(gòu)組織的數(shù)據(jù)鲁沥；
某個(gè)字段或某些字段：WHERE子句中用到的字段     

索引類型：B+ TREE，HASH

B+ TREE：順序存儲(chǔ)耕魄，每一個(gè)葉子結(jié)點(diǎn)到根結(jié)點(diǎn)的距離相同画恰；左前綴索引，適合于范圍類型的數(shù)據(jù)查詢吸奴；
    適用于B+ TREE索引的查詢類型：全鍵值允扇、鍵值范圍或鍵前綴；
    全值匹配：精確匹配某個(gè)值则奥；
        WHERE COLUMN = 'value';
    匹配最左前綴：只精確匹配起頭的部分考润；
            WEHRE COLUMN LIKE 'PREFIX%';                        
    匹配范圍值：
        精確匹配某一列，范圍匹配另一列逞度；
        只用訪問索引的查詢：覆蓋索引额划；
            index(Name)
            SELECT Name FROM students WHERE Name LIKE 'L%';                     
    不適用B+ TREE索引：
        如果查詢條件不是從最左側(cè)列開始，索引無效档泽；
            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';
        如果查詢中的某個(gè)列是為范圍查詢揖赴，那么其右側(cè)的列都無法再使用索引優(yōu)化查詢；
                WHERE age>30 AND Fname='Smith';

Hash索引：基于哈希表實(shí)現(xiàn)抑胎，特別適用于值的精確匹配查詢燥滑；

    適用場景：
        只支持等值比較查詢，例如=, IN(), <=>
    不用場景：
        所有非精確值查詢阿逃；MySQL僅對memory存儲(chǔ)引擎支持顯式的hash索引铭拧；

索引優(yōu)點(diǎn)：

降低需要掃描的數(shù)據(jù)量，減少IO次數(shù)恃锉；
可以幫助避免排序操作搀菩，避免使用臨時(shí)表； 
幫助將隨機(jī)IO轉(zhuǎn)為順序IO破托；

高性能索引策略：

(1) 在WHERE中獨(dú)立使用列肪跋，盡量避免其參與運(yùn)算；
    WHERE age+2 > 32 ; 
(2) 左前綴索引：索引構(gòu)建于字段的最左側(cè)的多少個(gè)字符土砂，要通過索引選擇性來評(píng)估
    索引選擇性：不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值州既；
(3) 多列索引：
    AND連接的多個(gè)查詢條件更適合使用多列索引，而非多個(gè)單鍵索引萝映；
(4) 選擇合適的索引列次序：選擇性最高的放左側(cè)吴叶；

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：當(dāng)前查詢語句中，第個(gè)SELECT語句的編號(hào)序臂；

- 復(fù)雜的查詢的類型主要三種：
    - 簡單子查詢
    - 用于FROM中的子查詢
    - 聯(lián)合查詢
    - 注意：聯(lián)合查詢的分析結(jié)果會(huì)出現(xiàn)一個(gè)額外的匿名臨時(shí)表蚌卤；

select_type：查詢類型：

- 簡單查詢：SIMPLE
- 復(fù)雜查詢：
- 簡單子查詢：SUBQUERY
    - 用于FROM中的子查詢：DERIVED
    - 聯(lián)合查詢中的第一個(gè)查詢：PRIMARY
    - 聯(lián)合查詢中的第一個(gè)查詢之后的其它查詢：UNION
    - 聯(lián)合查詢生成的臨時(shí)表：UNION RESULT

table：查詢針對的表；
type：關(guān)聯(lián)類型奥秆，或稱為訪問類型造寝，即MySQL如何去查詢表中的行

- ALL：全表掃描；
- index：根據(jù)索引的順序進(jìn)行的全表掃描吭练；但同時(shí)如果Extra列出現(xiàn)了"Using index”表示使用了覆蓋索引；
- range：有范圍限制地根據(jù)索引實(shí)現(xiàn)范圍掃描析显；掃描位置始于索引中的某一項(xiàng)鲫咽，結(jié)束于另一項(xiàng)；
- ref：根據(jù)索引返回的表中匹配到某單個(gè)值的所有行（匹配給定值的行不止一個(gè)）谷异；
- eq_ref：根據(jù)索引返回的表中匹配到某單個(gè)值的單一行分尸，僅返回一個(gè)行，但需要與某個(gè)額外的參考值比較歹嘹，而不是常數(shù)箩绍；
- const，system：與某個(gè)常數(shù)比較尺上，且只返回一行材蛛；

possiable_keys：查詢中可能會(huì)用到的索引圆到；
key：查詢中使用的索引；
key_len：查詢中用到的索引長度卑吭；
ref：在利用key字段所顯示的索引完成查詢操作時(shí)所引用的列或常量值芽淡；
rows：MySQL估計(jì)出的為找到所有的目標(biāo)項(xiàng)而需要讀取的行數(shù)；
Extra：額外信息

- Using index：使用了覆蓋索引進(jìn)行的查詢豆赏；
- Using where：拿到數(shù)據(jù)后還要再次進(jìn)行過濾挣菲； 
- Using temporary：使用了臨時(shí)表以完成查詢；
- Using filesort：對結(jié)果使用了一個(gè)外部索引排序掷邦；

日志

查詢?nèi)罩荆篻eneral_log

查詢?nèi)罩?記錄查詢語句白胀，日志存儲(chǔ)位置：
    文件：file
    表：table (mysql.general_log)
        general_log={ON|OFF}是否記錄所有語句的日志信息與一般查詢?nèi)罩疚募╣eneral_log_file）
        general_log_file=HOSTNAME.log 
        log_output={FILE|TABLE|NONE}

慢查詢?nèi)罩荆簂og_slow_queries

慢查詢：運(yùn)行時(shí)間超出指定時(shí)長的查詢；
    long_query_time
    存儲(chǔ)位置：
        文件：FILE
        表：TABLE抚岗，mysql.slog_log
        log_slow_queries={ON|OFF}是否記錄慢查詢?nèi)罩?        slow_query_log={ON|OFF}
        slow_query_log_file= 定義慢查詢?nèi)罩镜谋４嫖募?        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不記錄慢查詢?nèi)罩镜膭?dòng)作或者事務(wù)
        log_slow_rate_limit 定義慢查詢的時(shí)間限制
        log_slow_verbosity

錯(cuò)誤日志：log_error或杠， log_warnings

記錄如下幾類信息：
    (1) mysqld啟動(dòng)和關(guān)閉過程中輸出的信息； 
    (2) mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息苟跪； 
    (3) event scheduler運(yùn)行時(shí)產(chǎn)生的信息廷痘；
    (4) 主從復(fù)制架構(gòu)中，從服務(wù)器復(fù)制線程啟動(dòng)時(shí)產(chǎn)生的日志件已；
        log_error=/var/log/mariadb/mariadb.log|OFF
        log_warnings={ON|OFF} 

二進(jìn)制日志：binlog

用于記錄引起數(shù)據(jù)改變或存在引起數(shù)據(jù)改變的潛在可能性的語句（STATEMENT）或改變后的結(jié)果（ROW）笋额，也可能是二者混合；
功用：“重放”
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：語句篷扩；
ROW：行兄猩；
MIXED：混編；
查看二進(jìn)制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看當(dāng)前正在使用的二進(jìn)制日志文件：
SHOW MASTER STATUS鉴未；
查看二進(jìn)制 日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服務(wù)器變量：
log_bin=/PATH/TO/BIN_LOG_FILE
只讀變量；
session.sql_log_bin={ON|OFF}
控制某會(huì)話中的“寫”操作語句是否會(huì)被記錄于日志文件中铜秆；
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

常用的二進(jìn)制日志選項(xiàng)：

log_bin = {ON|OFF},也可以是一個(gè)文件路徑
log_bin_trust_function_creators 不阻止任何存儲(chǔ)函數(shù)
sql_log_bin = {ON|OFF} 當(dāng)前會(huì)話是否將修改記入到二進(jìn)制文件中
sql_log_off 是否將一般查詢記入到查詢?nèi)罩局?sync_binlog 同步緩沖區(qū)二進(jìn)制到應(yīng)到的時(shí)間淹真，0表示不急于時(shí)間同步连茧，旨在時(shí)間提交時(shí)同步
binlog_format={statement|row|mixed}
max_binlog_cache_size MariaDB二進(jìn)制日志的緩存區(qū)大小，僅用于緩存事務(wù)類的語句
max_binlog_stmt_cache_size 語句緩存區(qū)大小啸驯，即事務(wù)類和非事務(wù)類公用的大小 
max_binlog_size 二進(jìn)制日志文件的上限客扎，單位為字節(jié)
建議：切勿將二進(jìn)制日志與數(shù)據(jù)文件放在同一設(shè)備上
可以臨時(shí)通過sql_log_bin來控制未禁止的寫入

二進(jìn)制日志事件格式：

[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
事件發(fā)生的日期時(shí)間：#170916 16:24:14
事件發(fā)生的服務(wù)器id：server id 1
事件的結(jié)束位置：end_log_pos 563
事件的類型：Query
事件發(fā)生時(shí)所在服務(wù)器執(zhí)行此事件的線程的ID： thread_id=2
語句的時(shí)間戳與將其寫入二進(jìn)制日志文件中的時(shí)間差：exec_time=0
錯(cuò)誤代碼：error_code=0
設(shè)定事件發(fā)生時(shí)的時(shí)間戳：SET TIMESTAMP=1505549921/!/;
事件內(nèi)容：（這里是會(huì)有不同的執(zhí)行動(dòng)作）

中繼日志：

從服務(wù)器上記錄下來從主服務(wù)器的二進(jìn)制日志文件同步過來的事件；
對于非從服務(wù)器中繼日志沒有啟用
       relay_log_purge = {ON|OFF} 是否自動(dòng)清理不在需要的中繼日志
       relay_log_space_limit 中繼大小是否限制

事務(wù)日志：

事務(wù)型存儲(chǔ)引擎innodb用于保證事務(wù)特性的日志文件：     
    redo log 
    undo log 

清除日志：

  PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr（某個(gè)時(shí)間點(diǎn)之前） }