MySQL高阶(七)——MySQL索引

最新推荐文章于 2024-11-06 10:59:12 发布

木鬼与槐

最新推荐文章于 2024-11-06 10:59:12 发布

阅读量842

点赞数 30

分类专栏： MYSQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_58305115/article/details/140707315

版权

MYSQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

7.MySQL高阶——MySQL索引

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
索引的主要目的是提高查询速度。通过减少查询所需的磁盘I/O操作和内存消耗，以及加速排序和分组操作，来提高查询速度。

索引的分类

按实现方式分类：Hash索引和B+Tree索引

按功能分类：

在这里插入图片描述

单列索引

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

普通索引

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

创建索引

##   创建库
create database csdn;
use csdn;
#############  普通索引  ###########################
-- 方式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列创建索引
);

-- 方式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);
##  查看表结构
desc student;

在这里插入图片描述

查看索引

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

-- 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` = 'csdn' and a.table_name like '%student%';

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

删除索引

###   删除索引
#drop index 索引名 on 表名
drop index index_gender on student;
-- 或
#alter table 表名 drop index 索引名
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)

在这里插入图片描述

删除索引

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));
create index index_phone_name on student(phone_num,name);

## 查看表结构
desc  student;

在这里插入图片描述

删除索引

-- 操作-删除索引
 drop index index_phone_name on student;

实例操作

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眼中是等价的。
虽然四条语句都可以查出结果，但是索引主要目的是提高查询速度。

全文搜索

使用的关建字是fulltext
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。
用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。