MySQL中什么是索引?常用的索引有哪些种类?索引在什么情况下会失效?

最新推荐文章于 2023-07-20 19:49:48 发布

飘飘~

最新推荐文章于 2023-07-20 19:49:48 发布

阅读量1.1k

点赞数 41

文章标签：开发语言数据库开发 mysql 数据库 database

本文链接：https://blog.csdn.net/m0_46420244/article/details/124956104

版权

提示:面试baba必问题目之一！！！

2.1.3 主键索引（primary key）

① 查询条件中有or，即使有部分条件带索引也会失效。

② like查询以%开头。

③ 如果列类型是字符串，未使用引号引用数据，导致不走索引。

④ 索引列上参与计算。

⑤ 违背最左匹配原则。

⑥ 当MySQL全表扫描比索引快的时候。

前言

在这个大数据的时代，庞大的数据量需要高效的数据库操作技术，索引是提高数据库操作效率的重要对象。我们可以理解为“排好序的快速查找数据结构”。

本文将从索引的定义，索引的分类，索引的优势和劣势，索引的应用场景等四个方面与大家分享自己的对索引的理解，仅供大家参考。

Student表：

一、索引的定义

MySQL官方定义：索引(index)是帮助MySQL高效获取数据的数据结构。

本质：索引是数据结构。是一张描述索引列的列值与元表中记录行之间一一对应关系的序表。

结论：除数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现可高级查找算法，这种数据结构就是索引。

注意:

① 索引会影响where后面的排序和查找（oder by）

② 索引往往以文件的形式存储在磁盘上。

二、索引的分类

分类：

单列索引：普通索引、唯一索引、主键索引。

多列索引：全文索引。

2.1 单列索引

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

2.1.1 普通索引

定义：最基本的索引类型，没有唯一性的限制。

-- 创建普通索引
CREATE INDEX 索引名称 ON 表名(字段名);
CREATE INDEX stu_name on student(sname);
-- 修改普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(字段名);
ALTER TABLE student ADD INDEX sname(sname);

2.1.2 唯一索引（unique）

定义：不允许具有索引值相同的行，从而表示重复的索引和键值。

-- 添加唯一索引
CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);
CREATE UNIQUE INDEX stu_id ON student(sid);
-- 修改唯一索引
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段名);
ALTER TABLE student ADD UNIQUE INDEX sid(sid);

2.1.3 主键索引（primary key）

定义：在数据库关系图中，定义一个主键，将自动创建主键索引。

-- 添加主键索引
-- 创建表的时候创建
-- 修改主键索引
ALTER TABLE 表名  ADD PRIMARY KEY (字段名);
ALTER TABLE student ADD PRIMARY KEY (sid);

2.2 多列索引

定义：在表中的多个字段组合上创建的索引，只有在查询条件中欧冠使用了这些字段的左边字段时，索引才会被使用，使用组合索引遵循最左前缀集合。（）

2.2.1 全文索引（fulltext）

定义：索引的关键技术，用于检索文本信息，就是在某些文字中，通过其中的关键字或者词语段落，找到所属的记录行。

-- 创建全文索引
CREATE FULLTEXT INDEX 全文索引名 ON 表名(想要设置全文索引的字段) WITH PARSER ngram;
CREATE FULLTEXT INDEX content ON student(ssex,sname) WITH PARSER ngram;
-- 修改全文索引(with后面代表支持中文)
ALTER TABLE 表名 ADD FULLTEXT 全文索引名(想要设置全文索引的字段) WITH PARSER ngram;
ALTER TABLE student ADD FULLTEXT studentcontent(ssex,sname) WITH PARSER ngram;

主键不能设置全文索引！！！

三、索引的优势和劣势

3.1 索引的优势

①提高数据检索的效率，降低数据库的IO成本。（大学图书馆建立索引）

②通过索引对数据进行排序，降低排序成本，降低CPU小号。

性质：

（1）高效性：使用索引可以提高数据库的查询效率。

（2）完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性。

（3）唯一性：索引可以确保所查的数据的唯一性。

（4）特殊能力：通过使用索引，可以再查询过程中，使用优化隐藏器，提高系统性能。

*查找和排序速度变快。

3.2 索引的劣势

①创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

②索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

③当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

*插入(insert)、修改（update）、删除（delete）较慢。

四、索引的应用场景

4.1 适合建立索引的场景

        第一：在经常需要搜索的列上，可以加快搜索的速度；
        第二：在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
        第三：在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
        第四：在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
        第五：在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
        第六：在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

总而言之，经常使用查找的字段就加上索引。

4.2 索引失效

使用学生表，sid设置为主键索引，sname设置为普通索引。Navicat中type为查询类型，ALL为全表检索。

① 查询条件中有or，即使有部分条件带索引也会失效。

例如：

SELECT * FROM student WHERE Sid=1;

查询语句中有or时候

SELECT * FROM student WHERE Sid=1 OR birthday="1900-01-01";

② like查询以%开头。

例如：

SELECT * FROM student WHERE Sname="张三";

查询语句中like以%开头

SELECT * FROM student WHERE Sname LIKE"%三";

③ 如果列类型是字符串，未使用引号引用数据，导致不走索引。

例如：

并未命中普通索引，因为222字符串未加上引号。

SELECT * FROM student WHERE Sname=222;

④ 索引列上参与计算。

例如：

并未命中sid主键索引，因为做了计算。

SELECT * FROM student WHERE Sid-1=1;

⑤ 违背最左匹配原则。

例如：

因为ssex索引是建立在（ssex，sname）作何索引上面的，所以为命中索引。

SELECT * FROM student WHERE Ssex="男";

⑥ 当MySQL全表扫描比索引快的时候。

⑦其他及上索引失效参考索引失效超详细https://blog.csdn.net/sy_white/article/details/122112440?utm_source=app&app_version=5.3.0&code=app_1562916241&uLinkId=usr1mkqgl919blen

@最后祝大家问的都会，蒙的都对！！！yyds

——元气满满的阿飘

飘飘~

关注

41
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
4
评论
MySQL中什么是索引?常用的索引有哪些种类?索引在什么情况下会失效?

提示:面试baba必问题目之一！！！目录前言一、索引的定义二、索引的分类 2.1 单值索引 2.2 复合索引 2.3 唯一索引三、索引的优势和劣势 3.1 索引的优势 3.2索引的劣势四、索引的应用场景 4.1 适合建立索引的场景 4.2 不适合建立索引的场景五、扩展——索引结构和检索原理前言在这个大数据的时代，庞大的数据量需要...
复制链接

扫一扫