投稿
  1. 首页
  2. 随笔

MySQL索引

人工少女3修改器 • 2022-12-15 • 随笔 • 阅读 40

MySQL索引,第1张

MySQL索引

目录

前言

一.认识磁盘

二.MySQL与磁盘的交互基本单位

三.索引的理解

        3.1 引出索引

         3.2 MySQL管理Page

        3.2.1 单个Page的情况

        3.2.2 多page的情况

        3.3 什么是索引

四.聚簇索引和非聚簇索引

        4.1 非聚簇索引

         4.2 聚簇索引

五.索引 *** 作

        5.1 创建主索引

        5.2 唯一键索引创建

        5.3 普通索引

        5.4 全文索引

          5.5 查询索引

         5.6 删除索引

         5.7 索引创建原则

六. 其它概念

前言

        MySQL是对用户的数据进行管理的，而数据都保存在磁盘中，MySQL对数据进行管理需要先将数据从磁盘读取到内存中，再将修改了的数据，刷新到磁盘中。然而，这是IO *** 作，效率是比较低的。

        并且当数据量比较大时，当查询数据时，由于内存无法全部保存数据，只能将数据分批次读取上来，这样会导致IO次数增加。

        索引是MySQL中提高查询数据的一种方式。但是查询数据的提高是以插入，更新，删除的速度为代价的。

        所以索引的价值在于提高海量数据的查询速度。

        下面来介绍索引是如何提高查询效率的，首先我们来了解一下磁盘，并且为什么磁盘 *** 作会这么慢。

一.认识磁盘

磁盘的一个盘片：

  • 磁道：磁盘表面有许多同心圆，每一个同心圆称为一个磁道。
  • 扇区：每一个磁道又被划分成了若干段，每一段就是一个扇区。数据库文件保存在扇区中，一个扇区的大小一般是512字节。当一个数据库文件很大时，可能会占据多个扇区。

        从上图看，离圆心越近扇区越小，离圆心越远，扇区越大。最新的磁盘技术已经让扇区大小不同。

        所以找到一个数据库文件，需要定位到保存文件的扇区。

  • 多盘磁盘，每个盘都有两个面，大小完全相同。
  •  柱面：多个磁盘中，半径相同的同心圆构成一个柱面。
  • 每一个盘面都有一个磁头，磁头和盘面的关系时一一对应的。

磁盘定位文件的扇区：

        先定位盘面，也就确定了磁头。在确定柱面(磁头旋转到柱面处)，再确定好扇区(盘片旋转，使得磁头指向对应扇区)。

        这种磁盘数据定位方式叫做CHS。但是实际系统软件使用的并不是CHS(硬件是)，使用的是LBA，一种线性地址，可以想象成虚拟地址和物理地址(可以抽象成一个大数组)，系统将LBA的地址最后转化成CHS，交给磁盘进行数据读取。

        这样是为了不需要让OS管理磁盘，是得OS和磁盘解耦。避免磁盘技术改动，需要改动OS代码。

        磁盘传送数据并不耗时，最耗时的是磁头寻址。定位盘片，磁道和扇区。

        在硬件层面上，我们已经可以定位一个扇区，那么在系统软件层面上，我们也是按一个扇区的大小来读进行IO交互吗？

        答案是并不是。

  • 如过 *** 作系统使用硬件提供的数据大小来和磁盘进行交互，那么OS代码会和硬件强相关，当磁盘扇区大小改变，OS代码就会需要跟着变化。
  • 并且，单次交互512字节，还是太小了。当文件很大时，意味着进行IO的次数会很多，大大降低效率。
  • *** 作系统的文件系统读取的基本单位不是扇区，而是数据块。基本单位是4KB。

磁盘随机访问和连续访问：

        随机访问：是本次IO给出的扇区的地址和上一次IO给出的扇区地址不连续，需要重新定位盘片，磁道和扇区。

        连续访问：是本次IO给出的扇区的地址和上一次IO给出的扇区地址连续，不需要过多的定位。

        因此，连续访问的效率比随机访问的效率高。

二.MySQL与磁盘的交互基本单位

        MySQL作为一款应用软件，可以想象成一个特殊的文件系统。它有着更高的IO场景，为了提高IO效率，MySQL与磁盘交互的基本单位是16KB。

用指令查看：

         磁盘硬件设备的基本单位是512字节， *** 作系用户与磁盘交互的基本单位是4KB，称为一个page。MySQL和磁盘进行交互的基本单位是16KB，在MySQL中也被称为page，下面讨论的是MySQL的page。

MySQL与磁盘交互的基本单位是16字节是什么意思？

        MySQL每次和磁盘交互(IO)，一次性从磁盘拿16KB，不论数据大小是大于还是小于16KB，小于向上取整到16KB，大于分多次从磁盘获取，每次16KB。即，MySQL和磁盘交互的大小一定是16KB的整数倍。

MySQL为什么和磁盘交互交互是16KB，用多少加载多少不好吗？

        一个表中可能会有很多数据。如果每次查询一次数据，查询多次，就会需要进行多次IO，导致效率会很慢。

        而一次性加载16KB，16KB里会含有多条数据。用户连续申请的资源大概率在其中，大大减少了IO。提高了效率。

        但是，我们也不能保证一定会在其中。但是又很大的概率，因为有局部性原理。往往IO效率低下的主要矛盾不是每次IO数据量的大小，而是IO的次数。

        比如：一个表中有5条数据，用户查询数据，MySQL一次性获取16KB，可能就将数据全部拿上来了。下一次查询就不需要进行IO了。

注意：MySQL与磁盘交互(IO)每次是一个Page，也就是16KB。不是多个Page。

总结：

  • MySQL的数据文件是以Page为单位保存在磁盘中的。
  • MySQL的增删查改 *** 作，都是需要通过计算的，找到对应插入位置，或者找到对应修改或者查询的数据。
  • 只要涉及到计算就需要CPU的参与。根据冯诺依曼结构，数据需要加载到内存中。
  • 所以在特定的时间内，数据一定是磁盘中有，内存中也有。后序 *** 作完内存的数据之后，以特定的刷新策略，刷新到磁盘中。
  • 从磁盘读取数据和将数据刷新到磁盘中，需要涉及和磁盘的交互(IO)，此时IO的基本单位就是Page(16KB)。
  • 为了更好的进行上面的 *** 作，MySQL在运行的时候，在服务器(MySQLd)内部会向 *** 作系统申请被称为buffer pool的大内存空间，进行各种缓存。
  • buffer pool其实就是一个大块内存空间，一般125M或者256M。MySQL对该空间进行管理。

为什么MySQL要申请buffer  pool大块空间？

        MySQLd中肯定会存在大量的page，并且会需要频繁的进行申请内存和释放内存的 *** 作，为了不用频繁的向OS申请内存和释放内存，OS管理的东西太多了。申请一块大内存自己管理是最好的。 

画一个图来理解 一下：

        MySQL增删查改表中的数据不是在磁盘里 *** 作的，而是将数据加载到内存，准确点，是MySQL的buffer pool中。在buffer pool里 *** 作。 *** 作完之后，在刷新到磁盘中。

        MySQL会用一种方式记录Page是否被修改，被修改的Page被称为脏页。这种方式可以理解成位图的方式。当修改了Page中的数据，MySQL只需要将修改的Page刷新到磁盘中，并不是将所有Page都刷新到磁盘中。

        比如：buffer  pool中有5个page，MySQL以0表示未修改，1表示修改。此时记录为00000，当用户修改了第2个page，此时记录变为00100，此时，第三个page 被称为脏页，MySQL只需要将第三个page刷新到磁盘中。

三.索引的理解         3.1 引出索引

        建立测试表：

         我们发现，我们主键插入的顺序并不是有序的，但是在表中，确变成了有序的，这是谁做的呢？这样有什么好处呢？

        实际上这是因为MySQL的索引结构，要求的。好处下面介绍。

        说明：如果这里没有主键，插入什么顺序，表中就是什么顺序。这说明，索引需要和特定的设定有关。

         3.2 MySQL管理Page         3.2.1 单个Page的情况

        MySQL中的数据文件可能有一个或者多个Page构成，而MySQL是管理数据的。所以MySQL中会有很多Page。MySQL需要对page进行管理，就需要先描述后组织。

         不同的Page在MySQL中，都是16KB，同一文件的page使用双向链表组织起来。而数据都保存在page中，以链表的形式组织在page中。

        如果有主键，MySQL会按照主键给我们的数据进行排序。

为什么进行排序呢？

        插入数据时，排序的目的是为了优化查询效率。并且为B+数的结构做准备。

举个例子：

        如果不是有序，如果一个数据不在表中，查询时，需要将每一个数据都查询。

        如果是有序的，查询到大于当前值，还没找到，说明不存在。后面就不需要继续找了。

        但是，当数据量很大时，说明，一个page中保存的数据量很多。而数据在page中是以链表的形式组织的。查找数据时，一定也是线性查找。比如：数据保存在很后面，效率也很低。

        于是：MySQL在page中增加了一个类似于目录的结构，来将数据链表分成多段。目录结构中保存的是该段链表中的最小Key值和该结点的地址。如下：

         于是在单个page中查找某个值时，先查询目录，找到对应链表段，再遍历链表段，找到数据。这样会大大提高效率。

        比如：上面数据，查找id=5的数据。先在目录中查找，找到目录key值等于4的后，找到链表地址，遍历该段链表，即可找到5.寻找次数是4次。而如果没有目录，直接遍历链表，查找的次数是5次。但是由于上面的例子数据量不是很大，当数据量很大，目录跨度很大时，效率会有明显的提高。

        3.2.2 多page的情况

        MySQL中，Page的大小是固定的16KB。当一个文件数据很大时，需要由多个Page来构成。同一文件的多个Page使用双向链表组织起来。

         在单表不断被插入的情况，MySQL会在一个Page容量不足的情况下，自动开辟新的Page来保存新的数据，然后通过指针方式，将所有page组织起来。

        这样我们查找某个数据时，需要遍历整个双向链表，并且每一个page还需要遍历。

        虽然遍历一个page的效率有所提高，但是，遍历整个链表，并且需要将每一个遍历的page从磁盘加载到内存，这也就意味着依旧需要进行大量的IO。这样会效率仍然会收到影响。

        于是按照上面的思想，MySQL对每一个Page也添加了一个目录。

  • 使用一个page里面保存一个目录，来指向双向链表的某一个page，并且里面保存指向page中最小的键值。
  • 该page 中不保存数据信息，只保存目录。目录保存的是管理的page的地址和管理page中的最小键值。
  • 和双向链表page里的目录不同的是，这个目录管理的是管理的级别是整个page。而双向链表中page的目录管理的级别是链表的结点。

于是有了下面的结构：

        存在一个目录页来管理保存数据的page。先在目录页中比较，找到需要访问的保存数据的page，通过目录保存的指针找到保存数据的page。在在page中寻找数据。

        但是当文件数据的page过多时，也就是双向链表过长时。管理双向链表的目录页也会过长。于是，可以通过增加了目录页来管理下一层的目录页，如此循环。

       从上面可以看出，索引保存数据的结构采用的是B+树。由于上面的例子数据量太少，看不出有什么明显的差别。当数据量很大，并且，当一个page中保存的目录很多时，效率会明显提高。

        这样随便找一个id，需要查询的page数一定会减少，也就意味着IO的次数减少了。

简单介绍一下B+树：B+树是一个多叉平衡搜索树。B树概念和插入实现

  • B+树一个结点可以保存多个数据，并且是有序的。
  • 非叶子节点，数据保存的是一个个子节点中数据最小的键值和子节点的地址。
  • 叶子节点保存数据，是真实的所有用户数据。
  • 叶子结点以链表的形式连接起来。

        由于一个结点保存的数据很多，并且是多叉的，可以使得B+树的高度很低。

 总结：

  • 索引整个结构中page分为目录页和数据页，目录页中只保存管理page最小的键值和地址。不保存数据。数据页保存数据。
  • 查找时，自顶向下查找，只需要将部分的目录page加载到内存找到保存数据的page，即可完成查找。大大减少了IO。

为什么目录page不保存数据？

        page大小固定，不保存数据可以更多的保存目录信息，那说明一个目录page可以管理更多的page。使得树的高度变低。进行IO的次数变少。

怎么减少的IO？

        数据在磁盘中以B+树的形式保存，每次只需要加载一个结点到内存，即可找到下一层的的page，直到最后找到叶子结点。

        这样IO的次数最多是树的高度次，由于B+树的高度低，所以IO的次数会变少。

使用其它数据结构来管理数据行不行？

        行肯定是行，但是，会大大的增加IO的次数，导致效率降低。

        如果用链表，线性遍历，IO次数多。

        用二叉搜索树，二叉搜索树由退化问题，插入有序，会退化成线性结构。

        用红黑树或者AVL树，虽然查找的效率很高。但是由于是二叉树。树的高度也会很高。导致IO的次数页会增加。

        用哈希表，虽然查找的效率为O(1)，但是当哈希从都过多，一个桶中的链会比较长，效率也会降低。并且哈希桶不方便模糊匹配和范围查找。

        但是，官方索引实现方式中，MySQL是支持哈希的，不过innodb和MyISAM不支持。

        B树，非叶子结点也保存了数据，高度会有所增高。也只结点没有相连，不方便进行范围查找。

        B+树非叶子结点不保存数据，可以使得管理的页变多，树的高度降低，IO次数变少。叶子结点相连，方便范围查找。    

说明：

        MySQL每次和磁盘交互大小是一个Page，当查找到数据，将数据拿到buffer pool中后，也会将其组织成B+树的形式。可以理解是，为了方便查找。

步骤:

        用户查询数据，将磁盘B+树的结点拿到内存中，查找下一个结点，直到找到数据(叶子系结点)。

        数据拿到内存(buffer pool)后，可能会有多个page。MySQL也会将其再buffer pool中组织成B+树。

注意：用户查询数据也不一定是将数据全部加载到内存中，是需要多少加载多少。

        3.3 什么是索引

        MySQL是管理用户数据的软件，数据保存在磁盘中。MySQL增删查改数据需要将数据拿到内存中，需要进行IO。而MySQL和磁盘每次交互的大小是一个page(16KB)。当数据量很大时，会导致IO次数变得很多，效率会降低。

        索引是MySQL为了提高查询数据效率的一种方式。

        实现是以page大小为结点构建一颗B+树，叶子结点保存数据，非叶子结点保存孩子结点的地址和最小键值，也只结点组织成了链表形式。

        B+树的整体优势是，高度低，IO次数少。叶子结点组织成链表形式，方便范围查找。

四.聚簇索引和非聚簇索引         4.1 非聚簇索引

        非聚簇索引：数据文件和索引文件分离。典型代表是MyISAM存储引擎。

        MyISAM：同样是用B+树作为索引结构。叶子结点保存的是数据的地址或者路径。下图为MyISAM的主索引，col1为主键：

        用主键建立的索引为主索引。

        通过查询B+树，最终得到数据的地址或者路径。再通过地址或者路径找到数据。

使用MyISAM存储引擎建立一张表：

         当然，可能用户在建立表是没有设定主键，并且，使用其它信息列建立索引。这种缩影可以叫做辅助索引。

        MyISAM的辅助索引和主索引建立没有差别，主要差别是主索引保存的键值不能重复。辅助索引保存的键值可以重复。

        下图是以Col2建立的辅助索引。

         4.2 聚簇索引

        聚簇索引：数据文件和索引文件是一个文件。典型是innodb存储引擎。

        Innodb，使用的是B+树作为索引结构，叶子结点保存的就是数据。

        下图是以主键建立的索引结构，称为主索引。

         通过查询B+树，找到叶子结点就找到了数据。

用innodb存储引擎创建一张表：

         innodb同样可以不使用主键建立表，也叫做辅助索引。但是，innodb的辅助索引叶子结点保存的是对应主键值。

        查找数据向通过辅助索引找到主键值，再通过主键值，在主索引中找到数据。

为什么辅助索引不给叶子结点也赋上数据呢？

        原因是太浪费空间了。并且修改数据，需要修改全部索引数据。 

      说明：Innodb要求表中必须有主键，因为Innodb索引文件(数据文件)，最后必须按照主键来找到是数据。如果用户没有显示设置主键，MySQL会自动选择一个可以唯一标识数据记录的列作为主键，比如：自增字段。如果不存在这种列，MySQL会为Innodb表自动生成一个隐含字段作为主键。这个字段占6字节，类型为长整型。

五.索引 *** 作

        索引分为：

  • 主键索引(primary key)
  • 唯一索引(unique)
  • 普通索引(index)
  • 全文索引 (fulltext)
        5.1 创建主索引

        主要是设置主键。

  • 第一种方式

        在创建表时，在字段后面，直接加上primary key。

create table test2( id int primary key, name varchar(20));
  • 第二种方式

        在创建表时，指定某列或者某几列为主键。

create table test3( id int, name varchar(20), primary key(id));
  • 第三种方式

        创建表后增加主键。

create table test4( id int, name varchar(20));

alter table test4 add primary key(id);

主键索引特点：

  • 一个表中，最多有一个主键索引，当然可以使用复合主键。一个表中主键只能有一个。
  • 主键索引效率高。
  • 创建主键索引列不能为空，不能重复。
  • 主键索引基本上是int类型。
        5.2 唯一键索引创建

        主要是创建唯一键。

  • 第一种方式

     在创建表时，给字段加上unique属性。

create table test5( id int primary key, name varchar(20) unique);
  • 第二种方式

        在创建表时，指定某列或者某几列为唯一键。

create table test6( id int primary key, name varchar(20), unique (name));
  • 第三种方式

        创建表后增加唯一键。

create table test7( id int primary key, name varchar(20));

alter table test7 add unique(name);

唯一索引特点：

  • 一个表中可以由多个唯一索引，因为唯一键可以由多个。
  • 查询效率高
  • 如果在某一列建立唯一索引，必须保证这一列数据不能重复。
        5.3 普通索引
  • 第一种方式

     在创建表时，指定某列为普通索引

create table test8( id int primary key, name varchar(20), index(name));
  • 第二种方式

        在创建表后，指定某一列为普通索引

create table test9( id int primary key, name varchar(20));
alter table test9 add index(name);
  • 第三种方式

        创建表后，创建一个索引名为index_name的索引

 create table test10( id int primary key, name varchar(20));
create index index_name on test10(name);

普通索引特点：

  • 一个表中可以有多个普通索引
  • 普通索引，该列可以有重复值。
        5.4 全文索引

        对文章字段或者有大量文字的字段进行检索。

        使用全文索引必须使用MyISAM存储引擎，默认支持英文，不支持中文。如果对中文进行全文索引，可以使用sphinx的中文版(coreseek)。

创建全文缩影：

        在创建表时，指定某列为全文索引。

CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
                        title VARCHAr(200), 
                        body TEXT, 
                        FULLTEXT (title,body) 
                        )engine=MyISAM;

插入一些值：

  •  查询有没有包含database的语句

        并没有使用全文索引，而是将数据全部遍历查找。

 可以使用explain工具查看，是否使用索引。

  •  使用全文索引

         注意：上面的索引结构，在创建时，MySQL会自动创建对应索引结构。比如：创建表时，设置主键后，MySQL会自动创建主索引。唯一键创建后，MySQL会自动创建唯一索引。普通索引在创建后，也会自动创建等。 

          5.5 查询索引

  • show  keys  from  表名

  •  show  index  from  表名

  •  desc  表名；

         5.6 删除索引
  • 删除主键索引

alter  table  表名  drop   primary  key；删除主键。

  • 删除其它索引

第一种方法：

alter  table  表名  drop  index  索引名；

索引名是show  keys  from  表名中的key_name 字段。

alter table test5 drop index name;

 第二种方法：

        drop  index   索引名   on   表名

         5.7 索引创建原则
  • 频繁查询的字段，应该创建索引。
  • 唯一性差的字段，不应该单独创建索引，即使，频繁查询，查询耗时。
  • 更新频繁的字段不应该建立索引。
  • 不会出现在where语句中的字段，不应该建立索引。

索引提高了查询的效率，但是，降低了插入，删除和修改的效率。

六. 其它概念
  • 复合索引

        多个字段作为索引。

        比如：主键，一个表中只能有一个。如果，设置多个主键，该表中也只有一个主键，是所有主键组合起来，充当这个表的主键。

        在主索引中，关键字就会使所有主键的组合。

  • 索引覆盖

        使建立在复合主键的基础上。查询的字段正好在关键字里，不会再查询到也直接点，直接返回。相当于覆盖率往叶子系欸但查找的过程。

  • 索引最左匹配原则

        如果索引关键字是"id，name"，如果使用select  *  from  where  name = '...'，不能进行查找，因为name再关键字的右边。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/5612871.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
人工少女3修改器 人工少女3修改器 一级用户组
0 0
上一篇 2022-12-15
下一篇 2022-12-15

发表评论

登录后才能评论

评论列表（0条）

保存