1、MariaDB or MySQL：簡介
（DBMS：Database Managerment System，數(shù)據(jù)管理系統(tǒng)；
RDBMS即關(guān)系數(shù)據(jù)庫管理系統(tǒng)(Relational Database Management System)，是將數(shù)據(jù)組織為相關(guān)的行和列的系統(tǒng)，而管理關(guān)系數(shù)據(jù)庫的計(jì)算機(jī)軟件就是關(guān)系數(shù)據(jù)庫管理系統(tǒng)，常用的數(shù)據(jù)庫軟件有Oracle、SQL Server等）
數(shù)據(jù)庫排行
Relational DBMS：RDBMS（以類解決方案）
Oracle
MySQL --> MariaDB
NoSQL：
Document store
Key-value store （eg：Redis）
Wide column store
Graph DBMS
Search engine：倒排索引
Solr
ElasticSearch （ELS 搜索引擎）
Time Series DBMS
InfluxDB
層次模型 --> 網(wǎng)狀模型 --> (Codd) 關(guān)系模型
DBMS --> RDBMS
RDBMS：
范式：第一范式、第二范式、第三范式；
表：row, column
關(guān)系運(yùn)算：
選擇
投影
2、數(shù)據(jù)庫：
表、索引、視圖（虛表）、SQL、存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器、事件調(diào)度器；
DDL（數(shù)據(jù)定義語言）：CREATE，ALTER，DROP
DML（數(shù)據(jù)操控語言）：INSERT/UPDATE/DELETE/SELECT
約束：
主鍵約束：惟一、非空；一張表只能有一個(gè)；
惟一鍵約束：惟一，可以存在多個(gè)；
外鍵約束：參考性約束；
檢查性約束：check；
三層模型：
物理層 --> SA
邏輯層 --> DBA
視圖層 --> Coder
實(shí)現(xiàn)：
Oracle, DB2, Sybase, Infomix, SQL Server；
MySQL, MariaDB, PostgreSQL開源, SQLite；
3、MySQL：
5.1 --> 5.5 --> 5.6 --> 5.7 --> 8.0
MariaDB：5.5.x --> 10.x
特性：
插件式存儲(chǔ)引
單進(jìn)程多線程
安裝MySQL
OS Vendor：rpm
MySQL：
source code：cmak
binary package：
i686, x86_64;
glibc VERSION
prepackage：rpm, deb
os, arch,
clipboard1.png
skip_name_resolve 跳過名稱解析
innodb_file_per_table 是否使用單獨(dú)的表空間
max_connection 最大連接數(shù)
服務(wù)端程序：
mysqld, mysqld_safe, mysqld_multi
客戶端程序：
mysql, mysqldump, mysqlbinlog, mysqladmin, ...
非客戶端類管理程序：
myiamchk, myisampack, ...
mysqld-5.7
創(chuàng)建數(shù)據(jù)目錄，屬主屬組屬于運(yùn)行者用戶身份mysql；
初始化：
mysqld --initilize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
查配置文件路徑，修改配置文件：/usr/local/mysql/etc/my.cnf
datadir
socket
includedir
pid
準(zhǔn)備錯(cuò)誤日志文件：
復(fù)制啟動(dòng)腳本：/usr/local/mysql/support-files/mysql.server --> /etc/init.d/mysqld
配置文件：
讀取多處的多個(gè)配置文件，而且會(huì)以指定的次序的進(jìn)行；
# my_print_defaults
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
不同的配置文件中出現(xiàn)同一參數(shù)且擁有不同值時(shí)，后讀取將為最終生效值；
修改默認(rèn)讀取的配置文件（mysqld_safe命令）：
--defaults-file=file_name
于讀取的默認(rèn)配置文件之外再加載一個(gè)文件：
--defaults-extra-file=path
配置文件格式：
ini風(fēng)格的配置文件，能夠?yàn)閙ysql的各種應(yīng)用程序提供配置信息：
[mysqld]
[mysqld_safe]
[mysqld_multi]
[server]
[mysql]
[mysqldump]
[client]
...
PARAMETER = VALUE
PARAMETER：
innodb_file_per_table
innodb-file-per-table
程序文件：
服務(wù)端程序：mysqld_safe, mysqld_multi
客戶端程序：mysql, mysqldump, mysqladmin
工具程序：myisampack, ...
mysql --> mysql protocol --> mysqld
mysql：交互式CLI工具；
mysql [options] db_name
常用選項(xiàng)：
--host=host_name, -h host_name：服務(wù)端地址；
--user=user_name, -u user_name：用戶名；
--password[=password], -p[password]：用戶密碼；
--port=port_num, -P port_num：服務(wù)端端口；
--protocol={TCP|SOCKET|PIPE|MEMORY}：
本地通信：基于本地回環(huán)地址進(jìn)行請求，將基于本地通信協(xié)議；
Linux：SOCKET
Windows：PIPE，MEMORY
非本地通信：使用非本地回環(huán)地址進(jìn)行的請求； TCP協(xié)議；
--socket=path, -S path
--database=db_name, -D db_name：
--compress, -C：數(shù)據(jù)壓縮傳輸
--execute=statement, -e statement：非交互模式執(zhí)行SQL語句
--vertical, -E：查詢結(jié)果縱向顯示；
命令：
客戶端命令：于客戶端執(zhí)行；
服務(wù)端命令：SQL語句，需要一次性完整地發(fā)往服務(wù)端；語句必須有結(jié)束符；
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
mysql命令的使用幫助：
# man mysql
# mysql --help --verbose
sql腳本運(yùn)行：
mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT
mysqld服務(wù)器程序：工作特性的定義方式
命令行選項(xiàng)
配置文件參數(shù)
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
服務(wù)器參數(shù)/變量：設(shè)定MySQL的運(yùn)行特性；
mysql> SHOW GLOBAL|[SESSION] VARIABLES [LIKE clause];
狀態(tài)（統(tǒng)計(jì)）參數(shù)/變量：保存MySQL運(yùn)行中的統(tǒng)計(jì)數(shù)據(jù)或狀態(tài)數(shù)據(jù)；
mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];
顯示單個(gè)變量設(shè)定值的方法：
mysql> SELECT @@[global.|session.]system_var_name
%：匹配任意長度的任意字符；
_：匹配任意單個(gè)字符；
變量/參數(shù)級別：
全局：為所有會(huì)話設(shè)定默認(rèn)；
會(huì)話：跟單個(gè)會(huì)話相關(guān)；會(huì)話建立會(huì)從全局繼承；
服務(wù)器變量的調(diào)整方式：
運(yùn)行時(shí)修改：
global：僅對修改后新建立的會(huì)話有效；
session：僅對當(dāng)前會(huì)話有效，且立即生效；
啟動(dòng)前通過配置文件修改：
重啟后生效；
運(yùn)行時(shí)修改變量值操作方法：
mysql> HELP SET
SET [GLOBAL | SESSION] system_var_name = expr
SET [@@global. | @@session. | @@]system_var_name = expr
注：GLOBAL值的修改要求用戶擁有管理權(quán)限；
安裝完成后的安全初始化：
mysql_secure_installation
運(yùn)行前常修改的參數(shù)：
innodb_file_per_table=ON
skip_name_resolve=ON
...
SQL：ANSI SQL
SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ...
MySQL的數(shù)據(jù)類型：
字符型
數(shù)值型
日期時(shí)間型
內(nèi)建類型
字符型：
CHAR(#)， BINARY(#)：定長型；CHAR不區(qū)分字符大小寫，而BINARY區(qū)分；
VARCHAR(#)， VARBINARY(#)：變長型
TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
數(shù)值型：
浮點(diǎn)型：近似
FLOAT
DOUBLE
REAL
BIT
整型：精確
INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
DECIMAL
日期時(shí)間型：
日期：DATE
時(shí)間：TIME
日期j時(shí)間：DATETIME
時(shí)間戳：TIMESTAMP
年份：YEAR(2), YEAR(4)
內(nèi)建：
ENUM：枚舉
ENUM('Sun','Mon','Tue','Wed')
SET：集合
類型修飾符：
字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED
日期時(shí)間型：NOT NULL， NULL， DEFAULT
SQL MODE：定義mysqld對約束等違反時(shí)的響應(yīng)行為等設(shè)定；
常用的MODE：
TRADITIONAL
STRICT_TRANS_TABLES
STRICT_ALL_TABLES
修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
SQL：DDL，DML
DDL：（Data Control Language）數(shù)據(jù)控制語言
mysql> HELP Data Definition
CREATE, ALTER, DROP
DATABASE, TABLE
INDEX, VIEW, USER
FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER, SERVER
DML:Data Manipulation Language，數(shù)據(jù)操縱語言
mysql> HELP Data Manipulation
INSERT/REPLACE, DELETE, SELECT, UPDATE
數(shù)據(jù)庫：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
4、表：
CREATE
(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE TABLE [IF NOT EXISTS] tble_name (col_name data_typ|INDEX|CONSTRAINT);
table_options：
ENGINE [=] engine_name
查看支持的所有存儲(chǔ)引擎：
mysql> SHOW ENGINES;
查看指定表的存儲(chǔ)引擎：
mysql> SHOW TABLE STATUS LIKE clause;
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
(2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
直接創(chuàng)建表，并將查詢語句的結(jié)果插入到新創(chuàng)建的表中；
(3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
復(fù)制某存在的表的結(jié)構(gòu)來創(chuàng)建新的空表；
DROP:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name];
ALTER：
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
可修改內(nèi)容：
(1) table_options
(2) 添加定義：ADD
字段、字段集合、索引、約束
(3) 修改字段：
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
(4) 刪除操作：DROP
字段、索引、約束
表重命名：
RENAME [TO|AS] new_tbl_name
查看表結(jié)構(gòu)定義：
DESC tbl_name；
查看表定義：
SHOW CREATE TABLE tbl_name
查看表屬性信息：
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
5、索引：
創(chuàng)建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
刪除：
DROP INDEX index_name ON tbl_name
索引類型：
聚集索引、非聚集索引：索引是否與數(shù)據(jù)存在一起；
主鍵索引、輔助索引
稠密索引、稀疏索引：是否索引了每一個(gè)數(shù)據(jù)項(xiàng)；
BTREE（B+）、HASH、R Tree、FULLTEXT
BTREE：左前綴；
EXPLAIN：分析查詢語句的執(zhí)行路徑；
6、視圖：VIEW
虛表：存儲(chǔ)下來的SELECT語句；
創(chuàng)建：
CREATE VIEW view_name [(column_list)] AS select_statement
修改：
ALTER VIEW view_name [(column_list)] AS select_statement
刪除：
DROP VIEW [IF EXISTS] view_name [, view_name] ...
7、DML：
INSERT/REPLACE，DELETE，UPDATE，SELECT
INSERT：
單行插入
批量插入
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
DELETE：
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
注意：一定要有限制條件，否則將清空整個(gè)表；
限制條件：
[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制條件，否則將修改整個(gè)表中指定字段的數(shù)據(jù)；
限制條件：
[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]
注意：sql_safe_updates變量可阻止不帶條件更新操作；
SELECT：
Query Cache：緩存查詢的執(zhí)行結(jié)果；
key：查詢語句的hash值；
value：查詢語句的執(zhí)行結(jié)果；
SQL語句的編寫方式：
SELECT name FROM tbl2;
select name from tbl2;
查詢執(zhí)行路徑：
請求-->查詢緩存
請求-->查詢緩存-->解析器-->預(yù)處理器-->優(yōu)化器-->查詢執(zhí)行引擎-->存儲(chǔ)引擎-->緩存-->響應(yīng)
SELECT語句的執(zhí)行流程：
FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit 8、單表查詢：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
用法：
SELECT col1, col2, ... FROM tble_name； 極其危險(xiǎn)，慎用；
SELECT col1, col2, ... FROM tble_name WHERE clause；
SELECT col1, col2, ... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]；
DISTINCT：數(shù)據(jù)去重；
SQL_CACHE：顯式指定緩存查詢語句的結(jié)果；
SQL_NO_CACHE：顯式指定不緩存查詢語句的結(jié)果；
query_cache_type服務(wù)器變量有三個(gè)值：
ON：啟用；
SQL_NO_CACHE：不緩存；默認(rèn)符合緩存條件都緩存；
OFF：關(guān)閉；
DEMAND：按需緩存；
SQL_CACHE：緩存；默認(rèn)不緩存；
字段可以使用別名 ：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明過濾條件以實(shí)現(xiàn)“選擇”功能；
過濾條件：布爾型表達(dá)式；
[WHERE where_condition]
算術(shù)操作符：+, -, *, /, %
比較操作符：=, <>, !=, <=>, >, >=, <, <=
IS NULL， IS NOT NULL
區(qū)間：BETWEEN min AND max
IN：列表；
LIKE：模糊比較，%和_；
RLIKE或REGEXP
邏輯操作符：
AND， OR， NOT
GROUP BY：根據(jù)指定的字段把查詢的結(jié)果進(jìn)行“分組”以用于“聚合”運(yùn)算；
avg(), max(), min(), sum(), count()
HAVING：對分組聚合后的結(jié)果進(jìn)行條件過濾；
ORDER BY：根據(jù)指定的字段把查詢的結(jié)果進(jìn)行排序；
升序：ASC
降序：DESC
LIMIT：對輸出結(jié)果進(jìn)行數(shù)量限制
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
9、多表查詢：
連接操作：
交叉連接：笛卡爾乘積；
連接：
等值連接：讓表之間的字段以等值的方式建立連接；
不等值連接：
自然連接
自連接
外連接：
左外連接：
FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col
右外連接：
FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col
子查詢：在查詢中嵌套查詢；
用于WHERE子句中的子查詢；
(1) 用于比較表達(dá)式中的子查詢：子查詢僅能返回單個(gè)值；
(2) 用于IN中的子查詢：子查詢可以返回一個(gè)列表值；
(3) 用于EXISTS中的子查詢：
用于FROM子句中的子查詢；
SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;
聯(lián)合查詢：將多個(gè)查詢語句的執(zhí)行結(jié)果相合并；
UNION
SELECT clause UNION SELECT cluase；
MySQL --> MariaDB --> Percona-Server
InnoDB --> XtraDB
10、 存儲(chǔ)引擎：
表類型：也稱為“表類型”，表級別概念，不建議在同一個(gè)庫中的表上使用不同的ENGINE；
CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...
SHOW TABLE STATUS
常見的存儲(chǔ)引擎：SHOW ENGINES;
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
clipboard2.png
clipboard3.png
clipboard4.png
InnoDB：InnoBase
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
數(shù)據(jù)存儲(chǔ)于“表空間(table space)"中：
(1) 所有數(shù)據(jù)庫中的所有類型為InnoDB的表的數(shù)據(jù)和索引存儲(chǔ)于同一個(gè)表空間中；
空間文件：datadir定義的目錄中
文件：ibdata1, ibdata2, ...
(2) innodb_file_per_table=ON，意味著每表使用單獨(dú)的表空間文件；
每表的數(shù)據(jù)文件（數(shù)據(jù)和索引，存儲(chǔ)于數(shù)據(jù)庫目錄）存儲(chǔ)于自己專用的表
空間文件中，并存儲(chǔ)于數(shù)據(jù)庫目錄下: tbl_name.ibd
表結(jié)構(gòu)的定義：在數(shù)據(jù)庫目錄，tbl_name.frm
事務(wù)型存儲(chǔ)引擎，適合對事務(wù)要求較高的場景中；但較適用于處理大量短期事務(wù)；
基于MVCC（Mutli Version Concurrency Control）支持高并發(fā)；支持四個(gè)隔離級 別，默認(rèn)級別為REPEATABLE-READ；間隙鎖以防止幻讀；
使用聚集索引（主鍵索引）；
支持”自適應(yīng)Hash索引“；
鎖粒度：行級鎖；間隙鎖；
總結(jié)：
數(shù)據(jù)存儲(chǔ)：表空間；
并發(fā)：MVCC，間隙鎖，行級鎖；
索引：聚集索引、輔助索引；
性能：預(yù)讀操作、內(nèi)存數(shù)據(jù)緩沖、內(nèi)存索引緩存、
自適應(yīng)Hash索引、插入操作緩存區(qū)；
備份：支持熱備；
SHOW ENGINE INNODB STATUS;
MyISAM：
支持全文索引（FULLTEXT index）、壓縮、空間函數(shù)（GIS）；
不支持事務(wù)
鎖粒度：表級鎖
崩潰無法保證表安全恢復(fù)
適用場景：只讀或讀多寫少的場景、較小的表（以保證崩潰后恢復(fù)的時(shí)間較短）；
文件：每個(gè)表有三個(gè)文件，存儲(chǔ)于數(shù)據(jù)庫目錄中
tbl_name.frm：表格式定義；
tbl_name.MYD：數(shù)據(jù)文件；
tbl_name.MYI：索引文件；
特性：
加鎖和并發(fā)：表級鎖；
修復(fù)：手動(dòng)或自動(dòng)修復(fù)、但可能會(huì)丟失數(shù)據(jù)；
索引：非聚集索引；
延遲索引更新；
表壓縮；
行格式：
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
其它的存儲(chǔ)引擎：
CSV：將CSV文件（以逗號分隔字段的文本文件）作為MySQL表文件；
MRG_MYISAM：將多個(gè)MyISAM表合并成的虛擬表；
BLACKHOLE：類似于/dev/null，不真正存儲(chǔ)數(shù)據(jù)；
MEMORY：內(nèi)存存儲(chǔ)引擎，支持hash索引，表級鎖，常用于臨時(shí)表；
FEDERATED: 用于訪問其它遠(yuǎn)程MySQL服務(wù)器上表的存儲(chǔ)引擎接口；
MariaDB額外支持很多種存儲(chǔ)引擎：
OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...
搜索引擎：
lucene, sphinx
lucene：Solr, ElasticSearch
11、并發(fā)控制：
鎖：Lock （語句執(zhí)行是會(huì)自動(dòng)加鎖）
鎖類型 ：
讀鎖：共享鎖，可被多個(gè)讀操作共享；
寫鎖：排它鎖，獨(dú)占鎖；
鎖粒度：
表鎖：在表級別施加鎖，并發(fā)性較低；
行鎖：在行級別施加鎖，并發(fā)性較高；維持鎖狀態(tài)的成本較大；
鎖策略：在鎖粒度及數(shù)據(jù)安全性之間尋求一種平衡機(jī)制；
存儲(chǔ)引擎：級別以及何時(shí)施加或釋放鎖由存儲(chǔ)引擎自行決定；
MySQL Server：表級別，可自行決定，也允許顯式請求；
鎖類別：
顯式鎖：用戶手動(dòng)請求的鎖；
隱式鎖：存儲(chǔ)引擎自行根據(jù)需要施加的鎖；
顯式鎖的使用：
(1) LOCK TABLES
LOCK TABLES tbl_name read|write, tbl_name read|write, ...
UNLOCK TABLES
(2) FLUSH TABLES（刷寫，將內(nèi)存數(shù)據(jù)同步到磁盤文件上）
FLUSH TABLES tbl_name,... [WITH READ LOCK];
UNLOCK TABLES;
(3) SELECT cluase
[FOR UPDATE | LOCK IN SHARE MODE]
clipboard5.png
事務(wù)：
事務(wù)：一組原子性的SQL查詢、或者是一個(gè)或多個(gè)SQL語句組成的獨(dú)立工作單元；
事務(wù)日志：
innodb_log_files_in_group
innodb_log_group_home_dir
innodb_log_file_size
innodb_mirrored_log_groups
ACID測試：
A：AUTOMICITY，原子性；整個(gè)事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾；
C：CONSISTENCY，一致性；數(shù)據(jù)庫總是應(yīng)該從一個(gè)一致性狀態(tài)轉(zhuǎn)為另一個(gè)一致性狀態(tài)；
I：ISOLATION，隔離性；一個(gè)事務(wù)所做出的操作在提交之前，是否能為其它事務(wù)可見；出于保證并發(fā)操作之目的，隔離有多種級別；
D：DURABILITY，持久性；事務(wù)一旦提交，其所做出的修改會(huì)永久保存；
自動(dòng)提交：單語句事務(wù)
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0；
手動(dòng)控制事務(wù)：
啟動(dòng)：START TRANSACTION
提交：COMMIT
回滾：ROLLBACK
事務(wù)支持savepoints（保存點(diǎn)）：類似于快照
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
clipboard6.png
clipboard7.png
事務(wù)隔離級別：
READ-UNCOMMITTED：讀未提交 --> 臟讀；
READ-COMMITTED：讀提交--> 不可重復(fù)讀；
REPEATABLE-READ：可重復(fù)讀 --> 幻讀；
SERIALIZABLE：串行化；
mysql> SELECT @@session.tx_isolation;
+----------------------------------+
| @@session.tx_isolation |
+----------------------------------+
| REPEATABLE-READ |
+----------------------------------+
查看InnoDB存儲(chǔ)引擎的狀態(tài)信息：
SHOW ENGINE innodb STATUS;
MySQL用戶和權(quán)限管理
用戶賬號：user@host
user：賬戶名稱；
host：此賬戶可通過哪些客戶端主機(jī)請求創(chuàng)建連接線程；
%：任意長度的任意字符；
_：任意單個(gè)字符；
skip_name_resolve=ON
MySQL權(quán)限類別：
庫級別：
表級別：
字段級別：
管理類：
程序類：
管理類：
CREATE USER
RELOAD
LOCK TABLES
REPLICATION CLIENT， REPLICATION SLAVE
SHUTDOWN
FILE
SHOW DATABASES
PROCESS
SUPER
程序類：
FUNCTION，PROCEDURE，TRIGGER
操作：
CREATE，ALTER，DROP，EXECUTE
庫和表級別：
CREATE，ALTER，DROP
INDEX
CREATE VIEW
SHOW VIEW
GRANT：能夠把自己獲得的權(quán)限生成一個(gè)副本轉(zhuǎn)贈(zèng)給其它用戶；
OPTION
數(shù)據(jù)操作：
表：
INSERT/DELETE/UPDATE/SELECT
字段：
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有權(quán)限：ALL， ALL PRIVILEGES
元數(shù)據(jù)數(shù)據(jù)庫（數(shù)據(jù)字典）：mysql
授權(quán)：
db, host, user
tables_priv, column_priv, procs_priv, proxies_priv
MySQL的索引：
MySQL用戶管理：
'user'@'host';
host:
IP
主機(jī)名
NETWORK
%, _
創(chuàng)建用戶：
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
重命名：RENAME USER
刪除用戶：
DROP USER 'user'@'host' [, 'user'@'host'] ...
讓MySQL重新加載授權(quán)表：
FLUSH PRIVILEGES
修改用戶密碼：
(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password');
(2) UPDATE mysql.user SET Password=PASSWORD('cleartext password')
WHERE User='USERNAME' AND Host='HOST';
(3) mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'
生效：FLUSH PRIVILEGES
忘記管理員密碼的解決辦法：
(1) 啟動(dòng)mysqld進(jìn)程時(shí)，使用--skip-grant-tables和--skip-networking選項(xiàng)；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通過UPDATE命令修改管理員密碼；
(3) 以正常 方式啟動(dòng)mysqld進(jìn)程；
授權(quán)：GRANT
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
```
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
查看授權(quán)：SHOW GRANTS
SHOW GRANTS [FOR 'user'@'host']
取消授權(quán)：REVOKE
REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM 'user'@'host' [, 'user'@'host'] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
MySQL的索引：
索引：提取索引的創(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è)字符，要通過索引選擇性來評估
索引選擇性：不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值；
(3) 多列索引：
AND連接的多個(gè)查詢條件更適合使用多列索引，而非多個(gè)單鍵索引；
(4) 選擇合適的索引列次序：選擇性最高的放左側(cè)；
EXPLAIN來分析索引有效性：
EXPLAIN [explain_type] SELECT select_options
explain_type:
EXTENDED
| PARTITIONS
輸出結(jié)果：
id: 1
select_type: SIMPLE
table: students
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
id：當(dāng)前查詢語句中，第個(gè)SELECT語句的編號；
復(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è)外部索引排序；
查詢緩存：
緩存：k/v
key：查詢語句的hash值
value：查詢語句的執(zhí)行結(jié)果
如何判斷緩存是否命中：
通過查詢語句的哈希值判斷：哈希值考慮的因素包括
查詢本身、要查詢數(shù)據(jù)庫、客戶端使用的協(xié)議版本、...
SELECT Name FROM students WHERE StuID=3;
Select Name From students where StuID=3;
哪些查詢可能不會(huì)被緩存？
查詢語句中包含UDF
存儲(chǔ)函數(shù)
用戶自定義變量
臨時(shí)表
mysql系統(tǒng)表或者是包含列級別權(quán)限的查詢
有著不確定結(jié)果值的函數(shù)(now())；
查詢緩存相關(guān)的服務(wù)器變量：
query_cache_limit：能夠緩存的最大查詢結(jié)果；（單語句結(jié)果集大小上限）
有著較大結(jié)果集的語句，顯式使用SQL_NO_CACHE，
以避免先緩存再移出；
query_cache_min_res_unit：內(nèi)存塊的最小分配單位；緩存過小的查詢結(jié)果
集會(huì)浪費(fèi)內(nèi)存空間；
較小的值會(huì)減少空間浪費(fèi)，但會(huì)導(dǎo)致更頻繁地內(nèi)存分配及回收操作；
較大值的會(huì)帶來空間浪費(fèi)；
query_cache_size：查詢緩存空間的總共可用的大小；單位是字節(jié)，必須是
1024的整數(shù)倍；
query_cache_strip_comments
query_cache_type：緩存功能啟用與否；
ON：啟用；
OFF：禁用；
DEMAND：按需緩存，僅緩存SELECT語句中帶SQL_CACHE的查詢結(jié)果；
query_cache_wlock_invalidate：如果某表被其它連接鎖定，是否仍然可以
從查詢緩存中返回查詢結(jié)果；默認(rèn)為OFF，表示可以；
ON則表示不可以；
狀態(tài)變量：
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759688 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
命中率：
Qcache_hits/Com_select
##日志：
> 查詢?nèi)罩荆篻eneral_log
慢查詢?nèi)罩荆簂og_slow_queries
錯(cuò)誤日志：log_error， log_warnings
二進(jìn)制日志：binlog
中繼日志：relay_log
事務(wù)日志：innodb_log
1、查詢?nèi)罩? 記錄查詢語句，日志存儲(chǔ)位置：
文件：file
表：table (mysql.general_log)
general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}
2、慢查詢?nèi)罩? 慢查詢：運(yùn)行時(shí)間超出指定時(shí)長的查詢；
long_query_time
存儲(chǔ)位置：
文件：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
3、錯(cuò)誤日志
記錄如下幾類信息：
(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}
4、二進(jìn)制日志
用于記錄引起數(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)制日志事件格式：
# at 553
#160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;
事件的起始位置：# at 553
事件發(fā)生的日期時(shí)間：#160831 9:56:08
事件發(fā)生的服務(wù)器id：server id 1
事件的結(jié)束位置：end_log_pos 624
事件的類型：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=1472608568/*!*/;
事件內(nèi)容：BEGIN
中繼日志：
從服務(wù)器上記錄下來從主服務(wù)器的二進(jìn)制日志文件同步過來的事件；
事務(wù)日志：
事務(wù)型存儲(chǔ)引擎innodb用于保證事務(wù)特性的日志文件：
redo log
undo log
