Mysql 索引+表连接学习

最新推荐文章于 2023-07-31 16:53:10 发布

cuitchen

最新推荐文章于 2023-07-31 16:53:10 发布

阅读量553

点赞数

文章标签： mysql 索引

本文链接：https://blog.csdn.net/weixin_48232263/article/details/113868408

版权

本文详细介绍了MySQL的表连接类型，包括内连接、外连接（左连接、右连接）及其应用场景，强调了外连接在查找缺失数据时的作用。同时，探讨了索引的重要性，讲解了B+树索引原理，并分析了如何有效利用索引以提高查询效率。此外，通过示例展示了索引的创建、使用和失效情况，以及如何通过EXPLAIN命令分析SQL查询性能。最后，提出了查询优化策略，如合理分配WHERE条件，避免全表扫描等。

摘要由CSDN通过智能技术生成

Mysql 索引+表连接学习

表连接

连接方式

内连接 inner join , join
外连接 left join，left outer join，right join，right outer join，union

模拟表：
在这里插入图片描述

内连接

两张表可以不用连接，直接使用where匹配

SELECT a.*,b.* FROM t_user a,t_class b WHERE a.fk_class_id = b.class_id

inner join 和 join 效果一样，取两个表的交集

SELECT a.*,b.* FROM t_user a JOIN t_class b ON a.fk_class_id = b.class_id;

SELECT a.*,b.* FROM t_user a INNER JOIN t_class b ON b.class_id = a.fk_class_id;

运行结果都相同

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jZLNQEmf-1613726524946)(Mysql 索引+表连接学习.assets/image-20210115101542469.png)]

左连接

left join 和 left outer join 效果相同

查询结果左表会全部显示，右表根据条件进行匹配，若有值，则与左表行对应，若没有，则显示为null

/*
left join
*/
SELECT a.*,b.* FROM t_user a LEFT JOIN t_class b ON a.fk_class_id = b.class_id;

/*
left join + order by
*/
SELECT a.*,b.* FROM t_user a LEFT OUTER JOIN t_class b ON a.fk_class_id = b.class_id ORDER BY b.class_id , a.user_id DESC;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LHpZxO5Q-1613726524947)(Mysql 索引+表连接学习.assets/image-20210115103944951.png)]

右链接

right join 和 right outer join 结果相同

查询结果右表会全部显示，左表根据条件进行匹配，若有值，则与左表行对应，若没有，则显示为null

SELECT a.*,b.* FROM t_user a RIGHT JOIN t_class b ON b.class_id = a.fk_class_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-meZx4Bai-1613726524947)(Mysql 索引+表连接学习.assets/image-20210115104529060.png)]

在之前自己的代码中，几乎不会运用到外连接的方式，那么外连接运用场景是什么？能够实现什么功能？

首先主表的概念，主表会全部查询出来（如果没有where条件），而左表的数据是不全的，例如在右连接中，左表名为廉颇的数据没有查询出来。

假设现在需要找出有哪些班还没有学生，sql语句怎么写

/*
尝试 外连接应用场景
*/
SELECT b.* FROM t_user a RIGHT JOIN t_class b ON b.class_id = a.fk_class_id WHERE b.class_id > 0 AND a.user_id IS NULL;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v6momt5B-1613726524948)(Mysql 索引+表连接学习.assets/image-20210115110313816.png)]

通过where条件，只显示班级id >0 且用户id 为 NULL 时的结果，得出四五班还没有分配学生

提高一点获取每个班的人数

SELECT
	a.class_name,
	COUNT( b.user_id ) AS total_num 
FROM
	t_class a
	LEFT JOIN t_user b ON a.class_id = b.fk_class_id 
GROUP BY
	a.class_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HCqGxgW4-1613726524949)(Mysql 索引+表连接学习.assets/image-20210115124318560.png)]

UNION

用来连接两个 SELECT 语句，将两个查询结果集中相同的数据提取出来，默认有 distinct，也可以用all 显示全部的结果。

需要注意的是

两个select查询的字段数要相同，union是将两个结果上下拼接起来，所以select查询字段的顺序需要注意

SELECT fk_class_id,user_name FROM t_user UNION SELECT class_name,class_id FROM t_class;

SELECT fk_class_id,user_name FROM t_user UNION SELECT class_id,class_name FROM t_class;
/*这两个最后的结果是不一样的*/

多表（>2）

直接连接查询

SELECT a.*,b.*,c.* FROM t_user a RIGHT JOIN t_class b ON a.fk_class_id = b.class_id LEFT JOIN t_course c on a.fk_class_id = c.course_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GXoYWCSZ-1613726524950)(Mysql 索引+表连接学习.assets/image-20210115162351752.png)]

要注意逻辑，到底谁是主表

采用嵌套查询

内连接

SELECT
	a.*,
	b.* 
FROM
	( SELECT a.*, b.* FROM t_user a JOIN t_class b ON a.fk_class_id = b.class_id ) a
	JOIN t_course b ON a.fk_class_id = b.course_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CtlafviS-1613726524951)(Mysql 索引+表连接学习.assets/image-20210115114850443.png)]

内连接很简单，需要完全匹配

外连接

/*
外连接 三张表
*/
SELECT
	a.*,
	b.* 
FROM
	( SELECT a.*, b.* FROM t_user a LEFT JOIN t_class b ON a.fk_class_id = b.class_id ) a
	LEFT JOIN t_course b ON a.fk_class_id = b.course_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vae2k1dJ-1613726524951)(Mysql 索引+表连接学习.assets/image-20210115115133914.png)]

可以把一个select理解为一个最小的操作，嵌套查询也可以拆分为对两个表的查询，只是这里的表可以是一个查询结果集，在实际应用中，要根据业务需要考虑是用嵌套查询还是直接连接（之前遇到过需要用distinct去重以及排序，所以查询的字段以及嵌套逻辑会很复杂）

注意

内连接需要加上 on 条件，如果不加，就是交叉连接，即结果总数=表A总数 * 表B总数，A表每一条数据都会与B表的每一条数据匹配并显示

SELECT a.*,b.* FROM t_user a JOIN t_class b;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-htC7OpHv-1613726524952)(Mysql 索引+表连接学习.assets/image-20210115113702215.png)]

外连接如果不加 ON 则会报错

索引

MySql建立索引能够极大程度的提高检索速度

二叉查找树：任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值

平衡二叉树（ AVL 树）：在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1

B 树（Balance Tree）：即为平衡树的意思，没有二叉

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VIwzhClj-1613726524953)(Mysql 索引+表连接学习.assets/image-20210115144629240.png)]

B+树：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NIsOQJDg-1613726524954)(Mysql 索引+表连接学习.assets/image-20210115144657192.png)]

B+ 树非叶子节点上是不存储数据的，仅存储键值
B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的
B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接

几个概念：

聚集索引

也叫主键索引，一个表只能有一个聚集索引，而且是建立在主键上的，表中的数据是直接存放在叶子节点中的
非聚集索引

也叫二级索引，一个表可以拥有多个非聚集索引，叶子节点寸的是主键的值，所以有时后需要根据得到的主键值再进行回表查询（不进行回表则为覆盖索引）
联合索引

一个索引中可以包含多个字段，需要遵循最左前缀集合（字段1，字段2，字段3），只有当查询条件使用了第一个字段时，索引才会被使用，且从左往右直到范围匹配（比如 between、in）注意like%

InnoDB中主键所建立的是聚集索引，而建立唯一索引、普通索引等都是二级索引

主键一定是唯一索引，唯一索引并不一定就是主键

再细分为普通索引、唯一索引、主键索引、组合索引

CREATE [unique|fulltext][index|key][index_name] ON table (col_name[length])[asc|desc] 

ALTER TABLE `tablename` ADD INDEX name_city_age (xxx,xxx,xxx);

DROP INDEX [indexName] ON `tablename`;

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

索引失效

组合索引未使用最左前缀，where中并不是从该索引的第一个字段开始
like未使用最左前缀，where A like ‘%China’；

以上都是没有满足最左前缀规则

搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引；
where中含有or （但 or 两侧是对同一个字段判断，那么还是生效的）
如果列类型是字符串，要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）；
在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

demo

首先关闭查询缓存

set global query_cache_size=0
set global query_cache_type=0

show variables like '%cache%'

创建测试表

create table test_order
(
    id int auto_increment primary key,
    user_id int,
    order_id int,
    order_status tinyint,
    create_date datetime
);
create table test_orderdetail
(
    id int auto_increment primary key,
    order_id int,
    product_name varchar(100),
    cnt int,
    create_date datetime
);
create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
create index idx_orderid_productname on test_orderdetail(order_id,product_name);

创建存储过程，用来批量生成数据

CREATE DEFINER=`chen.study`@`%` PROCEDURE `test`(`loopcount` int(11))
BEGIN
DECLARE v_uuid  varchar(50);
DECLARE v_orderId int;
    while loopcount>0 do
        set v_uuid = uuid();
		SET v_orderId=rand()*100000;
        insert into test_order (user_id,order_id,order_status,create_date) values (rand()*1000,v_orderId,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        insert into test_orderdetail(order_id,product_name,cnt,create_date) values (v_orderId,v_uuid,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        set loopcount = loopcount -1;
    end while;
END

编写查询语句

/*
没有索引
*/
EXPLAIN SELECT
	a.*,
	b.* 
FROM
	test_order_copy1 a
	LEFT JOIN test_orderdetail_copy1 b ON a.order_id = b.order_id 
WHERE
	a.user_id = 225 
	AND a.order_id = 69416;

/*
有索引
*/
EXPLAIN SELECT
	a.*,
	b.* 
FROM
	test_order a
	LEFT JOIN test_orderdetail b ON a.order_id = b.order_id 
WHERE
	a.user_id = 225 
	AND a.order_id = 69416;

（_copy1 表示没有建立其他索引，只有主键索引）

执行select ......where a.user_id = 225 AND a.order_id = 69416;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jSWm2CRj-1613726524955)(Mysql 索引+表连接学习.assets/image-20210115165425497.png)]

没有索引时间为0.413s，但是用explain命令查看

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NZAo7Ux4-1613726524956)(Mysql 索引+表连接学习.assets/image-20210115165526293.png)]

rows 为10035

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DY4MYzhr-1613726524957)(Mysql 索引+表连接学习.assets/image-20210115165751820.png)]

查询时间为0.478，二者相差不大，但是有索引时，预计查询的行为 1；

执行select ......where a.user_id = 225;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NRNjNoFv-1613726524958)(Mysql 索引+表连接学习.assets/image-20210115170307664.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nn8LB8O0-1613726524958)(Mysql 索引+表连接学习.assets/image-20210115170328442.png)]

通过两张图发现，创建索引能够明显提升查询效率，而且有索引 explain rows为107，明显小于没有索引的表

索引失效

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pqSple53-1613726524959)(Mysql 索引+表连接学习.assets/image-20210115174546192.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yO3odevd-1613726524960)(Mysql 索引+表连接学习.assets/image-20210115174612550.png)]

因为所以第一个字段为user_id，而where中为 order_id，所以为满足条件，索引失效

但是，当

WHERE a.order_id = 69416 AND a.user_id = 225;

时，索引依然使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5jVP1Njs-1613726524961)(Mysql 索引+表连接学习.assets/image-20210115174818083.png)]

说明mysql在执行时，会优化sql语句

explain命令

https://www.cnblogs.com/tufujie/p/9413852.html

https://www.cnblogs.com/dwlovelife/p/11110215.html#4-%E6%80%A7%E8%83%BD%E5%88%86%E6%9E%90explain

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。再看key列，看是否使用了索引,null代表没有使用索引。然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时越长，最后看Extra列，在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现，这些是很影响数据库性能的。

优化

建立索引，提高查询效率

联查的时候，可以合理的分配where条件，例如先查询出来10W条数据，再进行where筛选；相比于，在每个子查询中都预先用where过滤，查询结果的总条数就会大大减少，进而，再次进行筛选时，基数就会小很多

具体的也要根据实际业务去做优化，不能一味的建立索引，因为索引会占用空间
order by limit 形式的sql语句让排序的表优先查
尽量避免select *，只查询需要的数据
避免子查询，通过连接来实现

可以通过explain命令查看语句执行情况，再进行调整

总结

在数据量大时，根据业务需求建立索引，但是索引也不是随便建，特别是联合索引，字段顺序都需要考虑到，其次，在设计查询语句的时候，不能单纯的把where limit order by 放在后面，要根据查询情况，放在合适的地方，减少基数，从而减少查询次数，进行优化。

数据量越大，索引效果越明显，感觉联查的时候会更明显，但是要注意空间问题，数据量越大那么索引占得空间也越大，所以需要综合考虑确定索引建立的字段。

问题

在上面的demo中，where如果限定为两个，那么查询速度两个相差不大，因为用的云数据库，网络原因？索引设计上，是不是可以理解为user_id 和 order_id 一起查的时候，在B+树中查询的结点树是相同的？在这里插入图片描述

cuitchen

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql 索引+表连接学习

Mysql 索引+表连接学习表连接连接方式内连接 inner join , join外连接 left join，left outer join，right join，right outer join，union模拟表：内连接两张表可以不用连接，直接使用where匹配SELECT a.*,b.* FROM t_user a,t_class b WHERE a.fk_class_id = b.class_id inner join 和 join 效果一样，取两个表的交集
复制链接

扫一扫