首页 / MYSQL / 为什么MySQL不使用JOIN上的主键加上ORDER？

为什么MySQL不使用JOIN上的主键加上ORDER？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了为什么MySQL不使用JOIN上的主键加上ORDER？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7156字，纯文字阅读大概需要11分钟。

内容图文

这对你来说很简洁(显然是MySQL)：

# Setting things up
DROP DATABASE IF EXISTS index_test_gutza;
CREATE DATABASE index_test_gutza;
USE index_test_gutza;

CREATE TABLE customer_order (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
);
INSERT INTO customer_order
    (id, invoice)
    VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5);

CREATE TABLE customer_invoice (
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_no MEDIUMINT UNSIGNED DEFAULT NULL,
    invoice_pdf LONGBLOB,
    PRIMARY KEY(id)
);
INSERT INTO customer_invoice
    (id, invoice_no)
    VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5);

# Ok, here's the beef
EXPLAIN
    SELECT co.id
    FROM customer_order AS co;

EXPLAIN
    SELECT co.id
    FROM customer_order AS co
    ORDER BY co.id;

EXPLAIN
    SELECT co.id, ci.invoice_no
    FROM customer_order AS co
    LEFT JOIN customer_invoice AS ci ON ci.id=co.invoice;

EXPLAIN
    SELECT co.id, ci.invoice_no
    FROM customer_order AS co
    LEFT JOIN customer_invoice AS ci ON ci.id=co.invoice
    ORDER BY co.id;

底部有四个EXPLAIN语句.前两个完全符合您的期望：

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | co    | index | NULL          | PRIMARY | 3       | NULL |    5 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

第三个已经很有趣 – 请注意customer_order中的主键如何不再使用：

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | co    | ALL    | NULL          | NULL    | NULL    | NULL                        |    5 |             |
|  1 | SIMPLE      | ci    | eq_ref | PRIMARY       | PRIMARY | 3       | index_test_gutza.co.invoice |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+

然而,第四个是zinger – 简单地在主键上添加ORDER BY导致customer_order上的filesort(这是预期的,因为它已经在上面被弄糊涂了)：

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                         | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+
|  1 | SIMPLE      | co    | ALL    | NULL          | NULL    | NULL    | NULL                        |    5 | Using filesort |
|  1 | SIMPLE      | ci    | eq_ref | PRIMARY       | PRIMARY | 3       | index_test_gutza.co.invoice |    1 | Using index    |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+----------------+

文件排序！而且我从不使用除customer_order表中的主键以外的任何内容进行排序,以及在customer_invoice表中使用JOIN的主键.那么,在所有好的和正确的名义中,为什么它会突然切换到filesort？！更重要的是,我该如何避免这种情况？为了记录,我很乐意接受一个记录在案的答案,解释为什么这是无法避免的(如果是这样的话.)

正如您现在可能怀疑的那样,这实际上是在生产中发生的,虽然这些表并不是很大(只有数百条记录),但发票表上的文件排序(包含PDF文件)在运行时会终止服务器类似于上面的查询(我需要知道哪些订单已经发出发票,哪些不是).

在你问之前,我设计了数据库,我认为我可以安全地将PDF文件存储在该表中,因为我从来不需要任何搜索查询 – 我总是有它的主键！

更新(评论概要)

以下是下面评论中建议的概要,因此您不必阅读所有内容：

> *你应该在customer_order.invoice *上添加一个键 – 我实际上在生产中尝试过,它没有区别(因为它不应该)
>您应该使用USE INDEX – 尝试过,不起作用.我也试过FORCE INDEX – 没有结果(没有任何改变)
>你过度简化了用例,我们需要实际的生产查询 – 我可能在第一次迭代中剥离了一些,所以我更新了它(我刚刚在SELECT中添加了ci.invoice_no,用于最后几次查询).为了记录,如果有人真的很好奇,这里是生产查询,完全一样(这将检索订单的最后一页)：

SELECT
    corder.id,
    corder.public_id,
    CONCAT(buyer.fname," ",buyer.lname) AS buyer_name,
    corder.status,
    corder.payment,
    corder.reserved AS R,
    corder.tracking_id!="" as A,
    corder.payment_received as pay_date,
    invoice.invoice_no AS inv,
    invoice.receipt_no AS rec,
    invoice.public AS pub_inv,
    proforma.proforma_no AS prof,
    proforma.public AS pub_pf,
    corder.rating,
    corder.rating_comments!="" AS got_comment
FROM
    corder
LEFT JOIN user as buyer ON buyer.id=corder.buyer
LEFT JOIN invoice as invoice ON invoice.id=corder.invoice
LEFT JOIN invoice as proforma ON proforma.id=corder.proforma
ORDER BY
    id DESC 
LIMIT 400, 20;

上面的查询(这也是我在生产中运行的那个)大约需要14秒才能运行.这是在生产中执行的简化查询,如上面的用例所示：

SELECT
    corder.id,
    invoice.invoice_no
FROM
    corder
LEFT JOIN invoice ON invoice.id=corder.invoice
ORDER BY
    corder.id DESC 
LIMIT 400, 20;

这个需要13秒才能运行.请注意,只要我们谈论结果的最后一页(我们是),LIMIT就没有任何区别.也就是说,在涉及filesort时,检索最后12个结果或所有412个结果之间绝对没有显着差异.

结论

ypercube的答案不仅正确,而且不幸的是它似乎是唯一合法的答案.我试图进一步将条件与字段分开,因为SELECT * FROM corder子查询最终可能涉及大量数据,如果corder本身包含LONGBLOB(并且在子查询中复制主查询中的字段是不优雅的),但不幸的是它没有似乎工作：

SELECT
    corder.id,
    corder.public_id,
    CONCAT(buyer.fname," ",buyer.lname) AS buyer_name,
    corder.status,
    corder.payment,
    corder.reserved AS R,
    corder.tracking_id != "" AS A,
    corder.payment_received AS pay_date,
    invoice.invoice_no AS inv,
    invoice.receipt_no AS rec,
    invoice.public AS pub_inv,
    proforma.proforma_no AS prof,
    proforma.public AS pub_pf,
    corder.rating,
    corder.rating_comments!="" AS got_comment
FROM
    corder
LEFT JOIN user as buyer ON buyer.id = corder.buyer
LEFT JOIN invoice AS invoice ON invoice.id = corder.invoice
LEFT JOIN invoice AS proforma ON proforma.id = corder.proforma
WHERE corder.id IN (
    SELECT id
    FROM corder
    ORDER BY id DESC
    LIMIT 400,20
)
ORDER BY
    corder.id DESC;

此操作失败,并显示以下错误消息：

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

我使用的是MySQL 5.1.61,这在5.1系列中是最近才出现的(显然这在5.5.x中也不支持).

解决方法:

你可以尝试这个版本(它基本上首先得到的是corder表的420行,保留其中的20行然后执行3个外连接)：

SELECT
    corder.id,
    corder.public_id,
    CONCAT(buyer.fname," ",buyer.lname) AS buyer_name,
    corder.status,
    corder.payment,
    corder.reserved AS R,
    corder.tracking_id != "" AS A,
    corder.payment_received AS pay_date,
    invoice.invoice_no AS inv,
    invoice.receipt_no AS rec,
    invoice.public AS pub_inv,
    proforma.proforma_no AS prof,
    proforma.public AS pub_pf,
    corder.rating,
    corder.rating_comments!="" AS got_comment
FROM
    ( SELECT * 
      FROM corder
      ORDER BY
        id DESC 
      LIMIT 400, 20
    )
    AS corder
LEFT JOIN user as buyer ON buyer.id = corder.buyer
LEFT JOIN invoice AS invoice ON invoice.id = corder.invoice
LEFT JOIN invoice AS proforma ON proforma.id = corder.proforma
ORDER BY
    corder.id DESC ;

内容总结

以上是互联网集市为您收集整理的为什么MySQL不使用JOIN上的主键加上ORDER？全部内容，希望文章能够帮你解决为什么MySQL不使用JOIN上的主键加上ORDER？所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/905722.html

来源：【匿名】

【上一篇】mysql – 带有多个分隔符的sql-maven-plugin 【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【为什么MySQL不使用JOIN上的主键加上ORDER？】教程文章相关的互联网学习教程文章

Mysql让主键归0

1、Mysql 让主键归0:TRUNCATE table1;2、只清空数据表，主键不归0：DELETE FROM table1; 参考：https://www.csdn.net/gather_2d/MtTaMgxsMjcxMy1ibG9n.html原文：https://www.cnblogs.com/sea-stream/p/11297010.html

MySQL 主键与索引的联系与区别分析

关系数据库依赖于主键，它是数据库物理模式的基石。主键在物理层面上只有两个用途：惟一地标识一行。作为一个可以被外键有效引用的对象。索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。下面是主键和索引的一些区别与联系。 1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可...

向Mysql主键自增长表中添加数据并返回主键

表level,其主键为lid1.select max(id) from table 　　查询语句：SELECT MAX(lid) FROM LEVEL　　　　　　　返回插入主键2.select LAST_INSERT_ID(id) from table　　查询语句：SELECT LAST_INSERT_ID(lid) FROM LEVEL;　　　　　　　返回主键列表，最后一个值为插入主键3.select @@identity from table　　查询语句：SELECT @@identity FROM level　　　　　　　返回n（表的行数）个主键，全为插入主键，取其中一个就行原文：htt...

MYSQL:基础—主键【代码】

MYSQL:基础—主键1.什么是主键　　表中的每一行都应该具有可以唯一标识自己的一列(或一组列)。而这个承担标识作用的列称为主键。　　如果没有主键，数据的管理将会十分混乱。比如会存在多条一模一样的记录，删除和修改特定行十分困难。2.那些列可以作为主键：　　任何列都可以作为主键，只要它满足以下条件：　　　　? 任何两行都不具有相同的主键值。就是说这列的值都是互不相同的。　　　　? 每个行都必须具有一个主...

mysql 查看所有没有主键的表

SELECT #CONCAT("truncate table ",table_name,";") table_name FROM information_schema. TABLESWHERE table_schema = ‘scdm‘AND TABLE_NAME NOT IN ( SELECT table_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING ( constraint_name, table_schema, table_name ) WHERE t.constraint_type = ‘PRIMA...

sqoop从hive导入数据到mysql时出现主键冲突【代码】

今天在将一个hive数仓表导出到mysql数据库时出现进度条一直维持在95%一段时间后提示失败的情况，搞了好久才解决。使用的环境是HUE中的Oozie的workflow任何调用sqoop命令，该死的oozie的日志和异常提示功能太辣鸡了，最后发现是重复数据导致数据进入mysql表时出现主键冲突进而导致数据同步失败。（1）众所周知hive表是没有主键与索引的，但是mysql的表一般在创建时就会指定主键，所以在把hive表中的数据导入mysql表的时候通常会使用...

mysql删除自增主键【代码】

在MySQL中删除主键需要两步.(1)如果有auto_increment，先删除之;(2)删除主键约束 primary key1、alter table table1 modify id int(11);mysql> desc table1; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name |...

MySQL里Create Index 能否创建主键 Primary Key

MySQL里Create Index 能否创建主键 Primary Key? 答案：不能，必须用 Alter table 创建。 MySQL一个索引列最大允许的有效长度，不是列的所有数据都被索引的MyISAM 是 1000字节 InnoDB 是 767 字节注意这里是字节。原文：http://www.jb51.net/article/18928.htm

javaweb学习总结(三十七)——获得MySQL数据库自动生成的主键【代码】

测试脚本如下：1createtable test1 2( 3 id intprimarykey auto_increment, 4 name varchar(20) 5 );　　测试代码： 1package me.gacl.demo;2 3import java.sql.Connection;4import java.sql.PreparedStatement;5import java.sql.ResultSet;6import me.gacl.utils.JdbcUtils;7 8publicclass Test {9publicstaticvoid main(String[] args) { 10 Connection conn = null; 11 PreparedStatement st = null;...

Mysql索引介绍及常见索引(主键索引、唯一索引、普通索引、全文索引、组合索引)的区别

Mysql各种索引区别：普通索引：最基本的索引，没有任何限制唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。主键索引：它是一种特殊的唯一索引，不允许有空值。全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。 Mysql索引概念：说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会...

删除没有主键的重复mysql行【代码】

嗨我有一个没有主键的mysql表,我需要删除重复的行.我怎么能这样做？user_id category_id 1 2 1 3 1 4 1 2 2 2 2 3 2 2解决方法:CREATE TABLE temp SELECT DISTINCT * FROM tablename;ALTER TABLE tablename RENAME junk;ALTER TABLE temp RENAME tablename;

详解innodb_index_stats导入数据时提示表主键冲突的错误

下面小编就为大家带来一篇innodb_index_stats导入备份数据时报错表主键冲突的解决方法。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧故障描述percona5.6，mysqldump全备份，导入备份数据时报错Duplicate entry hoc_log99-item_log_27-PRIMARY-n_diff_pfx01 for key PRIMARY故障原因查看了下这个主键应该是MySQL系统库下的系统表innodb_index_statsmysql> show create table innodb_index_stats\...

Redis学习之主键失效机制和LRU机制

一、Redis 的主键失效内部机制 Key失效机制有两种：消极方法（当访问该key的时候，判断Key是否过期，如果过期，删除key，并返回nil）积极方法（定时删除一部分失效的key）失效的内部表示 Redis内部使用一个dict来保存所有Key-Value对，使用expires来保存一、Redis 的主键失效内部机制 Key失效机制有两种：消极方法（当访问该key的时候，判断Key是否过期，如果过期，删除key，并返回nil）积极方法（定时删除一部分失效的key）失...

【PHP】MySQL获取插入数据的主键(自增加ID)

为防止主键冲突，设计DB的时候常常使用自增加（auto_increment 型）字段。因此插入数据前往往不知道改记录的主键是什么，为了方便后续或级联查询，我们需要在插入一行记录后获得DB自动生成的主键。这里稍微整理了下几种方法： DB中查询通用： SELECT max(id) FROM user; 这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。 MySQL： SELECT LAST_INSERT_ID(); 重点: 假如你使...

mysql下创建字段并设置主键的php代码_PHP教程

代码如下:mysql_select_db("hills_database_test",$dbcon); $alterpkadd="alter table hills_testcreatetable add id int(32) not null auto_increment primary key;"; mysql_query($alterpkadd,$dbcon); mysql_close($dbcon); 嘿嘿。。好容易哟。。一句话搞定http://www.bkjia.com/PHPjc/321900.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/321900.htmlTechArticle 代码如下:mysql_select_db("hills_database_test",$dbcon...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / 为什么MySQL不使用JOIN上的主键加上ORDER？

为什么MySQL不使用JOIN上的主键加上ORDER？

内容导读

内容图文

内容总结

内容备注

内容手机端

【为什么MySQL不使用JOIN上的主键加上ORDER？】教程文章相关的互联网学习教程文章

Mysql让主键归0

MySQL 主键与索引的联系与区别分析

向Mysql主键自增长表中添加数据并返回主键

MYSQL:基础—主键【代码】

mysql 查看所有没有主键的表

sqoop从hive导入数据到mysql时出现主键冲突【代码】

mysql删除自增主键【代码】

MySQL里Create Index 能否创建主键 Primary Key

javaweb学习总结(三十七)——获得MySQL数据库自动生成的主键【代码】

Mysql索引介绍及常见索引(主键索引、唯一索引、普通索引、全文索引、组合索引)的区别

删除没有主键的重复mysql行【代码】

详解innodb_index_stats导入数据时提示表主键冲突的错误

Redis学习之主键失效机制和LRU机制

【PHP】MySQL获取插入数据的主键(自增加ID)

mysql下创建字段并设置主键的php代码_PHP教程

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程