ElasticSearch

索引案例二:两表优化案例

2017-03-01  本文已影响20人  编程界的小学生

建表SQL

CREATE TABLE IF NOT EXISTS class(
    id INT (10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    card INT(10) UNSIGNED NOT NULL
);

CREATE TABLE IF NOT EXISTS book(
    bookid INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    card INT(10) UNSIGNED NOT NULL
);

INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO class(card) VALUE(FLOOR(1 + RAND() * 20));

INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));
INSERT INTO book(card) VALUE(FLOOR(1 + RAND() * 20));

LEFT JOIN

Paste_Image.png Paste_Image.png Paste_Image.png

给左边表(class)加索引出现如下:

Paste_Image.png
可以发现rows检索的行数没变。而且是index索引,索引全部。

给由边表(book)加索引出现如下:

Paste_Image.png
可发现只检索1行,而且是ref,效率极高。
所以加到右表(book)最为合适。

RIGHT JOIN

3.png

发现结果正好相反。

得出结论 :若为左连接,则把索引加到第二张表上的连接字段。若为右连接,则把索引加到第一张表的连接字段。

若有兴趣,欢迎来加入群,【Java初学者学习交流群】:458430385,此群有Java开发人员、UI设计人员和前端工程师。有问必答,共同探讨学习,一起进步!
欢迎关注我的微信公众号【Java码农社区】,会定时推送各种干货:


qrcode_for_gh_577b64e73701_258.jpg
上一篇 下一篇

猜你喜欢

热点阅读