图文详解MySQL中两表关联的连接表如何创建索引

本文介绍了MySQL中两表关联的连接表是如何创建索引的相关内容,分享出来供大家参考学习,下面来看看详细的介绍:

问题介绍

创建数据库的索引,可以选择单列索引,也可以选择创建组合索引。

遇到如下这种情况,用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来,如下图所示:


表间关系

问题就是,在这个关联表中该如何建立索引呢?

针对该表,有如下四种选择:

  • 针对于user_uuid建立单列索引idx_user
  • 针对于user_dept建立单列索引idx_dept
  • 建立组合索引idx_user_dept,即(user_uuid,dept_uuid)
  • 建立组合索引idx_dept_user,即(dept_uuid,user_uuid)

对关联表的查询,有如下四种情况:

-- 一、人员查所属部门用and方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d,org_user u,org_dept_user duser WHERE u.user_uuid=duser.user_uuid AND d.dept_uuid=duser.dept_uuid AND u.user_code="dev1";
-- 二、人员查所属部门用join方式
EXPLAIN SELECT d.dept_name,u.* FROM org_user u LEFT JOIN org_dept_user du ON u.user_uuid=du.user_uuid LEFT JOIN org_dept d ON du.dept_uuid=d.dept_uuid WHERE u.user_code="dev1";
-- 三、部门查人员用and方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d,org_user u,org_dept_user du WHERE u.user_uuid=du.user_uuid AND d.dept_uuid=du.dept_uuid AND d.dept_code="D006";
-- 四、部门查所属人员用join方式
EXPLAIN SELECT d.dept_name,u.* FROM org_dept d LEFT JOIN org_dept_user du ON d.dept_uuid=du.dept_uuid LEFT JOIN org_user u ON u.user_uuid=du.user_uuid WHERE d.dept_code="D006";

测试验证

一.人员查所属部门用and方式

1.1 关联表无索引

1.2 单索引 Idx_dept

1.3 单索引 Idx_user

1.4 组合索引 Idx_dept_user

1.5 组合索引 Idx_user_dept

1.6 所有都建立上

二 、人员查所属部门用join方式

2.1 关联表无索引

2.2 单索引 Idx_dept

2.3 单索引 Idx_user

2.4 组合索引 Idx_dept_user

2.5 组合索引 Idx_user_dept

2.6 所有都建立上

三 、部门查人员用and方式

3.1 关联表无索引

3.2 单索引 Idx_dept

3.3 单索引 Idx_user

3.4 组合索引 Idx_dept_user

3.5 组合索引 Idx_user_dept

3.6 所有都建立上

四 、部门查所属人员用join方式

4.1 关联表无索引

4.2 单索引 Idx_dept

4.3 单索引 Idx_user

4.4 组合索引 Idx_dept_user

4.5 组合索引 Idx_user_dept

4.6 所有都建立上

结论

通过上面的实际测试结果可以得出如下结论:针对于该关联表分别针对于user_uuid与dept_uuid建立单列索引idx_user,idx_dept最优。

其中索引idx_user适用与通过人员ID查询出该人员所在的部门;索引idx_dept适用与通过部门查询出该部门下所属的人员。

其它

测试数据

Test.sql

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

时间: 2017-05-17

MySQL的id关联和索引使用的实际优化案例

昨晚收到客服MM电话,一用户反馈数据库响应非常慢,手机收到load异常报警,登上主机后发现大量sql执行非常慢,有的执行时间超过了10s 优化点一: SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10; 表结构为: CREATE TABLE `game_shares_buy_list` ( `tran_id` int(10) unsigned NOT

Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

mysql关联子查询的一种优化方法分析

本文实例讲述了mysql关联子查询的一种优化方法.分享给大家供大家参考,具体如下: 很多时候,在mysql上实现的子查询的性能较差,这听起来实在有点难过.特别有时候,用到IN()子查询语句时,对于上了某种数量级的表来说,耗时多的难以估计.本人mysql知识所涉不深,只能慢慢摸透个中玄机了. 假设有这样的一个exists查询语句: select * from table1 where exists (select * from table2 where id>=30000 and table1.u

PHP实现将MySQL重复ID二维数组重组为三维数组的方法

本文实例讲述了PHP实现将MySQL重复ID二维数组重组为三维数组的方法.分享给大家供大家参考,具体如下: 应用场景 MYSQL在使用关联查询时,比如 产品表 与 产品图片表关联,一个产品多张产品图片,关联查询结果如下: $arr=[ ['id'=>1,'img'=>'img1'], ['id'=>1,'img'=>'img2'], ['id'=>1,'img'=>'img3'], ['id'=>2,'img'=>'img1'], ['id'=>2,'

MySQL 死锁套路:唯一索引 S 锁与 X 锁的爱恨情仇

在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例. 毫不夸张的说,有一半以上的死锁问题由唯一索引贡献,后面介绍的很多死锁的问题都跟唯一索引有关.这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇 我们来看一个简化过的例子 # 构造数据 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10), `level` int(11),

MySQL如何选择合适的索引

先来看一个栗子 EXPLAIN select * from employees where name > 'a'; 如果用name索引查找数据需要遍历name字段联合索引树,然后根据遍历出来的主键值去主键索引树里再去查出最终数据,成本比全表扫描还高. 可以用覆盖索引优化,这样只需要遍历name字段的联合索引树就可以拿到所有的结果. EXPLAIN select name,age,position from employees where name > 'a'; 可以看到通过select出的字段

MySql 知识点之事务、索引、锁原理与用法解析

本文实例讲述了MySql 知识点之事务.索引.锁原理与用法.分享给大家供大家参考,具体如下: 事务 事务概念 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎执行一组操作语句,那么久执行所有的操作,如果其中有任何一条崩溃或其他原因无法执行,所有语句将不会执行.也就是说事务内的语句,要么全部执行成功,要么全部执行失败. 事务特性ACID 原子性(atomicity) 一个事务被视为最小工作单元,不可拆分,整个事务所有的操作要么全部提交成功,要么全部失败回滚,不可只执行部分.

mysql一对多关联查询分页错误问题的解决方法

xml问价中查询数据中包含list,需要使用collection <resultMap id="XX" type="com.XXX.XXXX"> <id column="o_id" jdbcType="BIGINT" property="id" /> <result column="o_user_id" jdbcType="BIGINT"

MySQL批量插入和唯一索引问题的解决方法

MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this va

Mysql判断表字段或索引是否存在

判断字段是否存在: DROP PROCEDURE IF EXISTS schema_change; DELIMITER // CREATE PROCEDURE schema_change() BEGIN DECLARE CurrentDatabase VARCHAR(); SELECT DATABASE() INTO CurrentDatabase; IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schem