【ONE·MySQL || 索引】

总言

  主要内容：是什么、为什么、怎么用。以金字塔三角形结构，先介绍下层内存硬件的IO问题，再介绍索引的底层实现，最后再来介绍索引的操作使用。
  
  

  
  
  

1、前提认识（准备工作）

1.1、MySQL与OS、磁盘如何交互？

1.1.1、认识磁盘

  磁盘方面的相关内容见：磁盘管理（基础IO章节）。

  MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。
  作为计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的。再加上IO本身的特征，如何提交效率是 MySQL 的一个重要话题。
  
  
  

1.1.2、演绎草图

  1）、mysqld 在哪里工作？
  1、在网络层面， mysqld 是一个驻留在应用层的服务端程序，它通过网络接口监听并接收来自 mysql client 的请求，然后解析并执行这些请求所对应的数据库操作。
  2、在操作系统层面， mysqld 本质是一个进程，mysqld中的所有的操作（数据处理、逻辑运算、状态管理等等），都在计算机的内存中完成。
  3、为了保证数据的持久性和可靠性， mysqld 会周期性地将其内存中的数据变更（如新增、修改或删除的记录）同步到磁盘上，这一过程被称为“持久化”。
  

  2）、mysqld在对数据进行CRUD操作时，都是在哪完成的？

  1、MySQL 对数据的 CURD 操作，本质就是操作文件内容（如，通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据）。

  2、而MySQL 中的数据文件保存在磁盘中（以page为单位）。任何磁盘数据，要想在进程中要进行操作，本质都必须在内存中进行，即需要CPU参与。而为了便于CPU参与，需要将一定量的数据从磁盘中读取移动到内存中。

目标数据不存在--->换入
内存大小固定，当内存不足时--->换出

  3、所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘（磁盘和内存的数据交互，即IO，这里IO的基本单位是Page）。

  4、为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互

  PS：索引的本质是协助MySQL查找数据，也是在内存中进行的。
  
  
  
  

1.2、MySQL 与磁盘交互的基本单位

1.2.1、演绎草图

  MySQL InnoDB引擎使用 16KB 进行IO交互
  内核使用 4KB 进行IO交互
  磁盘使用 512字节 进行IO交互

  1、
  2、这意味着：mysql内部会充满大量的page—>要进行管理（ 先描述，再组织）—>什么样的数据结构，来完成对mysql page的管理？（索引的基础）
  
  
  

1.2.2、为何IO交互的单位是 Page大小？

  1）、问题说明

  问题一： 根据上述可知，MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互。但是，为什么以Page(16KB)进行交互？需要多少数据就加载多少数据不行吗？

  举例说明： 现有id=1~5共5条记录。

  1、按照需要多少，加载多少的做法： 一次加载一条记录。若MySQL要查找id=2的记录，共需要2次IO（第一次加载id=1，第二次加载id=2）。同理，若MySQL要找id=5，那么就需要5次IO。

  2、若按page加载： Page为16KB，能保存很多记录。若这5条（或者更多）都被保存在一个page中，那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。而往后如果在查找id=1,3,4,5等记录时，就完全不需要进行IO，直接在内存中就可以找到。 所以，以Page大小进行IO，可以大大减少了IO的次数。

  
  
  问题二： 如何保证用户一定下次找的数据，就在这个Page里面？
  
  回答：不能严格保证，但是有很大概率，因为有局部性原理。（往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数）
  
  
  
  
  2）、局部性原理简单介绍
  局部性原理：是计算机中一个核心概念，尤其在磁盘数据加载和缓存机制中发挥着重要作用。这个原理指出，当一个数据项被访问时，其附近的数据项也很可能在不久的将来被访问。 这种局部性可以分为两种类型：空间局部性和时间局部性。

  空间局部性： 这意味着如果一个数据项被访问，那么紧接着很可能会访问相邻的数据项。这种局部性在数组和连续存储的数据结构中特别明显。
  时间局部性： 这指的是如果一个数据项在最近被访问过，那么在未来它也很可能被再次访问。这通常发生在循环和重复执行的代码段中

  
  
  
  
  

2、理解索引（理论部分）

2.1、引入篇

2.1.1、基本概念

  索引： 在MySQL中，索引是一种数据结构，它可以帮助数据库系统更快地查询数据库中的数据。没有索引，数据库系统可能需要遍历整个数据库来找到用户查询的数据，这被称为全表扫描，对于大型数据库来说是非常低效的。通过使用索引，数据库系统可以显著减少需要查看的数据量，从而提高查询速度。
  

  得失相等： 索引的价值在于提高一个海量数据的检索速度。然而，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。
  

  常见索引分为：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)：解决中子文索引问题

  
  
  
  

2.1.2、演示一：索引提高效率说明

2.1.2.1、使用的数据（海量表）

  以下为构建海量表的相代码指令：

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建.(可以拷贝下面代码）

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;


-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;


-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;


-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

  以下是该表基础预览：

  
  
  

2.1.2.2、演示添加索引前后查询效果

  未添加索引前： 查询员工编号为111222的员工，可看到本机下单人操作用于大约在4.93秒。以如此速度操作，在实际项目中，若在公网中同时有大量人同时并发查询，那很可能就死机。

mysql> select * from EMP where empno = 111222;

  
  添加索引后：

alter table EMP add index(empno);

  
  
  
  

2.1.3、演示二：乱序建表→默认有序

2.1.3.1、相关演示

  演示现象如下：

  问题： 从无序到有序
  1、是谁做的？
  2、为什么要这样做？

  
  
  

2.1.3.2、操作汇总

  建表：

mysql> create table if not exists user(
    -> id int primary key,
    -> age int not null,
    -> name varchar(16) not null
    -> );
Query OK, 0 rows affected (0.04 sec)


mysql> show create table user \G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

  插入数据：（这里我们将id打乱插入）

mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)

  
  
  
  
  

2.2、阶段理解一：page结构

2.2.1、理解单个Page

  根据之前章节1中的内容，MySQL 中有很多数据表文件，而要管理好这些文件，就需要 先描述，再组织 。

  目前，可以简单理解成一个个独立文件是由一个或者多个Page构成的。不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表。

  
  由此就可以回答上述2.1.3中乱序插入数据，会获取到有序记录的原因：

  1、谁做的？
  MySQL。因为有主键， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。
  
  2、为什么要这样做？（为什么要让数据库在插入数据时进行排序，而不使用插入的顺序？）
  插入数据时排序是为了优化查询的效率。 根据上述page可知，页内部存放数据的模块实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
  
  3、优化效率是如何做到的？
  ①、有序。 正是因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。
  ②、页目录（后续小节学习）。 有序可以方便添加页目录，从而提高查找效率。
  
  
  
  

2.2.2、理解多个Page

  衔接前文： 通过上述可知，页模式的功能就是在查询某条数据的时候，直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

  问题说明： 但是，由于页模式内部采用了链表的结构（前一条数据指向后一条数据），本质上还是通过数据的逐条比较来取出特定的数据。如果有存在海量数据（如1千万条数据），一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。
  那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

  因此，我们引入目录的概念。
  
  
  
  
  

2.3、阶段理解二：目录

  现实生活中，书本需要目录的主要原因是为了提高读者的阅读效率。（想象一下，如果一本书没有目录，读者想要找到特定的信息或知识点，他们可能需要一页一页地翻阅整本书，这无疑会消耗大量的时间。而有了目录，读者可以直接定位到相关章节，从而快速找到所需内容。这种信息检索的方式大大减少了不必要的翻阅，提高了阅读效率。）

  目录是一种“空间换时间”的做法，用少量的纸张内容作为目录，可提高查找效率。MySQL这样的关系型数据库中，同样采取了这种设计理念。 在MySQL中，每个数据页（page）通常都有一个内部的“目录”结构，也被称为索引或页目录。 这个页目录的作用与书本的目录类似，都是为了提高数据检索的效率。
  
  
  
  

2.3.1、理解数据页

  1）、基本说明

  在单个Page内部，引入了目录可快速定位到数据页中的特定记录。比如，我们要查找id=4记录，先前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。

  
  多页情况同理。在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。这样，就可以通过多个Page遍历，Page内部通过目录来快速定位数据。

  （PS：图为理想结构。要保证整体有序，那么新插入的数据，不一定会在新Page上面，这里仅仅做演示。）
  
  
  
  
  2）、问题说明
  上述情况，一定程度上提高了单个page页内的遍历。但对于多个page来说，仍旧存在效率问题。在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO（将下一个Page加载到内存，进行线性检测）。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

  对此可以如何解决？
  回答：与之前的思路一致，也给Page带上目录。
  
  
  
  

2.3.2、理解目录页

  1）、基本说明
  如下图：存在一个目录页来管理数据页中的目录，目录页中的数据存放的就是其指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

  和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
  
  
  如此类推，当数据量很大，只有单层目录页效率提高不大时，可往上再加一层目录页。（这种结构就是传说中的B+树）

  像上述结构，就完成了主键索引的建构。以下为一些相关说明：
  1. 具有主键的表，一表具有一个B+树。
  2. 没有主键的表，目前我们认为是所有的数据是线性组织的。 (但是，如果表中没有主键，mysql会自动形成隐藏主键)
  3. B+中所有的叶子节点、路上节点，全部都需要加载到内存吗？不需要，可以按需进行load mysql page。
  
  

  2）、小节
  总结一下：
  1、Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
  2、查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。
  
  
  
  
  

2.4、阶段理解三

2.4.1、InnoDB 在建立索引来管理数据时选择的结构

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

  这两棵树在此情景下最有意义的区别是：
  ①B树节点既有数据，又有Page指针。而B+，只有叶子节点有数据，其他目录页，只有键值和Page指针。
  ②B+叶子节点，全部相连，而B没有。
  
  这就意味着：B+节点不存储data，①这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。 ②叶子节点相连，更便于进行范围查找。
  
  
  
  
  
  

2.4.2、聚簇索引 VS 非聚簇索引

  1）、基本介绍
  聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中两种不同类型的索引。
  

  在索引结构上：
  聚簇索引： 聚簇索引的叶节点包含了行的全部数据。由于数据实际上是根据索引的顺序存储的，因此聚簇索引的叶节点直接就是数据行。
  非聚簇索引： 非聚簇索引的叶节点包含了指向数据行的指针，而不是实际的数据。这些指针用于定位到数据表中对应的数据行。
  
  
  在数据存储方式上：
  聚簇索引： 聚簇索引决定了表中数据的物理存储顺序。也就是说，表中的数据行实际上是根据聚簇索引的顺序进行排序并存储的。 一个表只能有一个聚簇索引，因为数据只能按照一种顺序进行物理存储。
  非聚簇索引： 非聚簇索引与数据的物理存储顺序无关。它包含指向数据表中数据行的指针，这些指针按照索引键的顺序排序。 非聚簇索引可以有多个，因为它们只是指向数据表中数据行的指针，而不是实际存储数据。
  
  
  
  2）、非聚簇索引举例：MyISAM 引擎
  MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。 InnoDB 是将索引和数据放在一起的，而MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。（下图演示为 MyISAM表的主索引， Col1 为主键。）

  
  
  3）、聚簇索引 VS 非聚簇索 （在存储结构上的演示）
  在数据结构层面的体现：B+树中叶子节点是否存储数据
  在文件层面的体现：分别以InnoDB和MyISAM两种存储引擎建表（以下为演示）

  根据上图演示可知：
  1、在 InnoDB 存储引擎中，数据和索引通常存储在同一个文件中，这个文件通常具有 .ibd 扩展名。
  2、在 MyISAM 存储引擎中，数据和索引是分开存储的。
  ①.MYD：这是 MyISAM 表的数据文件，它包含了表中的所有数据。
  ②.MYI：这是 MyISAM 表的索引文件，它包含了表的索引信息，用于加速查询操作。
  
  
  
  

2.4.3、主键索引 VS 辅助索引/普通索引

  1）、主键索引
  主键索引，通常也被称为聚簇索引（Clustered Index），是根据表的主键值建立的索引。主键是表中的唯一标识，用于唯一地标识表中的每一行数据。主键索引具有以下特点：

  唯一性： 主键索引的值必须是唯一的，不允许有重复。
  物理存储顺序： 主键索引决定了表中数据的物理存储顺序。也就是说，表中的数据行实际上是按照主键索引的顺序进行排序并存储的。
  单一性： 一个表只能有一个主键索引，因为主键必须是唯一的。
  快速检索： 主键索引能够快速地定位到表中的特定行，因为索引的结构（通常是B-tree）允许进行高效的查找操作。
  
  
  
  
  
  
  2）、辅助索引/普通索引
  辅助索引，也被称为非聚簇索引（Non-clustered Index），是根据表中的非主键列建立的索引。辅助索引与主键索引的主要区别在于它们不决定数据的物理存储顺序。辅助索引具有以下特点：

  非唯一性： 辅助索引的值可以重复，因为它只是基于表中的非主键列建立的。
  物理存储独立： 辅助索引并不改变表中数据的物理存储顺序，它只是一个指向数据表中数据行的指针集合。
  多个可能： 一个表可以有多个辅助索引，因为可以基于多个非主键列创建索引。
  提高查询性能： 辅助索引可以显著提高对非主键列的查询性能，尤其是当这些列被频繁用于查询条件时。
  
  一个例子：下图为InnoDB 引擎下建立的辅助（普通）索引。

  可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。 所以通过辅助（普通）索引，找到目标记录，需要两遍索引(不一定，有时不需要回表)：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。
  
  PS，在InnoDB中，非聚簇索引（通常称为二级索引或辅助索引）的叶子节点包含的不是数据行本身，而是数据行的主键值。当通过非聚簇索引查找到主键值后，系统需要再根据这个主键值回到聚簇索引（通常是主键索引）中去查找实际的数据行，这个过程就叫做“回表查询”。
  
  
  
  

3、索引操作（实际操作）

3.1、创建索引

3.1.1、创建表时的索引操作

  1）、如何判断某一列是否有必要构建索引？
  虽然索引能方便查找，但这不代表任意列构建索引都是有实际运用价值的。判断某一列是否有必要构建索引通常涉及多个因素，包括查询性能、数据更新频率、数据的唯一性、索引维护成本等，可以从多方面考虑。以下为简单举例：
  1、查询频率和性能： 如果这一列经常作为查询条件（WHERE、JOIN、ORDER BY、GROUP BY等），则可考虑为其建立索引。如果查询该列的条件能显著提高查询性能（例如，大大减少需要扫描的数据行数），则索引可能是有益的。
  2、数据的唯一性： 如果这一列的数据非常唯一（例如，用户ID、电子邮件地址等），那么索引的效益可能不大，因为数据库可以快速定位到唯一值，不需要扫描大量数据。如果这一列的数据重复度很高（例如，工作类别、年龄等），索引可能会更有用，因为它可以帮助数据库快速过滤掉不需要的数据。
  3、数据更新频率： 如果这一列的数据经常更新，索引可能会降低数据更新的性能，因为每次数据更新时，索引也需要相应地进行更新。在高更新频率的列上创建索引时，需要权衡查询性能的提升和更新性能的下降。
  4、索引维护： 索引需要空间存储，并且每次插入、删除或更新数据时都需要维护。因此，创建过多索引可能会增加存储和维护成本。
  
  索引创建原则：

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

  
  
  2）、举例说明创建表时的索引操作
  在MyISAM存储引擎中，构建主键或者普通索引就是构建B+树，叶子保存数据记录的地址即可。
  在InnoDB存储引擎中，构建主键索引，由于其是聚簇的，叶子节点直接包含了行数据。若构建的是是普通索引，只需要根据普通列构建B+然后叶子上放的就是该普通数据对应记录的主键ID，后续查找的时候，可以通过回表的方式进行查找即可。
  
  
  
  

3.1.2、创建主键索引

  1）、在建表的时候添加主键索引
  在创建表的时候，直接在字段名后指定 primary key，被指定为主键的列也就自动创建了一个主键索引。

--两种写法（也是建表时添加主键的两种写法）

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

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

  
  
  
  2）、建表后在表中添加主键索引
  如果表已经存在，可以使用ALTER TABLE语句添加一个主键（也就意味着创建了一个唯一索引）。但需要注意，添加主键之前该列中的所有值都必须是唯一的，并且不能有NULL值。

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

  
  
  
  3）、主键索引的特点：

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

  
  
  
  

3.1.3、创建唯一索引

  1）、在建表的时候添加唯一索引
  在创建新表时，可以直接在列定义后面添加UNIQUE关键字来创建唯一索引。

--两种写法（也是建表时添加唯一键的两种写法）

-- 创建表时，在某列后直接指定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));

  
  
  

  2）、建表后在表中添加唯一索引
  如果表已经存在，可以使用ALTER TABLE语句来添加唯一索引。

create table user6(id int primary key, name varchar(30)）；
-- 创建表以后再添加唯一键
alter table user6 add unique(name);

  
  
  
  3）、唯一索引的特点：

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

  
  
  
  
  

3.1.4、创建普通索引

  1）、在建表的时候添加普通索引
  在创建新表时，可以直接在列定义后面添加INDEX关键字来创建普通索引。

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

  
  
  
  2）、建表后在表中添加普通索引
  如果表已经存在，你可以使用ALTER TABLE语句来添加普通索引。

ALTER TABLE example_table  ADD INDEX (column_name);
ALTER TABLE example_table  ADD INDEX idx_name (column_name);

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

  此外，还可以使用CREATE INDEX语句来创建并命名普通索引。

CREATE INDEX idx_age ON example_table (column_name);

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

  
  
  
  3）、普通索引的特点：

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

  
  
  
  
  

3.1.5、创建全文索引

  1）、基本介绍：
  在MySQL中，全文索引（Full-Text Index）是一种特殊类型的索引，用于在文本字段中执行全文搜索查询。 全文索引允许你快速检索包含特定单词或短语的记录，而无需对整个表进行扫描。这对于需要在大文本字段（如文章、评论等）中执行高效搜索的应用程序非常有用。

  MySQL中的全文索引是基于MyISAM和InnoDB存储引擎的。不过，请注意，MyISAM存储引擎提供了完整的全文索引功能，而InnoDB存储引擎的全文索引功能在某些方面可能有所限制（PS：innodb 从1.2.x 开始才增加了全文索引支持。而MySQL5.6版本中innodb的版本才升级到 1.2.x。）
  
  
  2）、创建方式和查询方式
  要创建全文索引，你需要使用ALTER TABLE语句或者CREATE TABLE语句，并指定FULLTEXT索引类型。

--方法一：
ALTER TABLE table_name ADD FULLTEXT(column_name);
--方法二：
CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    ...,  
    FULLTEXT(column1, column2, ...)  
);

--举例一：
ALTER TABLE articles ADD FULLTEXT(content, title);
--举例二：
CREATE TABLE articles (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    title VARCHAR(255),  
    content TEXT,  
    FULLTEXT(title, content)  
);

  创建了全文索引后，你可以使用MATCH()和AGAINST()函数来执行全文搜索查询：

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_term');

  注意事项：
  全文搜索是大小写不敏感的，并且默认情况下会忽略单词中的单复数形式和词干变化。
  全文搜索查询通常比普通的LIKE查询更快，因为它们使用了特殊的算法和索引结构。
加粗样式全文索引对于非中文内容效果较好，对于中文内容可能需要第三方解决方案或者使用其他搜索引擎（如Elasticsearch）。
  
  
  
  3）、相关演示：
  基本操作演示：

  
  可以用explain工具看一下，是否使用到索引：

  
  
  
  

3.2、查询索引

  方法如下：

--较常用的两种方式
show keys from 表名；
show index from 表名;

desc 表名；--相对而言信息简陋
show create table 表名; --这种方式也可以看出基本信息

  演示一：

  演示二：

  演示三：

          其中：
           Table：       表的名称
           Non_unique：  如果索引不能包括重复词，则为0。如果可以，则为1。
           Key_name：    索引的名称。
           Seq_in_index：索引中的列序列号，从1开始。
           Column_name： 列名称。
           Collation：   列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
           Cardinality： 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
            Sub_part：    如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
            Packed：      指示关键字如何被压缩。如果没有被压缩，则为NULL。
            Null：        如果列含有NULL，则含有YES。如果没有，则该列含有NO。
            Index_type：  使用的索引类型（BTREE, FULLTEXT, HASH, RTREE）。
            Comment：     索引说明。

  
  
  
  

3.3、删除索引

  DROP INDEX语句是MySQL删除索引常用的方式之一。

DROP INDEX index_ name ON table_ name ; 
--index name为要删除的索引名称，table name为要删除索引的表名。
--（如果给索引起了名称，要注意这里是删除的是索引名称，而非列名称）

  ALTER TABLE 语句也可以用来删除索引。

--若删除其他索引：
ALTER TABLE table_name DROP INDEX index_name;

--若删除主键索引：
alter table table_name drop primary key;

  
  相关演示：

  
  
  
  
  

3.4、其它

  1、索引最左匹配原则
  2、索引覆盖