查詢sql的數(shù)學(xué)表達(dá)
1 SELECT A.,B. //投影
FROM A,B //笛卡爾積
WHERE A.c1=1 AND B.c1=2 //選擇
UNION //并集，差集
SELECT A.,B.
FROM A,B
WHERE A.c1=1 AND B.c1=2 OR B.c1>100
mysql 邏輯架構(gòu)
- 連接層尿招，例如jdbc
- 服務(wù)層矾柜，如SQL接口，并完成緩存查詢就谜，SQL的分析和優(yōu)化及部分內(nèi)置函數(shù)的執(zhí)行
- 引擎層怪蔑，例如：MYISAM和InnoDB
- 存儲(chǔ)層，數(shù)據(jù)存儲(chǔ)層
MYISAM和InnoDB的主要區(qū)別
| 對(duì)比項(xiàng) | MYISAM | InnoDB |
|---|---|---|
| 外鍵 | 不支持 | 支持 |
| 事物 | 不支持 | 支持 |
| 緩存 | 只緩存索引丧荐，不緩存數(shù)據(jù) | 既緩存索引缆瓣，也緩存數(shù)據(jù) |
| 表空間 | 小 | 大 |
| 關(guān)注點(diǎn) | 性能 | 事物 |
mysql執(zhí)行解析順序圖
7種join理論
以下面兩張表做演示
左外連接效果：右邊為空時(shí)，以null 補(bǔ)全虹统。
左外連接弓坞，只保留自己獨(dú)有的數(shù)據(jù)
右外連接效果：左邊為空時(shí)，以null 補(bǔ)全车荔。
右外連接渡冻，只保留自己獨(dú)有的數(shù)據(jù)。
內(nèi)連接忧便，取交集族吻。
或者如下
全連接：取并集。(因?yàn)閙ysql不支持full join，這里我們使用union實(shí)現(xiàn))
全連接：取各自獨(dú)有的部分超歌。(因?yàn)閙ysql不支持full join砍艾，這里我們使用union實(shí)現(xiàn))
索引
索引是一種已經(jīng)排好序的快速查找數(shù)據(jù)結(jié)構(gòu)
索引的類型
- 單列索引：這個(gè)索引只包含一個(gè)列，一個(gè)表可以有多個(gè)單列索引
- 唯一索引：索引列的值巍举，必須唯一辐董，并且非空
- 復(fù)合索引 ：這個(gè)索引包含多個(gè)列
索引建立的原則
- 主鍵自動(dòng)建立唯一索引
- 頻繁作為查詢條件的字段應(yīng)該建立索引
- 與其他表字段建立關(guān)聯(lián)關(guān)系的字段應(yīng)該建立索引
- 頻繁更新的字段不適合建立索引
- where條件用不到的字段不建立索引
- 單列索引與復(fù)合索引的選擇（高并發(fā)下傾向于用復(fù)合索引）
- 查詢中的排序字段，該字段若通過(guò)索引去訪問(wèn)將大大加快效率
- 查詢中統(tǒng)計(jì)或者分組字段
不適合建立索引的情況
- 表記錄很少的情況下
- 經(jīng)常增刪改的表
- 數(shù)據(jù)重復(fù)且分布平均的表字段禀综，那么建立索引也就無(wú)意義 比如性別简烘，國(guó)籍等，因?yàn)樗饕倪x擇性太低了
使用explain來(lái)查看mysql的查詢語(yǔ)句的執(zhí)行計(jì)劃定枷，explain重要字段的解釋
- id：id列數(shù)字越大越先執(zhí)行孤澎，如果說(shuō)數(shù)字一樣大，那么就從上往下依次執(zhí)行
- type：查詢類型從最好到最差依次為system>const>eq_ref>ref>range>index>all
system:系統(tǒng)掃描欠窒，實(shí)際使用中幾乎不存在
const: 常量掃描覆旭，表示通過(guò)一次索引查詢就找到了，const用于比較primary或unique索引岖妄，因?yàn)橹恍枰ヅ湟恍袛?shù)據(jù) 所以非承徒快，如將其置于where列中 mysql就可以將其轉(zhuǎn)換為一個(gè)常量荐虐。
eq_ref：唯一鍵索引掃描七兜，對(duì)于每一個(gè)索引鍵表中只有一條記錄與之匹配，常見(jiàn)于主鍵和唯一鍵索引福扬，
ref：非唯一性掃描腕铸，返回匹配某個(gè)單獨(dú)值的行(可能是多條結(jié)果)，常見(jiàn)于使用非唯一索引
range：只檢索給定范圍的行铛碑，使用一個(gè)索引來(lái)選擇行狠裹，一般就是在where語(yǔ)句中出現(xiàn)between, >, < ,in等查詢，這種情況比全表掃描要好
index：index與all的區(qū)別是index遍歷索引汽烦，這比all要快
all：全表掃描 - possible keys 可能被使用到的索引
- key：實(shí)際使用的索引涛菠，若使用了覆蓋索引(查詢列的順序與個(gè)數(shù)與索引列的順序和個(gè)數(shù)一致的情況) 則該索引僅出現(xiàn)在key欄目中，possible keys 中不出現(xiàn)撇吞。
- key_len 其值顯示索引字段的最大可能長(zhǎng)度（字節(jié)數(shù)）俗冻，并非實(shí)際使用長(zhǎng)度，長(zhǎng)度約短越好
- ref 顯示索引的哪一列被使用了梢夯，如果可能的話 是一個(gè)常數(shù)言疗，哪些列或常量被用于查找索引列上的值。
- rows 大致掃描的行數(shù)
- Extra 其他額外很重要的信息(前三條重要 其余看看就行)颂砸。
using filesort:文件排序噪奄，這種情況 mysql會(huì)對(duì)數(shù)據(jù)使用外部的索引排序死姚，而不是根據(jù)表內(nèi)的索引排序進(jìn)行讀取。這種情況很差勤篮，什么時(shí)候會(huì)出現(xiàn)filesort都毒？ 當(dāng)我們使用order by時(shí)，如果用不到索引進(jìn)行排序碰缔，那么mysql會(huì)使用文件排序账劲。
using temporary：使用臨時(shí)表保存中間結(jié)果，mysql對(duì)查詢結(jié)果進(jìn)行排序時(shí)使用臨時(shí)表金抡，常見(jiàn)于order by瀑焦，group by。
using FileSort只是對(duì)數(shù)據(jù)使用外部的索引排序梗肝，但是Using temporary會(huì)將排序后的結(jié)果緩存于創(chuàng)建的一張臨時(shí)表中榛瓮，然后再刪除臨時(shí)表，這種情況相比于sing filesort更差
- using Index：表示相應(yīng)的查詢操作中使用了覆蓋索引（Covering Index）來(lái)獲取結(jié)果巫击，避免查詢表的數(shù)據(jù)行禀晓，效率不錯(cuò)，如果同時(shí)出現(xiàn)using Where坝锰，表明索引被用來(lái)執(zhí)行索引鍵值的查找粹懒，如果沒(méi)有同時(shí)出現(xiàn)Using Where，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找工作 也就是覆蓋索引顷级。
所以如果要使用覆蓋索引凫乖，一般只取select 需要的列 而不用select *，因?yàn)椴豢赡芩凶侄味家黄鹱鏊饕你蛋眩浯?多余字段也會(huì)消耗不必要的網(wǎng)絡(luò)IO拣凹。
- using Where：使用where過(guò)濾條件
- using join buffer：使用了連接緩存（join太多個(gè)表，配置文件里面的JoinBuffer的值可以調(diào)大一點(diǎn)）
- Impossible Where：where子句的值總是false恨豁，不能獲取任何數(shù)據(jù) 比如where 1 = 2
- 優(yōu)化distinct操作，在找到第一個(gè)匹配的數(shù)據(jù)后即停止找同樣值的動(dòng)作
JOIN優(yōu)化爬迟，
CREATE TABLE
class(
idint(11) NOT NULL AUTO_INCREMENT,
cardint(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
CREATE TABLE
book(
bookidint(11) NOT NULL AUTO_INCREMENT,
cardint(10) NOT NULL,
PRIMARY KEY (bookid)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
建立以上兩張表橘蜜，各隨機(jī)插入20條數(shù)據(jù)，
不建立索引時(shí)：兩張表均全表掃描
- 索引建在左表class時(shí),左表使用索引index付呕，但是兩表掃描行數(shù)均為20行
ALTER TABLE book ADD INDEX Y(card);
image.png
- 索引建在右表book時(shí),右表使用索引ref计福，左表掃描20行，右表掃描一行
ALTER TABLE book ADD INDEX Y(card);
image.png
我們知道ref的性能是優(yōu)于index的徽职，并且第二種情況掃描的行數(shù)更少象颖，因此可以得出以下總結(jié)
連表(join)查詢優(yōu)化原則
- 小表驅(qū)動(dòng)大表
- 左連接時(shí)索引應(yīng)建在右表上(如果索引已經(jīng)建立在左表上，那么換成右連接查詢)姆钉，右連接索引應(yīng)建在左表上(如果索引已經(jīng)建立在右表上说订，那么換成左連接查詢),這是由左連接特性決定的抄瓦，左連接時(shí)left join用于確定搜索右表數(shù)據(jù)，左表的數(shù)據(jù)一定有 所以我們需要將索引加在右表上陶冷，右連接則相反钙姊。
- 調(diào)大join buffer設(shè)置
關(guān)于索引的一些原則
-
聯(lián)合索引最左前綴原則:查詢從最左前列開(kāi)始并且不能跳過(guò)中間列， 如果有的中間列使用了返回查詢(大于埂伦，小于 不等于)煞额，那么后面的列將使用不到
聯(lián)合索引的使用原則舉例
以下補(bǔ)充索引對(duì)于排序時(shí)使用的情況，以KEY(a,b,c)為例子
會(huì)使用索引排序沾谜，不會(huì)產(chǎn)生filesort的情況
| 條件 | 備注 |
|---|---|
| order by a或者order by a,b或者order by a,b,c | 符合最左前綴原則 |
| order by a desc,b desc,c desc | 方向一致并且符合最左前綴原則 |
| where a= const order by b,c或者a=const and b=const order by c或者 a=const and b>const order by c | 最左前綴為常量膊毁，則其后面的列相當(dāng)于前綴，這種情況也沒(méi)問(wèn)題 |
不會(huì)使用索引排序基跑，會(huì)產(chǎn)生filesort的情況
| 條件 | 備注 |
|---|---|
| order by a desc,b asc,c desc | 方向不一致 |
| order by b,c或者order by a,c | 不符合最左前綴原則 |
| order by a,b,c,d | d不是索引的列 |
| where a in {...} order by b,c | 對(duì)于排序來(lái)說(shuō)媚媒，多個(gè)條件相當(dāng)于范圍查詢，這種情況也不符合最左前綴原則 |
對(duì)于group by(先排序后分組)其原則與order by幾乎一致涩僻，也是最左原則什么的缭召，注意的是：能在where里面過(guò)濾的就沒(méi)必要在having里過(guò)濾
一些常用法則
- 不要在索引列上做函數(shù)計(jì)算和轉(zhuǎn)換(隱式和顯式都不要)，如：
EXPLAIN SELECT * FROM person t WHERE LEFT(t.name,3) = "abc"
EXPLAIN SELECT * FROM person t WHERE t.name = 12 - <>和!= 會(huì)使索引失效
- is null 和 is not null 也會(huì)導(dǎo)致索引失效
- or 也會(huì)使索引失效
- in 和exists,根據(jù)小表驅(qū)動(dòng)大表原則逆日，當(dāng)department為小表時(shí) in要好于exists嵌巷，當(dāng)employee 為小表是exists要好于in
SELECT * FROM employee WHERE r_id in( SELECT r_id FROM department)相當(dāng)于 先執(zhí)行 SELECT * FROM department 再執(zhí)行SELECT * FROM employee WHERE employee.r_id = department.r_id
SELECT * FROM employee t1 WHERE EXISTS (SELECT 1 FROM department t2 where t1.r_id = t2.r_id ) 相當(dāng)于 SELECT * FROM employee 再執(zhí)行 SELECT * FROM department WHERE employee.r_id = department.r_id
鎖
- 從功能上分：
- 讀鎖(共享鎖)：多個(gè)讀操作之間可以同時(shí)進(jìn)行，不會(huì)互相干擾
- 寫(xiě)鎖(排它鎖)：在當(dāng)前寫(xiě)操作沒(méi)完成前室抽，會(huì)阻斷其他寫(xiě)鎖和讀鎖
- 從粒度上分大致分為
- 行鎖(偏寫(xiě))：
- 表鎖(偏讀)：
對(duì)于myisam(表級(jí)鎖搪哪，不支持事務(wù))：在執(zhí)行查詢語(yǔ)句(select)前，會(huì)自動(dòng)給表加讀鎖坪圾，在執(zhí)行寫(xiě)操作前會(huì)自動(dòng)給表加寫(xiě)鎖
myisam 讀鎖
LOCK TABLE book READ;
SELECT * from book where id =1 ; -- 可以查出結(jié)果
SELECT * FROM test2 where id =1 ; -- 不能查出其他表結(jié)果
INSERT INTO book VALUES (1,"1","1"); -- 讀鎖不能修改數(shù)據(jù)
UNLOCK TABLES ;
對(duì)于其他session
SELECT * from book where id =1 ; -- 可以查出結(jié)果
SELECT * FROM test2 where id =1 ; -- 可以查出結(jié)果
INSERT INTO book VALUES (1,"1","1"); -- 阻塞直到上面的讀鎖被釋放
myisam 寫(xiě)鎖
LOCK TABLE book WRITE;
SELECT * from book where id =1 ; -- 可以讀取結(jié)果
INSERT INTO book VALUES (2,"2","2"); -- 可以更改數(shù)據(jù)
SELECT * FROM test2 where id =1 ; -- 不能獲取其他表的數(shù)據(jù)
UNLOCK TABLES;
對(duì)于其他session
SELECT * FROM test2 where id =1 ; -- 可以查出結(jié)果
SELECT * from book where id =1 ; -- 阻塞 讀寫(xiě)均阻塞
對(duì)于innodb(行級(jí)鎖晓折，支持事務(wù))
session1
SET autocommit = 0; -- 取消自動(dòng)提交
update people SETname= "a" WHERE id = 1; -- 更新1號(hào)記錄
COMMIT; -- 提交
session2
SET autocommit = 0; -- 取消自動(dòng)提交
UPDATE people SETname= "b" WHERE id = 2; -- 更新2號(hào)記錄(因?yàn)閕nnodb使用的是行鎖，因此此時(shí)不阻塞)
COMMIT; -- 提交
注意
innodb使用的是行鎖兽泄，但是行鎖會(huì)升級(jí)到表鎖漓概。使用行鎖的前提是where后面使 用到索引，如果限制列沒(méi)建立索引或者有索引但是未使用到病梢，那么mysql的行鎖將會(huì)升級(jí)到表鎖胃珍。
使用for update 來(lái)鎖定select的行(使用的時(shí)候要注意加限制條件，避免鎖全表)
session1
SET autocommit = 0; -- 取消自動(dòng)提交
SELECT * FROM people WHERE id = 1 FOR UPDATE; -- 鎖定id為1的這一行 蜓陌，COMMIT;
session2
SET autocommit = 0; -- 取消自動(dòng)提交
UPDATE people SETname= "xxx" WHERE id = 1; -- 此時(shí)阻塞觅彰，直到session1 提交才會(huì)暫停阻塞
COMMIT; -- 提交
