索引是一种数据结构,用于快速查找和访问数据库表中的数据。索引的主要目的是提高查询效率,减少数据库的搜索时间。可以把它想象成一本书的目录:不需要逐页浏览整本书来找到特定的内容,而是直接查看目录,快速定位到所需的部分。
数据库按记录为单位存储数据,如果不使用索引而采取遍历查询数据,其时间复杂度是 O ( N ) O(N) O(N)。
总结:索引是数据的目录。在 MySQL 中,索引也叫做 Key。
索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。反之,如果记录的列存在大量相同的值,例如性别只记录了男或者女,那么它们大概各占一半,因此对该列创建索引无意义,它不是散列的。
可以对一张表创建多个索引。索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。索引虽然不够完美,但是它足够物美价廉,而且贴合数据库的使用场景:提高检索海量数据的速度。
磁盘和内存的 I/O 次数越少,效率越高。
对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。
首先用一个测试表来看看索引的威力。
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)
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);
使用方法:
- 退出 MySQL,将以上 SQL 保存在一个文件中,例如
index_data.sql
- 然后进入 MySQL server,使用命令
source index_data.sql
执行它。
由于它创建了 8000000 万条记录到数据库中,因此需要耗费一定时间(光标闪烁):
进入数据库中:
表的内容和结构如下:
表结构中表明它没有建立索引。
先尝试查询几条记录:
为员工编号建立索引后查询相同的记录:
结果显而易见。关于索引,这里只是简单地展示了它在使用时的性能,更多篇幅将会讨论它的实现原理,以更好地理解并使用索引。
磁盘在操作系统这门课中已经了解过,在此仅讨论和数据库索引有关的部分。以下内容部分引用自:数据库中的 B 树与 B+ 树
我们来看一下 磁盘 (disk) 的结构:一个典型的磁盘驱动器由一个或多个 盘片 (platter) 组成,它们以一个固定的速度围绕一个共同的 主轴 (spindle) 旋转。每个盘片表面覆盖着一层可磁化的物质。驱动器通过 磁臂 (arm) 末尾的 磁头 (head) 来读/写盘片。
盘片在 逻辑上 (而非物理上) 被划分为一系列的同心环状区域,数据就存储在这样的同心圆环上面,这些同心圆环被称为 磁道 (track)。每个盘面可以划分多个磁道,最外圈的磁道是 0 号磁道,向圆心增长依次为 1 号磁道、2 号磁道……磁盘的数据存放就是从最外圈开始的。
根据硬盘的规格不同,磁道数可以从几百到成千上万不等。每个磁道可以存储几个 Kb 的数据,但是计算机不必要每次都读写这么多数据。因此,再把每个磁道划分为若干个弧段,每个弧段就是一个 扇区 (sector)。
一个盘片被划分为许多磁道和扇区,一个磁道和一个扇区相交的区域称为一个 块 (block)。因此,磁盘上的任意一个块都可以通过其对应的磁道编号和扇区编号来寻址,也就是说,磁盘上的块地址格式由磁道编号和扇区编号组成:
块地址
=
(磁道编号,扇区编号)
块地址 = (磁道编号,扇区编号)
块地址=(磁道编号,扇区编号)
块是硬盘上存储的物理单位。出于稳定性考虑,通常一个块存储 512 字节的数据,但是实际上其容量可以是任意大小,具体取决于磁盘制造商和磁盘型号。
这里,我们假设每个块的容量为 512 字节。当我们从磁盘上读取或写入数据时,我们总是以块为单位进行读/写。如果现在我们读取一个 512 字节的块,假设其中第一个字节的地址为 0,最后一个字节的地址为 511,那么其中每个字节都有其各自的地址,我们称之为 偏移量 (offset)。
假设磁盘上的每个块的第一个和最后一个字节的偏移量都分别为 0 和 511。因此,我们只需要知道 磁道编号、扇区编号 和 偏移量 这三个信息就可以定位到磁盘上的任意一个字节:首先,利用磁道编号和扇区编号定位到该字节所在的块;然后,在块内通过偏移量定位到该字节。
正常情况下,我们可以通过盘片的旋转来选择扇区,通过磁头的轴向移动来选择磁道,也就是说,我们可以通过旋转盘片和移动磁头来定位到某个块,而数据总是以块的形式存储在磁盘上的。
我们知道,数据处理无法直接在磁盘上进行,数据需要被读入内存中处理后再写回磁盘,才能被程序读取。
内存中的数据可以被程序直接访问,我们将其称为 数据结构 (data structure)。而在磁盘上高效组织数据使得其能够以一种简单方式被利用的系统被称为 数据库管理系统 (DBMS)。因此要查找某个数据,本质就是在磁盘上找到这个数据存在的扇区。
MySQL 服务器(mysqld)在操作系统中是一个进程,在网络中是一个服务器,所以 MySQL 是运行在内存中的,因此对数据的所有操作包括索引都要在内存中进行。
MySQL 与磁盘交互的基本单位是“页”(Page)。在 MySQL 中,尤其是在 InnoDB 存储引擎中,数据以页为单位进行读写。和操作系统的“页”类似,这种设计有几个原因:
无特殊说明,下文都是在存储引擎为 InnoDB 的基础上讨论的。
通常情况下,MySQL 和磁盘交互的基本单位指的是 InnoDB 的默认页大小,是 16KB。
为什么是 16KB 而不是和操作系统一样是 4KB?
16KB 的默认页大小是 InnoDB 存储引擎根据多年的经验和性能测试选择的,旨在为广泛的应用场景提供最佳的性能平衡。然而,根据特定的工作负载和硬件配置,MySQL 提供了一定程度的灵活性,允许数据库管理员根据需要调整页大小。
简单地说,操作系统和数据库都为了 I/O 的效率设置了一个交互的基本单位:页(page),这是一个经验值。而 MySQL 作为数据库,它的 I/O 事件比操作系统更频繁,所以单位要更大一些。
注意,I/O 次数相比于单次 I/O 数据大小对 I/O 效率的影响大得多。
从冯诺依曼体系架构来看,MySQL 就是一个应用层的协议,它的作用类似一个文件系统,运行在操作系统之上,管理着磁盘中的数据。数据要被 CPU 处理,就必须加载到 mysqld 申请的内存中,然后通过系统调用写回磁盘。要管理这些数据,本质上是管理这些文件。和操作系统的思想类似,先描述,再组织。
为了减少内存和磁盘的 I/O 次数,mysqld 会为此向系统申请一块内存空间作为缓存,即 Buffer Pool。在数据发生改动后,MySQL 不会立即将它回写到磁盘中,而是存放在 Buffer Pool 中,当缓冲区有了一定数量的待写入数据后才会刷新。然而,内核也是有缓冲区的,因此 MySQL 中的待写入数据将会经过两个缓冲区的拷贝才会由内核写入磁盘。
谈到内存,往往避免不了要谈局部性原理。MySQL 和磁盘 I/O(跳过了磁盘和操作系统)的基本单位是一个 Page,这么做的目的是减少 I/O 次数,从而提高 I/O 效率。原因是下一次要访问的数据很可能也在这个 Page 中。
MySQL 作为运行在 OS 之上的应用软件,它只和文件交互,而不直接和数据交互(数据保存在文件中)。也就是说,为了减少和磁盘的交互次数,MySQL 尽量将所有操作都在它申请的内存中进行。
Buffer Pool 是 InnoDB 存储引擎的一个关键组件,用于提高数据库操作的性能。下面是 Buffer Pool 的作用:
首先用一个以 ID 作为主键的信息表作为测试表。
然后以 ID 乱序插入若干记录。
可以看到即使插入的主键是乱序的,MySQL 会按照主键对插入的记录进行排序。
为什么要这么做?
类似书本中的目录,一个 Page 相当于一个章节,章节内部的每一页都有编号,这样方便查找。Page 本身对于整个文件而言也相当于目录。Page 和 Page 中的记录都以链表的形式被组织起来。
一个 Page 的结构主要包括几个关键部分(了解即可):
图片来自:https://blog.j 列 e.us/2013/01/07/the-physical-structure-of-innodb-index-pages/
InnoDB 通过这样的页结构,实现了其高效的数据存储和访问机制。每个页都通过 B+树结构组织在一起,无论是数据页(B+树的叶子层)还是索引页(B+树的非叶子层),都遵循这种结构。这使得 InnoDB 能够高效地进行数据的读取、插入、更新和删除操作。
其中 User Records 存储的是数据,图示将它作为“数据字段”,其他部分作为“属性字段”。B+树将会在后续介绍。
值得注意的是,在 MySQL 的 InnoDB 存储引擎中,一个 Page(页面)记录的数据通常不会来自不同的表。每个 Page 是专门用于存储单一表中的数据或索引信息的。这是因为 InnoDB 的表和索引是基于 B+树数据结构组织的,而每个 B+树结构是独立于表的基础上构建的。这一点将会在后文中解释。
一个页中存放的数据记录以链表的形式被组织起来,当达到一定数量后,线性查找的时间复杂度会降低效率,在页内维护数据记录的目录以提高查找效率。
页内目录的目的:
页内目录的结构:
页内目录的机制:
页内目录的好处
由于页内目录是 Page 内的记录,所以这是一种空间换时间的做法,现实中书本的目录也是如此。
现在能解释最初为什么 MySQL 可以对记录排序了。因为 MySQL 默认会对含有主键的表的记录进行排序。页内部的数据记录本质是一个链表,链表的特点是增删快而查改慢,所以只要是有序的,那么二分查找的每一步都是有效的。并且主键的性质能保证排序是一定正确的,反之排序的依据不是主键(例如是性别),那么为之建立的索引也是无意义的。
MySQL 的一页大小是 16KB,如果单页不断被插入记录,那么在容量不足时 MySQL 会开辟新页来储存新记录,然后通过指针记录新页的位置。
图片来源(包括下文):https://blog.csdn.net/chenlong_cxy/article/details/128784469
值得注意的是,每一个 Page 内部和整体都是保持有序的,这意味着并不是每一条新纪录都会在新的 Page 中。这些关联在一起的 Page 共同维护着同一张表的所有记录,如果 Page 数量过多,那么 MySQL 在查询时仍然需要遍历 Page。虽然事先在 Page 内部使用了页内目录,但是首先得找到正确的 Page 后它才能发挥作用。
类似地,为每一个 Page 都建立目录,以供 MySQL 更快地找到正确的 Page。这类似某些检索系统,通过多级索引,最终划分到细支上。
以上讨论的 Page 的目录,叫做 B+树索引。那么什么是 B+树呢?有没有 B 树?
总之,B+树是含有索引的查找树,如果不断地为 Page 建立索引,那么最终总会有一个根结点作为索引的入口。
这是 InnoDB 存储引擎的索引结构,它是一棵 B+树。当一张表的数据量增加到需要多个页来存储时,InnoDB 使用一种结构来组织这些页,这个结构称为** B+树索引**。
在操作系统中的多级页表和多级索引也是类似的思想。
注意:在 B+树中,所有的数据记录都存储在叶子节点中,而内部节点仅存储键值作为索引。
B+树的特点:
当表被设置主键后,MySQL 会将它以 B+树的形式维护起来(叶子节点存数据,其他节点存索引),通过查询 B+树来提高效率。在一个有主键索引的表中,一个 B+树通常维护的是一张表中的所有记录。
是否所有 page 节点都需要加入到 Buffer Pool?
B+树一般有几层,在保证一定性能的情况下可以保存多少条记录?
B+树的层数和它能保存的记录数量依赖于几个关键因素,包括树的阶(即每个节点可以包含的最大子节点数),页(节点)的大小,以及记录的大小。这些参数决定了 B+树的高度和它能够有效管理的数据量大小。
B+树的层数通常很少,这是因为每个节点可以包含大量的子节点,这样的高分支因子使得即使是在存储大量记录的情况下,B+树的高度也相对较低。这是 B+树非常适合用于数据库索引的一个原因,因为即使是庞大的数据集也可以通过几次磁盘 I/O 操作访问到。
假设一个 B+树的阶是 100,这意味着每个内部节点可以最多有 100 个子节点,而每个叶节点可以包含最多 99 个记录(或索引项)。
实际上,即使是几百万到几十亿条记录,B+树的层数也通常只需维持在 3 到 4 层,这极大地减少了数据检索时的磁盘 I/O 次数,保证了数据库操作的高效性。
为什么 B+树的非叶子节点不存储数据呢?
索引和记录都被记录在 Page 的数据段中,这么做可以让一个 Page 都记录索引,这样这棵 B+树就会比较“矮胖”。换句话说就是让存放数据的节点只有一层叶子节点,其他节点就能全部用作存储索引,层数越低,I/O 次数越少,效率越高。如果数据和记录一起存储在一个 Page 中,那么 B+树就会变得比较高。
从 B+树的结构来看,它是边使用边构建的。
索引可以使用什么数据结构?
下面是几个常见的存储引擎,与其所支持的索引类型:
存储引擎 | 支持的索引类型 |
---|---|
InnoDB | BTREE |
MyISAM | BTREE |
MEMORY/HEAP | HASH、BTREE |
NDB | HASH、BTREE |
为什么不使用 B 树作为索引的结构?
而其他数据结构虽然很高效,但是效率接近二分,而 B+树的效率高于二分,每次都可以筛掉一大部分不符合条件的分支。哈希空间满后需要重新构建哈希,这样反而效率会降低,虽然这可以通过新老哈希来解决,但是和 B+树相比,还是有点麻烦了。
像 B+树这样,将所有数据存储在叶子节点的索引就是聚簇索引,反之是非聚簇索引。
不同存储引擎使用不同的索引结构,例如 MyISAM 就是非聚簇索引,InnoDB 就是聚簇索引。我们知道在 MySQL 中建表,实际上是在磁盘中创建文件,其中.frm
是结构文件。
采用 InnoDB 存储引擎创建表时会生成一个.ibd
文件,该文件中存储的是索引和数据相关的信息,索引和数据是存储在同一个文件中的。
采用 MyISAM 存储引擎创建表时会生成一个.MYD
文件和一个.MYI
文件,其中.MYD
文件中存储的是数据相关的信息,而.MYI
文件中存储的是索引相关的信息,索引和数据是分开存储的。
当插入记录时,使用 MyISAM 的表会立即写入到磁盘中,而使用 InnoDB 的需要刷新才会变化。
主键索引是数据库表中的一种特殊索引,用于唯一标识表中的每一行记录。
主键索引的主要特点和作用包括:
唯一性:主键的值必须是唯一的,不能有重复。这意味着通过主键可以唯一确定表中的每一条记录。
非空性:主键字段不能为 NULL。每一行都必须有一个主键值。
索引:主键自动成为一个索引(在大多数数据库管理系统中是聚簇索引),这使得基于主键的数据检索非常快速。因为聚簇索引影响数据的物理存储顺序,所以基于主键的查询可以高效地执行。
数据完整性:主键帮助维护数据的完整性。它确保了表中的每一行都可以被清晰地识别和引用。
外键关联:在关系型数据库中,其他表可以通过主键来引用该表中的记录,主键成为这种关系的基础。这种通过主键和外键建立的链接是维护数据完整性和实现数据之间关系的关键机制。
使用场景:选择主键时,通常选择不会更改的数据列。常用的主键类型包括自增整数(在很多数据库系统中被称为自动编号的字段)和全局唯一标识符(GUID)。
方法一:在属性名后指定
create table t1(
id int primary key
);
方法二:在表后指定
create table t2(
id int,
primary key(id)
);
方法三:在已有表中使用 alter 和 add 添加
create table t3(
id int
);
alter table t3 add primary key(id);
注意,不要随意定义主键,一张表只能有一个主键,即只能有一个索引。mysqld 会为有主键的表自动构建主键索引(聚簇索引和非聚簇索引)。
复合主键形式上虽然是两个主键,但它们的列值组合是唯一的,所以可以当做一个主键来使用,复合主键将自动成为表的聚簇索引。
唯一索引是数据库表中的一种索引,它确保索引键列中的每个值都是唯一的。这意味着两行不能有相同的索引键值。唯一索引用于防止数据表中出现重复的记录,从而保持数据的完整性和准确性。它既可以作为数据完整性的一个约束,也可以提高基于这些列的查询的效率。
方法一:在属性名后指定
create table t1(
id int unique
);
方法二:在表后指定
create table t2(
id int,
unique(id)
);
方法三:在已有表中使用 alter 和 add 添加
create table t3(
id int
);
alter table t3 add unique(id);
联合索引(也称为复合索引)是在数据库表的两个或多个列上创建的索引。这种类型的索引可以极大地提高涉及这些列的查询性能,尤其是当查询条件包含这些列的组合时。联合索引利用了数据库表中列的组合关系,以优化查询、更新和管理数据的操作。
联合索引的创建遵循特定的列顺序,这一点对于查询的优化至关重要。例如,如果在列 1 和列 2 上创建一个联合索引,则索引会首先按列 1 排序,然后在列 1 的每个值内部按列 2 排序。这意味着,当你的查询条件同时包含列 1 和列 2 时,该索引可以非常高效地使用。然而,如果查询只涉及列 2,则这个联合索引可能不会被使用(除非索引是覆盖索引,即查询只需要索引中的数据)。
在 MySQL 中,可以使用以下 SQL 语句创建联合索引:
CREATE INDEX index_name ON table_name(列 1, 列 2, ...);
或者在创建表的时候直接定义索引:
CREATE TABLE table_name (
列 1 datatype,
列 2 datatype,
...
INDEX index_name (列 1, 列 2, ...)
);
最左匹配原则(Leftmost Prefix Principle)是数据库索引特别是复合索引查询过程中的一个重要原则。它涉及到如何利用复合索引进行查询优化和索引选择,从而影响使用数据库的效率。
复合索引是在表的两个或多个列上创建的索引。最左匹配原则指的是,在使用复合索引时,查询条件必须从索引的最左边的列开始,并且按照索引列的顺序进行匹配。数据库能够利用索引加速查询的能力取决于查询条件如何与索引的最左边的列对应起来。
假设有一个复合索引是在col1
, col2
, col3
上创建的(按此顺序)。根据最左匹配原则:
col1
,可以有效利用这个索引。col1
和col2
,也可以有效利用这个索引。col2
或只包含col3
,则无法有效利用这个复合索引。>
、<
、BETWEEN
等),它右边的列就不能再利用这个索引进行优化查询了。%
或_
),索引就不会被使用。普通索引,也称为标准索引或单列索引,是数据库中最基本类型的索引(实际应用多)。普通索引不强制实施任何数据完整性约束,如唯一性约束。这意味着,即使是使用了普通索引的列也可以包含重复的值。
普通(辅助)索引和主键索引最主要的差别是它的主键不能重复,非主键可以重复。
对于 MyISAM 存储引擎,构建主键索引或普通索引就是构建 B+树,叶子节点保存的是数据记录的地址。
InnoDB 存储引擎中构建主键索引(聚簇索引)和普通索引(二级索引或非聚簇索引)有所不同,其区别主要体现在数据的存储结构和访问方式上。这些区别直接影响了数据的检索效率和存储方式。
主键索引(聚簇索引)
普通索引(非聚簇索引)
下面这张表中 ID 是主键:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(32) 列 LATE utf8_bin NOT NULL COMMENT '名称',
`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `I_name` (`name`)
) ENGINE=InnoDB;
id name age
1 小王 12
2 小陈 13
3 小刘 14
对于查询:
SELECT age FROM student WHERE name = '小王';
主键索引的 B+树的叶子节点存储了整条记录:
而普通索引的 B+树的叶子节点只存储主键:
回表(Bookmark Lookup)
回表会对性能产生影响,优化的方式是索引覆盖(covering index,或覆盖索引)。
当一个查询能够完全通过一个或多个索引来获取所需的所有数据,而无需访问数据行本身时,我们称这种情况为“索引覆盖”。这意味着查询操作只需要读取索引,而不必访问表中的数据行。索引覆盖能够显著提高查询效率,因为索引结构(如 B+树)通常优化了数据的读取操作,且索引的大小通常小于整个表的大小,从而减少了磁盘 I/O 操作和提高了查询速度。
覆盖索引的使用方式如下:
对于查询:
SELECT age FROM student WHERE name = '小刘';
上面以 NAME 建立的普通索引首先需要被删除,然后以 NAME 和 AGE 建立联合索引。
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);
如果在创建表时,可以这样建立联合索引:
CREATE INDEDX i_name_age ON student(name, age);
这个需求是常见的:根据名称获取年龄或其他信息。那么建立它们的复合索引,索引本身包含了需要查询的年龄列,数据库可以直接用索引中获取这些数据而无需回表。这个索引是一个覆盖索引,它覆盖了上面的查询。
方法一:
create table t4(
id int,
name varchar(20),
index(name)
);
方法二:在已有表中使用 alter 和 add 添加
create table t5(
id int,
name varchar(20),
);
alter table t5 add index(name);
方法三:在已有表中使用 create 和 on 添加
create table t6(
id int,
name varchar(20),
);
create index idx_name on t6(name);
全文索引是一种特殊类型的数据库索引,它允许对文本内容中的所有单词进行索引,以便进行高效的全文搜索。这种索引类型适用于包含大量文本的字段,如文章、报告、评论等,使得可以快速检索包含指定关键词或短语的记录。全文索引的设计旨在解决传统索引方法(如 B 树索引)在处理文本搜索时效率不高的问题。
测试表如下,其中正文主题 body 是 text 类型。
由于 InnoDB 只有在版本 5.6 之后的 mysqld 才支持全文索引,所以这里指定存储引擎为 MyISAM。
插入几条测试记录。
用模糊搜索:
虽然这样能搜索到,但是通过explain
命令可以看到,模糊查询并未使用到索引,因此在本文很长时就需要耗费时间。
全文索引的使用方式:
但是在 MySQL 的默认设置中,最小搜索长度通常是 3 或 4,这意味着全文索引只会为长度大于等于 4 或 3 的词语建立索引。
如果搜索的字符串长度小于 3:
原因是这些词语没有被建立全文索引,无法用索引定位。
查看存储引擎的最小/最大搜索长度:
可以在/etc/my.cnf
中的[mysqld]
选项下追加以下内容:
[mysqld]
innodb_ft_min_token_size = 1
然后重启 MySQL 服务器,并修复全文索引。注意,修改完参数以后,一定要修复索引,否则参数不会生效。
方法一:使用命令repair table productnotes quick;
方法二:删除并重新建立索引
在 MySQL 中,EXPLAIN
命令是一个非常有用的工具,用于分析 MySQL 如何执行一个查询。开发者和数据库管理员经常使用EXPLAIN
来查看查询的执行计划,包括 MySQL 如何使用索引,是否进行了表扫描,查询如何连接表,以及估算的行数等。通过理解EXPLAIN
的输出,可以帮助优化查询语句,改善数据库的性能。
要使用EXPLAIN
命令,只需在你的 SELECT 查询前加上关键字EXPLAIN
:
EXPLAIN SELECT * FROM your_table WHERE your_列 umn = 'some_value';
这将返回 MySQL 如何执行该查询的详细信息。
EXPLAIN
命令输出的结果中包含多个列,每列都提供了执行计划的不同方面的信息。以下是一些最重要的列:
通过EXPLAIN
的输出,你可以识别查询中的性能瓶颈,如是否进行了全表扫描(type 列为 ALL),是否有更好的索引可以使用(possible_keys 与 key 列),以及查询涉及的行数(rows 列)等。
基于这些信息,你可能需要:
例如在使用聚合函数count(*)
检查表的行数时,由于这是一个精确的数字,所以可能需要遍历整个表而耗费时间,但是explain
可以迅速地返回一个估计值。
方法一:通过show keys from 表名
查询。
其中:
方式二:show index from 表名
方式三:desc 表名
假如测试表的结构如下。
删除主键索引:alter table 表名 drop primary key
删除非主键索引:alter table 表名 drop index 索引名
也可以使用:drop index 索引名 on 表名
删除非主键索引
由于一个表只有一个主键索引,所以在删除主键索引的时候不用指明索引名,而一个表中可能有多个非主键索引,所以在删除非主键索引时需要指明索引名。