【MySQL学习】MySQL索引特性

求知.

已于 2023-05-16 20:44:56 修改

阅读量622

点赞数 11

分类专栏： MySQL学习文章标签： mysql 数据库数据库索引

于 2023-05-16 20:03:34 首次发布

本文链接：https://blog.csdn.net/qq_61635026/article/details/130611085

版权

MySQL学习专栏收录该内容

12 篇文章 1 订阅

订阅专栏

一、初识MySQL索引

1.1 MySQL索引的概念

MySQL索引是一种数据结构，用于提高数据库查询效率。它是一种按特定列或列组合排序的数据结构，允许快速定位和访问数据库表中的数据行。通过创建索引，可以减少查询过程中需要扫描的数据量，从而提高查询性能。

索引可以理解为数据库表的目录，其中包含了对表中数据行的引用。MySQL使用B树（B-tree）和哈希（Hash）等数据结构来实现索引。B树索引是最常见的类型，适用于范围查询和排序操作，而哈希索引适用于等值查询。

1.2 MySQL索引的作用

首先通过一个例子来说明为什么需要索引，先创建一张存储海量数据的表，在查询的时候，看看没有索引时有什么问题？

--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性，所以使用存储过程来创建

drop database if exists `bit_index`;
create database if not exists `index_db` default character set utf8;
use `index_db`;


-- 产生随机字符串
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);

创建并插入8000000条数据后，在进行以下操作：

查询员工编号为998876的员工

此时可以发现，单单是执行这一条SQL语句就耗时4.58秒，并且还是一个人在本机进行操作的情况下进行的。在实际项目中，如果放在公网中，假如同时有成百上千个人并发查询，那很可能就死机。

解决办法就是创建索引，下面为empno字段创建了一个索引

此时再次执行以上的查询语句

可以发现此时的查询速度就非常快了。

二、MySQL的数据存储

2.1 MySQL存储与磁盘之间的关系

MySQL是一种关系型数据库，它将数据存储在磁盘上，以持久化的保存数据，以下是MySQL存储与磁盘之间的关系：

数据文件：MySQL使用数据文件将表和索引数据存储到磁盘上。每个数据库表对应一个或多个数据文件，用于持久化存储表的数据行。
存储引擎：MySQL支持多个存储引擎，如 InnoDB、MyISAM、Memory等。每个存储引擎在磁盘上存储和管理数据的方式有所不同，原因在于使用了不同的数据结构和算法。
数据页：在存储引擎的层次结构中，数据被分割成固定大小的数据页(Page)。数据页是存储引擎从磁盘读取和写入数据的最小单位，不同的存储引擎其数据页大小不同，而MySQL的InnoDB存储引擎的数据页大小为16KB。
缓冲池(Buffer Pool)：为了提高性能，MySQL使用内存中的缓冲池来预存热门的数据页。缓冲池允许快速的访问数据，减少磁盘I/O次数。
索引文件：为了加快数据检索的速度，MySQL使用索引来组织和管理数据。索引文件存储了索引数据结构，以及指向数据文件中实际数据位置的指针（对于非聚簇索引而言）。
日志文件：MySQL使用事务日志（Transaction Log）来记录对数据库的修改操作，以保证数据的一致性和持久性。日志文件通常存储在磁盘上，用于恢复和重放操作。

通过将数据持久化到磁盘上的数据文件，MySQL可以在服务器关闭和重启后保留数据。它提供了机制来管理数据的读取和写入，以及保证数据的一致性和可靠性。磁盘的性能和存储能力对于数据库的性能和扩展性至关重要，因此，数据库管理员通常需要根据实际需求进行磁盘配置和优化。

2.2 MySQL与磁盘交互的基本单位

MySQL作为一款应用级软件，可以将其想象成一种特殊的文件系统，但MySQL有更多的I/O需求及场景，为了提高其基本的I/O效率，因此相比较于文件系统来说，其与磁盘交互的基本单位也就更大。

我们可以使用下面的SQL语句来查看采用InnoDB存储引擎时I/O的基本单位：

show global status like 'innodb_page_size'

我们可以发现Page的大小为16384字节，也就是16KB，因此InnoDB存储引擎使用的是16KB大小的Page作为其基本单位。而磁盘的基本单位是512B，采用InnoDB存储引擎的MySQL其基本I/O单位远大于磁盘的基本单位，因此大大的减少了MySQL每次与磁盘进行I/O的次数，一定程度上提升了其性能。

2.3 认识数据页Page

在MySQL中，Page指的是数据库存储引擎在磁盘上存储和管理数据的最小单位。每个Page具有固定的大小，其大小取决于采用的存储引擎和相关配置。以下是对Page的基本认识。

组织数据：MySQL将表的数据按页的形式组织并存储在磁盘上。数据页是存储引擎从磁盘读取和写入数据的基本单位。数据页通常包含多个数据行，每个数据行存储着数据库表中的一条记录。
I/O操作：当需要读取或修改数据库中的数据时，存储引擎就会操作数据页。读取操作涉及从磁盘读取相应的数据页到内存中，而写入数据设计到将数据页更改并保存到磁盘中。
缓存：为了提高性能，MySQL使用缓冲池(Buffer Pool)来缓存热门的数据页。缓冲池位于内存中，减少了频繁的I/O操作，从而加快了对数据的访问速度。
索引与数据页：索引也存储在数据页中。索引的数据结构(B+树或者Hash)帮助快速定位到存储在数据页中的特定数据行。通过索引，可以有效地执行查询操作，加快对数据的检索速度。
空间管理：存储引擎负责管理数据页的分配和释放。当需要存储新的数据行时，存储引擎会分配一个可用的数据页来存储该数据。当数据行不再需要时，存储引擎会释放相应的数据页，以便将空间重新用于其他数据。

理解MySQL中的数据页对于优化数据库性能和存储管理非常重要。合理设置数据页的大小和管理策略，以及优化缓存机制，有利于提高数据库的读写性能和整体效率。

三、索引的理解

3.1 测试案例

建立测试表

一定要添加主键，只有这样才会默认生成主键索引。

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, '欧阳锋');

按照上面SQL语句的顺序向表里面插入数据，然后查询表中的记录：

发现竟然默认是有序的！是谁干的呢？排序有什么好处呢？如果再做个对比试验，那就是去掉创建表但是添加的主键，那么此时的结果就和SQL执行的顺序是一样的了。

此时就可以猜测其有序性一定与主键索引有关。

3.2 探究单个和多个Page存储数据时的情况

单个Page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织，我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在MySQL中，都是 16KB ，使用 prev 和 next 构成双向链表。因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

为什么数据库在插入数据时要对其进行排序呢？按正常顺序插入数据不是也挺好的吗？

插入数据时排序的目的，就是优化查询的效率。
页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
正是因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。

多个Page

通过对单个Page的理解中，我们知道，上面页模式中，只有一个功能：就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

如果有百万千万条数据，一定需要大量的Page来保存这些数据，这些Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找，这样的话效率也会非常低下了。

3.3 页目录

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

从头逐页的向后翻，直到找到目标内容。
通过书提供的目录，发现指针章节在xxx页，那么我们便直接翻到xxx页。同时，查找目录的方案，可以顺序找，因为目录页相比于内容页肯定少很多，所以可以快速定位到相应的目录。

本质上，书中引入目录耗费了更多的纸张，但是却提高了效率。所以，引入目录是一种 “空间换时间” 的做法。

而MySQL的索引就仿佛引用的书目录的做法，而引入的页目录的概念。

单页情况

针对上面的单页Page的情况，我们也能引入目录。图示如下：

此时，在一个Page的内部就引入的目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。

现在我们可以再次正式回答上面的问题了，为何通过键值 MySQL 会自动排序？答案就是为了引入目录。

多页情况

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

以下的图，是理想结构，目前要保证整体有序，那么新插入的数据，不一定会在新Page上面，这里仅仅做演示。

在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。
这样，就可以通过遍历多个Page，找到所需数据所在的Page，然后在该Page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的 I/O 操作，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

为了解决上面存在的问题，和前面的思路一样，那就是给Page也加上目录。

如下图所示：

使用一个目录项来指向某一页，而这个目录项存放的就是指向的页中存放的最小数据的键值。
和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是数据行。
其中，每个目录项的构成是：键值 + 指针。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。在查找某一个数据时，就可通过比较键值，找到该访问那个Page，进而通过指针，找到下一个Page。

其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

可是，在每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还是需要线性遍历，其实很简单，只需要再在上层加目录页即可。

其实这种结构就是B+树的结构了，没错，至此，我们已经给我们的表user构建完了主键索引。

此时，如果我们使用查找一个id，就会发现，现在查找的Page数减少了，也就意味着IO次数减少了，那么效率也就提高了。

认识页目录

MySQL中的页目录是指在存储引擎层面上，用于管理和组织数据页的一种数据结构。不同的存储引擎可能有不同的页目录实现，下面以InnoDB存储引擎为例来介绍MySQL的页目录。

在InnoDB存储引擎中，页目录通常称为"页目录（Page Directory）“或"索引页（Index Page）”。它是一个特殊的数据页，用于存储和管理其他数据页的信息。每个索引在InnoDB中都有一个相应的页目录。
以下是页目录在InnoDB中的一些关键特点：

层次结构：页目录采用了多层的树状结构，常见的是B+树。树的叶子节点指向实际的数据页，中间节点用于索引和快速定位。
聚簇索引：对于InnoDB中的主键索引，页目录直接管理了整个表的数据页。这使得聚簇索引能够高效地支持基于范围的查询。
叶子节点存储数据：页目录的叶子节点直接存储了数据行的内容，而非存储索引键和指向数据行的指针。这样可以减少一次访问，提高查询性能。
跳跃指针：页目录的中间节点包含了指向下一层节点的跳跃指针，用于加速查找过程。这样可以避免遍历整个树结构，快速定位到目标数据页。
分裂和合并：当页目录节点的数据发生变化时，InnoDB会根据需要自动进行页目录节点的分裂或合并，以保持树结构的平衡。

通过页目录，InnoDB存储引擎能够高效地管理和组织数据页，支持快速的数据访问和索引查找。页目录的设计和优化对于数据库的性能和扩展性具有重要意义。

3.4 为什么InooDB存储引擎建立索引不采用其他数据结构

线性表

线性表将数据按顺序排列，对于插入和删除操作的时间复杂度是O(n)，其中n是数据的数量。这会导致频繁插入和删除操作时的性能下降，不适合数据库中需要高效地插入和删除数据的场景。

二叉搜索树

搜索二叉树是一种二叉树结构，它具有以下特点：左子树的所有节点值小于根节点，右子树的所有节点值大于根节点。搜索二叉树在有序数据的查找上效率很高，但在频繁插入和删除数据时，树的平衡性可能会被破坏，导致树高增加，甚至退化为单枝树，查询性能下降。

AVL树或者红黑树

AVL树是一种自平衡二叉搜索树，通过维护节点的平衡因子（左子树高度和右子树高度之差）来保持树的平衡。虽然AVL树可以在插入和删除操作后自动进行平衡调整，但其平衡调整的代价较高，需要频繁的旋转操作，影响了插入和删除的性能。
红黑树也是一种自平衡二叉搜索树，相对于AVL树来说，它的平衡调整操作更少。红黑树通过在节点上添加额外的颜色信息（红色或黑色）和一些特定的规则来维持平衡。尽管红黑树在插入和删除操作的平衡调整上相对高效，但它在某些特定的数据库场景下可能不如B+树效果好。

Hash

哈希索引使用哈希函数将键映射到索引位置。虽然哈希索引可以在等值查询上具有非常高的性能，但它不支持范围查询和排序操作。此外，哈希冲突可能导致性能下降，并且哈希索引需要将整个索引结构加载到内存中才能获得较高的性能。

InnoDB存储引擎最终选择B+树作为索引结构，因为B+树能够更好地满足数据库的需求。B+树具有以下优势：

B+树的节点可以存储更多的键值对，减少磁盘IO次数，提高查询性能。
B+树的有序性使得范围查询更加高效。
B+树通过保持树的平衡性，可以减少插入和删除操作对树结构的影响，提供较好的性能和可扩展性。
B+树的叶子节点形成一个链表，有利于顺序访问，适合数据库的磁盘存储特性。

3.5 常见存储引擎建立索引采用的数据结构

存储引擎	索引数据结构
InnoDB	B+树
MyISAM	B+树
Memory	Hash表
NDB	Hash表
TokuDB	Fractal Tree
RocksDB	LSM Tree
MariaDB ColumnStore	B+树
Spider	分布式索引
FusionIO	B+树
CSV	无索引

3.5 聚簇索引和非聚簇索引

聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中常见的两种索引类型，它们在数据存储和索引组织方面有一些关键的区别。

聚簇索引：

聚簇索引是对数据表的物理排序，表的行存储在磁盘上按照聚簇索引的顺序排列。每个表只能有一个聚簇索引。
聚簇索引决定了数据的物理存储顺序，因此在聚簇索引上的范围查询是非常高效的，可以快速定位到连续的数据行。
聚簇索引的叶子节点存储实际的数据行，因此在聚簇索引上的查询可以直接获取到所需的数据，无需再次查找。
如果表没有定义主键，则聚簇索引会使用唯一非空索引（如果存在），或者会自动生成一个隐藏的聚簇索引。

非聚簇索引：

非聚簇索引是单独存储索引键值和指向实际数据行的指针，它们与数据行的物理存储顺序无关。一个表可以有多个非聚簇索引。
非聚簇索引提供了快速查找索引键值的能力，当进行索引搜索时，首先根据非聚簇索引定位到对应的数据行的物理位置，然后再访问数据行获取数据。
非聚簇索引的叶子节点存储了索引键值和指向数据行的指针，因此在非聚簇索引上的查询需要两次访问，首先是索引查找，然后是根据指针获取数据行。
非聚簇索引适用于频繁进行范围查询或按照不同的列进行排序的场景。

在选择聚簇索引还是非聚簇索引时，需要考虑到查询的模式、频率以及数据的访问模式。聚簇索引适合范围查询和基于顺序的访问，而非聚簇索引适合单个值的查找和排序。通常情况下，将主键定义为聚簇索引是一种常见的做法，而其他查询频繁的列可以创建非聚簇索引来提高性能。

3.6 MyISAM 和 InnoDB 存储引擎创建的索引

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

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

案例：

创建一个采用MyISAM存储引擎的数据库表

--终端A
create table mtest(
	id int primary key,
	name varchar(11) not null
)engine=MyISAM;           --使用engine=MyISAM

--终端B
[lhf@myhost ~]$ sudo ls /var/lib/mysql/test_db -l  --mysql数据目录下
total 20
-rw-r----- 1 mysql mysql   61 May 16 18:47 db.opt
-rw-r----- 1 mysql mysql 8586 May 16 18:47 mtest.frm  --表结构数据
-rw-r----- 1 mysql mysql    0 May 16 18:47 mtest.MYD  --该表对应的数据，当前没有数据，所以是0
-rw-r----- 1 mysql mysql 1024 May 16 18:47 mtest.MYI  --该表对应的主键索引数据
[lhf@myhost ~]$

可以发现，采用MyISAM存储引擎创建的数据库表在文件系统中一共有三个文件，其中mtest.frm用于存储表结构数据，mtest.MYD用于存储表数据，由于此时表中没有数据，因此其大小为0，mtest.MYI用于存储主键索引数据。这就是数据与索引结构分开存储的索引方案，也就是非聚簇索引。

创建一个采用InnoDB存储引擎的数据库表

--终端A
create table itest(
	id int primary key,
	name varchar(11) not null
)engine=InnoDB;           --使用engine=InnoDB

--终端B
[lhf@myhost ~]$ sudo ls /var/lib/mysql/test_db -l
total 108
-rw-r----- 1 mysql mysql    61 May 16 18:47 db.opt
-rw-r----- 1 mysql mysql  8586 May 16 18:49 itest.frm   --表结构数据
-rw-r----- 1 mysql mysql 98304 May 16 18:49 itest.ibd   --该表对应的主键索引和用户数据，虽然现在一行数据没有，但是该表并不为0，因为有主键索引数据
[lhf@myhost ~]$

可以发现，采用InnoDB存储引擎创建的数据库表在文件系统中一共有两个文件，其中itest.frm用于存储表结构数据，而itest.ibd用于存储表数据以及主键索引数据。这就是数据与索引结构一起存储的索引方案，也就是聚簇索引。

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

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

同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的Col3建立对应的辅助索引如下图：

可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。所以通过辅助（普通）索引，找到目标记录，需要两遍索引：

首先检索辅助索引获得主键
然后用主键到主索引中检索获得记录

这种过程，就叫做回表查询。之所以 InnoDB 针对辅助（普通）索引，不给叶子节点也附上数据，就是为了节约更多的空间。

四、索引的操作

4.1 主键索引的创建

第一种方式

-- 在创建表的时候，直接在字段名后指定 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类型

4.2 唯一索引的创建

第一种方式

-- 在表定义时，在某列后直接指定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，等价于主键索引

4.3 普通索引的创建

第一种方式

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);

普通索引的特点：

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

4.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是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;
-- 准备数据
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数据

如果使用如下查询方式，虽然查询出数据，但是没有使用到全文索引：

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

发现key字段为NULL，即表示没有使用到索引。

全文索引的使用方式：

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

通过explain来分析这个SQL语句：

此时发现在查询的时候使用到了全文索引title。

4.5 索引的创建原则

在创建MySQL索引时，有一些原则可以指导我们做出更好的决策，以提高查询性能和减少存储开销。以下是一些常见的MySQL索引创建原则：

选择适合的列：选择那些经常用于查询条件的列或经常被用作连接条件的列来创建索引。这样可以加速常见的查询操作。
考虑列的选择性：选择具有高选择性的列作为索引列。选择性是指索引中不同值的数量与表中总行数之间的比率。高选择性的索引可以更好地过滤数据。
考虑查询的频率：基于经常执行的查询，优先选择创建索引。对于频繁执行的查询，索引可以大幅提升性能。
避免过度索引化：过多的索引可能导致维护开销和存储开销增加。仅为常见的查询条件和连接条件创建索引，并避免创建不必要的、重复的索引。
考虑索引覆盖：如果查询只需要索引中的列而无需回表获取额外数据，则可以考虑创建覆盖索引。覆盖索引可以减少IO操作和数据访问的成本。
注意索引顺序：复合索引的列顺序很重要。根据查询的频率和列的选择性，将最常用和最具选择性的列放在最左侧，以获得更好的查询性能。
考虑表的大小和更新频率：对于大型表或频繁更新的表，需要谨慎选择索引，以避免对插入、更新和删除操作的性能影响。
定期维护和优化索引：随着数据的增长和变化，定期评估现有索引的使用情况，识别需要创建或删除的索引，并进行必要的优化。

以上原则可以作为指导，在创建MySQL索引时帮助做出更明智的决策。但需要注意，索引设计是一个综合考虑的过程，需要根据具体的应用场景、查询模式和数据特点来做出最佳选择。在实际应用中，通过性能测试和监控来验证索引的效果，并根据需要进行调整和优化。

4.6 索引的查询

创建以下表：

create table user(
	id int primary key, 
	name varchar(30) unique
);

第一种方法： show keys from 表名

第二种方法： show index from 表名

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

4.7 索引的删除

第一种方法：删除主键索引

alter table 表名 drop primary key;

第二种方法：其他索引的删除

alter table 表名 drop index 索引名 ---索引名就是 "show keys from 表名" 中的 Key_name 字段

-- 例如
alter table user10 drop index idx_name;

第三种方法

drop index 索引名 on 表名

-- 例如
drop index name on user8;

五、其他索引相关

5.1 复合索引

复合索引（Composite Index）是基于多个列的组合创建的索引。与单列索引只针对一个列创建索引不同，复合索引同时考虑多个列的值，提供更多的查询优化和灵活性。

下面是关于复合索引的一些要点：

索引组合：复合索引由多个列的值组成，列的顺序非常重要，它们决定了索引的排序方式。例如，可以创建一个复合索引包含列A和列B，索引键由A和B的值组成。
多列查询优化：复合索引在多列查询中提供了更好的性能。当查询涉及到复合索引的列时，数据库可以使用复合索引进行快速定位和过滤数据，减少磁盘IO和数据扫描的开销。
最左前缀原则：复合索引的最左前缀原则是指，可以使用复合索引中的前缀列来满足查询的需要。也就是说，如果查询条件只涉及到复合索引的前几列，数据库可以使用该复合索引进行优化。
覆盖索引：复合索引可以作为覆盖索引，如果复合索引包含了查询所需的所有列，可以避免回表操作，提高查询性能。
索引选择性：在设计复合索引时，需要考虑列的选择性。选择性是指索引中不同值的数量与表中总行数之间的比率。高选择性的列更适合作为复合索引的前导列，以提高索引的效率。
索引顺序的重要性：复合索引的列顺序非常重要。根据查询的频率和列的选择性，可以将最常用和最具选择性的列放在前面，以获得更好的查询性能。

需要注意的是，过多或不必要的复合索引可能会增加索引维护和存储的开销，因此在设计复合索引时要谨慎选择索引的列和顺序。合理使用复合索引可以提高查询性能，但过度使用可能会导致性能下降和资源浪费。综合考虑查询需求、数据访问模式和列的选择性等因素，选择适当的列和顺序来创建复合索引，以优化数据库的性能。

5.2 复合索引的最左匹配原则

复合索引的最左匹配原则是指在使用复合索引进行查询时，查询条件必须从复合索引的最左侧列开始，并且连续地匹配索引的左侧列，才能有效地利用索引进行快速查找。

以下是关于最左匹配原则的一些要点：

索引顺序：复合索引中的列顺序很重要。复合索引按照列的顺序进行排序，最左侧的列是主排序列，然后是第二左侧的列，以此类推。
最左匹配：查询必须从复合索引的最左侧列开始，然后连续匹配复合索引的左侧列，以使查询能够利用索引进行快速查找。如果查询条件不符合最左匹配原则，将无法有效地使用复合索引，而是转而执行全表扫描或其他索引查找方式。
范围查询的限制：最左匹配原则对于范围查询有一定的限制。如果查询中包含范围条件（例如大于、小于等操作符），只有在最左侧的连续列上的范围查询才能利用复合索引。
覆盖索引和最左匹配：当复合索引包含了查询所需的所有列时，称之为覆盖索引。最左匹配原则同样适用于覆盖索引，查询条件必须从覆盖索引的最左侧列开始。

最左匹配原则的重要性在于，它决定了复合索引的使用效果。如果查询条件符合最左匹配原则，数据库可以使用复合索引快速定位到满足查询条件的数据，提高查询性能。因此，在设计复合索引时，应该根据查询的频率和查询条件的模式，将最常用和最具选择性的列放在复合索引的最左侧，以获得最佳的查询性能。

5.3 覆盖索引

覆盖索引（Covering Index）是一种特殊的索引设计，它包含了查询所需的所有列，可以在索引层面上满足查询的需求，无需回表到数据页获取额外的数据。这种设计可以提高查询性能，减少IO操作和数据访问的成本。

覆盖索引在查询中起到了关键的作用，它通过减少IO操作和数据访问的成本，提高了查询性能。对于经常执行的查询，如果可以使用覆盖索引来满足查询需求，可以大幅提升数据库的性能和响应时间。因此，在设计索引时，需要综合考虑查询的需求、列的选择性和索引大小等因素，合理地使用覆盖索引来优化查询性能。

求知.

关注

11
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
7
评论
【MySQL学习】MySQL索引特性

MySQL索引是一种数据结构，用于提高数据库查询效率。它是一种按特定列或列组合排序的数据结构，允许快速定位和访问数据库表中的数据行。通过创建索引，可以减少查询过程中需要扫描的数据量，从而提高查询性能。
复制链接

扫一扫