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í)行過程

1. 確定驅(qū)動表，我們先假設(shè) user表是驅(qū)動表，然后分析下執(zhí)行過程
2. 根據(jù)查詢條件 test_user.id >1 ,如果 id不是主鍵， 而且也沒索引， 那就是全表掃描ALL， 找到4條記錄 user_id = 2，3，4，5
3. 根據(jù)上面驅(qū)動表的數(shù)據(jù)(前面假設(shè)是 user)， 然后從被驅(qū)動表 test_order中尋找匹配的記錄，也就是 user_id =2，3，4，5 和 test_user.id = test_order.id匹配的記錄
4. 此時開始查詢 test_order，當(dāng)匹配第一條 test_user.id = 2時， 簡化查詢條件 test_user.id = test_order.id 就變成了 test_order.id = 2 并且還剩余 一個查詢條件 test_order.pay > 200
5. 所以 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ù)
6. 開始下一次 當(dāng) user_id =3時， test_order的單表查詢變成了 test_order.id =3 and test_order.pay > 200，進(jìn)行查詢， 滿足條件，返回結(jié)果
7. 依次類推，直到 user_id 的記錄3，4，5匹配完畢 ，最終得到 3條記錄
8. 這就是查詢過程

從上面的過程中，我們可以知道，驅(qū)動表 只訪問了一次
但是被驅(qū)動表 要匹配記錄，需要不停的去查詢，匹配，被動表訪問了很多很多次
所以 這就是為什么要把索引建立在被驅(qū)動表上的原因

至此，我們通過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)！