专栏首页JavaEEMySQL连接查询&索引介绍

MySQL连接查询&索引介绍

一、常见的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. 索引的优势:

  • 提高数据检索效率,降低了数据库的IO成本;
  • 通过索引列队数据进行排序,降低了数据排序的成本,降低了CPU的消耗。

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索引。

B树索引

首先从根节点开始,如果比17小,就往p1所指区域找,如果比35大,就往p3所指区域找,如果介于17和35之间,就往p2所指区域找。

7. 哪些情况适合建索引?

  • 主键自动建立唯一索引;
  • 频繁作为查询条件的字段应该建索引;
  • 查询中与其他表关联的字段,应建立外键索引;
  • 频繁更新的字段不应该建立索引;
  • where条件里用不到的字段不应该建索引;
  • 查询中排序的字段应该建立索引;
  • 查询中统计或者分组的字段应该建立索引。

8. 哪些情况不适合建索引:

  • 记录太少的表不适合建索引;
  • 经常增删改的表不适合建索引;
  • 如果某个列的数据很多重复的内容,比如表示状态的status列,不是0就是1,这样的就没必要建索引;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL连接查询索引优化

    首先没有使用索引,type是all,然后用了文件内排序,using filesort。这两个都是严重影响性能的,那么接下来就建索引。

    贪挽懒月
  • mysql连接查询

    以前查询都是随便查到结果就行了,因为发现每次查询的数量都很少,当然现在也是。不过效率一直是程序员执着的追求,我就多了解下差距。 首先是多张表联合,一张模板种类c...

    Ryan-Miao
  • MySQL 多表查询、连接查询(内连接、外连接)

    查询的结果是一个二维表,它是students表和classes表的“乘积”,即students表的每一行与classes表的每一行都两两拼在一起返回

    Michael阿明
  • MySQL索引介绍

    索引:是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

    HLee
  • [Go] golang连接查询mysql

    陶士涵
  • mysql的查询、子查询及连接查询

    一、mysql查询的五种子句 where(条件查询)、having(筛选)、group by(分组)、order by(排序)、limit(限制...

    java达人
  • mysql连接查询(左连接,右连接,内连接)

    以上就是MySQL 连接查询的资料整理,大家如有疑问可以留言讨论,共同进步,感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

    叫我可儿呀
  • MySQL查询索引分析

    前一段时间修改数据表时,给一个表添加一个datetime字段,当时遇到了一个问题:我是否需要给该datetime字段上加索引呢?如果不给该字段加索引,当wher...

    kevindang
  • MySQL数据库——多表查询之内连接查询、外连接查询、子查询

    多表查询就是从多个表中进行数据的查询操作,语法:select  列名列表 from 表名的列表  where...

    Winter_world

扫码关注云+社区

领取腾讯云代金券