「前言」文章内容大致是MySQL索引的学习。

「归属专栏」MySQL

「主页链接」个人主页

「笔者」枫叶先生(fy)

目录

• 一、索引概念
• 二、从硬件角度理解
• • 2.1 磁盘
  • 2.2 结论
• 三、从软件角度理解
• 四、共识
• 五、索引的理解
• • 5.1 一个现象和结论
  • 5.2 对Page进行建模
  • 5.3 索引可以采用的数据结构
  • 5.4 聚簇索引和非聚簇索引
• 六、索引操作
• • 6.1 创建主键索引
  • 6.2 唯一索引的创建
  • 6.3 普通索引的创建
  • 6.4 查询索引
  • 6.5 删除索引
  • 6.6 全文索引的创建
  • 6.7 索引创建原则

一、索引概念

• 如果没有索引，那么在查询数据时是直接一条条遍历表中的数据，那么查询的时间复杂度将会是O(N)
• 如果数据库表有索引，就能提高海量数据的检索速度，就能大大提高查找的效率
• 索引概念：索引是指对数据库中的数据进行结构化的组织和管理，以提高数据的检索效率。
• 索引的本质就是一种数据结构

MySQL是一款网络服务器，MySQL服务端是一直在内存中，MySQL客户端的CURD操作都会交给MySQL的服务端完成，即MySQL的所有CURD操作都是在内存中进行的，即索引也是在内存中的进行的。

比如，假设数据库组织数据的方式是线性的，查询的时间复杂度将会是O(N)；如果数据库组织数据的方式是以二叉树的，那么查询的时间复杂度将会大大降低。

所以，索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护，维护结构是需要成本的。

即查询速度的提高是以插入、更新、删除的速度为代价的

常见的索引分为：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）

先见一见索引

使用如下SQL创建一个海量数据表

drop database if exists `index_demon`;create database if not exists `index_demon` default character set utf8;use `index_demon`;-- 构建一个8000000条记录的数据-- 构建的海量表数据需要有差异性，所以使用存储过程来创建-- 产生随机字符串delimiter $$create function rand_string(n INT)returns varchar(255)begindeclare chars_str varchar(100) default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n doset 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)begindeclare 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))begindeclare i int default 0;set autocommit = 0;repeatset i = i + 1;insert into EMP values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());until i = max_numend 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);

上述SQL中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录。

将上述的SQL语句保存在一个文件中，然后MySQL中使用source命令执行文件中的SQL

等待SQL语句执行完成（时间有点久）

查看数据库就能看到一个名为index_demon的数据库

在查看EMP表中的数据时可以带上limit子句（数据量太大）

查询EMP表中指定工号的员工信息（该表没有索引），每次查询都要花费几秒进行查询（在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机）

下面给该表创建索引

再查询EMP表中指定工号的员工信息，发现查询的效率大大提高了

根据实验结果，发现索引能大大提高查询数据的效率，这就是索引的价值所在。

二、从硬件角度理解

2.1 磁盘

• MySQL给用户提供存储服务，存储的数据在磁盘这个外设当中
• 磁盘是计算机中的一个机械设备，相比于计算机的其他电子元件，磁盘的效率是比较低的
• 而如何提高效率是MySQL的一个重要话题，因此我们有必要了解一下磁盘的相关内容

磁盘在该文章有详细介绍，文章链接：点击传送，这里就简单说一下概念

磁盘的整体结构如下：（物理结构）

磁盘的存储结构

• 磁道（Tracker）：磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有自己的编号
• 柱面（Cylinder）：多个磁盘的同一个磁道重叠起来叫做磁柱
• 磁面（Head）：一个盘面，每个盘面都有自己的编号
• 扇区（Sector）：磁道的一个扇形区，每个扇区都有自己的编号

磁盘的基本读写单位是扇区，扇区大小一般是 512字节（512byte） ，每个盘片被分为若干个同心圆，每一个同心圆就是一个磁道，而每个磁道被划分为若干个扇区，每个扇区的大小都是 512字节

注意：近三十年来，扇区大小一直是512字节，但最近几年正在迁移到更大、更高效的4096字节扇区，通常称为4K扇区

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如： proc ， sys 之类，我们不考虑)

比如：数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件

在该目录下存储着mysql的文件

 ls /var/lib/mysql

新建一个数据库，本质上就是在该目录下创建一个目录（图中颜色没有高亮，普通用户查看）

所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区

而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的

扇区的定位方式

定位扇区时采用CHS寻址方式

注：通过 柱面Cylinder —— 磁头Head —— 扇区Sector进行寻址，这种寻址方法为 CHS寻址

说明一下：

• CHS寻址方式是磁盘定位扇区的方式，但实际CHS寻址方式对磁盘以外的设备来说没什么作用，因此系统软件在定位磁盘上的数据时采用的是LBA（LogicalBlock Address，逻辑区块地址）
• LBA是描述计算机存储设备上数据所在区块的通用机制，LBA和CHS之间可以通过计算公式进行相互转换，LBA存在的意义就是对底层逻辑器件进行虚拟化，让系统软件可以不用关心底层硬件具体的寻址方式，而实际底层硬件采用的还是CHS寻址方式

注：以上在磁盘篇章有详细介绍，这里就不再谈论。

2.2 结论

操作系统与磁盘交互的基本单位

操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：

• 操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码
• 此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了
• 即以下两个点：
• 不想让 OS 的代码和硬件强耦合，实现硬件和系统的解耦
• 操作系统与磁盘以4KB作为IO交互的基本单位，是为了提高IO效率（局部性原理）

磁盘随机访问(Random Access)与连续访问(Sequential Access)

• 随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
• 连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问

因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问

连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高

三、从软件角度理解

MySQL与磁盘交互的基本单位

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

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB

mysql> show global status like 'innodb_page_size';

注：后面统一使用InnoDB存储引擎进行讲解

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

Buffer Pool

• 实际上MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘
• 然而，OS是有内核缓冲区的。
• 因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。

四、共识

为了方面下面理解，我们需要有以下共识：

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

五、索引的理解

5.1 一个现象和结论

建立一个测试表，表当中包含用户的id、年龄和姓名，并将用户的id设置成主键

create table if not exists user (id int primary key, -- 一定要添加主键，只有这样才会默认生成主键索引age int not null,name varchar(16) not null);

然后插入多条记录，并且插入数据时没有按照主键的大小顺序插入，即乱序插入

insert into user (id, age, name) values(3, 18, '张三');insert into user (id, age, name) values(4, 16, '李四');insert into user (id, age, name) values(2, 26, '王五');insert into user (id, age, name) values(5, 36, '赵六');insert into user (id, age, name) values(1, 56, '田七');

插入完成后，查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的

引入主键之后，插入的数据自动按照主键排序这个排序工作是谁做的，为什么要这么做？

• 这个排序工作是MySQL自己做的，进行排序的目的是为了方便引入目录（下面谈）

重谈MySQL的page，MySQL与磁盘进行交互时为什么不是按需交互，而是以Page为基本单位进行交互（16KB）？

• 比如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO
• 但是，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录)，那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。
• 往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。
• 所以，就在单Page里面，就可以大大减少IO的次数
• 往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数导致IO效率低下

怎么保证，用户一定下次找的数据，就在这个Page里面？

• 这个是无法保障的，但是用户要找的数据会有很大的概率会在这个page里面（局部性原理）
• 局部性原理：当一个数据正在被访问时，那么下一次有很大可能会访问其周围的数据
• 如果局部性原理没有起作用，那就再把对应的Page加载到内存当中即可

综上，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率

5.2 对Page进行建模

• MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来
• 要管理MySQL内部的所有的Page，就需要对Page进行先描述，再组织
• 所以，不能简单认为Page就是一个内存块，Page内部也必须写入对应的管理信息

对Page进行建模如下：

struct page{struct page *next;struct page *prev;char buffer[NUM];}// 一个Page大小16KB

不同的Page ，在 MySQL 中，都是16KB ，都是使用prev和next指针构成双向链表，从而用链表的形式对Page进行了管理

假设测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：

• 每个Page结构体内部的数据会按照主键进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。目的是为了优化数据查询的效率
• 只要设置了主键，即便向表中插入的数据是乱序的，MySQL底层也会自动按照主键对插入的数据进行排序（创建了主键便是使用了索引，后序谈）

单个Page内创建页内目录（引入页目录）

• Page结构体内部存储的数据记录是以单链表的形式组织起来的。当页内部的数据量增多时，如果直接进行线性遍历，会导致效率低下。为了提高查询效率，可以在Page结构体内部引入页内目录
• 页内目录将Page结构体内部存储的数据记录按照主键划分为若干区域，并存储了这些区域的最小键值。在查询数据时，可以先通过页内目录找到目标数据所在区域的起始记录，然后再从该记录开始向后遍历，找到目标记录。
• 通过引入页内目录，可以减少不必要的线性遍历，提高查询效率

什么是页目录

比如我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：

• 第一种：从头逐页的向后翻，直到找到目标内容
• 第二种：通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位
• 本质上，书中的目录，是多花了纸张的，但是却提高了效率
• 所以，目录，是一种“空间换时间的做法
• 每个Page结构体内部的数据会按照主键进行排序，其实就是为了引入页内目录，因为只有数据按照主键排序后引入页内目录才有意义，就像书中每一页都是按照页码进行排序的一样，如果一本书的页码是乱序的，那么它的目录根本就没有意义

上面是理解单个Page，下面理解多个Page

多个Page

• 随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据
• 这时在查询数据时就需要，先遍历Page双链表确定目标数据在哪一个Page，然后再在该Page内部找到目标数据

Page之上创建页目录

• 随着Page的增多，遍历Page数量太大（本质也是线性遍历），也会导致查找效率降低
• 这时可以给各个Page结构体也建立页目录
• 页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值
• 其中，每个目录项的构成是：键值+指针，该目录不存储数据，只存放页目录
• 着数据量不断增大，Page变得越来越多，这时一个页目录无法管理所有的Page，这时就需要更多个的页目录

注意：目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址

页目录之上再创建页目录

• 随着数据量不断增大，我们可以不断在页目录之上再创建页目录，最终就一定能够得到一个入口页目录
• 这时在查询数据时就可以从入口页目录开始不断查询页目录，最终找到目标数据所在的Page，然后再在该Page内部找到目标数据

• 这个矮胖的树，实际就是一棵B+树，这棵B+树就是InnoDB的索引结构（存储结构）
• 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

B+树中的Page结点无需全部加载到Buffer Pool中：

• 当对MySQL中的某张表进行增删查改操作时，不需要将其对应B+树的所有结点全量加入到Buffer
  Pool中，甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中
• 当后续访问表中的数据时，再将该数据对应路径上的结点加入到Buffer Pool中即可，对于其他不需要的结点根本不用加入到Buffer Pool中，这一点和操作系统中的页表是很像

5.3 索引可以采用的数据结构

• 链表：查找时是线性遍历，效率太低。
• 普通二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。
• AVL树和红黑树：虽然保证了二叉树是绝对或近似平衡的，不会退化成线性结构，但AVL树和红黑树都是二叉树结构，这就意味着树的层高会比较高，而查询数据时都是从根结点开始向下进行查找的，这也就意味着在查询过程中需要遍历更多结点，如果这些结点还没有被加载到Buffer
  Pool中，这时就需要进行更多次的IO操作
• 哈希表（可以）：官方的索引实现方式中MySQL是支持HASH的，只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是 O(1) 的，但哈希表也有一个缺点就是不利于进行数据的范围查找

常见的存储引擎，与其所支持的索引类型：

注：这里的BTREE指的是B+树

B树 VS B+树

B+树是B树的一种变形结构

• B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作（IO效率降低）
• B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找

B树结构如下：

• B+树的每个节点只包含键值，所有的数据记录都存储在叶子节点中，并且叶子节点之间通过指针连接成一个有序链表（范围查询非常高效）
• 节点不存储data，这样一个节点就可以存储更多的key，可以使得树更矮，所以IO操作次数更少

B+树结构如下：

5.4 聚簇索引和非聚簇索引

MyISAM存储引擎 - 主键索引结构

• InnoDB存储引擎采用B+树作为索引的基本数据结构
• MyISAM存储引擎同样采用B+树作为索引的基本数据结构
• 与InnoDB存储引擎的B+树不同的是，MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址
• 非聚簇索引：像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引

注：图中Col1列为主键

InnoDB存储引擎 - 主键索引结构

• InnoDB是将索引和数据放在一起的
• 聚簇索引：像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引

聚簇索引 VS 非聚簇索引（实验）

创建一个是InnoDB为索引的表

mysql> create table if not exists test1(    -> id int unsigned auto_increment primary key,    -> name varchar(20)    -> )engine=InnoDB;

进入该路径，找到自己所在的数据库

ls /var/lib/mysql -l

进入自己所在的数据库

当采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件

• xxx.frm文件，该文件中存储的是表结构相关的信息
• 采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是所谓的聚簇索引，索引和数据是存储在同一个文件中的

再创建一个test2的表，使用MyISAM存储引擎创建表

mysql> create table if not exists test1(    -> id int unsigned auto_increment primary key,    -> name varchar(20)    -> )engine= MyISAM;

当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件

• xxx.frm文件，该文件中存储的是表结构相关的信息
• 采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件
• 其中xxx.MYD文件中存储的是数据相关的信息
• 而xxx.MYI文件中存储的是索引相关的信息
• 这就是所谓的非聚簇索引，索引和数据是分开存储的

普通索引

• MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这 种索引可以叫做辅助（普通）索引

MyISAM存储引擎 - 普通索引结构

• MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。
• 因此一张表可能会同时存在多个B+树结构，但由于MyISAM存储引擎的B+树叶子结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份

对于 MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复，如下图：

InnoDB存储引擎 - 普通索引结构

同样， InnoDB除了主键索引，用户也会建立辅助（普通）索引

• InnoDB存储引擎的普通索引采用的也是B+树结构，但普通索引的B+树中的键值可以重复，并且B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。
• 当根据普通索引查询数据时，会先查找普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询
• InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录

以上表中的 Col3 建立对应的辅助索引如下图：

注意：采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键

六、索引操作

6.1 创建主键索引

创建主键索引方式一

在创建表的时候，直接在字段名后指定primary key

mysql> create table if not exists user1(    -> id int primary key,    -> name varchar(20)    -> );

创建主键索引方式二

在创建表的最后，指定某列或某几列为主键索引

mysql> create table if not exists user2(    -> id int,    -> name varchar(20),    -> primary key(id)    -> );

创建主键索引方式三

创建表以后再添加主键

alter table user3 add primary key(id);

主键索引的特点：

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

6.2 唯一索引的创建

唯一索引的创建方式一

在表定义时，在某列后直接指定unique唯一属性

mysql> create table user4(    -> id int primary key,    -> name varchar(20) unique    -> );

唯一索引的创建方式二

创建表时，在表的后面指定某列或某几列为unique

mysql> create table user5(    -> id int primary key,    -> name varchar(20),    -> unique(name)    -> );

唯一索引的创建方式三

创建表以后再添加唯一键

alter table user6 add unique(name);

唯一索引的特点

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

6.3 普通索引的创建

普通索引的创建方式一

在表的定义最后，指定某列为索引

create table user7(id int primary key,name varchar(20),email varchar(30),index(name));

普通索引的创建方式二

创建完表以后指定某列为普通索引

alter table user8 add index(name);

普通索引的创建方式三

创建表后，使用create命令给指定字段创建普通索引，并指定索引名

-- 创建一个索引名为 idx_name 的索引create index idx_name on user9(name);

普通索引的特点

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

6.4 查询索引

查询索引方式一

语法：

 show keys from 表名;

例如，查询user1表

部分说明：

• Table： 表示创建索引的表的名称
• Non_unique： 表示该索引是否是唯一索引，如果是则为0，如果不是则为1
• Key_name： 表示索引的名称（索引也有名字）
• Column_name： 表示定义索引的列字段
• Index_type： 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）

第二种方法

语法：

show index from 表名;

比如查询user4的索引，有两行说明有两个索引

第三种方法

语法：（查出的信息比较简略）

 desc 表名;

比如查询user7表的索引

6.5 删除索引

删除主键索引

语法：

alter table 表名 drop primary key;

例如，删除user1的主键索引

删除非主键索引

语法：

alter table 表名 drop index 索引名;

注：索引名就是show keys from 表名 中的Key_name字段

例如，删除user4表的唯一键索引

第三种方法

该语法可以删除指定的非主键索引：

drop index 索引名 on 表名;

6.6 全文索引的创建

• 当对文章字段或有大量文字的字段进行检索时，会使用到全文索引
• MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文
• 如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

全文索引案例

创建一个文章表，表当中包含文章的id、文章名称、文章内容，并在创建表的最后通过fulltext给title和body列创建全文索引

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body) -- 全文索引)engine=MyISAM;

插入数据

INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');

如果要查询哪些文章中包含database关键字，我们可以通过模糊匹配进行查找（没有使用到全文索引）

select * from articles where body like '%database%';

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

在SQL语句前面加上explain，可以看到key对应的值为NULL，表示这条SQL在执行过程中没有用到任何索引

如果要通过全文索引来查询，需要使用match against进行搜

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

在这条SQL语句前面加上explain，可以看到key对应的值为title，表示这条SQL在执行过程中用到了索引名为title的索引

注：MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的

6.7 索引创建原则

索引创建的原则如下：

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

--------------------- END ----------------------

「 作者 」 枫叶先生「 更新 」 2023.9.1「 声明 」 余之才疏学浅，故所撰文疏漏难免，          或有谬误或不准确之处，敬请读者批评指正。

来源地址：https://blog.csdn.net/m0_64280701/article/details/132516434