精华内容
下载资源
问答
  • MySQL多表查询where子查询

    千次阅读 2019-05-31 21:49:32
    当前有用户,以及订单,订单详细时,需要查询购买了某商品Id为“1001”的商品的客户信息 如果用单条语句查询需要 先从订单详细表查询商品ID为“1001”的购物订单ID 再从订单表查询该购物订单id...

    SQL不仅支持单条的检索数据查询还允许创建子查询,即嵌套在其他查询中的查询。
    表结构:https://blog.csdn.net/wsdfym/article/details/90722724
    当前有用户表,以及订单表,订单详细表时,需要查询购买了某商品Id为“1001”的商品的客户信息

    如果用单条语句查询需要

    • 先从订单详细表查询商品ID为“1001”的购物订单ID
    • 再从订单表查询该购物订单id的用户id
    • 再根据用户id从用户表查询到用户信息
    select order_num
    from orderitems
    where prod_id = 1001
    

    在这里插入图片描述
    2.

    select cust_id
    from orders
    where order_num in (30007,30008)
    

    在这里插入图片描述
    3.

    select cust_name
    from customers
    where cust_id in (1,2)
    

    在这里插入图片描述
    现在结合这三个查询,从外到内 查询用户信息,查询该购物订单的顾客id,查询购买了该物品的订单id,
    从内到外查询购买了该物品的订单id,查询该购物订单的顾客id,查询用户信息

    select cust_name
    from customers
    where cust_id in ( select  cust_id
    									from orders
    									where order_num  in( select order_num
    																		from orderitems
    																		where prod_id = 1001
    																		)
    								)
    

    在这里插入图片描述
    该语句先执行最内层查询 “select order_num from orderitems where prod_id = 1001” 返回两个订单号 30007 ,30008,
    依据内层查询返回的两个订单号 再执行中层查询 返回两个顾客id 1,2,最后依据返回的顾客id查询顾客信息

    需要注意的是作为子查询只能返回单列,并且使用子查询并不总是检索数据最有效的方法

    展开全文
  • mysql多表查询,三张表或者以上

    千次阅读 2019-12-18 16:29:06
    mysql多表查询关键知识点 inner join inner join (内连接,或等值连接):获取两个表中字段匹配关系的记录。 left join(左连接):获取左表所有记录,即使右表没有对应匹配的记录。 right join(右连接): 与 LEFT...

    mysql多表查询关键知识点 inner join

    inner join (内连接,或等值连接):获取两个表中字段匹配关系的记录。
    left join(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
    right join(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

    假设有tb_a / tb_b / tb_c 三张表根据条件分别返回name,sql怎么写呢?话不多说,直接上语句

    输入

    
     select tb_a.name1,tb_b.name2,tb_c.name3 from  
     (tb_a inner join tb_b on tb_b.id = tb_a.tid)
     inner join tb_c on tb_b.tid = tb_c .did;
    
    

    返回

    name1 name2 name3
    A B C

    详解

    // select 后的*代表返回关联表的所有字段,如果是tb_a.name1这种表名.字段名的则是返回符合条件的字段名
    //三张表
    SELECT * FROM (1 INNER JOIN2 ON1.字段号=2.字段号) INNER JOIN3 ON1.字段号=3.字段号
    
    // INNER JOIN 关联四张数据表的写法:
    SELECT * FROM ((1 INNER JOIN2 ON1.字段号=2.字段号) INNER JOIN3 ON1.字段号=3.字段号) INNER J
    
    展开全文
  • Mysql多表查询效率的研究(一)

    万次阅读 2017-08-18 21:44:10
    Mysql多表查询效率的研究(一)本文探究了mysql InnoDB引擎在多表查询的应用场景下,使用子表、内连接和左联接运行速度的差别,并且比较了索引使用与否对查询效率的影响。 第一部分简略地概括了索引、子表查询、...

    Mysql多表查询效率的研究(一)

    本文探究了mysql InnoDB引擎在多表查询的应用场景下,使用子表、内连接和左联接运行速度的差别,并且比较了索引使用与否对查询效率的影响。
    第一部分简略地概括了索引、子表查询、联接查询的算法和数据结构;
    第二部分探讨索引的使用策略和查询语句的优化并进行测试;
    第三部分在前两部分的基础上进一步讨论mysql高性能的实现。

    一、数据结构基础

    1. 索引原理
      索引:INDEX,官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录,通过目录就知道要的资料在哪里,不用一页一页查阅找出需要的资料。
      为什么需要索引(Why is it needed)?
      当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,以及一个指向下一个节点(或数据块)的指针,不需要连续存储。
      记录集只能在某个关键字段上进行排序,所以如果需要在一个无序字段上进行搜索,就要执行一个线性搜索(Linear Search)的过程,平均需要访问N/2的数据块,N是表所占据的数据块数目。如果这个字段是一个非主键字段(也就是说,不包含唯一的访问入口),那么需要在N个数据块上搜索整个表格空间。
      但是对于一个有序字段,可以运用二分查找(Binary Search),这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。但是,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
      由于索引只是用来加速数据查询,那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间,所以这种情况下应该尽量避免。此外鉴于二分搜索的特性,数据的基数或独立性是很重要的。在基数为2的字段上建立索引,将把数据分割一半,而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序,而且查询优化器可能会在基数小于记录数某个比例时(如30%)的情况下将避免使用索引而直接查询原表,所以这种情况下的索引浪费了空间。
      B-Tree:B-Tree是一种多路搜索树(并不是二叉的):
      1.定义任意非叶子结点最多只有M个儿子;且M>2;
      2.根结点的儿子数为[2, M];
      3.除根结点以外的非叶子结点的儿子数为[M/2, M];
      4.每个结点存放至少M/2-1(取上整)和至多M-1个关键字;(至少2个关键字)
      5.非叶子结点的关键字个数=指向儿子的指针个数-1;
      6.非叶子结点的关键字:K[1], K[2], …, K[M-1];且K[i] < K[i+1];
      7.非叶子结点的指针:P[1], P[2], …, P[M];其中P[1]指向关键字小于K[1]的子树,P[M]指向关键字大于K[M-1]的子树,其它P[i]指向关键字属于(K[i-1], K[i])的子树;
      8.所有叶子结点位于同一层;
      如:(M=3)
      这里写图片描述
      B+Tree:B+树是B-树的变体,也是一种多路搜索树:
      1.其定义基本与B-树同,除了:
      2.非叶子结点的子树指针与关键字个数相同;
      3.非叶子结点的子树指针P[i],指向关键字值属于[K[i], K[i+1])的子树(B-树是开区间);
      5.为所有叶子结点增加一个链指针;
      6.所有关键字都在叶子结点出现;
      如:(M=3)
      这里写图片描述
      为什么选用B+Tree因为B+树不在节点中储存数据,那么一个磁盘块中可以储存更多的B+树非叶子节点,在对相同大小的数据建立索引时,B+树的度数更少,深度降低,那么查找时间也相应降低。同时B+树的双亲节点保存的是最小的数值,在SELECT时有效。最重要的是,磁盘读取数据时会进行一次“预读”,大小通常为页的整数倍。即使只需要读取一个字节,磁盘也会读取一页的数据(通常为4K)放入内存,内存与磁盘以页为单位交换数据。因为局部性原理认为,通常一个数据被用到,其附近的数据也会立马被用到。每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。并把B+tree中的m值设的非常大,就会让树的高度降低,有利于一次完全载入。
      聚簇索引
      所谓聚簇索引,就是指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键,如果是一般索引的话,data便会指向对应的主索引,如下图所示:
      这里写图片描述
      在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。
      在InnoDB的表建立时会默认选择UNIQUE的Auto-Increase字段作为叶子节点的主索引。如果没有unique的字段,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。并且一些经常会被修改、插入、删除的字段不适合作为聚簇索引的主索引。因为磁盘上数据保存的位置是和主索引息息相关的。如果数据项之间的大小关系改变了,那么B+树必须更改叶子节点和相关祖先节点的位置以适应新的数据。
      非聚簇索引就是指B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中,如下图:
      这里写图片描述
      聚簇索引与非聚簇索引的不同
      InnoDB的数据文件本身就是索引文件,而MyISAM储存的是数据的地址;
      InnoDB的辅助索引data域存储相应记录主键的值而不是地址,而MyISAM无论是主键索引还是辅助索引都记录是数据地址;
      聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

      单列索引与多列索引
      索引可以是单列索引也可以是联合索引(也叫复合索引)。按照上面形式创建出来的索引是单列索引,现在先看看创建联合(多列)索引:
      ALTER TABLE table ADD INDEX index(a,b,c)
      注意:INDEX(a, b, c)可以当做a或(a, b)的索引来使用,但和b、c或(b,c)的索引来使用这是一个最左前缀的优化方法。
      如果一个查询where子句中确实不需要联合索引中的某一列,那就用“补洞”。

    2. 联表查询原理
      笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用’,’ 如:
      由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN
      MySQL中的外连接,分为左外连接LEFT [OUTER] JOIN和右连接RIGHT [OUTER] JOIN,即除了返回符合连接条件的结果之外,还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果,相对应的使用NULL对应。

      **当前MySQL执行执行的策略很简单**:
      当进行联表查询时,Mysql会从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行,如:结果1和结果3),如果右边没有与on条件匹配的表,那连接的字段都是null.然后继续读下一条。mysql对任何关联都执行嵌套循环操作,即mysql先在一个表中循环取出单条数据,然后再嵌套循环
      

      到下一个表中寻打匹配的行,依次下去,直到描述到所表表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。mysql会尝试在最后一个关联表中打到所有匹配的行,如果最后一个关联表无法找到更多的行以后,mysql返回到上一层次关联表,看是否能够找到更多的匹配记录,依此类推迭代执行。
      按照这样的方式查找第一条表记录,再嵌套查询下一个关联表,然后回溯到上一个表,在mysql中是通过嵌套循环的方式来实现的–正如其名‘嵌套循环关联’。
      MySQL如何优化LEFT JOIN和RIGHT JOIN
      在MySQL中,A LEFT JOIN B join_condition执行过程如下:
      1)· 根据表A和A依赖的所有表设置表B。
      2)· 根据LEFT JOIN条件中使用的所有表(除了B)设置表A。
      3)· LEFT JOIN条件用于确定如何从表B搜索行。(换句话说,不使用WHERE子句中的任何条件)。
      4)· 可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系,MySQL提示出现一个错误。
      5)· 进行所有标准WHERE优化。
      6)· 如果A中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成另一个B行,其中所有列设置为NULL。
      7)· 如果使用LEFT JOIN找出在某些表中不存在的行,并且进行了下面的测试:WHERE部分的col_name IS NULL,其中col_name是一个声明为 NOT NULL的列,MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。
      RIGHT JOIN的执行类似LEFT JOIN,只是表的角色反过来。
      8)大表left join小表
      9)为经常使用的字段建立索引

    3. 子表查询(IN)
      使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接JOIN 替代,是因为 mysql不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
      相关子查询,尤其是使用不到索引时效率或非常低,可改写成join方式,因为join时内表中的一条记录可能跟外表中的多条记录匹配,所以最终会比使用相关子查询的方式多出一些重复的记录结果,故使用group by去重复,当然也可以使用distinct关键字,两者原理相同。如果重复值对于最终需求并没有什么影响则可以移除该从句以避免分组、排序造成的临时表和文件排序等额外开销,提高查询效率。

    二、查询策略与测试

    为了探讨查询语句和INDEX的策略,我选用了Mysql官方提供的employees数据库。下图是employees的ER图:
    这里写图片描述
    首先下载在mysql的test_db-master.zip,解压后导入shell> mysql -t < employees.sql。同时为了控制变量,我删除了所有外键和index,如下图所示:
    这里写图片描述
    下一步开启PROFILESset profiling=1.之后profile会记录最近的十条语句与他们的执行状态。用show profilesshow profile for query 1可以看到具体的信息包括数据分发、统计、打开表格的时间等。mysql官方文档有具体的语法解释。
    开启慢查询日志:默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启show variables like ‘%slow_query_log%’;
    set global slow_query_log=1;
    在my.ini或my.cnf(Linux)中可以设置阈值:
    slow_query_log =1
    slow_query_log_file=/tmp/mysql_slow.log
    并且为了测试要求,我们设置read的超时时间:

    SET net_read_timeout=3600;

    然后重启mysql。
    设置mysql的buffer
    SHOW VARIABLES LIKE ‘%query_cache%’;
    由于my.cnf文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境。
    以下只列出my.cnf文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。

    [mysqld] 
      port = 3306 
      serverid = 1 
      socket = /tmp/mysql.sock 
      skip-locking 
      # 避免MySQL的外部锁定,减少出错几率增强稳定性。 
      skip-name-resolve 

    禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!

    back_log = 384 

    指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求,该参数生效,主线程花费很短的时间检查连接并且启动一个新线程。

    back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。

    试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

    key_buffer_size = 256M 

    # key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。 对于内存在4GB左右的服务器该参数可设置为256M或384M。 注意:该参数值设置的过大反而会是服务器整体效率降低!

    max_allowed_packet = 4M
    thread_stack = 256K
    table_cache = 128K
    sort_buffer_size = 6M

    查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。 `
    **EXPLAIN**显示了MySQL如何使用索引来处理SELECT语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
    使用方法,在select语句前加上EXPLAIN就可以了:
    EXPLAIN format=json SELECT…..“。
    EXPLAIN列的解释:

    描述
    select_type 显示select的类型(是否使用UNION)。
    table 显示这一行的数据是关于哪张表的。
    type 这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为 const、eq_reg、ref、range、index和ALL。
    possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。
    key 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MySQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname) 来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引。
    key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好。
    ref 显示索引的哪一列被使用了,如果可能的话,是一个常数。
    rows MySQL认为必须检查的用来返回请求数据的行数。
    filtered 表示表中符合过滤条件的行数所占的百分比
    Extra 关于MySQL如何解析查询的额外信息。可以查看官方文档https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MySQL根本不能使用索引,结果是检索会很慢。

    Mysql WorkBench提供了explain的可视化功能,有兴趣的也可以尝试:
    这里写图片描述

    临时表

    在order by 和子查询时,有可能会使用临时表暂存数据,explain的Extra会显示using temporary。或者SHOW STATUS LIKE 'CREATE%'查看。在mysql中,MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎;内存临时表的大小通过SHOW VARIABLES LIKE '%HEAP%'或者SHOW VARIABLES LIKE '%tmp_table_size%';可以找到,如果临时表的容量超过限制,那么就会在硬盘中储存临时表(Linux的/tmp),大大增加IO操作。
    同样,在以下及几种场景中,mysql会使用临时表:

    1. ORDER BY子句和GROUP BY子句不同, 例如:ORDERY BY price GROUP BY name;
    2. 在JOIN查询中,ORDER BY或者GROUP BY使用了不是第一个表的列(即不是使用驱动表的列),例如:SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
    3. ORDER BY中使用了DISTINCT关键字ORDERY BY DISTINCT(price),在distinct查询体积较大的表时或者用了distinct却没有用where时也会使用。
    4. SELECT语句中指定了SQL_BUFFER_RESULT关键字,SQL_BUFFER_RESULT的意思就是告诉MySQL,查询结果输出用时会很长,请直接使用内存临时表将查询结果输出,通过将数据缓冲到临时表中可以有效的减少读锁对表的占用时间。
    5. SELECT语句中指定了SQL_SMALL_RESULT关键字;SQL_SMALL_RESULT的意思就是告诉MySQL,结果会很小,请直接使用内存临时表,不需要使用索引排序。SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用,一般情况下,我们没有必要使用这个选项,让MySQL服务器选择即可。

    直接使用磁盘临时表的场景
    1)GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列;
    2)使用UNION或者UNION ALL时,SELECT子句中包含大于512字节的列;
    使用临时表一般都意味着性能比较低,特别是使用磁盘临时表,性能更慢,因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有:
    1)创建索引:在ORDER BY或者GROUP BY的列上创建索引
    2)分拆很长的列:一般情况下,TEXT、BLOB,大于512字节的字符串,基本上都是为了显示信息,而不会用于查询条件, 因此表设计的时候,应该将这些列独立到另外一张表。
    3 ) 拆分使用union或union all的复杂sql语句,可以使用in或者join。
    4)优化DISTINCT,即DISTINCT语句被优化转换为GROUP BY操作或者利用UNIQUE INDEX消除DISTINCT。

    子查询

    为了避免多个索引使事情变复杂(MySQL的SQL优化器在多索引时行为比较复杂),这里我们将辅助索引drop掉:
    查看employees所有表的内外键约束:

     select * from information_schema.key_column_usage where CONSTRAINT_SCHEMA='employees';

    查看数据库所有表的索引:

     select * from information_schema.STATISTICS WHERE TABLE_SCHEMA='employees';

    删除指定的键:

    DROP INDEX indexname ON tablename

    为表添加索引:

    mysql> alter table `departments` add index test(dept_name);

    以上的操作是一些基本的index操作语句。下面进行正式的测试,我们选择了dept_emp (331.5K rows)employees(298.8K rows)作为样本,首先执行两张表之间的子查询语句:

    EXPLAIN SELECT t1.to_date FROM dept_emp t1 WHERE EXISTS(
       SELECT 1
       FROM employees t2
       WHERE t1.to_date = t2.hire_date
       );
    

    结果为
    这里写图片描述
    发现执行时间为773s。EXPLAIN的结果显示sql对dept_emp的33万行employees的30万进行全表查询。
    这里写图片描述

    联表查询

    再换成交叉连接语句执行联合查询:

     SELECT dept_emp.to_date,employees.hire_date FROM dept_emp,employees WHERE dept_emp.to_date=employees.hire_date ;

    运行时间1.25s。交叉连接的执行计划为:
    这里写图片描述
    我们可以看到,mysql用employees作为驱动表执行block nested loop算法。
    再试试left join

     SELECT dept_emp.to_date,employees.hire_date FROM dept_emp left join employees on dept_emp.to_date=employees.hire_date ;

    这里写图片描述
    duration为1.7s。因为dept_emp的数据量比employees大,那可以用dept_emp RIGHT JOIN employees 让employees作为驱动表。在mysql用 nested-loop join (NLJ)算法计算时,会使用驱动表A中符合条件的字段作为过滤条件来过滤表B,那么算法复杂度相当于count(A)*E(count(B)|A=B),所以用小表作为驱动,如dept_emp RIGHT JOIN employees,可以降低复杂度。
    当然在这里,使用dept_emp RIGHT JOIN employees相当于交叉连接,同样是小表作为驱动表。

    我们可以看到一共执行时间为0.06s,因为dept_emp,employees等表都是数据量10万+的,所以内链接在没有索引的情况下需要遍历10万*10万*10万的数据。我们可以再看看profile的记录:
    这里写图片描述
    其中opening tables 耗时0.03左右,极大地占用了查询时间。

    优化:建立索引

    下面我们对这三张表建立索引,首先测试字段的唯一性:字段唯一性的公式为Index Selectivity = Cardinality / #T,其中Cardinality为该字段的不重复数据的数量,#T为该字段的总数据量。
    这里写图片描述
    to_date的唯一性还不够(不足0.9),那么再尝试(to_date,from_date)的组合:
    这里写图片描述
    唯一性比单纯一条to_date更优化。那么单单针对本次date=date的查询设定联合索引(to_data,from_data):
    这里写图片描述
    同时为其他两张表建立index。再刷新缓存:
    这里写图片描述

    这里写图片描述
    使用了index后耗时下降了一半,并且我们看到key列里面表示where时使用了index。
    这里写图片描述
    opening tables的时间相同但是checking permissions的时间大大降低。

    视图

    视图算法:系统对视图以及外部查询视图的select语句的一种解析方式。

    视图算法分为三种:

    undefined:未定义(默认的),这不是一种实际使用的算法,是一种推卸责任的算法—-告诉系统,视图没有定义算法,你看着办。

    temptable:临时表算法;系统应该先执行视图的select语句,后执行外部查询的语句。

    merge:合并算法;系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(效率高),系统会默认尝试该算法,但是并不是所有的视图都可以自动条件下推,比如查询语句中有Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)、DISTINCT】GROUP BY、HAVING、LIMIT、UNION or UNION ALL、Subquery in the select list、Refers only to literal values (in this case, there is no underlying table)。

    在创建视图的时候指定算法:

    create algorithm = 指定算法 view view_name as select ...

    视图算法选择:如果视图的select语句 中会包含一个查询子句,而且很有可能顺序比外部的查询语句要靠后;则选择使用temptable,其他情况可以不用指定(默认即可)。

    三、总结

    参考

    MySQL索引背后的数据结构及算法原理

    展开全文
  • [图片说明](https://img-ask.csdn.net/upload/201806/03/1528023942_245734.png)如图,有张中间,怎么通过article的id查询article中所有的值已经tag中的name字段的值,name可能是个,小弟刚入行没多久,...
  • mysql多表查询(一)笛卡尔集

    千次阅读 2018-01-28 22:59:40
    为什么要使用多表查询?  因为我们经常需要在很多个表之间查询数据。比如,查询会员的银行卡提现记录。首先我们在会员表中查询会员的银行卡id,再到提现记录表中根据银行卡id查询提现记录。 什么是笛卡尔集? ...

    为什么要使用多表查询?

            因为我们经常需要在很多个表之间查询数据。比如,查询会员的银行卡提现记录。首先我们在会员表中查询会员的银行卡id,再到提现记录表中根据银行卡id查询提现记录。

    什么是笛卡尔集?

    笛卡尔集的列数为每个表的列数之和,笛卡尔集的行数为每个表的行数相乘。我们经常做的多表查询就是在笛卡尔集中通过筛选条件得出的数据,所以笛卡尔集是多表查询的基础。

    多表查询的条件有多少?

    笛卡尔集的筛选条件至少为n-1。n为表的个数。

    具体筛选条件的个数根据实际情况而定,以上为至少筛选条件的个数。

    展开全文
  • MySQL多表查询带来的重复记录问题

    千次阅读 2019-02-20 10:43:10
    在开发中因为不谨慎遇到了这样一个问题,这里总结一下问题、问题出现的原因和解决方式。 假如现在有这么一个需求,  1  ...
  • where a.id = d.Aid (d的Aid和a的id对应,但d条数据的Aid=a.id) group by a.id select a.id,a.name as 处理人,c.name as 所属机构 from A a,B b,C c where a.id = b.Aid and c.id = b.Cid (B是中间...
  • MySQL多表查询之GroupBy

    千次阅读 2014-11-24 17:34:21
    需求是根据主键id查询到顾客
  • mysql多表查询数值为空null时处理成0

    千次阅读 2019-06-27 10:38:13
    在遇到张表查询时,很可能查一个关联数值时,并没有这条关联记录,所以查询到的结果是null,通常需要把这个结果处理成0或者其他。如图 2.问题 部门是有11个的,但是其他几个部门没有人员,就不会展示出来,sql...
  • 1、使用union和union all合并两个查询结果:select 字段名 from tablename1 union select 字段名 from tablename2; ...注:下边的几个连接查询涉及到笛卡尔积的概念,即如果存在两张,第一张
  • 1、Duplicate column name 'ID'分析  1.1、第一种情况:  MySQL这个这个错误的一种情况,保存... 多表关联查询,只有一层查询时,id重复不会报错,但是如果再套一层查询,就报这个错误。  eg: SELECT a...
  • ... 在这里我自己想补充的一点是:在你建立索引完成时,可是你在navicat的试图中,在对应的设计中,没有找到新建立起来的索引,那么你可以点击“保存”按钮,那么这样的话,就可以看见新建立起来的...三、子查询
  • ![图片说明](https://img-ask.csdn.net/upload/201607/08/1467966862_584005.png)
  • Mysql 多表连接查询

    万次阅读 多人点赞 2018-05-08 19:27:21
    本文部分内容转载至:Mysql 多表查询详解,同时感谢原作者的整理与创作;
  • springboot+mybatis+mysql 多表联合查询

    万次阅读 2018-07-02 20:10:31
    springboot+mybatis+mysql 多表查询 这里有两张表 用户表和用户信息表user、info 两个实体类展示他们一对一的关系 通过springboot**注解的方式**实现多表联合查询 通过userId查询User及其对应的Info信息 /** ...
  • MySQL 高级多表查询

    千次阅读 2020-05-05 14:03:33
    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户...
  • Mysql 多表联合查询效率分析及优化

    万次阅读 多人点赞 2010-07-13 15:23:00
    1. 多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,...
  • mysql多表多字段查询并去重

    千次阅读 2019-03-15 14:33:00
    mysql多表多字段查询并去重 - MySQL-ChinaUnix.nethttp://bbs.chinaunix.net/forum.php?mod=viewthread&tid=4254936 MySQL多表查询 - 淋哥 - 博客园https://www.cnblogs.com/xuchunlin/p/6045263.html mysql-...
  • MySQL多表联合查询、连接查询、子查询

    万次阅读 多人点赞 2019-06-03 17:08:20
    联合查询的意义: 查询同一张表,但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的. 联合查询order by的使用 在联合查询中: order by不能...
  • MySQL多表查询

    万次阅读 多人点赞 2018-06-18 00:00:22
    多表查询如果不加任何条件,得到的结果称为笛卡尔积。 例如,查找雇员名、雇员工资以及部门所在的名字。 可以发现,结果是这样的,从第一个表中选出第一条记录,和第二个表中的所有所有记...
  • MySQL多表联合查询条件语句顺序

    千次阅读 2019-02-15 15:11:38
    mysql 多表联合查询 mysql 多表历澜和查询条件语句顺序 在数据库操作中,多表联合查询是后台开发者常用到的查询语句。这里记录一次本人在MySQL数据库下在进行多表联合查询时因为条件语句顺序放置错误而导致的异常。
  • Mysql 多表查询详解 一.前言 二.示例 三.注意事项 一.前言 上篇讲到Mysql中关键字执行的顺序,只涉及了一张表;实际应用大部分情况下,查询语句都会涉及到多张表格 : 1.1多表连接有哪些分类? 1.2针对这些分类...
  • mysql数据库多表查询实例

    千次阅读 2017-09-03 18:12:08
    有以下几张: 学生s:sid(主键)、sname(姓名)、sex(性别)、age(年龄) 班级c:cid(主键)、cname(班级名) 教师t:tid(主键)、tname(教师名称) 关系sc:id(主键)、sid(学生主键)、cid(班级主键)、...
  • mysql表查询和解锁操作

    万次阅读 2018-08-11 16:42:09
    mysql表查询和解锁操作 1、在做数据库操作时,有时会因为自己的粗心或者程序设计上的缺陷导致锁,在mysql中查看锁和解锁的步骤如下: //1.查看当前数据库锁的情况 SELECT * FROM information_schema....
  • mysql 单表多次查询多表联合查询,哪个效率高

    万次阅读 热门讨论 2017-04-14 10:13:13
    引自高性能MySQL
  • PHP完成MySQL多表连接查询-- JOIN

    千次阅读 2019-06-08 10:09:42
    可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。 JOIN 按照功能大致分为如下三类: 1、INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。 2、LEFT JOIN(左连接):...
  • mysql数据库多表查询练习题

    万次阅读 多人点赞 2018-05-14 10:40:38
    下面练习题中设计四个。分别为: dept emp salgrade tbyear 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。 SELECT d.deptno,d.dname,d.loc,e1.`count(*)` ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 994,360
精华内容 397,744
关键字:

mysql多表查询

mysql 订阅