MySQL连接查询&索引介绍

贪挽懒月

发布于 2021-04-12 17:27:24

2.3K0

发布于 2021-04-12 17:27:24

文章被收录于专栏：JavaEEJavaEE

一、常见的join查询：

join太多张表，也会导致查询速度变慢。下面就来分析一下join语句。

1. 执行顺序：

我们写一条join查询语句一般都是：

select * from tableA left join tableB on <join condition> where <select condition>

但是MySQL执行的时候，并不是按顺序执行的，MySQL执行sql语句是从from开始执行的，上面这条语句的执行顺序是：

from tableA
on <join condition>
left join tableB
where <select condition>

2. 内连接inner join：

连接查询

如图所示，A和B分别代表两张表，C是它们共同的部分，inner join查出来的就是C，即表A和表B的共同部分。

select * from A inner join B on A.key = B.key

3. 左连接left join：

还是上面那张图，A是左表的独有部分，C是AB的共有部分，left join就是左表的独有加上两表的共有，即左表的全部。所以left join查出来的是A表的全部。

select * from A left join B on A.key = B.key

4. 右连接right join：

右连接就是以右表为主，right join查出来的就是B的全部。

select * from A right join B on A.key = B.key

5. 只查询A的独占部分：

查询A的全部就是左连接，那么查询A独占就是：

select * from A left join B on A.key = B.key where B.key is null;

6. 只查询B的独占部分：

select * from A right join B on A.key = B.key where A.key is null;

7. 全连接：

全连接就是查询 A + B + C，即图中所有部分：

select * from A full outer join B on A.key = B.key;

8. 查询A + B的独占部分，即排除C：

select * from A full outer join B on A.key = B.key where A.key is null or B.key is null;

1. 是什么？

简单地说，索引是一种数据结构，帮助sql高效获取数据的一种数据结构，可类比字典的索引，可理解为排好序的快速查找的数据结构。所以索引会影响where后面的条件以及order by排序。

索引图示

比如现在要查找col2为91的这条记录，首先找到34，发现91比它大，往它的右边找，找到89，91还是比它大，继续往89右边找，就找到了91。

一般来说索引本身也很大，不会全部存储在内存中，因此索引往往以索引文件的形式存储在硬盘上。

2. 索引的优势：

3. 索引的劣势：

4. 索引分类：

假如现有一张user表，有id、name、email等字段。

单值索引：就是用一张表的某一个字段做索引，比如：create index idx_user_name on user(name)，这就是在name字段上建了一个单值索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：用表的某几个字段做索引，比如：create index idx_user_nameEmail on user(name, email)，表示用name和email这两个字段来建立索引。

那么问题来了，我分别在name和email上建单值索引，和建立一个name和email的复合索引，有什么区别？这两种方式都可以达到效果，但是复合索引的开销更小，因此建一个name和email的复合索引，比在name和email上分别建单值索引更优。

5. 基本语法：

创建：create [unique] index indexName on myTable(columnName(length)); 或者 alter myTable add [unique] index indexName on (columnName(length));
删除：drop index indexName on myTable;
查看：show index from myTable;

6. mysql的索引结构：

MySQL的索引总共有四种，分别是BTree索引、Hash索引、full-text全文索引和R-tree索引，最常用的就是Btree索引。