1200字范文 > MySQL的索引（普通索引唯一索引主键索引组合索引全文索引空间索引）相关操作

MySQL的索引（普通索引唯一索引主键索引组合索引全文索引空间索引）相关操作

时间：2022-02-16 06:08:39

索引介绍

Hash索引和B+tree索引的区别

应用场景

索引分类

普通索引

创建单列索引——普通索引（3种语法）

查看数据库中的索引

查看数据表中的索引

删除索引

唯一索引

主键索引

组合索引

创建和删除组合索引

全文索引

全文索引的案例

空间索引

索引的优点

索引的缺点

创建索引的原则

Hash算法

二叉树

平衡二叉树

BTREE树

每文一语

索引介绍

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

索引类似一本书的目录，比如要查找’student’这个单词，可以先找到s开头的页然后向后查找，这个就类似索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有Hash索引和B+Tree索引

Hash索引和B+tree索引的区别

1、在查询速度上，如果是等值查询，那么Hash索引明显有绝对优势，因为只需要经过一次 Hash 算法即可找到相应的键值，复杂度为O(1)；当然了，这个前提是键值都是唯一的。

如果键值不是唯一(或存在Hash冲突)，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据，这时候复杂度会变成O(n)，降低了Hash索引的查找效率。

所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等（当然B+tree索引也不适合这种离散型低的字段上）；

2、Hash 索引是无序的，如果是范围查询检索，这时候 Hash 索引就无法起到作用，即使原先是有序的键值，经过 Hash 算法后，也会变成不连续的了。因此

①、Hash 索引只支持等值比较查询、无法索成范围查询检索，B+tree索引的叶子节点形成有序链表，便于范围查询。

②、Hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询，因为需要对完整 key 做 Hash 计算，定位bucket。而 B+tree 索引具有最左前缀匹配，可以进行部分模糊查询。

③、Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。B+tree 索引的叶子节点形成有序链表，可用于排序。

3、Hash 索引不支持多列联合索引，对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用；

4、因为存在哈希碰撞问题，在有大量重复键值情况下，哈希索引的效率极低。B+tree 所有查询都要找到叶子节点，性能稳定；

应用场景

1、大多数场景下，都会有组合查询，范围查询、排序、分组、模糊查询等查询特征，Hash 索引无法满足要求，建议数据库使用B+树索引。

2、在离散型高，数据基数大，且等值查询时候，Hash索引有优势。

索引分类

按照功能划分，索引划为以下分类:

普通索引

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

创建单列索引——普通索引（3种语法）

create database mydb5;use mydb5;-- 方式1-创建表的时候直接指定create table student(sid int primary key,card_id varchar(20),name varchar(20),gender varchar(20),age int,birth date, phone_num varchar(20),score double,index index_name(name) -- 给name列创建索引);

直接使用index index_name（列名）即可

-- 方式2-直接创建-- create index indexname on tablename(columnname); create index index_gender on student(gender); -- 方式3-修改表结构(添加索引)-- alter table tablename add index indexname(columnname)alter table student add index index_age(age);

查看数据库中的索引

-- 1、查看数据库所有索引 -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’; select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';

查看数据表中的索引

-- 2、查看表中所有索引 -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’; select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';

-- 3、查看表中所有索引 -- show index from table_name; show index from student;

删除索引

drop index 索引名 on 表名 -- 或 alter table 表名 drop index 索引名

drop index index_gender on student -- 或 alter table student drop index index_name

唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

-- 方式1-创建表的时候直接指定create table student2(sid int primary key,card_id varchar(20),name varchar(20),gender varchar(20),age int,birth date, phone_num varchar(20),score double,unique index_card_id(card_id) -- 给card_id列创建索引);-- 方式2-直接创建-- create unique index 索引名 on 表名(列名) create unique index index_card_id on student2(card_id);-- 方式3-修改表结构(添加索引)-- alter table 表名 add unique [索引名] (列名)alter table student2 add unique index_phone_num(phone_num)

可以发现，就像我们创建普通索引的时候一样，将index前面加一个unique即可

删除索引的方法也是一样的

drop index index_card_id on student2 -- 或 alter table student2 drop index index_phone_num

主键索引

每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。

组合索引

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引。

复合索引的使用复合最左原则。

-- 创建索引的基本语法 create index indexname on table_name(column1(length),column2(length));

创建和删除组合索引

-- 组合索引use mydb5;-- 创建索引的基本语法-- 普通索引-- create index indexname on table_name(column1(length),column2(length)); create index index_phone_name on student(phone_num,name);-- 操作-删除索引drop index index_phone_name on student; -- 创建索引的基本语法-- 唯一索引create unique index index_phone_name on student(phone_num,name);

select * from student where name = '张三'; select * from student where phone_num = '15100046637'; select * from student where phone_num = '15100046637' and name = '张三'; select * from student where name = '张三' and phone_num = '15100046637'; /* 三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段才能够进行匹配。而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。 */

注意组合索引的匹配是从左到右依次

总结索引的匹配顺序：

首先根据创建组合索引的顺序，进行匹配，如果第一个是该索引的第一个，则可以用到该索引，如果第一个不是的话，而无法使用索引的效果，后期在MySQL优化里面会讲到索引的一些特性和具体使用。

全文索引

全文索引的关键字是fulltext

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。

用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

全文索引的版本、存储引擎、数据类型的支持情况：

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引；

在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用create index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多；

测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看:

-- 创建表的时候添加全文索引create table t_article (id int primary key auto_increment ,title varchar(255) ,content varchar(1000) ,writing_date date -- , -- fulltext (content) -- 创建全文检索);

全文索引的案例

insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'-10-01');insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'-10-02'); insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'-10-03');insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'-10-04');insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'-10-05');insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'-10-06');insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'-10-07');

-- 修改表结构添加全文索引alter table t_article add fulltext index_content(content)-- 直接添加全文索引create fulltext index index_content on t_article(content);

使用全文索引

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，格式:

select * from t_article where match(content) against('yo’); -- 没有结果单词数需要大于等于3 select * from t_article where match(content) against('you'); -- 有结果

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

创建空间索引的列，必须将其声明为NOT NULL。

空间索引一般是用的比较少，了解即可。

create table shop_info (id int primary key auto_increment comment 'id',shop_name varchar(64) not null comment '门店名称',geom_point geometry not null comment '经纬度’,spatial key geom_index(geom_point));