MySQL学习之索引入门

一.索引基础

1.什么是索引?
索引是一个单独的,存储在磁盘上的数据库结构,它包含着对数据库里所有记录的引用指针。使用索引可以让我们不必在查找数据时按顺序一个一个往下找,而是一次到位,方便快捷。

MySQL中索引的存储类型有两种:BTREE和HASH(这里涉及数据结构,先了解即可)

2.使用索引的优点:
1)通过创建唯一索引保证每行数据的一致性
2)可以大大加快数据的查询速度
3)使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间。
4)加速表和表之间的连接

3.索引的不利:
1)需维护
2)占用磁盘空间

4.索引的分类:
1)普通索引:允许在索引的列中插入重复值和空值。
2)唯一索引:索引列必须唯一,允许空值,但主键索引是一种特殊的唯一索引,不允许空值
3)单列索引:顾名思义,以一个列所作为索引列,一个表中可以有多个单列索引
4)组合索引:以多个列的组合作为索引,组合索引的查询遵循最左前缀原则(只有在查询条件中使用了这些列的左边列时,索引列才会被使用)
5)全文索引:在定义索引的列上支持值的全文范围内的查找,允许在这些索引列中插入重复值和空值。

6)空间索引:对空间数据类型的字段建立的索引。(初学了解即可)

5.索引设计原则: 这些原则让我们建立良好的索引避免性能浪费

1)尽量使用最少的索引达到最好的效果。太多的索引不仅占用磁盘空间,而且会影响插入,删除,更新等操作(因为表中数据更改的同时存储索引的磁盘的数据也会发生变化)

2)避免对经常更新的表进行太多索引,且尽量用单列索引,对经常用于查询的字段应该创建索引。

3)索引适合于数据量大的表。

4)在不同值少的列上不要使用索引(索引失效)

5)当唯一性是某种数据的特征时,可以指定唯一索引

6)在频繁进行排序或分组的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

二.索引SQL语句

1.创建索引:

1)在创建表时指定索引

例子:
CREATE TABLE book
( bookid INT NOT NULL,
bookname VARCHAR(225) NOT NULL,
authors VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) //建立普通单列索引
);

同理:
UNIQUE INDEX(year_publication) //建立唯一索引
INDEX MultiId(bookid,bookname) //建立组合索引 ,查询遵循最左原则
FULLTEXT INDEX fullText(bookname) //建立全文索引

2)向已存在表中加入索引

ALTER TABLE book ADD INDEX bookIdx(bookid) 或者
CREATE INDEX bookIdx ON book(bookid)
其他同理

2.删除索引

ALTER TABLE book DROP INDEX bookIdx; 或者
DROP INDEX bookIdx ON book;

三.索引案例实战

1.首先我们先创建一个表,不指定索引:

CREATE TABLE book (
  bookid int(11) NOT NULL,
  bookname varchar(225) NOT NULL,
  authors varchar(225) NOT NULL,
  info varchar(225) DEFAULT NULL,
  comment varchar(225) DEFAULT NULL,
  year_publication year(4) NOT NULL
);

表内字段如下:

字段名 描述
bookid 书的id,具有唯一性
bookname 书名,不具唯一性
author 作者名,不具唯一性
info 书的基本简介
comment 书的评论信息
year_publication 出版年份

然后向里面插入二十条数据:

use study;
INSERT INTO book VALUES(1,'数据库原理与应用教程','张三','本书可作为数据库入门书籍,里面详细介绍了数据库原理和操作','此书简单易懂,实为佳作',2020);
INSERT INTO book VALUES(2,'计算机网络','张四','本书详细介绍了计算机网络的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(3,'java核心技术卷一','张五','本书可作为java基础知识的学习书籍','此书深入浅出,适合初学者',2020);
INSERT INTO book VALUES(4,'算法导论','张五','本书详细介绍了算法的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(5,'计算机操作系统','张四','本书详细介绍了操作系统的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(6,'数据结构','张六','本书详细介绍了数据结构的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(7,'计算机组成','张七','本书详细介绍了计算机组成的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(8,'C语言程序设计','张四','本书详细介绍了C语言程序设计的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(9,'web程序开发基础','张八','本书详细介绍了web开发的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(10,'web程序开发进阶','张四','本书详细介绍了web开发进阶的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(11,'java核心技术卷二','张九','本书详细介绍了java高级特性的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(12,'spring cloud','张四','本书详细介绍了spring cloud的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(12,'java并发编程详解','张九','本书详细介绍了java并发编程的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(14,'java多线程编程实战指南','张四','本书详细介绍了java多线程的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(15,'深入浅出spring boot','张十','本书详细介绍了spring boot的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(16,'java并发编程实战','张二','本书详细介绍了java并发编程的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(17,'spring入门经典','张四','本书详细介绍了spring学习的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(18,'设计模式','张三','本书详细介绍了设计模式的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(19,'编译原理','张四','本书详细介绍了编译原理的相关知识','此书广度很好',2020);
INSERT INTO book VALUES(20,'C++程序设计','张六','本书详细介绍了C++程序设计的相关知识','此书广度很好',2020);
select * from book;

下面我们对它进行操作:
1.通过表的结构我们很容易知道bookid具有唯一性,我们可是将其设置为索引:
在这里插入图片描述
然后我们以bookid作为查询条件时会一步到位(时间复杂度为1):
在这里插入图片描述
如果没有bookid索引的话,时间复杂度为10

2.从表的结构我们知道,除了bookid之外,书名和作者一起也构成了唯一性:
于是:
在这里插入图片描述
然后进行查询操作:
在这里插入图片描述
下面演示最左原则:
下面的查询不会用到索引:
在这里插入图片描述
但下面的查询会用到:
在这里插入图片描述

栏目
728_90 cn stocks