正規(guī)外貿(mào)網(wǎng)站建設(shè)公司百度老年搜索
一、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ù)制的最大問題:有延時 !
一主一從配置
- MySQL版本必須一致。
- 主從都配置在[mysqld]結(jié)點下,都是小寫
- 主機 my.ini配置
- 從機配置
- [必須]從服務(wù)器唯一ID
- [可選]啟用二進(jìn)制日志
-
關(guān)閉防火
-
在Windows主機上建立帳戶并授權(quán)slave
-
從機配置
-
stop slave ; -如何停止從服務(wù)復(fù)制功能