Mysql中實(shí)現(xiàn)join聯(lián)表與自增id的方法

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)Mysql中實(shí)現(xiàn)join聯(lián)表與自增id的方法，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

join的寫法

如果用left join 左邊的表一定是驅(qū)動(dòng)表嗎？?jī)蓚€(gè)表的join包含多個(gè)條件的等值匹配，都要寫道on還是只把一個(gè)寫到on，其余寫道where部分？

create	table	a(f1	int,	f2	int,	index(f1))engine=innodb;
 create	table	b(f1	int,	f2	int)engine=innodb;
 insert	into	a	values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
 insert	into	b	values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
select	*	from	a	left	join	b	on(a.f1=b.f1)	and	(a.f2=b.f2);	/*Q1*/
 select	*	from	a	left	join	b	on(a.f1=b.f1)	where	(a.f2=b.f2);/*Q2*/

執(zhí)行結(jié)果：

由于表b沒(méi)有索引，使用的是Block Nexted Loop Join(BNL)算法

• 把表a的內(nèi)容讀入join_buffer中，因?yàn)閟elect * ，所以字段f1，f2都被放入
• 順序掃描b，對(duì)于每一行數(shù)據(jù)，判斷join條件是否滿足，滿足條件的記錄，作為結(jié)果集的一行，如果有where子句，判斷where部分滿足條件后再返回。
• 表b掃描完成后，對(duì)于沒(méi)有匹配的表a的行，用null補(bǔ)上，放到結(jié)果集中。

Q2語(yǔ)句中，explain結(jié)果：

b為驅(qū)動(dòng)表，如果一條語(yǔ)句EXTRA字段什么都沒(méi)有的話，就是Index Nested_Loop Join算法，因此流程是：

順序掃描b，每一行用b.f1到a中去查，匹配a.f2=b.f2是否滿足，作為結(jié)果集返回。

Q1與Q2執(zhí)行流程的差異是因?yàn)閮?yōu)化器基于Q2這個(gè)查詢語(yǔ)義做了優(yōu)化：在mysql里，null跟任何值執(zhí)行等值判斷和不等值判斷的結(jié)果都是null，包括select null = null 也返回null。

在Q2中，where a.f2 = b.f2表示，查詢結(jié)果里不會(huì)包含b.f2是null的行，這樣left join語(yǔ)義就是找到兩個(gè)表里f1 f2對(duì)應(yīng)相同的行，如果a存在而b匹配不到，就放棄。因此優(yōu)化器把這條語(yǔ)句的left join改寫成了join，因?yàn)閍的f1有索引，就把b作為驅(qū)動(dòng)表，這樣可以用NLJ算法，所以在使用left join時(shí)，左邊的表不一定是驅(qū)動(dòng)表。

如果需要left join的語(yǔ)義，就不能把被驅(qū)動(dòng)表的字段放在where條件里做等值判斷或不等值判斷，必須寫在on里面。

Nested Loop Join的性能問(wèn)題

BLN算法的執(zhí)行邏輯

• 將驅(qū)動(dòng)表的數(shù)據(jù)全部讀入join_buffer中，里面是無(wú)序數(shù)組。
• 順序遍歷被驅(qū)動(dòng)表的所有行，每一行都跟join_buffer做匹配，成功則作為結(jié)果集的一部分返回。

Simple Nested Loop Join算法邏輯是：順序去除驅(qū)動(dòng)表的每一行數(shù)據(jù)，到被驅(qū)動(dòng)表做全表匹配。

兩者差異：

在對(duì)被驅(qū)動(dòng)表做全表掃描時(shí)，如果數(shù)據(jù)沒(méi)有在buffer pool中，需要等待部分?jǐn)?shù)據(jù)從磁盤讀入。會(huì)影響正常業(yè)務(wù)的buffer pool命中率，而且會(huì)對(duì)被驅(qū)動(dòng)表做多次訪問(wèn)，更容易將這些數(shù)據(jù)頁(yè)放到buffer pool頭部。所以BNL算法性能會(huì)更好。自增id

mysql中自增id定義了初始值，不停的增長(zhǎng)，但是有上限，2^32-1，自增的id用完了會(huì)怎么樣呢。

表定義的自增值達(dá)到上限后，再申請(qǐng)下一個(gè)id時(shí)，得到的值保持不變。再次插入時(shí)會(huì)報(bào)主鍵沖突錯(cuò)誤。所以在建表時(shí)，如果有頻繁的增刪改時(shí)，就應(yīng)該創(chuàng)建8個(gè)字節(jié)的bigint unsigned。

innodb 系統(tǒng)自增row_id

如果創(chuàng)建了Innodb表沒(méi)有指定主鍵，那么innodb會(huì)創(chuàng)建一個(gè)不可見(jiàn)的，長(zhǎng)度為6個(gè)字節(jié)的row_id，所有無(wú)主鍵的innodb表，每插入一行數(shù)據(jù)，都將當(dāng)前的dict_sys.row_id值作為要插入數(shù)據(jù)的row_id，然后自增1。

實(shí)際上，代碼實(shí)現(xiàn)時(shí)，row_id是一個(gè)長(zhǎng)度為8字節(jié)的無(wú)符號(hào)長(zhǎng)整形，但是innodb在設(shè)計(jì)時(shí)，給row_id只是6個(gè)字節(jié)的長(zhǎng)度，這樣寫道數(shù)據(jù)時(shí)只放了最后6個(gè)字節(jié)。所以：

• row_id寫入表的范圍是0到2^48-1;
• 當(dāng)達(dá)到最大時(shí)，如果再有插入數(shù)據(jù)的行為來(lái)申請(qǐng)row_id，拿到以后再去最后6個(gè)字節(jié)就是0，然后繼續(xù)循環(huán)。
• 再innodb的邏輯里，達(dá)到最大后循環(huán)，新數(shù)據(jù)會(huì)覆蓋已經(jīng)存在的數(shù)據(jù)。

從這個(gè)角度看，我們應(yīng)該主動(dòng)創(chuàng)建自增主鍵，這樣達(dá)到上限后，插入數(shù)據(jù)會(huì)報(bào)錯(cuò)。數(shù)據(jù)的可靠性會(huì)更加有保障。

XID

redo log 和 binlog相互配合的時(shí)候，它們有一個(gè)共同的字段就是xid，在mysql中對(duì)應(yīng)事務(wù)的。xid最大時(shí)2^64次方，用盡只存在理論。

thread_id

系統(tǒng)保存了全局變量thread_id_counter，每新建一個(gè)連接，就將thread_id_counter賦值給這個(gè)新連接的線程變量。thread_id_counter定義的大小是4個(gè)字節(jié)，因此到2^32-1就會(huì)重置為0，然后繼續(xù)增加。但是show processlist里不會(huì)看到兩個(gè)相同的thread_id，這是因?yàn)閙ysql設(shè)計(jì)了一個(gè)唯一數(shù)組邏輯，給新線程分配thread_id的時(shí)候：

do	{
 		new_id=	thread_id_counter++;
 }	while	(!thread_ids.insert_unique(new_id).second);

上述就是小編為大家分享的Mysql中實(shí)現(xiàn)join聯(lián)表與自增id的方法了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。