一、7中join查詢

1、Join圖

2、建表語句

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;

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);

3、7種join語句

1 A、B兩表共有

select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

2 A、B兩表共有+A的獨(dú)有

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

3 A、B兩表共有+B的獨(dú)有

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

4 A的獨(dú)有

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

5 B的獨(dú)有

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null; #B的獨(dú)有

6 AB全有

#MySQL Full Join的實(shí)現(xiàn) 因?yàn)镸ySQL不支持FULL JOIN,下面是替代辦法

#left join + union(可去除重復(fù)數(shù)據(jù))+ right join

SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id

UNION

SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id

7 A的獨(dú)有+B的獨(dú)有

SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id WHERE B.`id` IS NULL

UNION

SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

二、索引簡(jiǎn)介

1、官方定義

索引(Index)是贊助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu).

可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu).

2、排好序的快速查找數(shù)據(jù)結(jié)構(gòu)

在數(shù)據(jù)之外,數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu),這些數(shù)據(jù)結(jié)構(gòu)以某種特定方式指向數(shù)據(jù).

這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法,這些數(shù)據(jù)結(jié)構(gòu)就是索引.如下圖：

左邊是數(shù)據(jù)表,一共有兩列七條記錄,最左邊的是數(shù)據(jù)記錄的物理地址

為了加快Col2的查找,可以維護(hù)一個(gè)右邊所示的二叉查找樹,每個(gè)節(jié)點(diǎn)分別包括索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針,這樣就可以運(yùn)用二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù),從而快速的檢索出符合條件的記錄.

3、我們平常所說的索引,如果沒有特別指明,都是指B樹(多路搜索樹,并不一定是二叉的)結(jié)構(gòu)組織的索引.其中聚集索引,次要索引,覆蓋索引,復(fù)合索引,前綴索引,唯一索引默認(rèn)都是使用B+樹索引,統(tǒng)稱索引.當(dāng)然,除了B+樹這種類型的索引之外,還有哈稀索引(hash index)等.

4、優(yōu)勢(shì)

• 提高數(shù)據(jù)檢索的效率,降低數(shù)據(jù)庫(kù)IO成本.

• 通過索引列對(duì)數(shù)據(jù)進(jìn)行排序,降低數(shù)據(jù)排序的成本,降低了CPU的消耗.

5、劣勢(shì)

雖然索引大大提高了查詢速度,同時(shí)卻會(huì)降低更新表的速度,如對(duì)表進(jìn)行INSERT、UPDATE和DELETE. 因?yàn)楦卤頃r(shí),MySQL不僅要保存數(shù)據(jù),還要保存一下索引文件每次更新添加了索引列的字段, 都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息.

6、索引分類

• 單值索引

一個(gè)索引只包括單個(gè)列,一個(gè)表可以有多個(gè)單列索引.

• 復(fù)合索引

一個(gè)索引包括多個(gè)列.

• 唯一索引

索引列的值必須唯一,但可以有空值.

7、基本語法

• 創(chuàng)建

CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length));

注意：如果是CHAR,VARCHAR類型,length可以小于字段實(shí)際長(zhǎng)度； 如果是BLOB和TEXT類型,必須指定length.

• 刪除

DROP INDEX [indexName] ON mytable;

• 查看

SHOW INDEX FROM table_name\G;

使用ALTER命令創(chuàng)建索引

有四種方式來添加數(shù)據(jù)表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個(gè)主鍵,這意味著索引值必須是唯一的,且不能為NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句創(chuàng)建索引的值必須是唯一的(除了NULL外,NULL可能會(huì)出現(xiàn)多次).

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出現(xiàn)多次.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為 FULLTEXT ,用于全文索引.

8、mysql索引結(jié)構(gòu)

• BTree索引原理

【初始化介紹】

一顆b+樹,淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊,可以看到每個(gè)磁盤塊包括幾個(gè)數(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)不存儲(chǔ)真實(shí)的數(shù)據(jù),只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng),如17、35并不真實(shí)存在于數(shù)據(jù)表中.

【查找過程】

如果要查找數(shù)據(jù)項(xiàng)29,那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存,此時(shí)發(fā)生一次IO,在內(nèi)存中用二分查找確定29在17和35之間,鎖定磁盤塊1的P2指針,內(nèi)存時(shí)間因?yàn)榉浅６?相比磁盤的IO)可以忽略不計(jì),通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存,發(fā)生第二次IO,29在26和30之間,鎖定磁盤塊3的P2指針,通過指針加載磁盤塊8到內(nèi)存,發(fā)生第三次IO,同時(shí)內(nèi)存中做二分查找找到29,結(jié)束查詢,總計(jì)三次IO.

真實(shí)的情況是,3層的b+樹可以表示上百萬的數(shù)據(jù),如果上百萬的數(shù)據(jù)查找只需要三次IO,性能提高將是巨大的,如果沒有索引,每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO,那么總共需要百萬次的IO,顯然成本非常非常高.

9、其他索引

• Hash索引

• full-text索引

• R-Tree索引

10、哪些情況需要?jiǎng)?chuàng)建索引

• 主鍵自動(dòng)建立唯一索引

• 頻繁作為查詢條件的字段

• 查詢中與其他表關(guān)聯(lián)的字段,外鍵關(guān)系創(chuàng)建索引

• 在高并發(fā)下創(chuàng)建組合索引

• 查詢中排序的字段,排序字段若通過索引去拜訪將大大提高排序速度

• 查詢中統(tǒng)計(jì)或分組字段

11、哪些情況不用建立索引

• 表記錄太少

• 經(jīng)常增刪改的表

提高了查詢速度,同時(shí)卻會(huì)降低更新表的速度,如對(duì)表進(jìn)行INSERT、UPDATE和DELETE.

因?yàn)楦卤頃r(shí),MySQL不僅要保存數(shù)據(jù),還要保存一下索引文件

• 數(shù)據(jù)重復(fù)且分布平均的表字段,因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引. 注意,如果某個(gè)數(shù)據(jù)列包括許多重復(fù)的內(nèi)容,為它建立索引就沒有太大的實(shí)際效果.