广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MYSQL INNODB中表数据的返回顺序问题
  • 405

0
分享到

MYSQL INNODB中表数据的返回顺序问题

2024-04-02 19:04:59 405人浏览 独家记忆
摘要

接上一篇： Http://blog.itpub.net/7728585/viewspace-2126344/ 如何证明INNODB辅助索引叶子结点KEY值相同的按照PRIMARY KEY排序 

接上一篇：
Http://blog.itpub.net/7728585/viewspace-2126344/
如何证明INNODB辅助索引叶子结点KEY值相同的按照PRIMARY KEY排序 


我们在上一篇中建立了表
Mysql> create table test (a int,b int,primary key(a),key(b));
Query OK, 0 rows affected (0.08 sec)
并且插入了数据
mysql> insert into test values(1,1);
Query OK, 1 row affected (0.08 sec)
mysql> insert into test values(5,1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test values(3,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(4,2);
Query OK, 1 row affected (0.59 sec)
mysql> insert into test values(10,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(7,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(8,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(11,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(20,6);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(21,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(19,7);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test values(16,7);
Query OK, 1 row affected (0.01 sec)

通过分析和程序跑出了在辅助索引列b中的存储顺序如下：
[root@ora12ctest test]# ./a.out test.ibd 4
Index_no is:42
find first one record!
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->


这里我们讨论一下SELECT * FROM 使用 USING INDEX 索引覆盖扫描B列的情况下和不使用索引使用索引而使用表本生的聚族索引的情况下数据
返回的顺序及性能比较。
首先给出猜测的结论：
1、在使用USING INDEX B列索引的时候，返回的顺序应该是和B列上辅助索引的返回顺序一致，也就是程序跑出的结果，在这里需要注意一点
   熟悉oracle的朋友如果DUMP过索引块，会看到索引的数据实际上INDEX KEY+ROWID，那么这种情况下肯定不能使用索引覆盖扫描(INDEX FAST FULL SCAN)，
   因为索引中压根不包含A值，但是INNODB却不同，他包含是PRIMARY KEY，所以使用到了USING INDEX.
2、在不使用任何索引，仅仅使用全表扫描，其实全表扫描也是按链表顺序扫描聚族索引B+树的叶子结点，所以我们可以推断他的顺序是和A列
   主键的排序一致的。
下面来证明这两点：
1、
mysql> explain select * from test force index(b);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | b    | 5       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
明显是Using index B索引
看看结果：
mysql> select * from test force index(b);
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  3 |    1 |
|  5 |    1 |
|  4 |    2 |
|  7 |    4 |
| 10 |    4 |
|  8 |    5 |
| 11 |    5 |
| 20 |    6 |
| 21 |    6 |
| 16 |    7 |
| 19 |    7 |
+----+------+
是不是和程序按照链表结构跑出来的一模一样
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->
这样结论1得到了验证

2、
mysql> explain  select * from test force index(primary);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
明显没有使用索引，那么我们可以断定他是使用了表本生也就是聚集索引的，按照聚集索引本生的链表进行返回，也就是按照主键
列A的顺序返回，因为是主键这个顺序也就自然固定了不用看B列的值了。来看看
mysql>  select * from test force index(primary);
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  3 |    1 |
|  4 |    2 |
|  5 |    1 |
|  7 |    4 |
|  8 |    5 |
| 10 |    4 |
| 11 |    5 |
| 16 |    7 |
| 19 |    7 |
| 20 |    6 |
| 21 |    6 |
+----+------+

可以看到确实如果结论2得到验证。


当然这个结论不光适合SELECT 全索引扫描的情况，为了证明这一点我增加了一列
C

mysql> alter table test add column c int;
Query OK, 0 rows affected (1.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update test set c=100;
Query OK, 12 rows affected (0.11 sec)
Rows matched: 12  Changed: 12  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

目的在于不然MYSQL使用Using index这个索引覆盖扫描的方式：
1、
mysql> explain select * from test force index(b) where b in(4,5,7);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | b             | b    | 5       | NULL |    6 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


mysql> select * from test force index(b) where b in(4,5,7);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 |    4 |  100 |
| 10 |    4 |  100 |
|  8 |    5 |  100 |
| 11 |    5 |  100 |
| 16 |    7 |  100 |
| 19 |    7 |  100 |
+----+------+------+
6 rows in set (0.01 sec)


2、
mysql> explain select * from test force index(primary) where b in(4,5,7);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    30.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql>  select * from test force index(primary) where b in(4,5,7);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 |    4 |  100 |
|  8 |    5 |  100 |
| 10 |    4 |  100 |
| 11 |    5 |  100 |
| 16 |    7 |  100 |
| 19 |    7 |  100 |
+----+------+------+
6 rows in set (0.00 sec)

可以清楚的看到他们的区别，也就是查询1是通过B列辅助索引的叶子结点查询出然后进行书签试查找主键回到的聚集索引，得出的
顺序当然是辅助索引B中B列的排序方式。而查询2当然也就是直接访问聚集索引过滤的条件，当然也就是主键的顺序。

然后我们讨论一下性能问题，虽然都是按照B+树的叶子结点进行顺序返回，但是聚集索引却要比辅助索引上的信息多，
也许要说这里聚集索引也是A,B列的值，辅助索引也是A,B列的值，
但是从前文看出：
./bcview  test.ibd 16 126 30|more
current block:00000003--Offset:00126--cnt bytes:21--data is:80000001000000000707a70000011b011080000001
current block:00000004--Offset:00126--cnt bytes:21--data is:8000000180000001
在聚集索引中有
000000000707a70000011b0110这样的信息实际上就是transaction id 和roll pointer
那么我们可以直观的判断出在同样的数据量下辅助索引的叶子PAGE会少于聚集索引的PAGE，
那么性能应该也会更好。

结论：
1、如果发现使用不同索引返回数据的顺序不一样，不要吃惊，不一样是正常，如果一样才要吃惊，INNODB全表扫描
   能够保证返回数据的顺序是主键的排序(虽然我们只验证单叶子结点情况，但是B+树的叶子结点是有PAGE和PAGE之间
   的指针的),这一点ORACLE中却不行，我曾经在ORACLE的书上看到，如果要保证排序只能用ORDER BY，但是这一点视乎
   在INNODB中并不适用，当然如果保险加上ORDER BY也是可以的，因为SORT的操作会被优化器忽略，这样以防万一。
   其实索引在INNODB和ORACLE中的另外一个功能就是避免排序。
2、create table test (a int,b int,primary key(a),key(b));这种方式如果where b= 在INNODB中可以使用索引覆盖扫描
   但是在ORACLE中不行，原因前面给出了。
3、在性能方面INNODB unsing index的性能在大多数情况下都要优于全表扫描(聚集索引)，原因也已经给出。
您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MYSQL INNODB中表数据的返回顺序问题

本文链接: https://www.lsjlt.com/news/46481.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • MYSQL INNODB中表数据的返回顺序问题
    接上一篇： http://blog.itpub.net/7728585/viewspace-2126344/ 如何证明INNODB辅助索引叶子结点KEY值相同的按照PRIMARY KEY排序  ...
    99+
    2022-10-18
  • ajax请求之返回数据顺序问题的示例分析
    小编给大家分享一下ajax请求之返回数据顺序问题的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！具体如下：ajax请求一...
    99+
    2022-10-19
  • Mysql怎么根据ID值的顺序返回结果
    Mysql怎么根据ID值的顺序返回结果，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。 今天遇到一个...
    99+
    2022-10-18
  • Mybatis返回map集合时,列的顺序与select不一致问题
    目录返回map集合,列的顺序与select不一致mybatis中返回map集合问题1.mapper.xml中写一个查询返回map的sql2.mapper.java 对应接收...
    99+
    2022-11-13
  • C++中的函数返回值问题
    目录1、返回值2、指针类型的函数——返回指针3、返回引用4、综合示例首先，强调一点，和函数传参一样，函数返回时也会做一个拷贝。 从某种角度上看，和传参一样，也...
    99+
    2022-11-13
  • Mysql使用sum()函数返回null的问题详解
    目录介绍问题验证解决区别参考总结介绍 SUM()函数用于计算一组值或表达式的总和，SUM()函数的语法如下： SUM(DISTINCT expression) SUM()函数是如何...
    99+
    2022-11-13
  • 怎么解决Mysql数据库提示innodb表不存在的问题
    本篇内容主要讲解“怎么解决Mysql数据库提示innodb表不存在的问题”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么解决Mysql数据库提示innodb表...
    99+
    2022-10-18
  • MySql中的longtext字段的返回问题及解决
    目录mysql中longtext字段的返回如下图所示解决方法Mysql中Text字段的范围汉字在utf8mb4中占用几个字符MySql中longtext字段的返回 最近开发中用到了longtext这种字段。在mysq...
    99+
    2022-07-05
    MySql中longtext字段 longtext字段返回 MySql的longtext
  • 有关mysql中sql的执行顺序的小问题
    今天工作中碰到一个sql问题，关于left join的，后面虽然解决了，但是通过此问题了解了一下sql的执行顺序 场景还原 为避免安全纠纷，把场景模拟。 有一个学生表-S，一个成绩表G CREATE T...
    99+
    2022-10-18
  • MySql中的longtext字段的返回问题如何解决
    这篇文章主要介绍了MySql中的longtext字段的返回问题如何解决的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇MySql中的longtext字段的返回问题如何解决文章都会有所收获，下面我们一起来看看吧。M...
    99+
    2023-07-02
  • SQL Server 数据库调整表中列的顺序操作方法及遇到问题
    SQL Server 数据库中表一旦创建，我们不建议擅自调整列的顺序，特别是对应的应用系统已经上线，因为部分开发人员，不一定在代码中指明了列名。表是否可以调整列的顺序，其实可以自主设置，我们建议在安装后设置...
    99+
    2022-10-18
  • Android中两个Activity之间数据传递及返回问题
    下面通过一个例子来详细说明 先上代码，再细细分析 MainActivity public class MainActivity extends Activity { ...
    99+
    2022-06-06
    数据 activity Android
  • 解决mybatis中resultType取出数据顺序不一致的问题
    目录mybatis resultType取出数据顺序不一致解决方法mybatis中resultType问题mybatis resultType取出数据顺序不一致 之前做一个页面的动态...
    99+
    2022-11-13
  • 如何解决处理后台返回json数据格式的问题
    小编给大家分享一下如何解决处理后台返回json数据格式的问题，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！最近在做一个移动端前端...
    99+
    2022-10-19
  • 详解mybatis插入数据后返回自增主键ID的问题
    1.场景介绍: ​开发过程中我们经常性的会用到许多的中间表,用于数据之间的对应和关联.这个时候我们关联最多的就是ID,我们在一张表中插入数据后级联增加到关联表中.我们熟知...
    99+
    2022-11-12
  • MybatisPlus使用Left Join...on...一对多多表联查和Ipage分页返回数据问题
    问题： 一对多关系表使用MybatisPlus的Ipage进行分页查询，会先执行联表查询sql语句,然后进行分页。  像图中联表查询一对多关系，会有多条重复数据，使用Ipage分页会将这10条数据返回到xml中resultMap绑定的ty...
    99+
    2023-09-13
    sql 数据库 mysql bug
  • 详解Python数据结构与算法中的顺序表
    目录0. 学习目标1. 线性表的顺序存储结构1.1 顺序表基本概念1.2 顺序表的优缺点1.3 动态顺序表2. 顺序表的实现2.1 顺序表的初始化2.2 获取顺序表长度2.3 读取指...
    99+
    2022-11-12
  • 探讨PHP返回数据乱码问题的原因和解决方法
    PHP作为一种流行的脚本语言，通过处理HTTP请求和响应的方式，使得Web应用程序开发变得简单高效。然而，PHP返回数据乱码问题在应用程序开发过程中经常出现，这给开发人员带来了很多麻烦。本篇文章将探讨PHP返回数据乱码问题的原因和解决方法。...
    99+
    2023-05-14
  • 详解C语言中return返回函数局部变量的问题
    目录return返回栈区局部变量的指针return返回栈区局部的临时变量return只读数据段和static数据在计算机中，释放空间并不需要将空间中的内容全部置成0或者1，而是只要设...
    99+
    2022-11-12
  • 怎么解决Innodb中undo tablespace没有清理数据的问题
    这篇文章主要讲解了“怎么解决Innodb中undo tablespace没有清理数据的问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么解决Innodb...
    99+
    2022-10-18
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作