MySQL之索引视图--索引

目录

1. 索引

1.1 什么是索引

索引的原理 

1.2 索引的优缺点

1.3 索引的分类

1.4 索引的设计原则

1.5 创建索引

1.5.1 普通索引

1.5.2 创建唯一性索引

1.5.3 创建全文索引（FULLTEXT）

1.5.4 创建单列索引

1.5.5 创建多列索引

1.5.6 创建空间索引

1.5.7 CREATE INDEX

1.5.8 ALTER TABLE

 1.6 删除索引

 1.7 MySQL使用索引的场景

 MySQL索引的优化

 1.8 SQL如何使用索引

 1.9 聚簇索引和非聚簇索引

 1.9.1 非聚簇索引

 1.9.2 聚簇索引

 1.10 实例操作

---

1. 索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能

的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索

引、全文索引、单列索引、多列索引和空间索引等。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里

所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速

度。（注意：一般数据库默认都会为主键生成索引）。

1.1 什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理： 

就是把无序的数据变成有序的查询。

1. 把创建的索引的列的内容进行排序

2. 对排序结果生成倒排表

3. 在倒排表内容上拼上数据地址链

4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

1.2 索引的优缺点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和

父表之间的 联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显

著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要

占用物理空 间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索

引，造成数据的维护速度降低了。

1.3 索引的分类

索引分为聚簇索引和非聚簇索引两种：

聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；

聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

1.4 索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的

索引。本小节将向小伙伴们介绍一些索引的设计原则：

1. 选择惟一性索引

2. 为经常需要排序、分组和联合操作的字段建立索引

3. 为常作为查询条件的字段建立索引

4. 限制索引的数目

5. 尽量使用数据量少的索引

6. 尽量使用前缀来索引

7. 删除不再使用或者很少使用的索引

1.5 创建索引​​​​​​​

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种

方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER

TABLE语句来创建索引。

创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：

CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);

 1.5.1 普通索引

# 直接创建索引

CREATE INDEX index_name ON table(column(length))

# 创建表的时候同时创建索引

Create table index1(
    Id int,
    Name varchar(20),
    Sex boolean,
    index(id),
);

# 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length));

# 查询索引

Show create table index1;

# 查询某张表中索引情况

show index from table_name;

# 使用计划查询SQL使用索引情况

Explain select * from index1 where id=1 ;

# 删除索引

DROP INDEX index_name ON table;

1.5.2 创建唯一性索引

Create table index2(
    Id int unique,
    Name varchar(20),
    Unique index index2_id(id asc)
);

1.5.3 创建全文索引（FULLTEXT）

# MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；

# 他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，

# 或是随后使用ALTER TABLE 或CREATE INDEX被添加。

# 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中， 

# 然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。

# 不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

只能创建在char,varchar或text类型的字段上：

create table index3(
    Id int,
    Info varchar(20),
    Fulltext index index3_info(info)
);

explain select * from table where id=1;

EXPLAIN分析结果的含义:

table：这是表的名字。

type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，

性能从差到好）。

possible_keys：可能可以利用的索引的名字。

Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。

key_len：索引中被使用部分的长度，以字节计。

ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就

是1。

Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。

1.5.4 创建单列索引

Create table index4(
    Id int,
    Subject varchar(30),
    Index index4_st(subject(10))
); 

1.5.5 创建多列索引

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。

如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。

也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，

MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

Create table index5(
    Id int,
    Name varchar(20),
    Sex char(4),
    Index index5_ns(name,sex)
);

1.5.6 创建空间索引

Create table index6(
    Id int,
    Space geometry not null,
    Spatial index index6_sp(space)
)engine=myisam;

建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型

包括geometry，point，linestring和polygon类型等，平时很少用到。

1.5.7 CREATE INDEX

首先保证已经存在表，才能使用这个命令创建索引。

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：

help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX 索引名
ON 表名 (属性名 [ (长度) ] [  ASC | DESC] );

1．创建普通索引
	CREATE INDEX index_name ON table(column(length))

2．创建惟一性索引
	CREATE UNIQUE INDEX indexName ON table(column(length))

3．创建全文索引
	CREATE FULLTEXT INDEX index_content ON article(content)

4．创建单列索引
	CREATE INDEX index3_name on index3 (name(10));

 1.5.8 ALTER TABLE

用ALTER TABLE语句来创建索引，也是存在表的情况下。

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形

式如下：

ALTER  TABLE 表名  ADD  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX 
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

1．创建普通索引
	ALTER TABLE table_name ADD INDEX index_name (column(length))

2．创建惟一性索引
	ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3．创建全文索引
	ALTER TABLE index3 add fulltext index index3_name(name);

4．创建单列索引
	ALTER TABLE index3 add index index3_name(name(10));

 1.6 删除索引

删除索引是指将表中已经存在的索引删除掉。对应已经存在的索引，可以通过DROP语句来删除索

引。基本形式如下：

DROP INDEX 索引名 ON 表名 ;

 1.7 MySQL使用索引的场景

1. 快速查找符合where条件的记录

2. 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规

    模最小的那个索引，以便尽快淘汰不符合条件的记录。

3. 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也

    会被自动作为索引来加速查找。

例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均会作为

索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。

4. 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。

5. 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引

6. 对建立了索引的字段做sort或group操作时，MySQL会使用索引

MySQL索引的优化

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索

引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和

DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁

盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索

引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要

花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意

事项和优化方法。

何时使用聚集索引或非聚集索引:

 1.8 SQL如何使用索引

1. B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作。

2. 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。

3. 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。

4. 对于联合索引，sql条件中的最左前缀匹配字段会用到索引。

5. 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或

_开头的情形)。而理论上每张表里面最多可创建16个索引（版本不同，可能会有变化）。

1.9 聚簇索引和非聚簇索引

1.9.1 非聚簇索引

索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。

先创建一张表：

CREATE TABLE `user` ( 
`id` INT NOT NULL ,
`name` VARCHAR NOT NULL ,
`class` VARCHAR NOT NULL);

对于MYISAM引擎，如果创建 id 和 name 为索引。对于下面查询：

select * from user where id = 1;

会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到改行数据在硬盘

中的存储位置，因此MYISAM引擎的索引，叶子页面上不仅存储了主键id 还存储着 数据存储的地

址信息。如图：

像这样的索引就称为非聚簇索引。

非聚簇索引的二级索引与主键索引类似。

1.9.2 聚簇索引

对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取

数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。

聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地

址，而是直接存储数据：

这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！

关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多

数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢

得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就

是版本号） 以及回滚指针。

在二级索引方面， InnoDB 与 MyISAM 有很大区别。

InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引

来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般

来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

假设对 InnoDB 引擎上表name字段加索引，那么name索引叶子页面则只会存储主键id：

检索时，先通过name索引树找到主索引id，再通过id在主索引树的聚簇索引叶子页面取出数据。

参考资料： 参考资料：简单理解InnoDB聚簇索引与MyISAM非聚簇索引_若尘拂风的博客-CSDN博客_myisam的非聚簇索引

1.10 实例操作

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)

学号，姓名，性别，年龄，所在系 Sno为主键

课程表：Course (Cno, Cname,)

课程号，课程名 Cno为主键

学生选课表：SC (Sno, Cno, Score)

学号，课程号，成绩 Sno,Cno为主键

1. 用SQL语句创建学生表Student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默

认值是 “计算机”。

##创建Student表并查看表结构
mysql> create table Student(
    -> Sno int primary key auto_increment,
    -> Sname varchar(200) not null unique,
    -> Ssex varchar(20) check (Ssex='男' or Ssex='女') not null,
    -> Sage int not null,
    -> Sdept varchar(100) default '计算机' not null
    -> );

mysql> decs Student;

 2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。

mysql> alter table Student modify Sage smallint;

 表中属性变化如下：

 3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。

首先，我们先创建含有Cno的Course表和SC表：

##创建SC表和Course表并查看表结构
mysql> create table Course(
    -> Cno int primary key not null,
    -> Cname varchar(100) not null
    -> );

mysql> create table SC(
    -> Sno int not null,
    -> Cno varchar(200) primary key not null,
    -> Score int not null
    -> );

mysql> desc SC;

##建立按Sno和Cno组合的升序的主键索引
mysql> create unique index SC_INDEX on SC(Sno asc,Cno asc);

4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

mysql> create view stu_info as select Student.Sname as '姓名',Student.Ssex as '性别',Course.Cname as '课程名',SC.Score as '成绩' from Student,Course,SC;