sql_mode 是一個(gè)容易忽視的變量，默認(rèn)情況下為空，可以忍耐一些非法操作，在生產(chǎn)環(huán)境中，必須將其設(shè)置為嚴(yán)格模式，在開發(fā)測(cè)試環(huán)境中配該變量也是很有必要的，因?yàn)檫@樣可以在生產(chǎn)之前發(fā)現(xiàn)問題。
創(chuàng)新互聯(lián)基于成都重慶香港及美國等地區(qū)分布式IDC機(jī)房數(shù)據(jù)中心構(gòu)建的電信大帶寬，聯(lián)通大帶寬，移動(dòng)大帶寬，多線BGP大帶寬租用,是為眾多客戶提供專業(yè)服務(wù)器托管報(bào)價(jià)，主機(jī)托管價(jià)格性價(jià)比高，為金融證券行業(yè)川西大數(shù)據(jù)中心，ai人工智能服務(wù)器托管提供bgp線路100M獨(dú)享，G口帶寬及機(jī)柜租用的專業(yè)成都idc公司。
sql_mode 常用值如下：
和其它數(shù)據(jù)庫相比，MySQL 有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用，主要體現(xiàn)在存儲(chǔ)引擎的架構(gòu)上，插件式的存儲(chǔ)引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取相分離，這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。
連接層
最上層是客戶端和連接服務(wù)，包含本地 socket 通信和 tcp/ip 通信，主要完成連接處理、授權(quán)認(rèn)證及相關(guān)的安全方案，該層引入了線程池，為授權(quán)用戶提供線程，還實(shí)現(xiàn)了 ssl 安全鏈接。
服務(wù)層
引擎層
存儲(chǔ)引擎層，負(fù)責(zé)了數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過 API 與存儲(chǔ)引擎進(jìn)行通信。
存儲(chǔ)層
數(shù)據(jù)存儲(chǔ)層，主要是將數(shù)據(jù)存儲(chǔ)在運(yùn)行于裸設(shè)備的文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。
開啟診斷分析工具
set profiling=1;
顯示最近的幾條查詢
show profiles;
查看 SQL 的執(zhí)行步驟
show profile cpu,block io for query 1;
重慶分公司
重慶分公司
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT 查看支持的存儲(chǔ)引擎
show engines;
查看當(dāng)前默認(rèn)的存儲(chǔ)引擎
show variables like '%storage_engine%';
InnoDB
InnoDB 是 MySQL 默認(rèn)的事務(wù)型引擎，用來處理大量的短期事務(wù)，除非有特別的原因需要用到其他存儲(chǔ)引擎，否則優(yōu)先考慮 InnoDB。
MyISAM
MyISAM 提供了大量的特性，包括全文檢索、壓縮、空間函數(shù)等，但 MyISAM 不支持事務(wù)和行級(jí)鎖，缺點(diǎn)是崩潰后無法安全恢復(fù)。
Archive
Archive 檔案存儲(chǔ)引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引；
Archive 表適合日志和數(shù)據(jù)采集類應(yīng)用；
根據(jù)英文的測(cè)試結(jié)論來看，Archive 表比 MyISAM 表要小大約 75%，比支持事務(wù)處理的 InnoDB 表小大約 83%。
Blackhole
Blackhole 引擎沒有實(shí)現(xiàn)任何存儲(chǔ)機(jī)制，它會(huì)丟棄所有插入的數(shù)據(jù)，不做任何保存。但服務(wù)器會(huì)記錄Blackhole表的日志，所以可以用于復(fù)制數(shù)據(jù)到備庫，或者簡(jiǎn)單地記錄到日志。但這種應(yīng)用方式會(huì)碰到很多問題，因此并不推薦。
CSV
CSV 引擎可以將普通的 CSV 文件作為 MySQL 表來處理，但不支持索引， CSV 可以作為一種數(shù)據(jù)交換的機(jī)制，CSV 引擎存儲(chǔ)的數(shù)據(jù)可以被文本編輯器、execl 讀取。
Memory
如果需要快速地訪問數(shù)據(jù)，并且這些數(shù)據(jù)不會(huì)被修改，重啟以后丟失也沒有關(guān)系，那么使用Memory表是非常有用，Memory 表至少比 MyISAM 表要快一個(gè)數(shù)量級(jí)。
Federated
Federated 引擎是訪問其他 MySQL 服務(wù)器的一個(gè)代理，盡管該引擎看起來提供了一種很好的跨服務(wù)器的靈活性，但也經(jīng)常帶來問題，因此默認(rèn)是禁用的。
| 對(duì)比項(xiàng) | InnoDB | MyISAM |
|---|---|---|
| 外鍵 | 支持 | 不支持 |
| 事務(wù) | 支持 | 不支持 |
| 行表鎖 | 行鎖，操作時(shí)只鎖定操作的那一行，不會(huì)對(duì)其他行產(chǎn)生影響，適合于高并發(fā) | 表鎖，即使只操作一行也會(huì)鎖定整個(gè)表，不適合高并發(fā) |
| 緩存 | 不僅緩存索引還要緩存真實(shí)數(shù)據(jù)，對(duì)內(nèi)存要求較高，而且內(nèi)存大小對(duì)性能有決定性的影響 | 只緩存索引，不緩存真實(shí)數(shù)據(jù) |
| 關(guān)注點(diǎn) | 并發(fā)寫、事務(wù)、更大資源 | 節(jié)省資源、消耗少、簡(jiǎn)單業(yè)務(wù) |
| 默認(rèn)安裝 | Y | Y |
| 默認(rèn)使用 | Y | N |
| 自帶系統(tǒng)表使用 | N | Y |
分庫分表
SQL 優(yōu)化
建立索引
調(diào)整 my.cnf 優(yōu)化服務(wù)器及配置參數(shù)
數(shù)據(jù)本身之外，數(shù)據(jù)庫還維護(hù)著一個(gè)滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引；
一般來說索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上；
雖然索引提高了查詢的效率，但是也降低了更新的效率，因?yàn)楦卤頃r(shí)，不僅要插入數(shù)據(jù)，同時(shí)還要保存一下索引文件每次更新添加了的索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息；
實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的。
.jpg)
如圖所示，磁盤塊 1 包含數(shù)據(jù)項(xiàng) 17 和 35，包含指針 P1、P2、P3
P1 表示小于 17 的磁盤塊，P2 表示介于 17 和 35 之間的磁盤塊，35 表示大于 35 的磁盤塊
查找過程
如果要查找數(shù)據(jù)項(xiàng) 29，首先將磁盤塊 1 加載到內(nèi)存，此時(shí)發(fā)生一次 IO，利用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 加載到內(nèi)存，此時(shí)發(fā)生一次 IO，利用二分查找確定 29 在26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過磁盤塊 3 的 P2 指針的磁盤地址把磁盤塊 8 加載到內(nèi)存，此時(shí)發(fā)生一次 IO，同時(shí)利用二分查找到 29，查詢結(jié)束。
B+ 樹的非葉子節(jié)點(diǎn)只是存儲(chǔ) key，占用空間非常小，因此每一層的節(jié)點(diǎn)能索引到的數(shù)據(jù)范圍更加的廣，換句話說，每次 IO 操作可以觀看更多的數(shù)據(jù)；
葉子節(jié)點(diǎn)兩兩相連，符合磁盤的預(yù)讀特性。如圖存儲(chǔ) 5、8 、9 的葉子節(jié)點(diǎn)，它有個(gè)指針指向了 10、15、18 這個(gè)葉子節(jié)點(diǎn)，那么當(dāng)我們從磁盤讀取5、8、9 對(duì)應(yīng)的數(shù)據(jù)的時(shí)候，由于磁盤的預(yù)讀特性，會(huì)順便把 10、15、18 對(duì)應(yīng)的數(shù)據(jù)讀取出來，這個(gè)時(shí)候?qū)儆陧樞蜃x取，而不是磁盤尋道了，加快了速度；
支持范圍查詢，而且部分范圍查詢非常高效，原因是數(shù)據(jù)都是存儲(chǔ)在葉子節(jié)點(diǎn)這一層，并且有指針指向其他葉子節(jié)點(diǎn)，這樣范圍查詢只需要遍歷葉子節(jié)點(diǎn)這一層，無需整棵樹遍歷。
聚簇索引并不是一種單獨(dú)的索引類型，而是一種數(shù)據(jù)存儲(chǔ)方式，聚簇表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲(chǔ)在一起；
按照聚簇索引排列順序，查詢顯示一定范圍數(shù)據(jù)的時(shí)候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫不不用從多個(gè)數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量的 IO 操作；
對(duì)于 MySQL 數(shù)據(jù)庫目前只有 InnoDB 數(shù)據(jù)引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引；
由于數(shù)據(jù)物理存儲(chǔ)排序方式只能有一種，所以每個(gè) MySQL 的表只能有一個(gè)聚簇索引，一般情況下就是該表的主鍵；
為了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主鍵列盡量選用有序的順序 ID，而不建議用無序的 ID，比如 UUID這種。
即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引
隨表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name)
);
單獨(dú)建單值索引：
CREATE INDEX idx_customer_name ON customer (customer_name);
刪除索引：
DROP INDEX idx_customer_name ON customer;索引列的值必須唯一，但可以為空
隨表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name),
UNIQUE (customer_no)
);
單獨(dú)建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no);
刪除索引：
DROP INDEX idx_customer_no ON customer;設(shè)為主鍵后自動(dòng)創(chuàng)建主鍵索引
隨表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id)
);
單獨(dú)建主鍵索引：
ALTER TABLE customer ADD PRIMARY KEY customer (customer_no);
刪除建主鍵索引：
ALTER TABLE customer DROP PRIMARY KEY;
修改建主鍵索引： 必須先刪除掉 (DROP) 原索引，再新建 (ADD) 索引一個(gè)索引包含單個(gè)列
隨表一起建索引：
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no, customer_name)
);
單獨(dú)建索引：
CREATE INDEX idx_no_name ON customer (customer_no, customer_name);
刪除索引：
DROP INDEX idx_no_name ON customer;哪些情況需要?jiǎng)?chuàng)建索引？
哪些情況不需要?jiǎng)?chuàng)建索引？
使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 查詢語句，從而知道 MySQL 是如何處理 SQL 語句的，分析查詢語句或是表結(jié)構(gòu)的性能瓶頸。
EXPLAN 的作用：
查看表的讀取順序
查看哪些索引可以被使用
數(shù)據(jù)讀取操作的操作類型
哪些索引被實(shí)際使用
表之間的引用
使用方式：
Explain + SQL
Explain SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
-> UNION
-> SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;id：
SELECT 查詢的序列號(hào),包含一組數(shù)字，表示查詢中執(zhí)行 SELECT 子句或操作表的順序
id 相同：執(zhí)行順序由上至下
id 不同：如果是子查詢，id 的序號(hào)會(huì)遞增，id 值越大優(yōu)先級(jí)越高，越先被執(zhí)行
每個(gè) id 表示一趟獨(dú)立的查詢，一個(gè) SQL 的查詢趟數(shù)越少越好
select_type
查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢
SIMPLE：最簡(jiǎn)單的查詢，不包含 UNION 和子查詢
PRIMARY：查詢中若包含復(fù)雜的子部分，最外層查詢被標(biāo)記為 PRIMARY
DERIVED：在 FROM 列表中包含的子查詢被標(biāo)記為 DERIVED，MySQL 會(huì)遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時(shí)表里
SUBQUERY：在 SELECT 或 WHERE 列表中包含子查詢
DEPENDENT SUB：在 SELECT 或 WHERE 列表中包含子查詢，子查詢基于外層
UNCACHEABLE SUBQUREY：結(jié)果集不能被緩存的子查詢，必須重新為外層查詢的每一行進(jìn)行評(píng)估
UNION：若第二個(gè) SELECT 出現(xiàn)在 UNION 之后，則被標(biāo)記為 UNION
table
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type
顯示連接使用的類型，按最優(yōu)到最差的類型排序
system：表只有一行記錄
const：表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快
如將主鍵置于 where 列表中，MySQL 就能將該查詢轉(zhuǎn)換為一個(gè)常量
eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描
ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體
range：只檢索給定范圍的行，使用一個(gè)索引來選擇行，key 列顯示使用了哪個(gè)索引，一般就是在 where 語句中出現(xiàn)了 between、<、>、in 等的查詢，這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束語另一點(diǎn)，不用掃描全部索引
index：出現(xiàn) index 是 SQL 使用了索引但是沒用通過索引進(jìn)行過濾，一般是使用了覆蓋索引或者是利用索引進(jìn)行了排序分組
all：Full Table Scan，將遍歷全表以找到匹配的行
index_merge：在查詢過程中需要多個(gè)索引組合使用，通常出現(xiàn)在有 or 的關(guān)鍵字的 SQL 中
ref_or_null：對(duì)于某個(gè)字段既需要關(guān)聯(lián)條件，也需要 null 值的情況下，查詢優(yōu)化器會(huì)選擇用 ref_or_null 連接查詢
index_subquery：利用索引來關(guān)聯(lián)子查詢，不再全表掃描
一般來說，得保證查詢至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref
possible_keys
顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用
key
實(shí)際使用的索引，如果為NULL，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的 select 字段重疊
key_len
表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度， key_len 字段能夠檢查是否充分的利用上了索引
ref
顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)，哪些列或常量被用于查找索引列上的值
rows
rows 列顯示 MySQL 認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)
Extra
包含不適合在其他列中顯示但十分重要的額外信息
index(a,b,c)
| Where語句 | 索引是否被使用 |
|---|---|
| WHERE a = 3 | y，使用到 a |
| WHERE a = 3 AND b = 5 | y，使用到 a、b |
| WHERE a = 3 AND b = 5 AND c = 4 | y，使用到 a、b、c |
| WHERE b = 3、WHERE b = 3 AND c = 4、WHERE c = 4 | n |
| WHERE a = 3 AND c = 5 | y，使用到 a，b中斷了 |
| WHERE a = 3 AND b > 4 AND c = 5 | y，使用到 a，b 中斷了 |
| WHERE a IS NULL AND b IS NOT NULL | is null 支持索引 但是is not null 不支持，所以 a 可以使用索引，b 不可以使用索引 |
| WHERE a <> 3 | <> 不能使用索引 |
| WHERE abs(a) = 3 | abs 不能使用索引 |
| WHERE a = 3 AND b LIKE 'kk%' AND c = 4 | y，使用到 a、b、c |
| WHERE a = 3 AND b LIKE '%kk' AND c = 4 | y，使用到 a |
| WHERE a = 3 AND b LIKE '%kk%' AND c = 4 | y，使用到 a |
| WHERE a = 3 AND b LIKE 'k%kk%' AND c = 4 | y，使用到 a、b、c |
創(chuàng)建索引的建議：
對(duì)于單值索引，盡量選擇針對(duì)當(dāng)前查詢過濾性更高的字段
選擇組合索引，當(dāng)前查詢過濾性最高的字段在索引的位置越靠前越好
選擇組合索引，盡量選擇可以能夠包含當(dāng)前查詢中的 where 字句中更多字段的索引
在選擇組合索引的時(shí)候，如果某個(gè)字段可能出現(xiàn)范圍查詢時(shí)，盡量把這個(gè)字段放在索引次序的最后面
保證被驅(qū)動(dòng)表的 join 字段已經(jīng)被索引
left join 時(shí)，選擇小表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表
inner join 時(shí)，MySQL 會(huì)自己把小結(jié)果集的表選為驅(qū)動(dòng)表
子查詢盡量不要放在被驅(qū)動(dòng)表，有可能使用不到索引
能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢
盡量不要使用not in 或者 not exists，用 left join on xxx is null 替代
ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序
如果不在索引列上，filesort 有兩種算法：
雙路排序
單路排序：
group by 使用索引的原則幾乎跟 order by 一致 ，唯一區(qū)別是 group by 即使沒有過濾條件用到索引，也可以直接使用索引
什么是慢查詢?nèi)罩荆?/p>
慢查詢?nèi)罩臼?MySQL 提供的一種日志記錄，它用來記錄在 MySQL 中響應(yīng)時(shí)間超過閾值的語句，具體指運(yùn)行時(shí)間超過long_query_time 值的 SQL，則會(huì)被記錄到慢查詢?nèi)罩局校?/p>
long_query_time 的默認(rèn)值為10，意思是運(yùn)行10秒以上的語句。
默認(rèn)慢查詢?nèi)罩臼顷P(guān)閉的，需要手動(dòng)開啟
查看慢查詢?nèi)罩臼欠耖_啟
SHOW VARIABLES LIKE '%slow_query_log%';
開啟慢查詢?nèi)罩?set global slow_query_log=1;查看并配置 long_query_time
查看long_query_time
SHOW VARIABLES LIKE 'long_query_time%';
set long_query_time=1日志分析工具 mysqldumpslow
.jpg)
常用參考：
hadoop100得到返回記錄集最多的10個(gè)SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log
得到訪問次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop100-slow.log
得到按照時(shí)間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop100-slow.log
另外建議在使用這些命令時(shí)結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log | more
復(fù)制的基本原理
master 將改變記錄到二進(jìn)制日志（binary log），這些記錄過程叫做二進(jìn)制日志事件，binary log events；
slave 將 master 的 binary log events 拷貝到它的中繼日志（relay log）；
slave 重做中繼日志中的事件，將改變應(yīng)用到自己的數(shù)據(jù)庫中，MySQL 復(fù)制是異步的且串行化的。
復(fù)制的基本原則
每個(gè) slave 只有一個(gè) master
每個(gè) slave 只能有一個(gè)唯一的服務(wù)器 ID
每個(gè) master 可以有多個(gè)salve
1、配置主數(shù)據(jù)庫
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog_format=mixed為從服務(wù)分配賬號(hào)
查看主服務(wù)器 BIN 日志的信息
show master status;重啟主數(shù)據(jù)庫
systemctl restart mariadb2、配置從數(shù)據(jù)庫
連接主數(shù)據(jù)庫
CHANGE MASTER TO
-> MASTER_HOST="192.168.10.100",
-> MASTER_USER="slave",
-> MASTER_PASSWORD="123456",
-> MASTER_LOG_FILE="mysql-bin.000001",
-> MASTER_LOG_POS=388;啟動(dòng)從數(shù)據(jù)庫
start slave;
