mysql--索引

目录

1、长什么样

2、硬件理解

3、软件理解

4、进一步认识

5、索引的理解

6、为什么不选择其他数据结果？

7、聚簇索引和非聚簇索引

8、索引操作

（1）主键索引创建

第一种方式

第二种方式

第三种方式

主键索引的特点

（2）唯一键索引创建

第一种方式

第二种方式

第三种方式

唯一键索引的特点

（3）普通索引的创建

第一种方式

第二种方式

第三种方式

普通索引的特点

（4）查看索引

（5）删除索引

  (6)复合索引

9、哪一列适合作为索引？

10全文索引

---

理解索引，并熟悉相关索引操作

提高算法的因素：1、数据组织方式 2、算法本身
而索引，本质是一种数据的组织方式

索引是什么：主键、唯一键、普通索引

1、长什么样

代码：

alter table stu add index(id);

没有建立索引 VS 建立索引的区别：建立索引查询会更快，数量级的速度提高

2、硬件理解

IO硬件交互很慢,可以看博主操作系统部分关于文件系统的文章

3、软件理解

mysql进行大量的io交互，为了提高效率，每一次访问是16K，而非64k
mysql处于应用层
但是实际上，和底层硬件打交道的只有OS，
因此mysql和底层数据的交互实际上还是通过OS处理
此时，mysql实际上是和在os中开辟的一个缓冲区进行交互
在OS和硬件交互的实际情况中，还是每次以64K的大小进行

4、进一步认识

mysql的数据实际还是存储在磁盘中的
但是，mysql并不刻意绕过OS直接对硬件访问和操作
因此，数据需要通过OS加载到内存中
mysql的应用再将数据加载到自己的缓冲区内buffer pool进行相关操作
mysql自己处理数据时，每一次交互的单位是以16k作为基本单位，page
处理完的数据再交给OS
OS再通过合适的时机刷新回到磁盘
同时，mysql的CURD操作，是对数据的处理。
对数据的处理，本质就是计算
计算就要涉及CPU

5、索引的理解

现象：随机插入数据，但是查看表却是有序的！增加主键
如果不增加主键呢？

建表的时候，mysql会自动给我们进行数据排序
如果我们创建表的时候
没有指定主键列，会默认指定主键列
因此，不论是我们加上主键还是不加主键，都会自动以主键列进行排序
为什么？
为了方便查询
原来的数据是线性的排序：
图片
在原来的数据库数据结构中
事实上是一个一个大小为16K的page数据块，叫做数据页
以此为一个数据单元进行管理
再通过将这些数据单元进行线性连接排序
就得到一个整体的线性数据结构
这样，查找一个数据，
先找到第一个数据表找，如果没有
则到下一个数据表找，以此往后，直至找到
于是，数据的查询就是这样线性的方式进行
算法的执行，是依赖于数据结构而实现的
于是，在不建立索引的情况下
一般的查询都是以线性遍历为主
但是，当数据量很大的时候，
线性遍历的效率并不高
怎么办？
引入索引。
什么索引？
索引，其实就是目录
你可以理解为书本的目录
书本的内容就是数据
目录，就是对应的索引
这样，要找第n个数据，直接定向到对应数据页拿取数据即可
如何实现呢？
首先，在最底层的数据页上
一部分空间不存储数据，而是存储目录
例如：
1-100为一组
101-200为一组
201-300为一组
以此推之
现在你要找到第300个数据
如果是原来的线性遍历，你需要从前往后，规规矩矩遍历300次
但是现在不同，
你发现，300这个数据不在第一组，不在第二组，而是在第三组
于是，直接到第三组，再遍历第三组
于是，原本需要查找300次
现在只需要查找3+100次数
少了3倍！
如果是数据很大
那么，就再第一层的数据页的基础上，再加一层
第二层的数据页不存放数据，只存放目录索引
于是，数据查找的效率大大的提高！
因此，
索引的本质是使用16K的page数据页存储数据，然后引入目录
然后用B+树对page数据页进行管理
最底层是数据页，上层是目录页
这个叫做mysqk innode db下的索引结构
实质上，就是引入一个新的数据组织方式
依赖于这种B+树的特殊数据组织方式
使用了新的检索方法
从而提高了效率

B树：多叉树，底层数据为线性结构

6、为什么不选择其他数据结果？

链表是线性遍历，没区别
红黑树、AVL树？
虽然近乎于平衡，但是层级太高
和B+树对比，虽然都是自顶向下查询
但是，前二者是二叉树，后者是多叉树
明显多叉树要比二叉树的层级更低
层级低就意味着访问底层 I/O 的次数少
效率更高

hash？查找范围有限

7、聚簇索引和非聚簇索引

特性	聚簇索引	非聚簇索引
物理存储顺序	是（根据索引的顺序存储数据）	否（数据存储顺序与索引无关）
数据行数量	只能有一个聚簇索引	可以有多个非聚簇索引
性能	适合范围查询和排序操作	适合点查找和单列或多列的查询
存储结构	数据和索引一起存储	索引与数据分开存储
空间使用	较少，因数据与索引合一	占用额外存储空间

8、索引操作

索引分三类：主键索引、唯一键索引、普通索引

查看表的主键索引：

show index from table;

（1）主键索引创建

第一种方式

--在创建表的时候，直接在字段后指明主键索引

create table stu(
id int  primariy key;
name varchar(32),
gender  char(2)
);

第二种方式

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

create table stu (
id int,
name varchar(32),
gender cahr(2),
primary key (id)
);

第三种方式

create table stu(
id int,
name varchar(32),
gender char(2)
);--表格创建完毕后再添加主键alter table stu 
add primary key (id);

主键索引的特点

唯一性、

非空性、

自动创建、

提高查询性能、

支持外键、

排序

（2）唯一键索引创建

第一种方式

--在表格定义时，在字段后直接指定唯一键索引
create table stu (
id int primary key ,
name varchar(32) unique,
gender char(2)
);

第二种方式

--创建表时，在后面指定某列 / 几列作为唯一键索引
create table stu(
id int primary key,
name varchar(32),
gender char(2),
unique (name)
);

第三种方式

create table stu(
id int primary key,
name varchar(32),
gender char(2)
);--创建表完成后，再添加alter table stu
add unique(name);

唯一键索引的特点

一个表可有多个索引
查询效率高
某一列设定唯一键，则该列所有数据唯一
如果唯一键指定not null，则相当于主键索引

（3）普通索引的创建

第一种方式

create index index_name on table_name (column1, column2, ...);

第二种方式

create table table_name (column1 datatype,column2 datatype,...index index_name (column1, column2, ...)
);

第三种方式

​
create table  Employees (EmployeeID int primary key,LastName varchar(50),FirstName varchar(50),index index_name (FirstName)
);​

普通索引的特点

允许重复值、
检索变快、
支持多个列的组合索引、
不强制列中数据的唯一性、
即使在索引列中有重复值，插入操作依然可以顺利进行

（4）查看索引

show index from table_name;

（5）删除索引

drop index index_name on table_name;

  (6)复合索引

如果有一个表有多个索引，并不是分开建立B+树
而是以多个索引组合作为新索引创建B+树
这种索引，成为复合索引
复合索引最左匹配原则
索引覆盖

9、哪一列适合作为索引？

高频被读取
唯一性比较强
低频被修改
不会出现在where字句中的字段不建立索引

10全文索引

全文索引（Full-text Index）是一种特殊类型的索引，主要用于加速对大量文本数据的搜索和查询。它允许数据库高效地执行复杂的文本搜索操作，比如查找包含特定单词或短语的记录。以下是关于全文索引的详细说明，包括其定义、优缺点、实现方法以及常用数据库中的示例。

什么是全文索引
全文索引是针对文本字段（如文章、评论、描述等）建立的索引，使得在这些字段中进行搜索时，可以快速找到相关的记录。与普通索引不同，全文索引支持更复杂的查询，例如模糊搜索、布尔搜索和短语搜索。