溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“MySQL中SQL優(yōu)化、索引優(yōu)化、鎖機(jī)制、主從復(fù)制的方法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL中SQL優(yōu)化、索引優(yōu)化、鎖機(jī)制、主從復(fù)制的方法”吧！
myisam存儲：如果表對事務(wù)要求不高，同時是以查詢和添加為主的，我們考慮使用myisam存儲引擎，比如bbs 中的發(fā)帖表，回復(fù)表
需要定時進(jìn)行碎片整理（因?yàn)閯h除的數(shù)據(jù)還是存在）：optimize table table_name;
InnoDB存儲：對事務(wù)要求高，保存的數(shù)據(jù)都是重要數(shù)據(jù)，我們建議使用INN0DB,比如訂單表，賬號表.
面試問MyISAM和INNODB的區(qū)別：
1.事務(wù)安全
2.查詢和添加速度
3.支持全文索引
4.鎖機(jī)制
5.外鍵MyISAM不支持外鍵，INNODB 支持外鍵.
Mermory存儲：比如我們數(shù)據(jù)變化頻繁，不需要入庫，同時又頻繁的查詢和修改，我們考慮使用memory
查看mysql以提供什么存儲引擎：show engines;
查看mysql當(dāng)前默認(rèn)的存儲引擎：show variables like '%storage_engine%';
SQL性能下降原因：
1、查詢語句寫的爛
2、索引失效（數(shù)據(jù)變更）
3、關(guān)聯(lián)查詢太多join（設(shè)計(jì)缺陷或不得已的需求）
4、服務(wù)器調(diào)優(yōu)及各個參數(shù)設(shè)置（緩沖、線程數(shù)等）
通常SQL調(diào)優(yōu)過程：
觀察，至少跑1天，看看生產(chǎn)的慢SQL情況。
開啟慢查詢?nèi)罩?，設(shè)置闕值，比如超過5秒鐘的就是慢SQL，并將它抓取出來。
explain + 慢SQL分析。
show profile。
運(yùn)維經(jīng)理 or DBA，進(jìn)行SQL數(shù)據(jù)庫服務(wù)器的參數(shù)調(diào)優(yōu)。
總結(jié)：
1、慢查詢的開啟并捕獲
2、explain + 慢SQL分析
3、show profile查詢SQL在Mysql服務(wù)器里面的執(zhí)行細(xì)節(jié)和生命周期情況
4、SQL數(shù)據(jù)庫服務(wù)器的參數(shù)調(diào)優(yōu)
手寫順序：
SELECT DISTINCT <select_list>FROM <left_table> <join_type>JOIN <right_table> on <join_codition> //join_codition：比如員工的部門ID和部門表的主鍵id相同WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>ORDER BY <order_by_condition>LIMIT <limit_number>
MySQL機(jī)讀順序：
1 FROM <left_table> 2 ON <join_condition> 3 <join_type> JOIN <right_table> 4 WHERE <where_condition> 5 GROUP BY <group_by_list> 6 HAVING <having_condition> 7 SELECT 8 DISTINCT <select_list> 9 ORDER BY <order_by_condition> 10 LIMIT <limit_number>
總結(jié)：
運(yùn)行順序一上一下
創(chuàng)建表插入數(shù)據(jù)（左右主外鍵相連）：
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;//設(shè)置存儲引擎，主鍵自動增長和默認(rèn)文本字符集CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id'))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);#查詢執(zhí)行后結(jié)果mysql> select * from tbl_dept;+----+----------+--------+| id | deptName | locAdd |+----+----------+--------+| 1 | RD | 11 || 2 | HR | 12 || 3 | MK | 13 || 4 | MIS | 14 || 5 | FD | 15 |+----+----------+--------+5 rows in set (0.00 sec)mysql> select * from tbl_emp;+----+------+--------+| id | NAME | deptId |+----+------+--------+| 1 | z3 | 1 || 2 | z4 | 1 || 3 | z5 | 1 || 4 | w5 | 2 || 5 | w6 | 2 || 6 | s7 | 3 || 7 | s8 | 4 || 8 | s9 | 51 |+----+------+--------+8 rows in set (0.00 sec)1、inner join：只有 deptId 和 id 的共有部分
2、left join（全A）：前七條共有數(shù)據(jù)；第八條a表獨(dú)有數(shù)據(jù)，b表補(bǔ)null
3、right join（全B）：前七條共有數(shù)據(jù)；第八條b表獨(dú)有數(shù)據(jù)，a表補(bǔ)null
4、左join獨(dú)A：表A獨(dú)有部分
5、右join獨(dú)B：表B獨(dú)有部分
6、full join：MySQL不支持full join，用全a+全b，union去重中間部分
union關(guān)鍵字可以合并去重
7、A、B各自獨(dú)有集合
MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（索引的本質(zhì)是數(shù)據(jù)結(jié)構(gòu)，排序+查詢兩種功能）。
索引的目的在于提高查詢效率，可以類比字典。
如果要查“mysql”這個單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。
如果沒有索引，那么你可能需要逐個逐個尋找，如果我想找到Java開頭的單詞呢？或者Oracle開頭的單詞呢？
是不是覺得如果沒有索引，這個事情根本無法完成？
索引可以理解為：排好序的快速查找數(shù)據(jù)結(jié)構(gòu)
下圖就是一種可能的索引方式示例：
假如：找4號這本書，掃碼得到對應(yīng)的編號為91，91比34大往右邊找，91比89大往右邊找，然后找到（比較三次后就可以找到，然后檢索出對應(yīng)的物理地址）
為了加快Col2的查找，可以維護(hù)一個右邊所示的二叉查找樹，每個節(jié)點(diǎn)分別包含索引鍵值和一個指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄
結(jié)論：在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引
一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上。
我們平常所說的索引，如果沒有特別指明，都是指B樹（多路搜索樹，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引，次要索引，覆蓋索引，復(fù)合索引，前綴索引，唯一索引默認(rèn)都是使用B+樹索引，統(tǒng)稱索引。當(dāng)然，除了B+樹這種類型的索引之外，還有哈稀索引(hash index)等
優(yōu)勢：
類似大學(xué)圖書館建書目索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本。
通過索引列對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。
劣勢：
實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的（占空間）
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息。
索引只是提高效率的一個因素，如果你的MysQL有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引，或優(yōu)化查詢
主鍵索引：索引值必須是唯一的，且不能為NULL
第一種：CREATE TABLE table_name(id int PRIMARY KEY aoto_increment,name varchar(10));
第二種： ALTER TABLE table_name ADD PRIMARY KEY (columnName);
普通索引：索引值可出現(xiàn)多次
第一種：CREATE INDEX index_name on table_name(columnName);
第二種：ALTER TABLE table_name ADD INDEX index_name (columnName);
全文索引：主要是針對文本的檢索，如：文章，全文索引只針對MyISAM引擎有效，并且只針對英文內(nèi)容生效
建表時創(chuàng)建
#建表CREATE TABLE articles(
id INT UNSIGNED ATUO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title,body))engine=myisam charset utf8; #指定引擎#使用select * from articles where match(title,body) against('英文內(nèi)容'); #只針對英語內(nèi)容生效#說明#1、在mysql中fultext索引只針對 myisam 生效#2、mysq1自己提供的flltext只針對英文生效->sphinx (coreseek)技術(shù)處理中文工#3、使用方法是match(字段名...) against(‘關(guān)鍵字')#4、全文索引一個叫停止詞，因?yàn)樵谝粋€文本中創(chuàng)建索引是一個無窮大的數(shù)，因此對一些常用詞和字符就不會創(chuàng)建，這些詞稱為停止詞ALTER TABLE table_name ADD FULLTEXT index_name (columnName);
唯一索引：索引列的值必須唯一，但允許有空值NULL，并可以有多個。
第一種： CREATE UNIQUE INDEX index_name ON table_name(columnName);
第二種：ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (columnName);
單值索引：即一個索引只包含單個列，一個表可以有多個單列索引。
第一種： CREATE INDEX index_name ON table_name(columnName);
第二種：ALTER TABLE table_name ADD INDEX index_name ON (columnName);
select * from user where name=''; //經(jīng)常查name字段，為其建索引create index idx_user_name on user(name);
復(fù)合索引：即一個索引包含多個列
第一種： CREATE INDEX index_name ON table_name(columnName1，columnName2...);
第二種：ALTER TABLE table_name ADD INDEX index_name ON (columnName1，columnName2...);
select * from user where name='' and email=''; //經(jīng)常查name和email字段，為其建索引create index idx_user_name on user(name, email);
查詢索引
第一種：SHOW INDEX FROM table_name;
第二種：SHOW KEYS FROM table_name;
刪除索引
第一種： DROP INDEX index_name ON table_name;
第二種：ALTER TABLE table_name DROP INDEX index_name;
刪除主鍵索引：ALTER TBALE table_name DROP PRIMARY KEY;
MySQL索引結(jié)構(gòu)：
BTree索引
Hash索引
full-text全文索引
R-Tree索引
初始化介紹
一顆b+樹，淺藍(lán)色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示),如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3,
P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。
真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)：3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非葉子節(jié)點(diǎn)只不存儲真實(shí)的數(shù)據(jù)，只存儲指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。
查找過程
如果要查找數(shù)據(jù)項(xiàng)29，那么首先會把磁盤塊1由磁盤加載到內(nèi)存，此時發(fā)生一次IO。在內(nèi)存中用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊1的P2指針，內(nèi)存時間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29 在 26 和 30 之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO
真實(shí)的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高
主鍵自動建立唯一索引
頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
單鍵/組合索引的選擇問題，who?(在高并發(fā)下傾向創(chuàng)建組合索引)
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統(tǒng)計(jì)或者分組字段
Where條件里用不到的字段不創(chuàng)建索引
表記錄太少（300w以上建）
經(jīng)常增刪改的表（提高了查詢速度，同時卻會降低更新表的速度，如對表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件）
數(shù)據(jù)重復(fù)且分布平均的表字段，因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實(shí)際效果。（比如：國籍、性別）
假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值的分布概率天約為50%，那么對這種表A字段建索引一般不會提高數(shù)據(jù)庫的查詢速度。
索引的選擇性是指索引列中不同值的數(shù)目與表中記錄數(shù)的比。如果一個表中有2000條記錄，表索引列有1980個不同的值，那么這個索引的選擇性就是1980/2000=0.99。一個索引的選擇性越接近于1，這個索引的效率就越高
MySQL Query Optimizer（查詢優(yōu)化器）[?kw??ri] [??pt?ma?z?]
Mysql中專門負(fù)責(zé)優(yōu)化SELECT語句的優(yōu)化器模塊，主要功能：通過計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息，為客戶端請求的Query提供他認(rèn)為最優(yōu)的執(zhí)行計(jì)劃（他認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式，但不見得是DBA認(rèn)為是最優(yōu)的,這部分最耗費(fèi)時間）
當(dāng)客戶端向MySQL請求一條Query，命令解析器模塊完成請求分類，區(qū)別出是SELECT并轉(zhuǎn)發(fā)給MySQL Query Optimizer時，MySQL Query Optimizer首先會對整條Query進(jìn)行優(yōu)化，處理掉一些常量表達(dá)式的預(yù)算直接換算成常量值。并對Query中的查詢條件進(jìn)行簡化和轉(zhuǎn)換，如去掉一些無用或顯而易見的條件、結(jié)構(gòu)調(diào)整等。然后分析Query 中的 Hint信息(如果有），看顯示Hint信息是否可以完全確定該Query的執(zhí)行計(jì)劃。如果沒有Hint 或Hint信息還不足以完全確定執(zhí)行計(jì)劃，則會讀取所涉及對象的統(tǒng)計(jì)信息，根據(jù)Query進(jìn)行寫相應(yīng)的計(jì)算分析，然后再得出最后的執(zhí)行計(jì)劃
MySQL常見瓶頸：
CPU：CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時候
IO：磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時候
服務(wù)器硬件的性能瓶頸：top，free，iostat和vmstat來查看系統(tǒng)的性能狀態(tài)
使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸
官網(wǎng)地址
Explain的作用：
表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以使用
哪些索引被實(shí)際使用
表之間的引用
每張表有多少行被優(yōu)化器查詢
使用Explain：
explain + sql語句
執(zhí)行計(jì)劃包含的信息（重點(diǎn)） ：| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_emp; +----+------+--------+ | id | NAME | deptId | +----+------+--------+ | 1 | z3 | 1 | | 2 | z4 | 1 | | 3 | z5 | 1 | | 4 | w5 | 2 | | 5 | w6 | 2 | | 6 | s7 | 3 | | 7 | s8 | 4 | | 8 | s9 | 51 | +----+------+--------+ 8 rows in set (0.00 sec) mysql> explain select * from tbl_emp; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
執(zhí)行計(jì)劃包含的信息（重點(diǎn)） ：| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
面試重點(diǎn)：id、type、key、rows、Extra
select查詢的序列號，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序
三種情況：
1、id相同，執(zhí)行順序由上至下（t1、t3、t2）
2、id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行（t3、t1、t2）
3、id相同不同，同時存在。先走數(shù)字大的，數(shù)字相同的由上至下（t3、s1、t2）
查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢。
SIMPLE [?s?npl] ：簡單的select查詢,查詢中不包含子查詢或者UNION
PRIMARY：查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為（最后加載的那個）
SUBQUERY [?kw??ri] ：在SELECT或WHERE列表中包含了子查詢
DERIVED [d??ra?vd]：在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生）MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時表里
UNION [?ju?ni?n]：若第二個SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；若UNION包含在FROM子句的子查詢中外層SELECT將被標(biāo)記為：DERIVED
UNION RESULT [r??z?lt] ：從UNION表獲取結(jié)果的SELECT（兩個select語句用UNION合并）
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
顯示查詢使用了何種類型
訪問類型排列：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL
type常用八種類型：
結(jié)果值從最好到最壞依次是（重點(diǎn)）：：system > const > eq_ref > ref > range > index > ALL
一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref
詳細(xì)說明
system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時不會出現(xiàn)，這個也可以忽略不計(jì)。
const：表示通過索引一次就找到了，const用于比較primary key或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量。
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。
ref：非唯一性索引掃描，返回匹配某個單獨(dú)值的所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨(dú)值的行，然而，它可能會找到多個符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體
range：只檢索給定范圍的行,使用一個索引來選擇行。key列顯示使用了哪個索引一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢。這種范圍掃描索引掃描比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束語另一點(diǎn)，不用掃描全部索引
index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引列。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件?。ㄒ簿褪钦f雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的）
all：Full Table Scan，將遍歷全表以找到匹配的行
工作案例：經(jīng)理這條SQL我跑了一下Explain分析，在系統(tǒng)上可能會有ALL全表掃描的情況，建議嘗試一下優(yōu)化。我把這條SQL改了改，我優(yōu)化后是這么寫，這個效果已經(jīng)從ALL變成了…
顯示可能應(yīng)用在這張表中的索引，一個或多個。查詢涉及到的字段火若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用（系統(tǒng)認(rèn)為理論上會使用某些索引）
實(shí)際使用的索引。如果為NULL，則沒有使用索引（要么沒建，要么建了失效）
查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中
覆蓋索引：建的索引字段和查詢的字段一致，如下圖
表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
key_len顯示的值為索引字段的最大可能長度，并非實(shí)際使用長度，即key_len是根據(jù)表定義計(jì)算而得，不是通過表內(nèi)檢索出的
顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。哪些列或常量被用于查找索引列上的值。
根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)（越小越好）
未建索引時：
建索引后：掃描行數(shù)減少
包含不適合在其他列中顯示但十分重要的額外信息
信息種類：Using filesort 、Using temporary 、Using index 、Using where 、Using join buffer 、impossible where 、select tables optimized away 、distinct
Using filesort（需要優(yōu)化）
說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無法利用索引完成的排序操作稱為"文件排序"
Using temporary（需要優(yōu)化）
使了用臨時表保存中間結(jié)果，MysQL在對查詢結(jié)果排序時使用臨時表。常見于排序order by和分組查詢group by
Using index（good）
表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數(shù)據(jù)行，效率不錯！
情況一：
情況二：
覆蓋索引 / 索引覆蓋（Covering Index）。
理解方式一：就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，MySQL可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件,換句話說查詢列要被所建的索引覆蓋。
理解方式二：索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它不必讀取整個行。畢竟索引葉子節(jié)點(diǎn)存儲了它們索引的數(shù)據(jù)；當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
注意：
如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不可select*
因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龝?dǎo)致索引文件過大，查詢性能下降
Using where：表明使用了where過濾。
Using join buffer：使用了連接緩存
impossible where：where子句的值總是false，不能用來獲取任何元組
select tables optimized away
在沒有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作，或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即完成優(yōu)化。
distinct
優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作。
寫出下圖的表的執(zhí)行順序
第一行（執(zhí)行順序4）：id列為1，表示是union里的第一個select，select_type列的primary表示該查詢?yōu)橥鈱硬樵?，table列被標(biāo)記為，表示查詢結(jié)果來自一個衍生表，其中derived3中3代表該查詢衍生自第三個select查詢，即id為3的select。【select d1.name… 】
第二行（執(zhí)行順序2）：id為3，是整個查詢中第三個select的一部分。因查詢包含在from中，所以為derived?！緎elect id,namefrom t1 where other_column=’’】
第三行（執(zhí)行順序3）：select列表中的子查詢select_type為subquery，為整個查詢中的第二個select。【select id from t3】
第四行（執(zhí)行順序1）：select_type為union，說明第四個select是union里的第二個select，最先執(zhí)行【select name,id from t2】
第五行（執(zhí)行順序5）：代表從union的臨時表中讀取行的階段，table列的<union1,4>表示用第一個和第四個select的結(jié)果進(jìn)行union操作?！緝蓚€結(jié)果union操作】
建表：
CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL ); INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (1,1,3,3,'3','3'); //查詢 mysql> select * from article; +----+-----------+-------------+-------+----------+-------+---------+ | id | author_id | category_id | views | comments | title | content | +----+-----------+-------------+-------+----------+-------+---------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | 3 | 1 | 1 | 3 | 3 | 3 | 3 | +----+-----------+-------------+-------+----------+-------+---------+ 3 rows in set (0.00 sec)
案例
要求：查詢 category_id 為 1 且 comments 大于1 的情況下，views 最多的 article_id
//功能實(shí)現(xiàn) mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; +----+-----------+ | id | author_id | +----+-----------+ | 3 | 1 | +----+-----------+ 1 row in set (0.00 sec) //explain分析 mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
結(jié)論：很顯然，type是ALL，即最壞的情況。Extra里還出現(xiàn)了Using filesort，也是最壞的情況。優(yōu)化是必須的
開始優(yōu)化
新建索引（給WHERE語句后使用的字段添加索引）
創(chuàng)建方式：
create index idx_article_ccv on article(category_id,comments,views);
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
索引用處不大，刪除：DROP INDEX idx_article_ccv ON article;
結(jié)論：
type變成了range，這是可以忍受的。但是extra里使用Using filesort仍是無法接受的。
但是我們已經(jīng)建立了索引，為啥沒用呢？
這是因?yàn)榘凑誃Tree索引的工作原理，先排序category_id，如果遇到相同的category_id則再排序comments,如果遇到相同的comments 則再排序views。
當(dāng)comments字段在聯(lián)合索引里處于中間位置時，因comments > 1條件是一個范圍值(所謂range)，MySQL無法利用索引再對后面的views部分進(jìn)行檢索，即range類型查詢字段后面的索引無效。
改進(jìn)
上次創(chuàng)建索引相比，這次不為comments字段創(chuàng)建索引
結(jié)論：type變?yōu)榱藃ef，ref 中是 const，Extra 中的 Using filesort也消失了，結(jié)果非常理想
建表：
CREATE TABLE IF NOT EXISTS class( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS book( bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(bookid) ); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); //查詢 mysql> select * from class; +----+------+ | id | card | +----+------+ | 1 | 17 | | 2 | 2 | | 3 | 18 | | 4 | 4 | | 5 | 4 | | 6 | 8 | | 7 | 9 | | 8 | 1 | | 9 | 18 | | 10 | 6 | | 11 | 15 | | 12 | 15 | | 13 | 12 | | 14 | 15 | | 15 | 18 | | 16 | 2 | | 17 | 18 | | 18 | 5 | | 19 | 7 | | 20 | 1 | | 21 | 2 | +----+------+ 21 rows in set (0.00 sec) mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 1 | 8 | | 2 | 14 | | 3 | 3 | | 4 | 16 | | 5 | 8 | | 6 | 12 | | 7 | 17 | | 8 | 8 | | 9 | 10 | | 10 | 3 | | 11 | 4 | | 12 | 12 | | 13 | 9 | | 14 | 7 | | 15 | 6 | | 16 | 8 | | 17 | 3 | | 18 | 11 | | 19 | 5 | | 20 | 11 | +--------+------+ 20 rows in set (0.00 sec)
開始Explain分析：type都是all，需要優(yōu)化（總有一個表來添加索引驅(qū)動）
左連接為左表加索引
刪除索引：drop index y on class;
左連接為右表添加索引
刪除索引：drop index Y on book;
案例：如果別人建的索引位置不對，只需要自己查詢時調(diào)整左右表的順序即可
結(jié)論：
第二行的type變?yōu)榱藃ef，rows也變少了，優(yōu)化比較明顯。這是由左連接特性決定的。LEFT JOIN條件用于確定如何從右表搜索行，左邊一定都有，所以右邊是我們的關(guān)鍵點(diǎn)，一定需要在右表建立索引（小表驅(qū)動大表）。
左連接，右表加索引
同理：右連接，左表加索引
建表：
CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(phoneid) )ENGINE=INNODB; INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); //查詢 mysql> select * from phone; +---------+------+ | phoneid | card | +---------+------+ | 1 | 10 | | 2 | 13 | | 3 | 17 | | 4 | 5 | | 5 | 12 | | 6 | 7 | | 7 | 15 | | 8 | 17 | | 9 | 17 | | 10 | 14 | | 11 | 19 | | 12 | 13 | | 13 | 5 | | 14 | 8 | | 15 | 2 | | 16 | 8 | | 17 | 11 | | 18 | 14 | | 19 | 13 | | 20 | 5 | +---------+------+ 20 rows in set (0.00 sec)
用上一節(jié)兩個表，刪除他們的索引：
三表查詢語句應(yīng)為：SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
創(chuàng)建索引：
應(yīng)該為第一個LFET JOIN 的右表 book 建索引
alter table `book` add index Y(`card`);
應(yīng)該為第二個LFET JOIN 的右表 phone 建索引
alter table `phone` add index z(`card`);
Explain分析：
后2行的 type 都是ref且總 rows優(yōu)化很好，效果不錯。因此索引最好設(shè)置在需要經(jīng)常查詢的字段中
結(jié)論：
Join語句的優(yōu)化
盡可能減少Join語句中的NestedLoop的循環(huán)總次數(shù)：“永遠(yuǎn)用小結(jié)果集驅(qū)動大的結(jié)果集（比如：書的類型表驅(qū)動書的名稱表）”。
優(yōu)先優(yōu)化NestedLoop的內(nèi)層循環(huán)，保證Join語句中被驅(qū)動表上Join條件字段已經(jīng)被索引。
當(dāng)無法保證被驅(qū)動表的Join條件字段被索引且內(nèi)存資源充足的前提下，不要太吝惜JoinBuffer的設(shè)置
建表：
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年齡',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'職位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入職時間'
)CHARSET utf8 COMMENT'員工記錄表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);索引失效案例：
1、全值匹配我最愛
2、最佳左前綴法則（重要?。?/code>：如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過復(fù)合索引中間列。
中間列不能斷：
3、不在索引列上做任何操作（計(jì)算、函數(shù)、（自動or手動）類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
4、存儲引擎不能使用索引中范圍條件右邊的列（范圍之后全失效，范圍列并不是做的查詢而是排序）。
5、盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select *。
6、mysql在使用不等于（!=或者<>）的時候無法使用索引會導(dǎo)致全表掃描。
7、is null, is not null 也無法使用索引。
8、like以通配符開頭（’%abc…’），mysql索引失效會變成全表掃描的操作（%寫在最右邊索引不會失效，或覆蓋索引）。
問題：解決like '%字符串%'時索引不被使用的方法？ 采用覆蓋索引的方法！
建表：
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');//查詢mysql> select * from tbl_user;+----+------+------+-----------+| id | name | age | email |+----+------+------+-----------+| 1 | 1aa1 | 21 | a@163.com || 2 | 2bb2 | 23 | b@163.com || 3 | 3cc3 | 24 | c@163.com || 4 | 4dd4 | 26 | d@163.com |+----+------+------+-----------+4 rows in set (0.00 sec)創(chuàng)建索引：
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
索引成功使用：
索引失效：
總結(jié)：%寫在最右邊，如果非要寫在最左邊，就使用覆蓋索引
9、字符串不加單引號索引失效。
Explain分析：
10、少用or，用它來連接時會索引失效
建表：
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
//查看表結(jié)構(gòu)
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)建索引：
create index idx_test03_c1234 on test03(c1,c2,c3,c4); //查看索引 mysql> show index from test03; +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | | | test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | | | test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | | | test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | | +--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec)
1）逐一增加列
2）交換條件順序不影響索引，但最好按照建索引順序來寫SQL
3) 限定范圍
4）order by
5）group by
定值、范圍還是排序，一般order by是給個范圍
group by基本上都需要進(jìn)行排序，會有臨時表產(chǎn)生
建議：
對于單值索引，盡量選擇針對當(dāng)前query過濾性更好的索引。
在選擇組合索引的時候，當(dāng)前Query中過濾性最好的字段在索引字段順序中，位置越靠左越好。
在選擇組合索引的時候，盡量選擇可以能夠包含當(dāng)前query中的where字句中更多字段的索引。
盡可能通過分析統(tǒng)計(jì)信息和調(diào)整query的寫法來達(dá)到選擇合適索引的目的。
優(yōu)化總結(jié)口訣
全值匹配我最愛， 最左前綴要遵守；
帶頭大哥不能死， 中間兄弟不能斷；
索引列上少計(jì)算， 范圍之后全失效；
LIKE 百分寫最右， 覆蓋索引不寫 *；
不等空值還有OR， 索引影響要注意；
VAR 引號不可丟， SQL 優(yōu)化有訣竅。
EXISTS [?ɡ?z?sts]語法：SELECT ...FROM table WHERE EXISTS (subquery)
該語法可以理解為：將主查詢的數(shù)據(jù)，放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（TRUE或FALSE）來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留
提示：
EXSTS(subquey) 只返回TRUE或FALSE，因此子查詢中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方說法是實(shí)際執(zhí)行時會忽略SELECT清單，因此沒有區(qū)別。
EXISTS子查詢的實(shí)際執(zhí)行過程可能經(jīng)過了優(yōu)化而不是我們理解上的逐條對比，如果擔(dān)憂效率問題，可進(jìn)行實(shí)際檢驗(yàn)以確定是否有效率問題。
EXISTS子查詢往往也可以用條件表達(dá)式，其他子查詢或者JOIN來替代，何種最優(yōu)需要具體問題具體分析
in和exists用法：
1、ORDER BY之后子句，盡量使用Index方式排序，避免使用FileSort方式排序
建表：
create table tblA( #id int primary key not null auto_increment, age int, birth timestamp not null ); insert into tblA(age, birth) values(22, now()); insert into tblA(age, birth) values(23, now()); insert into tblA(age, birth) values(24, now()); create index idx_A_ageBirth on tblA(age, birth); //查詢 mysql> select * from tblA; +------+---------------------+ | age | birth | +------+---------------------+ | 22 | 2021-04-04 19:31:45 | | 23 | 2021-04-04 19:31:45 | | 24 | 2021-04-04 19:31:45 | +------+---------------------+ 3 rows in set (0.00 sec) mysql> show index from tblA; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbla | 1 | idx_A_ageBirth | 1 | age | A | 3 | NULL | NULL | YES | BTREE | | | | tbla | 1 | idx_A_ageBirth | 2 | birth | A | 3 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
關(guān)注點(diǎn)：是order by之后會不會產(chǎn)生Using filesort
MySQL支持二種方式的排序，F(xiàn)ileSort和lIndex，Index效率高，它指MySQL掃描索引本身完成排序。FileSort方式效率較低。
ORDER BY滿足兩情況，會使用Index方式排序：
ORDER BY語句使用索引最左前列。
使用where子句與Order BY子句條件列組合滿足索引最左前列。
2、盡可能在索引上完成排序操作，遵照建索引的最佳左前綴
3、如果不在索引列上，mysql的filesort有兩種算法（自動啟動）
雙路排序
MySQL4.1之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到數(shù)據(jù)，讀取行指針和OrderBy列，對他們進(jìn)行排序，然后掃描已經(jīng)排序好的列表，按照列表中的值重新從列表中讀對應(yīng)的數(shù)據(jù)輸出。
從磁盤取排序字段，在buffer進(jìn)行排序，再從磁盤取其他字段。
取一批數(shù)據(jù)，要對磁盤進(jìn)行了兩次掃描，眾所周知，I\O是很耗時的，所以在mysql4.1之后，出現(xiàn)了第二種改進(jìn)的算法，就是單路排序
單路排序
從磁盤讀取查詢需要的所有列，按照order by列在buffer對它們進(jìn)行排序，然后掃描排序壓的列表進(jìn)行輸出，它的效率更快一些，避免了第二次讀取數(shù)據(jù)。并且把隨機(jī)IO變成了順序IO,但是它會使用更多的空間，因?yàn)樗衙恳恍卸急４嬖趦?nèi)存中了
結(jié)論及引申出的問題
由于單路是后出的，總體而言好過雙路
但是用單路有問題，在sort_buffer中，方法B比方法A要多占用很多空間，因?yàn)榉椒˙是把所有字段都取出,所以有可能取出的數(shù)據(jù)的總大小超出了sort_buffer的容量，導(dǎo)致每次只能取sort_buffer容量大小的數(shù)據(jù)，進(jìn)行排序（創(chuàng)建tmp文件，多路合并)，排完再取取
sort_buffer容量大小，再排……從而多次I/O。
本來想省一次I/O操作，反而導(dǎo)致了大量的I/O操作，反而得不償失
4、優(yōu)化策略
增大sort_buffer_size參數(shù)的設(shè)置
增大max_length_for_sort_data參數(shù)的設(shè)置
Why？
5、小總結(jié)：
group by實(shí)質(zhì)是先排序后進(jìn)行分組，遵照索引建的最佳左前綴。
當(dāng)無法使用索引列，增大max_length_for_sort_data參數(shù)的設(shè)置 + 增大sort_buffer_size參數(shù)的設(shè)置。
where高于having，能寫在where限定的條件就不要去having限定了
介紹：
MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來記錄在MySQL中響應(yīng)時間超過閥值的語句，具體指運(yùn)行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局小?/p>
具體指運(yùn)行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局?。long_query_time的默認(rèn)值為10，意思是運(yùn)行10秒以上的語句。
由他來查看哪些SQL超出了我們的最大忍耐時間值，比如一條sql執(zhí)行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結(jié)合之前explain進(jìn)行全面分析
操作說明：
默認(rèn)情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)账?，需要我們手動來設(shè)置這個參數(shù)。
當(dāng)然，如果不是調(diào)優(yōu)需要的話，一般不建議啟動該參數(shù)，因?yàn)殚_啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊?。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?/p>
查看是否開啟及如何開啟：
默認(rèn)： SHOW VARIABLES LIKE '%slow_query_log%'; [?ve?ri?bls]
開啟：set global slow_query_log=1;，只對當(dāng)前數(shù)據(jù)庫生效，如果MySQL重啟后則會失效
如果要永久生效，就必須修改配置文件my.cnf(其它系統(tǒng)變量也是如此)
修改my.cnf文件，[mysqld] 下增加或修改參數(shù)slow_query_log和slow_query_log_file后，然后重啟MySQL服務(wù)器。也即將如下兩行配置進(jìn)my.cnf文件
slow_query_log =1slow_query_log_file=/var/lib/mysqatguigu-slow.log
關(guān)于慢查詢的參數(shù)slow_query_log_file，它指定慢查詢?nèi)罩疚募拇娣怕窂剑?code>系統(tǒng)默認(rèn)會給一個缺省的文件host_name-slow.log（如果沒有指定參數(shù)slow_query_log_file的話）
開啟了慢查詢?nèi)罩竞螅裁礃拥腟QL才會記錄到慢查詢?nèi)罩纠锩婺兀?/strong>
這個是由參數(shù)long_query_time控制，默認(rèn)情況下long_query_time的值為10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';
可以使用命令修改，也可以在my.cnf參數(shù)里面修改。
假如運(yùn)行時間正好等于long_query_time的情況，并不會被記錄下來。也就是說，在mysql源碼里是判斷大于long_query_time，而非大于等于。
命名修改慢SQL閾值時間：set global long_query_time=3; [?ɡl??bl]
看不到修改情況的話，重開連接，或者換一個語句：show global variables like 'long_query_time';
記錄慢SQL并后續(xù)分析:
假設(shè)我們成功設(shè)置慢SQL閾值時間為3秒（set global long_query_time=3;）。
模擬超時SQL：select sleep(4);
查詢當(dāng)前系統(tǒng)中有多少條慢查詢記錄：show global status like '%Slow_queries%'; [?ste?t?s]
在配置文件中設(shè)置慢SQL閾值時間（永久生效）：
#[mysqld]下配置:slow_query_log=1;slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3;log_output=FILE;
日志分析工具mysqldumpslow
在生產(chǎn)環(huán)境中，如果要手工分析日志，查找、分析SQL，顯然是個體力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的幫助信息，mysqldumpslow --help。
常用mysqldumpslow幫助信息：
s：是表示按照何種方式排序
c：訪問次數(shù)
l：鎖定時間
r：返回記錄
t：查詢時間
al：平均鎖定時間
ar：平均返回記錄數(shù)
at：平均查詢時間
t：即為返回前面多少條的數(shù)據(jù)
g：后邊搭配一個正則匹配模式，大小寫不敏感的
工作常用參考：
得到返回記錄集最多的10個SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到訪問次數(shù)最多的10個SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照時間排序的前10條里面含有左連接的查詢語句：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建議在使用這些命令時結(jié)合│和more 使用，否則有可能出現(xiàn)爆屏情況：`mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more
1、建表：
create database bigData;use bigData;//部門表CREATE TABLE dept( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20)NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "")ENGINE=INNODB DEFAULT CHARSET=utf8;//員工表CREATE TABLE emp( id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0, //編號 ename varchar(20) not null default "", //名字 job varchar(9) not null default "", //工作 mgr mediumint unsigned not null default 0, //上級編號 hiredate date not null, //入職時間 sal decimal(7,2) not null, //薪水 comm decimal(7,2) not null, //紅利 deptno mediumint unsigned not null default 0 //部門編號)ENGINE=INNODB DEFAULT CHARSET=utf8;
2、設(shè)置參數(shù)log_bin_trust_function_creators
創(chuàng)建函數(shù)，假如報(bào)錯：This function has none of DETERMINISTIC…
由于開啟過慢查詢?nèi)罩?，因?yàn)槲覀冮_啟了bin-log，我們就必須為我們的function指定一個參數(shù)
show variables like 'log_bin_trust_function_creators';set global log_bin_trust_function_creators=1;
這樣添加了參數(shù)以后，如果mysqld重啟，上述參數(shù)又會消失，永久方法：
windows下：my.ini[mysqld] 加上 log_bin_trust_function_creators=1
linux下：/etc/my.cnf 下my.cnf[mysqld] 加上 log_bin_trust_function_creators=1
3、創(chuàng)建函數(shù)，保證每條數(shù)據(jù)都不同
隨機(jī)產(chǎn)生字符串
delimiter $$ #為了存儲過程能正常運(yùn)行，修改命令結(jié)束符，兩個 $$ 表示結(jié)束create function rand_string(n int) returns varchar(255)begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i=i+1; end while; return return_str;end $$
隨機(jī)產(chǎn)生部門編號
delimiter $$create function rand_num() returns int(5)begin declare i int default 0; set i=floor(100+rand()*10); return i;end $$
4、創(chuàng)建存儲過程
創(chuàng)建往emp表中插入數(shù)據(jù)的存儲過程
delimiter $$create procedure insert_emp(in start int(10),in max_num int(10)) #max_num：表示插入多少條數(shù)據(jù)begin declare i int default 0; set autocommit = 0; #關(guān)閉自動提交，避免寫一個insert提交一次，50w條一次性提交 repeat set i = i+1; insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num()); until i=max_num end repeat; commit;end $$
創(chuàng)建往dept表中插入數(shù)據(jù)的存儲過程
delimiter $$create procedure insert_dept(in start int(10),in max_num int(10))begin declare i int default 0; set autocommit = 0; repeat set i = i+1; insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8)); until i=max_num end repeat; commit;end $$
5、調(diào)用存儲過程
往dept表中插入數(shù)據(jù)
mysql> DELIMITER ; # 修改默認(rèn)結(jié)束符號為（;），之前改成了## mysql> CALL insert_dept(100, 10); Query OK, 0 rows affected (0.01 sec)
往emp表中插入50萬數(shù)據(jù)
mysql> DELIMITER ;mysql> CALL insert_emp(100001, 500000);Query OK, 0 rows affected (27.00 sec)
查看運(yùn)行結(jié)果
mysql> select * from dept; +----+--------+---------+--------+ | id | deptno | dname | loc | +----+--------+---------+--------+ | 1 | 101 | mqgfy | ck | | 2 | 102 | wgighsr | kbq | | 3 | 103 | gjgdyj | brb | | 4 | 104 | gzfug | p | | 5 | 105 | keitu | cib | | 6 | 106 | nndvuv | csue | | 7 | 107 | cdudl | tw | | 8 | 108 | aafyea | aqq | | 9 | 109 | zuqezjx | dpqoyo | | 10 | 110 | pam | cses | +----+--------+---------+--------+ 10 rows in set (0.00 sec) mysql> select * from emp limit 10; #查看前10條數(shù)據(jù)（50W太多了） +----+--------+-------+----------+-----+------------+---------+--------+--------+ | id | empno | ename | job | mgr | hiredate | sal | comm | deptno | +----+--------+-------+----------+-----+------------+---------+--------+--------+ | 1 | 100002 | xmbva | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 | | 2 | 100003 | aeq | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 109 | | 3 | 100004 | cnjfz | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 105 | | 4 | 100005 | wwhd | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 100 | | 5 | 100006 | e | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 | | 6 | 100007 | yjfr | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 108 | | 7 | 100008 | xlp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 | | 8 | 100009 | mp | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 102 | | 9 | 100010 | tcdl | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 107 | | 10 | 100011 | akw | salesman | 1 | 2021-04-05 | 2000.00 | 400.00 | 106 | +----+--------+-------+----------+-----+------------+---------+--------+--------+ 10 rows in set (0.00 sec)
Show Profile是mysql提供可以用來分析當(dāng)前會話中語句執(zhí)行的資源消耗情況。可以用于SQL的調(diào)優(yōu)的測量
官網(wǎng)文檔
默認(rèn)情況下，參數(shù)處于關(guān)閉狀態(tài)，并保存最近15次的運(yùn)行結(jié)果
分析步驟：
1、是否支持，看看當(dāng)前的mysql版本是否支持：show variables like 'profiling';
默認(rèn)是關(guān)閉，使用前需要開啟
2、開啟功能，默認(rèn)是關(guān)閉，使用前需要開啟：set profiling=on;
3、運(yùn)行SQL（隨便運(yùn)行用來測試）
mysql> select * from emp group by id%10 limit 150000; mysql> select * from emp group by id%20 order by 5;
4、查看結(jié)果：show profiles;
mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 1 | 0.00204000 | show variables like 'profiling' | | 2 | 0.55134250 | select * from emp group by id%10 limit 150000 | | 3 | 0.56902000 | select * from emp group by id%20 order by 5 | +----------+------------+-----------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
5、診斷SQL，show profile cpu,block io for query ID號;（ID號為第4步Query_ID列中數(shù)字）
mysql> show profile cpu,block io for query 3; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000049 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | Opening tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL | | init | 0.000021 | 0.000000 | 0.000000 | NULL | NULL | | System lock | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000017 | 0.000000 | 0.000000 | NULL | NULL | | preparing | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | | Creating tmp table | 0.000045 | 0.000000 | 0.000000 | NULL | NULL | | Sorting result | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | Sending data | 0.568704 | 0.546875 | 0.046875 | NULL | NULL | | Creating sort index | 0.000048 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | freeing items | 0.000061 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set, 1 warning (0.00 sec)
參數(shù)備注（寫在代碼中）：show profile cpu,block io for query 3;（如此代碼中的cpu,block）
ALL：顯示所有的開銷信息。
BLOCK IO：顯示塊lO相關(guān)開銷。
CONTEXT SWITCHES ：上下文切換相關(guān)開銷。
CPU：顯示CPU相關(guān)開銷信息。
IPC：顯示發(fā)送和接收相關(guān)開銷信息。
MEMORY：顯示內(nèi)存相關(guān)開銷信息。
PAGE FAULTS：顯示頁面錯誤相關(guān)開銷信息。
SOURCE：顯示和Source_function，Source_file，Source_line相關(guān)的開銷信息。
SWAPS：顯示交換次數(shù)相關(guān)開銷的信息。
6、日常開發(fā)需要注意的結(jié)論（Status列中的出現(xiàn)此四個問題嚴(yán)重）
converting HEAP to MyISAM：查詢結(jié)果太大，內(nèi)存都不夠用了往磁盤上搬了。
Creating tmp table：創(chuàng)建臨時表，拷貝數(shù)據(jù)到臨時表，用完再刪除
Copying to tmp table on disk：把內(nèi)存中臨時表復(fù)制到磁盤，危險(xiǎn)!
locked：鎖了
永遠(yuǎn)不要在生產(chǎn)環(huán)境開啟這個功能，只能在測試環(huán)境使用！
第一種：配置文件啟用。在mysq l的 my.cnf 中，設(shè)置如下：
#開啟general_log=1#記錄日志文件的路徑general_log_file=/path/logfile#輸出格式log_output=FILE
第二種：編碼啟用。命令如下：
set global general_log=1;
set global log_output='TABLE';
此后，你所編寫的sql語句，將會記錄到mysql庫里的geneial_log表，可以用下面的命令查看：
mysql> select * from mysql.general_log; +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+ | 2021-04-05 19:57:28.182473 | root[root] @ localhost [::1] | 5 | 1 | Query | select * from mysql.general_log | +----------------------------+------------------------------+-----------+-----------+--------------+---------------------------------+ 1 row in set (0.00 sec)
定義：
鎖是計(jì)算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。
在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（如CPU、RAM、I/O等）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜
例子：京東購物
打個比方，我們到京東上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，那么如何解決是你買到還是另一個人買到的問題？
這里肯定要用到事務(wù)，我們先從庫存表中取出物品數(shù)量，然后插入訂單，付款后插入付款表信息，然后更新商品數(shù)量。在這個過程中，使用鎖可以對有限的資源進(jìn)行保護(hù)，解決隔離和并發(fā)的矛盾
鎖的分類：
從對數(shù)據(jù)操作的類型（讀\寫）分
讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進(jìn)行而不會互相影響。
寫鎖（排它鎖）：當(dāng)前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。
從對數(shù)據(jù)操作的粒度分
表鎖
行鎖
特點(diǎn)：偏向MyISAM存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
案例分析
建表表
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
#查詢
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)手動增加表鎖：lock table 表名字 read(write), 表名字2 read(write), 其他;
mysql> lock table mylock read; Query OK, 0 rows affected (0.00 sec)
查看表上加過的鎖：show open tables;
mysql> show open tables; +--------------------+------------------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+------------------------------------------------------+--------+-------------+ | performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 | | performance_schema | replication_connection_status | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | mysql | columns_priv | 0 | 0 | | my | test03 | 0 | 0 | | bigdata | mylock | 1 | 0 | # In_use為1時表示已上鎖
釋放鎖：unlock tables;
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) # 再次查看 mysql> show open tables; +--------------------+------------------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+------------------------------------------------------+--------+-------------+ | performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 | | performance_schema | replication_connection_status | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | mysql | columns_priv | 0 | 0 | | my | test03 | 0 | 0 | | bigdata | mylock | 0 | 0 |
加讀鎖——為mylock表加read鎖（讀阻塞寫例子）
為mylock表加write鎖（MylSAM存儲引擎的寫阻塞讀例子）
MyISAM在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會自動給涉及的表加寫鎖。
MySQL的表級鎖有兩種模式：
表共享讀鎖(Table Read Lock)
表獨(dú)占寫鎖(Table Write Lock)
結(jié)合上表，所以對MyISAM表進(jìn)行操作，會有以下情況：
對MyISAM表的讀操作（加讀鎖），不會阻塞其他進(jìn)程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當(dāng)讀鎖釋放后，才會執(zhí)行其它進(jìn)程的寫操作。
對MyISAM表的寫操作〈加寫鎖），會阻塞其他進(jìn)程對同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會執(zhí)行其它進(jìn)程的讀寫操作。
重點(diǎn)?。?code>簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞
看看哪些表被加鎖了：show open tables;
如何分析表鎖定
可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定
mysql> show status like 'table_locks%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 170 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec)
這里有兩個狀態(tài)變量記錄MySQL內(nèi)部表級鎖定的情況，兩個變量說明如下：
Table_locks_immediate：產(chǎn)生表級鎖定的次數(shù)，表示可以立即獲取鎖的查詢次數(shù)，每立即獲取鎖值加1 ;
Table_locks_waited（重點(diǎn)）：出現(xiàn)表級鎖定爭用而發(fā)生等待的次數(shù)(不能立即獲取鎖的次數(shù)，每等待一次鎖值加1)，此值高則說明存在著較嚴(yán)重的表級鎖爭用情況；
此外，MyISAM的讀寫鎖調(diào)度是寫優(yōu)先，這也是MyISAM不適合做寫為主表的引擎。因?yàn)閷戞i后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞
偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)(TRANSACTION)；二是采用了行級鎖
由于行鎖支持事務(wù)，復(fù)習(xí)老知識：
事務(wù)(Transaction）及其ACID屬性
并發(fā)事務(wù)處理帶來的問題
事務(wù)隔離級別
1）事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有以下4個屬性，通常簡稱為事務(wù)的ACID屬性：
原子性(Atomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。
一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性;事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)〈如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（lsolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過程中的中間狀態(tài)對外部是不可見的，反之亦然。
持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。
2）并發(fā)事務(wù)處理帶來的問題
更新丟失(Lost Update)
當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題――最后的更新覆蓋了由其他事務(wù)所做的更新。
例如，兩個程序員修改同一java文件。每程序員獨(dú)立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋前一個程序員所做的更改。
如果在一個程序員完成并提交事務(wù)之前，另一個程序員不能訪問同一文件，則可避免此問題。
臟讀(Dirty Reads)
一個事務(wù)正在對一條記錄做修改，在這個事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做”臟讀”。
一句話：事務(wù)A讀取到了事務(wù)B已修改但尚未提交的的數(shù)據(jù)，還在這個數(shù)據(jù)基礎(chǔ)上做了操作。此時，如果B事務(wù)回滾，A讀取的數(shù)據(jù)無效，不符合一致性要求
不可重復(fù)讀(Non-Repeatable Reads)
一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了，這種現(xiàn)象就叫做“不可重復(fù)讀”。
一句話：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù)，不符合隔離性。
幻讀(Phantom Reads)
一個事務(wù)接相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀“。
一句話：事務(wù)A讀取到了事務(wù)B體提交的新增數(shù)據(jù)，不符合隔離性
多說一句：幻讀和臟讀有點(diǎn)類似。臟讀是事務(wù)B里面修改了數(shù)據(jù)；幻讀是事務(wù)B里面新增了數(shù)據(jù)。
3）事務(wù)隔離級別
”臟讀”、“不可重復(fù)讀”和“幻讀”，其實(shí)都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決
數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。同時，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。
?？串?dāng)前數(shù)據(jù)庫的事務(wù)隔離級別：show variables like 'tx_isolation';
mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set, 1 warning (0.00 sec) # 默認(rèn)情況下：MySQL避免了臟讀和不可重復(fù)讀
建表：
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB; INSERT INTO test_innodb_lock VALUES(1,'b2'); INSERT INTO test_innodb_lock VALUES(3,'3'); INSERT INTO test_innodb_lock VALUES(4, '4000'); INSERT INTO test_innodb_lock VALUES(5,'5000'); INSERT INTO test_innodb_lock VALUES(6, '6000'); INSERT INTO test_innodb_lock VALUES(7,'7000'); INSERT INTO test_innodb_lock VALUES(8, '8000'); INSERT INTO test_innodb_lock VALUES(9,'9000'); INSERT INTO test_innodb_lock VALUES(1,'b1'); CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b); //查看 mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) mysql> show index from test_innodb_lock; +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 8 | NULL | NULL | YES | BTREE | | | | test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | | +------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
行鎖定基本演示（兩個客戶端更新同一行記錄）
疑惑解答為什么兩個都要commint
無索引行鎖升級為表鎖
什么是間隙鎖
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖，對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP）”。
InnoDB也會對這個“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）。
危害
因?yàn)镼uery執(zhí)行過程中通過過范圍查找的話，他會鎖定整個范圍內(nèi)所有的索引鍵值，即使這個鍵值并不存在。
間隙鎖有一個比較致命的弱點(diǎn)，就是當(dāng)鎖定一個范圍鍵值之后，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場景下這可能會對性能造成很大的危害
begin（中間寫自己的操作）commit
總結(jié)：
Innodb存儲引擎由于實(shí)現(xiàn)了行級鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級鎖定的。當(dāng)系統(tǒng)并發(fā)量較高的時候，Innodb的整體性能和MylISAM相比就會有比較明顯的優(yōu)勢了。
但是，Innodb的行級鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r候，可能會讓Innodb的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會更差
如何分析行鎖定?
通過檢查lnnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：show status like 'innodb_row_lock%';
mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.00 sec)
對各個狀態(tài)量的說明如下:
Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；
Innodb_row_lock_time：從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度；
Innodb_row_lock_time_avg：每次等待所花平均時間；
Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最常的一次所花的時間；
Innodb_row_lock_waits：系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)；
對于這5個狀態(tài)變量，比較重要的主要是：
lnnodb_row_lock_time（等待總時長）
Innodb_row_lock_time_avg（等待平均時長）
lnnodb_row_lock_waits（等待總次數(shù)）
尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時長也不小的時候，我們就需要分析（Show Profile）系統(tǒng)中為什么會有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃。
優(yōu)化建議
盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
合理設(shè)計(jì)索引，盡量縮小鎖的范圍
盡可能較少檢索條件，避免間隙鎖
盡量控制事務(wù)大小，減少鎖定資源量和時間長度
盡可能低級別事務(wù)隔離
頁鎖
開銷和加鎖時間界于表鎖和行鎖之間;會出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。（了解一下即可）
slave會從master讀取binlog來進(jìn)行數(shù)據(jù)同步
原理圖：
MySQL復(fù)制過程分成三步：
1、master將改變記錄到二進(jìn)制日志(binary log)。這些記錄過程叫做二進(jìn)制日志事件，binary log events;
2、slave將master的binary log events拷貝到它的中繼日志(relay log) ;
3、slave重做中繼日志中的事件，將改變應(yīng)用到自己的數(shù)據(jù)庫中。MySQL復(fù)制是異步的且串行化的
每個slave只有一個master
每個slave只能有一個唯一的服務(wù)器ID
每個master可以有多個salve
復(fù)制的最大問題是延遲。
一、mysql版本一致且后臺以服務(wù)運(yùn)行
二、主從都配置在[mysqld]結(jié)點(diǎn)下，都是小寫
主機(jī)修改my.ini配置文件：
1、[必須]主服務(wù)器唯一ID：server-id=1
2、[必須]啟用二進(jìn)制日志
log-bin=自己本地的路徑/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
3、[可選]啟用錯誤日志
log-err=自己本地的路徑/mysqlerr
log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
4、[可選]根目錄
basedir=“自己本地路徑”
basedir=“D:/devSoft/MySQLServer5.5/”
5、[可選]臨時目錄
tmpdir=“自己本地路徑”
tmpdir=“D:/devSoft/MySQLServer5.5/”
6、[可選]數(shù)據(jù)目錄
datadir=“自己本地路徑/Data/”
datadir=“D:/devSoft/MySQLServer5.5/Data/”
7、主機(jī)，讀寫都可以
read-only=O
8、[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫
binlog-ignore-db=mysql
9、[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫
binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字
從機(jī)修改my.cnf配置文件：
1、[必須]從服務(wù)器唯一ID：vim etc/my.cnf（進(jìn)入修改配置文件）
...#server-id=1 //注釋吊...server-id=1 //開啟...
2、[可選]啟用二進(jìn)制日志
三、配置文件，請主機(jī)+從機(jī)都重啟后臺mysql服務(wù)
主機(jī)：手動重啟
Linux從機(jī)命名：
service mysql stop
service mysql start
四、主機(jī)從機(jī)都關(guān)閉防火墻
windows手動關(guān)閉
關(guān)閉虛擬機(jī)linux防火墻： service iptables stop
五、在Windows主機(jī)上建立帳戶并授權(quán)slave
GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@‘從機(jī)器數(shù)據(jù)庫IP’ IDENTIFIED BY ‘123456’;
刷新：flush privileges;
查詢master的狀態(tài)
show master status;
記錄下File和Position的值
執(zhí)行完此步驟后不要再操作主服務(wù)器MYSQL，防止主服務(wù)器狀態(tài)值變化
六、在Linux從機(jī)上配置需要復(fù)制的主機(jī)
CHANGE MASTER TO MASTER_HOST=’主機(jī)IP’,
MASTER_USER=‘zhangsan’,
MASTER_PASSWORD=’123456’,
MASTER_LOG_FILE='File名字’,
MASTER_LOG_POS=Position數(shù)字;
啟動從服務(wù)器復(fù)制功能：start slave;
show slave status\G（下面兩個參數(shù)都是Yes，則說明主從配置成功!）
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
七、主機(jī)新建庫、新建表、insert記錄，從機(jī)復(fù)制
主機(jī)操作
從機(jī)（自動同步）
八、如何停止從服務(wù)復(fù)制功能：stop slave;
如果有一段數(shù)據(jù)暫時不要？
從機(jī)：
主機(jī)（需要重新查刻度）：
感謝各位的閱讀，以上就是“MySQL中SQL優(yōu)化、索引優(yōu)化、鎖機(jī)制、主從復(fù)制的方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL中SQL優(yōu)化、索引優(yōu)化、鎖機(jī)制、主從復(fù)制的方法這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼