Mysql實(shí)戰(zhàn)-為什么索引要建立在被驅(qū)動表上
前面我們講解了B+Tree的索引結(jié)構(gòu),也詳細(xì)講解下 left Join的底層驅(qū)動表 選擇原理,那么今天我們來看看到底如何用以及如何建立索引和索引優(yōu)化
開始之前我們先提一個問題, 為什么索引要建立在被驅(qū)動表上 ?
1.建表及測試數(shù)據(jù)
我們先創(chuàng)建兩個表 test_user 和 test_order 這兩個表作為我們的測試表及測試數(shù)據(jù)
- test_user 5條數(shù)據(jù), 索引只有主鍵id
- test_order 5條數(shù)據(jù),索引同樣也只有主鍵id
#創(chuàng)建表 test_user
CREATE TABLE `test_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`id_card` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份證ID',
`user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用戶名字',
`age` int DEFAULT NULL COMMENT '年齡',
PRIMARY KEY (`id`),
KEY `idx_age` (`age`),
KEY `idx_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表'
#創(chuàng)建表 test_order
CREATE TABLE `test_order` (
`id` int NOT NULL AUTO_INCREMENT,
`order_name` varchar(32) NOT NULL DEFAULT '',
`user_name` varchar(32) NOT NULL,
`pay` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='訂單表'
插入數(shù)據(jù)
#插入 user 用戶數(shù)據(jù)
INSERT INTO `test`.`test_user` (`id`, `id_card`, `user_name`, `age`) VALUES (1, '11', 'aa', 10);
INSERT INTO `test`.`test_user` (`id`, `id_card`, `user_name`, `age`) VALUES (2, '22', 'bb', 20);
INSERT INTO `test`.`test_user` (`id`, `id_card`, `user_name`, `age`) VALUES (3, '33', 'cc', 30);
INSERT INTO `test`.`test_user` (`id`, `id_card`, `user_name`, `age`) VALUES (4, '44', 'dd', 40);
INSERT INTO `test`.`test_user` (`id`, `id_card`, `user_name`, `age`) VALUES (5, '55', 'ee', 50);
#插入 order 訂單數(shù)據(jù)
INSERT INTO `test`.`test_order` (`id`, `order_name`, `user_name`, `pay`) VALUES (1, '衣服', 'aa', 100);
INSERT INTO `test`.`test_order` (`id`, `order_name`, `user_name`, `pay`) VALUES (2, '鞋子', 'bb', 200);
INSERT INTO `test`.`test_order` (`id`, `order_name`, `user_name`, `pay`) VALUES (3, '電視', 'cc', 300);
INSERT INTO `test`.`test_order` (`id`, `order_name`, `user_name`, `pay`) VALUES (4, '零食', 'cc', 400);
INSERT INTO `test`.`test_order` (`id`, `order_name`, `user_name`, `pay`) VALUES (5, '衣服', 'cc', 500);
查詢結(jié)果
2. 不用連接查詢 笛卡爾積
我們先不用 join語句, 直接查詢2個表,看下效果
#直接查詢2個表
select * from test_user,test_order;
得到的解雇i就是 笛卡爾積
- user表中的每一條記錄,都與order表的一條記錄形成組合
- user中有5條數(shù)據(jù),order表中也有5條數(shù)據(jù)
- user 的 第一條,分別和 order 5條對應(yīng)
- 從而倆個表連接后就有 5 * 5 =25條記錄
查詢結(jié)果笛卡爾積, 25條結(jié)果
3.帶條件的查詢過程即被驅(qū)動表的查詢過程
上面我們見識到了 如果沒有任何條件,我們連接的2個表會形成笛卡爾積,數(shù)量膨脹很大,所以 我們在連接的時候一般都需要過濾條件,我們加一些條件,看下效果
#帶條件的 笛卡爾積查詢
select * from test_user,test_order where test_user.id > 1 and test_user.id = test_order.id and test_order.pay >200 ;
執(zhí)行結(jié)果如下, 只有3條
查詢條件如下
- test_user.id > 1
- test_user.id = test_order.id
- test_order.pay > 200
- 首先 id > 1, 就只剩下 user2,3,4,5
- 然后test_user.id = test.order.id 這樣子就會把很多笛卡爾積 全部去掉, 只保留 兩個表 id相同的記錄, 還是user的 2,3,4,5
- 最后還有個 pay>200, 這樣就通過掉了 user=2這一條 pay=200, 只保留 3,4,5
- 也就是我們要的查詢結(jié)果
我們來分析下執(zhí)行過程
- 確定驅(qū)動表,我們先假設(shè) user表是驅(qū)動表,然后分析下執(zhí)行過程
- 根據(jù)查詢條件 test_user.id >1 ,如果 id不是主鍵, 而且也沒索引, 那就是全表掃描ALL, 找到4條記錄 user_id = 2,3,4,5
- 根據(jù)上面驅(qū)動表的數(shù)據(jù)(前面假設(shè)是 user), 然后從被驅(qū)動表 test_order中尋找匹配的記錄,也就是 user_id =2,3,4,5 和 test_user.id = test_order.id匹配的記錄
- 此時開始查詢 test_order,當(dāng)匹配第一條 test_user.id = 2時, 簡化查詢條件 test_user.id = test_order.id 就變成了 test_order.id = 2 并且還剩余 一個查詢條件 test_order.pay > 200
- 所以 test_order 的表就變成了單表查詢, 兩個查詢條件 test_order.id = 2 and test_order.pay >200, 執(zhí)行test_order的單表查詢,查詢結(jié)果不滿足,因?yàn)?test_order.id =2 的 pay=200,不pay >200的條件, 本次結(jié)束, 繼續(xù)
- 開始下一次 當(dāng) user_id =3時, test_order的單表查詢變成了 test_order.id =3 and test_order.pay > 200,進(jìn)行查詢, 滿足條件,返回結(jié)果
- 依次類推,直到 user_id 的記錄3,4,5匹配完畢 ,最終得到 3條記錄
- 這就是查詢過程
從上面的過程中,我們可以知道,驅(qū)動表 只訪問了一次
但是被驅(qū)動表 要匹配記錄,需要不停的去查詢,匹配,被動表訪問了很多很多次
所以 這就是為什么要把索引建立在被驅(qū)動表上的原因文章來源:http://www.zghlxwxcb.cn/news/detail-721687.html
至此,我們通過Mysql的執(zhí)行查詢過程,分析了解到了索引要建立在被驅(qū)動表上的原理,這對于我們后期進(jìn)行SQL分析,有著重要的作用文章來源地址http://www.zghlxwxcb.cn/news/detail-721687.html
到了這里,關(guān)于MSQL系列(十二) Mysql實(shí)戰(zhàn)-為什么索引要建立在被驅(qū)動表上的文章就介紹完了。如果您還想了解更多內(nèi)容,請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章,希望大家以后多多支持TOY模板網(wǎng)!