一、SQL查詢的解析

• 關(guān)聯(lián)查詢過多
• 索引失效（單值、符合）
  
  
  

二、mysql explain使用簡介

1、關(guān)于id的說明：

2 、select_type

常見和常用的值有如下幾種：

分別用來表示查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢。

1、 SIMPLE 簡單的select查詢，查詢中不包含子查詢或者UNION

2、 PRIMARY 查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為PRIMARY

3、 SUBQUERY 在SELECT或WHERE列表中包含了子查詢

4、DERIVED 在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生），MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時表中

5、UNION 若第二個SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION：若UNION包含在FROM子句的子查詢中，外層SELECT將被標(biāo)記為：DERIVED
6、UNION RESULT 從UNION表獲取結(jié)果的SELECT

3、 關(guān)于 type 說明

4 、 table

指的就是當(dāng)前執(zhí)行的表

5 、possible_keys 和 key

possible_keys： 顯示可能應(yīng)用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用。
key ： 實際使用的索引，如果為NULL，則沒有使用索引。（可能原因包括沒有建立索引或索引失效）

6、 explain 關(guān)于 EXtra介紹

包含不適合在其他列中顯式但十分重要的額外信息

6.1 Using filesort（九死一生）

說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”。

6.2 Using temporary（十死無生）

使用了用臨時表保存中間結(jié)果，MySQL在對查詢結(jié)果排序時使用臨時表。常見于排序order by和分組查詢group by。

6.3 Using index（發(fā)財了）

表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index），避免訪問了表的數(shù)據(jù)行，效率不錯。如果同時出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找；如果沒有同時出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。

6.4 Using where

表明使用了where過濾

6.5 Using join buffer

表明使用了連接緩存,比如說在查詢的時候，多表join的次數(shù)非常多，那么將配置文件中的緩沖區(qū)的join buffer調(diào)大一些。

6.6 impossible where

where子句的值總是false，不能用來獲取任何元組

SELECT * FROM t_user WHERE id = '1' and id = '2'

### 6.7 select tables optimized away
在沒有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進(jìn)行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。

### 6.8 distinct
優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作

覆蓋索引(Covering Index), 一般說為索引覆蓋。

• 理解方式一:就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，MySQL可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件,換句話說查詢列要被所建的索引覆蓋。
• 理解方式二:索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它不必讀取整個行。畢竟索引葉子節(jié)點存儲了它們索引的數(shù)據(jù);當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個索引包含了(或覆蓋了)滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。

- 小練習(xí)

答案：

Join語句的優(yōu)化

常見失效：

第2點解釋、最佳左側(cè)法則：

如果索引了多例，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列
自己理解：（在復(fù)合索引中）按照復(fù)合索引中的第一個字段進(jìn)行引用。

第4點解釋、存儲引擎不能使用索引中范圍條件右邊的列。

第5點解釋、

第6點解釋、

第7點解釋、

第8點解釋、

解決方法

• like 匹配的值可以在左邊寫 % ，右邊不能寫。
• 可以使用覆蓋索引解決

第9點解釋、字符串不加單引號索引失效

in與exists

ORDER BY優(yōu)化

• MySQL支持二種方式的排序，FileSort和Index，Index效率高.它指MySQL掃描索引本身完成排序。FileSort方式效率較低。
  
  

MYSQLDUNMPSLOW

三、批量導(dǎo)入數(shù)據(jù)時（1000w）時注意

• 示例
• 

CREATE TABLE demo_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 ;

#創(chuàng)建表 demo_emp
CREATE TABLE demo_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 ;

• 2.1隨機產(chǎn)生字符串
  

##隨機產(chǎn)生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghjklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUWWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i<n DOSET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i=i+1;END WHILE;RETURN return_str;
END $$

• 2.2隨機產(chǎn)生部門編號
  

#用于隨機產(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 $$

• 2.3創(chuàng)建往emp表中插入數(shù)據(jù)的存儲過程（進(jìn)行一次提交）
  

CREATE DEFINER=`root`@`%` PROCEDURE `insert_emp`(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
##set autocommit =）把autocommit設(shè)置成0
SET autocommit = 0;
REPEAT
SET i = i+ 1;
INSERT INTO demo_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

3、驗證

• 練習(xí)
  

四 Profile進(jìn)行sql分析

4.1 是否支持

查詢時不要出現(xiàn)的：

4.2全局日志

• 1 、配置啟用
• 1 、編碼置啟用
  

五、數(shù)據(jù)庫鎖機制

查看鎖的命令 : show open tables;

釋放鎖

unlock 表名

簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞。

六、ACID原理

事務(wù)隔離級別

手動提交事務(wù)

七、大坑

7.1 無索引行鎖升級為表鎖

我們知道鎖主要是加在索引上，如果對非索引字段更新，行鎖可能會變表鎖 , 從上面的測試中也可以驗證這個觀點。

結(jié)論
InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖 ,并且該索引不能失效，否則會從行鎖升級為表鎖 。

所以建表的時候 ，結(jié)合你的業(yè)務(wù)，如果有更新的操作，切記要對操作的字段建立索引，不然并發(fā)下這個問題就非常明顯

7.2 間隙鎖危害

對條件范圍進(jìn)行加鎖

7.3 面試常見問題： 如何鎖一行

杭鎖總結(jié)：

# 通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況
show STATUS LIKE 'innodb_row_lock%'

八、 主從復(fù)制

8.1原理

8.2 規(guī)則

復(fù)制的最大問題：有延時 ！

一主一從配置

1. MySQL版本必須一致。
2. 主從都配置在[mysqld]結(jié)點下，都是小寫
3. 主機 my.ini配置
   
   
   
4. 從機配置

• [必須]從服務(wù)器唯一ID
• [可選]啟用二進(jìn)制日志

5. 關(guān)閉防火

6. 在Windows主機上建立帳戶并授權(quán)slave
   
   

7. 從機配置
   

8. stop slave ; -如何停止從服務(wù)復(fù)制功能