锐客网

  1. 首页 > 睿知 > it技术 > >

mysql索引怎么存储 mysql索引存储在内存还是磁盘

IT技术索引

mysql索引二叉搜索树、N叉树
页分裂：B 树mysql索引怎么存储的插入可能会引起数据页mysql索引怎么存储的分裂mysql索引怎么存储，删除可能会引起数据页的合并mysql索引怎么存储，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一 。
页分裂逆过程：页合并，当删除数据后，相邻的两个数据页利用率很低的时候会做数据页合并
主键索引：key：主键，value：数据页，存储每行数据
非主键索引：key：非主键索引 ， value：主键key，导致回表
最左匹配：优先将区分度高的列放到前面，这样可以高效索引，
最左匹配原则遇到范围查询就停止匹配，范围查询(、、between、like)为什么？因为出现范围匹配后 ， 后面的索引字段无法保证有序，局部有序失去，顺序失去则无法提高查询效率
SELECT * FROMtableWHERE a IN (1,2,3) and b1;
如何建立索引？
还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!
索引组织表
索引用页存储：key【10】-point【6】，通过调整key大?。币炒笮」潭ǖ那榭鱿?nbsp;， 通过调整key大小 ， 使得N叉树变化；
如key 10, point 6则单个索引16字节，页大小为16k，则页面总共可以存储1024个索引，即N大小
覆盖索引: 二级索引的信息已经存在想要的列，例如主键
如果现在有一个高频请求，要根据市民的身份证号查询mysql索引怎么存储他的姓名，这个联合索引就有意义了 。它可以在这个高频请求上用到覆盖索引 ， 不再需要回表查整行记录 ， 减少语句的执行时间 。
索引下推优化：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数 。
整理索引碎片，重建表：alter table T engine=InnoDB
??首先是看key的大?。硗馐鞘菀车拇笮 。绻枰谋銷，则需要从这两个方面做改动；
一个innoDB引擎的表，数据量非常大，根据二级索引搜索会比主键搜索快，文章阐述的原因是主键索引和数据行在一起 ， 非常大搜索慢，我的疑惑是：通过普通索引找到主键ID后，同样要跑一边主键索引，对于使用覆盖索引的情况下，使用覆盖索引可以直接解决问题
Mysql InnoDB索引原理 理解Mysql索引mysql索引怎么存储的原理和数据结构有助于mysql索引怎么存储我们更好的使用索引以及进行SQL优化mysql索引怎么存储，索引是在存储引擎层面实现的，所以不同的引擎实现的索引也有一定的区别，但是在生产环境中，我们最常用的就是InnoDB引擎和B树索引 ， OK ， 那本文要讨论的重点也同样是InnoDB引擎下的B树索引。
我们建立一个表来进行测试，表的DDL如下所示，我们要关注的是表t_book上的主键索引id和name author publish_date三列组成的索引test_index 。
Mysql中的B树索引是使用B 树实现的，关于B 树的数据结构个人认为美团点评技术博客中Mysql索引原理及慢查询优化一文中介绍的非常详实，B 树的数据结构如下图所示 。
图中浅蓝色块即磁盘块，根节点磁盘块中存储17和35两个数据，其中指针P1指向小于17的数据，指针P2指向大于17小于35的数据，指针P3指向大于35的数据 。显然通过B 树索引查询数据与B 树的高度有关，如上图的B 树索引查找一个叶子节点的数据只需要三次磁盘IO，对于Mysql来说三层的B 树可以索引上百万的数据，这对于查询效率的提升是巨大的 。
总结起来Mysql中B树索引有以下关键特点mysql索引怎么存储：
Mysql中的B树索引有两种数据存储形式，一种为聚簇索引，一种为二级索引 。
InnoDB一般会使用表的主键来作为聚簇索引，如果一个表没有主键（不建议这么玩）InnoDB会选用一个唯一非空索引来代替，如果没有这样的索引，InnoDB会隐式建立一个聚簇索引 。聚簇的含义即是数据行和相邻的键值紧凑的存储在一起，占据一块连续的磁盘空间，因此通过聚簇索引访问数据可以有效减少随机IO，通常使用聚簇索引查找比非聚簇索引查找速度更快 。以我们建立的表t_book为例，聚簇索引即为自增主键id，其B树索引数据结构可以用下图来表示 。
聚簇索引有以下关键特点：
InnoDB的B树索引中除了聚簇索引，就都是二级索引了，二级索引的含义是索引的叶子节点除了存储了索引值，还存储了主键id，在使用二级索引进行查询时，查找到二级索引B树上的叶子节点后还需要去聚簇索引上去查询真实数据，但是这里有一种特殊情况 ， 即查询所需的所有字段在二级索引中都可以获取 ， 此时就不需要再去回表查数据了 ， 这种情况就是索引覆盖（EXPLAIN中EXTRA列中会出现USING INDEX，本文只关注索引结构 ， 不详细讨论索引覆盖等技术的使用，如果深入理解索引的数据结构，索引覆盖等技术也没有那么神秘） 。
在我们的测试表t_book中，test_index即为二级索引，由于我们把除了主键id所有的列都作为一个联合索引，所以在这个表上的查询都可以使用索引覆盖技术，但是具体生产环境中也不建议总是采用这种做法，索引列的增加也会增大插入更新数据时的索引更新成本，具体的优化要视具体情况决策 。t_book上的二级索引test_index的索引结构由下图表示 。
通过以上结构，我们可以推断出二级索引的以下关键特点：
索引覆盖：
最左前缀匹配：
二级索引可以说是我们在Mysql中最常用的索引，通过理解二级索引的索引结构可以更容易理解二级索引的特性和使用 。
最后聊点轻松的索引结构，哈希索引就是通过哈希表实现的索引，即通过被索引的列计算出哈希值 ， 并指向被索引的记录 。
哈希索引有如下特性：
Mysql索引原理及慢查询优化
高性能Mysql 第三版
Mysql分区表时候索引如何进行存储正常MySQL底层存储索引默认使用的是B 树,但是如果设立了分区表的情况下,他的底层是如何进行存储的呢?
查看MySQL对应的自己电脑上面的安装目录下,是有一个ibd文件,用innblock和bcview两个小工具，(github上可以搜到),便可以实现对ibd文件的查看,通过对比着没有设立分区的表的ibd文件,就可以看出来
分区表会将索引分成分区个个数的索引树来存储索引 ， 也就是分开存储 。
「Mysql索引原理（六）」聚簇索引本节课主要关注InnoDB，但是这里讨论mysql索引怎么存储的原理对于任何支持聚簇索引的存储引擎都是适用的 。
叶子节点包含mysql索引怎么存储了全部数据 ， 其他节点只包含索引列 。InnoDB将通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列 。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替 。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引 。
如果主键比较大的话，那辅助索引将会变的更大，因为 辅助索引的叶子存储的是主键值mysql索引怎么存储；过长的主键值 ， 会导致非叶子节点占用占用更多的物理空间
【mysql索引怎么存储 mysql索引存储在内存还是磁盘】 所以建议使用int的auto_increment作为主键
主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面 。当达到页的最大值时，下一条记录就会写入新的页中 。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满 。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即mysql索引怎么存储：只要索引是相邻的 ， 那么对应的数据一定也是相邻地存放在磁盘上的 。如果主键不是自增id ， 那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页 ， 当然也有其他一些措施来减少这些操作 ， 但却无法彻底避免 。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高 。
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读?。谑强疾煌５难暗啦煌５男?。聚簇索引则只需一次I/O 。（强烈的对比）
不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小 ， 这些操作是需要在内存中完成的 。
MyISM使用的是非聚簇索引， 非聚簇索引的两棵B 树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B 树的节点存储了主键 ， 辅助键索引B 树存储了辅助键 。表数据存储在独立的地方，这两颗B 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别 。由于 索引树是独立的，通过辅助键检索无需访问主键的索引树。
所以说，聚簇索引性能最好而且具有唯一性 ， 所以非常珍贵 ， 必须慎重设置 。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引 ，这个要看实际情况 。
聚簇索引和非聚簇索引的数据分布有区别 ， 主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和以外，下面通过一个列子来讲解InnoDB和MyISAM是如何存储数据的：
该表的主键取值1~10000，按照随机顺序插入并使用optimize table命令做了优化 。换句话说，数据在磁盘上的存储方式已是最优，但行的顺序是随机的 。列col2的值是从1~100之间随机赋值 ， 所以有很多重复的值 。
MyISAM的数据分布很简单，所以先介绍它 。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：
在行的旁边显示行号，从0开始递增 。因为行是定长的 ， 所以MyISAM可以从表的开头跳过所需的字节找到需要的行 。
col2上的索引
事实上，MyISAM中主键索引和其他索引在结构上没有什么不同 。主键索引就是一个名为PRIMARY的唯一非空索引 。
InnoDB支持聚簇索引，所以使用不同的方式存储同样的数据 。
第一眼看上去，感觉和前面的没什么区别 ， 但是该图显示了整个表，而不是只有索引 。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储，这也是为什么MyISAM索引和数据结构是分开的 。
聚簇索引的每一个叶子节点都包含了主键值 。事务ID、用于事务和MVCC的回滚指针以及所有的剩余列 。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列 。
还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同 。InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值 ， 并以此作为指向行的“指针” 。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作 。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针” 。
我们在来看一下 col2索引。
每一个叶子节点包含了索引列（这里是col2），紧接着是主键值（col1），上图我们省略了非叶子节点这样的细节 。InnoDB非叶子节点包含了索引列和一个指向下一级节点的指针 。
最后 ， 以一张图表示InnoDB和MyISAM保存数据和索引的区别 。
前面讲过，最好使用AUTO_INCREMENT自增列来聚集数据，避免随机的、不连续的、值分布范围大的列做聚簇索引，特别是对于I/O密集型的应用 。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：他使得聚簇索引的插入变得完全随机，这是最坏的情况 ， 使得数据没有任何聚集特性 。
为了演示这一点，我们做两个基准测试：
1、使用证书ID插入userinfo表，和uuid作为主键的userinfo_uuid表
userinfo_uuid表跟userinfo表除了主键给为UUID ， 其他字段都一样
测试这两个表的设计，首先在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录 。然后向两个表继续插入300万数据，使索引的大小超过服务器的内存容量 。测试结果如下：
向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大 。这一方面是由于主键字段更长 ， 另一方面毫无疑问是由于页分裂和碎片导致的 。
为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化 。
自整型主键插入
因为主键是顺序的，所以InnoDB把每一条记录都存在上一条记录的后面 。当达到页的最大容量后 ， 下一条记录就会写入到新的页中 。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果 。
UUID插入
因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间 。这会正价很多的额外工作，并导致数据分布不够优化 。
缺点：
把这些随机值载入到聚簇索引后 ， 也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充 。
结论 ：使用InnoDB时应尽可能地按主键顺序插入数据，并且尽可能地单调增加聚簇键的值来插入新行 。
Mysql空间索引在涉及LBSmysql索引怎么存储的服务开发过程中mysql索引怎么存储，经常需要存储地理空间的位置并进行一定计算（附近商家等需求），本文主要介绍mysql对于LBS的支持 。
Mysql的空间扩展主要提供一下几个方面的功能：
其中前两点对InnoDB ， MyISAM，NDB ， ARCHIVE等mysql存储引擎都支持，第三点只有对InnoDB和MyISAM的支持，由于InnoDB的支持行锁以及事务的特性 ， 现在基本上已经是默认存储引擎了，所以本文以下内容都默认使用InnoDB 。
创建空间列以及空间索引的语句如下：
Mysql的空间数据类型与OpenGIS的数据类型相对应 。
Mysql的空间数据有不同表示格式，其中咱能看懂的也就第一种
因为上文提到了SRID ， 这里说下什么是SRID，SR是指Spatial Reference，也就是mysql索引怎么存储我们常说的空间参考系，mysql支持卡迪尔坐标系和地理坐标系，其中地理坐标系又有好多种，下面说几种常用的空间参考系
Mysql的所有空间坐标系都存在表 mysql.st_spatial_reference_system 中，这个表是隐藏的，看不见的，但是你可以通过 infomation_shcema.st_spatial_reference_system 中查看参考系的信息，这个表就是 mysql.st_spatial_reference_system 的一个视图的实现 。
mysql的空间索引的数据结构是R树，R树实际上就是多维的B树，B树的数据结构在我的另一篇博客中有介绍，这里就不展开了，说几点在应用的时候需要注意的 。
最后转一篇博文
mysql索引怎么存储的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql索引存储在内存还是磁盘、mysql索引怎么存储的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：有没有人用过美团的服务器？分享一下使用体验吧！ 美团服务器怎么样的啊知乎

    下一篇：国内代理ip软件价格，国内代理ip软件价格多少