`
javaroad
  • 浏览: 21454 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql索引与存储结构(一)

阅读更多

    首先从一个问题说起。

    问题现象:
    查询语句如下:

-- 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
分享到:
评论

相关推荐

    mysql存储与索引技术

    对于 InnoDB 引擎,当表空间文件(ibdata1)过大且无法收缩时,可以通过修改配置文件(如 /etc/my.cnf)中的 `innodb_file_per_table` 参数,使每个表的数据和索引存储在独立的文件中,从而更方便地管理空间。...

    mysql 索引与执行计划

    InnoDB引擎中,聚簇索引将数据和索引存储在同一结构中,因此查询速度非常快。 5. **非聚簇索引**:与聚簇索引相对应,它们将索引和数据分开存储。 ##### 1.1.3 基础语法 MySQL提供了多种方法来创建、查看和删除...

    MySQL Innodb 索引原理详解

    在深入探讨MySQL Innodb索引之前,我们先了解几种基本的树形数据结构,包括二叉搜索树、B树、B+树以及B*树。 ##### 1.1 搜索二叉树(Binary Search Tree) 搜索二叉树是一种特殊的二叉树,每个节点至多有两个子...

    MySQL 索引最佳实践

    ### MySQL索引最佳实践 #### 理解索引的重要性 在数据库管理中,索引是一种数据结构,用于提高查询速度。它对于开发者和数据库管理员(DBA)来说至关重要。索引选择不当可能会导致生产环境中的诸多问题。尽管索引...

    MySQL索引的数据结构与算法.pptx

    ### MySQL索引的数据结构与算法 #### 一、MySQL存储引擎概述 MySQL提供了多种存储引擎以适应不同的应用场景。其中,最常用的包括InnoDB、MyISAM、MEMORY等。每种存储引擎都有其特点和适用场景。 - **InnoDB**:...

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

    正如标题所提到的,“MySQL索引背后的数据结构及算法原理”这一主题是技术面试中的重要内容之一。本文旨在深入探讨MySQL索引的相关概念及其背后的原理。 #### 索引的本质 索引本质上是一种数据结构,其目的是帮助...

    Mysql-索引原理分析

    非聚集索引(Secondary Index,或Non-Clustered Index)与聚集索引不同,它的索引结构独立于数据行。非聚集索引中,每个索引条目包含键值和一个书签,书签指向对应数据行的实际位置。这意味着,对于非聚集索引查询,...

    Mysql索引数据结构.pptx

    索引是一种特殊的数据结构,它按照一定的排序规则存储了数据表中的部分或全部数据,使得数据库可以直接定位到所需的数据,从而避免全表扫描,显著提升查询速度。 常见的数据结构有数组、栈、队列、链表、树等。在...

    MySQL索引最佳实践

    #### 一、理解MySQL索引的重要性 索引是数据库性能优化中最常用也是最重要的手段之一。合理的索引设计可以显著提高查询效率,减少服务器资源的消耗。在MySQL中,索引的选择与配置对于开发人员和数据库管理员来说至...

    mysql索引、触发器、事务、存储过程说明

    在MySQL数据库中,索引是一种用于提高数据检索速度的数据结构。它类似于书籍中的目录,可以帮助数据库快速定位到特定的数据记录。索引可以极大地改善查询性能,尤其是在处理大型数据集时。 #### 创建索引 - **唯一...

    MySQL索引 使用笔记

    【MySQL索引 使用笔记】 MySQL数据库是世界上最流行的开源关系型数据库管理系统之一,其高效的数据查询能力在很大程度上依赖于索引。本笔记将深入探讨MySQL中的索引使用,旨在帮助你提升数据库性能。 1. 索引的...

    mysql的索引优化

    - **索引结构**:MySQL索引通常是以B树形式存储的。B树具有良好的查询性能,即使在大型数据集中也能保持高效的查找能力。 - **索引的组成**:对于每个索引项,MySQL都会保存一个指向实际数据文件中记录位置的“指针...

    MySQL索引分析和优化.pdf

    **MySQL索引**本质上是用来加快数据检索过程的一种数据结构,类似于书籍中的目录。当执行查询时,如果涉及到的列已经建立了索引,MySQL可以通过索引来快速定位到所需数据的位置,从而大大减少查找时间。 - **无索引...

    mysql索引数据结构详解

    MySQL 索引是一种特殊的数据结构,它可以帮助快速定位和检索数据。索引的主要目的便是降低树的高度,从而提高查询效率。下面我们将详细介绍 MySQL 索引的数据结构和工作原理。 索引的存储 索引文件存储在磁盘上,...

    mysql索引和锁机制ppt介绍

    ### MySQL索引和锁机制详解 #### 一、索引基础 **索引定义:** 索引是MySQL中用于提高查询效率的一种数据结构。通过索引可以在数据表中快速定位到所需的数据行,大大减少不必要的全表扫描。 **索引的重要性:** 1....

    MySQL索引背后的数据结构及算法原理-07071521.pdf

    本文档主要讨论B-Tree索引,具体包括B-Tree和B+Tree的数据结构特点、MySQL索引的实现、索引使用策略及优化等。 MyISAM和InnoDB是MySQL中的两种主要存储引擎,它们对索引的实现各不相同。MyISAM存储引擎使用的是非...

Global site tag (gtag.js) - Google Analytics