数据优化查询之索引学习

借鉴文章：https://blog.csdn.net/u012954706/article/details/81241049

一  .索引是啥？

索引是对数据库表中一列或多列的值进行排序的一种存储结构。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。

简单来说，索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

 

二、索引的作用

在数据库系统中建立索引主要有以下作用：

1、快速取数据； --索引的排序功能和存储逻辑

2、保证数据记录的唯一性；--唯一索引

3、实现表与表之间的参照完整性；--主键索引

4、在使用ORDER by、group by子句进行数据检索时，利用索引可以排序和分组的时间。--索引的排序功能和存储逻辑

 

三、索引的类型

索引是一个抽象的名次，它包含了很多具体的细分，不同的实现方式形成的不同类型的索引，也将在不同的场景中发挥各自的作用。在这里，我们根据数据库的功能，可以在数据库设计器中创建四种索引：普通索引、唯一索引、主键索引和聚集索引（非聚集索引）。

下面我依次做一个介绍。

1、普通索引

最基本的索引类型，没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)； ALTER INDEX RENAME TO;[2]

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) )；

2、唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

对某个列建立UNIQUE索引后，插入新纪录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

创建唯一索引的几种方式：

创建索引，例如CREATE UNIQUE INDEX ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); ；

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

3、主键索引

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

4、聚集索引（聚簇索引）

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

 

 

1.2、什么场景不适合创建索引

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
第五，不会出现在where条件中的字段不该建立索引。

1.3、什么样的字段适合创建索引

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5、经常用到排序的列上，因为索引已经排序。
6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的
2、索引优缺点
2.1、优点

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
2.2、缺点

索引的缺点是创建和维护索引需要耗费时间<br/**>
索引可以提高查询速度，会减慢写入速度

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
3、索引分类
1，普通索引：
仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

 1 CREATE INDEX index_name on user_info(name) ; 

2，唯一索引：
与普通索引类型，不同的是：加速查询 + 列值唯一（可以有null）

 1 CREATE UNIQUE INDEX mail on user_info(name) ; 

3，全文索引：
全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
4，组合索引：
将几个列作为一条索引进行检索，使用最左匹配原则。
1、普通索引
1.1.1、创建表的时候同事创建索引

1 create table healerjean (
2 id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
3 name VARCHAR(32) NOT NULL COMMENT '姓名',
4 email VARCHAR(64) NOT NULL COMMENT '邮箱',
5 message text DEFAULT NULL COMMENT '个人信息',
6 INDEX index_name (name) COMMENT '索引name'
7 ) COMMENT = '索引测试表';

 

 

1.1.2、在存在的表上创建索引

 1 create index index_name on healerjean(name) 

 

1.1.3、注意：

对于创建索引时如果是blob 和 text 类型，必须指定length。

 1 create index ix_extra on in1(message(200));

2alter table employee add index emp_name (name); 

 

1.2、删除索引

 1 drop index_name on healerjean;

2 alter TABLE users drop index name_index ; 

1.3、查看索引

这个时候，我们会发现其实主键id也是一个索引

 1 show index from healerjean;