• Mysql竟然不支持将同一个用不同的别名进行关联查询,甚至不能在一条sql语句中两次使用同一个。错误提示: #1137 - Cant reopen table: mytablecreate TEMPORARY table temp1 asselect * from(select 1 ...
      晕!Mysql竟然不支持将同一个表用不同的别名进行关联查询,甚至不能在一条sql语句中两次使用同一个表。
    错误提示: #1137 - Can't reopen table: 'mytable'
    create TEMPORARY table temp1 as
    select * from(
    select 1 as id,50 as ic,'2007-10-8'as itime,'a' as wd union
    select 2 as id,80 as ic,'2007-10-9' as itime,'ab' as wd union
    select 3 as id,50 as ic,'2007-11-3' as itime,'abc' as wd union
    select 4 as id,50 as ic,'2007-6-8' as itime,'ad' as wd union
    select 5 as id,60 as ic,'2007-4-9' as itime,'ac' as wd union
    select 6 as id,80 as ic,'2007-12-9' as itime,'aa' as wd
    )a;

    create TEMPORARY table temp2 as
    select * from(
    select 1 as id,50 as ic,'2007-10-8'as itime,'a' as wd union
    select 2 as id,80 as ic,'2007-10-9' as itime,'ab' as wd union
    select 3 as id,50 as ic,'2007-11-3' as itime,'abc' as wd union
    select 4 as id,50 as ic,'2007-6-8' as itime,'ad' as wd union
    select 5 as id,60 as ic,'2007-4-9' as itime,'ac' as wd union
    select 6 as id,80 as ic,'2007-12-9' as itime,'aa' as wd
    )a;

    select min(a.id) as id,a.ic,a.itime,a.wd
    from temp1 a inner join
    (
    select ic,max(cast(itime as date)) as itime from temp2 group by ic
    ) b 
    on a.ic=b.ic and cast(a.itime as date)=cast(b.itime as date)
    group by a.id,a.ic,a.itime,a.wd
    order by a.wd

    (当然也可以用其他方式变通解决)或者:
    create TEMPORARY table mytable as
    select * from(
    select 1 as id,50 as ic,'2007-10-8'as itime,'a' as wd union
    select 2 as id,80 as ic,'2007-10-9' as itime,'ab' as wd union
    select 3 as id,50 as ic,'2007-11-3' as itime,'abc' as wd union
    select 4 as id,50 as ic,'2007-6-8' as itime,'ad' as wd union
    select 5 as id,60 as ic,'2007-4-9' as itime,'ac' as wd union
    select 6 as id,80 as ic,'2007-12-9' as itime,'aa' as wd
    )a;

    create TEMPORARY table temp as
    select * from(
    select ic,max(cast(itime as date)) as itime from mytable group by ic
    )a;

    select min(a.id) as id,a.ic,a.itime,a.wd
    from mytable a inner join temp b on a.ic=b.ic and cast(a.itime as date)=cast(b.itime as date)
    group by a.id,a.ic,a.itime,a.wd
    order by a.wd
    展开全文
  • mysql关联查询语句

    2019-01-04 10:40:54
    今天和mysql相关死磕到底 Baby 我的眼里都是 心里都是全部都是你 哒哒哒哒哒哒 哒哒哒 一、前言 1.1关系 理解关系,以一个例子展开: 有一个包含产品目录的数据库,其中每一类物品占一行。对于每一种...

    今天和mysql相关死磕到底

    Baby 我的眼里都是

    心里都是全部都是你

    哒哒哒哒哒哒 哒哒哒

    一、前言

    1.1关系表

    理解关系表,以一个例子展开:

    有一个包含产品目录的数据库表,其中每一类物品占一行。对于每一种物品,要存储的信息包含产品描述,价格,以及生产该产品的供应商。

    现在有同一供应商生产的多个产品,那么在何处存储供应商名称,地址,联系方法等供应商信息呢?将这些数据与产品信息分开存储的理由是:

    []同一供应商生产的每个产品,其供应商的信息都是相同的,对每个产品重复此信息既浪费时间又浪费空间

    []如果供应商的信息发生变化,例如供应商迁址或者电话号码变动,只需要修改一次即可

    []如果有重复的数据(即每种产品都存储供应商信息),则很难保证每次输入该数据的方式都相同,不一致的数据在报表中就很难利用。

    关键是,相同的数据出现多次不是一个好事,也是关系数据库设计的基础,关系表的设计就是要把信息分解成多个表,一类数据一个表。各个表通过某些共同的值互相关联,所以才叫关系数据库。

    1.2关系表的连接

    如上所述,将数据分解成多个表可以更有效的存储,更方便的处理,并且可伸缩性更好,但是这些好处是有代价的。

    如果数据存储在多个表中,怎样用一条select语句检索出数据?答案就是使用连接。简单来说,连接是一种机制,用来在一条select 语句中关联表,因此称为连接。使用特殊的用法,可以关联多个表返回一组输出,连接在运行时关联表中正确的行。

    二、关联查询

    基于前言,本篇重点在于关联查询,Let's begin!

    2.1创建连接

    创建连接非常简单,指定要连接的所有表以及关联他们的方式即可,重点在于找到关联的方式。其实就是找关联行。

    例如:

    $select vend_name,prod_name,prod_price from Vendors,Products where Vendors.vend_id=Products.vend_id;

    分析:

    (1)select 语句与前面所有查询语句一样,指定要检索的列。这里最大的差别在于所指定的两列(prod_name,prod_price)在一个表中,而第三列(vend_name,)在另外一张表中。

    (2)from子句。与以前的select语句不一样,这里列出了两个表:Vendors,Products,它们就是这select语句连接的两个表的名字。这两个表通过where子句正确的连接,where子句指示DBMS将Vendords表中的vend_id与Products中的vend_id匹配起来。

    (3)可以看到要匹配的两列指定为Vendors.vend_id=Products.vend_id。这里必须完全指定列名,要不然DBMS无法区分需要哪一个表和哪一个表的什么去做关联。

    (4)where子句的重要性:在连接两个表时,实际要做的是就是将第一表中的每一行与第二个表中的每一行去配对,where子句作为过滤条件,只包含那些可以匹配条件的行,如果没有where子句,实际要做的是就是将第一表中的每一行与第二个表中的每一行去配对,而不管是否可以匹配在一起,然后返回,生成的记过就是笛卡尔积,类似于:排列组合。

    2.2内连接

    目前未知的连接叫做等值连接,它基于两个表之间的相等测试。这种连接也叫作内连接。然而内连接也可以使用另外一种语法,如下:

    $select vend_name,prod_name,prod_price from Vendors inner join Products on Vendors.vend_id=Products.vend_id;

    可以看出,这里用到了 inner join .. on 来连接两个表,而不是where,但是效果都是一样的,具体采用哪种方式,依据个人习惯。

    上面的连接我们用到了两张表,实际呢连接的话其实可以使用多个表,虽然mysql本身不限制连接的表的个数,但是出于性能考虑,以及可读性上,连接3张表算是最多的了。

    实际举例:

    系统分为了三个表:user表,driverv表,transport_protocal表;transport_protocal表通过fk_driver_id与driver表id 关联,driver表通过fk_user_id与user表id关联

    要求:查询用户名为18012345678的在途运单号是多少?

    查询语句如下:

    $select user.username,tp.trans_number from user,driver,transport_protocol tp  where tp.fk_driver_id=driver.id and driver.fk_user_id=user.id and user.username='18112345678' and tp.is_onway=1;

    2.3自连接

    自连接就是需要查询的两个表实际是相同的表,需要根据表第一次查询出来的结果再次过滤,这里需要用到表别名进行表区分:

    场景:假如要给与Jim Jones同一公司的所有顾客发送一封邮件,这个查询要求首先找出Jim Jones 工作的公司,然后找出在该公司工作的顾客,下面是查询语句:

    #方式一:采用子查询

    select user_id,cust_name,cust_contact from Customers where cust_name = (select cust_name from Customers where cust_contact='Jim Jones';

    #方式二:自连接

    select c1.user_id,c1.cust_name,c1.cust_contact from Customers c1,Customers c2 where c1.cust_name = c2.cust_name and c2.cust_contact ='Jim Jones';

    tip :可以看出子查询写起来比较麻烦,所以习惯用自连接而不用子查询

    2.4 外连接

    许多连接将一个表中的行与另外一个表中的行相关联,但有时候需要包含没有关联行的那些行

    通俗的讲:没有关联行的时候可能才会用到,内连接的时候肯定存在关联行,要不然无法匹配

    举栗子:

    查询要求:对每个顾客下的订单数进行计数,包括至今未下过单的顾客

    顾客表:Customers  订单表:Orders

    select Customers.cust_id,Orders.order_num from Customer left join Orders on Customers.cust_id =Orders.cust_id;

    解释:未下过单的顾客因为在订单表里面,是没有数据的,那就没有关联行,那么还需要统计出这部分顾客的姓名和计数,那么就需要外连接顾客表,查询出这部分顾客的id和计数(没有那肯定是0了)

    常用的外连接用到:left join 和right join ;具体用哪个join关键在于sql查询语句中需要连接表格的位置。

    三、其他

    3.1组合查询:两个表联合查询相同列:UNION

    UNION 语句:用于将不同表中相同列中查询的数据展示出来; (不包括重复数据)

    UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来; (包括重复数据)

    mysql>select country from Websites

    union select country from apps;

    3.2 update 单表,根据查询结果,修改数据

    例子:需要根据查出来的车牌id,修改对应的这些id的车辆某个数据

    $update truck inner join (select id from truck where plate like "粤%" limit 30) t1 on t1.id=truck.id set carrier_code=' 2016040' ;

    延伸---------------------------------------------------------------------------------------------

    1.MySQL不允许SELECT FROM后面指向用作UPDATE的表。

    2.MySQL不允许UPDATE在SET时直接SELECT

    3.Currently, you cannot update a table and select from the same table in a subquery.[other table 也是一样]

    4.MySQL是通过临时表来实现FROM子句里面的嵌套查询

    # update select 语句(注意:必须使用inner join)

    #语法:update a inner join (select yy from b) c on a.id=c.id set a.xx=c.yy;

    Happy Ending~,最近沉迷于sql语句学习无法自拔。

    数据库练习题:https://www.cnblogs.com/aqxss/p/6563625.html

    展开全文
  • 在上篇文章史上最简单MySQL教程详解(基础篇)之数据库设计范式及应用举例我们介绍过,在关系型数据库中,我们通常为了减少数据的冗余量将对数据进行规范,将数据分割到不同的中。当我们需要将这些数据重新合成...


    在上篇文章史上最简单MySQL教程详解(基础篇)之数据库设计范式及应用举例我们介绍过,在关系型数据库中,我们通常为了减少数据的冗余量将对数据表进行规范,将数据分割到不同的表中。当我们需要将这些数据重新合成一条时,就需要用到我们介绍来将要说到的表连接。

    常用术语

    • 冗余(Redundancy):存储两次数据,以便使系统更快速。
    • 主键(Primary Key):主键是唯一的。同一张表中不允许出现同样两个键值。一个键值只对应着一行。
    • 外键(Foreign Key):用于连接两张表。

    表连接的方式

    • 内连接
    • 外连接
    • 自连接

    我们接下来将对这三种连接进行详细的介绍。

    数据准备

    我们需要创建下面的数据表来作为示例:

    student表

    表结构:

    字段 解释
    studentId 学号(主键)
    name 姓名
    phone 电话
    collegeId 学生所在学院ID(外键)

    SQL语句:

    CREATE TABLE `student` (
      `studentId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `collegeId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`studentId`),
      KEY `collegeId` (`collegeId`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    数据:
    这里写图片描述

    college表

    表结构:

    字段 解释
    collegeId 学院ID(主键)
    collegeName 学院名

    SQL语句:

    CREATE TABLE `college` (
      `collegeId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `collegeName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`collegeId`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    数据:
    这里写图片描述

    内连接

    内连接就是表间的主键与外键相连,只取得键值一致的,可以获取双方表中的数据连接方式。语法如下:

    SELECT 列名1,列名2... FROM1 INNER JOIN2 ON1.外键=表2.主键 WhERE 条件语句;

    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student INNER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    +——+————-+
    3 rows in set (0.04 sec)

    这样,我们就成功将【student】表中的【name】和【college】表中的【collegeName】进行了重新结合,并检索出来。

    外连接

    与取得双方表中数据的内连接相比,外连接只能取得其中一方存在的数据,外连接又分为左连接和右连接两种情况。接下来,我们将介绍这两种连接方式。

    左外连接

    左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。语法如下:

    SELECT 列名1 FROM1 LEFT OUTER JOIN2 ON1.外键=表2.主键 WhERE 条件语句;

    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student LEFT OUTER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    | 赵六 | NULL |
    | 钱七 | NULL |
    +——+————-+
    5 rows in set (0.00 sec)

    我们可以看出,与内连接查询结果不同的是:【赵六】、【钱七】这两个学生虽然没有学校ID但是也被查出来了,这就是我们所说的,他会以左连接中的左表的全部数据作为基准进行查询。

    右外连接

    同理,右连接将会以右边作为基准,进行检索。语法如下:

    SELECT 列名1 FROM1 RIGHT OUTER JOIN2 ON1.外键=表2.主键 WhERE 条件语句;

    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student RIGHT OUTER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    | NULL | 厦大 |
    +——+————-+
    4 rows in set (0.00 sec)

    我们可以看出,这里就是以右边的表【college】为基准进行了检索,因为【student】中并没有【厦大】的学生,所以检索出来的为【NULL】

    注意事项:

    • 内连接是抽取两表间键值一致的数据,而外连接(左连接,右连接)时,是以其中一个表的全部记录作为基准进行检索。
    • 左连接和右连接只有数据基准的区别,本质上是一样的,具体使用哪一种连接,根据实际的需求所决定
    • 无论是内连接还是外连接,在查询的时候最好使用【表名.列名】的方式指定需要查询的列名,否则一旦两个表中出现了列名一致的数据时,可能会报错,养成良好的习惯很重要。
    • 表的别名:其实我们在查询的过程中,如果遇到了特别复杂的数据表名,我们可以通过取别名的方式来实现,使用的是我们以前使用过的【AS】语句,例如,我们的内连接就可以化简为下面的语句:
      SELECT s.name,c.collegeName FROM student AS s INNER JOIN college AS c ON s.collegeId = c.collegeId;查询结果一致,是不是瞬间觉得语句简洁很多呢?

    自连接

    自连接顾名思义就是自己跟自己连接,有人或许会问,这样的连接有意义吗?答案是肯定的。
    例如,我们将【student】的数据改为下图:
    这里写图片描述
    运行结果如图:

    mysql> SELECT * FROM student s ,student a where a.collegeId=s.collegeId AND a.name <> s.name ORDER BY a.collegeId;
    +———–+——+——-+———–+———–+——+——-+———–+
    | studentId | name | phone | collegeId | studentId | name | phone | collegeId |
    +———–+——+——-+———–+———–+——+——-+———–+
    | 4 | 赵六 | 136 | 11 | 1 | 张三 | 139 | 11 |
    | 1 | 张三 | 139 | 11 | 4 | 赵六 | 136 | 11 |
    | 5 | 钱七 | 135 | 22 | 2 | 李四 | 130 | 22 |
    | 2 | 李四 | 130 | 22 | 5 | 钱七 | 135 | 22 |
    +———–+——+——-+———–+———–+——+——-+———–+
    4 rows in set (0.00 sec)

    可以看出,我们就将【student】表中在同一个学校的学生查出来了。
    语句释义:

    • 【student s】和【student a】的含义就是分别给我们的【student】表取了两个不同的别名;
    • 【a.collegeId = s.collegeId AND a.name <> s.name 】的含义是找出【collegeId】相同,但是【name】不同的人.
    • 【ORDER BY a.collegeId;】将结果顺序输出;

    自连接的使用情况还是很多的,比如当我们找某个站点所经过的所有公交等,都可以采用自连接的方式进行检索;

    子查询

    通常我们在查询的SQL中嵌套查询,称为子查询。子查询通常会使复杂的查询变得简单,但是相关的子查询要对基础表的每一条数据都进行子查询的动作,所以当表单中数据过大时,一定要慎重选择。基本语法如下:

    SELECT 列名1 ...FROM 表名 WHERE 列名 比较运算符 (SELECT 命令);

    例如:我们利用上面的内连接的例子,在它的基础上查出学校为【清华】的学生的姓名

    mysql> SELECT * FROM (SELECT student.name,college.collegeName FROM student INNER JOIN college ON student.collegeId = college.collegeId)b WHERE b.collegeName = ‘清华’;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 赵六 | 清华 |
    +——+————-+
    2 rows in set (0.00 sec)

    查询成功。
    到此,已经介绍完了所有关于MySQL基础篇的内容,接下来,我们将介绍史上最简单MySQL教程详解(进阶篇)之存储引擎及默认引擎设置

    展开全文
  • 简单地,可以对每个进行一表查询,然后将结果在应用程序中进行关联。例如,下面这个查询: select * from tag join tag_post on tag_post.tag_id = tag.id join post on tag_post.post_id = post.id where ...
    很多高性能的应用都会对关联查询进行分解。

    简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联。例如,下面这个查询:

    select * from tag
    
    join tag_post on tag_post.tag_id = tag.id
    
    join post on tag_post.post_id = post.id
    
    where tag.tag=’mysql’;
    
    

    可以分解成下面这些查询来代替:

    Select * from tag where tag=’mysql’;
    
    Select * from tag_post where tag_id=1234;
    
    Select * from post where id in(123,456,567,9989,8909);
    

    到底为什么要这样做?

    咋一看,这样做并没有什么好处,原本一条查询,这里却变成了多条查询,返回结果又是一模一样。

    事实上,用分解关联查询的方式重构查询具有如下优势:

    1. 让缓存的效率更高。

    许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。

    1. 将查询分解后,执行单个查询可以减少锁的竞争。

    2. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。

    3. 查询本身效率也可能会有所提升

    4. 可以减少冗余记录的查询。

    5. 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套环关联,某些场景哈希关联的效率更高很多。

    参考:
    https://www.zhihu.com/question/68258877
    https://www.zhihu.com/question/56236190

    展开全文
  • A,B数据规模十几万,数据规模都不大,单机MySQL够用了,在单机的基础上要关联两表的数据,先说一个极端情况,A,B都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络...

    A,B两个表数据规模十几万,数据规模都不大,单机MySQL够用了,在单机的基础上要关联两表的数据,先说一个极端情况,A,B两个表都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络IO成了瓶颈,这个时候两次十万行结果集的拉去可能远小于1次亿级别的结果集的拉取,那么将关联合并拉到service层做更快。但实际业务中一般不会有这么蠢的行为,一般关联会有连接条件,并且连接条件上会有索引,一般是有一个结果集比较小,拿到这个结果集去另一张表去关联出其它信息,如果放到service层去做,最快的方式是,先查A表,得到一个小的结果集,一次rpc,再根据结果集,拼凑出B表的查询条件,去B表查到一个结果集,再一次rpc,再把结果集拉回service层,再一次rpc,然后service层做合并,3次rpc,如果用数据库的join,关联结果拉回来,一次rpc,帮你省了两次rpc,当然数据库上做关联更快,对应到数据库就是一次blk nested loop join,这是业务常用情况。

    但是确实大多数业务都会考虑把这种合并操作放到service层,我觉得有几方面考虑:

    第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,而将数据库当成一种带事务能力的kv系统来使用,这是一种重业务,轻DB的架构思路

    第二:很多复杂的业务可能会由于发展的历史原因,一般不会只用一种数据库,一般会在多个数据库上加一层中间件,多个数据库之间还能做毛的join,自然业务会抽象出一个service层,降低对数据库的耦合。

    第三:对于一些大型公司由于数据规模庞大,不得不对数据库进行分库分表,这个问题我在《阿里为什么要禁用三表以上的join》上也回答过,对于分库分表的应用,使用join也受到了很多限制,除非业务能够很好的根据sharding key明确要join的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。举一个很常见的业务例子,在分库分表中,要同步更新两个表,这两个表位于不同的物理库中,为了保证数据一致性,一种做法是通过分布式事务中间件将两个更新操作放到一个事务中,但这样的操作一般要加全局锁,性能很捉急,而有些业务能够容忍短暂的数据不一致,怎么做?让它们分别更新呗,但是会存在数据写失败的问题,那就起个定时任务,扫描下A表有没有失败的行,然后看看B表是不是也没写成功,然后对这两条关联记录做订正,这个时候同样没法用join去实现,只能将数据拉到service层应用自己来合并了。。。

    为什么互联网公司不让使用join?

    第一,不利于写操作。执行读操作时,会锁住被读的数据,阻塞其他业务对该部分数据的更新操作(U or D)。如果涉及多个聚合函数(缓存中没有max or min时),相当于同时锁住多张表,不能进行读写,直接影响其他业务,这影响了系统的整体性能;

    第二,不利于维护。业务发生变动时,比如join中一张表改了,可能导致系统中原有的sql不可用,最终导致基于该SQL执行结果的上层显示失败(也意味着以往的开发工作已无效)。如果使用步骤一和步骤二的方式,只需要修改其中一个步骤就行。实际工作中,也就是只需要修改其中的一个service(对应一张表)即可。

    既影响性能,又不利于维护,所以我们尽量不要用join。另外,集团不让用三张表,是在OLTP场景中。不要一叶障目哦,如果是在OLAP的应用场景中,使用join是非常合适的。

    我们平台过一段时间就会把生产数据库的慢查询导出来分析,要嘛修改写法,要嘛新增索引。以下是一些笔记、总结整理

     

    慢查询排查

            show status;  // 查询mysql数据库的一些运行状态

            show status like 'uptime'; // 查看mysql数据库启动多长时间,myisam存储引擎长时间启动需要进行碎片整理

            查看慢查询

            show status like 'slow_queries';

            查询慢查询时间

            show variables like 'long_query_time';

            设置慢查询时间 

            set long_query_time = 0.5;

    分析执行情况 EXPLAIN详解

            分析查询语句的执行情况,可以分析出所查询的表的一些特征

            EXPLAIN/DESCRIBE/DESCSELECT * FROM...;

    每个字段说明:

    idSELECT标识符。这是SELECT的查询序列号。

    select_type:表示SELECT语句的类型。它可以是以下几种取值:

        SIMPLE:表示简单杳询,其中不包括连接查询和子查询;

        PRIMARY:表示主查询,或者最外层的查询语句;

        UNION:表示连接查询的第2个或后面的查询语句;

        DEPENDENT UNION:连接查询中的第2个或后面的SELECT语句,取决于外面的查询;

        UNION RESULT:连接查询的结果;

        SUBQUERY:子查询中的第一个SELECT语句;

        DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询;

        DERIVED:导出表的SELECT (FROM语句的子查询)。

    table:表示查询的表。

    type:表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型:

        (1) system

            该表仅有一行的系统表。这是const连接类型的一个特例。

        (2) const

            数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的査询优化中作为常量对待。const表查询速度很快,因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

            在下面查询中,tb1_name可用const表:

            SELECT  *  from tb1_name WHERE primary_key=1;

            SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2

        (3) eq_ref

            对于每个来自前面的表的行组合,从该表中读取一行。当一个索引的所有部分都在查询中使用,并且索引是UNIQUE或者PRIMARY KEY时,即可使用这种类型。

            eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。

            在下面例子中,MySQL可以使用eq_ref来处理ref_tables:

            SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn;

            SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1;

        (4)ref

            对于来自前面的表的任意组合,将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况,或者查询中使用了索引列在左子集,既索引中左边的部分列组合。ref可以用于使用=或者<=>操作符的带索引的列。

            以下的几个例子中,mysql将使用 ref 来处理ref_table:   

            select * from ref_table where key_column=expr; 

            select * from ref_table,other_table where ref_table.key_column=other_table.column; 

            select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;

        (5)ref_or_null

            这种连接类型类似ref,不同的是mysql会在检索的时候额外的搜索包含null值的记录。在解决子查询中经常使用该链接类型的优化。

            在以下的例子中,mysql使用ref_or_null 类型来处理 ref_table:

            select * from ref_table where key_column=expr or key_column is null;

        (6)index_merge

            该链接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。

        (7)unique_subquery

            该类型替换了下面形式的IN子查询的ref:

            value in (select primary_key from single_table where some_expr)

        (8)index_subquery

            这种连接类型类似 unique_subquery。可以替换IN子查询,不过它用于在子查询中没有唯一索引的情况下,

            例如以下形式:

            value in (select key_column from single_table where some_expr)

        (9)range

            只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。ken_len包含所使用索引的最长关键元素。当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in操作符,用常量比较关键字列时,类型为range。

            下面介绍几种检索制定行的情况:

            select * from tbl_name where key_column = 10; 

            select * from tbl_name where key_column between 10 and 20; 

            select * from tbl_name where key_column in (10,20,30); 

            select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);

        (10)index

             连接类型跟ALL一样,不同的是它只扫描索引树。它通常会比ALL快点,因为索引文件通常比数据文件小。

        (11)ALL

            对于前面的表的任意行组合,进行完整的表扫描。如果第一个表没有被标识为const的话就不大好了,在其他情况下通常是非常糟糕的。正常地,可以通过增加索引使得能从表中更快的取得记录以避免ALL。

    possible_keys

            possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。如果这个字段的值是NULL,就表示没有索引被用到。这种情况下,就可以检查WHERE子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引,然后再用explain 检查一下。

    key

           key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是NULL。想要让MySQL强行使用或者忽略在 possible_keys字段中的索引列表,可以在查询语句中使用关键字force index, use index或 ignore index。参考SELECT语法。

    key_len

            key_len 字段显示了mysql使用索引的长度。当key 字段的值为NULL时,索引的长度就是NULL。注意,key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

    ref

            表示使用哪个列或常数与索引一起来查询记录。

    rows

            显示MySQL在表中进行查询时必须检查的行数。 

    Extra

            本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释

            distinct

            MySQL当找到当前记录的匹配联合结果的第一条记录之后,就不再搜索其他记录了。 

            not exists

            MySQL在查询时做一个LEFT JOIN优化时,当它在当前表中找到了和前一条记录符合LEFT JOIN条件后,就不再搜索更多的记录了。下面是一个这种类型的查询例子:

            select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

            假使 t2.id 定义为 not null。这种情况下,MySQL将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时,这就意味着 t2.id 肯定不会都是null,就不会再在 t2 中查找相同id值的其他记录了。也可以这么说,对于 t1 中的每个记录,mysql只需要在t2 中做一次查找,而不管在 t2 中实际有多少匹配的记录。

            range checked for each record (index map: #)

            mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。这个过程不会很快,但总比没有任何索引时做表连接来得快。

            using filesort

            MySQL需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了,对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。

            using index

            字段的信息直接从索引树中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。

            using temporary

            mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group by和order by子句,它以不同的方式列出了各个字段。

            using where

            where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录,否则的话当查询的extra字段值不是using where并且表连接类型是all或index时可能表示有问题。

            如果你想要让查询尽可能的快,那么就应该注意extra字段的值为using filesort和using temporary的情况。    

    其他

    索引没起作用的情况

    1. 使用LIKE关键字的查询语句

            在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。

    2. 使用多列索引的查询语句

            MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。

    3. 使用OR关键字的查询语句

            查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才会使用索引。否则,查询将不使用索引。

    优化数据库结构

            合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

    1. 将字段很多的表分解成多个表

            对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

    2. 增加中间表

            对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

    3. 增加冗余字段

            设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅。但是合理加入冗余字段可以提高查询速度。 

    切分查询

            比如我们要删除旧的数据,可能需要一次性删除很多数据会锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切成多个较小的查询可以尽可能小的影响MySQL性能,同时还可以减少MySQL复制的延迟,如下:

            DELETE FROM table WHERE create_date < NOW(); 

            替换为

            rows_affected = 0

            do { 

                rows_affected = do_query(

                    "DELETE FROM table WHERE create_date < NOW() LIMIT 10000"    

                )

            } while rows_affected > 0

            一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法,如果每次暂停一会会更好。

    分解关联查询

            有时候将一个大的查询分解为多个小查询是很有必要的。

            很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效,例如:

            SELECT * FROM tag 

            JOIN tag_post ON tag_id = tag.id

            JOIN post ON tag_post.post_id = post.id

            WHERE tag.tag = 'mysql';

            分解为:

            SELECT * FROM tag WHERE tag = 'mysql';

            SELECT * FROM tag_post WHERE tag_id = 1234;

            SELECT * FROM post WHERE post.id in (123,456,567);

            有很多场景都可以使用:比如当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

    优化LIMIT分页

            在分页偏移量很大的时候,如LIMIT 10000,20这样的查询,MySQL需要查询10020条记录然后只返回最后20条,前面10000条记录都被抛弃,这样代价非常高。

            优化的最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列,对于偏移量很大的时候,这样做的效率回提升很大,如下:

            SELECT file_id, description FROM film ORDER BY title LIMIT 50, 5;

            修改为:

            SELECT film.film_id, film.description FROM film 
            INNER JOIN (SELCT film_id FROM film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

            这里“延迟关联”将大大提升查询效率,它让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询需要的所有列。

    展开全文
  • 文章目录
  • 引自高性能MySQL
  • 总的来说,MySQL认为任何一个查询都是一关联”——并不仅仅是一个查询需要到匹配才叫关联,所以在MySQL中,每一个查询,每一个片段(包括子查询,甚至基于单的SELECT)都可能是关联。 所以理解MySQL如何...
  • MySQL表关联查询效率高点还是多表查询效率高,为什么? 《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么?这样的话那sql要怎么写? 转载于:...
  • 大圣网络 2017-01-31 09:19 ...连接查询 ...连接查询: 将多张表(>=2)...连接查询: join, 使用方式: 左 join 右;左: 在join关键字左边的;右: 在join关键字右边的 连接查询分类:SQL中将连接查询分成
  • MYSQL表关联查询

    2012-05-18 14:49:07
    在接触这个语句之前 我要到数据库查询不同的 内容我一般需要执行2sql语句 循环2。 而现在有了这个语句 可以简化成只执行1语句 循环一 $result = mysql_query(” SELECT states.state_id, states....
  • Mysql表查询效率的研究(一)本文探究了mysql InnoDB引擎在多表查询的应用场景下,使用子、内连接和左联接运行速度的差别,并且比较了索引使用与否对查询效率的影响。 第一部分简略地概括了索引、子表查询、...
  • mysql表查询 3.关联查询 使用的数据库为mysql表查询 2.建立多数据库案例中的数据库。 1.1内连接查询 使用内连接查询,只有满足条件的结果才会显示。 1)内连接(第一种语法) select i.name,b.class,s....
  • 这个SQL查询关联两个数据,一个是攻击IP用户主要是记录IP的信息,如第一攻击时间,地址,IP等等,一个是IP攻击次数主要是记录每天IP攻击次数。而需求是获取某天攻击IP信息和次数。(以下SQL...
  • 本文转自:https://blog.csdn.net/Tim_phper/article/details/78344444概述:交代一下背景,这算是一项目经验吧,属于公司一... 这个SQL查询关联两个数据,一个是攻击IP用户主要是记录IP的信息,如第一攻击...
  • 现在想一性把跟ol_warehouse_product这张表有关联的数据查出来,出现重复数据,找了好久资料都没办法解决,最开始的时候有很多重复数据,通过group by之后现在就剩下条了,但是数据库ol_warehouse_product中...
  • B:demo_info,信息中有提交人和确认人个字段(其他字段暂不提及),都关联了用户的用户ID 问题场景:想要在加载信息列表的时候,直接一关联查询到提交人和确认人的姓名。 SQL: SELECT demo...
1 2 3 4 5 ... 20
收藏数 107,560
精华内容 43,024