MySql 索引

一.索引作用

        索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

        索引就像一本书的目录，虽然增加了书的页数，但是大大加快了我们查询书中内容的速度。

二.认识数据库存储

        MySQL含有多种存储引擎，其中最常用的就是 InnoDB存储引擎,下面我们以InnoDB为例，简述数据库的存储。

        MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， 在InnoDB存储引擎的条件下MySQL 进行IO的基本单位是 16KB 。

        也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎使用16KB进行IO交互。即，MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）。

        这里大家可能有个问题，为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗? 

        以page为单位，是为了减少内存和磁盘间的IO次数。 将数据从磁盘加载到内存会耗费很大时间，根据系统局部性原理，当我们要使用或修改某些数据时，有很大概率会需要使用他附近的数据，这样我们通过加载一个page,读取多个数据，能大大减少IO次数，减少系统开销。

        这里我们需要建立以下共识：

• MySQL 中的数据文件，我们可以看成是以page为单位保存在磁盘当中的。
• MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位 就是Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称 为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 何为更高的效率，一定要尽可能的减少系统和磁盘IO的次数

        也就是说，  MySQL 服务器预先在内存开辟了一段空间，当我们需要对数据进行操作时，以page为单位，从磁盘中加载进内存，然后再以特定的刷新策略刷新到磁盘。

 三.索引的理解和底层实现

        我们可以了解由于要对多个数据进行处理，内存中一定会有多个page,那么如何对这些page进行管理呢？先描述再组织。

         InnoDB存储引擎，和MyISAM存储引擎中都是采取B+树的形式对数据进行存储， Memory存储引擎则是使用哈希索引的结构存储。这里我们主要讲解B+树的形式。 

        我们结合实际操作讲解，先创建一个表。

        插入以下数据：

         查表：

         可以看到数据自动安装主键的大小排序，其实对于主键，MySql会自动为我们建立主键索引。

        如同一本书有目录，page也有目录，称为索引， 每一个page中，不仅存储着表数据，还存储着目录，目录我们可以看成key-value的键值对，key就是我们的主键或唯一键等（上面的例子中就是id了），value就是对应数据的地址。

        以上是单个page,对于多个page,查阅上面的目录也很复杂，因此我们给Page也带上目录。 呈现出下面的结构：

• 使用一个目录项（value）来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。类似于书的目录，指向的章节的起始页数.
• 和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
• 其中，每个目录项的构成是：键值+指针。图中没有画全。

但是一旦数据多起来，查阅上面的结构也比较复杂，这时我们可以再增加一层。

         这就是传说中的B+树，通过B+树的形式我们就能构建出索引。Page分为目录页（上层）和数据页（最底层）。目录页只放各个下级Page的最小键值。 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。

        我们可以看到B+树是胖矮状态的，我们只先需要加载顶层目录页，再加载对应的一个次级目录，最后加载一个数据页，这样通过B+树的结构我们大大减少了IO次数。

我们再来讨论为什么不采取其他数据结构？

•  链表？线性遍历，IO多次。
•   二叉搜索树？退化问题，可能退化成为线性结构。
•   AVL &&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。
•  Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash，根据其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，另外还有其他差别，有兴趣可以查一下。 

       那么对于B树呢？ 

       首先我们要了解B树的结构：

        

         B树节点，既有数据，又有Page指针。而B+，只有叶子节点有数据，其他目录页，只有键值和 Page指针， B+叶子节点，全部相连，而B没有。

        为何选择B+ ,B+节点不存储data，这样一个节点就可以存储更多的key,可以使得树更矮，所以IO操作次数更少。 叶子节点相连，更便于进行范围查找。

聚簇索引 VS 非聚簇索引

MyISAM 存储引擎-主键索引 ，MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引， Col1 为主键。

        其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。 相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的。也就是一个数据节点是存储数据，一个存储地址。

         当我们使用engine=MyISAM，存储表格时，可以看到三个不同后缀的文件

        使用engine=InnoDB时，可以看到俩个文件

这也表明，InnoDB索引和数据在一起，MyISAM索引和数据分离。 

四.MySql中对索引的操作

创建主键索引

第一种方式：

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

第三种方式：

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

 主键索引的特点：

• 一个表中，最多有一个主键索引，当然可以使符合主键 主键索引的效率高（主键不可重复）
• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int

唯一索引的创建 

第一种方式

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

第二种方式

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建 

第一种方式:

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);

第二种方式:

create table user9(id int primary key, name varchar(20), email
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式:

create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引的创建 

        当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。比如想从文章中找到某个单词或句子时，可以使用全文索引，加快速度。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进 行全文检索，可以使用sphinx的中文版(coreseek)。下面看例子操作。

        创建一个表格，并向其中插入部分数据。

        在表格中用全文索引的形式查找含有database单词的句子。

                 使用explain 可以查看语句用了什么类型的索引。

        可见key值不为空，使用了全文索引。 

复合索引的创建

        复合索引就是用多列的属性当列。如当我们频繁的需要通过查询学号来查询名字时，我们可以将学号和名字建立复合索引，这样当我们遍历B+树，可能在顶层查询学号，刚好在非叶子节点层就查到对应的姓名，这时就可以直接返回，减少IO次数。

查询索引

第一种方法： show keys from 表名

第二种方法: show index from 表名;

第三种方法（信息比较简略）： desc 表名；

删除索引

• 第一种方法-删除主键索引： alter table 表名 drop primary key;
• 第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的 Key_name 字段
• 第三种方法方法： drop index 索引名 on 表名 

索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引 

        索引就讲解到这里了，如果你觉得有帮助的话，请各位点点赞和收藏吧。 

原文地址：https://blog.csdn.net/2301_76293625/article/details/142643523

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！