首先从一个问题说起。
问题现象:
查询语句如下:
-- sql1
SELECT
w.wid, w.rid
FROM warestock w JOIN product p ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
warestock 在 wid ,rid上创建了联合主键,product_code是product的主键。我期望的输出结果能按照w.wid, w.rid的顺序排序。结果却没有按照这个w.wid, w.rid升序输出结果。
按照以往的知识,sql执行时应该是直接索引扫描warestock,然后再关联product时用到主键关联。然后再用rid过滤数据。 结果却并没有按照w.wid, w.rid的顺序排序。
分析原因:
1.怀疑是查询缓存问题:加上SQL_NO_CACHE,输出结果和sql1完全一致,说明与查询缓存无关。
2.warestock 和product执行顺序的原因。把sql1换成如下子查询,确实也能实现按照顺序wid,rid输出结果。
SELECT
w.wid, w.rid
FROM warestock w where w.rid IN (3, 4, 5, 6, 10)
and exists(select 1 from product p where p.product_code = w.wid)
或者忽略warestock表的主键索引也能达到同样的效果,sql如下,但是根本原因还是没有找到。
SELECT SQL_NO_CACHE
w.wid, w.rid
FROM warestock w IGNORE INDEX (PRIMARY) JOIN product p ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
3.再次分析执行计划,发现warestock并没有按照用到主键索引。而是index_pc,再查看索引: show index from product 不知道什么时候在product上的run_type字段建了索引。到此为止,查明原因:product并没有使用主键索引导致没有按照顺序输出结果。
解决方案如下:
1.删除掉离散度较低的索引 或者
2.强制使用product的主键索引
SELECT SQL_NO_CACHE
w.wid, w.rid
FROM warestock w JOIN product p FORCE INDEX (PRIMARY) ON w.wid = p.product_code
WHERE w.rid IN (3, 4, 5, 6, 10)
结论、扩展:
1.MyIsam和innodb数据和索引存储结构方式是不一样的。
Innodb主键索引是主键和数据列放在一起,每个普通索引都带着主键列,并且索引顺序是按照普通索引列和主键列排序;MyIsam主键索引是和数据列放开存放的,普通索引和主键索引没有任何区别,普通索引也不会保存主键索引的信息。从以下sql的执行计划中也可以看出这一点:
-- sql2
CREATE TABLE test1 (
`wid` bigint(20) NOT NULL COMMENT 'SKU代码(商品编号)',
`orgid` int(11) NOT NULL DEFAULT '0',
`topwpid` int(11) DEFAULT NULL,
PRIMARY KEY (`wid` ),
KEY k_orgid (`orgid`)
) ENGINE=Innodb DEFAULT CHARSET=utf8;
insert into test1 values(12345,12345,111),
(12346,12346,111) ;
-- sql3:
select wid from test1
索引和主键存放在一起,用到了覆盖索引。执行计划如下:
修改test1 的存储引擎为MyISAM,执行计划如下:
上面的执行计划用到的索引不一样,原因是存储引擎是InnoDB时sql3用到了覆盖索引,而存储引擎是MyISAM时,只需主键索引扫描即可,因为主键索引和数据列是分开存放的。 这也从侧面验证了如上面所说的Innodb和MyIsam普通索引的存储方式。
` 如此说来sql3执行时,存储引擎是Innodb时,如果暗示优化器忽略IGNORE INDEX普通索引k_orgid,则会按照PRIMARY扫描。
如果忽略PRIMARY,则会按照普通索引k_orgid扫描
存储引擎是MyIsam时,如果IGNORE INDEX普通索引k_orgid,则依然按照PRIMARY扫描。如果忽略PRIMARY,则会执行全表扫描。
2.根据统计信息分析后,优化器会合理选择小表驱动大表的执行计划。
sql1 的执行计划可以看出这一点
- 大小: 21.3 KB
- 大小: 10.9 KB
- 大小: 9.9 KB
分享到:
相关推荐
1 .索引的存储结构是什么? 是B树、B+树还是二叉树 2.什么是聚集索引? 聚集索引的主键索引和次要索引区别是什么 3.什么是非聚集索引? 非聚集索引的主键索引和次要索引区别是什么
索引是一个单独的、物理的数据库结构,是某个表中一列或者若干列的集合以及相应的标识这些值所在的数据页的逻辑指针清单。 索引是依赖于表建立的,提供了数据库中编排表中数据的内部方法。表的存储由两部分组成,一...
从数据结构底层实现,阐述B树、B+树的特点,到mysql为什么选择了B+树作为索引存储结构。接着介绍mysql底层存储实现段簇页,和聚簇索引非聚簇索引包括联合索引的关系。最后列举一些sql是否可走索引,涉及最左匹配原则...
特别需要说明的是, MySQL支持诸多存储引擎,而各种存储引擎对 索引的支持也各不相同,因此MySQL数据库支 持多种索引类型,如BTree索引,哈希索引, 全文索引等等。为了避免混乱,本文将只关注 于BTree索引,因为这...
数据库的索引可以加快查询速度,原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。 索引优化是数据库优化的最重要手段。 如果查询语句使用索引...
什么是索引,index 索引:是帮助高效获取数据的数据结构 索引也可能是一个文件 那么多存储结构 hash map 红黑树 二叉树等 为什么mysql底层要用B+ tree实现?
1.2 MySQL与其他数据库的简单比较 1.3 MySQL 的主要适用场景 1.4 小结 第2章 MySQL架构组成 2.0 引言 2.1 MySQL物理文件组成 2.2 MySQL Server系统架构 2.3 MySQL 自带...
在关系数据库中,索引是一种单独对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的作用相当于图书的目录,可以...
在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。 而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的...
1. MySQL体系结构和存储引擎 2. InnoDB存储引擎 2.1 InnoDB体系结构 2.2 Checkpoint技术 2.3 Master Thread工作方式 2.4 InnoDB关键特性 3. 文件 3.1 参数文件 3.2 日志文件 3.3 套接字文件 3.4 pid文件 ...
Mysql-索引结构直观图解。上一篇刚刚通俗化的说明了B-TREE的几个结果与存储方式,其实跟索引感觉上还是没有关联起来, 那么本篇,就通过实际的一个数据行的例子,说明一下
对MySQL两种常用存储引擎的索引数据结构做了细致描述,针对性的优化做了介绍。
接着以InnoDB的内部实现为切入点,逐一详细讲解了InnoDB存储引擎内部的各个功能模块,包括InnoDB存储引擎的体系结构、内存中的数据结构、基于InnoDB存储引擎的表和页的物理存储、索引与算法、文件、锁、事务、备份,...
索引是数据库存储引擎用于快速查找到指定数据的一种数据结构。 可以用新华字典做类比:如果新华字典中对每个字的详细解释是数据库中表的记录,那么按部首或拼音等排序的目录就是索引,使用它可以让我们快速查找的某...
│ day2_MySQL索引深入剖析-笔记.pdf │ day2_MySQL索引深入剖析-课件.mp4 │ day2_MySQL索引深入剖析-课件.pdf │ day3_MySQL事务与锁详解-笔记.pdf │ day3_MySQL事务与锁详解-课件.pdf │ day3_MySQL事务与锁详解...
第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。 第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。 第三部
06.MySQL高级索引数据结构.avi 07.MySQL高级索引数据结构BTREE.avi 08.MySQL高级索引数据结构B+TREE.avi 09.MySQL高级索引索引分类.avi 10.MySQL高级索引索引语法.avi 11.MySQL高级索引索引设计原则.avi 12.MySQL...
│ │ 4_MySQL索引底层结构和执行计划.mp4 │ │ 5_MySQL索引优化原则.mp4 │ │ 6_MySQL运维常见错误part1.mp4 │ │ 7_MySQL运维常见错误part2.mp4 │ │ ERROR1040_1917970.1.pdf │ │ ERROR1062_1593526.1.pdf ...
4.2.1 B树数据结构4.2.2 B+树数据结构,以及为什么选择B+树4.2.3 一个错误的观点:B树和B+树的区别之一为B树的非叶子节点存储数据4.3 简单猜想:为什么索引中每个节点在内存中的地址是随机的5 MySQL索引的体现形式...